learning-PLSQL Oracle 12c

Estructura de Bloque PL/SQL.
Tipos de Bloque
Declarando Variables PL/SQL

Estructura de Bloque PL/SQL

DECLARE (opcional)
– Variables, cursores, excepciones definida por usuario
BEGIN (mandatorio)
– Setencias SQL
– Sentencias PL/SQL
EXCEPTION (opcional)
– Acciones a desarrollar cuando ocurre una excepción
END; (mandatorio)

Tipos de Bloque

Anónimo

  [DECLARE]
  BEGIN
    --sentencias
  [EXCEPTION]
  END;

Procedimiento

  PROCEDURE nombre
  IS
  BEGIN
    --sentencias
  [EXCEPTION]
  END;

Función

  FUNCTION nombre
  RETURN tipoDato
  IS
  BEGIN
    --sentencias
    RETURN valor;
  [EXCEPTION]
  END;

Ejemplo Bloque Anonimo:

-- Para habilitar la salida en SQL Developer
SET SERVEROUTPUT ON;

DECLARE
  v_fname VARCHAR(20);
BEGIN
  SELECT  first_name INTO v_fname
  FROM    employees
  WHERE   employee_id=100;

--paquete predefinido Oracle (DBMS_OUTPUT)y su procedimiento (PUT_LINE) 
  DBMS_OUTPUT.PUT_LINE('El primer nombre del empleado es ' || v_fname);
END;

Resultado:

  Procedimiento PL/SQL terminado correctamente.
  El primer nombre del empleado es Steven

Declarando Variables PL/SQL

Sintaxis

identifier  [CONSTANT]  datatype  [NOT NULL] [:= | DEFAULT expr];

Ejemplos:

DECLARE 
  v_myname  varchar(20);
  v_mycity  varchar(20) := 'San Martin';
BEGIN
  v_myname := 'Edwin';
  DBMS_OUTPUT.PUT_LINE('Mi nombre es: ' || v_myname);
  DBMS_OUTPUT.PUT_LINE('Vivo en: ' || v_mycity);
END;

Resultado:

  Procedimiento PL/SQL terminado correctamente.
  Mi nombre es: Edwin
  Vivo en: San Martin

Tipos de Variables

PL/SQL variables
- Escalar: Sostiene un solo valor, no tiene componentes internos (Boolean, text, integer, date, varchar, char, binary_float, bynary_double, pls_integer, binary_integer, timestamp, interval)
- Referencia:
- Objetos largos(LOB: Oracle no va a tratar de interpretarlo, solo lo almacena):
  - Libros: CLOB,
  - Photo: BLOB,
  - MOVIE: BFILE (Se almacena fuera de la base de datos),
  - Caracteres especiales: NCLOB
- Compuestos:
  - Colecciones,
  - Registro
Variables No-PL/SQL:
- Bind variables (enlazadas) o lenguaje principal

Convención de nombres de variables

Estructura PL/SQL	Convención	Ejemplo
Variable v_variable_name v_rate	v_variable_name	v_rate
Constante c_constant_name c_rate	c_constant_name	c_rate
Parámetro de subprograma	p_parameter_name	p_id
Bind (host) variable	b_bind_name	b_salary
Cursor	cur_cursor_name	cur_emp
Registro	rec_record_name	rec_emp
Tipo	type_name_type	ename_table_type
Excepción	e_exception_name	e_products_invalid
Archivo	f_file_handle_name	f_file

Declarando variables con el atributo %type

DECLARE
  v_emp_lname employees.last_name%TYPE;
BEGIN
  SELECT last_name INTO v_emp_lname
  FROM  employees
  WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE('Mi apellido es: ' || v_emp_lname);
END;

Resultado:

  Procedimiento PL/SQL terminado correctamente.
  Mi apellido es: King

Variables Bind o enlazadas

VARIABLE b_emp_salary NUMBER;

BEGIN
  SELECT  salary  INTO  :b_emp_salary
  FROM    employees
  WHERE   employee_id=100;
END;
/
PRINT b_emp_salary;

SELECT  first_name
FROM    employees
WHERE salary = :b_emp_salary;

Resultado:

  Procedimiento PL/SQL terminado correctamente.
  
  B_EMP_SALARY
  ------------
  24000
  
  FIRST_NAME         
  --------------------
  Steven

AUTOPRINT con BIND Variables

VARIABLE b_emp_salary NUMBER
SET AUTOPRINT ON

