manual sql

3 TEMA 3. LENGUAJES DE CONSULTA Y DEFINICION DE DATOS

3 TEMA 3. LENGUAJES DE CONSULTA Y DEFINICION DE DATOS .............. 1

3.1 Introduccin...................................................................................................... 3 3.2 Estructuras lgicas de datos.............................................................................. 4

3.2.1 Tablas ....................................................................................................... 4 3.2.2 Columnas .................................................................................................. 4 3.2.3 Filas o registros......................................................................................... 4 3.2.4 Valor ......................................................................................................... 4 3.2.5 Vistas ........................................................................................................ 4 3.2.6 Indices....................................................................................................... 5 3.2.7 Clusters ..................................................................................................... 5

3.3 Expresiones....................................................................................................... 5 3.4 Tipos de datos................................................................................................... 6

3.4.1 CHAR y VARCHAR ............................................................................... 6 3.4.2 LONG ....................................................................................................... 6 3.4.3 NUMBER ................................................................................................. 7 3.4.4 DATE ....................................................................................................... 7 3.4.5 RAW y LONG RAW ............................................................................... 7 3.4.6 NULL ....................................................................................................... 7

3.5 Operadores y Funciones ................................................................................... 7 3.5.1 Operadores................................................................................................ 8 3.5.2 Funciones.................................................................................................. 9

3.6 Sentencias SQL ................................................................................................ 9 3.6.1 /* */ COMENTARIO .......................................................................... 9 3.6.2 CREATE TABLE (DDL)....................................................................... 10 3.6.3 CREATE VIEW (DDL) ......................................................................... 10 3.6.4 CREATE INDEX (DDL) ....................................................................... 10 3.6.5 DROP object (DDL)............................................................................... 11 3.6.6 DESCRIBE (DDL)................................................................................. 11 3.6.7 ALTER TABLE (DDL) ......................................................................... 11 3.6.8 RENAME (DDL) ................................................................................... 12 3.6.9 SELECT (DML)..................................................................................... 12 3.6.10 INSERT (DML)...................................................................................... 13 3.6.11 DELETE (DML) .................................................................................... 13 3.6.12 UPDATE (DML).................................................................................... 14 3.6.13 LOCK TABLE (DML)........................................................................... 14 3.6.14 COMMIT (DML) ................................................................................... 14 3.6.15 ROLLBACK (DML) .............................................................................. 15 3.6.16 SAVEPOINT (DML) ............................................................................. 15 3.6.17 GRANT (DDL) ...................................................................................... 16 3.6.18 REVOKE (DDL) .................................................................................... 16 3.6.19 CONSTRAINT CLAUSE ...................................................................... 17

3.7 PL/SQL........................................................................................................... 19

Tema 3: Lenguajes de consulta y definicin de datos 1

3.7.1 Estructura de un programa en PL/SQL .................................................. 20 3.7.2 Variables y constantes ............................................................................ 21 3.7.3 Cursores .................................................................................................. 21 3.7.4 %TYPE, %ROWTYPE .......................................................................... 22 3.7.5 Estructuras de control ............................................................................. 22 3.7.6 Gestin de errores................................................................................... 24 3.7.7 Delimitadores ......................................................................................... 24 3.7.8 Tipos de datos......................................................................................... 25 3.7.9 Operadores de comparacin ................................................................... 25 3.7.10 Funciones................................................................................................ 26 3.7.11 Control de transacciones......................................................................... 27 3.7.12 Trabajar con Cursores............................................................................. 28 3.7.13 Subprogramas (Procedimientos y Funciones) ........................................ 31 3.7.14 Programas de ejemplo ............................................................................ 33


3.1 Introduccin El SQL es un lenguaje que permite expresar operaciones diversas, por ejemplo aritmticas, combinatorias y lgicas, con datos almacenados en Bases de Datos Relacionales, que son aquellas que se caracterizan porque la informacin est contenida en estructuras, llamadas tablas, donde los datos estn dispuestos en filas y columnas. SQL significa Structured Query Language (Lenguaje Estructurado de Consultas).

El concepto de Base de Datos Relacional arranca de un artculo publicado en 1970 por Codd, empleado de IBM, donde se sentaban los conceptos bsicos de un modelo relacional de datos y de un sublenguaje para acceder a ellos basado en el clculo de predicados. La idea se desarroll en IBM, dando lugar a un primer prototipo llamado System R que utilizaba un lenguaje llamado SEQUEL (que posteriormente dara lugar al SQL).

El ANSI (American National Standards Institute) ha adoptado este lenguaje como estndar, publicando y desarrollando unas especificaciones para este lenguaje, que has sido posteriormente aceptadas por ISO (International Standards Organization). No significa esto que los productos existentes sigan estrictamente esta norma, principalmente porque el estndar no cubre todas las necesidades planteadas. Del mismo modo, existen diferencias entre distintos productos comerciales.

Las peticiones de datos se expresan en SQL mediante sentencias que deben seguir las normas sintcticas y semnticas del lenguaje. Estas sentencias se pueden escribir directamente en la pantalla de un terminal interactivo, o pueden ser utilizadas embebidas en programas, incorporndose as su capacidad expresiva a la lgica y funciones de stos. A sta ltima forma de utilizar el SQL se le llama SQL dinmico o embebido.

El SQL permite la realizacin de consultas y actualizaciones sobre datos almacenados en tablas relacionales. Este es el principal uso que harn de l usuarios y programadores. Pero tambin hay otras tareas que se pueden realizar mediante sentencias SQL, aunque pertenecen ms a las responsabilidades de los administradores de las bases de datos (DBA). Entre estas funciones adicionales se encuentran la definicin y destruccin de objetos, y la gestin de autorizaciones de acceso.

Existen dos tipos de sentencias SQL:

Sentencias de manipulacin de datos (Data Manipulation Language). Permiten realizar consultas y mantenimiento de los datos. Comienzan con las siguientes palabras del lenguaje: SELECT, INSERT, UPDATE y DELETE.

Sentencias de definicin de datos (Data Definition Language). Permiten definir nuevos objetos y/o destruir otros existentes. Algunos ejemplos de sentencias son las de tipo CREATE y DROP.

Vamos a realizar una descripcin detallada del lenguaje SQL, y de aqu en adelante consideraremos el SGBD ORACLE como el sistema relacional base para nuestros ejemplos. Esto implica que no todas las sentencias SQL sern estndar, pero puesto que ORACLE es la herramienta base en las prcticas de la asignatura, se ilustrar con mayor


claridad la funcionalidad del lenguaje y dar la posibilidad de explotar de forma ms eficiente el sistema relacional empleado en las prcticas.

3.2 Estructuras lgicas de datos.

3.2.1 Tablas

Una tabla es la estructura de datos que contiene los datos en una base de datos relacional. Una tabla se compone de filas y columnas. Una tabla puede representar una nica entidad que se desee representar en el sistema. Tambin puede representar una relacin entre dos entidades. Aunque es posible que una tabla represente al mismo tiempo una entidad y una relacin con otra entidad, se debe tratar de separar esta funcionalidad en la medida de lo posible.

El nombre formal para una tabla es una relacin.

3.2.2 Columnas

