sesion06b - introducción al pl-sql (oracle)
DESCRIPTION
Este minitutorial tiene como objetivo captar todos los conceptos dictados en cada sesión en el curso de Base de Datos Avanzado II, así como brindar apoyo a los alumnos de la carrera técnica de Computación e Informática, que por algún motivo no hayan asistido a clases. UNIDAD 4. Programación en Oracle Logro de la Unidad de Aprendizaje Al término de la unidad, el alumno construye programas estructurados utilizando el lenguaje PL/SQL dentro del manejador de base de datos Oracle. Incorpora cursores para procesar grandes volúmenes de información y gestiona los posibles errores de ejecución con el uso de excepciones. Temario 4.1 Tema 6: PROGRAMACIÓN PL/SQL 4.1.1 Introducción a Oracle PL/SQL 4.1.2 Tipos de datos en PL/SQL 4.1.3 Estructuras de Bloques de PL/SQL 4.1.4 Sentencias SQL en PL/SQLTRANSCRIPT
/* Sesión06 – Introducción al PL/SQLEstudiante: José Luis Toro AlcarrazCurso: Base de Datos Avanzado IICorreo:[email protected]*/
Objetivos de la sesión.
Conocer los beneficios del lenguaje de programación PL/SQL Distinguir las diferentes secciones que conforman un bloque PL/SQL
1) Introducción al PL/SQL.2) Bloque PL/SQL.3) Declarar variables en PL/SQL.4) Operadores y Funciones.
1) Introducción al PL/SQL.
Lenguaje de programación que se utiliza para acceder a bases de datos ORACLE desde distintos entornos. Está integrado con el servidor de base de datos de modo que su código puede ser procesado de forma rápida y eficiente.
PL/SQL significa Lenguaje Procedimental SQL y trabaja con:
Variables y tipos de datos Estructuras de control Procedimientos y funciones Tipos de objetos y métodos
a) Características del PL/SQL
Combina la potencia y la flexibilidad del SQL con la de un lenguaje 3GL Lenguaje estructurado y potente Código es procesado de forma rápida y eficiente Permite empaquetar órdenes SQL Minimiza comunicación entre cliente y el servidor de Base de Datos
Nota: La diferencia de un lenguaje de tercera generación y los de cuarta generación es que los de tercera generación es un lenguaje donde se tiene que programar absolutamente todo (c++, Pascal, etc.) y en los de cuarta generación solo se tiene que programas eventos.
DECLARE/* Declaración de variables a utilizar */v_NewMajor VARCHAR2(10):= 'HISTORY‘; v_LastName VARCHAR2(10):= 'URMANN‘;
BEGIN/* Actualiza la tabla students */UPDATE students
SET major = v_NewMajor WHERE last_name = v_LastName;
IF SQL%NOTFOUND THENINSERT INTO students (ID, last_name, major)
VALUES (seq_student.NEXTVAL, v_LastName, v_NewMajor);END IF;
END;
2) Bloque PL/SQL
La unidad básica en PL/SQL es el bloque. Un programa PL/SQL puede estar compuesto por bloques secuenciales o anidados. Consta de tres secciones:
- Sección Declarativa. - Sección Ejecutable.- Sección de Excepciones.
a) Sección Declarativa (Opcional).
Aquí se declaran o localizan todas las variables, tipos, cursores y subprogramas locales usados por el bloque. Pueden aparecer instrucciones SQL como instrucciones procedimentales Esta sección es opcional.
DECLARE -- Sección Declarativa V_TOTAL NUMBER(6); V_CADENA VARCHAR2(100);
b) Sección Ejecutable (Obligatoria).
Sección principal del bloque. Se incluyen tanto las sentencias sql como las procedimentalesEs la única sección que es obligatoria.
BEGIN -- Sección ejecutable DBMS_OUTPUT.PUT_LINE('BIENVENIDOS A ORACLE PLSQL');END;
c) Sección de Excepciones (Opcional).
Se usa para responder a los errores que se presenten en la ejecución del programa El código de esta sección no se utiliza a menos que ocurra un error Esta sección es opcional.
EXCEPTION -- Sección de tratamiento de errores WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO HAY DATOS');
d) Estructura del bloque.
DECLARE /* Sección declarativa */ … …BEGIN /* Sección ejecutable */ … … EXCEPTION /* Sección de excepciones */ … … END;/
Para activar la visualización de mensaje del servidor en el SQL PLUS.
SET SERVEROUTPUT ON SIZE 30000;
Para editar un programa PL/SQL.
EDIT
Ejemplo1:
SQL> BEGIN DBMS_OUTPUT.PUT_LINE('HOLA MUNDO'); END;SQL> /
Procedimiento PL/SQL terminado correctamente.
SQL> SET SERVEROUTPUT ON SIZE 30000;SQL> /HOLA MUNDO
Procedimiento PL/SQL terminado correctamente.
Ejemplo2:
SQL> BEGIN DBMS_OUTPUT.PUT_LINE('BIENVENIDOS A ORACLE PL/SQL'); END; /BIENVENIDOS A ORACLE PL/SQL
Procedimiento PL/SQL terminado correctamente.
Ejemplo3:
SQL> DECLARE /* SECCIÓN DECLARATIVA */ V_TOTAL NUMBER(4,2); V_NUMERO1 NUMBER(4,2); V_NUMERO2 NUMBER(4,2); BEGIN /* SECCIÓN EJECUTABLE */ V_NUMERO1:=25; V_NUMERO2:=5; V_TOTAL:= V_NUMERO1/V_NUMERO2; DBMS_OUTPUT.PUT_LINE('RESULTADO:' || V_TOTAL); EXCEPTION /* SECCIÓN DE EXCEPCIONES */ WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('ERROR DE DIVISIÓN POR CERO'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR DESCONOCIDO!!!'); END; /Resultado:5
Procedimiento PL/SQL terminado correctamente.
SQL> EDITEscrito file afiedt.buf
EDIT Abrirá una block de notas con el código del último PL/SQL, actualizamos, guardamos y ejecutamos con el símbolo “/” sin comillas DECLARE /* SECCIÓN DECLARATIVA */ V_TOTAL NUMBER(4,2); V_NUMERO1 NUMBER(4,2); V_NUMERO2 NUMBER(4,2); BEGIN /* SECCIÓN EJECUTABLE */ V_NUMERO1:=25; V_NUMERO2:=0; V_TOTAL:= V_NUMERO1/V_NUMERO2; DBMS_OUTPUT.PUT_LINE('RESULTADO:' || V_TOTAL); EXCEPTION /* SECCIÓN DE EXCEPCIONES */ WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('ERROR DE DIVISIÓN POR CERO'); WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR DESCONOCIDO!!!'); * END;SQL> /Error de división por Cero
Procedimiento PL/SQL terminado correctamente.
SQL> EDITEscrito file afiedt.buf
EDIT Abrirá una block de notas con el código del último PL/SQL, actualizamos, guardamos y ejecutamos con el símbolo “/” sin comillas DECLARE /* SECCIÓN DECLARATIVA */ V_TOTAL NUMBER(4,2); V_NUMERO1 NUMBER(4,2); V_NUMERO2 NUMBER(4,2); BEGIN /* SECCIÓN EJECUTABLE */ V_NUMERO1:=255; V_NUMERO2:=5; V_TOTAL:= V_NUMERO1/V_NUMERO2; DBMS_OUTPUT.PUT_LINE('RESULTADO:' || V_TOTAL); EXCEPTION /* SECCIÓN DE EXCEPCIONES */ WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('ERROR DE DIVISIÓN POR CERO'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR DESCONOCIDO!!!'); * END;SQL> /Error Desconocido!!!
Procedimiento PL/SQL terminado correctamente.
e) Bloques anidados.
Un bloque puede contener otros bloques. Se pueden anidar bloques en la sección ejecutable o en la sección de excepciones. No se puede anidar bloques dentro de la sección declarativa.
Ejemplo:
SQL> DECLARE V_TOTAL NUMBER(4,2); V_NUMERO1 NUMBER(4,2) :=25; V_NUMERO2 NUMBER(4,2) :=5; BEGIN
BEGIN V_TOTAL:= V_NUMERO1/V_NUMERO2; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('ERROR DE DIVISIÓN POR CERO'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR DESCONOCIDO!!!'); END; BEGIN DBMS_OUTPUT.PUT_LINE('RESULTADO:' || V_TOTAL); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR DESCONOCIDO!!!'); END; /RESULTADO:5
Procedimiento PL/SQL terminado correctamente.
f) Comentarios.
Un comentario monolínea comienza con dos guiones (--) y continúa hasta el final de la línea.
Un comentario multilínea comienza con /* y termina con */.
3) Declarar variables en PL/SQL.
Son espacios de memoria que pueden contener valores de datos. A medida que se ejecuta el programa puede cambiar el contenido de la variable. Se declaran en la sección declarativa de un bloque. Cada una de ellas tiene un nombre y un tipo de dato específico.
a) Sintaxis de la declaración.
CONSTANT: Convierte a la variable en una constante, donde su valor inicial es de solo lectura.NOT NULL: Permite hacer obligatorio inicializar la variable.
nombre_variable tipo [CONSTANT] [NOT NULL][:=valor]
Ejemplo:
SQL> DECLARE v_total number(4,2); v_numero1 number(4,2) :=25; -- asignando valor inicial v_numero2 number(4,2) NOT NULL :=5; -- obligando inicializar la variable BEGIN v_total:= v_numero1/v_numero2; dbms_output.put_line('Resultado:' || v_total);
EXCEPTION WHEN others THEN dbms_output.put_line('Error Desconocido!!!'); END; /Resultado:5
Procedimiento PL/SQL terminado correctamente.
b) Utilización del %TYPE.
Permite definir una variable con el tipo de dato que tiene una columna de una tabla determinada.
El uso de %TYPE permite que los programas PL/SQL sean más flexibles y capaces de adaptarse a las definiciones cambiantes de las bases de datos.
DECLARE nombre_variable tabla.columna%TYPE;
c) Utilización del %ROWTYPE.
Permite definir un registro en PL/SQL con los mismos tipos que una fila de una tabla determinada Si cambia la definición de la tabla, %ROWTYPE también lo hace.
DECLARE variable_registro tabla%ROWTYPE;
Ejemplo:
DECLAREV_EMPRECORD EMP%ROWTYPE;V_ENAME EMP.ENAME%TYPE;V_JOB EMP.JOB%TYPE;
BEGIN/* RECUPERA UN REGISTRO DE LA TABLA EMP Y LO ALMACENA EN
V_EMPRECORD */SELECT * INTO V_EMPRECORD FROM EMP WHERE EMPNO = 7844;
DBMS_OUTPUT.PUT_LINE('EMP: ' || V_EMPRECORD.ENAME || ' PUESTO: ' || V_EMPRECORD.JOB);
/* RECUPERA DOS CAMPOS DE LA TABLA EMP Y LOS ALMACENA EN DOS VARIABLES */
SELECT ENAME, JOB INTO V_ENAME, V_JOB FROM EMP WHERE EMPNO = 7844;
DBMS_OUTPUT.PUT_LINE('EMP: ' || V_ENAME || ' PUESTO: ' || V_JOB);
END;
4) Operadores y Funciones
a) Operadores.
Los operadores lógicos, aritméticos y de concatenación usados en PL/SQL son los mismos que se utilizan en SQL.
Operador Operación** , NOT Exponenciación, negación lógica+ , - , || Suma, resta, concatenación* , / Multiplicación, división= , < , > , <= , >=,<>, !=, IS NULL, LIKE, BETWEEN
Comparación
AND ConjunciónOR Inclusión
a) Funciones.
La mayoría de funciones válidas en SQL se pueden utilizar en PL/SQL con excepción de las funciones de grupo las cuales se aplican a grupos de filas de una tabla por tanto solo pueden ir dentro de sentencias SQL.
SQL> DECLARE V_SAL NUMBER; V_NAME VARCHAR2(50); V_FECHA DATE; BEGIN V_SAL := ROUND(2585.6452, 2); V_NAME := INITCAP('LENGUAJE DE PROGRAMACIÓN VI'); V_FECHA := TO_DATE('20/03/2012', 'DD/MM/YYYY'); DBMS_OUTPUT.PUT_LINE(' CURSO: ' || V_NAME || ' FECHA: ' || TO_CHAR(V_FECHA,'DD DAY MONTH YYYY')); DBMS_OUTPUT.PUT_LINE(' SALARIO: ' || V_SAL); END; /CURSO: LENGUAJE DE PROGRAMACIÓN VI FECHA: 20 MARTES MARZO 2012SALARIO: 2585,65
Procedimiento PL/SQL terminado correctamente.
Problema1: Cuantos días han pasado desde el inicio del año a la fecha PL/SQL.
SQL> DECLARE V_DIAS NUMBER(3); BEGIN V_DIAS:= SYSDATE - TRUNC(SYSDATE,'YY'); DBMS_OUTPUT.PUT_LINE('HAN PASADO '|| V_DIAS || ' DIAS'); END; /HAN PASADO 82 DIAS
Procedimiento PL/SQL terminado correctamente.
Problema2: Cuantos días han pasado desde que empezó el curso de BDA2 a la fecha actual.Sabiendo que empezó el 7 de febrero del 2012.
SQL> SELECT NEXT_DAY(ADD_MONTHS(ROUND(SYSDATE,'YY'),1),2)-SYSDATE FROM DUAL;
NEXT_DAY(ADD_MONTHS(ROUND(SYSDATE,'YY'),1),2)-SYSDATE----------------------------------------------------- -42,841956
Excepciones:
WHEN SQL%NOTFOUND THEN No se encuentra el SQLWHEN NOT_DATA_FOUND THEN NO hay datosWHEN ZERO_DIVIDE THEN Cuando se divide entre ceroWHEN OTHERS THEN Otros errores