Procedimientos y Triggers

25 de noviembre de 2010 - 21:44 - Procedimientos y Triggers

Un procedimiento almacenado es un conjunto de instrucciones en PL/SQL, que pueden ser llamado usando el nombre que se le haya asignado.

La sintaxis para crear un procedimiento es la siguiente:

CREATE [OR REPLACE] PROCEDURE name [(param [IN|OUT|IN OUT|] datatype) . . .]
[IS|AS] pl/sql_subprogram

El uso de OR REPLACE permite sobreescribir un procedimiento existente. Si se omite, y el procedimiento ya existe, se producirá un error. Los modificadores IN, OUT, IN OUT indican si el parametro es de entrada, salida o ambos.

A continuación se presenta un ejemplo de creación de un procedimiento:

SQL> CREATE PROCEDURE credit (acc_no IN NUMBER, amount IN NUMBER)
1> AS BEGIN
2> UPDATE accounts
3> SET balance = balance + amount
4> WHERE account_id = acc_no;
5> END;

Este procedimiento actualizará la(s) tupla(s) con numero de cuenta igual al parámetro acc_no con un incremento de amount en el balance de dicha cuenta.

Si se desea eliminar (borrar) un procedimiento almacenado, se usa la instrucción:

SQL> DROP PROCEDURE name;

EJERCICIO:

cree una tapla imp con 3 columnas id_emp, dept_id, impuesto
cree un procedimiento para registrar el impuesto a pagar por los salarios devengados, teniendo en cuenta;
* si el salarios es menor a 2500 -> 5% impuesto
* si el salario esta entre 2500 y 3999 -> 6% de impuesto 
* si el salario esta entre 4000 y 8000 -> 8% de impuesto 
* si el salario esta entre 8000 y -> 10% de impuesto 
*/

drop table imp;
create table imp 
( id_emp number(6), dept_id number (4), impuesto number (10,8));

--------------
create or replace procedure imp_sal is
  cursor impuesto is 
    select employee_id, department_id, salary from employees; 

  v_imp number:= 0;
begin
    for imp_rec in impuesto loop
    if  imp_rec.salary  < 2500 then
      v_imp := (imp_rec.salary*0.5);
    end if;
    if  imp_rec.salary  >=2500 and imp_rec.salary <= 3999 then
      v_imp := (imp_rec.salary*0.6);
    end if;
    if  imp_rec.salary  >=4000 and imp_rec.salary <= 8000 then
      v_imp := (imp_rec.salary*0.8);
    end if;
    if  imp_rec.salary  > 8000 then
      v_imp := (imp_rec.salary*0.10);
    end if;
  insert into imp values (imp_rec.employee_id, imp_rec.department_id, v_imp); 
  end loop;
end imp_sal;
  
  
---LLAMADO

begin
imp_sal; 
end;

Procedimientos almacenados
    Un procedimiento es un subprograma que ejecuta una acción especifica y que no devuelve ningún valor. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código.
    La sintaxis de un procedimiento almacenado es la siguiente:
 

CREATE [OR REPLACE] 
PROCEDURE  [(  [IN|OUT|IN OUT] , 
                               [IN|OUT|IN OUT] , ...)] 
IS
  -- Declaracion de variables locales
BEGIN
  -- Sentencias
[EXCEPTION]
  -- Sentencias control de excepcion
END [];
     El uso de OR REPLACE permite sobreescribir un procedimiento existente. Si se omite, y el 
procedimiento existe, se producirá, un error.
    La sintaxis es muy parecida a la de un bloque anónimo, salvo porque se reemplaza la seccion DECLARE por la secuencia PROCEDURE ... IS en la especificación del procedimiento.
    Debemos especificar el tipo de datos de cada parámetro. Al especificar el tipo de dato del parámetro no debemos especificar la longitud del tipo.
    Los parámetros pueden ser de entrada (IN), de salida (OUT) o de entrada salida (IN OUT). El valor por defecto es IN, y se toma ese valor en caso de que no especifiquemos nada.
 
 
CREATE OR REPLACE 
PROCEDURE Actualiza_Saldo(cuenta NUMBER, 
                          new_saldo NUMBER)
IS
  -- Declaracion de variables locales
BEGIN
  -- Sentencias
  UPDATE SALDOS_CUENTAS 
	SET SALDO = new_saldo, 
            FX_ACTUALIZACION = SYSDATE
  WHERE CO_CUENTA = cuenta;

END Actualiza_Saldo; 
También podemos asignar un valor por defecto a los parámetros, utilizando la clausula DEFAULT
 o el operador de asiganción (:=) .
 
 
CREATE OR REPLACE 
PROCEDURE Actualiza_Saldo(cuenta NUMBER, 
                          new_saldo NUMBER DEFAULT 10 )
IS
  -- Declaracion de variables locales
BEGIN
  -- Sentencias
  UPDATE SALDOS_CUENTAS 
	SET SALDO = new_saldo, 
            FX_ACTUALIZACION = SYSDATE
  WHERE CO_CUENTA = cuenta;

END Actualiza_Saldo; 
 
    Una vez creado y compilado el procedimiento almacenado podemos ejecutarlo. Si el sistema nos indica que el procedimiento se ha creado con errores de compilación podemos ver estos errores de compilacion con la orden SHOW ERRORS en SQL *Plus.
    Existen dos formas de pasar argumentos a un procedimiento almacenado a la hora de ejecutarlo (en realidad es válido para cualquier subprograma). Estas son:
Notación posicional: Se pasan los valores de los parámetros en el mismo orden en que el procedure los define.
 
 
BEGIN
	Actualiza_Saldo(200501,2500);
        COMMIT;

END; 
 
Notación nominal:Se pasan los valores en cualquier orden nombrando explicitamente el parámetro.
 BEGINActualiza_Saldo(cuenta => 200501,new_saldo  => 2500);
 	COMMIT;

END;

TRIGGERS

Un trigger es un bloque PL/SQL asociado a una tabla, que se ejecuta cuando una determinada instrucción en SQL se va a ejecutar sobre dicha tabla.La sintaxis para crear un trigger es la siguiente:
CREATE [OR REPLACE] TRIGGER 
{BEFORE|AFTER} {DELETE|INSERT|UPDATE [OF col1, col2, . . ., colN]
[OR {DELETE|INSERT|UPDATE [OF col1, col2, . . ., colN]. . .]}
ON table
[REFERENCING OLD AS oldname, NEW as newname]
[FOR EACH ROW [WHEN (condition)]]
pl/sql_block
El uso de OR REPLACE permite sobreescribir un trigger existente. Si se omite, y el trigger existe, se producirá, un error.
El modificador FOR EACH ROW indica que el trigger se disparará cada vez que se desee hacer operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN, se establece una restricción; el trigger solo actuará, sobre las filas que satisfagan la restricción.
A continuación se presenta un ejemplo de creación de un trigger:
SQL> CREATE TRIGGER salary_check
1> BEFORE
2> INSERT OR UPDATE OF sal, job
3> ON employee
4> FOR EACH ROW
5> WHEN (new.job <> 'PRESIDENT')
6> DECLARE
7> minsal NUMBER
8> maxsal NUMBER
9> BEGIN
10> /* Se obtienen los valores minimo y maximo para el salario de */
11> /* un cargo determinado, usando la tabla sal_guide */
12> SELECT minsal, maxsal
13> INTO minsal, maxsal
14> FROM sal_guide
15> WHERE job = :new.job
16> /* Si el salario del empleado a insertar/modificar esta por */
17> /* debajo del minimo, o por encima del maximo, se genera */
19> /* un error. */
20> IF (:new.sal < minsal OR :new.sal > maxsal)
21> THEN raise_application_error(-20601, 'Salary '||:new.sal||
22> ' out of range for job '||:new.job||' for employee '||
23> :new.ename);
24> END IF;
25 > END;
Este trigger impide que se agregue o modifique un empleado con el sueldo mayor o menor que los valores maximo y minimo respectivamente para su cargo. Se agrega la restricción de que el trigger no se dispararán si el cargo es PRESIDENTE.
Si se desea eliminar (borrar) un trigger, se usa la instrucción:SQL> DROP TRIGGER name
 
                                                              EJERCICIOS RESUELTOS

create table Cuenta_emp 
(id_dept number (4), 
nombre_dept varchar2 (25),
num_emp number (4),
sal_prom number (20));


2.Cree un procedimiento para llenar la tabla recien creada, de tal forma que en los siguientes campos se guarde la siguiente información:
ID_DEPT: ID del departamento
NOMBRE_DEPT: Nombre del departamento
NUM_EMP: Numero de empleados en ese departamento
SAL_PROM: Promedio de salarios en ese departamento.

llenar esta unica tabla

set serveroutput on
create or replace procedure llenar 
(p_id cuenta_emp.id_dept%type,
p_nombre cuenta_emp.nombre_dept%type,
p_numero cuenta_emp.num_emp%type,
p_salario cuenta_emp.sal_prom%type)
is

begin
insert into cuenta_emp (id_dept, nombre_dept, num_emp, sal_prom)
values (p_id, p_nombre, p_numero, p_salario);
exception
when others then
dbms_output.put_line('Se produjo un error');
end llenar;

-------
Llenarla con datos de las tablas Employees y Departments

create or replace procedure llenar (p_id number)is
 
   cursor llenar is
   select d.department_id, d.department_name, count (e.employee_id), avg(e.salary)
   from employees e join departments d 
   on d.department_id = e.department_id
   where d.department_id = p_id
   group by d.department_id, d.department_name;
 
 v_ID_DEPT departments.department_id%TYPE;
 v_NOMBRE_DEPT departments.department_name%type;
 v_NUM_EMP number;
 v_SAL_PROM number;
 
   begin
   open llenar;
   fetch llenar into v_id_dept, v_nombre_dept, v_num_emp, v_sal_prom;
   insert into cuenta_emp values (v_id_dept, v_nombre_dept, v_num_emp, v_sal_prom);
   close llenar;
end;

Llamado al Procedimiento

SET SERVEROUTPUT ON
   DECLARE
   V_id_depto EMPLOYEES.DEPARTMENT_ID%TYPE:=0;  
   BEGIN
   V_id_depto:= &nume_dpto;
   llenar (V_id_depto);
   dbms_output.put_line ('se inserto '||v_id_depto);
end;

select * from cuenta_emp;

3. Realice un trigger de tal forma que se mantengan actualizados los datos en la tabla recien creada, de acuerdo a los posibles cambios que puedan presentarse en los registros de la tabla EMPLOYEES y DEPARTMENTS.

create or replace trigger actual
before update on cuenta_emp
for each row
begin
insert into cuenta_emp (id_dept, nombre_dept, num_emp, sal_prom)
values (:new.id_dept, :new.nombre_dept, :new.num_emp, :new.sal_prom);
end;

Procedimientos y Triggers

0 comentarios