DECLARE
  v_empno NUMBER(6) := &empno;
BEGIN
  SELECT salary into :b_emp_salary
  FROM employees
  WHERE employee_id = v_empno;
END;

Resultado:

  Antiguo:DECLARE
  v_empno NUMBER(6) := &empno;
  BEGIN
    SELECT salary into :b_emp_salary
    FROM employees
    WHERE employee_id = v_empno;
  END;
  
  Nuevo:DECLARE
    v_empno NUMBER(6) := 140;
  BEGIN
    SELECT salary into :b_emp_salary
    FROM employees
    WHERE employee_id = v_empno;
  END;
  
  Procedimiento PL/SQL terminado correctamente.
  B_EMP_SALARY
  ----
  2500

Escritura de Sentencias SQL Ejecutables

Antes de todo, Habilitamos la salida en SQL Developer

SET SERVEROUTPUT ON;

Uso de Comentarios y Funciones de una sola fila

DECLARE 
    --declaramos variable de salario anual
    v_salario_anual   NUMBER(9, 2);
    v_last_name       employees.last_name%TYPE;
    v_hire_date       employees.hire_date%TYPE;
BEGIN
    /* asignamos valor de salario anual: se consulta el 
       salario mensual y se multiplica por 12
     */
    SELECT salary * 12, last_name, hire_date
    INTO v_salario_anual, v_last_name, v_hire_date
    FROM employees
    WHERE employee_id = 100;
    
    --se imprime variable de salario anual

    dbms_output.put_line('Salario anual: ' || v_salario_anual);
    
    /* uso de variable de una sola fila: 
       mostrar la cantidad de caracteres de last_name
    */
    
    dbms_output.put_line('Tamano de last_name: '
                         || v_last_name
                         || ' '
                         || length(v_last_name));
    
    /* uso de variable de una sola fila:
       para determinar la cantidad de dias desde su contratación
    */

    dbms_output.put_line('cantidad de dias desde contratacion: ' 
                         || months_between(sysdate, v_hire_date));
END;
/

Resultado:

    Salario anual: 288000
    Tamano de last_name: King 4
    cantidad de dias desde contratacion: 203.67

    Procedimiento PL/SQL terminado correctamente.

Bloques anidados

DECLARE
    v_externa VARCHAR2(20) := 'VARIABLE EXTERNA';
BEGIN
    DECLARE
        v_interna VARCHAR2(20) := 'VARIABLE INTERNA';
    BEGIN
        dbms_output.put_line(v_externa);
        dbms_output.put_line(v_interna);
    END;

    dbms_output.put_line(v_externa);
    -- DBMS_OUTPUT.PUT_LINE(v_interna); variable interna no se encuentra en el alcance
END;

Resultados: 

    VARIABLE EXTERNA
    VARIABLE INTERNA
    VARIABLE EXTERNA

    Procedimiento PL/SQL terminado correctamente.

Nombramientos por bloque

Son cualificadores o etiquetas que permiten identificar explicitamente de que ambito se encuentra la variable a la que se hace referencia

BEGIN <<externo>>
DECLARE
    v_variable VARCHAR2(20) := 'VARIABLE EXTERNA';
BEGIN
    DECLARE
        v_variable VARCHAR2(20) := 'VARIABLE INTERNA';
    BEGIN
        dbms_output.put_line(v_variable);
        --Si agregamos el nombramiento, se hace referencia no a la variable interna sino a la externa
        dbms_output.put_line(externo.v_variable);
    END;

    dbms_output.put_line(v_variable);
    -- DBMS_OUTPUT.PUT_LINE(v_interna); variable interna no se encuentra en el alcance
END;
END externo;

Resultados: 
    
    VARIABLE INTERNA
    VARIABLE EXTERNA
    VARIABLE EXTERNA

    Procedimiento PL/SQL terminado correctamente.

Uso de Sentencias SQL en un bloque PL/SQL

SET SERVEROUTPUT ON;

Utilizando PL/SQL para la Manipulación de datos. Hacer cambios en las tablas de bases de datos mediante el uso de comandos DML:

INSERT
UPDATE
DELETE
MERGE

INSERT

BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
    VALUES(employees_seq.NEXTVAL, 'Edwin', 'Palacios', 'edwin@gmail.com', CURRENT_DATE, 'AD_ASST', 4000);
END;
/

UPDATE

