6.1 Procedimientos Almacenados - Parte 1

Instituto Tecnológico de QuerétaroAsignatura: Taller de Base Datos

Docente: Alejandro Hernández Villalobos

No. documento: 6.1 (V1.2)

Nombre: Procedimientos almacenados (stored procedures)

Objetivo: Conocer la declaración, modificación y llamado de un procedimiento almacenado desde SQL Developer.

Desarrollo:

Para poder ejecutar el código de un procedimiento almacenado, se tienen que realizar 3 pasos previamente:

1)Creación/Compilación.2)Modificación.3)Llamado.

1) Creación/Compilación de un procedimiento almacenado

Hay dos formas que se puede crear un procedimiento almacenado:

a) Desde el SQL worsheet:

1)Primero se escribe el procedimiento almacenado.2)Se corre el script con el segundo icono de la barra, o bien con F5, y el procedimiento será creado y compilado, como se ve en la siguiente ilustración:



Para comprobar que si se creo, ir al Object Browser y buscar bajo “Procedures”, como se muestra a continuación:



b) Desde el Object Browser:

1)Se oprime el botón contextual del ratón sobre procedures, y se desplegara un menú, de ahí seleccionar la opción “New Procedure”. como se muestra a continuación:

2)En la siguiente pantalla hay que especificar el nombre del procedimiento, en este caso PEJEMPLO_2 y dar clic en el botón de “Aceptar”:

3)Una tercera pantalla será abierta, una nueva herramienta dentro SQL Developer, el editor PLSQL, como se puede ver en la siguiente ilustración:



4)Finalmente damos clic en el ícono de compilación y un mensaje aparecerá en indicando con el procedimiento almacenado fue compilado.



2) Modificación de un procedimiento almacenado

Hay dos formas que se puede crear un procedimiento almacenado:

a)A través de SQL Worksheet, de la misma forma que realizo el paso anterior.

b)A través del object browser, ir a “procedures“, ir al procedimiento que queremos modificar en este caso “P_EJEMPLO2”, con el botón contextual de ratón elegimos del menú la opción “Edit”:



c)Y aparecerá nuevamente el editor PLSQL.

Ahí se pueden hacer la modificaciones necesarias y compilar nuevamente, para guardar los cambios en la base de datos.



3)Llamado/Ejecución de un procedimiento almacenado:

La forma en que ejecutaremos nuestro procedimiento almacenado es a través de un bloque de PLSQL, como se muestra a continuación:

BEGIN pmsj.depurarmsj; p_ejemplo1;END;/SELECT *FROM vmsj;

Ejercicio 6.1.1.- Crea un procedimiento almacenado de nombre prc_611, que genere un mensaje 'Hola Mundo', a través de la rutina pmsj.insmsj. Después llámalo desde un bloque PLSQL anónimo.

Nota1: Para realizar esto, tienes que hacer los siguientes pasos:

1)Crear el procedimiento almacenado.2)Hacer las modificaciones pertinentes en el editor PLSQL.3)Invocarlo.



Tipos de parámetros y llamado

Un procedimiento almacenado puede tener 3 tipos de parámetros:

A) IN (por default), parámetro de entradaB) OUT, parámetro de salidaC) IN OUT, parámetro de entrada/salida.

a) Parámetros de entrada, pueden ser referenciados dentro del PA, envían valores del bloque PLSQL al procedimiento almacenado.

Ejemplo 3 (PA con 3 parámetros de entrada):

CREATE OR REPLACE PROCEDURE P_EJEMPLO3 ( Pmensaje IN VARCHAR2, Pnumero IN NUMBER, Pfecha IN DATE) AS V2mensaje VARCHAR2(60) := NULL; Nnumero NUMBER; Dfecha DATE; BEGIN V2mensaje := substr(Pmensaje,1,60); pmsj.insmsj('Los primeros 60 caracteres del parametro Pmensaje son : "' || V2mensaje || '"'); Nnumero := Pnumero + 10; pmsj.insmsj('El valor del parametro Pnumero (mas 10) es : ' || to_char(Nnumero)); Dfecha := Pfecha - 10; pmsj.insmsj('El valor del parametro Pfecha (10 dias antes) es : ' || to_char(Dfecha, 'dd-month-yyyy hh24:mi'));END P_EJEMPLO3;

Tipos de llenado de parámetros

Existen dos formas en que pueden ser llenados los parámetros de un PA.

a)Posicionalb)Nombrado (Referencial)

El llenado posicional será determinado por la posición de los parámetros y de los valores que estos van a recibir.

Ejemplo:

BEGIN pmsj.depurarmsj; p_ejemplo3('Este es el valor que va a tener el parametro', 50, sysdate);END;/



SELECT *FROM vmsj;

En el caso del llenado nombrado, la posición no tiene relevancia ya que se indica explícitamente que valor parámetro actual será enviado a que parámetro formal.

Ejemplo:

BEGIN pmsj.depurarmsj; p_ejemplo3(Pfecha=>sysdate, Pmensaje => 'Este es el valor que va a tener el parametro', Pnumero => 50); END;/SELECT *FROM vmsj;

Nota: Parámetros formales: Pfecha, Pmensaje y Pnumero Parámetros Actuales: sysdate, 'Este es el valor que va a tener el parametro' y 50

Ejercicio 6.1.2.- Crea un procedimiento almacenado de nombre prc_612, que reciba un parámetro de entrada de tipo alfanumérico y otro de tipo booleano.Si el parámetro booleano es FALSE, entonces imprimir el mensaje 'Primer Parámetro Ignorado', en caso contrario, con el valor del parámetro alfanumérico genera un mensaje, a través de la rutina pmsj.insmsj. Genera dos bloques PLSQL anónimos.a)Ejecuta el PA, con llenado posicional de parámetros.b)Ejecuta el PA, con llenado referencial de parámetros.



b) Parámetros de salida, pueden ser referenciados dentro del PA, y adicionalmente se le pueden asignar valores, envía valores del procedimiento almacenado al bloque PLSQL, por lo que se necesitara una variable para recibir el valor que enviado. Ejemplo 4 (PA con un parámetro de entrada y otro de salida):

CREATE OR REPLACEPROCEDURE P_EJEMPLO4(PINnumero IN NUMBER, POUTnumero OUT NUMBER) AS Nnumero NUMBER;BEGIN Nnumero := PINnumero; POUTnumero := POWER(Nnumero, 2) ; END P_EJEMPLO4;

Llamado del PA:

DECLARE Npotencia NUMBER;BEGIN pmsj.depurarmsj; p_ejemplo4(PINnumero => 5, POUTnumero => Npotencia); pmsj.insmsj('Valor de parámetro de entrada' || 5); pmsj.insmsj('Valor de parámetro de salida' || Npotencia);END;/SELECT *FROM vmsj;

Nota: Parámetros formales: PINnumero, POUTnumero. Parámetros actuales: 5, Npotencia.

Ejercicio 6.1.3.- Crea un procedimiento almacenado de nombre prc_613, que reciba como parámetro de entrada el no. de un empleado (empno) y regrese en un parámetro de salida alfanumérico el nombre del empleado (ename), todo esto de la tabla EMP.

a)Ejecuta el PA, con llenado posicional de parámetros.b)Ejecuta el PA, con llenado referencial de parámetros.

Debe validar que el número de empleado introducido pueda no existir.

NOTA: Si la clave del empleado no existe, regresa un mensaje de error que diga 'Cve de empleado no existe'

6.1 Procedimientos Almacenados - Parte 1

Documents

Transcript of 6.1 Procedimientos Almacenados - Parte 1