Empezaremos con los bloques anónimos, caracterizados porque no tienen nombre y se suelen crear y ejecutar desde PL/SQL.
Todo bloque debe acabar en . para que sea almacenado en el buffer SQL. Una vez guardado lo podemos ejecutar con la orden “run”. También podemos guardarlo en un fichero con la siguiente orden:
save nombrefichero [replace]
El replace sólo lo pondremos si el fichero ya esta creado.
Para cargar y ejecutar este bloque anónimo guardado en fichero ejecutaremos la siguiente orden:
start nombrefichero
El start lo podemos cambiar por la @ y nos funcionará igualmente.
Pero también podemos cargarlo sin ejecutarlo con la orden “get” y luego ejecutarlo posteriormente con la orden “run”
Un ejemplo muy sencillo de bloque seria el que nos muestra en pantalla un nombre.
BEGIN
DBMS_OUTPUT.PUT_LINE('nombre');
END;
.
Además en los bloques PL/SQL se pueden utilizar lo que llamamos variables de sustitución, que nos pedirán datos por pantalla antes de ejecutar el bloque. Estas variables tienen que ir antepuestas del & para que funcionen.
Un ejemplo seria un bloque que nos pide el DNI de un usuario y nos muestra su nombre.
DECLARE
Vnom clientes.nombre%TYPE;
BEGIN
select nombre into Vnom from clientes where NIF= '&V_nif';
DBMS_OUTPUT.PUT_LINE (Vnom);
END;
.
Como pueden ver es bastante sencillo, pero no tienen tanta funcionalidad como los procedimientos o funciones.
miércoles, 10 de agosto de 2011
Descargas Gratuitas
Aqui les dejamos unos ejemplos de cada publicacion y son gratis.
Si quieres ver nuestros documentos has clic AQUI
- Galeria de Imagenes del BLOG
- O descargar las imagenes en Google Docs
- Descarga los ejemplos de PL/SQL
- O descargar las imagenes en Google Docs
- Descarga los ejemplos de PL/SQL
Si quieres ver nuestros documentos has clic AQUI
Conclusión
En conclusión para la creacion de este blog llamada PL/SQL fue algo importante para nosotros ya que fuimos recreando temas y aprender mas de ellas con la finalidad de que el publico tenga la intención de leer cada publicación para que asi pueda aprender y comprender mas de estos temas que fuimos publicando ya que no nos fue facil recopilar información, agregar links de descarga de ejemplos de cada publicación, etc.
En fin, esperamos que sea de su agrado este blog y sea entendible y si tienen dudas estan los ejemplos que les proporcionaremos en las descargas.
¡Gracias por su atención!
Antecedentes del Equipo
AQUI ESTAN LOS INTEGRANTES QUE AYUDARON A REALIZAR ESTE BLOG PARA LA MATERIA BASE DE DATOS, GRACIAS POR SU COLABORACIÓN.
NUESTRO EQUIPO:
- ESTUDIA EN LA UNIVERSIDAD TECNOLOGICA FIDEL VELAZQUEZ
- ESTA EN EL AREA DE SISTEMAS INFORMATICOS
- ESTAMOS EN TERCER CUATRIMESTRE
- EN EL GRUPO 303
NUESTRO EQUIPO:
- ESTUDIA EN LA UNIVERSIDAD TECNOLOGICA FIDEL VELAZQUEZ
- ESTA EN EL AREA DE SISTEMAS INFORMATICOS
- ESTAMOS EN TERCER CUATRIMESTRE
- EN EL GRUPO 303
Atributos del Cursor
Para conocer detalles de la situación del cursor tenemos 4 atributos:
%FOUND: devuelve verdadero di el ultimo FETCH ha recuperado algún valor; en caso contrario devuelve falso; si el cursor no esta abierto nos devuelve error.
%NOTFOUND: hace justamente lo contrario al anterior.
%ROWCOUNT: nos devuelve el número de filas recuperadas hasta el momento.
%ISOPEN: devuelve verdadero si el cursor esta abierto.
Ejemplo:
%FOUND: devuelve verdadero di el ultimo FETCH ha recuperado algún valor; en caso contrario devuelve falso; si el cursor no esta abierto nos devuelve error.
%NOTFOUND: hace justamente lo contrario al anterior.
%ROWCOUNT: nos devuelve el número de filas recuperadas hasta el momento.
%ISOPEN: devuelve verdadero si el cursor esta abierto.
Ejemplo:
Recursividad en PL/SQL
PL/SQL implementa, al igual que la mayoria de los lenguajes de programación, la posibildad de escribir subprogramas recursivos:
Operadores en PL/SQL
La siguiente tabla ilustra los operadores de PL/SQL.
| Tipo de operador | Operadores |
| Operador de asignación | := (dos puntos + igual) |
| Operadores aritméticos | + (suma) - (resta) * (multiplicación) / (división) ** (exponente) |
| Operadores relacionales o de comparación | = (igual a) <> (distinto de) < (menor que) > (mayor que) >= (mayor o igual a) <= (menor o igual a) |
| Operadores lógicos | AND (y lógico) NOT (negacion) OR (o lógico) |
| Operador de concatenación | || |
Atributos %TYPE y %ROWTYPE
En lugar de indicar explicitamente el tipo y la longitud de una variabl, existe la posibilidad de utilizar los atributos %TYPE y %ROWTYPE para declarar variables que sean del mismo tipo que otros objetos ya definidos.
- %TYPE declara una variable del mismo tipo que otra, o que una columna de una tabla.
Al declarar una variable del mismo tipo que otro objeto usando los atributos %TYPE y %ROWTYPE, se hereda el tipo y la longitud, pero no los posibles atributos NOT NULL ni los valores por defecto que tuviese definidos por el objeto original.
Ejemplo:
- %TYPE declara una variable del mismo tipo que otra, o que una columna de una tabla.
Al declarar una variable del mismo tipo que otro objeto usando los atributos %TYPE y %ROWTYPE, se hereda el tipo y la longitud, pero no los posibles atributos NOT NULL ni los valores por defecto que tuviese definidos por el objeto original.
Ejemplo:
martes, 9 de agosto de 2011
Procedimientos y Funciones en PL/SQL
Los procedimientos PL/SQL son subprogramas compuestos por un conjunto de sentencias SQL. Funciones y procedimientos PL/SQL no son muy diferentes. Un procedimiento o función está constituido por un conjunto de sentencias SQL y PL/SQL lógicamente agrupados para realizar una tarea específica. Los procedimientos y funciones almacenados constituyen un bloque de código PLSQL que ha sido compilado y almacenado en las tablas del sistema de la base de datos Oracle.
Los procedimientos o funciones PLSQL son dinámicos ya que admiten parámetros que les pueden ser pasados antes de su ejecución. Por lo tanto, un procedimiento o función puede realizar diferentes tareas dependiendo de los parámetros que le hayan pasado.
Los procedimientos y funciones Oracle están compuestos por una parte en la que se definen de variables y cursores, otra parte ejecutable compuesta por sentencias SQL y PLSQL, y otra parte opcional enfocada a manejar las excepciones y errores ocurridos durante la ejecución.
Los pasos que sigue Oracle para ejecutar un procedimiento o función son los siguientes:
- Verificar si el usuario tiene permiso de ejecución.
- Verificar la validez del procedimiento o función.
- Y finalmente ejecutarlo.
Las ventajas de usar procedimientos y funciones en lugar de scripts SQL son:
- Facilidad para gestionar la seguridad.
- Mejor rendimiento al estar compilados y almacenados en la base de datos.
- Mejor gestión de la memoria.
- Mayor productividad e integridad.
La diferencia más importante entre los procedimientos y las funciones es que una función, al final de su ejecución, devuelve un valor al bloque PL/SQL que la llamó. Sin embargo, en los procedimientos esto no es posible, aunque si que podemos definir múltiples parámetros de salida que se devolverían al bloque PL/SQL desde el que se ejecutó el procedimiento (esto último también es posible en las funciones).
Sintaxis de un procedimiento PL/SQL:
Sintaxis de una función PL/SQL:
Nombre-parámetro: es el nombre que nosotros queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos podemos omitir los paréntesis.
IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.
OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.
IN OUT: Son parámetros de entrada y salida a la vez.
Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).
Ejemplo de creación de un procedimiento PL/SQL:
Para poder ejecutarlo hay que seguir los siguientes pasos:
Los procedimientos o funciones PLSQL son dinámicos ya que admiten parámetros que les pueden ser pasados antes de su ejecución. Por lo tanto, un procedimiento o función puede realizar diferentes tareas dependiendo de los parámetros que le hayan pasado.
Los procedimientos y funciones Oracle están compuestos por una parte en la que se definen de variables y cursores, otra parte ejecutable compuesta por sentencias SQL y PLSQL, y otra parte opcional enfocada a manejar las excepciones y errores ocurridos durante la ejecución.
Los pasos que sigue Oracle para ejecutar un procedimiento o función son los siguientes:
- Verificar si el usuario tiene permiso de ejecución.
- Verificar la validez del procedimiento o función.
- Y finalmente ejecutarlo.
Las ventajas de usar procedimientos y funciones en lugar de scripts SQL son:
- Facilidad para gestionar la seguridad.
- Mejor rendimiento al estar compilados y almacenados en la base de datos.
- Mejor gestión de la memoria.
- Mayor productividad e integridad.
La diferencia más importante entre los procedimientos y las funciones es que una función, al final de su ejecución, devuelve un valor al bloque PL/SQL que la llamó. Sin embargo, en los procedimientos esto no es posible, aunque si que podemos definir múltiples parámetros de salida que se devolverían al bloque PL/SQL desde el que se ejecutó el procedimiento (esto último también es posible en las funciones).
Sintaxis de un procedimiento PL/SQL:
Sintaxis de una función PL/SQL:
Nombre-parámetro: es el nombre que nosotros queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos podemos omitir los paréntesis.
IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.
OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.
IN OUT: Son parámetros de entrada y salida a la vez.
Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).
Ejemplo de creación de un procedimiento PL/SQL:
Para poder ejecutarlo hay que seguir los siguientes pasos:
Cursores en PL/SQL
PL/SQL utiliza cursores para gestionar las instrucciones SELECT. Un cursor es un conjunto de registros devuelto por una instrucción SQL. Técnicamente los cursores son fragmentos de memoria que reservados para procesar los resultados de una consulta SELECT.
Podemos distinguir dos tipos de cursores:
Cursores implicitos. Este tipo de cursores se utiliza para operaciones SELECT INTO. Se usan cuando la consulta devuelve un único registro.
Cursores explicitos. Son los cursores que son declarados y controlados por el programador. Se utilizan cuando la consulta devuelve un conjunto de registros. Ocasionalmente también se utilizan en consultas que devuelven un único registro por razones de eficiencia. Son más rápidos.
Un cursor se define como cualquier otra variable de PL/SQL y debe nombrarse de acuerdo a los mismos convenios que cualquier otra variable. Los cursores implicitos no necesitan declaración.
OPEN: Ejecuta la consulta asociada al cursor, se obtienen las filas y se situa el cursor en una posición antes de la primera fila: fila en curso.
FETCH: Introduce los valores de la fila en curso en las variables del bloque declaradas en el bloque.
CLOSE: Se libera el cursor.
Un ejemplo para crear un cursor es el siguiente:
Podemos distinguir dos tipos de cursores:
Cursores implicitos. Este tipo de cursores se utiliza para operaciones SELECT INTO. Se usan cuando la consulta devuelve un único registro.
Cursores explicitos. Son los cursores que son declarados y controlados por el programador. Se utilizan cuando la consulta devuelve un conjunto de registros. Ocasionalmente también se utilizan en consultas que devuelven un único registro por razones de eficiencia. Son más rápidos.
Un cursor se define como cualquier otra variable de PL/SQL y debe nombrarse de acuerdo a los mismos convenios que cualquier otra variable. Los cursores implicitos no necesitan declaración.
OPEN: Ejecuta la consulta asociada al cursor, se obtienen las filas y se situa el cursor en una posición antes de la primera fila: fila en curso.
FETCH: Introduce los valores de la fila en curso en las variables del bloque declaradas en el bloque.
CLOSE: Se libera el cursor.
Un ejemplo para crear un cursor es el siguiente:
Estructuras de Control
Las estructuras de control de PL/SQL son habituales de los lenguajes de programación estructurados.
Estructuras de control alternativas (IF)
Estructuras repetitvas
Estructuras de control alternativas (IF)
Estructuras repetitvas
Tipos de datos en PL/SQL
NUMBER (Numérico): Almacena números enteros o de punto flotante, virtualmente de cualquier longitud, aunque puede ser especificada la precisión (Número de digitos) y la escala que es la que determina el número de decimales.
CHAR (Caracter): Almacena datos de tipo caracter con una longitud maxima de 32767 y cuyo valor de longitud por default es 1.
VARCHAR2 (Caracter de longitud variable): Almacena datos de tipo caracter empleando sólo la cantidad necesaria aún cuando la longitud máxima sea mayor.
Aqui se muestra mas tipos de datos:
CHAR (Caracter): Almacena datos de tipo caracter con una longitud maxima de 32767 y cuyo valor de longitud por default es 1.
VARCHAR2 (Caracter de longitud variable): Almacena datos de tipo caracter empleando sólo la cantidad necesaria aún cuando la longitud máxima sea mayor.
Aqui se muestra mas tipos de datos:
| Tipo de dato / Sintáxis | Oracle 8i | Oracle 9i | Descripción |
|---|---|---|---|
| dec(p, e) | La precisión máxima es de 38 dígitos. | La precisión máxima es de 38 dígitos. | Donde p es la precisión y e la escala. Por ejemplo: dec(3,1) es un número que tiene 2 dígitos antes del decimal y un dígito después del decimal. |
| decimal(p, e) | La precisión máxima es de 38 dígitos. | La precisión máxima es de 38 dígitos. | Donde p es la precisión y e la escala. Por ejemplo: decimal(3,1) es un número que tiene 2 dígitos antes del decimal y un dígito después del decimal. |
| double precision | |||
| float | |||
| int | |||
| integer | |||
| numeric(p, e) | La precisión máxima es de 38 dígitos. | La precisión máxima es de 38 dígitos. | Donde p es la precisión y e la escala. Por ejemplo: numeric(7,2) es un número que tiene 5 dígitos antes del decimal y 2 dígitos después del decimal. |
| number(p, e) | La precisión máxima es de 38 dígitos. | La precisión máxima es de 38 dígitos. | Donde p es la precisión y e la escala. Por ejemplo: number(7,2) es un número que tiene 5 dígitos antes del decimal y 2 dígitos después del decimal. |
| real | |||
| smallint | |||
| char (tamaño) | Hasta 32767 bytes en PLSQL. Hasta 2000 bytes en Oracle 8i. | Hasta 32767 bytes en PLSQL. Hasta 2000 bytes en Oracle 9i. | Donde tamaño es el número de caracteres a almacenar. Son cadenas de ancho fijo. Se rellena con espacios. |
| varchar2 (tamaño) | Hasta 32767 bytes en PLSQL. Hasta 4000 bytes en Oracle 8i. | Hasta 32767 bytes en PLSQL. Hasta 4000 bytes en Oracle 9i. | Donde tamaño es el número de caracteres a almacenar. Son cadenas de ancho variable. |
| long | Hasta 2 gigabytes. | Hasta 2 gigabytes. | Son cadenas de ancho variable. |
| raw | Hasta 32767 bytes en PLSQL. Hasta 2000 bytes en Oracle 8i. | Hasta 32767 bytes en PLSQL. Hasta 2000 bytes en Oracle 9i. | Son cadenas binarias de ancho variable. |
| long raw | Hasta 2 gigabytes. | Hasta 2 gigabytes. | Son cadenas binarias de ancho variable. |
| date | Una fecha entre el 1 de Enero de 4712 A.C. y el 31 de Diciembre de 9999 D.C. | Una fecha entre el 1 de Enero de 4712 A.C. y el 31 de Diciembre de 9999 D.C. | |
| timestamp (fractional seconds precision) | No soportado por Oracle 8i. | fractional seconds precision debe ser un número entre 0 y 9. (El valor por defecto es 6) | Incluye año, mes día, hora, minutos y segundos. Por ejemplo: timestamp(6) |
| timestamp (fractional seconds precision) with time zone | No soportado por Oracle 8i. | fractional seconds precision debe ser un número entre 0 y 9. (El valor por defecto es 6) | Incluye año, mes día, hora, minutos y segundos; con un valor de desplazamiento de zona horaria. Por ejemplo: timestamp(5) with time zone |
| timestamp (fractional seconds precision) with local time zone | No soportado por Oracle 8i. | fractional seconds precision debe ser un número entre 0 y 9. (El valor por defecto es 6) | Incluye año, mes día, hora, minutos y segundos; con una zona horaria expresada como la zona horaria actual. Por ejemplo: timestamp(4) with local time zone |
| interval year (year precision) to month | No soportado por Oracle 8i. | year precision debe ser un número entre 0 y 9. (El valor por defecto es 2) | Período de tiempo almacenado en años y meses. Por ejemplo: interval year(4) to month |
| interval day (day precision) to second (fractional seconds precision) | No soportado por Oracle 8i. | day precision debe ser un número entre 0 y 9. (El valor por defecto es 2) fractional seconds precision debe ser un número entre 0 y 9. (El valor por defecto es 6) | Incluye año, mes día, hora, minutos y segundos. Por ejemplo: interval day(2) to second(6) |
| rowid | El formato del campo rowid es: BBBBBBB.RRRR.FFFFF donde BBBBBBB es el bloque en el fichero de la base de datos; RRRR es la fila del bloque; FFFFF es el fichero de la base de datos. | El formato del campo rowid es: BBBBBBB.RRRR.FFFFF donde BBBBBBB es el bloque en el fichero de la base de datos; RRRR es la fila del bloque; FFFFF es el fichero de la base de datos. | Datos binarios de ancho fijo. Cada registro de la base de datos tiene una dirección física o rowid. |
| urowid [tamaño] | Hasta 2000 bytes. | Hasta 2000 bytes. | Rowid universal. Donde tamaño es opcional. |
| boolean | Válido en PLSQL, este tipo de datos no existe en Oracle 8i. | Válido en PLSQL, este tipo de datos no existe en Oracle 9i. | |
| nchar (tamaño) | Hasta 32767 bytes en PLSQL. Hasta 2000 bytes en Oracle 8i. | Hasta 32767 bytes en PLSQL. Hasta 2000 bytes en Oracle 9i. | Donde tamaño es el número de caracteres a almacenar. Cadena NLS de ancho fijo. |
| nvarchar2 (tamaño) | Hasta 32767 bytes en PLSQL. Hasta 4000 bytes en Oracle 8i. | Hasta 32767 bytes en PLSQL. Hasta 4000 bytes en Oracle 9i. | Donde tamaño es el número de caracteres a almacenar. Cadena NLS de ancho variable. |
| bfile | Hasta 4 gigabytes. | Hasta 4 gigabytes. | Localizadores de archivo apuntan a un objeto binario de sólo lectura fuera de la base de datos. |
| blob | Hasta 4 gigabytes. | Hasta 4 gigabytes. | Localizadores LOB apuntan a un gran objeto binario dentro de la base de datos. |
| clob | Hasta 4 gigabytes. | Hasta 4 gigabytes. | Localizadores LOB apuntan a un gran objeto de caracteres dentro de la base de datos. |
| nclob | Hasta 4 gigabytes. | Hasta 4 gigabytes. | Localizadores LOB apuntan a un gran objeto NLS de caracteres dentro de la base de datos. |
Datos compatibles con PL/SQL
PL/SQL dispone de tipos de datos compatibles con los tipos usados para las columnas de las tablas (NUMBER, VARCHAR2, DATE.....) además de los suyos propios como es el tipo BOLEAN.
Las declaraciones de los datos deben realizarse en la sección de declaraciones.
EJEMPLO
Las declaraciones de los datos deben realizarse en la sección de declaraciones.
Variables y Constantes
VARIABLES
Las variables son nombres para procesar los elementos de los datos. Declaración:
:= y DEFAULT son lo mismo. Si ponemos NOT NULL es obligatorio inicializar la variable.
Las variables son nombres para procesar los elementos de los datos. Declaración:
Nombre_variable tipo [NOT NULL] [:= valor | DEFAULT valor]
:= y DEFAULT son lo mismo. Si ponemos NOT NULL es obligatorio inicializar la variable.
EJEMPLO
CONSTANTES
Las constantes son como las variables pero no puede modificarse su valor. Se declaran de la siguiente manera:
nombre_constante CONSTANT tipo_de_dato := valor
Caracteristicas de PL/SQL
La unidad Básica de PL/SQL es el bloque, todos los programas PL/SQL están compuestos por Bloques, que pueden estar anidados. Normalmente, cada bloque define una unidad lógica de trabajo en el programa, separando así unas tareas de otras.
Ejemplo de Bloque PL/SQL
Introduccion
PL/SQL (Procedural Language/Structured Query Language) es un lenguaje de programación incrustado en Oracle.
PL/SQL soportara todas las consultas, ya que la manipulación de datos que se usa es la misma que en SQL, incluyendo nuevas características:
- El manejo de variables.
- Estructuras modulares.
- Estructuras de control de flujo y toma de decisiones.
- Control de excepciones.
El lenguaje PL/SQL está incorporado en:
- Servidor de la base de datos.
- Herramientas de Oracle (Forms, Reports, ...).
En un entorno de base de datos los programadores pueden construir bloques PL/SQL para utilizarlos como procedimientos o funciones, o bien pueden escribir estos bloques como parte de scripts SQL*Plus.
Los programas o paquetes de PL/SQL se pueden almacenar en la base de datos como otro objeto, y todos los usuarios que estén autorizados tienen acceso a estos paquetes. Los programas se ejecutan en el servidor para ahorrar recursos a los clientes.
PL/SQL soportara todas las consultas, ya que la manipulación de datos que se usa es la misma que en SQL, incluyendo nuevas características:
- El manejo de variables.
- Estructuras modulares.
- Estructuras de control de flujo y toma de decisiones.
- Control de excepciones.
El lenguaje PL/SQL está incorporado en:
- Servidor de la base de datos.
- Herramientas de Oracle (Forms, Reports, ...).
En un entorno de base de datos los programadores pueden construir bloques PL/SQL para utilizarlos como procedimientos o funciones, o bien pueden escribir estos bloques como parte de scripts SQL*Plus.
Los programas o paquetes de PL/SQL se pueden almacenar en la base de datos como otro objeto, y todos los usuarios que estén autorizados tienen acceso a estos paquetes. Los programas se ejecutan en el servidor para ahorrar recursos a los clientes.
EJEMPLO BASICO DE SQL:
