PLSQL Básico1

Encontré varios ejemplos de hace varios años cuando aprendía plsql, allí les dejo algunos ejemplitos, ojalá les sirvan:

Ejemplo 1: Lo unico que hace es mostrar una linea en consola.

CREATE OR REPLACE PROCEDURE proc01 IS
BEGIN
  dbms_output.put_line('Hola mundo con los procedimientos de Oracle');
END;

Ejemplo 2: Igual que el anterior pero ahora la ultima línea finaliza con el nombre del procedimiento... buena practica!

CREATE OR REPLACE PROCEDURE proc02 IS
BEGIN
  dbms_output.put_line('Otro procedimiento pero ahora con otro texto');
END proc02;

Ejemplo 3: Uso de la clausula %TYPE y %ROWTYPE, la clausula %TYPE sirve para asignar los tipos de datos heredados de otras variables en tiempo de ejecución lo mismo para %ROWTYPE que actua a nivel de fila

CREATE OR REPLACE PROCEDURE proc03 IS
  v_fecha_sistema DATE;
  v_fecha_contratacion v_fecha_sistema%TYPE;
  v_salario employees.salary%TYPE;
  reg_empleados employees%ROWTYPE; 
BEGIN
  SELECT sysdate INTO v_fecha_sistema FROM dual;
  SELECT * INTO reg_empleados FROM employees WHERE employee_id=200;
  v_salario := reg_empleados.salary;
  v_fecha_contratacion := reg_empleados.hire_date;
  dbms_output.put_line('El nombre del empleado es:'||reg_empleados.first_name);
  dbms_output.put_line('El salario del empleado es:'||v_salario);
  dbms_output.put_line('La fecha de contratación del empleado es:'||v_fecha_contratacion);
  dbms_output.put_line('La fecha del sistema es:'||v_fecha_sistema);
END proc03;

Ejemplo 4: Muestra el uso de etiquetas para resolucion de nombres de variables, analiza el ámbito de la variable v_cantidad

CREATE OR REPLACE PROCEDURE proc04 IS
BEGIN
  <<ambito_externo>>
  DECLARE 
  v_cantidad NUMBER := 100;
  BEGIN
    DECLARE
    v_cantidad NUMBER := 150;
    BEGIN
    -- Inicio de un bloque de codigo interno
    dbms_output.put_line('Valor de la variable interna:'||v_cantidad);
    dbms_output.put_line('Valor de la variable externa:'||ambito_externo.v_cantidad);
    END;
    -- Aqui podemos continuar con los datos de la variable etiquetada externamente
    dbms_output.put_line('Valor de la variable:'||v_cantidad);
  END;
END proc04;

Ejemplo 5: Podemos crear funciones dentro del procedimiento y usarlas en el mismo procedimiento.

