Ver estos tutoriales:
http://plsql-tutorial.com/index.htm
http://www.desarrolloweb.com/articulos/introduccion-al-lenguaje-plsql.html
http://www.desarrolloweb.com/articulos/caracteristicas-plsql-I.html
http://www.desarrolloweb.com/articulos/caracteristicas-plsql-II.html
http://www.desarrolloweb.com/articulos/bloque-anonimo-procedimiento-plsql.html
http://www.desarrolloweb.com/articulos/fundamentos-lenguaje-plsql.html
http://www.desarrolloweb.com/articulos/subprogramas-plsql.html
http://www.desarrolloweb.com/articulos/cursores-pl-sql-I.html
http://www.desarrolloweb.com/articulos/cursores-pl-sql-II-parte.html
http://www.desarrolloweb.com/articulos/cursores-pl-sql-III-parte.html
http://www.desarrolloweb.com/articulos/excepciones-oracle.html
Para que este procedimiento funcione, vamos a tener que tener creada una tabla llamada SUPPLIER
"SUPPLIER_ID" "SUPPLIER_NAME" "CONTACT_NAME"
1 "nombre1" "contacto1"
2 "nombre2" "contacto2"
El PL es el siguiente:
create or replace
PROCEDURE HOLAMUNDO(
in_id_supplier IN VARCHAR2,
out_codigo_respuesta OUT VARCHAR2,
out_descripcion OUT VARCHAR2
) AS
----------------------------------------------------
-- Declaracion Constantes
----------------------------------------------------
-- Valor del proveedor
CONS_NOMRE_PROVEEDOR CONSTANT VARCHAR2(50) := 'nombre1';
----------------------------------------------------
-- Declara variables
----------------------------------------------------
var_supplier_name VARCHAR2(30);
var_supplier_contact VARCHAR2(30);
----------------------------------------------------
-- Declara cursores
----------------------------------------------------
CURSOR cursor_obtener_supplier(var_id NUMBER) IS
select s.supplier_name
from supplier s
where s.supplier_id = var_id;
----------------------------------------------------
-- Declara excepciones
----------------------------------------------------
SUPPLIER_NOT_FOUND EXCEPTION;
ID_SUPPLIER_ARGUMET_IS_INVALID EXCEPTION;
----------------------------------------------------
-- BODY
----------------------------------------------------
BEGIN
-- inicializamos las variables de retorno correctamente
out_codigo_respuesta :='0000';
out_descripcion:='El procedimiento de consulta de proveedores ha finalizado correctamente.';
-- Comprueba si se ha pasado el Id del Supplier
IF in_id_supplier IS NULL THEN
RAISE ID_SUPPLIER_ARGUMET_IS_INVALID;
END IF;
-- Obtiene el nombre del proveedor a partir del Id del proveedor ingresado por parametro
OPEN cursor_obtener_supplier(in_id_supplier);
FETCH cursor_obtener_supplier INTO var_supplier_name;
-- Verificamos q no sea null, en caso que sea null lanzamos una excepcion
IF (var_supplier_name IS NULL) THEN
CLOSE cursor_obtener_supplier;
RAISE SUPPLIER_NOT_FOUND;
END IF;
-- cerramos el cursor
CLOSE cursor_obtener_supplier;
-- Verificamos que el nombre sea igual al nombre definido en la constante
IF (var_supplier_name = CONS_NOMRE_PROVEEDOR) THEN
DBMS_OUTPUT.PUT_LINE ('El nombre del proveedor es ' || CONS_NOMRE_PROVEEDOR );
ELSE
DBMS_OUTPUT.PUT_LINE ('El nombre del proveedor no es ' || CONS_NOMRE_PROVEEDOR );
END IF;
-- Obtenemos el contacto del proveedor (otra forma de obtener sin cursor)
select s.contact_name
INTO var_supplier_contact
from supplier s
where s.supplier_id = in_id_supplier;
DBMS_OUTPUT.PUT_LINE ('El contacto del proveedor es ' || var_supplier_contact);
----------------------------------------------------
-- EXCEPCIONES
----------------------------------------------------
EXCEPTION
WHEN SUPPLIER_NOT_FOUND THEN
BEGIN
out_codigo_respuesta := '1200';
out_descripcion := 'No existe el proveedor para el ID ingresado: ' || in_id_supplier;
END;
WHEN ID_SUPPLIER_ARGUMET_IS_INVALID THEN
BEGIN
out_codigo_respuesta := '1200';
out_descripcion := 'Falta el ID de supplier en los parámetros de llamada.';
END;
END;
Luego vamos a poder ejecutar este PL, de la siguiente manera (mediante el comando RUN)
Luego aparecerá esta ventana en donde tendremos que ingresar el valor de los parametros de entrada, en este caso solo vamos a ingresar el id_supplier
Para crear un PL utilizamos la sentencia:
create or replace
PROCEDURE HOLAMUNDO(<parametros de entrada>...)
<BODY DEL PL>
END HOLAMUNDO;
Para esto necesitaremos permisos de OWNER.
En lugar de crear el PL, muchas veces necesitamos correrlo como script (utilizaremos las bondades de PL SQL pero no como un store procedure sino directamente para realizar una accion sobre la BD. Para esto en lugar de empezar con la sentencia:
create or replace
PROCEDURE HOLAMUNDO(<parametros de entrada>...)
<BODY DEL PL>
END HOLAMUNDO;
Utilizamos la siguiente sentencia:
DECLARE
<BODY DEL PL>
END;
De este forma, vamos a poder correr las sentencias SQLs sin necesidad de crear un SP ni de tener un usuario con privilegios OWN.
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
Ejemplo muy util utilizando PL, que recorre un SELECT y va actualizando. (de esta forma, no necesitamos crear el store procedure sino que directamente lo ejecutamos y listo)
Solo se debera reemplazar por las tablas que uno necesite, pero la idea es la misma:
DECLARE
----------------------------------------------------
-- VARIABLES
----------------------------------------------------
var_equip_name EQUIPMENT.NAME%TYPE;
var_snc_id SUBNETWORKCONNECTION.IDENTIFIER%TYPE;
CURSOR cursor_obtener_subnet(var_snc_id VARCHAR2) IS
SELECT snc.identifier, eq.name
FROM equipment eq ,subnetworkconnection snc
WHERE snc.subnetworkidentifier IN ('3394','3386','3336','3361','3440')
AND snc.EQUIPMENTAIDENTIFIER = eq.identifier
AND snc.administrativecode LIKE '%TN%'
AND snc.administrativecode <> eq.name;
BEGIN
DBMS_OUTPUT.put_line('--Inicio del PL SUBNET --');
OPEN cursor_obtener_subnet(var_snc_id);
LOOP
FETCH cursor_obtener_subnet INTO var_snc_id, var_equip_name;
EXIT WHEN cursor_obtener_subnet%NOTFOUND;
-- Se actualiza el codigo administrativo.
UPDATE SUBNETWORKCONNECTION snc
SET snc.ADMINISTRATIVECODE = var_equip_name
WHERE snc.identifier = var_snc_id;
DBMS_OUTPUT.put_line('El codigo administrativo que se actualizo es de la snc id: '||var_snc_id);
END LOOP;
CLOSE cursor_obtener_subnet;
DBMS_OUTPUT.put_line('-- Fin del PL SUBNET--');
END;
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
Otro ejemplo mas sencillo, con un cursor sin parámetros
----------------------------------------------------
-- VARIABLES
----------------------------------------------------
var_trail_identifier VARCHAR2(255);
CURSOR cursor_regularizar_trails
IS
SELECT trail.identifier
FROM trail
WHERE rownum < 10;
BEGIN
DBMS_OUTPUT.put_line('-- Inicio del PL REGULARIZAR_TRAILS --');
OPEN cursor_regularizar_trails;
LOOP
FETCH cursor_regularizar_trails INTO var_trail_identifier;
LOOP
FETCH cursor_regularizar_trails INTO var_trail_identifier;
EXIT WHEN cursor_regularizar_trails%NOTFOUND;
-- Se actualiza el SERIALNUMBER del TRAIL a fin de Regularizarlo.
DBMS_OUTPUT.put_line('El IDENTIFIER del Trail es : '||var_trail_identifier);
DBMS_OUTPUT.put_line('El IDENTIFIER del Trail es : '||var_trail_identifier);
END LOOP;
CLOSE cursor_regularizar_trails;
DBMS_OUTPUT.put_line('-- Fin del PL REGULARIZAR_TRAILS --');
END;
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
Ejemplo de una función:
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;
Es similar a un procedimiento, pero la diferencia es que nos va a devolver solo un valor mediante la sentencia RETURN.