Declare 
    v_incremento_salario    employees.salary%type := 800;
Begin
    Update employees 
    set salary = salary + v_incremento_salario
    Where job_id = 'ST_CLERK';
End;
/

Nota: Como se puede observar el SQL se trabaja de forma igual, Asimismo con eliminar, merge, commit, rollback y savepoint

Cursor SQL

Un cursor es un puntero a la zona de memoria privada asignada por el servidor Oracle. Se utiliza para manejar el conjunto de resultados de una sentencia SELECT

Hay dos tipos de cursores: implícitos y explícitos.

– Implícito: Creado y gestionado internamente por el servidor Oracle para procesar sentencias SQL.

– Explícita: Declarado explícitamente por el programador.

Atributos de Cursor

Atributo	Descripción
SQL%FOUND	Atributo booleano que se evalúa como TRUE si la última sentencia SQL afectó al menos una fila
SQL%NOTFOUND	Atributo booleano que se evalúa como TRUE si la última sentencia SQL no afectó ni una fila
SQL%ROWCOUNT	Un valor entero que representa el número de filas afectadas por la última sentencia SQL

Ejemplo de ROWCOUNT

SET SERVEROUTPUT ON;
Declare 
    v_disminucion_salario    employees.salary%type := 800;
Begin
    Update employees 
    set salary = salary - v_disminucion_salario
    Where job_id = 'ST_CLERK';
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' filas afectadas');
End;

Estructuras de Control en bloque PLSQL

SET SERVEROUTPUT ON;

IF - THEN - ELSIF - ELSE - END IF

Sintaxis

    IF  condition THEN
        statements;
    [ELSIF  condition THEN
        statements;]
    [ELSE
        statements;]
    END IF;

Ejemplo

DECLARE
    v_edad  number  := 185;
BEGIN
    IF (v_edad < 18) THEN
    DBMS_OUTPUT.PUT_LINE('Menor de edad');
    ELSIF (v_edad < 60 AND v_edad >= 18) THEN
    DBMS_OUTPUT.PUT_LINE('Mayor de edad');
    ELSIF (v_edad < 100 AND v_edad >= 60) THEN
    DBMS_OUTPUT.PUT_LINE('Adulto mayor');
    ELSE
    DBMS_OUTPUT.PUT_LINE('Wow');
    END IF;
END;
/

Sentencias Case

Selecciona un resultado y lo devuelve Sintaxis

CASE selector
    WHEN expression1 THEN result1
    [WHEN expression2 THEN result2
    ...
    WHEN expressionN THEN resultN]
    [ELSE resultN+1]
END;

Ejemplo

DECLARE
   v_nota   number(9,2) := 8;
   v_valoracion varchar2(20);
BEGIN
    v_valoracion := CASE v_nota
                        WHEN 10 THEN 'Excelente'
                        WHEN 9  THEN 'Muy bueno'
                        WHEN 8  THEN 'Bueno'
                        ELSE 'Esfuerzate mas'
                    END;
    DBMS_OUTPUT.PUT_LINE('Valoracion obtenida mediante sentencia CASE: ' || v_valoracion);
END;
/

Expresiones CASE de busqueda

La cual ofrece una mejor libertad

DECLARE
   v_nota   number(9,2) := 7.5;
   v_valoracion varchar2(20);
BEGIN
    v_valoracion := CASE 
                        WHEN (v_nota >= 9 AND v_nota <= 10) THEN 'Excelente'
                        WHEN (v_nota >= 7 AND v_nota < 9)  THEN 'Muy bueno'
                        WHEN (v_nota >= 6 AND v_nota < 7)  THEN 'Bueno'
                        ELSE 'Esfuerzate mas'
                    END;
    DBMS_OUTPUT.PUT_LINE('Valoracion obtenida mediante expresiones CASE: ' || v_valoracion);
END;
/

Insertando SQL en case

En el siguiente ejercicio trata sobre un aumento de salario, si es el departamento de codigo 100, sus empleados aumentaran en un 10% su salario y los demás departamentos en un 5%

DECLARE
    v_department_id departments.department_id%type  := 100;
BEGIN
    CASE v_department_id
        WHEN  100    THEN   UPDATE employees
                            SET salary = salary*1.1
                            WHERE department_id = v_department_id;
        ELSE  UPDATE employees
              SET salary = salary*1.05
              WHERE department_id = v_department_id;
    END CASE;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' empleados beneficiados');