CREATE OR REPLACE PROCEDURE proc05 IS
  
  -- Funcion sumar:
  FUNCTION sumar(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1+ope2;
  END sumar;
  
  -- Funcion restar:
  FUNCTION restar(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1-ope2;
  END restar;
  
  -- Funcion multiplicar:
  FUNCTION multiplicar(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1*ope2;
  END multiplicar;
  
  -- Funcion dividir:
  FUNCTION dividir(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1/ope2;
  END dividir;
  
BEGIN
  dbms_output.put_line('Suma de 15 y 45: '||sumar(15,45));
  dbms_output.put_line('Resta de 15 y 45: '||restar(15,45));
  dbms_output.put_line('Multiplicacion de 15 y 45: '||multiplicar(15,45));
  dbms_output.put_line('Division de 15 y 45: '||dividir(15,45));
END proc05;

Ejemplo 6: Muestra el uso de las estructuras de control condicional IF-THEN e IF-THEN-ELSE

CREATE OR REPLACE
PROCEDURE proc06(vi_cantidad_minima IN INTEGER) IS
  v_sueldo_base NUMBER := 4000;
  v_cantidad_empleados INTEGER := 0;
BEGIN
  --Obtenemos la cantidad de empleados que tienen un sueldo mayor al sueldo base
  SELECT COUNT(employee_id) INTO v_cantidad_empleados FROM employees 
  WHERE salary > v_sueldo_base;
  
  -- Testeamos si la cantidad de empleados es mayor a una constante
  dbms_output.put_line('La cantidad de empleados es:'||v_cantidad_empleados); 
  dbms_output.put_line('-----------------------------------------------------'); 
  dbms_output.put_line('Uso de las estructura if-then'); 
  IF v_cantidad_empleados > vi_cantidad_minima THEN
    dbms_output.put_line('La cantidad de empleados:'||v_cantidad_empleados||', ES MAYOR a la minima');  
  END IF;
  
  dbms_output.put_line('Uso de las estructura if-then-else'); 
  IF v_cantidad_empleados > vi_cantidad_minima THEN
    dbms_output.put_line('La cantidad de empleados:'||v_cantidad_empleados||', ES MAYOR a la minima');  
  ELSE
    dbms_output.put_line('La cantidad de empleados:'||v_cantidad_empleados||', NO ES MAYOR a la minima');  
  END IF;
  
END proc06;

Ejemplo 7: Muestra el uso de las estructuras de control iterativas LOOP y EXIT

CREATE OR REPLACE
PROCEDURE proc07(vi_cantidad_iteraciones IN INTEGER) IS
  v_contador1 INTEGER := 0;
  v_contador2 INTEGER := 0;
BEGIN
  -- Primero con una declaración EXIT para salir de un bucle.
  dbms_output.put_line('Iteracion con clausula EXIT');
  LOOP
    IF v_contador1 = vi_cantidad_iteraciones THEN
      EXIT;
    ELSE
      dbms_output.put_line('Iterando con EXIT');    
    END IF;
    v_contador1 := v_contador1 + 1;
  END LOOP;
  
  --Ahora iteramos pero con la clausula EXIT WHEN
  v_contador1 := 0;
  dbms_output.put_line('Iteracion con la clausula EXIT WHEN');
  LOOP
  v_contador1 := v_contador1 + 1;
  dbms_output.put_line('Iterando con EXIT WHEN');    
    EXIT WHEN v_contador1 >= 20;
  END LOOP;
  
  --Ahora iteramos con la clausula EXIT WHEN con etiquetas
  v_contador1 := 0;
  v_contador2 := 0;
  dbms_output.put_line('Iteracion con la clausula EXIT WHEN CON ETIQUETAS');
  <<externo>>
  LOOP
    v_contador1 := v_contador1 + 1;
    dbms_output.put_line('Iterando con EXIT WHEN');    
    v_contador2 := 0;
    <<interno>>
    LOOP
     v_contador2 := v_contador2 + 1;
 dbms_output.put_line('Iterando con EXIT WHEN ANIDADO');
        EXIT externo WHEN (v_contador1 = 2 AND v_contador2 = 3);  
        EXIT interno WHEN v_contador2 >= 3;
    END LOOP;
    EXIT externo WHEN v_contador1 >= 3;
  END LOOP;
    
END proc07;

Ejemplo 8: Muestra el uso de estructuras de iteracion WHILE-LOOP-END LOOP con EXIT y etiquetas

CREATE OR REPLACE
PROCEDURE proc08(vi_cantidad_iteraciones IN INTEGER) IS
  v_contador1 INTEGER := 0;
  v_contador2 INTEGER := 0;
  v_contador3 INTEGER := 0;
BEGIN  
  --Ahora iteramos con la clausula EXIT WHEN con etiquetas
  v_contador1 := 0;
  v_contador2 := 0;
  dbms_output.put_line('Iteracion con la clausula EXIT WHEN CON ETIQUETAS');
  <<externo>>
  LOOP
    v_contador1 := v_contador1 + 1;
    dbms_output.put_line('Iterando con EXIT WHEN');    
    v_contador2 := 0;
    <<interno>>
    LOOP
     v_contador2 := v_contador2 + 1;
 dbms_output.put_line('Iterando con EXIT WHEN ANIDADO'); 
 v_contador3 := 0;
 WHILE (v_contador3 < 5 ) LOOP
   v_contador3 := v_contador3 + 1;
   dbms_output.put_line('Iterando con EXIT WHEN ANIDADO CON WHILE-LOOP-END LOOP');
 END LOOP;
        EXIT externo WHEN (v_contador1 = 2 AND v_contador2 = 3);  
        EXIT interno WHEN v_contador2 >= 3;
    END LOOP;
    EXIT externo WHEN v_contador1 >= 3;
  END LOOP;   
END proc08;