Programando con PL/SQL en una Base de
Datos Oracle
Instructor: Ing. Francisco Riccio.
OCA Oracle Database Administrator 10g
OCP Oracle Database Administrator 10g
OCP Oracle Database Administrator 11g
OCA Oracle Application Server 10g
Oracle Database 10g RAC Administrator Certified Expert
Managing Oracle on Linux Certified Expert
Oracle Database SQL Certified Expert
Oracle Database 11g Essentials For Implementors
MCTS SQL Server 2005
Email:
[email protected]
Ing. Francisco Riccio.
Página1
Contenido
Consideraciones en la instalación del Oracle XE y configuración del Oracle SQL Developer ................ 3
Introducción a PL/SQL ....................................................................................................................... 7
Declaración de variables.................................................................................................................... 9
Manejo de Estructuras de Control (IF/CASE/LOOP/WHILE/FOR) ...................................................... 14
Manejo de Estructuras Complejas ................................................................................................... 19
(Registros/Index By/Nested Table/VArray) ...................................................................................... 19
Manejo de Cursores ........................................................................................................................ 25
Manipulación de Excepciones.......................................................................................................... 30
Manejo de Archivos......................................................................................................................... 33
Creación de Stored Procedures y Funciones .................................................................................... 36
Creación Paquetes........................................................................................................................... 40
Creación de Triggers ........................................................................................................................ 44
Consideraciones en el Diseño de Código PL/SQL .............................................................................. 51
Programación Orientada a Objetos en PL/SQL ................................................................................. 58
Ing. Francisco Riccio.
Página2
Consideraciones en la instalación del Oracle XE y
configuración del Oracle SQL Developer
Descargas
Oracle XE:
http://www.oracle.com/technetwork/database/express
edition/downloads/index.html
Oracle SQL Developer:
http://www.oracle.com/technetwork/developertools/sql
developer/downloads/index.html
Descargados los productos e instalados, se indica los pasos para poder generar
una conexión hacia una base de datos desde el Oracle SQL Developer.
Ing. Francisco Riccio.
Página3
Donde en Nombre de Conexión se coloca un nombre de identificación de la
conexión que pudiera ser cualquier nombre.
En usuario y contraseña debe ser un usuario válido de la base de datos con su
contraseña, en este caso estamos utilizando el usuario SYSTEM que existe en la
base de datos.
En los campos de Nombre de Host, Puerto y Nombre del Servicio, son datos que
hacen referencia al listener de la base de datos. El listener es un componente de
la base de datos que será responsable de realizar la conexión a la base de datos.
Estos datos pueden ser extraídos en el servidor de base de datos publicando el
comando: lsnrctl status, ejemplo:
Ing. Francisco Riccio.
Página4
Donde lo marcado con rojo son los datos del listener que son colocados en el
Oracle Developer.
Luego podemos dar click en Probar y debe salir Estado: Correcto y
posteriormente guardamos los datos.
Ing. Francisco Riccio.
Página5
Ing. Francisco Riccio.
Página6
Introducción a PL/SQL
PLSQL es una extensión de programación a SQL.
Es el lenguaje de programación de 4ta generación para base de datos Oracle.
Respecto a su arquitectura
Todo código PLSQL se compone de código PLSQL+ sentencias SQL.
Donde el código PLSQL es ejecutado en un engine llamado PLSQL y las secciones
que son sentencias SQL son ejecutadas en el SQL Statement Executor (Oracle
Database Server).
Toda base de datos Oracle tiene un PLSQL engine de forma inherente. Existe
otros productos que cuentan con un PLSQL engine como el Oracle Application
Server en sus productos (Oracle Forms, Oracle Reports).
En el caso de aplicaciones con Oracle Forms y Reports, el PLSQL Engine se
ejecutará en el lado del cliente y las secciones con sentencias SQL son
ejecutadas en la base de datos.
Algunos Beneficios
• Permite crear programas modulares.
•
Integración con herramientas de Oracle.
• Portabilidad.
Ing. Francisco Riccio.
Página7
• Maneja Excepciones.
Un código en PLSQL puede ser de dos tipos: código anónimo y subprogramas.
Un código anónimo es básicamente aquel que el código fuente reside en el lado
cliente y un subprograma reside el código fuente en el servidor. Los
subprogramas pueden ser: stored procedures, funciones, triggers y paquetes.
Se muestra las diferentes secciones que componen los diferentes tipos de código
PLSQL.
Ing. Francisco Riccio.
Página8
Declaración de variables
La sección de declaración de variables se define en la sección DECLARE ejemplo:
DECLARE
mivar <tipo_dato>
Los tipos de datos en Oracle Database 11g en PLSQL:
CHAR, VARCHAR, NUMBER, BINARY_INTEGER:
PLSQL_INTEGER, BOOLEAN, BINARY_FLOAT, BINARY_DOUBLE
Ing. Francisco Riccio.
Página9
DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE.
TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL
DAY TO SECOND
Ing. Francisco Riccio.
Página10
Ejemplo #1:
SET SERVEROUTPUT ON
DECLARE
V_MIVARIABLE VARCHAR(20):='HOLA MUNDO';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_MIVARIABLE);
DBMS_OUTPUT.PUT_LINE('FIN DEL PROGRAMA');
END;
/
Ejemplo #2
SET SERVEROUTPUT ON
DECLARE
V_NUM1 NUMBER(4,2):=10.2;
V_NUM2 NUMBER(4,2):=20.1;
BEGIN
DBMS_OUTPUT.PUT_LINE('LA SUMA ES: '||TO_CHAR(V_NUM1+V_NUM2));
END;
/
Ing. Francisco Riccio.
Página11
Casting entre tipos de datos:
Las funciones de conversión básica son: TO_NUMBER, TO_CHAR y TO_DATE.
Ejemplo:
Si deseamos convertir de número a caracter: TO_CHAR(20);
Si deseamos convertir de caracter a número: TO_NUMBER('20');
Si deseamos convertir de fecha a caracter: TO_CHAR(v_fecha, 'DDMMYYYY
HH24:MI:SS');
Mapeando variables a tipos de datos de columnas de tablas
Para indicar que el tipo de dato de una variable será el que tiene una columna
de una tabla se hace mediante el operador %type.
Ejemplo #3:
SET SERVEROUTPUT ON
DECLARE
V_FECHA V$DATABASE.CREATED%TYPE;
BEGIN
SELECT CREATED INTO V_FECHA FROM V$DATABASE;
DBMS_OUTPUT.PUT_LINE('LA FECHA DE CREACION DE LA BASE DE DATOS
FUE: '||TO_CHAR(V_FECHA,'DDMMYYYY'));
END;
Ing. Francisco Riccio.
Página12
/
Consideraciones
• La asignación de un valor sobre una variable se realiza mediante el
operador := ejemplo: v_mivariable:=20;
• Toda instrucción de PLSQL finaliza con punto y coma (;).
• Si un query devuelve una sola fila y con un campo puede ser asignado a
una variable, ejemplo:
SELECT COUNT(*) INTO MI_VARIABLE FROM HR.EMPLOYEES;
• Si un query devuelve una sola fila y con 2 o más campos también puede
ser asignado a varias variables, ejemplo:
SELECT NAME, CREATED INTO MI_VAR1, MI_VAR2 FROM V$DATABASE;
Ing. Francisco Riccio.
Página13
Manejo de Estructuras de Control
(IF/CASE/LOOP/WHILE/FOR)
IF
Sintaxis:
IF (OPERACIÓN LOGICA) THEN
ELSIF THEN
ELSE
END IF;
Ejemplo #4:
SET SERVEROUTPUT ON
DECLARE
V_FECHA V$DATABASE.CREATED%TYPE;
BEGIN
SELECT CREATED INTO V_FECHA FROM V$DATABASE;
IF (SYSDATE V_FECHA > 30) THEN
DBMS_OUTPUT.PUT_LINE('LA BASE DE DATOS FUE CREADA HACE MÁS DE 30
DIAS.');
ELSE
DBMS_OUTPUT.PUT_LINE('LA BASE DE DATOS FUE CREADA HACE MENOS DE
30 DIAS.');
END IF;
END;
/
Ejemplo #5
SET SERVEROUTPUT ON
Ing. Francisco Riccio.
Página14
DECLARE
V_FECHA V$DATABASE.CREATED%TYPE;
BEGIN
SELECT CREATED INTO V_FECHA FROM V$DATABASE;
IF (SYSDATE V_FECHA > 30) THEN
DBMS_OUTPUT.PUT_LINE('LA BASE DE DATOS FUE CREADA HACE MÁS DE 30
DIAS.');
ELSIF (SYSDATE V_FECHA > 15) AND (SYSDATE V_FECHA < 30) THEN
DBMS_OUTPUT.PUT_LINE('LA BASE DE DATOS FUE CREADA HACE MÁS DE 15
DIAS Y MENOS DE 30 DIAS.');
ELSE
DBMS_OUTPUT.PUT_LINE('LA BASE DE DATOS FUE CREADA HACE MENOS DE
15 DIAS.');
END IF;
END;
/
CASE
Sintaxis:
CASE
WHEN CONDICION1 THEN
WHEN CONDICION2 THEN
ELSE
END CASE;
Ing. Francisco Riccio.
Página15
Ejemplo #6:
SET SERVEROUTPUT ON
DECLARE
V_TOTAL NUMBER:=0;
BEGIN
SELECT COUNT(*) INTO V_TOTAL FROM DBA_OBJECTS;
CASE
WHEN V_TOTAL<2000 THEN
DBMS_OUTPUT.PUT_LINE('LA BASE DE DATOS TIENE MENOS DE 2000
OBJETOS.');
WHEN (V_TOTAL<4000) AND (V_TOTAL > 2000) THEN
DBMS_OUTPUT.PUT_LINE('LA BASE DE DATOS TIENE ENTRE 4000 A 2000
OBJETOS.');
ELSE
DBMS_OUTPUT.PUT_LINE('LA BASE DE DATOS TIENE MAS DE 4000
OBJETOS.');
END CASE;
END;
/
LOOP
Permite generar bucles.
Sintaxis:
LOOP
EXIT WHEN CONDICION_SALIDA
END LOOP
Ing. Francisco Riccio.
Página16
Ejemplo #7:
Imprimiendo los primeros 10 números:
SET SERVEROUTPUT ON
DECLARE
V_NUM NUMBER:=0;
BEGIN
LOOP
V_NUM:=V_NUM+1;
DBMS_OUTPUT.PUT_LINE('NUMERO: '||TO_CHAR(V_NUM));
EXIT WHEN V_NUM >= 10;
END LOOP;
END;
/
WHILE
Sintaxis:
WHILE CONDICION LOOP
END LOOP
Ejemplo #8:
SET SERVEROUTPUT ON
DECLARE
V_NUM NUMBER:=1;
BEGIN
WHILE V_NUM<11 LOOP
DBMS_OUTPUT.PUT_LINE('NUMERO: '||TO_CHAR(V_NUM));
Ing. Francisco Riccio.
Página17
V_NUM:=V_NUM+1;
END LOOP;
END;
/
FOR
Sintaxis:
FOR VARIABLE IN INICIO..FINAL LOOP
END LOOP;
Ejemplo #9:
SET SER
Comentarios de: Programando con PL/SQL en una base de datos Oracle 10 (0)
No hay comentarios