END;
/

Sentencias de bucle

LOOP

Sintaxis

    LOOP
        statements1;
        ...
        EXIT [WHEN condition];
    END LOOP;

Ejemplo

DECLARE
    v_count number:= 0;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Valor de contador en LOOP Basico: ' || v_count);
        -- incremento de contador
        v_count := v_count + 1;
        EXIT WHEN v_count > 4;
    END LOOP;
END;
/

Resultado: 

    Valor de contador en LOOP Basico: 0
    Valor de contador en LOOP Basico: 1
    Valor de contador en LOOP Basico: 2
    Valor de contador en LOOP Basico: 3
    Valor de contador en LOOP Basico: 4
    
    Procedimiento PL/SQL terminado correctamente.

While

Repite mientras la condición sea true

    WHILE condition
    LOOP
        statements1;
        statements2;
        ...
    END LOOP;

ejemplo

DECLARE
    v_count number:= 0;
BEGIN
    WHILE (v_count <= 4)
    LOOP
        DBMS_OUTPUT.PUT_LINE('Valor de contador en WHILE: ' || v_count);
        -- incremento de contador
        v_count := v_count + 1;
    END LOOP;
END;
/

Resultado: 

    Valor de contador en WHILE: 0
    Valor de contador en WHILE: 1
    Valor de contador en WHILE: 2
    Valor de contador en WHILE: 3
    Valor de contador en WHILE: 4
    
    Procedimiento PL/SQL terminado correctamente.

FOR

El contador (counter) se declara implicitamente

    FOR counter IN [REVERSE] lower_bound..upper_bound
    LOOP
        statements1;
        statements2;
        ...
    END LOOP;

BEGIN
    FOR i IN 0..4
    LOOP
        DBMS_OUTPUT.PUT_LINE('Valor de contador en FOR: ' || i);
    END LOOP;
END;
/

Resultado: 

    Valor de contador en FOR: 0
    Valor de contador en FOR: 1
    Valor de contador en FOR: 2
    Valor de contador en FOR: 3
    Valor de contador en FOR: 4
    
    Procedimiento PL/SQL terminado correctamente.

For REVERSE

Observar que el rango siempre se agrega de manera ascendete

BEGIN
    FOR i IN REVERSE 0..4
    LOOP
        DBMS_OUTPUT.PUT_LINE('Valor de contador en FOR ENVERSE: ' || i);
    END LOOP;
END;
/

Resultado:
    Valor de contador en FOR ENVERSE: 4
    Valor de contador en FOR ENVERSE: 3
    Valor de contador en FOR ENVERSE: 2
    Valor de contador en FOR ENVERSE: 1
    Valor de contador en FOR ENVERSE: 0
    
    Procedimiento PL/SQL terminado correctamente.

LOOPS anidados y etiquetas

ejemplo

DECLARE
    v_contador_externo  number  := 0;
    v_contador_interno  number  := 0;
BEGIN
    <<loop_externo>>
    LOOP
        v_contador_interno := 0;
        
        <<loop_interno>>
        LOOP
            DBMS_OUTPUT.PUT_LINE(v_contador_externo || v_contador_interno);
            v_contador_interno := v_contador_interno +1;
            EXIT loop_interno WHEN v_contador_interno > 2;
        END LOOP loop_interno;
        
        v_contador_externo := v_contador_externo +1;
        EXIT loop_externo WHEN v_contador_externo > 2;
        
    END LOOP loop_externo;
END;

Resultado: 

    00
    01
    02
    10
    11
    12
    20
    21
    22
    
    Procedimiento PL/SQL terminado correctamente.

Tipos de datos compuestos

SET SERVEROUTPUT ON;

Registros

DECLARE
    -- Registro
    TYPE t_rec IS RECORD(
        v_sal       number(8),
        v_minsal    number(8) DEFAULT 1000,
        v_hire_date employees.hire_date%type,
        v_rec1      employees%rowtype -- %rowtype permite definir un elemento con la estructura de la tabla 
        );
    
    v_myrec t_rec; -- Se usa el identificador v_myrec para t_rec
BEGIN
    -- ejemplo de como se puede manipular los elementos del registro
    v_myrec.v_sal := v_myrec.v_minsal + 500;
    
    -- ejemplo de como obtener una fila completa
    SELECT * INTO v_myrec.v_rec1
    FROM employees
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE('V_SAL: ' || v_myrec.v_sal);
    DBMS_OUTPUT.PUT_LINE('Salario real através de V_REC1: ' 
                            || v_myrec.v_rec1.salary 
                            || ' del empleado: '
                            || v_myrec.v_rec1.first_name);