Cada columna de una tabla representa un (y slo un) atributo de la entidad. El nombre de la columna debe indicar su naturaleza, en la medida de lo posible. Una columna se identifica por su nombre, no por su posicin.

El orden interno de las columnas de una tabla carece de importancia. De hecho, jams se conocer el orden fsico de las columnas. Se puede especificar el orden en el que mostrar las columnas tras una seleccin, y por supuesto, esto no afectar al orden interno de las columnas en la tabla.

3.2.3 Filas o registros

Los registros almacenan los datos de una tabla. Cada fila o registro representa una ocurrencia de la entidad o relacin representada en la tabla. Los registros no se deben duplicar en una tabla, y existen mecanismos de seguridad que pueden garantizar esta premisa (claves primarias).

El orden interno de las filas dentro de una tabla carece de importancia. De hecho, jams se conocer el orden fsico de las filas. Es ms, no es necesario insertar filas en un determinado orden; slo es necesario preocuparse por el orden de recuperacin de las mismas tras una consulta.

3.2.4 Valor

Un valor es el dato referenciado por la interseccin de una fila y una columna determinadas. Los valores pertenecen al tipo de datos al que pertenece la columna, y pueden carecer de valor (NULL).

3.2.5 Vistas

Una vista es la representacin lgica de otra tabla o combinacin de tablas. Una vista obtiene sus datos de las tablas en las que se basa, que se llaman tablas base. Estas tablas base pueden ser tablas reales u otras vistas.


Las vistas se pueden utilizar casi de igual forma que las tablas. Sin embargo, las vistas no contienen datos, sino que, como ya hemos dicho, los obtienen de las tablas base. Por tanto, todas las operaciones realizadas sobre las vistas afectan realmente a las tablas base.

Las vistas se utilizan para proporcionar diferentes representaciones de los datos que residen en otras tablas o vistas. En general, las vistas se utilizan tambin con los siguientes propsitos:

Proporcionar un nivel adicional de seguridad para las tablas a travs de la restriccin a cierto numero de registros y atributos.

Ocultar la complejidad de los datos. Reducir la complejidad sintctica. Presentar los datos desde otra perspectiva. Proporcionar un nivel de integridad referencial.

3.2.6 Indices

Los ndices se utilizan principalmente con dos propsitos:

Permitir un acceso rpido a las filas de una tabla Forzar la unicidad de filas en una tabla

Los ndices dan un acceso ms rpido a los datos para operaciones que devuelven una pequea porcin de las filas de una tabla.

Una regla importante para determinar cuando es interesante indexar un atributo de una tabla: Las consultas SQL que devuelven menos del 15% de las filas de una tabla pueden ser realizadas ms rpidamente cuando se utilizan ndices.

Los ndices nicos tambin contribuyen a garantizar la no duplicidad de filas en una tabla. Como regla general, siempre se debera crear un ndice sobre la clave primaria de una tabla.

3.2.7 Clusters

El clustering es un medio de estructurar datos en una o ms tablas, de forma que las filas estn fsicamente ms juntas. El clustering es beneficioso si una aplicacin selecciona frecuentemente el mismo grupo de filas de una tabla o tablas.

El clustering puede mejorar el rendimiento de algunas operaciones; sin embargo, tambin puede empeorar el de otras. Es conveniente asegurarse de las condiciones de trabajo antes de decidir si se aplica o no clustering, y donde debe aplicarse

3.3 Expresiones Por su importancia en los siguientes apartados, vamos a definir con cierta rigurosidad el concepto de expresin. Su importancia es tal que una expresin forma parte de las funciones SQL que ms se utilizan en el trabajo cotidiano.

Existen varias formas de identificar una expresin:


[table.] { column | ROWID } text number sequence.CURRVAL sequence.NEXTVAL NULL ROWNUM LEVEL SYSDATE UID USER

: { n | variable } [ :ind_variable ]

function_name ( [DISTINCT | ALL] expr [, expr] )

(expr) +expr, -expr, PRIOR expr expr * expr, expr / expr expr + expr, expr expr, expr || expr

(expr [, expr] )

Las expresiones se usan en:

Como lista de valores en la sentencia SELECT Como condicin en las clusulas WHERE y HAVING En la clusula ORDER BY En la clusula VALUE del comando INSERT En la clusula SET del comando UPDATE

3.4 Tipos de datos

3.4.1 CHAR y VARCHAR

Los tipos CHAR y VARCHAR se usan para definir cadenas de texto genricas, y puede contener caracteres con cualquier valor ASCII. El nmero mximo de caracteres que admiten es 255. La diferencia entre ambos es que mientras CHAR proporciona un tamao fijo para una cadena, VARCHAR admite una cantidad variable de caracteres (Nota: al trabajar con SQL embebido esta diferencia es importante a la hora de especificar condiciones en una sentencia).

La sintaxis para este tipo es CHAR[(n)] o VARCHAR[(n)]. Sus contenidos se especifican entre comillas simples. Ejemplo: Hola, 09-MAR-98, Jackies.

3.4.2 LONG

Es un tipo de datos que se utiliza para almacenar cadenas de hasta 65,535 caracteres. Funciona exactamente igual que el VARCHAR, pero no se puede utilizar en las clusulas WHERE, GROUP BY, ORDER BY, CONNECT BY y DISTINCT, ni como ndices ni dentro de funciones.


3.4.3 NUMBER

Se utiliza para almacenar nmeros con una precisin mxima de 38 dgitos (lo que incluye nmeros desde 1.0E-129 hasta 9.99E124), y una escala que va desde 84 hasta 127.

La sintaxis para este tipo es NUMBER[(precisin [, escala])]. Algunos ejemplos:

7,456,123.89 NUMBER 7,456,123.89 7,456,123.89 NUMBER(9) 7,456,123 7,456,123.89 NUMBER(9,1) 7,456,123.9 7,456,123.89 NUMBER(9,2) 7,456,123.89 7,456,123.89 NUMBER(9,-2) 7,456,100

Otros tipos de datos vlidos que se incluyen por compatibilidad son DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, y DOUBLE_PRECISION. Algunos ejemplos de datos numricos: 7, 255, 29K, 6M, 3.56, 7E5, 3e-2.

3.4.4 DATE

Se usa para almacenar informacin de fechas y horas. Almacena informacin de siglo, ao, mes, da, hora, minuto y segundo. Por defecto se toma la hora 00:00:00, y el formato de fecha por defecto es DD-MON-YY. Existen funciones TO_DATE y TO_CHAR para convertir entre distintos formatos.

Por ejemplo, TO_DATE(13-NOV-85 10:56 A.M., DD-MON-YY HH:MI A.M.)

3.4.5 RAW y LONG RAW

Se utilizan para tipos de datos binarios, es decir, con caracteres que no pertenecen todos al cdigo ASCII. RAW tiene un mximo de contenido de 255, y LONG RAW hasta 65,535. Funcionan igual que el VARCHAR y LONG VARCHAR.

Hay que introducir los datos en formato hexadecimal.

3.4.6 NULL

No se trata de un tipo de datos concreto. Es ms bien un valor que indica la ausencia de cualquier valor para un determinado literal (o columna).

Para comparar valores con NULL slo se pueden usar los operadores IS NULL o IS NOT NULL.

