CARTILLA BÁSICA PL/SQL
El un Lenguaje Procedimental, que contiene instrucciones del Lenguaje de Bases de Datos
SQL, Estructuras de Programación y recursos propios de ORACLE, que hacen de éste una
herramienta poderosa para el desarrollo de aplicaciones.
La potencialidad que este lenguaje le aporta a las herramientas Developer 2000, lo hacen
indispensable para el desarrollo de aplicaciones profesionales.
ESTRUCTURA BÁSICA DE UN PROGRAMA DE PL/SQL
DECLARE
-- Bloque para declarar variables.
BEGIN
-- Cuerpo del programa
BEGIN
-- Instrucción SQL
END;
-- Fin de instrucciones SQL
END;
-- Fin del programa PL/SQL
TIPOS DE DATOS
Los tipos de datos son los que normalmente se usan en la definición de TABLAS, más
algunos adicionales.
NUMBER: datos de tipo numérico (máxima precisión 38 dígitos) - NUMBER(n) o NUMBER
(n,m)
CHAR: datos de tipo carácter fijos (máximo 255 caracteres) Char(n)
VARCHAR2: datos de tipo carácter variables (máximo 2000 caracteres) varchar2(n)
DATE: Para datos de tipo fecha y hora
BOOLEAN: Puede contener valores booleanos: TRUE, FALSE
BINARY_INTEGER: Integer
Cabe notar que los dos últimos tipos de datos no se usan para definiciones de tablas. A
continuación algunos ejemplos de uso de tipos de datos para definición (dentro del
bloque de DECLARE):
VARIABLES ESCALARES:
uno number;
dos number(7):=2;
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
tres number(10,2);
cuatro varchar2(30):= ' ';
cinco boolean;
seis date;
VARIABLES NO ESCALARES:
Existen otros tipos de variables que se suelen definir sobre campo, registros y/o matrices.
Esta definición se hace similar a cualquier otro lenguaje de programación.
TYPE nombre_tipo IS RECORD (variable1 tipo1; variable2 tipo2);
v_registro nombre_tipo;
O tipo Tablas:
TYPE nombre_tabla IS TABLE OF tipo INDEX BY BINARY_INTEGER;
CONSTANTES:
Se definen como variables escalares adicionando el uso de la palabra CONSTANT, al hacer
uso de esta condición limita a que el elemento no se podrá modificar en el transcurso del
programa.
costo CONSTANT number(4):=2300;
ATRIBUTOS:
En PL/ SQL las variables tienen atributos que permiten referenciar el tipo de dato y la
estructura de un objeto sin repetir su definición.
Por ejemplo si en la tabla EMPLOYEES tenemos el campo job_id que es varchar2(6), y
sobre este campo quiero definir una variable que se llame job_rank, se puede hacer de
dos formas:
DECLARE
job_rank varchar2(6);
Pero la forma correcta es:
DECLARE
job_rank employees.job_id%TYPE;
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
El atributo %TYPE permite a la variable heredar el tipo de dato definido en la tabla para
ese campo, permitiendo así un mantenimiento adecuado de la aplicación en caso de
modificaciones y una mayor consistencia en los datos.
Otra forma de definición es con %ROWTYPE, usada para definir una variable tipo registro
con las mismas columnas y los mismos tipos de datos de la fila de una tabla.
En este caso dentro de la programación para referirse a dicha variable se debe hacer
equivalente a cualquier lenguaje de programación:
nombre_registro.nombre_campo:=valor;
INICIALIZACIÓN O ASIGNACIÓN DE VARIABLES
Las variables dentro de un programa PL, se pueden asignar de varias formas y en
diferentes momentos dentro del bloque de programación:
Asignación en la definición:
DECLARE
variable number(3):=3;
Asignación directa dentro del programa:
DECLARE
variable number(3);
BEGIN
…
variable:=3;
…
END;
Asignación por un INTO dentro de una instrucción SQL:
DECLARE
variable number(6);
BEGIN
…
SELECT salary INTO variable
FROM employees
WHERE employee_id=100;
…
END;
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
ESTRUCTURAS DE PROGRAMACIÓN
Estas estructuras son similares a las que normalmente se usan en los diferentes lenguajes
de programación (Java, C#, C++, entre otros). Dentro de PL/SQL se usan con el fin de
procesar datos bajo condicionales y de manera interactiva.
CONDICIONALES:
IF condición THEN
Instrucciones;
Comandos SQL;
END IF;
IF condición THEN
Instrucciones;
Comandos SQL;
ELSE
Instrucciones;
Comandos SQL;
END IF;
IF condición THEN
Instrucciones;
Comandos SQL;
ELSIF
Instrucciones;
Comandos SQL;
ELSE
Instrucciones;
Comandos SQL;
END IF;
Las condiciones son las conocidas o usadas en cualquier lenguaje de programación
comercial, entre algunos ejemplos se tienen:
IF variable1>variable2 THEN …
IF salary*3 = 4000 THEN …
IF commission_pct >= 0,3 THEN …
CICLOS:
WHILE condición LOOP
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
Instrucciones;
Comandos SQL;
END LOOP;
FOR contador IN 1..10 LOOP
Instrucciones;
Comandos SQL;
END LOOP;
FOR variable_cursor IN cursor LOOP
Instrucciones;
Comandos SQL;
END LOOP;
CURSORES:
Se usa para definir un apuntador a una tabla a partir de una consulta, para luego ser
utilizada en el programa. El control del recorrido dentro de la tabla es controlado por
programación.
Existen cursores explícitos e implícitos. Los implícitos, los usa SQL en todos sus
procedimientos de manipulación de datos, incluyendo consultas que devuelven una sola
fila. Los explícitos son los que se crean para poder tener el control de ellos en un área de
memoria.
DECLARE
CURSOR c_empleados IS
SELECT employee_id, first_name, salary
FROM employees
WHERE job_id=’IT_PROG’;
programadores c_empleados%ROWTYPE;
aumento number(6);
BEGIN
…
OPEN c_empleados;
FETCH c_empleados INTO programadores;
WHILE (c_empleados%found) LOOP
…
aumento:=programador.commission_pct + 0,3;
FETCH c_empleados INTO programadores;
END LOOP;
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
CLOSE c_empleados;
…
END;
Otro de los usos de los cursores es para actualizar campos bajo determinadas condiciones.
Para este caso hay que agregarle la sentencia FOR UPDATE OF y WHERE CURRENT OF:
DECLARE
CURSOR c_familia IS
SELECT last_name, salary
FROM employees
WHERE last_name=’King’
FOR UPDATE OF salary;
rosca c_familia%ROWTYPE;
aumento number(6);
BEGIN
…
FOR rosca IN c_familia LOOP
…
aumento:=[Link] + 10000;
UPDATE employees
SET salary = aumento
WHERE CURRENT OF c_familia;
END LOOP;
…
END;
FUNCIONES
Funciones temporales:
Una función es un programa que computa un valor, tiene un valor único de retorno.
FUNCTION salario_anual (v_employee_id IN employees.employee_id%TYPE)
RETURN number
IS
v_salrio_anual number:=0;
BEGIN
BEGIN
SELECT salary*12 INTO v_salario_anual
FROM employees
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
WHERE employee_id= v_employee_id;
EXCEPTION
WHEN no_data_found THEN
Null;
WHEN others THEN
Null;
END;
RETURN(v_salario_anual);
END;
Esta función solo es posible ejecutarla dentro de un bloque de PL/SQL, para realizar su
llamado, se ejecuta la línea:
Para evaluar el resultado de la función se usa:
total_pagado:=salario_anual(employee_id);
Funciones almacenadas:
Cuando una función es muy usada dentro de la aplicación, y tiene mucha transacción, en
lugar de estarla creando dentro de cada usuario y generando así congestionen los
procesos, es preferible dejarla como un objeto más en la base de datos.
CREATE OR REPLACE FUNCTION raiz_cuadrada (numero IN number)
RETURN number
IS
residuo number:=0;
BEGIN
IF num < 0 then
residuo:=0;
ELSE
residuo:=SQRT(numero);
END IF;
RETURN(Res);
END;
Luego de creada la función, se puede probar con el siguiente comando SQL:
SELECT raiz_cuadrada(4) FROM dual;
Para saber que funciones o procedimientos hay creados dentro de la base de datos, esto
se puede realizar mediante la instrucción SQL:
SELECT DISTINCT name, type
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
FROM user_source;
Para poder borrar el objeto, se debe realizar desde el usuario que lo creo, con la
instrucción:
DROP FUNCTION raiz_cuadrada;
PROCEDIMIENTOS ALMACENADOS:
CREATE OR REPLACE PROCEDURE nombre_procedimiento(
parametro modo tipo,...)
IS
Variables tipo;
BEGIN
Instrucciones;
Comandos SQL;
END;
EJEMPLOS SOBRE MODELO HR
BLOQUE ANÓNIMO:
Realizar un bloque anónimo que haga un incremento de las comisiones de todos los
empleados, dependiendo de la antigüedad y salario.
• Si lleva más de 20 años y el salario está por debajo de U$22000 incrementarle 0,40 a la
comisión actual.
• Si lleva más de 15 años y el salario está por debajo de U$10000 incrementarle 0,30 a la
comisión actual.
• Si lleva más de 10 años y el salario está por debajo de U$7200 incrementarle 0,20 a la
comisión actual.
• Si lleva menos de 5 incrementarle 0,01 a la comisión actual.
DECLARE
v_años NUMBER;
v_comision employees.commission_pct%TYPE;
v_nueva_comision employees.commission_pct%TYPE := 0;
v_salario [Link]%TYPE;
CURSOR actualizar_comision_cursor IS
select *
from employees
FOR UPDATE OF commission_pct NOWAIT;
BEGIN
FOR emp_record IN actualizar_comision_cursor LOOP
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
v_años := round(months_between(sysdate, emp_record.hire_date)/12,0);
v_comision := NVL(emp_record.commission_pct, 0);
v_salario := emp_record.salary;
IF v_años > 20 AND v_salario < 22000 THEN
v_nueva_comision := (v_comision + 0.4);
ELSIF (v_años BETWEEN 15 AND 20) AND v_salario < 10000 THEN
v_nueva_comision := (v_comision + 0.3);
ELSIF (v_años BETWEEN 10 AND 15) AND v_salario < 7200 THEN
v_nueva_comision := (v_comision + 0.2);
ELSIF v_años < 5 THEN
v_nueva_comision := (v_comision + 0.01);
END IF;
IF v_nueva_comision <> v_comision THEN
UPDATE employees
SET commission_pct = v_nueva_comision
WHERE CURRENT OF actualizar_comision_cursor;
END IF;
END LOOP;
END;
/
Con el fin de realizar las pruebas necesarias para comprobar la funcionalidad del bloque
para satisfacer lo planteado, se adicionaron algunas líneas de impresión en pantalla para
validar que cambios hacia.
SET SERVEROUTPUT ON
DECLARE
v_años NUMBER;
v_comision employees.commission_pct%TYPE;
v_nueva_comision employees.commission_pct%TYPE := 0;
v_salario [Link]%TYPE;
CURSOR actualizar_comision_cursor IS
SELECT *
FROM employees
FOR UPDATE OF commission_pct NOWAIT;
BEGIN
FOR emp_record IN actualizar_comision_cursor LOOP
v_años := round(months_between(sysdate, emp_record.hire_date)/12,0);
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
v_comision := NVL(emp_record.commission_pct, 0);
v_salario := emp_record.salary;
DBMS_OUTPUT.PUT_LINE('Al empleado de codigo '||emp_record.employee_id||' que
lleva '||v_años||' años en la empresa ');
IF v_años > 20 AND v_salario < 22000 THEN
v_nueva_comision := (v_comision + 0.4);
DBMS_OUTPUT.PUT_LINE('Se le incrementara la comision en 0.4, quedando la
comision en '||v_nueva_comision);
ELSIF (v_años BETWEEN 15 AND 20) AND v_salario < 10000 THEN
v_nueva_comision := (v_comision + 0.3);
DBMS_OUTPUT.PUT_LINE('Se le incrementara la comision en 0.3, quedando la
comision en '||v_nueva_comision);
ELSIF (v_años BETWEEN 10 AND 15) AND v_salario < 7200 THEN
v_nueva_comision := (v_comision + 0.2);
DBMS_OUTPUT.PUT_LINE('Se le incrementara la comision en 0.2, quedando la
comision en '||v_nueva_comision);
ELSIF v_años < 5 THEN
v_nueva_comision := (v_comision + 0.01);
DBMS_OUTPUT.PUT_LINE('Se le incrementara la comision en 0.01, quedando la
comision en '||v_nueva_comision);
ELSE
DBMS_OUTPUT.PUT_LINE('El empleado mantendra su comision actual');
END IF;
IF v_nueva_comision <> v_comision THEN
UPDATE employees
SET commission_pct = v_nueva_comision
WHERE CURRENT OF actualizar_comision_cursor;
END IF;
END LOOP;
END;
/
Un fragmento de lo impreso en pantalla es dado por la siguiente imagen:
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
PROCEDIMIENTO ALMACENADO:
Trabajando con el modelo de HR, automatizar una consulta que calcule el número total de
diferentes trabajos y tiempo en meses que ha tenido un empleado en el transcurso de su
existencia en la empresa, incluyendo el que tiene actualmente.
Rta/
CREATE OR REPLACE PROCEDURE total_trabajos(p_id_empleado IN
employees.employee_id%TYPE,
p_no_trabajos OUT NUMBER,
p_no_meses OUT NUMBER)
IS
BEGIN
--Al contador es necesario sumarle 1, que representa el trabajo actual
SELECT count(*) + 1 INTO p_no_trabajos
FROM job_history j, employees e
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.
WHERE e.employee_id = p_id_empleado
AND j.employee_id = e.employee_id
AND NOT e.job_id = j.job_id;
SELECT round(months_between(sysdate, hire_date),0) INTO p_no_meses
FROM employees
WHERE employee_id=p_id_empleado;
END total_trabajos;
/
Se definen dos variables con el fin de poder imprimir en estas el resultado del
procedimiento, posterior a esto se hace uso del comando PRINT para mostrar los cálculos;
en la prueba mostrada a continuación se uso el empleado de código 100, el cual en el cual
en el tiempo que lleva trabajando (285 meses) solo ha tenido un trabajo.
Prohibida su copia o reproducción. Todos los Derechos Reservados. JSGS.