END;
/

Resultado: 
    V_SAL: 1500
    Salario real através de V_REC1: 24000 del empleado: Steven
    
    Procedimiento PL/SQL terminado correctamente.

Arreglos Asociados (llave, valor)

El valor bien puede ser escalar, creado bajo la deficion de una columna (%type) o mediante la definicion de una tabla o record (%rowtype)

DECLARE
    -- declaración de arreglo asociado de tipo igual a la estructura de la columna LAST_NAME
    TYPE ename_table_type IS TABLE OF employees.last_name%type
        INDEX BY PLS_INTEGER; -- PLS_INTEGER | BINARY_INTEGER
    
    -- declaración de identificador
    ename_table ename_table_type;
    
BEGIN
    --asignamos al arreglo el elemento con index 1
    ename_table(1)  := 'PALACIOS';
    
    IF (ename_table.EXISTS(1)) THEN
        DBMS_OUTPUT.PUT_LINE(ename_table(1));
    END IF;
    
END;
/

Además de EXISTS, los metodos que se pueden utilizar son: EXISTS, COUNT, FIRST, LAST, PRIOR (el previo), NEXT, DELETE

VARRAY

Array variables, son de longitud fija

DECLARE
    --Declaramos VARRAY
    TYPE emp_array IS VARRAY(100) OF VARCHAR2(30);
    
    --identificador
    emps emp_array;
BEGIN
    -- agregamos elementos al VARRAY
    emps := emp_array('Scrum', 'XP');
    
    --agregar el espacio de un elemento mas
    emps.extend;
    -- agregamos elemento
    emps(emps.LAST) := 'Kanba';
    
    -- imprimimos elemento
    FOR indice IN emps.FIRST..emps.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE(emps(indice));
    END LOOP;
END;
/

Resultados:
Scrum
XP
Kanba

Procedimiento PL/SQL terminado correctamente.

VARRAY en SQL

Create or replace TYPE emp_array AS VARRAY(100) OF VARCHAR2(30);
Describe emp_array;


    Resultado:
    
    Type EMP_ARRAY compilado
    
    Nombre    ¿Nulo? Tipo                        
    --------- ------ --------------------------- 
    EMP_ARRAY        VARRAY(100) OF VARCHAR2(30)

Nota: teniendo este VARRAY se puede utilizar como cualquier tipo de dato en ORACLE

Consulta de VARRAY en SQL

Select name 
From club
Where 'Gen' IN (SELECT * From table(club.members));

Donde members es un campo en la tabla club de tipo VARRAY

Cursores Explicitos

SET SERVEROUTPUT ON;

Sintaxis

    CURSOR cursor_name IS
        select_estatements:

Ejemplo

DECLARE
    -- 1. declara cursor
    CURSOR c_emp_cursor IS
        SELECT employee_id, last_name
        FROM employees
        WHERE department_id = 30;
        
    v_emp_id            employees.employee_id%type;
    v_emp_last_name     employees.last_name%type;
    
BEGIN
    -- 2. abrir el cursos
    OPEN c_emp_cursor;
    
    LOOP
        -- 3. fetch, asignar valores dentro de el fetch a variables
        FETCH c_emp_cursor INTO v_emp_id, v_emp_last_name;
        DBMS_OUTPUT.PUT_LINE(v_emp_id || ' ' ||v_emp_last_name);
        
        -- 4. se itera el cursor hasta que ya no exista más tuplas usando la propiedad %NOTFOUND
        EXIT WHEN c_emp_cursor%NOTFOUND;
        
    END LOOP;
    
    -- 5. Close Se cierra el cursor
    CLOSE c_emp_cursor;
END;
/

Cursores y registros

En este caso se crea un variable record con la estructura del cursor

DECLARE
    -- 1. declara cursor
    CURSOR c_emp_cursor IS
        SELECT employee_id, last_name
        FROM employees
        WHERE department_id = 30;
        
    -- declaración de variable record con la estructura del cursor  
    v_emp_record    c_emp_cursor%rowtype;
    