3.5 Operadores y Funciones Existen en ORACLE muchos operadores y funciones. Nosotros slo vamos a tratar las ms corrientes y que ms uso pueden tener dentro del uso habitual del sistema (para ms informacin se puede consultar el SQL Language Reference Manual).


3.5.1 Operadores

3.5.1.1 Operadores aritmticos () Operador de precedencia + - Denota un nmero positivo o negativo * / Multiplicar o dividir + - Sumar o restar

3.5.1.2 Operadores de comparacin = Comparacin !=, ^=, Distinto >, < Mayor que, menor que >=, , >=, =,

3.5.2 Funciones ABS(n) Devuelve el valor absoluto de n CEIL(n) Devuelve el menor entero mayor o igual que n FLOOR(n) Devuelve el mayor entero menor o igual que n MOD(m,n) Resto de la divisin POWER(m,n) Potencia: m elevado a n SQRT(n) Raz cuadrada (NULL si n

3.6.2 CREATE TABLE (DDL)

Crea una tabla, la estructura bsica para mantener los datos en la BD. Una tabla puede tener hasta 254 columnas.

Sintaxis:

CREATE TABLE [user.]table ( { column_element | table_constraint } [,{ column_element | table_constraint } ] ) [TABLESPACE tablespace] [AS query]

Ejemplo:

CREATE TABLE EMP (EMPNO NUMBER NOT NULL PRIMARY KEY, ENAME CHAR(10) NOT NULL CHECK(ENAME=UPPER(ENAME)), DEPTNO NUMBER(2), FOREIGN KEY (DEPTO) REFERENCES DEPT(DEPTNO));

3.6.3 CREATE VIEW (DDL)

Define una vista (una tabla lgica basada en una o ms vistas).

Sintaxis:

CREATE VIEW [user.]view [ ( alias [, alias] ) ] AS query [WITH CHECK OPTION [CONSTRAINT constraint] ]

Ejemplo:

CREATE VIEW ROSTER (ID, DEPTNO) AS SELECT EMPNO, DEPTNO FROM EMP WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM DEPT) WITH CHECK OPTION CONSTRAINT WCO;

(La opcin with check option impide insertar una fila cuyo numero de departamento no exista en la tabla DEPTNO. La fila ser realmente insertada en la tabla EMP slo si se cumple la condicin.)

3.6.4 CREATE INDEX (DDL)

Crea un ndice para una tabla. El ndice proporciona acceso directo a las filas de la tabla, reduciendo el tiempo de acceso. Un ndice contiene una entrada para cada valor que aparece en la columna indexada.

Sintaxis:


CREATE [UNIQUE] INDEX index ON { table ( column [ASC|DESC] [, column [ASC|DESC] ] ) CLUSTER cluster} [TABLESPACE tablespace] [NOSORT]

Ejemplo:

CREATE UNIQUE INDEX I_EMP$EMPNO ON EMP (EMPNO);

3.6.5 DROP object (DDL)

La sentencia DROP sirve para borrar objetos de la base de datos. Se puede utilizar directamente sobre tablas, vistas e ndices.

Sintaxis:

DROP TABLE [user.]table

DROP VIEW [user.]view

DROP INDEX [user.]index

Ejemplo:

DROP TABLE MY_TEST

DROP VIEW MY_VIEW

DROP INDEX MY_INDEX

3.6.6 DESCRIBE (DDL)

Muestra informacin sobre la estructura de una tabla o vista.

Sintaxis:

DESCRIBE [user.]table

Ejemplo:

DESCRIBE EMP;

3.6.7 ALTER TABLE (DDL)

Permite alterar la definicin de una tabla con las siguientes posibilidades:

Aadir columnas o restricciones Modificar definiciones de columnas


Eliminar restricciones Indicar que se ha realizado un backup de la tabla

Sintaxis:

ALTER TABLE [user.]table [ADD ( {column_element | table_constraint} [,{column_element | table_constraint} ] ) ] [MODIFY (column_element [,column_element] ) ] [DROP CONSTRAINT constraint] [BACKUP]

Ejemplo:

ALTER TABLE EMP ADD (THRIFTPLAN NUMBER(7,2), LOANCODE CHAR(1));

ALTER TABLE EMP MODIFY (THRIFTPLAN NUMBER(9,2)) DROP CONSTRAINT ENAME_CNSTR;

3.6.8 RENAME (DDL)

Se utiliza para renombrar una tabla o vista. No se pueden renombrar columnas.

Sintaxis:

RENAME old TO new

Ejemplo:

RENAME DEPT TO EMP_DEPT;

CREATE TABLE TEMPORARY (NEWNAME) AS SELECT OLDNAME FROM STATIC; DROP TABLE STATIC; RENAME TEMPORARY TO STATIC;

3.6.9 SELECT (DML)

Se utiliza para mostrar filas y columnas de una o ms tablas, siguiendo ciertas condiciones y aplicando determinadas operaciones.

Sintaxis:

SELECT [ALL|DISTINCT] { * | table.* | expr [c_alias] } [, { * | table.* | expr [c_alias] } ] FROM [user.]table [t_alias] [,[user.]table [t_alias]] [WHERE contition] [GROUP BY expr [, expr] [HAVING condition] ] [{UNION|INTERSECT|MINUS} SELECT ]


[ORDER BY {expr|position} [ASC|DESC] [,{expr|position} [ASC|DESC] ] ]

Ejemplo:

SELECT ENAME, SAL, JOB, DEPTNO FROM EMP WHERE DEPTNO = 30;

SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP WHERE JOB = CLERK GROUP BY DEPTNO HAVING MIN(SAL)>1000;

3.6.10 INSERT (DML)

Se utiliza para insertar nuevos registros en las tablas o vistas (la tabla base de la vista).

Sintaxis:

INSERT INTO [user.]table [ (column [, column] ) ] { VALUES (value [, value] ) | query }

Ejemplo:

INSERT INTO EMP (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) VALUES (7980, JINKS, CLERCK, 1.2E3, NULL, 40);

INSERT INTO BONUS SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE COMM < 25 * SAL OR JOB IN (PRESIDENT, MANAGER);

3.6.11 DELETE (DML)

Elimina registros de una tabla.

Sintaxis:

DELETE [FROM] [user.]table [alias] [WHERE condicion]

Ejemplo:

DELETE FROM TEMP_ASSIGN;

DELETE FROM EMP WHERE JOB = SALESMAN AND COMM < 100;


3.6.12 UPDATE (DML)

Se utiliza para modificar los datos de una tabla

Sintaxis:

UPDATE [user.]table [alias] SET column = expr [, column = expr] [WHERE condition]

UPDATE [user.]table [alias] SET (column [,column] ) = (query) [,(column [,column] ) = (query) ] [WHERE condition]

Ejemplo:

UPDATE EMP SET COMM = NULL WHERENJOB = TRAINEE;

3.6.13 LOCK TABLE (DML)

Se utiliza para bloquear una o ms tablas en el modo especificado, permitiendo compartir o denegar el acceso a la tabla mientras dure la operacin en curso.

Sintaxis:

LOCK TABLE [user.]table [, [user.]table ] IN lockmode MODE [NOWAIT]

