Procedimientos

Un procedimiento es un bloque PL/SQL con nombre que realiza una accion. Un procedimiento puede estar almacenado en la base de datos, como un objeto mas de la BD para ser ejecutado multiples veces.

(*) la sintaxis para MySql la puedes consultar en la docuementación de MySql o en esta copia local

CREATE [ OR REPLACE ] PROCEDURE nombre_de_procedimiento
  ( argumento 1 ,
    argumento 2,
    ... )
IS [AS]
PL/SQL Block;

CREATE OR REPLACE PROCEDURE subir_el_sueldo
(v_id in employees.employee_id%TYPE)
IS
BEGIN
	UPDATE employees
	SET salary = salary * 1.2
	WHERE  employee_id = v_id;
END subir_el_sueldo;
/

y para ejecutarlo… execute subir_el_sueldo(200)

Vamos a ver como va la cosa…. Primero se debe crear el procedimiento siguiendo la sintaxis normal. CREATE (OR REPLACE para que no se queje si lo creamos varias veces) PROCEDURE y le pones un nombre. A continuación defines las variables que acepta (v_id in employees.employee_id%TYPE) y finalmente la sentencia SQL. Una vez que se ha creado el procedimiento, o bien picandolo directamente en la consola sql o bien guardandolo en un fichero .sql y ejecutándolo después. (@fichero.sql) Una vez se ha grabado el procedimiento en la el servidor de Oracle se puede ejecutar tantas veces como se quiera simplemente invocandolo execute subir_el_sueldo(200)

Se pueden pasar parametros a los procedimientos.

• IN (por defecto) pasar un valor constante desde el entorno al procedimiento (es la que hemos usado en el ejemplo anterior) (entrada).
• OUT pasa un valor desde el procedimiento a el entorno (salida).
• IN PUT Pasa un valor desde el entorno y un posible valor diferente desde el procedimiento a el entorno usando el mismo parametro.

CREATE OR REPLACE PROCEDURE consulta_sobre_empleados
(v_id IN employees.employee_id%TYPE,
 v_nombre OUT  employees.first_name%TYPE,
 v_salario OUT  employees.salary%TYPE,
 v_comision OUT  employees.commission_pct%TYPE)
IS
BEGIN
	SELECT first_name, salary, commission_pct
INTO v_nombre, v_salario, v_comision
FROM employees
WHERE employee_id = v_id;

END consulta_sobre_empleados;
/

Pero no podemos ejecutar tan rápido. Al usar variables de salida, debemos tener variables preparadas para recoger la salida. Por lo que en el entorno de ejecución (la consola SQL o lo que sea) debemos crearlas:

 SQL> variable g_n varchar2(15)
 SQL> variable g_s number
 SQL> variable g_c number

Y ahora ya podemos ejecutar:

SQL>  execute consulta_sobre_empleados(200, :g_n, :g_s, :g_c)

Con esto le hemos pasado 200, y las tres variables, g_n, g_s y g_c para que las rellene.Si lo quieres comprobar mira el contenido de cualquiera de ellas:

SQL> print g_n
G_N
--------------------------------
Jennifer

Este ejemplo va a ser un poquito más complejo y funcional. Primero creamos el procedimiento:

CREATE OR REPLACE PROCEDURE formatea_tlf
(v_tlf IN OUT varchar2)
IS
BEGIN
	v_tlf := '(' || SUBSTR(v_tlf, 1, 3) ||
               ')' || SUBSTR(v_tlf, 4, 3) ||
	 '-' || SUBSTR(v_tlf, 7);

END  formatea_tlf;
/

Y ahora podemos creamos un bloque pl/sql que lo va a usar…

variable v_tlf varchar2(15)
BEGIN
	:v_tlf := '8003387532';

END;
/
EXECUTE formatea_tlf(:v_tlf)
/

si ahora hacemos un print de v_tlf nos dará el tlf formateado:

SQL> print v_tlf;
V_TLF
--------------------------------
(800)338-7532

• Por Posición Lista de valores en el orden en que se declaran las variables
• Por Nombre Lista de valores en orden arbitrario mediante la asociación de cada uno con su nombre (nombre_de_variable⇒valor)
• Combinación Una convinación de ambas

Primero creo una secuencia que empieza por el primer número libre después de último id para poder usarla posteriormente en el procedimiento.

CREATE SEQUENCE departments_sec
INCREMENT BY 10
START WITH 270
NOCYCLE;

Ahora creo un procedimiento para insertar departamentos

CREATE OR REPLACE PROCEDURE add_dept
	(v_nombre IN departments.department_name%TYPE,
	 v_loc    IN departments.location_id%TYPE) 
IS
	BEGIN
		 INSERT INTO  departments (department_id, department_name, location_id)
		 VALUES (departments_sec.nextval, v_nombre, v_loc);
	END add_dept;
/

Y por último lo uso….

	BEGIN	
 --  	add_dept;    ( este no funcionaría )
	add_dept( 'nombre_de_departento', 1200); 
	add_dept( v_nombre => 'otro wmas', v_loc=> 1200);
--  	add_dept( v_loc => 2912);   ( este tampoco )
	END;
/

DROP PROCEDURE nombre_de_procedimiento