BEGIN
    -- 2. abrir el cursos
    OPEN c_emp_cursor;
    
    LOOP
        -- 3. fetch, asignar valores dentro de el fetch a variables
        FETCH c_emp_cursor INTO v_emp_record;
        DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id 
                             || ' ' 
                             ||v_emp_record.last_name);
        
        -- 4. se itera el cursor hasta que ya no exista más tuplas usando la propiedad %NOTFOUND
        EXIT WHEN c_emp_cursor%NOTFOUND;
        
    END LOOP;
    
    -- 5. Close Se cierra el cursor
    CLOSE c_emp_cursor;
END;
/

Cursor con bucle FOR (RECOMENDADO)

Se recomienda esta practica, dado que:

El cursor bucle FOR es un atajo para procesar cursores explicitos.
se evita hacer: OPEN, FETCH, EXIT Y CLOSE.
el registro es implicitamente declarado.

DECLARE
    -- 1. declara cursor
    CURSOR c_emp_cursor IS
        SELECT employee_id, last_name
        FROM employees
        WHERE department_id = 30;
    
BEGIN
    -- 2. Se itera el cursor asignando al record v_emp_record (creado implícitamente)
    FOR v_emp_record IN c_emp_cursor
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id 
                             || ' ' 
                             ||v_emp_record.last_name);        
    END LOOP;
END;
/

Resultado de los tres ejemplos anteriores: 

114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
119 Colmenares

Procedimiento PL/SQL terminado correctamente.

Atributos de cursores

Atributo	Tipo	Descripción
%ISOPEN	Boolean	Evalua a TRUE si el cursor esta abierto
%NOTFOUND	Boolean	Evalua a TRUE si la más reciente recuperación no retorna una fila
%FOUND	Boolean	Evalua a TRUE si la más reciente recuperación retorna una fila; complemento de %NOTFOUND
%ROWCOUNT	Number	Evalua el número de filas que han sido recuperadas.

CURSOR FOR LOOPS utilizando Subconsultas

De esta manera se evita declarar el cursor

BEGIN
    FOR emp_record IN ( SELECT employee_id, last_name
                        FROM employees
                        WHERE department_id = 30) -- observar que no se utiliza ; 
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp_record.employee_id 
                             || ' ' 
                             ||emp_record.last_name);   
    END LOOP;
    
END;
/

Resultado:
    114 Raphaely
    115 Khoo
    116 Baida
    117 Tobias
    118 Himuro
    119 Colmenares
    
    Procedimiento PL/SQL terminado correctamente.

Cursor con parametros

DECLARE
    CURSOR c_emp_cursor (deptno NUMBER) IS  SELECT employee_id, last_name
                                            FROM employees
                                            WHERE department_id = deptno;
BEGIN

    DBMS_OUTPUT.PUT_LINE('Empleados del deptno 10');
    FOR r_emp_record IN c_emp_cursor(10)
    LOOP
        DBMS_OUTPUT.PUT_LINE(r_emp_record.employee_id 
                         || ' ' 
                         ||r_emp_record.last_name);                    
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Empleados del deptno 20');
    
    
    FOR r_emp_record IN c_emp_cursor(20)
    LOOP
        DBMS_OUTPUT.PUT_LINE(r_emp_record.employee_id 
                         || ' ' 
                         ||r_emp_record.last_name);                    
    END LOOP;
    
END;

Resultado:

    Empleados del deptno 10
    200 Whalen
    Empleados del deptno 20
    201 Hartstein
    202 Fay
    
    Procedimiento PL/SQL terminado correctamente.

Clausula For Update

Bloqueo explicito para denegar el acceso a otras sesiones durante el tiempo de transacción

Select *
From employees
For Update [OF column_reference] [NOWAIT | WAIT n];

Clausula WHERE CURRENT OF

Utilizado para referenciar la fila actual desde un cursor explicito con el objetivo de editar o eliminar una fila a través del cursor.

Sintaxis:

WHERE CURRENT OF cursor_name;

ejemplo:

UPDATE  employees
    SET salary = ...
    WHERE CURRENT OF c_emp_cursor;

Recomendaciones con cursores: Utilizar FOR UPDATE en las filas que se desea bloquear o borrar, esto con el objetivo de bloquear el acceso mediante otras sesiones permitiendo que la información se encuentre consistente

Excepciones

Sintaxis para atrapar excepciones