Siendo lockmode = ROW SHARE (permite acceso concurrente. Prohiben los bloqueos de tipo exclusivo. = SHARE UPDATE) ROW EXCLUSIVE (prohibe igual que ROW SHARE, pero adems impide los bloqueos en modo SHARE) SHARE UPDATE (permite acceso concurrente. Prohiben los bloqueos de tipo exclusivo. = ROW SHARE) SHARE (permite consultas concurrentes, pero no actualizaciones) SHARE ROW EXCLUSIVE (permite consultas en una tabla y prohibe a otros bloquearla en modo SHARE o actualizacin de registros) EXCLUSIVE (slo permite consultas)

Ejemplo:

LOCK TABLE EMP IN EXCLUSIVE MODE NOWAIT

3.6.14 COMMIT (DML)

El comando COMMIT se utiliza para:

Hacer permanentes los cambios de la transaccin actual


Borrar los savepoints de una transaccin Finalizar la transaccin Eliminar los bloqueos de la transaccin.

Sintaxis:

COMMIT [WORK]

WORK se usa por compatibilidad ANSI

Ejemplo:

COMMIT

3.6.15 ROLLBACK (DML)

Se utiliza para deshacer todo lo realizado en la actual transaccin. El uso del ROLLBACK sin un SAVEPOINT hace:

Finaliza la transaccin Deshace los cambios realizados durante la ltima transaccin Elimina los savepoints de la transaccin Elimina los bloqueos de la transaccin

Si se usa con un SAVEPOINT se produce:

Deshace slo una porcin de la transaccin Mantiene el savepoint especificado, pero elimina los posteriores Elimina los bloqueos producidos despus del establecimiento del savepoint

Sintaxis:

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint]

Ejemplo:

ROLLBACK;

ROLLBACK TO SAVEPOINT SP5;

3.6.16 SAVEPOINT (DML)

Identifica un punto en una transaccin que permite recuperar con posterioridad el estado de la base de datos justo en ese momento a travs del comando ROLLBACK.

Sintaxis:

SAVEPOINT savepoint


Ejemplo:

SAVEPOINT SP1;

3.6.17 GRANT (DDL)

Sirve par varios propsitos:

Proporcionar acceso a una base de datos (y cambiar passwords) Proporcionar acceso a un espacio de tablas con lmite de espacio Proporcionar varios tipos de acceso a objetos de la base de datos

Sintaxis:

GRANT dabase_priv [, databse_priv] TO user [, user] [IDENTIFIED BY password [, password] ]

Siendo database_priv = DBA | CONNECT | RESOURCE

GRANT RESOURCE [ (quota [k|m] ) ] ON tablespace TO { PUBLIC | user [, user] }

Siendo quota el espacio en bytes.

GRANT { object_priv [, object_priv] | ALL [PRIVILEGES] } ON [user.]object TO { user | PUBLIC } [, user] [WITH GRANT OPTION]

Siendo object_priv = ALTER | DELETE | INDEX | INSERT | REFERENCES | SELECT | UPDATE. Si se utiliza UPDATE o REFERENCES, se pueden especificar columnas.

Ejemplo:

GRANT CONNECT, RESOURCE TO SCOTT IDENTIFIED BY TIGER;

GRANT RESOURCE (10M) ON FINANCE TO SCOTT;

GRANT ALL ON BONUS TO JONES WITH GRANT OPTION; GRANT SELECT, UPDATE ON GOLF_HANDICAP TO PUBLIC;

3.6.18 REVOKE (DDL)

Sirve para realizar todo lo contrario que GRANT:

Eliminar privilegios de la base de datos para uno o varios usuarios Eliminar privilegios de los espacios de tablas para usuarios


Eliminar privilegios de acceso para usuarios de una o varias tablas, vistas y secuencias

Sintaxis:

REVOKE { [CONNECT] [, RESOURCE] [,DBA] } FROM user [, user]

REVOKE space_privilege ON tablespace FROM user [, user]

REVOKE {object_priv [, object_priv] | ALL [PRIVILEGES] } ON [user.]object FROM {user | PUBLIC} [,user]

Ejemplo:

REVOKE RESOURCE FROM SCOTT, JOLLY_ROGER;

REVOKE RESOURCE ON SYSTEM FROM SCOTT;

REVOKE ALTER, DELETE, INSERT, UPDATE ON DEPT10 FROM JONES;

3.6.19 CONSTRAINT CLAUSE

Las clusulas de restriccin sirven para limitar el rango de valores vlidos para una columna o grupo de columnas de una tabla. Las sentencias INSERT, UPDATE y DELETE obligan a la evaluacin de las clusulas de restriccin, que deben ser satisfechas para que las sentencias se ejecuten con xito.

Bsicamente, las restricciones se usan para:

Imponer que los valores de un atributo dado no pueden ser nulos (NOT NULL)

Imponer que el valor de una columna debe ser nico en toda la tabla (UNIQUE)

Identificar una columna o grupo de columnas como clave primaria de una tabla.

Identificar el valor de una columna o conjunto de columnas como clave ajena en otra tabla.

Obligar a que los valores de una columna satisfagan una expresin o estn dentro de un conjunto de valores predeterminado.

Sintaxis:

Para una tabla:

[{UNIQUE | PRIMARY KEY} (column [, column] ) [CONSTRAINT constraint] ]

[FOREIGN KEY (column [, column] ) REFERENCES [user.]table [(column [, column] ) ] [CONSTRAINT constraint]

[CHECK (condition) [CONSTRAINT constraint] ]


Para una columna:

Column [NULL] | [NOT NULL [CONSTRAINT constraint] ] [ { UNIQUE | PRIMARY KEY } [CONSTRAINT constraint] ] [REFERENCES [user.]table [(column)] ] [CONSTRAINT constraint] [CHECK (condition) [CONSTRAINT constraint ]

Ejemplo:

CREATE TABLE P_E (PROJECT NUMBER, EMPLOYEE NUMBER, PRIMARY KEY ( PROJECT, EMPLOYEE));

o

CREATE TABLE p_e (DEPTNO NUMBER PRIMARY KEY, );

CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL CONSTRAINT NN_CONSTRNT);

Nota: Caso especial para la creacin de tablas. En ORACLE, cuando se desea mantener completamente la integridad referencial con opciones, se puede utilizar lo siguiente:

Sintaxis:

FOREIGN KEY () REFERENCES () ON { DELETE | MODIFY } { RESTRICT | CASCADE | SET NULL }


3.7 PL/SQL PL/SQL es un lenguaje de programacin avanzado de cuarta generacin (4GL). Es la extensin procedural que proporciona Oracle al SQL. El objetivo de Oracle al proporcionar este lenguaje fue ofrecer una herramienta de fcil uso para obtener un acceso a la informacin almacenada en una base de datos Oracle, a travs de la inclusin de sentencias SQL embebidas dentro de un lenguaje de programacin de alto nivel.

La idea del SQL embebido no es nueva, ni se le debe a Oracle. El SQL embebido es un mtodo de programacin que permite acceder a las bases de datos a travs de sentencias SQL que se declaran dentro de un programa de alto nivel, y que permiten tanto obtener informacin de la base de datos como actualizarla.

El modo de trabajo cuando se utiliza SQL embebido dentro de un programa es ligeramente diferente al modo de proceder cuando se realiza un programa normal. Lo habitual es realizar un programa cuyo cdigo fuente se compila y se obtiene un ejecutable que realiza las tareas programadas. Sin embargo, cuando se utiliza SQL embebido, el proceso consta de tres fases en lugar de dos: la primera es igual que en el caso anterior, es decir, la creacin del cdigo fuente, esta vez con sentencias SQL apropiadamente declaradas dentro del cdigo; la segunda es distinta, y en este caso consiste en un proceso de pre-compilacin, donde todas las sentencias SQL se convierten a cdigo del lenguaje de alto nivel que se est utilizando; la tercera y ltima corresponde a la segunda fase del caso anterior, y no es ms que la compilacin final del cdigo fuente resultado de la pre-compilacin, con lo que se obtiene un cdigo ejecutable.

En particular, el procesamiento que se realiza de los programas PL/SQL por parte de Oracle sigue la siguiente arquitectura:


Veamos ahora un ejemplo de un programa realizado en PL/SQL, lo que nos dar una mejor idea del tipo de programacin que estamos definiendo:

DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM inventory WHERE product = 'TENNIS RACKET' FOR UPDATE OF quantity; IF qty_on_hand > 0 THEN -- check quantity UPDATE inventory SET quantity = quantity - 1 WHERE product = 'TENNIS RACKET'; INSERT INTO purchase_record VALUES ('Tennis racket purchased', SYSDATE); ELSE INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE); END IF; COMMIT; END;

