TEST 1 INTRODUCCION A ORACLE: SQL Y PL/SQL
TEMARIO
RDBMS/ORDBMS
USOS Y BENEFICIOS DE PL/SQL
DIFERENCIA ENTRE SENTENCIAS SQL/COMANDOS SQL PLUS
FUNCIONES DISPONIBLES EN SQL
EQUALITY/NONEQUALITY/OUTER JOINS
FUNCIONES DE GRUPO(GROUP BY/HAVING)
SUBQUERIES
SENTENCIAS DML (INSERT/UPDATE/DELETE/CONTROL TRANSACTIONS)
CREACION Y MANEJO DE TABLAS
10. CONSTRAINTS
11. CREACION/EXPLOTACION VISTAS
12. DICCIONARIO DE DATOS
13. OBJETOS DE BASE DE DATOS 20220w222u
14. SEGURIDAD - CONTROL DE ACCESO DE USUARIOS(GRANT/REVOKE)
15. DECLARACION DE VARIABLES,EJECUCION BLOCKS DE PL/SQL
16. DECLARACION DE DATATYPE
17. ESTRUCTURAS DE CONTROL (IF/FOR)
18. ARRAY/VARRAY
19. CREACION DE REGISTROS/TABLA DE REGISTROS/%ROWTYPE
20. CURSORES (IMPLICITOS/EXPLICITOS)(PARAMETROS EN CURSORES/FOR UPDATE)
21. EXCEPCIONES
22. SENTENCIAS LOOP
23. SQL DINAMICO
RDBMS: El Sistema de Administación de Bases Relacionales es una herramienta standard para de Administración de Negocios. Proporciona rapidez, eficiencia, accesos confiables a enorme cantidad de datos.
ORDBMS: Es un Objeto de RDBMS, lo cual significa que los usuarios pueden definir tipos de datos adicionales y usarlos sin el modelo relacional. Este método agrega valor al dato almacenado en la Base. Los tipos de datos definidos por el usuario facilitan el desarrollo de aplicaciones para trabajar con datos complejos como imágenes, audio y video.
USOS Y BENEFICIOS DE PL/SQL
Soporta SQL, mejor performance, mayor productividad, total integración con ORACLE, seguridad. Bloques de PL/SQL pueden ser enviados por una aplicación a la base de datos ejecutando operaciones complejas sin excesivo tráfico en la red.
DIFERENCIA ENTRE SENTENCIAS SQL/COMANDOS SQL PLUS
La diferencia entre las sentencias SQL y los comandos SQL/PLUS radica en que las sentencias SQL trabajan sobre objetos de la Base de Datos mientras que los comandos también controlan el manejo de SQL/PLUS.
COMANDOS SQL/PLUS:
@,/,append,atribute, break, change, clear, compute, connect, copy, define, del, disconnect, edit, exit, run, save, store, etc.
Comandos prompt,accept,pause(comunicación con el usuario)
SQL> CLEAR BUFFER;
buffer cleared
SQL> INPUT
PROMPT INGRESE UN TITULO DE MAS DE 30 CARACT.DE LONG.
ACCEPT MITITULO PROMPT 'TITULO: '
3 TTITLE LEFT MITITULO SKIP 2
4 SELECT * FROM SGP.SP02
5
SQL> SAVE PROMPT1
Created file PROMPT1
SQL> START PROMPT1
INGRESE UN TITULO DE MAS DE 30 CARACT.DE LONG.
TITULO: ES UNA PRUEBA DEL COMANDO PROMPT
ES UNA PRUEBA DEL COMANDO PROMPT
CLAVE TITULO1
PRESUPUESTO DEPARTAMENTO DE CONTABILIDAD
ATENPROV SERVICIO ADMINISTRATIVO FINANCIERO
-UTILIZANDO &&-
1 SELECT MIN(&&NUMBER_COL) MINIMO,
2 SUM(&&NUMBER_COL) TOTAL,
3* AVG(&&NUMBER_COL) PROMEDIO FROM &&TABLA
SQL> /
old 1: SELECT MIN(&&NUMBER_COL) MINIMO,
new 1: SELECT MIN(NRO_RENGLON) MINIMO,
old 2: SUM(&&NUMBER_COL) TOTAL,
new 2: SUM(NRO_RENGLON) TOTAL,
old 3: AVG(&&NUMBER_COL) PROMEDIO FROM &&TAB
new 3: AVG(NRO_RENGLON) PROMEDIO FROM SGP.SP
MINIMO TOTAL PROMEDIO
1 50047 22,6354591
SQL> UNDEFINE NUMBER_COL --borra la definición de la variable NUMBER_COL
SQL> ED
Wrote file afiedt.buf
1 SELECT MIN(&&NUMBER_COL) MINIMO,
2 SUM(&&NUMBER_COL) TOTAL,
3* AVG(&&NUMBER_COL) PROMEDIO FROM &&TABLA
SQL> /
Enter value for number_col: --pide datos nuevamente porque se borró la variable NUMBER_COL, si no fuera así, tomaría los datos almacenados con anterioridad porque tiene &&
INPUT
PROMPT INGRESE UNA CLAVE VALIDA
PROMPT COMO POR EJEMPLO ATENPROV
ACCEPT VARIABLE VARCHAR2 'CLAVE : '
SELECT * FROM SGP.SP02
WHERE CLAVE = &VARIABLE
SQL> SET LINES 3000
SQL> START PROMPT2
INGRESE UNA CLAVE VALIDA
COMO POR EJEMPLO ATENPROV
SP2-0003: Ill-formed ACCEPT command starting as VARCHAR2 'CLAVE : '
Enter value for variable: 'PRESUPUESTO'
old 2: WHERE CLAVE = &VARIABLE
new 2: WHERE CLAVE = 'PRESUPUESTO'
CLAVE TITULO1 T
----- ----- ----- -------- ----- ------ ----- ----- ------- -
PRESUPUESTO DEPARTAMENTO DE CONTABILIDAD D
Para displayar un mensaje en la pantalla del usuario y tener la confirmación(enter) del usuario hay que utilizar el comando PAUSE
EJ.
SQL> CLEAR BUFFER
buffer cleared
SQL> INPUT
1 PROMPT INGRESE UNA CLAVE VALIDA
2 PROMPT COMO POR EJEMPLO ATENPROV
3 PAUSE PRESIONE ENTER PARA CONTINUAR
4 ACCEPT VARIABLE VARCHAR2 'CLAVE : '
SELECT * FROM SGP.SP02
6 WHERE CLAVE = &VARIABLE
7
SQL> SAVE PROMPT2 REPLACE
Wrote file PROMPT2
SQL> START PROMPT2
INGRESE UNA CLAVE VALIDA
COMO POR EJEMPLO ATENPROV
PRESIONE ENTER PARA CONTINUAR
'PRESUPUESTO'
SP2-0003: Ill-formed ACCEPT command starting as VARCHAR2 'CLAVE : '
Enter value for variable: 'PRESUPUESTO'
old 2: WHERE CLAVE = &VARIABLE
new 2: WHERE CLAVE = 'PRESUPUESTO'
CLAVE TITULO1
PRESUPUESTO DEPARTAMENTO DE CONTABILIDAD
SQL>
BIND VARIABLES
Son variables que se crean en SQL PLUS y se utilizan en PL/SQL.
Pueden ser usadas como si hubiesen sido declaradas en su PL/SQL SUBPROGRAM y acceder la variable desde SQL PLUS. Como son reconocidas por SQL PLUS se pueden displayar sus valores en SQL PLUS o referenciarlos en otro PL/SQL SUBPROGRAM que se corra en SQL PLUS. Para crearlas: VARIABLE var number
Para referenciarlas :var
Para ver el contenido de var PRINT var
Ej.
SQL> begin
2 :var:=25;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print :var
VAR
25
REFCURSOR Bind Variables
SQL> VARIABLE VAR1 REFCURSOR--defino la bind variable
SQL> CLEAR BUFFER
buffer cleared
SQL> INPUT
1 BEGIN
2 OPEN :VAR1 FOR SELECT * FROM SGP.SP02;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> PRINT VAR1
CLAVE TITULO1 TITULO2
PRESUPUESTO DEPARTAMENTO DE CONTABILIDAD DIVISION P
ATENPROV SERVICIO ADMINISTRATIVO FINANCIERO ATENCION A
LA ESTRUCTURA PRINT CIERRA EL CURSOR
FUNCIONES DISPONIBLES EN SQL
En SQL existen: -Funciones numéricas: abs,mod,floor,sign,ceil,round,trunc.
-Funciones de tipo Carácter: concat, lpad, rpad, replace,
upper, lower, ltrim, rtrim, substr, instr, length, etc.
-Funciones Date: add_months, next_day, last_day, sysdate, trunc,
months_between, round.
-Funciones de Conversión: to_char, to_number, to_date.
-Funciones agregadas: avg, count, max, min, sum, stddev, variance.
CEIL(15,7)---RETORNA 16
FLOOR(15,7)--RETORNA 15
EQUALITY/NONEQUALITY/OUTER JOINS
EQUALITY JOIN.
--SELECCIONA DE TODAS LAS O/C DE ESE NNE, LA DE MAXIMA FECHA_ORDEN
SELECT A1.NRO_ORDEN,A1.ANIO_ORDEN,
NRO_PEDCOT,ANIO_PEDCOT FROM COMPRAS.CO50 A1,COMPRAS.CO51 A2
WHERE A1.NRO_ORDEN=A2.NRO_ORDEN AND
A1.ANIO_ORDEN=A2.ANIO_ORDEN AND
A2.NNE = 8910290813279 AND
A1.FECHA_ORDEN=(SELECT MAX(FECHA_ORDEN) FROM COMPRAS.CO50 A3
WHERE EXISTS(SELECT * FROM COMPRAS.CO51 A4
WHERE A4.NNE = 8910290813279 AND
A4.NRO_ORDEN=A3.NRO_ORDEN AND
A4.ANIO_ORDEN=A3.ANIO_ORDEN));
ANTI-JOIN.
SELECT * FROM EMP
WHERE DEPTNO NOT IN (SELECT DEPTNO FROM DEPT
WHERE LOC='BOSTON');
SEMI-JOIN.
SELECT * FROM DEPT
WHERE EXISTS (SELECT * FROM EMP
WHERE DEPT.ENAME=EMP.ENAME AND
EMP.BONUS > 5000);
SELF-JOIN.
SELECT A1.ENAME,A2.ENAME FROM EMP A1, EMP A2
WHERE A1.MGR=A2.EMPNO;
OUTER-JOIN.
SELECT A2.NRO_SIDIF,A1.NRO_PROV,A1.FECHA_RECEPCION FROM A1.SP50,A2.SP60
WHERE A1.LETRA_EXPEDIENTE= A2.LETRA_EXPEDIENTE(+) AND
A1.NRO_EXPEDIENTE= A2.NRO_EXPEDIENTE(+) AND
A1.NRO_REGISTRO= A2.NRO_REGISTRO(+); --Con el signo(+) trae todas las columnas del join (si no tienen datos les mueve null).
FUNCIONES DE GRUPO(GROUP BY/HAVING)
SELECT DEPTNO,MIN(SAL),MAX(SAL) FROM EMP
WHERE JOB='CLERK'
GROUP BY DEPTNO
HAVING MIN(SAL) < 1000;--SELECCIONA MIN/MAX SALARIO PARA EMPLEADOS EN C/DEPTO. CUYO SALARIO MINIMO SEA < 1000
EJ.
SELECT E1.NAME,E1.SAL
FROM EMP E1,DEPT, EMP E2
WHERE E1.DEPTNO=DEPT.DEPTNO AND
E1.DEPTNO=E2.DEPTNO
GROUP BY E1.NAME,E1.SAL
HAVING E1.SAL=MIN(E2.SAL); --RESTRIGE LOS GRUPOS DE FILAS PARA LOS CUALES LA CONDICION ES VERDADERA. SI SE OMITE, ORACLE RETORNA LA SUMATORIA DE FILAS DE TODOS LOS GRUPOS (AFECTA A LOS CAMPOS DEL GROUP BY)
SUBQUERIES
UPDATE SGP.SP50 SET DESTINO_UC=DESTINO_UC
WHERE OPERACION='COM' AND IMPORTE > 0 AND
'N' = (SELECT RAE FROM SGP.SP20
WHERE SGP.SP20.FUENTE_FINANC = SGP.SP50.FUENTE_FINANC) AND
TRUNC(FECHA_RECEPCION,'MONTH')='01/01/2001';
SENTENCIAS DML (SELECT/INSERT/UPDATE/DELETE).
EJ.
SELECT * FROM EMP;
INSERT INTO EMP VALUES(1234,'DAVIS',7698);
UPDATE EMP SET NOMBRE='DAVID';
DELETE FROM EMP WHERE NOMBRE='DAVID';
HIERARCHICAL QUERY CLAUSE
SELECT LPAD(' ',2*(LEVEL-1))||ENAME ORG.CHART,EMPNO,MGR,JOB
FROM EMP
START WITH JOB='PRESIDENT' --INDICA LA FILA PPAL.
CONNECT BY EMPO=MGR;--DETERMINA LA RELACION ENTRE PADRES/HIJOS
SENTENCIAS DDL(CREATE/ALTER/DROP/RENAME/TRUNCATE/
ANALYZE/GRANT/REVOKE/AUDIT/NOAUDIT).
Las sentencias DDL poseen commit implícito.
EJ.
CREATE TABLE.SPXXX.(NAME VARCHAR2(10),NRO NUMBER(2));
DROP TABLE SPXXX;
DROP VIEW VIEW_DATA;--VIEW_DATA ES UNA VISTA
GRANT SELECT ON SPXXXX TO SCOTT; -- SCOTT ES UN USUARIO
REVOKE DELETE ON SPXXXX FROM SCOTT;
ALTER TABLE SP100_RESPONSABLES ADD EJERCICIO NUMBER(4);
ALTER TABLE SP100_RESPONSABLES MODIFY EJERCICIO NUMBER(4) NOT NULL;
ALTER TABLE SP100_RESPONSABLES DROP CONSTRAINT SP100_PKEY;
ALTER TABLE SP100_RESPONSABLES ADD CONSTRAINT SP100_PKEY
PRIMARY KEY (EJERCICIO,DESTINO_UC);
TRUNCATE TABLE SP100_RESPONSABLES --BORRA LA TABLA MANTENIENDO PRIV.GRANTEOS,CONSTRAINTS.
CONTROL TRANSACTIONS.
EJ.
COMMIT;
ROLLBACK;
SAVEPOINT UPDATE_SP10;(Setea un punto al cual se puede realizar un rollback)
UPDATE SGP.SP10 SET IMPORTE=1000
WHERE NRO_PROV=1546;
ROLLBACK TO SAVEPOINT UPDATE_SP10;(VUELVE ATRÁS EL UPDATE DE SP10).
SET TRANSACTION.;(Establece propiedades para una transacción. Afecta sólo a la corriente transacción, no a otros usuarios ni otras transacciones. La transacción finaliza con una sentecia COMMIT o ROLLBACK).
SET TRANSACTION READ ONLY;
SET TRANSACTION READ WRITE;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION USE ROLLBACK SEGMENTE ...;
SESSION CONTROL.
EJ.
ALTER SESSION.Especifica o modifica condiciones o parámetros que afecten su conexión a la Base. Las especifiaciones son válidas hasta la desconexión.
ALTER SESSION ENABLE COMMIT IN PROCEDURE;
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION ADVISE COMMIT;
ALTER SESSION ADVISE ROLLBACK;
ALTER SESSION ADVISE NOTHING;
SET ROLE .Habilita/Deshabilita roles para la sesión corriente.
SET ROLE RPRESUP IDENTIFIED BY usu_sgp;--Habilita el rol RPRESUP con la password usu_sgp para la corriente sesión.
SET ROLE ALL;-- Habilita todos los roles para la corriente sesión.
SET ROLE ALL EXCEPT BANKER;-- Habilita todos los roles para la corriente sesión excepto el rol banker.
SET ROLE NONE;--Deshabilita todos los roles para la sesión corriente, incluyendo el rol por default.
CREACION Y MANEJO DE TABLAS
CREATE TABLE HT0517_CONCEPTOS_VALIDACION (
MATRICULA NUMBER(7) NOT NULL,
CONCEPTO NUMBER(4) NOT NULL,
FECHA_DESDE DATE NOT NULL,
FECHA_HASTA DATE NOT NULL,
CARGO_REINTEGRO NUMBER(1), -- 1=NORMAL, -1=CARGO/REINTEGRO
CANTIDAD NUMBER(10,2),
VALOR_UNITARIO NUMBER(10,2),
IMPORTE NUMBER(12,2),
NIVEL NUMBER(3),
TIPO NUMBER(2),
NRO_RENGLON NUMBER(4),
USUARIO VARCHAR2(20),
FECHA_ULT_MOD DATE,
--> CONSTRAINTS
CONSTRAINTS
CONSTRAINT HC051701 CHECK (FECHA_HASTA >= FECHA_DESDE),
CONSTRAINT HC051702 CHECK (CARGO_REINTEGRO IN (1, -1)));
CONSTRAINT HY1103 PRIMARY KEY (GRUPO_PAGO, CONCEPTO, FECHA_DESDE)
CONSTRAINT HK110301 FOREIGN KEY (GRUPO_PAGO) REFERENCES HT0121 (GRUPO_PAGO),
CONSTRAINT SP25_UKEY UNIQUE (NRO_PROV),
CREATE SYNONYM HT0311 FOR HT0311_CONC_LIQUIDAR_MANIOBRA;
GRANT INSERT ON SP69 TO RPRESUP;
GRANT SELECT ON SP69 TO RPRESUP;
GRANT DELETE ON SP69 TO RPRESUP;
CREACION/EXPLOTACION VISTAS
CREATE VIEW EMPLEADOS(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) AS
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP1
UNION
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP2;
DICCIONARIO DE DATOS
Es un conjunto de tablas de sólo lectura que provee información acerca de su base de datos asociada. Contiene: -Definición de objetos del esquema en la base (tablas, vistas, índices, clusters, sinónimos, secuencias, procedimientos, funciones, paquetes, triggers). -Cuánto espacio ha sido alocado por los objetos y cuánto es utilizado por ellos. -Valores por default para las columnas.
-Información de los constraints de integridad. -Nombre de usuarios de Oracle.
-Privilegios y roles para cada usuario que haya sido granteado.
El Diccionario de Datos se crea cuando se crea la Base. Oracle lee en él para verificar que el objeto del esquema exista y que el usuario tenga las propiedades de acceso a él. Está contenido en el tablespace SYSTEM, el cual siempre está en línea. En la tabla DBA_ROLLBACK_SEGS del diccionario, se encuentra la información de cada segmento de rollback. Oracle lo modifica cada vez que una sentencia DDL es ejecutada. Puede ser consultado por cualquier usuario Oracle como referencia de sólo lectura.
Para acceder a las vistas del Diccionario, el usuario lo hace a través de SQL. Algunas vistas están disponibles para todos los usuarios, otras sólo para el DBA. Los prefijos de las vistas son: USER(vistas del usuario-en su esquema)
ALL(vistas expandidas del usuario)
DBA(vistas del DBA)
-Consultas al Diccionario de Datos
Prefijo USER
SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS; --Objetos contenidos en el esquema del usuario.
Prefijo ALL
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS; --Todos los objetos a los cuales tiene acceso contenidos en el esquema del usuario.
Prefijo DBA
Los sinónimos no son creados para estas vistas, porque deben ser consultadas sólo por el Administrador. Por lo tanto, para consultarlas los DBA deben prefijarlas con su dueño, SYS.
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM SYS.DBA_OBJECTS;
SELECT DATA_OBJECT_ID FROM DBA_OBJECTS
WHERE OWNER='SCOTT' AND OBJECT_NAME='EMP';--Retorna el nº de objetos de datos para la tabla EMP en el esquema SCOTT.
SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS;
OBJETOS DE BASE DE DATOS 20220w222u
Asociado con cada usuario de base de datos está el Esquema. Un esquema es una colección de objetos de esquema(estructuras lógicas que directamente referencian a datos de la base) tales como tablas, secuencias, store_procedures, sinónimos, índices, clusters, database links, snapshots, procedimientos, funciones y paquetes.
-Tabla. Es la únidad básica de almacenamiento de datos en una base Oracle. Posee filas y columnas. Cada tabla es definida con un nombre y un conjunto de columnas. Cada columna tiene un nombre, un datatype y una longitud.
-Secuencia. Genera una lista de números únicos para columnas numéricas de una tabla. Los números de secuencia son independientes de las tablas. Después de la creación una secuencia puede ser accedida por varios usuarios para generar números actuales de secuencia.
-Procedimiento o función. Conjunto de estructuras SQL y PL/SQL agrupadas en una unidad ejecutable para realizar tareas específicas. Las funciones retornan un valor, los procedimientos no.
-Sinónimo. Es un alias para tablas, vistas, secuencias o unidades de programas. No es un objeto de esquema por sí mismo, pero es una referencia directa a éste.
Son usados para enmarcar el verdadero nombre y dueño del objeto del esquema. Provee accesos públicos, simplifica las sentencias SQL.
-Indices. Son estructuras opcionales asociadas con tablas los cuales son creados para incrementar la performance en la recuperación de datos. Son creados en una o más columnas de una tabla. Son mantenidos y usados automáticamente por Oracle. Los cambios a los datos de la tabla son automáticamente incorporados a los índices pertinentes con completa transparencia para el usuario.
SEGURIDAD - CONTROL DE ACCESO DE USUARIOS(GRANT/REVOKE)
PRIVILEGIOS
El privilegio es un derecho a ejecutar un tipo de sentencia sql.Algunos privilegios incluyen: -derecho de conexión a la base(create a session)
-derecho a crear una tabla en su esquema
-derecho a seleccionar filas de alguna tabla
-derecho a ejecutar algún store_procedure
Existen privilegios de sistema y privilegios de objetos, éstos últimos permiten a los usuarios ejecutar una acción particular sobre un objeto específico(tablas,vistas,secuencias,procedimientos,funciones,paquetes,etc.).
El granteo a objetos también afecta a su sinónimo. Si se dropea el sinónimo, sus granteos quedan sin efecto.
GRANTEO DE PRIVILEGIOS
Los privilegios son granteados a usuarios para que puedan acceder y modificar datos en la base. Un usuario puede recibir un privilegio de dos maneras diferentes:
-Granteados a usuarios explícitamente.
Ej.
GRANT SELECT ON EMP TO SCOTT; -- SCOTT ES UN USUARIO
-Granteados a roles(grupos de privilegios) y entonces el rol es granteado a uno o más usuarios.
--OBJECT PRIVILEGES
Ej.
GRANT INSERT ON SP69 TO RPRESUP; -- RPRESUP ES UN ROL
GRANT ALL ON SP69 TO AGUSTINA; --GRANTEA DELETE,INSERT,SELECT,UPDATE AL USUARIO
REVOKE DELETE ON SP69 FROM AGUSTINA;--REVOCA EL PRIVILEGIO DE DELETE
REVOKE ALL ON SP69 FROM AGUSTINA;
GRANT ALL ON BONUS TO JONES WITH GRANT OPTION--El usuario Jones puede ejercitar todos los privilegios de la Tabla Bonus y Grantear algún privilegio de ésta a otros usuarios o roles.
GRANT SELECT,UPDATE ON CUTE TO PUBLIC;--GRANTEA LA VISTA CUTE A TODOS LOS USUARIOS
REVOKE UPDATE ON CUTE FROM PUBLIC;--REVOCA EL PRIVILEGIO DE LA VISTA A TODOS LOS USUARIOS
--SYSTEM PRIVILEGES
EJ.
GRANT CREATE ANY LIBRARY TO SGP WITH ADMIN OPTION.
GRANT CREATE ANY PROCEDURE TO SGP WITH ADMIN OPTION.
GRANT CREATE ANY SEQUENCE TO SGP WITH ADMIN OPTION.
GRANT CREATE ANY SYNONYM TO SGP WITH ADMIN OPTION.
GRANT EXECUTE ANY LIBRARY TO SGP WITH ADMIN OPTION.
GRANT PROCEDURE TYPE TO SGP.
GRANT LIBRARY TYPE TO SGP.
GRANT CREATE TYPE TO SGP.
15. DECLARACION DE VARIABLES,EJECUCION BLOCKS DE PL/SQL
16. DECLARACION DE DATATYPE
ESTRUCTURAS DE CONTROL (IF/FOR)
IF.. THEN . ELSE..END IF;
FOR I IN 1..VAR LOOP
...
END LOOP;
WHILE :SYSTEM.LAST_RECORD = 'FALSE'
LOOP
WRECORD := TO_NUMBER(:SYSTEM.CURSOR_RECORD);
WNRO_PROVE := :ST15_PROVE_INVITADOS.NRO_PROVE;
WNRO_BENEF := :ST15_PROVE_INVITADOS.NRO_BENEF;
FIRST_RECORD;
LOOP
IF WNRO_PROVE = :ST15_PROVE_INVITADOS.NRO_PROVE AND
WNRO_BENEF = :ST15_PROVE_INVITADOS.NRO_BENEF AND
WRECORD <> TO_NUMBER(:SYSTEM.CURSOR_RECORD) THEN
WEXISTE:=1;
END IF;
IF :SYSTEM.LAST_RECORD = 'TRUE' OR WEXISTE = 1 THEN
EXIT;
END IF;
NEXT_RECORD;
END LOOP;
IF WEXISTE = 1 THEN
CLEAR_MESSAGE;
MESSAGE ('PROVEEDOR YA INTEGRA LA LISTA');
WEXISTE := 0;
RAISE FORM_TRIGGER_FAILURE;
END IF;
WEXISTE := 0;
GO_RECORD(WRECORD + 1);
WRECORD := TO_NUMBER(:SYSTEM.CURSOR_RECORD);
END LOOP;
ARRAY/VARRAY
Es un conjunto ordenado de datos de un mismo tipo. Cada elemento tiene un índice el cual es el número correspondiente a la posición en el array. El número de elementos en el array es la longitud del array. Oracle permite arrays de longitud variable los cuales se llaman varrays. Se debe especificar la longitud máxima cuando se declara el array. Inicialmente el varray es nulo.
CREATE TYPE PRICES AS VARRAY(10) OF NUMBER(12,2);
El varray de prices no tiene más de 10 elementos cada uno de tipo number(12,2).
DECLARE
TYPE NUMEROS IS VARRAY(10) OF NUMBER(3);
NRO NUMEROS;
I NUMBER(3):=9;
BEGIN
NRO:=NUMEROS(10,2,3,4,5,8,6,9,20,100);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(NRO(I)));
--LA SALIDA SERA 20
END;
OTROS TIPOS DE DECLARACION DE VARRAY.
TYPE CALENDARIO IS VARRAY(365) OF DATE; --BASADO EN DATE
CURSOR C1_DEPT IS
SELECT * FROM DEPT;
TYPE LISTA_EMP IS VARRAY(20) OF C1_DEPT%ROWTYPE; --BASADO EN UN CURSOR
TYPE REG IS RECORD(
CAMPO1 VARCHAR2(20),
CAMPO2 VARCHAR2(40));
TYPE GLOSARIO IS VARRAY(250) OF REG; --BASADO EN UN REGISTRO
NESTED_TABLE:
CREATE TYPE AGU AS TABLE OF TABLA;
TYPE AGU IS TABLE OF TABLA;
CREACION DE REGISTROS/TABLA DE REGISTROS/%ROWTYPE
Un registro es un grupo de items de datos relacionados entre sí almacenados en campos cada uno de los cuales tiene nombre y datatype. Los registros facilitan la organización y representación de la información.
El atributo %ROWTYPE permite declarar un registro que representa un fila en la tabla de base de datos.
A diferencia de tablas y varrays, los registros no pueden ser creados y almacenados en la base.
Creación de Registros.
EJ.
DECLARE
TYPE REG_SP50 IS RECORD (
EJERCICIO SGP.SP50.EJERCICIO%TYPE,
NRO_EXPEDIENTE SGP.SP50.NRO_EXPEDIENTE%TYPE,
LETRA_EXPEDIENTE SGP.SP50.LETRA_EXPEDIENTE%TYPE);
Una vez definido el registro se pueden declarar registros de ese tipo.
EJ.
DECLARE
REGISTRO1 REG_SP50;
Se pueden usar como parámetros de funciones y procedimientos.
EJ.
PROCEDURE EMPLEADOS(PARAM1 REG_SP50);
Se pueden anidar registros, es decir, un registro puede ser componente de otro registro.
EJ.
DECLARE
TYPE REG_SP50 IS RECORD (EJERCICIO SGP.SP50.EJERCICIO%TYPE,
NRO_EXPEDIENTE SGP.SP50.NRO_EXPEDIENTE%TYPE,
LETRA_EXPEDIENTE SGP.SP50.LETRA_EXPEDIENTE%TYPE);
TYPE REG_CUTE IS RECORD (NUMERO NUMBER(5),
FECHA DATE,
CUTE REG_SP50);--REGISTRO ANIDADO
Atributo %ROWTYPE.
DECLARE
REG1 SGP.SP50%ROWTYPE;
CURSORES (IMPLICITOS/EXPLICITOS)(PARAMETROS EN CURSORES/FOR UPDATE)
PL/SQL usa dos tipos de cursores: implícitos y explícitos. PL/SQL declara
Cursores implícitos para todas las sentencias de manipulación de datos incluyendo queries que retornen una sola fila. Para queries que retornen más de una fila se deben declarar cursores explícitos o usar un cursor FOR LOOP.
Para el control de cursores se usan los siguientes comandos: OPEN,FETCH,CLOSE.
Cursores implícitos.
SQL%FOUND
SQL%ISOPEN
SQL%NOTFOUND
SQL%ROWCOUNT
EJ.
UPDATE SGP.SP78 SET EJEC_COMPROMISO = NVL(EJEC_COMPROMISO,0) - WAFECTA,
WHERE EJERCICIO = :OLD.EJERCICIO AND FUENTE_FINANC = :OLD.FUENTE_FINANC
AND ORG_TEC = :OLD.ORG_TEC AND SUB_CTA = :OLD.SUB_CTA
AND TRUNC(MES_ANIO,'MONTH') = TRUNC(:OLD.FECHA_RECEPCION,'MONTH');
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20101,'SALDOS RAE ANTERIORES INEXISTENTES');
END IF;
Declaración y explotación de cursores explícitos.
DECLARE
WLOW NUMBER(2);
WHIGH NUMBER(2);
REG SGP.SP50%ROWTYPE;
CURSOR C1 (LOW INTEGER DEFAULT 0, --LOW HIGH SON PARAMETROS FORMALES
HIGH INTEGER DEFAULT 99)
RETURN REG IS
SELECT * FROM SGP.SP50
WHERE LOW < 100 AND
HIGH < 1000;
BEGIN
OPEN C1(WLOW,WHIGH); --WLOW/WHIGH SON LOS PARAM.ACTUALES QUE TOMAN EL
LOOP --VALOR DE LAS VARIABLES DEL MISMO NOMBRE.
FETCH C1 INTO REG;
EXIT WHEN C1%NOTFOUND;
..
...
END LOOP;
CLOSE C1;
END;
Uso de cursores en subqueries.
DECLARE
CURSOR C1 IS
SELECT T1.DEPTNO, DNAME, "STAFF"
FROM DEPT T1,
(SELECT DEPTNO, COUNT(*) "STAFF" FROM EMP GROUP BY DEPTNO) T2
WHERE T1.DEPTNO=T2.DEPTNO AND
"STAFF" >= 5;
Parámetros en cursores.
Para pasar parámetros a un cursor se utiliza la sentencia OPEN. A menos que acepte los valores por default, cada parámetro formal en la declaración del cursor debe tener su parámetro actual correspondiente en la sentencia OPEN.
Cursores FOR LOOP.
Un cursor FOR LOOP declara implícitamente un índice de loop como un registro %ROWTYPE, abre el cursor, fechea repetidamente las filas del registro y cierra el cursor cuando todas las filas han sido procesadas.
Cuando un cursor FOR LOOP es ingresado, su nombre no puede pertenecer a otro que haya sido abierto con la sentencia OPEN o que esté incluído en otro cursor FOR LOOP. Antes de cada iteración FOR LOOP, PL/SQL fechea el cursor en el registro declarado implícitamente(C1_REC) el cual es equivalente a la declaración explícita siguiente:C1_REC C1%ROWTYPE. El registro es definido solamente dentro del loop. No se pueden referenciar campos de éste fuera del loop. La secuencia de sentencias en el loop es ejecutada un vez por cada fila que satisface al query asociado al cursor. Cuando se abandona el loop el cursor se cierra automáticamente o hasta que encuentre una sentencia EXIT
O GOTO o abandone el loop prematuramente por una excepción provocada dentro del loop.
EJ.
DECLARE
CURSOR C1 IS
SELECT N1,N2,N3 FROM DATA_TABLE
WHERE EXPER_NUM =1;
BEGIN
FOR C1_REC IN C1
LOOP
RESULT:=C1_REC.N2 / (C1_REC.N1 + C1_REC.N3);
END LOOP;
END;
EXCEPCIONES
Existen dos tipos de excepciones: Predefinidas y
Definidas por el Usuario
Excepciones predefinidas. Forman un paquete standard, se levantan solas.
Ej.
COLLECTION_IS_NULL(Cuando se le asigna valores a VARRAYS sin inicializar).
CURSOR_ALREADY_OPEN(Al abrir un cursor ya abierto).
LOGIN_DENIED(Usuario incorrecto).
TOO_MANY_ROWS(La select arroja más de una fila).
Excepciones Definidas por el Usuario. Las levanta el usuario con la sentencia RAISE.
EJ.
BEGIN
DECLARE
DIA_INVALIDO EXCEPTION;
MES_INVALIDO EXCEPTION;
AÑO_INVALIDO EXCEPTION;
CARACTER_INVALIDO EXCEPTION;
CARACTER_NOVALIDO EXCEPTION;
PRAGMA EXCEPTION_INIT(DIA_INVALIDO,-01847);
PRAGMA EXCEPTION_INIT(MES_INVALIDO,-01843);
PRAGMA EXCEPTION_INIT(AÑO_INVALIDO,-01841);
PRAGMA EXCEPTION_INIT(CARACTER_INVALIDO,-01858);
PRAGMA EXCEPTION_INIT(CARACTER_NOVALIDO,-01830);
BEGIN
REG_SP50.FECHA_DOC:= TO_DATE(REG_SP51.FECHA_DOC,'DD/MM/RR');
EXCEPTION WHEN DIA_INVALIDO THEN
WMENSAJE:=' DIA NO VALIDO EN FECHA DOCUMENTO';
IF LENGTH(NVL(WMENSAJE_ERROR,' ')) + LENGTH(NVL(WMENSAJE,' ')) < 1000 THEN
WMENSAJE_ERROR:=WMENSAJE_ERROR||CHR(13)||WMENSAJE;
END IF;
WHEN MES_INVALIDO THEN
WMENSAJE:=' MES NO VALIDO EN FECHA DOCUMENTO';
IF LENGTH(NVL(WMENSAJE_ERROR,' ')) +LENGTH(NVL(WMENSAJE,' ')) < 1000 THEN
WMENSAJE_ERROR:=WMENSAJE_ERROR||CHR(13)||WMENSAJE;
END IF;
WHEN AÑO_INVALIDO THEN
WMENSAJE:= ' AÑO NO VALIDO EN FECHA DOCUMENTO';
IF LENGTH(NVL(WMENSAJE_ERROR,' ')) + LENGTH(NVL(WMENSAJE,' ')) < 1000 THEN
WMENSAJE_ERROR:=WMENSAJE_ERROR||CHR(13)||WMENSAJE;
END IF;
WHEN CARACTER_INVALIDO THEN
WMENSAJE:=' CARACTER NO VALIDO EN FECHA DOCUMENTO';
IF LENGTH(NVL(WMENSAJE_ERROR,' '))+LENGTH(NVL(WMENSAJE,' ')) < 1000 THEN
WMENSAJE_ERROR:=WMENSAJE_ERROR||CHR(13)||WMENSAJE;
END IF;
WHEN CARACTER_NOVALIDO THEN
WMENSAJE:=' CARACTER NO VALIDO EN FECHA DOCUMENTO';
IF LENGTH(NVL(WMENSAJE_ERROR,' ')) + LENGTH(NVL(WMENSAJE,' ')) < 1000 THEN
WMENSAJE_ERROR:=WMENSAJE_ERROR||CHR(13)||WMENSAJE;
END IF;
END;
END;
SENTENCIAS LOOP
Existen tres tipos: LOOP. END LOOP
WHILE.LOOP
FOR.LOOP
Para salir del loop se utiliza la sentencia EXIT/EXIT WHEN, esta última se ejecuta cuano se cumple la condición WHEN.
EJ.
OPEN C1;
LOOP
FETCH C1 INTO REG;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
El loop puede tener etiquetas
EJ.
<<ETIQUETA>>
LOOP
SENTENCIAS..
END LOOP;
Excepcionalmente las etiquetas se ponen en el END LOOP.
EJ.
<<ETIQUETA>>
LOOP
SENTENCIAS..
END LOOP ETIQUETA;
Loops anidados:
<<OUTER>>
LOOP
..
LOOP
..
EXIT OUTER WHEN .. --SALE DE AMBOS LOOPS
END LOOP;
...
END LOOP OUTER;
SQL DINAMICO
TABLA COLS.TABLE_NAME%TYPE;
COLUMNA COLS.COLUMN_NAME%TYPE;
--CURSOR QUE BUSCA EL NOMBRE DE LA TABLA Y LA COLUMNA
CURSOR C1 IS SELECT TABLE_NAME, COLUMN_NAME FROM COLS
WHERE UPPER(COLUMN_NAME) LIKE 'LIQUIDADO%';
--VARIABLES PARA SQL DINAMICO
V7 INTEGER:=2;
W_CUR INTEGER;
W_RES INTEGER;
WUPDATE VARCHAR2(500);
WVAR VARCHAR2(20);
WX VARCHAR2(50);
BEGIN
OPEN C1;
W_CUR:=DBMS_SQL.OPEN_CURSOR;
LOOP
FETCH C1 INTO TABLA, COLUMNA;
IF C1%NOTFOUND THEN
EXIT; --SALE CUANDO NO SE ENCUENTRAN MAS TABLAS
END IF;
BEGIN
WVAR:='HG'||SUBSTR(RTRIM(TABLA),3,4)||'%';
SELECT TRIGGER_NAME INTO WX FROM USER_TRIGGERS WHERE TRIGGER_NAME LIKE WVAR;
DBMS_SQL.PARSE(W_CUR,'ALTER TRIGGER '||WX||' DISABLE',V7);
W_RES:=DBMS_SQL.EXECUTE(W_CUR);
EXCEPTION WHEN NO_DATA_FOUND THEN
WX:=NULL;
END;
WUPDATE:='UPDATE '||RTRIM(TABLA)||
' SET '
||RTRIM(COLUMNA)||
' = ''S'' WHERE '
||RTRIM(COLUMNA)||
' <> ''S'' ';
DBMS_OUTPUT.PUT_LINE(WUPDATE);
DBMS_SQL.PARSE(W_CUR,WUPDATE,V7);
W_RES:=DBMS_SQL.EXECUTE(W_CUR);
IF WX IS NOT NULL THEN
DBMS_SQL.PARSE(W_CUR,'ALTER TRIGGER '||WX||' ENABLE',V7);
W_RES:=DBMS_SQL.EXECUTE(W_CUR);
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(W_CUR);
CLOSE C1;
Naming Conventions
PL/SQL User's Guide and Reference
Synonyms
You can create synonyms to provide location transparency for remote schema
objects such as tables, sequences, views, stand-alone subprograms, and packages.
However, you cannot create synonyms for items declared within subprograms or
packages. That includes constants, variables, cursors, cursor variables, exceptions,
and packaged subprograms.
Scoping
Within the same scope, all declared identifiers must be unique. So, even if their
datatypes differ, variables and parameters cannot share the same name. For
example, two of the following declarations are illegal:
DECLARE
valid_id BOOLEAN;
valid_id VARCHAR2(5); -- illegal duplicate identifier
FUNCTION bonus (valid_id IN INTEGER) RETURN REAL IS ...
-- illegal triplicate identifier
For the scoping rules that apply to identifiers, see "Scope and Visibility" on
page 2-37.
Case Sensitivity
Like all identifiers, the names of constants, variables, and parameters are not case
sensitive. For instance, PL/SQL considers the following names to be the same:
DECLARE
zip_code INTEGER;
Zip_Code INTEGER; -- same as zip_code
Name Resolution
In potentially ambiguous SQL statements, the names of database columns take
precedence over the names of local variables and formal parameters. For example,
the following DELETE statement removes all employees from the emp table, not just
'KING', because Oracle assumes that both enames in the WHERE clause refer to the
database column:
DECLARE
ename VARCHAR2(10) := 'KING';
BEGIN
DELETE FROM emp WHERE ename = ename;
Scope and Visibility
Fundamentals 2-37
In such cases, to avoid ambiguity, prefix the names of local variables and formal
parameters with my_, as follows:
DECLARE
my_ename VARCHAR2(10);
Or, use a block label to qualify references, as in
<<main>>
DECLARE
ename VARCHAR2(10) := 'KING';
BEGIN
DELETE FROM emp WHERE ename = main.ename;
The next example shows that you can use a subprogram name to qualify references
to local variables and formal parameters:
FUNCTION bonus (deptno IN NUMBER, ...) RETURN REAL IS
job CHAR(10);
BEGIN
SELECT ... WHERE deptno = bonus.deptno AND job = bonus.job;
For a full discussion of name resolution, see Appendix D.
Scope and Visibility
References to an identifier are resolved according to its scope and visibility. The
scope of an identifier is that region of a program unit (block, subprogram, or
package) from which you can reference the identifier. An identifier is visible only in
the regions from which you can reference the identifier using an unqualified name.
Figure 2-2 shows the scope and visibility of a variable named x, which is declared
in an enclosing block, then redeclared in a sub-block.
Identifiers declared in a PL/SQL block are considered local to that block and global
to all its sub-blocks. If a global identifier is redeclared in a sub-block, both
identifiers remain in scope. Within the sub-block, however, only the local identifier
is visible because you must use a qualified name to reference the global identifier.
Although you cannot declare an identifier twice in the same block, you can declare
the same identifier in two different blocks. The two items represented by the
identifier are distinct, and any change in one does not affect the other. However, a
block cannot reference identifiers declared in other blocks at the same level because
those identifiers are neither local nor global to the block.
Scope and Visibility
PL/SQL User's Guide and Reference
Scope and Visibility
PL/SQL User's Guide and Reference
Figure 2-2 Scope and Visibility
The example below illustrates the scope rules. Notice that the identifiers declared in
one sub-block cannot be referenced in the other sub-block. That is because a block
cannot reference identifiers declared in other blocks nested at the same level.
DECLARE
a CHAR;
b REAL;
BEGIN
-- identifiers available here: a (CHAR), b
DECLARE
a INTEGER;
c REAL;
BEGIN
-- identifiers available here: a (INTEGER), b, c
END;
Scope Visibility
Outer x
Inner x
DECLARE
X REAL;
BEGIN
DECLARE
X REAL;
BEGIN
END;
DECLARE
XREAL;
BEGIN
DECLARE
XREAL;
BEGIN
END;
END; END;
DECLARE
X REAL;
BEGIN
DECLARE
X REAL;
BEGIN
END;
END;
DECLARE
X REAL;
BEGIN
DECLARE
X REAL;
BEGIN
END;
END;
Scope and Visibility
Fundamentals 2-39
DECLARE
d REAL;
BEGIN
-- identifiers available here: a (CHAR), b, d
END;
-- identifiers available here: a (CHAR), b
END;
Recall that global identifiers can be redeclared in a sub-block, in which case the local
declaration prevails and the sub-block cannot reference the global identifier unless
you use a qualified name. The qualifier can be the label of an enclosing block, as the
following example shows:
<<outer>>
DECLARE
birthdate DATE;
BEGIN
DECLARE
birthdate DATE;
BEGIN
IF birthdate = outer.birthdate THEN ...
As the next example shows, the qualifier can also be the name of an enclosing
subprogram:
PROCEDURE check_credit (...) IS
rating NUMBER;
FUNCTION valid (...) RETURN BOOLEAN IS
rating NUMBER;
BEGIN
IF check_credit.rating < 3 THEN ...
However, within the same scope, a label and a subprogram cannot have the same
name.
Assignments
PL/SQL User's Guide and Reference
Assignments
Variables and constants are initialized every time a block or subprogram is entered.
By default, variables are initialized to NULL. So, unless you expressly initialize a
variable, its value is undefined, as the following example shows:
DECLARE
count INTEGER;
BEGIN
count := count + 1; -- assigns a null to count
The expression on the right of the assignment operator yields NULL because count
is null. To avoid unexpected results, never reference a variable before you assign it a
value.
You can use assignment statements to assign values to a variable. For example, the
following statement assigns a new value to the variable bonus, overwriting its old
value:
bonus := salary * 0.15;
The expression following the assignment operator can be arbitrarily complex, but it
must yield a datatype that is the same as or convertible to the datatype of the
variable.
Boolean Values
Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. For
example, given the declaration
DECLARE
done BOOLEAN;
the following statements are legal:
BEGIN
done := FALSE;
WHILE NOT done LOOP
END LOOP;
When applied to an expression, the relational operators return a Boolean value. So,
the following assignment is legal:
done := (count > 500);
Expressions and Comparisons
Fundamentals 2-43
Logical Operators
The logical operators AND, OR, and NOT follow the tri-state logic shown in Table 2-
AND and OR are binary operators; NOT is a unary operator.
As the truth table shows, AND returns TRUE only if both its operands are true. On
the other hand, OR returns TRUE if either of its operands is true. NOT returns the
opposite value (logical negation) of its operand. For example, NOT TRUE returns
FALSE.
NOT NULL returns NULL because nulls are indeterminate. It follows that if you apply
the NOT operator to a null, the result is also indeterminate. Be careful. Nulls can
cause unexpected results; see "Handling Nulls" on page 2-48.
Order of Evaluation
When you do not use parentheses to specify the order of evaluation, operator
precedence determines the order. Compare the following expressions:
NOT (valid AND done) | NOT valid AND done
If the Boolean variables valid and done have the value FALSE, the first expression
yields TRUE. However, the second expression yields FALSE because NOT has a
higher precedence than AND. Therefore, the second expression is equivalent to:
(NOT valid) AND done
Datatypes
Fundamentals 2-15
LONG and LONG RAW
You use the LONG datatype to store variable-length character strings. The LONG
datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG
value is 32760 bytes.
You use the LONG RAW datatype to store binary data or byte strings. LONG RAW data
is like LONG data, except that LONG RAW data is not interpreted by PL/SQL. The
maximum length of a LONG RAW value is 32760 bytes.
You can insert any LONG value into a LONG database column because the maximum
width of a LONG column is 2147483647 bytes. However, you cannot retrieve a value
longer than 32760 bytes from a LONG column into a LONG variable.
Likewise, you can insert any LONG RAW value into a LONG RAW database column
because the maximum width of a LONG RAW column is 2147483647 bytes. However,
you cannot retrieve a value longer than 32760 bytes from a LONG RAW column into a
LONG RAW variable.
LONG columns can store text, arrays of characters, or even short documents. You can
reference LONG columns in UPDATE, INSERT, and (most) SELECT statements, but
not in expressions, SQL function calls, or certain SQL clauses such as WHERE, GROUP
BY, and CONNECT BY. For more information, see Oracle8i SQL Reference.
RAW
You use the RAW datatype to store binary data or byte strings. For example, a RAW
variable might store a sequence of graphics characters or a digitized picture. Raw
data is like VARCHAR2 data, except that PL/SQL does not interpret raw data.
Likewise, Net8 does no character set conversions when you transmit raw data from
one system to another.
The RAW datatype takes a required parameter that lets you specify a maximum
length up to 32767 bytes. The syntax follows:
RAW(maximum_length)
You cannot use a constant or variable to specify the maximum length; you must use
an integer literal in the range 1 .. 32767.
The maximum width of a RAW database column is 2000 bytes. So, you cannot insert
RAW values longer than 2000 bytes into a RAW column. You can insert any RAW value
into a LONG RAW database column because the maximum width of a LONG RAW
column is 2147483647 bytes. However, you cannot retrieve a value longer than
32767 bytes from a LONG RAW column into a RAW variable.
Datatypes
PL/SQL User's Guide and Reference
ROWID and UROWID
Internally, every database table has a ROWID pseudocolumn, which stores binary
values called rowids. Each rowid represents the storage address of a row. A physical
rowid identifies a row in an ordinary table. A logical rowid identifies a row in an
index-organized table. The ROWID datatype can store only physical rowids.
However, the UROWID (universal rowid) datatype can store physical, logical, or
foreign (non-Oracle) rowids.
Suggestion: Use the ROWID datatype only for backward compatibility with old
applications. For new applications, use the UROWID datatype.
When you select or fetch a rowid into a UROWID variable, you can use the built-in
function ROWIDTOCHAR, which converts the binary value into an 18-byte character
string. Conversely, the function CHARTOROWID converts a UROWID character string
into a rowid. If the conversion fails because the character string does not represent a
valid rowid, PL/SQL raises the predefined exception SYS_INVALID_ROWID. This
also applies to implicit conversions.
Physical Rowids Physical rowids provide fast access to particular rows. As long as
the row exists, its physical rowid does not change. Efficient and stable, physical
rowids are useful for selecting a set of rows, operating on the whole set, and then
updating a subset. For example, you can compare a UROWID variable with the
ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to
identify the latest row fetched from a cursor. See "Fetching Across Commits" on
page 5-49.
A physical rowid can have either of two formats. The 10-byte extended rowid format
supports tablespace-relative block addresses and can identify rows in partitioned
and non-partitioned tables. The 6-byte restricted rowid format is provided for
backward compatibility.
Extended rowids use a base-64 encoding of the physical address for each row
selected. For example, in SQL*Plus (which implicitly converts rowids into character
strings), the query
SQL> SELECT rowid, ename FROM emp WHERE empno = 7788;
might return the following row:
ROWID ENAME
AAAAqcAABAAADFNAAH SCOTT
|