EXCEPTION
    WHEN exception1 [OR exception2 ... ] THEN
        statements1;
        statements2;
        ...
    [WHEN exception3 [OR exception4 ... ] THEN
        statements1;
        statements2;
        ...]        
    [WHEN OTHERS THEN
        statements1;
        statements2;
        ...]

Excepciones predefinidas de ejemplo:

NO_DATA_FOUND : no encuentra datos en la consulta.
TOO_MANY_ROWS : devuelve mas de las filas esperadas.
INVALID_CURSOR : Cursor invalido.
ZERO_DIVIDE : division entre cero.
DUP_VAL_ON_INDEX: Cuando se quiere duplicar una llave primaria.

Excepciones No predefinidas

Ejemplo: se desea atrapar la excepcion de querer registrar un valor null

DECLARE
    e_insert_exception  EXCEPTION; -- declaracion de exception
    PRAGMA EXCEPTION_INIT(e_insert_exception, -01400); -- se coloca el codigo del servidor oracle cuando no se pueden editar valores nulos
BEGIN
    INSERT INTO departments (department_id, department_name)
    VALUES (280, NULL);
EXCEPTION
    WHEN e_insert_exception THEN -- referencia de la exception en el when
    DBMS_OUTPUT.PUT_LINE('OPERACION DE INSERCION FALLIDA');
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Resultado:
    OPERACION DE INSERCION FALLIDA
    ORA-01400: no se puede realizar una inserción NULL en ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")
    
    Procedimiento PL/SQL terminado correctamente.

Funciones para atrapar Errores

SQLCODE: Devuelve el valor numérico para el código de error.
SQLERRM: Devuelve el mensaje asociado con el número de error.

...
EXCEPTION
   ...
   WHEN OTHERS THEN
   error_code := SQLCODE;
   error_message := SQLRRM;
   ...
END;

Atrapando funciones definidas por el usuario (RAISE)

Utilizando RAISE se pueden atrapar excepciones definidas por el usuario.

- Detiene la ejecución normal de un bloque PL / SQL o subprograma y transfiere el control a un controlador de excepciones.

- Explícitamente plantea excepciones predefinidas o excepciones definidas por el usuario.

En el siguiente ejemplo se dispara una excepcion para indicar que el usuario debe ser mayor de edad.

DECLARE
    v_edad  NUMBER := 27;
    e_restriccion_edad_exception    EXCEPTION; 
BEGIN
    IF v_edad > 18 THEN
        DBMS_OUTPUT.PUT_LINE('Bienvenid@');
    ELSE
        RAISE e_restriccion_edad_exception; -- activamos la excepcion de restriccion de edad
    END IF;
EXCEPTION
    WHEN e_restriccion_edad_exception THEN
       DBMS_OUTPUT.PUT_LINE('Acceso denegado, debes ser mayor de edad'); 
END;
/

Procedure RAISE_APPLICATION_ERROR

Puede usar este procedimiento para emitir mensajes de error definidos por el usuario de subprogramas almacenados. Puede reportar errores en su aplicación y evitar volver excepciones no controladas.

Se utiliza en dos lugares diferentes:

  – sección ejecutable.
  
  – sección de excepción.

Devuelve condiciones de error al usuario de una manera consistente con otros errores de Oracle Server

Sintaxis

raise_application_error (error_number,
    message[, {TRUE | FALSE}]);

Ejemplo: utilizando en el BEGIN

DECLARE
    v_edad  NUMBER := 17;
BEGIN
    IF v_edad > 18 THEN
        DBMS_OUTPUT.PUT_LINE('Bienvenid@');
    ELSE
        RAISE_APPLICATION_ERROR(-20001, 'Acceso denegado, debes ser mayor de edad'); -- excepcion de restriccion de edad
    END IF;
END;
/

Ejemplo: utilizando en el exception

DECLARE
    v_edad  NUMBER := 17;
    e_restriccion_edad_exception    EXCEPTION; 
BEGIN
    IF v_edad > 18 THEN
        DBMS_OUTPUT.PUT_LINE('Bienvenid@');
    ELSE
        RAISE e_restriccion_edad_exception; -- activamos la excepcion de restriccion de edad
    END IF;
EXCEPTION
    WHEN e_restriccion_edad_exception THEN
       RAISE_APPLICATION_ERROR(-20001, 'Acceso denegado, debes ser mayor de edad'); -- excepcion de restriccion de edad 
END;
/