Como se puede apreciar, en un programa escrito en PL/SQL se pueden utilizar sentencias para manipular los datos de una base de datos, pero tambin se pueden utilizar sentencias de control para procesar los datos. Tambin es posible declarar funciones, procedimientos, constantes, variables, y se puede hacer un tratamiento de los errores (gestin de errores).

3.7.1 Estructura de un programa en PL/SQL

Viendo el programa anterior, se aprecia un primer bloque de declaracin de variables, seguido en una secuencia BEGIN..END; que contiene el cuerpo del programa principal. Las sentencias SQL que se encuentran se ejecutan directamente contra la base de datos y tanto recogen como actualizan datos. Las sentencias de control (IF-ELSE-END IF) permiten decidir que acciones realizar segn las condiciones de trabajo.

En general, la estructura de bloques que se puede tener en un programa escrito en PL/SQL es la siguiente:

donde se aprecia un primer bloque para las definiciones que es opcional, un segundo bloque que contiene el cdigo del programa, y un ltimo bloque, llamado de excepciones, incluido en el bloque principal, que define las acciones a realizar en caso de errores, y tambin es opcional.


3.7.2 Variables y constantes

Las variable pueden ser definidas de cualquier tipo SQL. Por ejemplo:

part_no NUMBER(4); in_stock BOOLEAN;

La asignacin de valores a las variables se hace a travs de dos modos distintos. El primero consiste en utilizar el operador := tal y como se ve en los ejemplos siguientes: tax := price * tax_rate; bonus := current_salary * 0.10; amount := TO_NUMBER(SUBSTR('750 dollars', 1, 3)); valid := FALSE;

El segundo consiste en obtener datos de una consulta y guardarlos en variables definidas con anterioridad, como por ejemplo:

SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;

En este caso, la variable bonus tomar el valor definido por el 10% del salario de un empleado en concreto.

La declaracin de constantes se hace igual que la declaracin de variables, pero se aade la palabra reservada CONSTANT y de forma inmediata se debe definir un valor para dicha constante:

credit_limit CONSTANT REAL := 5000.00;

3.7.3 Cursores

Oracle utiliza reas de trabajo para ejecutar sentencias SQL y almacenar la informacin que se va a procesar. Por ejemplo, cuando se desea utilizar una sentencia SQL que recupere ms de un registro de una tabla, la informacin no se podr recuperar toda de golpe, sino que se tendr que proceder registro a registro. Para ello se utilizan los cursores, es decir, para reservar una zona de memoria donde se recibir la informacin del registro, y poder procesar as todos los registros objeto de la consulta.

La definicin de cursores se realiza del siguiente modo:

DECLARE CURSOR c1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20;

es decir, dentro del bloque de declaraciones se especifica la palabra CURSOR, a continuacin el nombre del cursor (c1 en este caso) y luego la palabra IS seguida de la sentencia SQL que se pretende procesar.

Trabajar con cursores en PL/SQL es relativamente sencillo: basta con declarar el cursor, y construir despus un bucle en el programa principal que recorra todos los elementos del cursor. Un ejemplo de cmo realizar este procesamiento es:


DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, deptno FROM emp; ... BEGIN FOR emp_rec IN c1 LOOP ... salary_total := salary_total + emp_rec.sal; END LOOP;

Obsrvese que la variable emp_rec no se ha definido con anterioridad. Este es un mtodo habitual de trabajo con cursores, es decir, una vez definido el cursor, se crea un bucle (FOR...IN...LOOP - END LOOP) donde el acceso a los elementos del cursor se realiza utilizando el nombre de la variable definido en el LOOP, un punto como separador de estructura (contenidos) y luego los atributos seleccionados con la sentencia SQL (emp_rec.sal).

3.7.4 %TYPE, %ROWTYPE

Existe una forma cmoda de declarar variables de tipos de tablas que vamos a utilizar en consultas SQL. Si no recordamos exactamente el tipo de un atributo de una tabla, podemos utilizar el operador %TYPE para indicar que una variable es del tipo de un atributo de una tabla, por ejemplo:

my_title books.title%TYPE;

Esta declaracin indica que la variable my_title es del tipo del atributo title de la tabla books. Si lo que deseamos es, sin embargo, declarar una variable de tipo registro que contenga todos los atributos de una tabla, lo haremos del siguiente modo:

dept_rec dept%ROWTYPE;

lo que indica en este caso que la variable dept_rec es un registro (contiene todos los campos) de la tabla dept. En este caso, se utiliza una notacin de . para indicar los campos de un registro (por ejemplo, dept_rec.deptno har referencia al campo deptno de la tabla dept).

Las variables de tipo registro se suelen utilizar para recoger la informacin de una tabla dentro de un cursor. El siguiente ejemplo muestra como se realizara esta accin:

DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp; emp_rec c1%ROWTYPE; Posteriormente, dentro del cursor, se realizara la siguiente accin: FETCH c1 INTO emp_rec;

3.7.5 Estructuras de control

Como ya hemos mencionado con anterioridad, es posible utilizar estructuras de control en un programa PL/SQL para decidir dinmicamente que acciones realizar en funcin de ciertos parmetros o valores de variables que se den en un momento dado. Las estructuras de control de que disponemos en PL/SQL son las siguientes:


3.7.5.1 Control condicional

IF condicin THEN sentencias

ELSE sentencias

END IF;

IF condicin THEN sentencias

ELSIF condicin THEN sentencias

ELSE sentencias

END IF;

Con esta estructura se ejecutarn las sentencias que estn a continuacin del THEN cuando la condicin que se evala sea cierta, y se ejecutarn las sentencias a continuacin del ELSE cuando la condicin sea falsa.

3.7.5.2 Control iterativo

LOOP sentencias

END LOOP;

