Objetivos:• Diferenciar entre bloques anónimos y subprogramas.
• Crear un procedimiento e invocarlo desde un bloque anónimo.
• Crear funciones/procedimientos con y sin parámetros.
• Diferenciar entre procedimientos y funciones.
NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
____________________________________________________________________________________
SubProgramas.
Como su nombre indica, los bloques anónimos son bloques ejecutables de PL/SQL que no tienen nombre. Debido a esto, no pueden ser reutilizados ni almacenados para un posterior uso.
Los procedimientos y funciones son bloques PL/SQL también conocidos como subprogramas. Estos subprogramas son compilados y almacenados en la Base de Datos. La estructura de los subprogramas es similar a la estructura de los bloques anónimos. Los subprogramas se pueden declarar tanto a nivel de esquema como también dentro de cualquier otro bloque PL/SQL (es decir pueden estar dentro de otro subprograma: función, procedimiento, paquete, trigger, etc.). Un subprograma contiene las siguientes secciones:
• Parte declarativa: Los subprogramas pueden tener una sección declarativa opcional. Sin embargo, a diferencia de bloques anónimos, la parte declarativa de un subprograma no inicia con la palabra clave DECLARE. La sección declarativa(opcional) sigue la palabra clave IS o AS en la declaración del subprograma.
• Sección ejecutable: Esta es la parte obligatoria del subprograma, la cual contiene la aplicación de la lógica de negocio. Si examina el código en esta sección, puede determinar fácilmente la funcionalidad de negocio del subprograma. Esta sección comienza y termina con las palabras clave BEGIN y END, respectivamente.
• Sección EXCEPTION: Esta es una sección opcional que se incluye para manejar excepciones.
____________________________________________________________________________________
Diferencias entre Los Bloques Anónimos y los SubProgramas.
SubProgramas.
Como su nombre indica, los bloques anónimos son bloques ejecutables de PL/SQL que no tienen nombre. Debido a esto, no pueden ser reutilizados ni almacenados para un posterior uso.
Los procedimientos y funciones son bloques PL/SQL también conocidos como subprogramas. Estos subprogramas son compilados y almacenados en la Base de Datos. La estructura de los subprogramas es similar a la estructura de los bloques anónimos. Los subprogramas se pueden declarar tanto a nivel de esquema como también dentro de cualquier otro bloque PL/SQL (es decir pueden estar dentro de otro subprograma: función, procedimiento, paquete, trigger, etc.). Un subprograma contiene las siguientes secciones:
• Parte declarativa: Los subprogramas pueden tener una sección declarativa opcional. Sin embargo, a diferencia de bloques anónimos, la parte declarativa de un subprograma no inicia con la palabra clave DECLARE. La sección declarativa(opcional) sigue la palabra clave IS o AS en la declaración del subprograma.
• Sección ejecutable: Esta es la parte obligatoria del subprograma, la cual contiene la aplicación de la lógica de negocio. Si examina el código en esta sección, puede determinar fácilmente la funcionalidad de negocio del subprograma. Esta sección comienza y termina con las palabras clave BEGIN y END, respectivamente.
• Sección EXCEPTION: Esta es una sección opcional que se incluye para manejar excepciones.
____________________________________________________________________________________
Diferencias entre Los Bloques Anónimos y los SubProgramas.
Bloques Anónimos
|
Subprograms
|
Bloques de PL/SQL sin nombre.
|
Bloques de PL/SQL con nombre.
|
Compilados por cada corrida.
|
Compilados una sola vez.
|
No se almacenan en la Base de Datos.
|
Almacenados en la Base de Datos.
|
No pueden ser invocados por otras aplicaciones.
|
Gracias al nombre, pueden ser invocados por otras aplicaciones.
|
No retornan Valores.
|
En el caso de las funciones, deben retornar un valor; adicionalmente pueden(ambos) retornar parámetros de salida.
|
No reciben parámetros.
|
Pueden recibir parámetros.
|
La tabla no sólo muestra las diferencias entre los bloques anónimos y subprogramas, sino que también puntualiza las ventajas generales de los subprogramas.
Los bloques anónimos no son objetos de Base de Datos persistentes. Ellos se compilan cada vez que son ejecutados. No se almacenan en la Base de Datos para su reutilización. Si desea volver a utilizarlos, debe volver a ejecutar la secuencia de comandos que crea el bloque anónimo, lo cual causa recompilación y ejecución.
Los Procedimientos y funciones son compilados y almacenados en la Base de Datos. Estos son recompilados sólo cuando se modifican. Ya que se almacenan en la Base de Datos, cualquier aplicación con los permisos apropiados puede hacer uso de estos subprogramas. A su vez, la aplicación puede pasar parámetros a los subprogramas si estos están diseñados para aceptar parámetros. Del mismo modo, una aplicación puede recuperar datos al invocar una función o procedimiento.
____________________________________________________________________________________
Procedimientos.
Procedimientos.
Sintaxis:
CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name
[ (argument1 [ mode1 ]
[ NOCOPY ]
datatype [ DEFAULT expr ]
[, argument2 [ mode2 ]
[ NOCOPY ]
datatype [ DEFAULT expr ]
]...
)
]
[ invoker_rights_clause ]
{ IS | AS }
{ pl/sql_subprogram_body};
En la sintaxis:
procedure_name
|
Es el nombre del procedimiento a ser creado.
|
OR REPLACE
|
Especifica OR REPLACE para volver a crear el procedimiento si ya existe. Utilice esta cláusula para
cambiar la definición de un procedimiento existente sin tener que eliminarlo,
re-crearlo, ni otorgar los privilegios de objeto que ya poseía. Si se re-define
un procedimiento, entonces la Base de Datos Oracle lo recompila.
|
argument
|
Es el nombre dado a
los parámetros del procedimiento. Cada argumento está asociado con un modo y
tipo de datos. Un procedimiento puede tener cualquier número de argumentos
separados por comas.
|
mode
|
Tipo de argumento:
IN (default)
-- De entrada.
OUT -- De salida.
IN OUT -- De entrada y salida
|
datatype
|
Es el tipo de dato
del parámetro. El tipo de dato de los parámetros no puede tener el tamaño
explícito; en cambio, utilizar %TYPE.
|
NOCOPY
|
Especifica NOCOPY para pedir a la Base de Datos que pase este argumento tan rápido como sea
posible. Esta cláusula puede mejorar significativamente el rendimiento cuando
se pasa un valor grande (un Record, index-by
table, o un VARRAY) a un parámetro OUT o IN-OUT . Los valores de los parámetros IN se pasan siempre como NOCOPY.
|
invoker_rights_clause
|
El invoker_rights_clause le permite
especificar si el procedimiento se ejecuta con los privilegios y en el
esquema del usuario que lo posee (owner) o con los privilegios y en el
esquema del usuario que lo invoca.
Esta cláusula también
determina la forma que la Base de Datos resuelve los nombres externos en
consultas, operaciones DML, y sentencias de SQL dinámico en el procedimiento.
Ejemplo: AUTHID { CURRENT_USER | DEFINER } |
pl/sql_subprogram_body
|
Es el bloque de PL/SQL que ejecuta la acción.
|
Ejemplos:
CREATE OR REPLACE PROCEDURE pro_simple
IS
v_number NUMBER;
v_varchar2 VARCHAR2(10);
BEGIN
v_number := 16;
v_varchar2 := 'Hola Mundo';
DBMS_OUTPUT.PUT_LINE(v_varchar2||', tengo '||v_number||' años');
END;/*Este ejemplo Simple muestra como crear un procedimiento.*/
--
SET SERVEROUTPUT ON
BEGIN
pro_simple;
END;
/*Ejecutamos el procedimiento en un bloque anónimo.*/
---OUTPUT:
CREATE OR REPLACE PROCEDURE p_sal_increase
(
p_dept IN departments.department_id%TYPE,
p_job IN jobs.job_id%TYPE,
p_por_incr IN NUMBER
) AUTHID CURRENT_USER IS
v_por_increase NUMBER (3,2);
v_dept_job CHAR(1);
--
CURSOR c_dept_job IS
SELECT 'A'
FROM employees
WHERE department_id = p_dept
AND job_id = p_job;
BEGIN
v_por_increase := p_por_incr/100;
IF v_por_increase > 2 THEN
RAISE_APPLICATION_ERROR(-20001, 'El Aumento no puede ser mayor del 200%');
END IF;
--
OPEN c_dept_job;
FETCH c_dept_job INTO v_dept_job;
CLOSE c_dept_job;
--
IF v_dept_job IS NOT NULL THEN
UPDATE employees
SET salary = salary+(salary*v_por_increase)
WHERE department_id = p_dept
AND job_id = p_job;
ELSE
RAISE_APPLICATION_ERROR(-20002, 'Departamento o Tipo de empleo invalido, Favor Validar la información.');
END IF;
END;
/
SHOW ERROR
/*El procedimiento recibe como parámetros: el numero de departamento, el tipo de empleo y un valor en términos porcentuales; El porciento recibido equivale al aumento que se le va aplicar a los empleados con el departamento y empleo recibido. Debido a que especifiqué: AUTHID CURRENT_USER, el usuario a ejecutar el proceso debe tener los privilegios necesarios para hacerlo.*/
---
Antes de ejecutar el siguiente SCRIPT es recomendable consultar la tabla employees para notar el salario actual de los empleados a actualizar.
BEGIN
p_sal_increase
(
50,
'ST_MAN',
10
);
END;
/*Ejecutamos el Procedure y luego consultamos la tabla una vez mas.*/
---OUTPUT:
____________________________________________________________________________________
Funciones.
CREATE OR REPLACE PROCEDURE pro_simple
IS
v_number NUMBER;
v_varchar2 VARCHAR2(10);
BEGIN
v_number := 16;
v_varchar2 := 'Hola Mundo';
DBMS_OUTPUT.PUT_LINE(v_varchar2||', tengo '||v_number||' años');
END;/*Este ejemplo Simple muestra como crear un procedimiento.*/
--
SET SERVEROUTPUT ON
BEGIN
pro_simple;
END;
/*Ejecutamos el procedimiento en un bloque anónimo.*/
---OUTPUT:
(
p_dept IN departments.department_id%TYPE,
p_job IN jobs.job_id%TYPE,
p_por_incr IN NUMBER
) AUTHID CURRENT_USER IS
v_por_increase NUMBER (3,2);
v_dept_job CHAR(1);
--
CURSOR c_dept_job IS
SELECT 'A'
FROM employees
WHERE department_id = p_dept
AND job_id = p_job;
BEGIN
v_por_increase := p_por_incr/100;
IF v_por_increase > 2 THEN
RAISE_APPLICATION_ERROR(-20001, 'El Aumento no puede ser mayor del 200%');
END IF;
--
OPEN c_dept_job;
FETCH c_dept_job INTO v_dept_job;
CLOSE c_dept_job;
--
IF v_dept_job IS NOT NULL THEN
UPDATE employees
SET salary = salary+(salary*v_por_increase)
WHERE department_id = p_dept
AND job_id = p_job;
ELSE
RAISE_APPLICATION_ERROR(-20002, 'Departamento o Tipo de empleo invalido, Favor Validar la información.');
END IF;
END;
/
SHOW ERROR
/*El procedimiento recibe como parámetros: el numero de departamento, el tipo de empleo y un valor en términos porcentuales; El porciento recibido equivale al aumento que se le va aplicar a los empleados con el departamento y empleo recibido. Debido a que especifiqué: AUTHID CURRENT_USER, el usuario a ejecutar el proceso debe tener los privilegios necesarios para hacerlo.*/
---
Antes de ejecutar el siguiente SCRIPT es recomendable consultar la tabla employees para notar el salario actual de los empleados a actualizar.
BEGIN
p_sal_increase
(
50,
'ST_MAN',
10
);
END;
/*Ejecutamos el Procedure y luego consultamos la tabla una vez mas.*/
---OUTPUT:
Funciones.
Sintaxis:
CREATE [ OR REPLACE ] FUNCTION [ schema. ]function_name
[ (argument1 [ mode1 ]
[ NOCOPY ] datatype
[, argument2 [ mode2 ]
[ NOCOPY ] datatype
]...
)
]
RETURN datatype
[ { invoker_rights_clause
| DETERMINISTIC
| parallel_enable_clause
}
[ invoker_rights_clause
| DETERMINISTIC
| parallel_enable_clause
]...
]
{ { AGGREGATE | PIPELINED }
USING [ schema. ]implementation_type
| [ PIPELINED]
{ IS | AS }
{ pl/sql_function_body }
} ;
En la sintaxis:
function_name
|
Es el nombre de la función a
ser creada.
|
OR REPLACE
|
Especifica OR REPLACE para volver a crear la función
si ya existe. Utilice esta cláusula para cambiar la definición de una función
existente sin tener que eliminarla, re-crearla, ni otorgar los privilegios de
objeto que ya poseía. Si se re-define una función, entonces la Base de Datos Oracle la
recompila.
|
argument
|
Es el nombre
dado a los parámetros de la función. Cada argumento está asociado con un modo
y tipo de datos. Una función puede tener cualquier número de argumentos
separados por comas.
|
mode
|
Tipo de argumento:
IN (default) -- De entrada.
OUT -- De salida.
IN OUT -- De entrada y salida
|
datatype
|
Es el
tipo de dato del parámetro. El tipo de dato de los parámetros no puede tener
el tamaño explícito; en cambio, utilizar %TYPE.
|
NOCOPY
|
Especifica NOCOPY para pedir a la Base de Datos
que pase este argumento tan rápido como sea posible. Esta cláusula puede
mejorar significativamente el rendimiento cuando se pasa un valor grande (un
Record, index-by table, o un VARRAY)
a un parámetro OUT o IN-OUT .
Los valores de los parámetros IN se
pasan siempre como NOCOPY.
|
RETURN
|
Es el
tipo de dato del valor de retorno de la función. Esta cláusula es requerida debido
a que cada función debe devolver un valor. El valor de retorno puede tener
cualquier tipo de datos PL/SQL excepto booleano(BOOLEAN).
|
invoker_rights_clause
|
El invoker_rights_clause le permite especificar si la función se ejecuta
con los privilegios y en el esquema del usuario que lo posee (owner) o con
los privilegios y en el esquema del usuario que lo invoca.
Esta
cláusula también determina la forma que la Base de Datos resuelve los nombres
externos en consultas, operaciones DML, y sentencias de SQL dinámico
en la función.
Ejemplo: AUTHID { CURRENT_USER | DEFINER }
|
DETERMINISTIC
|
Especificar DETERMINISTIC para indicar que la función devuelve el mismo valor de
resultado cada vez que se llama con los mismos valores en sus argumentos.
Debe
especificar esta palabra clave si su intención es de llamar a la función en
la expresión de un índice basado en funciones o de una consulta de una vista
materializada marcada como REFRESH FAST o ENABLE QUERY REWRITE.
|
PARALLEL_ENABLE
|
Es un indicio
de optimización que indica que la función se puede ejecutar desde un servidor
en paralelo de una operación de consulta en paralelo. La función no debe
utilizar el estado de sesión, como variables de paquete, ya que dichas
variables no son necesariamente compartidas entre los servidores de ejecución
en paralelo.
|
PIPELINE
|
Especificar PIPELINE para instruir a Oracle que retorne los resultados de una función de
tabla de forma iterativa. Una función de tabla devuelve un tipo de colección
(una tabla anidada o VARRAY). Se consulta la tabla de funciones mediante el
uso de la palabra clave TABLE antes de que el nombre de la función en la
cláusula FROM de la consulta.
|
AGGREGATE USING
|
Especificar AGGREGATE USING para identificar esta función como una función de agregado, o
una que evalúa un grupo de filas y devuelve una sola fila. Puede especificar
las funciones de agregado en la lista select, cláusula HAVING, y la cláusula
ORDER BY.
|
pl/sql_subprogram_body
|
Es el bloque de PL/SQL que
ejecuta la acción.
|
Ejemplos:
CREATE OR REPLACE FUNCTION simple_function
RETURN VARCHAR2
IS
BEGIN
RETURN 'Ejemplo de una Función Simple.';
END;
/*Este ejemplo muestra como crear un simple función en Oracle.*/
---
SELECT simple_function
FROM dual;
/*En este SELECT le damos uso a nuestra funcion.*/
---OUTPUT:
CREATE OR REPLACE FUNCTION low_high_salary
(
p_dept IN departments.department_id%TYPE,
p_job_id IN jobs.job_id%TYPE,
p_sal IN CHAR DEFAULT 'B' -- L: Mas Bajo; H: Mas Alto, B: Ambos
)
RETURN VARCHAR2
IS
CURSOR c_high_low IS
SELECT
MIN(salary) AS bajo,
MAX(salary) AS alto
FROM employees
WHERE department_id = p_dept
AND job_id = p_job_id;
--
v_high_low_rec c_high_low%ROWTYPE;
--
e_wrong_entry EXCEPTION;
BEGIN
OPEN c_high_low;
FETCH c_high_low INTO v_high_low_rec;
CLOSE c_high_low;
--
IF v_high_low_rec.bajo IS NULL THEN
RAISE e_wrong_entry;
END IF;
--
CASE
WHEN UPPER(p_sal) = 'B' THEN
RETURN 'Salario Mas Alto: '||v_high_low_rec.alto||
', Salario Mas Bajo: '||v_high_low_rec.bajo;
WHEN UPPER(p_sal) = 'L' THEN
RETURN 'Salario Mas Bajo: '||v_high_low_rec.bajo;
WHEN UPPER(p_sal) = 'H' THEN
RETURN 'Salario Mas Alto: '||v_high_low_rec.alto;
ELSE
RETURN 'Valores Validos para p_sal: B,L,H.';
END CASE;
--
EXCEPTION
WHEN e_wrong_entry THEN
RETURN 'Debe introducir un departamento/empleo valido.';
END;
/
SHOW ERROR
/*Creamos una función que recibe como parámetro, el numero de departamento, el código de empleo y una variable que especifica el tipo de salario que se quiere retornar; Según se especifique en el tercer parámetro, la función retornaría el salario mayor, menor o ambos de dicho empleo en el departamento indicado.*/
---
SELECT low_high_salary(50, 'ST_MAN','H')
FROM dual;
/*Realizamos una consulta en la cual usamos la función antes definida.*/
---OUTPUT:
