Procedimiento

La sintaxis simplificada para la declaración CREATE OR REPLACE PROCEDURE es la siguiente

sigue

CREAR[O REEMPLAZAR]PROCEDIMIENTO nombre_del_procedimiento

[(nombre_del_parametro[ENTRADA|SALIDA|ENTRADA SALIDA]tipo[, ...])]

{ES|AS}

COMIENZO

<cuerpo_del_procedimiento>

FINprocedimiento_nombre;

Dónde,

nombre-del-procedimiento especifica el nombre del procedimiento.

[O REEMPLAZAR] la opción permite la modificación de un procedimiento existente.

La lista de parámetros opcionales contiene nombre, modo y tipos de los

[Link] representa el valor que se pasará desde afuera y OUT
representa el parámetro que se utilizará para devolver un valor fuera de la
procedimiento.

El cuerpo del procedimiento contiene la parte ejecutable.

La palabra clave AS se utiliza en lugar de la palabra clave IS para crear una independiente

procedimiento

Ejemplo
CREAR O REEMPLAZAR PROCEDIMIENTO saludos

AS

INICIAR

dbms_output.put_line('¡Hola Mundo!');

FIN;
produce el siguiente resultado indicado por–

INICIAR

saludos

FIN;

EliminacióndeProcedimiento

ELIMINAR PROCEDIMIENTO nombre-del-procedimiento;

ELIMINAR PROCEDIMIENTO saludos;

Modo IN & OUT Ejemplo 1

DECLARAR

un número

b número;

c número;

PROCEDIMIENTO encontrarMin(x EN número, y EN número, z FUERA número) ES

COMIENZO

SI x<y ENTONCES

z:=x;

ELSE

z:=y;

ENDIF;

FIN;

COMENZAR
 a:=23;

b:=45;

encontrarMin(a,b,c);

dbms_output.put_line(' Mínimo de (23, 45) : '||c);

END;

Mínimo de (23, 45) : 23

Ejemplo 2 de modo ON y OFF

DECLARAR

un número

PROCEDIMIENTO squareNum(x EN SALIDA número) ES

INICIAR

x:=x*x;

FIN;

INICIO

a:=23;

cuadradoNum(a);

dbms_output.put_line(' Cuadrado de (23): '||a);

FIN;

El cuadrado de (23): 529

Function
La sintaxis para la declaración CREATE FUNCTION es la siguiente:

CREAR [O REEMPLAZAR] FUNCIÓN nombre_función

[(nombre_del_parámetro [ENTRADA | SALIDA | ENTRADA Y SALIDA] tipo [, ...])]

DEVOLVER return_datatype
{ES | COMO}
INICIO
< cuerpo_de_función >
FIN [nombre_función];

Dónde,

El nombre de la función especifica el nombre de la función.

[OR REPLACE] option allows the modification of an existing function.

La lista de parámetros opcionales contiene nombre, modo y tipos de los parámetros.

IN representa el valor que se pasará desde afuera y OUT representa el
parámetro que se utilizará para devolver un valor fuera del procedimiento.

La función debe contener una declaración de retorno.

La cláusula RETURN especifica el tipo de dato que vas a devolver de la

función.

El cuerpo de la función contiene la parte ejecutable.

La palabra clave AS se utiliza en lugar de la palabra clave IS para crear un independiente

función.

Select * from customers;

Crea una declaración de función para:

CREAR O REEMPLAZAR FUNCIÓN totalCustomers

DEVOLVER número ES

total number(2) :=0;

INICIAR

SELECT count(*) como total

DE clientes;

RETORNAR total;

FIN;

CallingaFunctionthisfunction:

DECLARAR

número c(2);

INICIO

c:=totalClientes();

dbms_output.put_line('Número total de clientes: '||c);

FIN;

Ejemplo

Demuestra la Declaración, Definición e Invocación de una Función PL/SQL Sencilla que

calcula y devuelve el máximo de dos valores

DECLARAR

un número

número b;

número c;

FUNCIÓN findMax(x EN número, y EN número)

DEVOLVER número

ES
 número z;

INICIO

SI x>y ENTONCES

z:=x;

ELSE

Z:=y;

ENDIF;

DEVOLVER z;

FIN;

INICIO

a:=23;

b:=45;

c:=encontrarMax(a,b);

dbms_output.put_line(' Máximo de (23,45): '||c);

FIN;

Tarea: Programa recursivo

Cursors
Oracle crea un área de memoria, conocida como el área de contexto.

Acursor es un puntero a esta área de contexto. PL/SQL controla el contexto

área a través de un cursor.

Un cursor contiene las filas (una o más) devueltas por una declaración SQL.

procesar las filas devueltas por la declaración SQL, una a la vez.

Hay dos tipos de cursores -

Cursore implícitos
 Cursoresexplícitos

CursoresImplícitos
Los cursores implícitos son creados automáticamente por Oracle.
No hay un cursor explícito para la declaración. Los programadores no pueden
controlar los cursores implícitos y la información en ellos.
INSERToperations, the cursor holds the data that needs to be inserted.
Para las operaciones UPDATE y DELETE, el cursor identifica las filas.
eso se vería afectado

Attribute & Description

%ENCONTRADO

1 DevuelveVERDADERO si una declaración INSERT, UPDATE o DELETE afectó a uno o

más filas o una declaración SELECT INTO devolvió una o más filas.
De lo contrario, devuelve FALSE.

%NO ENCONTRADO

El opuesto lógico de %FOUND. Devuelve VERDADERO si un INSERT, UPDATE o

2
La instrucción DELETE no afectó filas, o una instrucción SELECT INTO no devolvió ninguna

filas. De lo contrario, devuelveFALSO.

 %ESTÁABIERTO

3 Siempre devuelve FALSE para cursores implícitos, porque Oracle cierra el SQL
el cursor se cierra automáticamente después de ejecutar su declaración SQL asociada.

%FILAS

4 Devuelve el número de filas afectadas por un INSERT, UPDATE o DELETE

declaración, o devuelto por una declaración SELECT INTO.

Ejemplo:

DECLARAR

total_rows número(2);

INICIAR

ACTUALIZAR clientes

ESTABLECER salario=salario+500;

SI sql%noencontrado ENTONCES

dbms_output.put_line('no se seleccionaron clientes');

ELSIF sql%encontrado ENTONCES

total_rows:=sql%filas;

dbms_output.put_line(total_rows||' clientes seleccionados ');

ENDIF;

FIN;
Cursoresexplícitos

Los cursores explícitos son cursores definidos por el programador para obtener más
control sobre el área de contexto.
Un cursor explícito debe ser definido en la sección de declaración del
Bloque PL/SQL.
Se crea en una declaración SELECT que devuelve más de una fila.

La sintaxis para crear un cursor explícito.

CURSOR cursor_name IS select_statement;

Trabajar con un cursor explícito incluye los siguientes pasos −

Declarando el cursor para inicializar la memoria

CURSOR c_customers ES

SELECCIONAR id,nombre,dirección DE clientes;

Abriendo el cursor para asignar la memoria

ABRIR c_clientes;

Obteniendo el cursor para recuperar los datos

OBTENER c_clientes EN c_id,c_nombre,c_direccion;

Closing the cursor to release the allocated memory

CERRAR c_clientes;

Ejemplo:

DECLARAR
 c_id [Link]%tipo;

c_nombre [Link]%tipo;

c_addr [Link]%tipo;

CURSOR c_clientes es

SELECT id, name, address FROM customers;

INICIO

ABRIR c_clientes;

BUCLE

OBTENER c_customers en c_id, c_name, c_addr;

SALIR CUANDO c_customers%noencontrado;

dbms_output.put_line(c_id || ' ' || c_name || ' ' ||

c_dirección);
FIN DEL BUCLE;

CERRAR c_customers;

FIN;
DECLARAR

c_id [Link]%tipo;

c_name [Link]%tipo;

c_addr [Link]ón%tipo;

CURSOR c_customersis

SELECT id,name,address FROM customers;

COMENZAR
 ABRIR c_customers;

Bucle

OBTENER c_customersintoc_id,c_name,c_addr;

SALIR CUANDO c_customers%noencontrado;

dbms_output.put_line(c_id||' '||c_name||' '||c_addr);

FINCICLO;

CERRAR c_customers;

FIN;

Registros
Un registro es una estructura de datos que puede contener elementos de datos de diferentes
tipos.
Los registros constan de diferentes campos, similares a una fila de una base de datos.
mesa.

SQL puede manejar los siguientes tipos de registros −

Basado en tabla
Registros basados en cursor

User-defined records

Basado en tablas
%ROWTYPE permite a un programador crear una tabla-
registros basados en cursor y basados en posición.

Ejemplo:

DECLARAR
 customer_rec customers%rowtype;

INICIAR

SELECCIONAR * EN customer_rec

DE clientes

DONDE id=5;

dbms_output.put_line('ID del cliente: '||customer_rec.id);

dbms_output.put_line('Nombre del Cliente: '||customer_rec.name);

dbms_output.put_line('Dirección del Cliente: '||customer_rec.address);

dbms_output.put_line('Salario del Cliente: '||customer_rec.salary);

FIN;

Registros basados en cursor:

Usando el cursor, se recuperó el registro y ejemplo:

DECLARAR

CURSOR customer_curis

SELECCIONAR id,nombre,dirección

DE clientes;

customer_rec customer_cur%tipo_fila;

COMENZAR

ABRIR customer_cur;

CICLO

FETCH customer_curintocustomer_rec;

SALIR CUANDO customer_cur%noencontrado;

DBMS_OUTPUT.put_line(customer_rec.id||' '||customer_rec.name);

FINDEBUCLE;
FIN;

RegistrosDefinidosporelUsuario:
Tipo de registro definido por el usuario que te permite definir los diferentes
estructuras de registros.
Estos registros constan de diferentes campos,
Supongamos que quieres llevar un registro de tus libros en una biblioteca.
los siguientes atributos sobre cada libro -

Título

Autor

Asunto

ID del libro

Sintaxis de los tipos de registro −

TIPO
type_name ES REGISTRO
( nombre_campo1 tipo_dato1 [NO NULO] [:= EXPRESIÓN POR DEFECTO],
nombre_campo2 tipo_dato2 [NO NULO] [:= EXPRESIÓN POR DEFECTO],
...
nombre_campoN tipo_datoN [NO NULO] [:= EXPRESIÓN POR DEFECTO];
nombre_de_registro tipo_nombre;

El registro de Libro se declara de la siguiente manera:-

DECLARAR

TIPO libros ES REGISTRO

(título varchar(50),

autor varchar(50)

asunto varchar(100)

número de id del libro);

libro1 libros;

libro2 libros;
Accediendo a este campo

DECLARAR

tipo librosesregistro

(título varchar(50),

autor varchar(50)

asunto varchar(100)

número de libro_id);

libro1 libros;

libro2 libros

INICIAR

--Book1specification

[Link]:='C Programming';

[Link]:='Nuha Ali ';

[Link]:='C Programming Tutorial';

book1.book_id:=6495407;

--Book2specification

[Link]:='Telecom Billing';

[Link]:='Zara Ali';

[Link]:='Telecom Billing Tutorial';

book2.book_id:=6495700;

--Registro del libro impreso 1

dbms_output.put_line('Título del libro 1: '||[Link]);

dbms_output.put_line('Autor del libro 1 : '||[Link]);

dbms_output.put_line('Tema del libro 1 : '||[Link]);

dbms_output.put_line('Libro 1 id_del_libro : '||book1.book_id);

 Impresora a registro

dbms_output.put_line('Título del libro 2: '||[Link]);

dbms_output.put_line('Autor del libro 2 : '||[Link]);

dbms_output.put_line('Tema del libro 2 : '||[Link]);

dbms_output.put_line('Libro 2 id_libro : '||book2.book_id);

FIN;

Exceptions

Una excepción es una condición de error durante la ejecución de un programa.

SQL apoya a los programadores para detectar tales condiciones
usar el bloque EXCEPCIÓN en el programa y una acción apropiada es
tomado contra la condición de error.

Hay dos tipos de excepciones −

Excepciones definidas por el sistema

Excepciones definidas por el usuario

Sintaxisparaelmanejodeexcepciones
La excepción predeterminada se manejará utilizando CUANDO otros ENTONCES.

DECLARAR

<sección de declaraciones>

INICIO

<comando(s) ejecutable(s)>

EXCEPCIÓN
 <manejo de excepciones va aquí>

CUANDO excepción1 ENTONCES

declaraciones-de-manejo-de-excepciones1

CUANDO exception2 ENTONCES

excepciones2-manejo-de-declaraciones

CUANDO exception3 ENTONCES

excepciones3-manejo-de-declaraciones

........

CUANDO otros ENTONCES

instrucciones-de-manejo-de-excepciones3

FIN;

Ejemplo:

DECLARAR

c_id [Link]%type:=8;

c_name [Link]%tipo;

c_direccion [Link]%tipo;

INICIAR

SELECCIONAR nombre, dirección INTO c_nombre, c_dirección

DE clientes

WHERE id=c_id;

DBMS_OUTPUT.PUT_LINE('Nombre: '||c_name);

DBMS_OUTPUT.PUT_LINE('Dirección: '||c_addr);

EXCEPCIÓN

CUANDO no_data_found ENTONCES

dbms_output.put_line('¡No existe tal cliente!');

 CUANDO otros ENTONCES

dbms_output.put_line('¡Error!');

FIN;

Lanzarexcepciones:

Servidor de base de datos automáticamente siempre que haya alguna base de datos interna
error, pero las excepciones pueden ser lanzadas explícitamente por el programador
usando el comando AUMENTAR
Sintaxis de la Excepción definida por el usuario

DECLARAR

exception_name EXCEPTION;

COMENZAR

SI condición ENTONCES

LEVANTAR exception_name;

ENDIF;

EXCEPCIÓN

CUANDO exception_name ENTONCES

declaración;

FIN;

Ejemplo :

DECLARAR

c_id [Link]%tipo;

c_name [Link]%tipo;

c_addr [Link]ón%tipo;
 -- excepción definida por el usuario

ex_id_inválido EXCEPCIÓN;

COMENZAR
SI c_id <= 0 ENTONCES

LEVAR ex_invalid_id;

ELSE
SELECCIONAR nombre, dirección EN c_nombre, c_dirección

DE clientes

DONDE id = c_id;

DBMS_OUTPUT.PUT_LINE ('Nombre: '|| c_name);

DBMS_OUTPUT.PUT_LINE ('Dirección: ' || c_addr);

FIN SI;

EXCEPCIÓN
CUANDO ex_invalid_id ENTONCES

¡El ID debe ser mayor que cero!

CUANDO no_se_encontraron_datos ENTONCES

¡No existe tal cliente!

CUANDO otros ENTONCES

dbms_output.put_line('¡Error!');

FIN;

DECLARAR

c_id [Link]%type:= &cc_id;

c_nombre [Link]%tipo;

c_addr [Link]ón%tipo;

--excepción definida por el usuario

ex_id_invalido EXCEPCIÓN;

INICIO
 SI c_id<=0 ENTONCES

LEVANTAR ex_invalid_id;

SINO

SELECCIONAR nombre, dirección EN c_name, c_addr

DE clientes

DONDE id=c_id;

DBMS_OUTPUT.PUT_LINE('Nombre: '||c_name);

DBMS_OUTPUT.PUT_LINE('Dirección: '||c_addr);

ENDIF;

EXCEPCIÓN

CUANDO ex_invalid_id ENTONCES

¡El ID debe ser mayor que cero!

CUANDO no_se_encontraron_datos ENTONCES

dbms_output.put_line('¡No existe tal cliente!');

CUANDO otros ENTONCES

dbms_output.put_line('¡Error!');

FIN;

Desencadenantes

Beneficios de los Disparadores

Se pueden escribir desencadenadores para los siguientes propósitos −

Generando automáticamente algunos valores de columnas derivadas

Haciendo cumplir la integridad referencial

Registro de eventos y almacenamiento de información sobre el acceso a la tabla

 Auditoría

Synchronous replication of tables

Imponiendo autorizaciones de seguridad

Prevención de transacciones inválidas

La sintaxis para crear un trigger es :-

CREAR [O REEMPLAZAR] DISPARADOR nombre_del_disparador

{ANTES|DESPUÉS|EN LUGAR DE}

{INSERTAR[O] |ACTUALIZAR[O] |ELIMINAR}

[DE col_name]

EN table_name

[REFERENCIANDO ANTIGUO COMO o NUEVO COMO n]

[POR CADA FILA]

CUANDO(condición)

DECLARAR

Declaraciones de declaración

INICIAR

Declaraciones ejecutables

EXCEPCIÓN

Declaraciones de manejo de excepciones

FIN;

Dónde,

CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing

disparador con el nombre_del_disparador.

{ANTES | DESPUÉS | EN LUGAR DE} − Esto especifica cuándo se activará el disparador

ejecutado. La cláusula INSTEAD OF se utiliza para crear un trigger en una vista.

{INSERTAR [O] | ACTUALIZAR [O] | ELIMINAR} - Esto especifica la operación DML.

[DE col_name] - Esto especifica el nombre de la columna que será actualizada.

[EN la tabla_nombre] - Esto especifica el nombre de la tabla asociada con el
disparador.

[REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values
para varias declaraciones DML, como INSERTAR, ACTUALIZAR y ELIMINAR.

[POR CADA FILA] - Esto especifica un disparador a nivel de fila, es decir, el disparador será
se ejecuta por cada fila afectada. De lo contrario, el desencadenador se ejecutará solo una vez
cuando se ejecuta la declaración SQL, se llama a un desencadenador a nivel de tabla.

CUANDO (condición) - Esto proporciona una condición para las filas para las cuales el disparador sería
fuego. Esta cláusula es válida solo para desencadenadores a nivel de fila.