Esta es una estructura que se repetir iterativamente mientras se satisfaga cierta condicin. La forma de proporcionar la condicin de control vara segn los siguientes casos:

WHILE condicin LOOP sentencias

END LOOP;

FOR variable IN valor_minimo..valor_mximo LOOP sentencias

END LOOP;

Siempre es posible salir de un bucle repetitivo utilizando la sentencia:

EXIT;

o bien

EXIT WHEN condicin;

lo cual proporciona ms control sobre las acciones que se estn realizando dentro de los bucles. La sentencia EXIT proporciona una salida inmediata del bucle, mientras que la sentencia EXIT WHEN lo hace cuando se cumple la condicin indicada.


3.7.6 Gestin de errores

La gestin de los errores permite en un programa PL/SQL decidir que tratamiento se debe proporcionar a un a lnea del proceso que hay generado un error, o a cualquier circunstancia no deseada en el flujo normal del programa, y abortarlo tras realizar el tratamiento oportuno del error. Como ya hemos mencionado con anterioridad, este procesamiento se hace a travs del bloque EXCEPTION. Un ejemplo que muestra un tratamiento de errores es el siguiente:

DECLARE ... comm_missing EXCEPTION; -- declaracin de la excepcin BEGIN ... IF commission IS NULL THEN RAISE comm_missing; -- lanzar la excepcin END IF; bonus := (salary * 0.10) + (commission * 0.15); EXCEPTION WHEN comm_missing THEN ... -- procesar la excepcin WHEN OTHERS THEN ... -- procesar cualquier otra excepcin END;

Existen una serie de excepciones definidas en el sistema que se lanzan automticamente sin que sean declaradas en los programas. Por ejemplo, cuando se intenta hacer una divisin por cero, se lanza la excepcin ZERO_DIVIDE, que puede ser tratada dentro del programa o puede ser tratada por defecto por el sistema.

3.7.7 Delimitadores

Los smbolos que se pueden utilizar como delimitadores en un programa PL/SQL son los siguientes y tienen el siguiente significado:

SIMBOLO SIGNIFICADO

+ Operador suma % Indicador de atributo Delimitador de cadenas o caracteres . Delimitador de componentes / Operador divisin ( Delimitador de expresin ) Delimitador de expresin : Indicador de variable husped , Separador de items * Operador multiplicacin Delimitador de identificadores = Operador comparador igual que < Operador comparador menor que > Operador comparador mayor que ; Final de sentencia - Operador resta

:= Operador asignacin


|| Operador concatenacin de cadenas ** Operador elevado /* Inicio de comentario */ Fin de comentario .. Operador de rango != Operador de comparacin distinto Operador de comparacin distinto = Operador de comparacin mayor o igual -- Comentario de una lnea

3.7.8 Tipos de datos

Los tipos de datos que se pueden utilizar en un programa PL/SQL son los siguientes:

Es posible tambin definir subtipos utilizando la siguiente sentencia:

SUBTYPE subtype_name IS base_type [NOT NULL];

pero siempre se tratar de tipos definidos en base a tipos predefinidos o subtipos definidos por el usuario.

3.7.9 Operadores de comparacin

Adems de los operadores que hemos visto como delimitadores o smbolos que utiliza PL/SQL como operadores, existen una serie de operadores para realizar operaciones no


numricas, pero que tienen perfecto sentido dentro del mbito de trabajo en el que estamos. A continuacin se presentan los principales operadores adicionales:

IS NULL variable. Operador que devuelve un valor booleano verdadero o falso segn la variable sea nula o no nula.

variable LIKE valor. Operador que devuelve un valor booleano verdadero o falso segn la cadena almacenada en variable sea igual al dato valor. No se debe confundir este operador con el de igualdad, que aunque se puede aplicar a cadenas, no permite el uso de caracteres universales (%, _).

variable BETWEEN valor1 AND valor2. Operador que devuelve un valor booleano verdadero o falso segn la variable est o no comprendida entre los valores valor1 y valor2

variable IN conjunto. Operador que devuelve un valor booleano segn el valor de la variable se encuentre dentro de un conjunto de valores.

3.7.10 Funciones

PL/SQL permite la utilizacin de funciones predefinidas para trabajar con varios tipos de datos: nmeros, caracteres, fechas, referencias de objetos, etc. Adems existe un conjunto de funciones de propsito general y otro conjunto de funciones que permiten realizar conversiones entre tipos de datos. Se presenta a continuacin una lista que muestra un esquema de las funciones predefinidas del mbito de aplicacin de cada una de ellas:

Funciones sobre errores: SQLCODE, SQLERRM.

Funciones sobre nmeros: ABS, ACOS, ASIN, ATAN, ATAN2, CEIL, COS, COSH, EXP, FLOOR, LN, LOG, MOD, POWER, ROUND, SIGN, SIN, SINH, SQRT, TAN, TANH, TRUNC.

Funciones sobre caracteres: ASCII, CHR, CONCAT, INITCAP, INSTR, INSTRB, LENGTH, LENGTHB, LOWER, LPAD, LTRIM, NLS_INITCAP, NLS_LOWER, NLSSORT, NLS_UPPER, REPLACE, RPAD, RTRIM, SOUNDEX, SUBSTR, SUBSTRB, TRANSLATE, TRIM, UPPER.

Funciones de conversin: CHARTOROWID, CONVERT, HEXTORAW, RAWTOHEX, ROWIDTOCHAR, TO_CHAR, TO_DATE, TO_MULTI_BYTE, TO_NUMBER, TO_SINGLE_BYTE.

Funciones de fecha: ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, ROUND, SYSDATE, TRUNC.

Funciones de referencia de objetos: DEREF, REF, VALUE

Funciones de propsito general: BFILENAME, DECODE, DUMP, EMPTY_BLOB, EMPTY_CLOB, GREATEST, LEAST, NLS_CHARSET_DECL_LEN, NLS_CHARSET_ID, NLS_CHARSET_NAME, NVL, SYS_CONTEXT, SYS_GUID, UID, USER, USERENV, VSIZE.


3.7.11 Control de transacciones

El control de transacciones consiste en una serie de tcnicas que permiten salvaguardar la consistencia de la base de datos, incluyendo la manera de controlar cuando los cambios realizados en una base de datos deben ser permanentes o deben desecharse.

Una transaccin consiste en una serie de sentencias DML de SQL que componen una unidad lgica. Normalmente, las bases de datos tratan cada una de las sentencias SQL como unidades lgicas, pero es necesario que las bases de datos proporcionen mecanismos para poder agrupar conjuntos de sentencias como unidades lgicas. Las sentencias que se pueden utilizar para trabajar con estas agrupaciones lgicas o transacciones son las siguientes:

COMMIT. Valida los cambios realizados en la base de datos por la transaccin actual.

ROLLBACK. Deshace todos los cambios realizados en la base de datos por la transaccin actual.

SAVEPOINT. Sita puntos de control dentro de la transaccin y permite deshacer cambios en la base de datos de forma parcial por la transaccin.

SET TRANSACTION. Permite configurar las propiedades de la transaccin, como lectura/escritura y nivel de aislamiento.