Nota: El parámetro final pasado al procedimiento es un Boolean(true/false) que indica al procedimiento que agregue este error a la pila de errores o reemplace todos los errores de la pila con este error. Pasar el valor de 'True' agrega el error a la pila actual, mientras que el valor predeterminado es 'False'.

Procedimientos

Sintaxis

    CREATE [OR REPLACE] PROCEDURE procedure_name
        [(argument1 [mode1] datatype1,
          argument2 [mode2] datatype2,
        . . .)]
    IS|AS
    procedure_body;

Ejemplo

creamos procedimiento

CREATE OR REPLACE PROCEDURE p_show_message
    (v_message  varchar2) -- parametros
    IS
    v_message_date  DATE := sysdate;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Message: ' 
                          || v_message 
                          || ' Fecha: ' 
                          || v_message_date);
END;
/

fin de creacion de procedimiento

invocamos procedimiento en un bloque anonimo y le pasamos por parametro

BEGIN
    p_show_message('Este es un mensaje creado mediante un storage procedure ');
END;

Resultado: 

    Procedure P_SHOW_MESSAGE compilado
    
    Message: Este es un mensaje creado mediante un storage procedure  Fecha: 08/06/20
    
    Procedimiento PL/SQL terminado correctamente.

Funciones

SET SERVEROUTPUT ON;

Sintaxis:

CREATE [OR REPLACE] FUNCTION function_name
    [(argument1 [mode1] datatype1,
    argument2 [mode2] datatype2,
    . . .)]
RETURN datatype
IS|AS
function_body;

Ejemplo: Crearemos un funcion la cual permita calcular el ISSS de un empleado

CREATE OR REPLACE FUNCTION calcular_isss
    (p_salary   employees.salary%TYPE) --parametro
    
    RETURN  employees.salary%TYPE -- return
    IS
    -- variables y/o constantes
    c_porcentaje_isss   CONSTANT NUMBER(9,2) := 0.03;               
    c_monto_maximo      CONSTANT employees.salary%TYPE := 1000;
    v_monto_isss        employees.salary%TYPE := 0;
BEGIN
    IF p_salary IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'El salario no es valido, tiene valor null');
    ELSE
        IF p_salary > c_monto_maximo THEN
            v_monto_isss := c_monto_maximo*c_porcentaje_isss;
        ELSE
            v_monto_isss := p_salary*c_porcentaje_isss;
        END IF;
    END IF;  
    RETURN v_monto_isss;
END;
/

Invocamos la funcion mediante el siguiente bloque anonimo

BEGIN
    dbms_output.put_line('El calculo del ISSS es: $' || calcular_isss(100));
END;

Name		Name	Last commit message	Last commit date
Latest commit History 11 Commits
Procedures project payment		Procedures project payment
Programación en PLSQL		Programación en PLSQL
README.md		README.md

EdwinPalacio5/learning-plsql

Folders and files

Latest commit

History

Repository files navigation

learning-PLSQL Oracle 12c

Estructura de Bloque PL/SQL

Tipos de Bloque

Declarando Variables PL/SQL

Tipos de Variables

Convención de nombres de variables

Declarando variables con el atributo %type

Variables Bind o enlazadas

AUTOPRINT con BIND Variables

Escritura de Sentencias SQL Ejecutables

Uso de Comentarios y Funciones de una sola fila

Bloques anidados

Nombramientos por bloque

Uso de Sentencias SQL en un bloque PL/SQL

INSERT

UPDATE

Cursor SQL

Atributos de Cursor

Estructuras de Control en bloque PLSQL

IF - THEN - ELSIF - ELSE - END IF

Sentencias Case

Expresiones CASE de busqueda

Insertando SQL en case

Sentencias de bucle

LOOP

While

FOR

For REVERSE

LOOPS anidados y etiquetas

Tipos de datos compuestos

Registros

Arreglos Asociados (llave, valor)

VARRAY

VARRAY en SQL

Consulta de VARRAY en SQL

Cursores Explicitos

Cursores y registros

Cursor con bucle FOR (RECOMENDADO)

Atributos de cursores

CURSOR FOR LOOPS utilizando Subconsultas

Cursor con parametros

Clausula For Update

Clausula WHERE CURRENT OF

Excepciones

Excepciones No predefinidas

Funciones para atrapar Errores

Atrapando funciones definidas por el usuario (RAISE)

Procedure RAISE_APPLICATION_ERROR

Procedimientos

Funciones

About

Topics

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Languages

Packages