Una transaccin, pues, est definida como el conjunto de sentencias entre un COMMIT y el siguiente COMMIT. COMMIT se debe utilizar para validar los cambios realizados en una base de datos, siempre y cuando la consistencia de la base de datos est asegurada. ROLLBACK se debe utilizar para recuperar el estado de la base de datos al comienzo de la transaccin si alguna de las sentencias de la transaccin ha fallado o ha generado resultados no deseados. SAVEPOINT se debe utilizar para marcar puntos de control que ofrezcan estados consistentes de la base de datos, y poder volver a ellos cuando sea necesario con la sentencia ROLLBACK TO SAVEPOINT sp. Por ltimo, SET TRANSACTION se debe utilizar para configurar ciertos parmetros de las transacciones para determinar niveles de aislamiento y modo de trabajo lectura/escritura.

El comportamiento que cabe esperar de la base de datos ante las transacciones lo definiremos a continuacin. En primer lugar, cuando falla una sentencia SQL simple, la base de datos har un rollback de la sentencia exclusivamente. Si por algn motivo el programa terminase debido a una excepcin no tratada, la base de datos no har un rollback de forma implcita. Por consiguiente, habr que tener especial cuidado en la gestin de las excepciones. Cuando se realiza un rollback hasta un savepoint determinado, todos los savepoints definidos con posterioridad son borrados y no es posible volver a acceder a ellos. Si al finalizar un programa PL/SQL no se especifica que accin realizar sobre la transaccin (ROLLBACK o COMMIT), el resultado de la transaccin depender de lo que realicemos con posterioridad a la finalizacin de la transaccin. Es por ello que una buena prctica en la creacin de programas PL/SQL consiste en la definicin explcita de los COMMIT y ROLLBACK en aquellos puntos que sean necesarios, sobre todo en los puntos de finalizacin de la transaccin (final del bloque, tratamiento de excepciones).


3.7.12 Trabajar con Cursores

PL/SQL utiliza dos tipos de cursores: implcitos y explcitos. PL/SQL declara un cursor implcitamente para todas las sentencias DML de SQL, incluyendo consultas que devuelven slo un registro. Sin embargo, para consultas que devuelven ms de un registro, es necesario definir un cursor explcito o utilizar un bucle FOR para un cursor.

Cuando se trabaja con cursores explcitos, existen tres sentencias para controlar los cursores: OPEN, FETCH y CLOSE. En primer lugar, y una vez declarado el cursor, es necesario inicializarlo con la sentencia OPEN, con lo que se identifica ya el conjunto de resultados. Posteriormente, se utiliza la sentencia FETCH para recuperar el primer registro del resultado. Se puede ejecutar de forma repetitiva la sentencia FETCH para ir pasando por el resto de registros que componen el conjunto de resultados, hasta que se termina de completar un recorrido por todos los registros encontrados. Y una vez se han procesado todos los registros, hay que liberar el cursor con la sentencia CLOSE.

La declaracin de un cursor se hace siguiendo la sintaxis:

CURSOR cursor_name [(parameter[, parameter]...)] [RETURN return_type] IS select_statement;

donde el return_type debe representar un registro de una base de datos, y los parmetros son tipos de datos vlidos SQL. Algunos ejemplos de declaraciones de cursores vlidas:

DECLARE CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000; CURSOR c2 RETURN dept%ROWTYPE IS SELECT * FROM dept WHERE deptno = 10;

Un cursor puede aceptar parmetros, que pueden aparecer en la consulta asociada del mismo modo que pueden aparecer constantes. Dicho de otro modo, es una forma de pasar variables a las consultas SQL que ejecutan los cursores. Los parmetros slo pueden ser de entrada, se especifican con la palabra IN, y no sirven para devolver resultados de la ejecucin de una sentencia. La sintaxis de los parmetros es:

cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression]

y un ejemplo de definicin de cursores con parmetros sera:

DECLARE CURSOR c1 (low INTEGER DEFAULT 0, high INTEGER DEFAULT 99) IS SELECT ...

Cuando se abre un cursor (OPEN) se ejecuta la consulta y se identifica el conjunto de resultados. Para los cursores declarados con la clusula FOR UPDATE, adems se bloquean los registros recuperados. Un ejemplo de uso de OPEN es:

DECLARE CURSOR c1 IS SELECT ename, job FROM emp WHERE sal < 3000; ... BEGIN OPEN c1; ... END;


Si se desea pasar parmetros a un cursor, el momento de hacerlo es al utilizar la sentencia OPEN, tal y como se muestra en este ejemplo:

DECLARE emp_name emp.ename%TYPE; salary emp.sal%TYPE; CURSOR c1 (name VARCHAR2, salary NUMBER) IS SELECT ... ... BEGIN ... OPEN c1(emp_name, 3000); OPEN c1('ATTLEY', 1500); OPEN c1(emp_name, salary);

La sentencia FETCH sigue la siguiente estructura:

FETCH c1 INTO variable_1,variable_2,...,variable_n;

La sentencia FETCH, como ya hemos mencionado, debe ser utilizada dentro de un bucle. Un ejemplo de uso de FETCH es el siguiente:

LOOP FETCH c1 INTO my_record; EXIT WHEN c1%NOTFOUND; -- procesamiento del registro END LOOP;

La sintaxis de cierre de cursor (CLOSE) sigue la siguiente estructura:

CLOSE cursor_name;

Los cursores implcitos se abren para procesar cualquier sentencia SQL no asociada con un cursor explcito. Con los cursores implcitos no se pueden utilizar las sentencias OPEN, FETCH y CLOSE.

Los cursores (variables) tienen 4 atributos que se pueden utilizar para tener un mayor control sobre el procesamiento de las consultas dentro de los bucles. Dichos parmetros son:

%FOUND. Atributo que contiene el valor NULL despus de que se abre el cursor, pero antes de realizar el primer FETCH. Despus de cada FETCH (incluido el primero), el atributo toma el valor TRUE si se encontr algn registro que satisfaga la consulta definida para el cursor, o FALSE en caso contrario. Ejemplo de uso:

LOOP FETCH c1 INTO my_ename, my_sal, my_hiredate; IF c1%FOUND THEN -- fetch succeeded ... ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP;

%ISOPEN. Es un atributo que contiene el valor TRUE si el cursor est abierto, y en caso contrario contiene el valor FALSE. Ejemplo de uso:

IF c1%ISOPEN THEN -- cursor is open


... ELSE -- cursor is closed, so open it OPEN c1; END IF;

%NOTFOUND. Es un parmetro que es lo lgicamente opuesto a %FOUND. Sin embargo, hay que tener cuidado con el uso de este atributo, puesto que si FETCH no se ejecuta nunca de forma satisfactoria (no se encuentra ningn registro) su valor siempre ser NULL y no se saldr nunca del bucle. Un ejemplo de uso es:

LOOP FETCH c1 INTO my_ename, my_sal, my_hiredate; EXIT WHEN c1%NOTFOUND; ... END LOOP;

%ROWCOUNT. Es un atributo que, cuando el cursor est abierto, tiene valor 0, y a medida que se van recuperando registros, va incrementando en 1 su valor. Se utiliza para contar los registros recuperados en un bucle con FETCH. Un ejemplo de uso es:

LOOP FETCH c1 INTO my_ename, my_deptno; IF c1%ROWCOUNT > 10 THEN ... END IF; ... END LOOP;

Por ltimo, vamos a ilustrar el uso de los cursores con algunos ejemplos.

Ejemplo 1: El programa utiliza un cursor para obtener los valores de tres campos (n1, n2 y n3) de la tabla data_table, y guarda el resultado en tres variables locales del programa. Posteriormente, el programa inserta en otra tabla (temp) una serie de datos que han sido calculados en la informacin obtenida con el cursor para el procesamiento de la consulta anterior. Obsrvese la finalizacin del programa con la clusula COMMIT, que valida todas las inserciones realizadas en la tabla temp de forma conjunta, en lugar de hacer una validacin una a una.

DECLARE num1 data_table.n1%TYPE; -- Declare variables num2 data_table.n2%TYPE; -- having same types as num3 data_table.n3%TYPE; -- database columns result temp.col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1; BEGIN OPEN c1; LOOP FETCH c1 INTO num1, num2, num3; EXIT WHEN c1%NOTFOUND; result := num2/(num1 + num3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; CLOSE c1; COMMIT; END;


Ejemplo 2: Este programa comprueba todos los contenedores que contienen el item nmero 5469, retirando sus contenidos hasta acumular un total de 1000 unidades.

DECLARE CURSOR bin_cur(part_number NUMBER) IS SELECT amt_in_bin FROM bins WHERE part_num = part_number AND amt_in_bin > 0 ORDER BY bin_num FOR UPDATE OF amt_in_bin; bin_amt bins.amt_in_bin%TYPE; total_so_far NUMBER(5) := 0; amount_needed CONSTANT NUMBER(5) := 1000; bins_looked_at NUMBER(3) := 0; BEGIN OPEN bin_cur(5469); WHILE total_so_far < amount_needed LOOP FETCH bin_cur INTO bin_amt; EXIT WHEN bin_cur%NOTFOUND; -- if we exit, there's not enough to fill the order bins_looked_at := bins_looked_at + 1; IF total_so_far + bin_amt < amount_needed THEN UPDATE bins SET amt_in_bin = 0 WHERE CURRENT OF bin_cur; -- take everything in the bin total_so_far := total_so_far + bin_amt; ELSE -- we finally have enough UPDATE bins SET amt_in_bin = amt_in_bin - (amount_needed - total_so_far) WHERE CURRENT OF bin_cur; total_so_far := amount_needed; END IF; END LOOP; CLOSE bin_cur; INSERT INTO temp VALUES (NULL, bins_looked_at, '

Los procedimientos tienen la siguiente sintaxis en PL/SQL:

PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];

donde los parmetros se definen de la siguiente forma (IN indica parmetro de entrada, OUT indica parmetro de salidad, IN OUT indica parmetro de entrada/salida):

parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype_name [{:= | DEFAULT} expression]

Hay que destacar que los tipos de datos de los parmetros no se pueden restringir. Por ejemplo, no se puede definir un tipo de datos CHAR(5), sino que se debe definir un tipo de datos CHAR.

Un ejemplo de procedimiento sera el siguiente:

PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + amount WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id,'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id,'Salary is null'); END raise_salary;

Las funciones tienen la siguiente sintaxis en PL/SQL:

FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];

donde los parmetros, igual que en los procedimientos, se definen de la siguiente forma:

parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype_name [{:= | DEFAULT} expression]


Un ejemplo de funcin sera:

FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT losal, hisal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary

Programa 1:

DECLARE x NUMBER := 100; BEGIN FOR i IN 1..10 LOOP IF MOD(i,2) = 0 THEN -- i is even INSERT INTO temp VALUES (i, x, 'i is even'); ELSE INSERT INTO temp VALUES (i, x, 'i is odd'); END IF; x := x + 100; END LOOP; COMMIT; END;

Programa 2:

DECLARE CURSOR c1 is SELECT ename, empno, sal FROM emp ORDER BY sal DESC; my_ename CHAR(10); my_empno NUMBER(4); my_sal NUMBER(7,2); BEGIN OPEN c1; FOR i IN 1..5 LOOP FETCH c1 INTO my_ename, my_empno, my_sal; EXIT WHEN c1%NOTFOUND; /* in case the number requested */ /* is more than the total */ /* number of employees */ INSERT INTO temp VALUES (my_sal, my_empno, my_ename); COMMIT; END LOOP; CLOSE c1; END;

Programa 3:

DECLARE x NUMBER := 0; counter NUMBER := 0; BEGIN FOR i IN 1..4 LOOP x := x + 1000; counter := counter + 1; INSERT INTO temp VALUES (x, counter, 'outer loop'); /* start an inner block */ DECLARE x NUMBER := 0; -- this is a local version of x BEGIN FOR i IN 1..4 LOOP x := x + 1; -- this increments the local x counter := counter + 1; INSERT INTO temp VALUES (x, counter, 'inner loop'); END LOOP; END; END LOOP; COMMIT; END;


Programa 4:

DECLARE CURSOR c1 IS SELECT account_id, oper_type, new_value FROM action ORDER BY time_tag FOR UPDATE OF status; BEGIN FOR acct IN c1 LOOP -- process each row one at a time acct.oper_type := upper(acct.oper_type); /*----------------------------------------*/ /* Process an UPDATE. If the account to */ /* be updated doesn't exist, create a new */ /* account. */ /*----------------------------------------*/ IF acct.oper_type = 'U' THEN UPDATE accounts SET bal = acct.new_value WHERE account_id = acct.account_id; IF SQL%NOTFOUND THEN -- account didn't exist. Create it. INSERT INTO accounts VALUES (acct.account_id, acct.new_value); UPDATE action SET status = 'Update: ID not found. Value inserted.' WHERE CURRENT OF c1; ELSE UPDATE action SET status = 'Update: Success.' WHERE CURRENT OF c1; END IF; /*--------------------------------------------*/ /* Process an INSERT. If the account already */ /* exists, do an update of the account */ /* instead. */ /*--------------------------------------------*/ ELSIF acct.oper_type = 'I' THEN BEGIN INSERT INTO accounts VALUES (acct.account_id, acct.new_value); UPDATE action set status = 'Insert: Success.' WHERE CURRENT OF c1; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- account already exists UPDATE accounts SET bal = acct.new_value WHERE account_id = acct.account_id; UPDATE action SET status = 'Insert: Acct exists. Updated instead.' WHERE CURRENT OF c1; END; /*--------------------------------------------*/ /* Process a DELETE. If the account doesn't */ /* exist, set the status field to say that */ /* the account wasn't found. */ /*--------------------------------------------*/ ELSIF acct.oper_type = 'D' THEN DELETE FROM accounts WHERE account_id = acct.account_id;


IF SQL%NOTFOUND THEN -- account didn't exist. UPDATE action SET status = 'Delete: ID not found.' WHERE CURRENT OF c1; ELSE UPDATE action SET status = 'Delete: Success.' WHERE CURRENT OF c1; END IF; /*--------------------------------------------*/ /* The requested operation is invalid. */ /*--------------------------------------------*/ ELSE -- oper_type is invalid UPDATE action SET status = 'Invalid operation. No action taken.' WHERE CURRENT OF c1; END IF; END LOOP; COMMIT; END;


manual sql

Documents