Upload
patricia-molina
View
506
Download
5
Embed Size (px)
Citation preview
1
Yony Richard Montoya Burgos 1
ORACLE
ORACLE SQL - PLSQLLic. Yony Richard Montoya Burgos
Yony Richard Montoya Burgos
TEMARIO
Introducción a ORACLEAccediendo a la InformaciónLenguaje ProceduralEstructura de la BDIntegridad de Datos
Yony Richard Montoya Burgos
Conceptos BásicosDBMS: Sistema Manejador de Bases de DatosOracle esta basado en arquitectura Multi-Servidor
Unidad 1: Introducción a Oracle
2
Yony Richard Montoya Burgos
Modelamiento de DatosPermite obtener un modelamiento abstracto del problema desde el punto de vista de la información.Se obtiene un ERD (Diagrama Entidad Relación) Compuesto de Entidades y Relaciones entre las mismas.
Unidad 1: Introducción a Oracle
Yony Richard Montoya Burgos
Diseño de DatosMapeo del diagrama entidad relación.Se consideran campos resultado de las relaciones entre entidades.
Unidad 1: Introducción a Oracle
Yony Richard Montoya Burgos
Construcción de la Base de DatosMapeamiento físico del diagrama entidad relación.Se usa el lenguaje de manipulación de datos (DML) de SQL para lograrlo.
Unidad 1: Introducción a Oracle
3
Yony Richard Montoya Burgos
Notacion en OracleEntidad:
Relación:
Opcional RequeridoUno o mas
Unidad 1: Introducción a Oracle
NOMBRE
# <campo llave>* <campo requerido>º <campo opcional>
NOMBRE
# <campo llave>* <campo requerido>º <campo opcional>
NOMBRE 1
# <campo llave>* <campo requerido>º <campo opcional>
RELACION
Yony Richard Montoya Burgos
Notación en OracleEntidades:
Nombre en MAYUSCULAAtributos en MINUSCULA
RelacionesNombres en MINUSCULAManeja DEBE SER (requerido) y PUEDE SER (opcional)
Unidad 1: Introducción a Oracle
Yony Richard Montoya Burgos
EjerciciosCrear un ERD de: (ver doc adjuntos)
Unidad 1: Introducción a Oracle
4
Yony Richard Montoya Burgos
Procesos y Almacenamiento
Unidad 1: Introducción a Oracle
Yony Richard Montoya Burgos
Base de DatosDatabase Buffer: Cache para contener bloques de datos.Redo-Log-Buffer: Información sobre cambios en el Database BufferShared Pool: Compartido por todos los usuarios, compuesto por Dictionary Cache y Library Cache.
Unidad 1: Introducción a Oracle
Yony Richard Montoya Burgos
DBWR: Encargado de escribir los bloques modificados a los data files.LGWR: Encargado de escribir información a los redo-logSMON,PMON: System Monitor y Pocess Monitor
Unidad 1: Introducción a Oracle
5
Yony Richard Montoya Burgos
Estructura de la BD (lógica)Compuesta de:
Base de Datos: Compuesta de una o mas divisiones de almacenamiento.TableSpace: División Lógica de la BD.Segmento: Si se crea un objeto, automaticamente se asigna una porción del TableSpaceExtent: Unidad de almacenamiento Lógica, si se incrementa el tamaño de la BD un extent se asigna.
Unidad 1: Introducción a Oracle
Yony Richard Montoya Burgos
Estructura de la BD (física)Compuesta de:
Data Files: Archivos del TableSpace.Blocks: Un extent consiste de uno o mas bloques de datos continuos.Archivos Redo-Logs: Guradan información de las transacciones.Archivos de Control: Mantienen la información de la BD.
Unidad 1: Introducción a Oracle
Yony Richard Montoya Burgos
Arquitectura de la BD
Unidad 1: Introducción a Oracle
6
Yony Richard Montoya Burgos
Herramientas de ConsultaSQL*Plus, herramienta de consultaSoporta SQL (Structured Query Language)PL/SQL Lenguaje Procedural SQL de Oracle, posee su propio lenguaje de comandos.Isql*Plus, herramienta de consulta via Web de entornos de Oracle version 10
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Sentencias SQLSELECT: Recuperación de DatosINSERT, UPDATE, DELETE : Lenguaje de manipulación de Datos (DML)CREATE, ALTER, DROP, RENAME, TRUNCATE: Lenguaje de Definición de Datos (DDL)COMMIT, ROLLBACK, SAVEPOINT: Control de TransaccionesGRANT, REVOKE: Lenguaje de control de datos (DCL)
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
PL/SQLExtensión de SQL, con características de lenguaje de programaciónLenguajes de manipulación y consulta son incluidas
Unidad 2: Accediendo a la Inform.
7
Yony Richard Montoya Burgos
Unidad 2: Accediendo a la Inform.
Interaccion SQL y SQL* PlusBuffer
SQL*Plus
Servidor
Comandos
SQL*PlusReporte Formateado
Resultados
Sentencias SQLSentencias SQL
Yony Richard Montoya Burgos
SQL*PLUSEntorno de consultaPropietario de OracleSólo maneja una línea a la vez.
Unidad 2: Accediendo a la Inform.
sqlplus [usuario[clave[@db]]]
Yony Richard Montoya Burgos
Comandos SQL*PLUSDESC[RIBE] tabla
Muestra la estructura de la tabla
L[IST]Muestra todas la lineas del Buffer
R[RUN]Muestra y ejecuta la sentencia en el buffer
SAV[E] archivo [REP[LACE]APP[END]]Guarda el contenido del Buffer a un archivo
Unidad 2: Accediendo a la Inform.
8
Yony Richard Montoya Burgos
Comandos SQL*PLUSGET archivo
Copia la información del archivo al buffer
STA[RT] archivoEjecuta un archivo de comandos guardado
@ archivoIgual a START
ED[IT]Edita el contenido del buffer
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Comandos SQL*PLUSSPO[OL] archivo [OFF|OUT]
Almacena la información de las consultas en un archivo, OFF cierra el archivo. OUT cierra el archivo y lo imprime.
EXITSale del SQL*PLUS
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Sentencias SQLSelección
SELECT [DISTINCT] {* | columna [alias],...}FROM tabla
Selecciona un conjunto de columnas (SELECT) de una tabla (FROM).
Unidad 2: Accediendo a la Inform.
9
Yony Richard Montoya Burgos
SeleccionSELECT * FROM empleado;
Muestra todas las columnas de una tabla.SELECT nombre,apellido FROM empleado;
Selecciona sólo las columnas indicadas
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SeleccionSe soportan operaciones aritméticas
+ Suma- Resta* Multiplicación/ División
SELECT nombre,salario,salario+50FROM empleado;
La precendencia de operadores es *,/,+,- los paréntesis siempre tiene mayor precedencia
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SeleccionSELECT nombre,salario, 0.13*(salario+10) FROM empleado;
Se pueden usar parentesis para manejar la precedencia de operadores.
Unidad 2: Accediendo a la Inform.
10
Yony Richard Montoya Burgos
Unidad 2: Accediendo a la Inform.
Un valor nulo es un valor no disponible, no asignado, desconocido o no aplicable.No es un espacio en blanco o un cero.Las operaciones aritméticas con valores nulos dan como resultado un valor nulo.
SQL> SELECT ename, job, sal, comm2 FROM emp;
Yony Richard Montoya Burgos
Unidad 2: Accediendo a la Inform.
Definiendo Alias de ColumnasSe puede renombrar un encabezado de una columna.Es muy utilizado con Operaciones Aritmeticas.Se utiliza justo a lado de un nombre de columna.Necesita comillas dobles si se usanespacios en blanco, caracteresespeciales.
Yony Richard Montoya Burgos
Seleccion
Se puede o no usar [AS] , es opcional
Unidad 2: Accediendo a la Inform.
SQL> SELECT ename AS name, sal salary2 FROM emp;
SQL> SELECT ename “Name”,2 sal*12 “Annual Salary”3 FROM emp;
11
Yony Richard Montoya Burgos
Seleccion
Concatena columnas de una tabla.
Caracteres especiales se colocan entre comillas simples
Unidad 2: Accediendo a la Inform.
SQL> SELECT ename || job AS “Empleados”2 FROM emp;
SQL> SELECT ename ||‘ es un ’|| job 2 AS “Detalle Empleados”3 FROM emp;
Yony Richard Montoya Burgos
Seleccion
DISTINCT permite eliminar las filas repetidas, si se aplica a mas de una columna se toma la combinación.
Unidad 2: Accediendo a la Inform.
SQL> SELECT DISTINCT deptno2 FROM emp;
Yony Richard Montoya Burgos
EjerciciosCrear una consulta para mostrar sólo los codigos de Departamento de la tabla DeptCrear una consulta para mostrar empno,ename, job y cambiar encabezados a Codigo, Empleado y CargoCrear una consulta mostrando ename, job y hiredate de la manera siguiente: <ename> con el cargo <job> contratado el <hiredate>
Unidad 2: Accediendo a la Inform.
12
Yony Richard Montoya Burgos
Limitando FilasSELECT [DISTINCT] {* | columna [alias],...}FROM tabla[WHERE condicion];
La clausula WHERE es opcionalRestringe el número de filas de la consulta.
SELECT nombre,apellidos FROM empleado WHERE nd=5;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Limitando FilasLos campos de tipo carácter deben estar entre comillas simples.Los campos de tipo Date deben estar entre comillas simples, el formato es DD-MON-YY (formato ingles).La comparación de caracteres es sensible al contexto.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Operadores de Comparación= Igual a>, >= Mayor a, Mayor o igual a<, <= Menor a, Menor o Igual a<> DiferenteBETWEEN...AND... Entre dos valores inclusiveIN(lista) Empareja valores con la listaLIKE Empareja con un patrónIS NULL Es un valor nulo?
Unidad 2: Accediendo a la Inform.
13
Yony Richard Montoya Burgos
Operadores de ComparaciónSELECT nombre,salarioFROM empleadoWHERE salario BETWEEN 1000 AND 1500;
Límite inferior y límite superior de la consultaSimilar a salario>=1000 and salario<=1500
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Operadores de ComparaciónSELECT nombre,salario,nss_supervFROM empleadoWHERE nss_superv IN (‘888665555’,’ 333445555’);
Compara todos los valores con los indicados en la lista
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Operadores de ComparaciónLIKE ejecuta búsquedas en base a patrones:
% indica cero o mas_ indica un caracter
SELECT nombre FROM empleadoWHERE nombre LIKE ‘J%’;
Si se desea usar en la búsqueda los valores % o _ usar con el identificador \
SELECT nombre FROM empleadoWHERE nombre LIKE ‘J\_a%’;
Busca todos lo nombres que comiencen con J_a
Unidad 2: Accediendo a la Inform.
14
Yony Richard Montoya Burgos
Operadores LógicosAND Ambos componentes son verdaderosOR Verdad si alguno es TRUENOT Niega la condición
Primero re realizan los operadores de comparación, luego NOT, AND y OR (Precedencia de operadores)
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
EjerciciosCrear una consulta para mostrar todos los empleados nacidos en 1972Mostrar el nombre y el salario de los empleados que no tienen un supervisor.Mostrar los nombres y el salario de todos los empleados que estan en el departamento 5 y que ganan 40000 y 50000.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
OrdenamientoSe puede ordenar las columnas mediante la claúsula ORDER BY
ASC Orden AscendenteDESC Orden Descendente
SELECT nombre,salario,fecha_nctoFROM empleadoORDER BY fecha_ncto;
Unidad 2: Accediendo a la Inform.
15
Yony Richard Montoya Burgos
OrdenamientoSintaxis GeneralSELECT expresiónFROM tabla[WHERE condición][ORDER BY {columna, expresión} [ASC|DESC]];
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
OrdenamientoEl ordenamiento por defecto es ascendente.Se puede ordenar por nombres de encabezado.No necesariamente la columna de ordenamiento debe formar parte de la consulta.Se puede ordenar por mas de un campo.El tipo de ordenamiento es particular para cada campo.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLSe tienen funciones
Simples (afectan a una columna)
Múltiples (afectan a varias columnas)
Unidad 2: Accediendo a la Inform.
16
Yony Richard Montoya Burgos
Funciones SQLFunciones Simples
Manipulan datosAceptan argumentos y devuelven un valorActuan en cada fila resultadoDevuelve un único valor por filaPueden combinarse
Funcion (columna|expresión, [arg1, arg2, ...])
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLFunciones Simples
GeneralesConversiónNuméricasFechaCaracter
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLFunciones de Caracter
ConversiónLOWER(col|exp) Convierte a minúsculasUPPER(col|exp) Convierte a MayúsculasINITCAP(col|exp) 1ª en Mayúscula y las
demas minúsculas
Unidad 2: Accediendo a la Inform.
17
Yony Richard Montoya Burgos
Funciones SQLFunciones de Caracter
ManipulaciónCONCAT(exp1,exp2) Concatena dos valoresSUBSTR(exp1,m[,n]) Devuelve una subcadena de
exp1 comenzando en m, n caracteres.LENGTH(exp) Devuelve el número de
caracteresINSTR(exp,cadena) Devuelve la posición de cadena
en expLPAD(exp,n,’cadena’) Rellena un valor con ‘cadena’
hasta completar n caracteres, justificado a la derecha.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLFunciones de Caracter
También existenRPAD, REPLACE, ASCII, LTRIM, RTRIM, CHR
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLSELECT nombre,salarioFROM empleadoWHERE LOWER(nombre)=‘alicia’;----------------------------------------------SELECT UPPER(nombred) FROM departamento;
Unidad 2: Accediendo a la Inform.
18
Yony Richard Montoya Burgos
Funciones SQLSELECT nombre,salarioFROM empleadoWHERE LENGHT(nombre)=5;----------------------------------------------SELECT nombre,
CONCAT(INITCAP(‘ vive en ’),direccion) FROM empleadoWHERE INSTR(direccion,’Houston’)>0;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
EjerciciosSeleccionar los empleados cuyo código termine en 44Mostrar el nombre y el apellido (todo en mayúsculas) justificados en 80 caracteres en el formato: NOMBRE, APELLIDO.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLFunciones NuméricasROUND(exp[,n]) Redondea con n posiciones
decimales, o sin decimales si se omite nTRUNC(exp[,n]) Trunca con n posiciones decimales o
sin decimales (si se omite n)MOD(m,n) Resto de m entre n
Existen también, SIN,COS,TAN,EXP,LOG,LN,SQRT,SIGN,POWER,etc.
Unidad 2: Accediendo a la Inform.
19
Yony Richard Montoya Burgos
Funciones SQLSELECT ROUND(25.293,1) FROM DUAL;
25.3----------------------------------------------SELECT ROUND(25.293,-1) FROM DUAL;
30
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLSELECT TRUNC(36.234,0) FROM DUAL;
36----------------------------------------------SELECT TRUNC(36.234,-1) FROM DUAL;
30
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLManejo de Fechas
Oracle almacena las fechas en un formato numérico.El formato fecha por defecto es: DD-MMM-YYSe usa SYSDATE para obtener la fecha y la hora del sistema
Unidad 2: Accediendo a la Inform.
20
Yony Richard Montoya Burgos
Funciones SQLOperaciones Aritméticas con Fechas
+ numero Suma un número de días a una fecha- numero Resta un número de días a una fecha- date Devuelve el número de días entre dos fechas
SELECT nombre, fecha_cto+30 “Primer mes cumplido”FROM empleado;-----------------------------SELECT nombre, sysdate-fecha_cto “Dias Trabajados” FROM empleado;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLFunciones de FechaMONTHS_BETWEEN(d1,d2) Número de meses entre dos
fechasADD_MONTHS(date,n) Adiciona n meses calendarioa
una fechaNEXT_DAY(date,’char’) Encuentra el siguiente día de
la semana, ‘char’ indica el dia en el formato del sistemaLAST_DAY(date) Encuentra la fecha del último
día del mes de la fechaROUND(date[,’formato’]) Redondea la fecha al día mas
cercano, o al formato indicadoTRUNC(date[,’formato’]) Trunca la fecha al día mas
cercano o al especificado por formato
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQL
SELECT nombre, ROUND(fecha_cto,’MONTH’), ROUND(fecha_cto,’YEAR’)
FROM empleado;-----------------------------SELECT nombre, TRUNC(fecha_cto,’MONTH’),
TRUNC(fecha_cto,’YEAR)FROM empleado;
Unidad 2: Accediendo a la Inform.
21
Yony Richard Montoya Burgos
Funciones SQLFunciones de Conversión
Conversión de Tipos de DatosImplicitaExplicita
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLConversión de Datos Implicita
Las conversiones en la asignación son automáticas para:
La conversión es automática también para la evaluación
Unidad 2: Accediendo a la Inform.
VARCHAR2VARCHAR2DATENUMBER
A
DATENUMBERVARCHAR2 | CHARVARCHAR2 | CHAR
DE
Yony Richard Montoya Burgos
Funciones SQLConversión de Datos ExplicitaTO_CHAR(numero|fecha[,’fmt’]) Convierte un número
o fecha a carácter de acuerdo a un formato.
TO_NUMBER(char[,’fmt’]) Convierte una cadena en formato numérico de acuerdo a un formato.
TO_DATE(char[,’fmt’]) Convierte una cadena en fecha.
Unidad 2: Accediendo a la Inform.
22
Yony Richard Montoya Burgos
Funciones SQLSELECT nombre,
TO_CHAR(fecha_ncto,’MM/YY’) “Mes de Contrato”
FROM empleado;Fmt puede ser:
YYYY, YY, YEAR, MM, MONTH, MON, RMDY, DAY, WW, WDDD, DD, D, Y.YYYY
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLSe puede usar el inficador fm para suprimir ceros de la izquierda y espacios en blancoAdicionalmente se pueden manejar las horas.
AM, PM, A.M., HH, HH12, HH24, MI, SS, SSSSS
También se pueden manejar números ordinales
TH, SP, SPTH, THSP
Los simbolos de puntuación son aceptados asi como cualquier carácter encerrado entre comillas dobles “ ”
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLSELECT nombre, TO_CHAR(fecha_ncto,’fmDD
Month YYYY’) “Mes de Contrato” FROM empleado;---------------------SELECT nombre, TO_CHAR(fecha_ncto,’fmDD
“de” MONTH “de” Y,YYY’)FROM empleado;
Unidad 2: Accediendo a la Inform.
23
Yony Richard Montoya Burgos
Funciones SQLConversión de Números
9 Indica un número0 Fuerza a mostrar el cero$ Signo de dólarL Usa el símbolo local. Punto decimal, Separador de milesMI, PR Signo de menos, negativos en parentesisEEEE Notación científica
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLSELECT nombre,
TO_CHAR(salario,’$99,999’) Salario FROM empleado;------------------------SELECT nombre,
TO_CHAR(salario,’L99999’) Salario FROM empleado;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLSELECT nombre,
TO_NUMBER(‘2345’,’99,999’)FROM empleado;
Utiliza el mismo formato de número que para la conversión de caracteres.
Unidad 2: Accediendo a la Inform.
24
Yony Richard Montoya Burgos
Funciones SQLSELECT nombre, TO_DATE(‘Enero 22
de 2003’,’Mounth DD “de” YYYY’)FROM empleado;
Utiliza el mismo formato de fechas que para la conversión de caracteres.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLConversión de Datos Explicita
Conversión de Valores NULOS
NVL(campo,valor) Convierte los valores nulos en “valor”.
Se pueden usar caracteres, números y fechas.Los tipos de datos deben coincidir.Sólo convierte los campos nulos, los que tiene valor no los cambia.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones SQLSELECT nombre,salario,salario*12 “Salario
Anual”FROM empleado;
No se muestran los salarios con valores nulosSELECT nombre,salario, NVL(salario,0)*12
“Salario Anual”FROM empleado;
Unidad 2: Accediendo a la Inform.
25
Yony Richard Montoya Burgos
Funciones SQLFunción de Decodificación
Trabaja como el CASE o el IF-THEN-ELSEDECODE(col|exp, cond1,valor1
[,cond2, valor2,.....][,valor por defecto])
-------------------SELECT nombre,salario,DECODE(sexo, ‘M’, ‘Femenino’, ‘H’, ‘Masculino’) SexoFROM empleado;-------------------
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
EjerciciosMostrar nombre,fecha de contrato y fecha de contrato en literal en el formato “Monday, Twenty-Fourth of December, 1974”. Mostrar los meses trabajados de cada empleado.Mostrar la consulta en el formato siguiente: “<nombre> gana $<salario> mensual y gana $<salario anual> al año.”Crear una consulta para reemplazar el campo nss_superv por “Sin Supervisor” para los campos que no tengan un supervisor
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
EjerciciosCrear la consulta que muestre una salida con el nombre del empleado seguido de asteriscos por cada unidad de mil de su salario. Ejemplo:
Alicia ************************* Etiquete la columna como “Empleados y sus salarios”.Mostrar la fecha de contrato de los empleados de sexo masculino en el formato “Lunes 25 de Diciembre de 2001” y de los de sexo Femenino “Lunes, 25 – Diciembre – 2001”
Unidad 2: Accediendo a la Inform.
26
Yony Richard Montoya Burgos
Multiples TablasUsar información de varias tablasSe usa Join, Productos Cartesianos
Join, union de tablas de acuerdo a un campo comunProducto cartesiano, Union de todas las filas de una tabla con las filas de la otra.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Multiples TablasProducto Cartesiano
La condición del Join es omitida
SELECT nombre,nombred FROM empleado,departamento;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Multiples TablasJOIN
EQUIJOIN: Lo valores de campos deben emparejar(Llave Primari y Foranea)NON-EQUIJOIN: No se tiene relación entre los camposOUTER JOIN: Mostrar columnas que no son parte del JOINSELF JOIN: Join de tablas consigo mismas.
Se pueden usar alias de tabla para disminuir el proceso de escritura de la consulta
SELECT nombre FROM empleado eWHERE e.salario>=30000;
Unidad 2: Accediendo a la Inform.
27
Yony Richard Montoya Burgos
Multiples TablasEQUIJOIN
Los valores de los campos deben coinsidirSe maneja llaves primarias y llaves foraneasGeneralmente los campos deben tener el mismo tipo.
SELECT tabla1.columna,tabla2.columnaFROM tabla1,tabla2WHERE tabla1.columna1=tabla2.columna2;
SELECT nombre,apellido,nombred,ndFROM empleado,departamentoWHERE empleado.nd=departamento.numerod;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Multiples TablasNON-EQUIJOIN
En ciertas ocasiones no se tiene relación directa entre tablasNo existe correspondencia de tablasNo se usa la igualdad entre campos
SELECT e.nombre,e.salario,c.nombreFROM empleado e,cargos cWHERE e.salario BETWEEN c.minimo AND
c.maximo;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Multiples TablasOUTER JOIN
Si alguna fila no satisface la condición no se muestra en el resultadoSe permite mostrar información de filas que no son parte del joinEl operador de join es el (+)
SELECT t1.col,t2.col FROM tabla1 t1, tabla2 t2 WHERE t1.col(+)=t2.col
SELECT t1.col,t2.col FROM tabla1 t1, tabla2 t2 WHERE t1.col=t2.col(+)
Unidad 2: Accediendo a la Inform.
28
Yony Richard Montoya Burgos
Multiples TablasOUTER JOIN
SELECT e.nombre, d.numerod, d.nombredFROM empleado e, departamento dWHERE e.nd(+)=d.numerodORDER BY e.nd;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Multiples TablasSELF JOIN
En ocaciones es necesario trabajar con la misma tabla.Los campos componentes del join pertenecen a la misma tablaEl supervisor es a su vez un empleado, por lo que la relación es involutiva
SELECT emp.nombre||’ trabaja para ‘||supervisor.nombreFROM empleado emp, empleado supervisorWHERE emp.nss_superv=supervisor.nss;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
EjerciciosMostrar el nombre del empleado y el de departamento de todos los empleados que tiene una A en su nombre.Mostrar el nombre, codigo de empleado junto con el nombre y código de su supervisor.Realizar la consulta anterior incluyendo los empleados que no tienen supervisorCrear una consulta mostrando el número de departamento, nombre del empleado y todos los empleados que trabajan en el mismo departamento que el empleado indicado.
Unidad 2: Accediendo a la Inform.
29
Yony Richard Montoya Burgos
EjerciciosMostrar el nombre y la fecha de contrato (de la tabla contratos) de cualquier empleado que haya sido cotratado después que el empleado “Allen”
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoLas funciones de grupo trabajan sobre un conjunto de filas para dar un resultado por grupo.Se tienen:
AVG([DISTINCT|ALL]n) Promedio de los valores de nCOUNT({*|[DISTINCT|ALL]exp}) Numero de filasMAX([DISTINCT|ALL]n) Máximo valor de nMIN([DISTINCT|ALL]n) Mínimo valor de nSTDDEV([DISTINCT|ALL]n) Desviación EstandarSUM([DISTINCT|ALL]n) Suma de valores de nVARIANCE([DISTINCT|ALL]n) Varianza
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoTodas las funciones de grupo excepto COUNT(*) ignoran los valores nulosSe pueden indicar que se tomaran sólo los valores no repetidos (DISTINCT) o todos (ALL).
Unidad 2: Accediendo a la Inform.
30
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoSELECT AVG(salario), MAX(salario),
MIN(salario), SUM(salario)FROM empleadoWHERE sexo=‘H’;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoSELECT COUNT(*) FROM empleadoWHERE nd=5;
Devuelve el número de filas en la tabla, incluyendo filas duplicadas y valores nulos.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoSELECT COUNT(salario) FROM empleadoWHERE nd=5;
Sólo devuelve el número de filas, omite los valores nulos.
Ejercicio: Mostrar el número de departamentos distintos en la tabla empleado
Unidad 2: Accediendo a la Inform.
31
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoSe puede agrupar las consultas por uno o varios camposUtilizar la claúsula GROUP BY
SELECT campo, funcion_grupo(campo)FROM tabla[WHERE condición][GROUP BY expresión de grupo][ORDER BY campo]
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoTodas los campos que no esten en las funciones de grupo deben estar en la claúsula GROUP BY.
SELECT nd,AVG(salario)FROM empleadoGROUP BY nd;--------------------------SELECT nd,AVG(salario)FROM empleadoGROUP BY nss;
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoSe puede agrupar las consultas por columnas que no esten en la consulta
SELECT AVG(sal) FROM empleadoGROUP BY nd;
Se puede usar las funciones de grupo en ORDER BY
SELECT nd, AVG(salario)FROM empleadoGROUP BY nd ORDER BY AVG(salario);
Unidad 2: Accediendo a la Inform.
32
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoSe puede agrupar por mas de una columnaSi se usan funciones de grupo y campos utilizar la clausula GROUP BYLas funciones de grupo no pueden formar parte de las condiciones WHERE
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoUtilizar la claúsula HAVING para condiciones de grupo
SELECT coluna, funcionFROM tabla[WHERE condicion][GROUP BY expresion][HAVING condicion][ORDER by columan];
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Funciones de Agregación y de GrupoSELECT nd,MAX(salario)FROM empleadoGROUP BY ndHAVING max(salario)>40000;
Las funciones de grupo se pueden combinarSELECT max(avg(salario))FROM empleadoGROUP BY nd;
Unidad 2: Accediendo a la Inform.
33
Yony Richard Montoya Burgos
EjerciciosMostrar el número de supervisores existentes.Crear la consulta para mostrar el número de empleados total y los empleados contratados el 2001,2002 y 2003 de la manera siguiente:
TOTAL 2001 2002 2003Crear la siguiente salida:
Departamento Numero Empleados Salario
Donde Salario es el promedio con 2 decimales.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SubconsultasEn ciertas ocaciones para resolver una consulta es necesario realizar dos consultas.La consulta interna (subconsulta) se ejecutará primero antes de la consulta principal.El resultado de la subconsulta es usado por la consulta principal (consulta externa).
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SubconsultasSELECT camposFROM tablasWHERE exp op (SELECT campos
FROM tablas);Se puede colocar la consulta en las claúsulas: WHERE, HAVING, FROM.El operador incluye >.=, o IN.Existen dos clases de operadores los de fila: (>,=,<,>=,<=,<>) y de filas múltiples (IN,ANY,ALL)
Unidad 2: Accediendo a la Inform.
34
Yony Richard Montoya Burgos
SubconsultasSELECT nombre,apellido FROM empleadoWHERE salario > (SELECT salario
FROM empleado WHERE LOWER(nombre)=‘alicia’);
La consulta busca los empleados que ganan mas que el empleado ‘alicia’.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SubconsultasLas subconsultas van encerradas en paréntesis.Las subconsultas no deben tener la claúsula ORDER BY.Usar operadores de simple fila con subconsultas de simple-fila y multiples filas con subconsultas de multiples-filas.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SubconsultasSELECT nombre,salarioFROM empleadoWHERE salario = (SELECT MIN(salario)
FROM empleado);Se pueden usar funciones de grupo en las subconsultas.
Unidad 2: Accediendo a la Inform.
35
Yony Richard Montoya Burgos
SubconsultasSELECT nd,MIN(salario)FROM empleadoGROUP BY ndHAVING MIN(salario) = (SELECT MIN(salario)
FROM empleadoWHERE nd=5);
Oracle primero realiza las subconsultas.Luego realiza la comparación con HAVING
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SubconsultasSELECT nombre,apellidoFROM empleadoWHERE salario = (SELECT MIN(salario)
FROM empleadoORDER BY nd);
Es muy comun generar subconsultas que devuelven múltiples filas y que son usadas en consultas de simple-fila.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SubconsultasSubconsultas de mas de una fila.Usan operadores de comparación de multiples-filas
Unidad 2: Accediendo a la Inform.
Compara con todos los valores devueltos por la subconsulta
ALL
Compara con cada valor devuelto por la subconsulta
ANY
Compara con una listaIN
SignificadoOperador
36
Yony Richard Montoya Burgos
SubconsultasSELECT nombre,salario,ndFROM empleadoWHERE salario IN (SELECT MIN(salario)
FROM empleadoORDER BY nd);
Se compara con una lista que es tomada de la subconsulta
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SubconsultasSELECT nss, nombre, salarioFROM empleadoWHERE salario < ANY (SELECT salario
FROM empleadoWHERE nd=5)
AND nd<>5;
< ANY significa menor que el máximo> ANY significa mayor que el mínimo= ANY es lo mismo que IN
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SubconsultasSELECT nss,nombre, salario)FROM empleadoWHERE salario > ALL (SELECT AVG(salario)
FROM empleadoGROUP BY nd);
> ALL significa mas que el máximo.< ALL significa menos que el mínimo.El operador NOT puede ser usado con IN, ANY y ALL
Unidad 2: Accediendo a la Inform.
37
Yony Richard Montoya Burgos
EjerciciosMostrar el nombre del empleado y su fecha de contrato (de la tabla contratos) para todos loslos empleados del mismo departamento que ‘Joyce’ excluyendo a ‘Joyce’.Mostrar el nombre del empleado, número de departamento y salario para todos los empleados cuya ubicación del departamento sea en ‘Bellaire’Muestre el nombre, código y salario de todos los empleados que ganan mas que el salario promedio y que trabajan en un departamento con cualquier empleado con una T en su nombre.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Subconsultas de Múltiples ColumnasSe desea comparar mas de una columna.Usar subconsultas con campos compuestos.
SELECT col1,col2FROM tablaWHERE(col,col,...) IN (SELECT col,col,...
FROM tablaWHERE cond);
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Subconsultas de Múltiples ColumnasMostrar el número de orden, número de producto de cualquier item en la que el número y la cantidad emparejen (ambos) con el número y la cantidad de un item de la orden 605
SELECT orid,prodid,qtyFROM itemWHERE (prodid,qty) IN (SELECT prodid,qty
FROM itemWHERE ordid=605)
AND ordid<>605;
Unidad 2: Accediendo a la Inform.
38
Yony Richard Montoya Burgos
SubconsultasSe puede utilizar una subconsulta como parte del FROM
Unidad 2: Accediendo a la Inform.
SQL> SELECT a.nombre, a.salario, a.nd, b.salavg
FROM empleado a,
WHEREa.nd= b.ndAND a.salario > b.salavg;
(SELECT nd, AVG(salario) salavg FROM empleadoGROUP BY nd) b
Yony Richard Montoya Burgos
EjerciciosMostrar el nombre, número de departamento y salario de cualquier empleado cuyo número de departamento y salario sea igual al número de departamento y salario de cualquier empleado que gana una comision.Crear una consulta para mostrar el nombre, fecha de nacimiento, y salario de todos los empleados que tienen el mismo salairo y comisión que ‘Ahmad’
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Reportes Interactivos
Unidad 2: Accediendo a la Inform.
Usuario
. . .sal = ? . . .. . .deptno = ?. . .. . .ename = ?. .
Quiero introducir valoresde la consulta en tiempo
de ejecución
39
Yony Richard Montoya Burgos
Variables de SubstituciónUsar variables de substitución SQL*Plus para almacenar valores temporales.
Ampersand simple (&)Ampersand doble (&&)Comandos DEFINE y ACCEPT
Pasar variables entre sentencias SQL.Dinámicamente modificar encabezados y pie de página.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Variables de SubstituciónUsar variables con el prefijo ampersand (&) para preguntar al usuario por un valor cada vez que la consulta se ejecute (si es que la variable no existe).
Unidad 2: Accediendo a la Inform.
SQL> SELECT empno,ename, sal, deptno2 FROM emp3 WHERE empno = &numero_empleado;
Ingresar valor para numero_empleado: 7369
EMPNO ENAME SAL DEPTNO----------- ------------------ ---------- ----------------7369 SMITH 800 20
Yony Richard Montoya Burgos
Variables de SubstituciónEl comando SET VERIFY ON fuerza a mostrar el comando antes y después del reemplazo.
Unidad 2: Accediendo a la Inform.
SQL> SET VERIFY ONSQL> SELECT empno, ename, sal, deptno2 FROM emp3 WHERE empno = &numero_empleado;
Ingrese un valor para numero_empleado: 7369
old 3: WHERE empno = &numero_empleadonew 3: WHERE empno = 7369
. . .
40
Yony Richard Montoya Burgos
Variables de SubstituciónUsar comillas simples para campos de tipo carácter o fecha.
Unidad 2: Accediendo a la Inform.
SQL> SELECT ename, deptno, sal*122 FROM emp3 WHERE job = ‘&job_title’;
Enter a value for job_title: ANALYST
ENAME DEPTNO SAL*12 ------------------ ---------------- -----------SCOTT 20 36000FORD 20 36000
Yony Richard Montoya Burgos
Variables de SubstituciónUsar variables de substitución para ingresar lo siguiente:
Condiciones WHERE.Claúsulas ORDER BYExpresiones de ColumnaNombres de TablaSentencias SQL completas
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Variables de Substitución
Unidad 2: Accediendo a la Inform.
SQL> SELECT empno, ename, job, &column_name2 FROM emp3 WHERE &condition4 ORDER BY &order_column;
Enter value for column_name: salEnter value for condition: sal>=3000Enter a value for order_column: enameEMPNO ENAME JOB SAL---------- ------------------- ----------- ---------------7902 FORD ANALYST 30007839 KING PRESIDENT 50007788 SCOTT ANALYST 3000
41
Yony Richard Montoya Burgos
Variables de SubstituciónUsar && si se quiere reusar la variable.
Unidad 2: Accediendo a la Inform.
SQL> SELECT empno, ename, job, &&column_name2 FROM emp3 ORDER BY &column_name;
Enter value for column_name: deptnoEMPNO ENAME JOB DEPTNO---------- ------------------- ----------- ---------------7839 KING PRESIDENT 107782 CLARK MANAGER 107934 MILLER CLERK 10
. . .14 rows selected.
Yony Richard Montoya Burgos
Variables de SubstituciónSe pueden definir variables con los comandos:
DEFINE Crea una variable tipo CHARACCEPT Lee la entrada de usuario y lo almacena en una variable
Si se necesita definir variables que incluyen espacios se debe encerrar los valores entre comillas
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Variables de SubstituciónSe puede usar:DEFINE variable=valor Crea una variable tipo CHAR y le asigna un valor.
DEFINE variable Muestra la variable, su valor y su tipo de dato.
DEFINE Muestra todas las variables de usuario
Unidad 2: Accediendo a la Inform.
42
Yony Richard Montoya Burgos
Variables de SubstituciónEl comando ACCEPT crea una consulta personalizada.Explicitamente se define una variable NUMBER o DATE.Oculta la entrada del usuario por razones de seguridadACCEPT variable [tipo][FORMAT formato]
[PROMPT texto][HIDE]
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Variables de SubstituciónTipo: puede ser NUMBER,CHAR,DATEFormato: Especifica el formatoPROMPT: Muestra el texto al usuario.HIDE: Permite ocultar la entrada del usuario.
Unidad 2: Accediendo a la Inform.
ACCEPT dept PROMPT ‘Introduzca el departamento: ‘SELECT *FROM deptWHERE dname = UPPER (‘&dept’)/
Introduzca el departamento: SalesDEPTNO DNAME LOC-------------- ------------------- -----------------30 SALES CHICAGO
Yony Richard Montoya Burgos
Variables de SubstituciónUna variable queda definida hasta que:
Use el comando UNDEFINE para borrarlaSe termine la sesión SQL*Plus.
Se puede verificar los cambios con DEFINE.Para definir variables para cada sesión modificar el archivo login.sql de manera que las variables sean creadas al inicio de sesión
Unidad 2: Accediendo a la Inform.
43
Yony Richard Montoya Burgos
Variables de SubstituciónCrear variables en la sesión SQL*Plus
Se puede usar la variable con el prefijo &
Unidad 2: Accediendo a la Inform.
SQL> DEFINE deptname = salesSQL> DEFINE deptname
DEFINE DEPTNAME = “sales” (CHAR)
SQL> SELECT *2 FROM dept3 WHERE dname = UPPER ( ‘&deptname’ );
Yony Richard Montoya Burgos
Se puede usar SET para controlar la sesión actual.
Se puede verificar que se esta usando con el comando SHOW
DEFINE DEPTNAME = “sales” (CHAR)
SQL> SET ECHO ON
SQL> SHOW ECHOecho ON
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
•ARRAYSIZE {20 | n} Tamaño del arreglo de datos
•COLSEP {_ | text} Separador de Columna
•FEEDBACK {6 | n | OFF | ON} Muestra # de registros
•HEADING {OFF | ON} Mostrar o no el encabezado
•LINESIZE {80 | n} Caracteres por línea
•LONG {80 | n} Ancho para campos LONG
•PAGESIZE {24 | n} Líneas por página
•PAUSE {OFF | ON | text} Controla el pausado
•TERMOUT {OFF | ON} La salida se envia o no a la pantalla
Unidad 2: Accediendo a la Inform.
Variables de Comando SET
44
Yony Richard Montoya Burgos
•COLUMN [column option] Formato de Columna
•TTITLE [text | OFF | ON] Título del Reporte
•BTITLE [text | OFF | ON] Pie de Página
•BREAK [ON report_element] Elimina Duplicados
Unidad 2: Accediendo a la Inform.
Comandos de Formato SQL*PlusSe puede controlar las características del reporte usando:
Yony Richard Montoya Burgos
COLUMNControla el formato de salida de una columna
•CLE[AR]: Borra los formatos de columna
•FOR[MAT] format: Cambia el formato de la columna
•HEA[DING] texts: Establece el encabezado
•JUS[TIFY] {align}: Alinea los valores del encabezado de columna (left, center, o right).
COL[UMN] [{column | alias} [option]]
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
COLUMNCrea encabezado de columnas
Muestra la configuración de formato de la columna ENAME
Borra la configuración de la columna ENAMECOLUMN ename
COLUMN ename CLEAR
COLUMN ename HEADING ‘Employee|Name’ FORMAT A15 COLUMN sal JUSTIFY LEFT FORMAT $99,990.00COLUMN mgr FORMAT 999999999 NULL ‘No manager’
Unidad 2: Accediendo a la Inform.
45
Yony Richard Montoya Burgos
Elemento Descripción Ejemplo ResultadoAn Muestra en un ancho de n 9 Elimina ceros de la
izquierda 999999 1234
0 Muestra ceros a la Izq. 099999 01234 $ Signo de Dolar $9999 $1234 L Moneda Local L9999 L1234 . Posición punto decimal 9999.99 1234.00 , Separador de Mil 9,999 1,234
Unidad 2: Accediendo a la Inform.
Modelos de Formato de COLUMN
Yony Richard Montoya Burgos
Comando BREAKElimina valores duplicados
Se puede saltar número de filas cuando los valores cambian
SQL> BREAK ON ename ON job
SQL> BREAK ON ename SKIP 4 ON job SKIP 2
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Encabezado y Pie de PáginaMuestra el encabezado del reporte
Muestra el pie de página del reporte
TTI[TLE] [text l OFF l ON]
SQL> TTITLE ‘Salario l Reporte’
SQL> BTITLE “Confidencial”
Unidad 2: Accediendo a la Inform.
BTI[TLE] [text l OFF l ON]
46
Yony Richard Montoya Burgos
Sat Nov 29 Page 1Empleados
Reporte
TrabajoCategoría Empleado Salario ----------------------------- -------------------- ------------CLERK ADAMS $1,100.00
JAMES $950.00MILLER $1,300.00 SMITH $800.00
MANAGER BLAKE $2,850.00 CLARK $2,975.00
SALESMAN ALLEN $1,600.00 MARTIN $1,250.00TURNER $1,500.00WARD $1.250.00
Confidencial
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SET PAGESIZE 37
SET LINESIZE 60
SET FEEDBACK OFF
TTITLE ‘Empleados|Reporte’
BTITLE ‘Confidencial’
BREAK ON job
COLUMN job HEADING ‘Trabajo|Categoría’ FORMAT A15
COLUMN ename HEADING ‘Empleado’ FORMAT A15
COLUMN sal HEADING ‘Salario’ FORMAT $99,999.999
SELECT job,ename,sal FROM emp
WHERE sal < 3000
ORDER BY job,ename
/
SET FEEDBACK ON
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Lenguaje de Manipulación de DatosUna sentencia DML es ejecutada cuando:
Adiciona nuevas filas a una tablaModifica filas existentes en una tablaElimina filas de una tabla
Una transacción consiste de una colección de sentencias DML.
Unidad 2: Accediendo a la Inform.
47
Yony Richard Montoya Burgos
Lenguaje de Manipulación de DatosAdicionar nuevas filas a una tablas usando la sentencia INSERT
Solo es posible insertar una fila con esta sintaxis.
INSERT INTO table [(column . . .])]VALUES (value [, value. . .]);
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Insertando Nuevas FilasInsertar filas con los valores para cada columnaListar los valores en el orden definido en la tablaOpcionalmente listar las columnas en la claúsula INSERT
Caracteres y fechas entre comillas simples.
SQL> INSERT INTO dept (deptno, dname, loc)2 VALUES (50, ‘DEVELOPMENT’, ‘DETROIT’);1 row created.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Insertando Valores NULLMétodo Implicito: Omitir la columna de la lista de columnas
Método Explícito: Especificar la palabra clave NULL.
SQL> INSERT INTO dept (deptno, dname )2 VALUES (60, ‘MIS’);1 row created.
SQL> INSERT INTO dept2 VALUES (70, ‘FINANCE’, NULL);1 row created.
Unidad 2: Accediendo a la Inform.
48
Yony Richard Montoya Burgos
Insertando Valores EspecialesLa función SYSDATE almacena la fecha y la hora actual
SQL> INSERT INTO emp (empno, ename, job,2 mgr, hiredate, sal, comm, 3 deptno)4 VALUES (7196, ‘GREEN’, ‘SALESMAN’,5 7782, SYSDATE, 2000, NULL,6 10);1 row created.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Insertando Valores EspecíficosSe pueden usar las funciones de conversión.
Verificar Adición
SQL> INSERT INTO emp2 VALUES (2296, ‘AROMANO’, ‘SALESMAN’, 7782,3 TO_DATE(‘FEB 3, 2003’, ‘MON DD, YYYY’); 4 1300, NULL, 10);1 row created.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------- ----------- ---------- -------- --------------- ------ --------- ------------2296 AROMANO SALESMAN 7782 03-FEB-03 1300 10
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Lenguaje de Manipulación de DatosSe pueden crear scripts usando variables de substitución.
SQL> INSERT INTO dept (deptno, dname, loc)2 VALUES (&department_id,3 ‘&department_name’, &location’);
Enter values for department_id: 80Enter value for department_name: EDUCATIONEnter value for location: ATLANTA
1 row created.
Unidad 2: Accediendo a la Inform.
49
Yony Richard Montoya Burgos
SubConsultasSe pueden insertar valores resultado de una subconsulta
No Usar la claúsula VALUESEl número de parámetros de la subconsulta debe emparejar con las columnas del INSERT
SQL> INSERT INTO managers (id, name, salary, hiredate) 2 SELECT empno, ename, sal, hiredate 3 FROM emp4 WHERE job = ‘MANAGER’;3 rows created.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Actualizando informaciónModificar filas con la sentencia UPDATE
Modifica mas de una fila a la vez, si fuese requerido.
UPDATE table
SET column = value [, column = value, . . .]
[WHERE condition];
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Actualizando InformaciónIdentificar una fila o filas en particular conla cláusula WHERE.
Todas las filas son modificadas si se omite la cláusula WHERE
SQL> UPDATE emp2 SET deptno = 203 WHERE empno = 7782;1 row updated.
SQL> UPDATE employee2 SET deptno = 20;14 rows updated.
Unidad 2: Accediendo a la Inform.
50
Yony Richard Montoya Burgos
Actualizando Múltiples ColumnasSe puede usar subconsultas para actualizar múltiples columnas.
SQL> UPDATE emp2 SET (job, deptno) =3 (SELECT job, deptno4 FROM emp5 WHERE empno = 7499)6 WHERE empno = 7698;
1 row updated.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Actualizar Filas Basados en Otra TablaSe pueden usar subconsultas para ctualizar filas en una tabla basados en valores de otra tabla.
SQL> UPDATE employee2 SET deptno = (SELECT deptno3 FROM emp4 WHERE empno = 7788)5 WHERE job = (SELECT job6 FROM emp7 WHERE empno = 7788);
2 rows updated.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
SQL> UPDATE emp2 SET deptno = 553 WHERE deptno = 10;
UPDATE emp*
ERROR at line 1:ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK)violated - parent key not found
Númerode Departm
ent number no exis
te
Unidad 2: Accediendo a la Inform.
Integridad de DatosSe controla la integridad de la información si se tuvieran llaves foraneas.
51
Yony Richard Montoya Burgos
Eliminando InformaciónSe puede eliminar filas de una tabla usando la sentencia DELETE.
DELETE [FROM] table[WHERE condition];
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Eliminando InformaciónFilas específicas son eliminadas usando la cláusula WHERE.
Todas las filas son eliminadas sin WHERE
SQL> DELETE [FROM] dept2 WHERE dname = ‘DEVELOPMENT’;1 row deleted.
SQL> DELETE [FROM] dept4 rows deleted.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Usando SubconsultasSe pueden usa subconsultas para eliminar filas de una tabla basados en valores de otra.
SQL> DELETE emp2 WHERE deptno = 3 (SELECT deptno4 FROM dept5 WHERE deptno = ‘SALES’)
2 rows updated.
Unidad 2: Accediendo a la Inform.
52
Yony Richard Montoya Burgos
SQL> DELETE FROM dept 2 WHERE deptno = 10;
DELETE FROM dept*
ERROR at line 1:ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK)violated - child record found
No se puede borrar una fila que
contiene una llave prim
aria
usada como una llave foránea
en otra tabla.
Unidad 2: Accediendo a la Inform.
Integridad de DatosSe considera la integridad de la información si hubiesen llaves foráneas.
Yony Richard Montoya Burgos
Transacciones en la BDComienza cuando la primera sentencia SQL es ejecutada.Finaliza con uno de los siguientes eventos:
COMMIT o ROLLBACKSentencias DDL o DCL tienen COMMIT automáticoCerrar la sesión SQL*PlusEl sistema colapsa
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Ventajas del COMMIT y ROLLBACKAsegura la consistencia de los datos.Muestra los datos antes de hacer los cambios permanentesAgrupa lógicamente operaciones relacionadas
Unidad 2: Accediendo a la Inform.
53
Yony Richard Montoya Burgos
INSERT UPDATE INSERT DELETE
Transacción
COMMIT Savepoint A Savepoint B
ROLLBACK a Savepoint B
ROLLBACK a Savepoint A
ROLLBACK
Unidad 2: Accediendo a la Inform.
Controlando las Transacciones
Yony Richard Montoya Burgos
Estado de los datos antes del COMMIT o ROLLBACKLos datos de un estado anterior pueden ser recuperados.Los usuarios actuales pueden ver lso resultados de operciones DML usando una sentencia SELECTOtros usuarios no pueden ver los resultados de las sentecias DMLLas filas afectadas son bloqueadas; otros usaurios no pueden cambiar los datos dentro de las filas afectadas.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Estado después del COMMITLos cambios son permanentes.El estado previo se pierdeTodos los usuarios pueden ver los resultadosLos bloqueos de las filas son liberadosTodos los SavePoints son liberados
Unidad 2: Accediendo a la Inform.
54
Yony Richard Montoya Burgos
COMMIT los cambiosRealizar los cambios
Hacer los cambios permanentes
SQL> UPDATE emp2 SET deptno = 103 WHERE empno = 7782;
1 row updated.
SQL> COMMIT;
Commit complete.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Estado después del ROLLBACKDescarga cambios pendientes.El estado previo se recuperaLos bloqueos de las filas son liberados
SQL> DELETE FROM employee;14 rows deleted.
SQL> ROLLBACKRollback complete.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Marcas (SAVEPOINT)Crear una marca en la transacción actual con SAVEPOINT.Volver a una marca con un ROLLBACK TO SAVEPOINT
SQL> UPDATE. . .SQL> SAVEPOINT update_done;Savepoint created.SQL> INSERT. . .SQL> ROLLBACK TO update_done;Rollback complete.
Unidad 2: Accediendo a la Inform.
55
Yony Richard Montoya Burgos
ROLLBACK de SentenciasSi una sentencia DML falla sólo la sentencia es recuperada (ROLLBACK)Oracle implementa un SAVEPOINT implicitoEl usuario debeterminar las transacciones explícitamente.
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Consistencia de LecturaSe garantiza una vista consistente de los datos todas la vecesCambios de un usuario no tiene conflicto con los realizados por otro usuarioConsistencia de lectura asegura que:
Lecturas no esperen por las escriturasEscrituras no esperen por las lecturas
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
UPDATE emp SET sal = 2000 WHERE ename =
‘SCOTT’;
SELECT *FROM emp;
Usuario A
Usuario B
Bloques de Datos
Segmentos
De Rollback
Datoscambiados y no cambiados
Datos “antiguos” antes del cambio
Imagen de Lectuaconsistente
Unidad 2: Accediendo a la Inform.
Implementación de consistencia de lectura
56
Yony Richard Montoya Burgos
BloqueosPreviene interacción destructiva entre transacciones concurrentesNo requiere intervención del usuarioAutomáticamente usa el nivel mas bajo de restricciónSe mantiene mientras dure la transacciónSe tiene dos modos básicos:
ExclusivoCompartido
Unidad 2: Accediendo a la Inform.
Yony Richard Montoya Burgos
Objecto Descripción
Table Unit básica de almacenamiento; compuesta de filas y columnas
View Representación Lógica se subconjutnos de datos de una o mas tables
Sequence Genera valores de llave primaria
Index Mejora el rendimiento de algunas consultas
Synonym Permite dar nombres alternativos a los objetos
Unidad 3: Manejo de TABLAS
Objetos de la BD
Yony Richard Montoya Burgos
Convención de NombresDebe comnezar con una letraPuede ser de 1 a 30 caracteres de largoDebe contener solo A-Z,a-z,0-9,_,$ y #No debe duplicar el nombre de otro objeto del mismo usuarioNo debe ser una palabra reservada del Servidor Oracle.
Unidad 3: Manejo de TABLAS
57
Yony Richard Montoya Burgos
Sentencia CREATE TABLEDebe tener :
Privilegio CREATE TABLEArea de almacenamiento
Se debe especificar:Nombre de TablaNombre de columnas, tipo de datos y tamaño
CREATE [GLOBAL TEMPORARY] TABLE [schema.] table(column datatype [DEFAULT expr] [, . . .]);
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Referencia a tablas de otros UsuariosSe debe usar nombre del propietario como prefijo de la tablaSe debe tener derechos para crear otras tablas.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Opción DEFAULTEspecifica un valor por defecto para una columna en el proceso de INSERT
Son permitidos valores literales, expresiones o funciones SQL.No se permiten nombres de otras columnas o pseudocolumnas.El tipo del valor por defecto debe ser el mismo que el de la columna.
. . .hiredate DATE DEFAULT SYSDATE,
Unidad 3: Manejo de TABLAS
58
Yony Richard Montoya Burgos
SQL> CREATE TABLE dept12 (deptno NUMBER (2),3 dname VARCHAR2 (14),4 loc VARCHAR2 (13);Table created.
SQL> DESCRIBE dept1
Name Null Type---------------------------------- ----------- ---------------------DEPTNO NUMBER(2)DNAME VARCHAR2 (14)LOC VARCHAR2 (13)
Unidad 3: Manejo de TABLAS
Creando TablasCrear la tabla
Yony Richard Montoya Burgos
Tipos de TablasTablas de Usuario
Tablas creadas y mantendias por el usuarioDiccionario de Datos
Tablas mantenidas por el Servidor Oracle.Usa lor prefijos:
USER_: Objetos del usuarioALL_: Objetos a los que el usuario tiene derechoDBA_: Objetos para usuarios con derechos de DBAV$_: Muestra el rendimiento de la BD, los bloqueos, sólo disponible para los DBA
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Consultando al Diccionario de DatosTablas pertenecientes al usuario
Ver distintos tipos de objetos pertenecientes al usuario
Ver tablas,vistas,sinónimos y secuencias del usuario
SQL> SELECT *2 FROM user_tables;
SQL> SELECT DISTINCT object_type2 FROM user_objects;
SQL> SELECT *2 FROM user_catalog;
Unidad 3: Manejo de TABLAS
59
Yony Richard Montoya Burgos
Tipos de DatosTipo de Dato Descripción
VARCHAR2(size) Caracter de longitud Variable
CHAR(size) Caracter de longitud Fija
NUMBER(ps) Valor numérico
DATE Tipo Fecha y Hora
LONG Caracter de longitud variable hasta 2 gigabytes
CLOB Caracter tipo Single-byte hasta 4 gigabytes
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Tipo de Dato Descripción
RAW y LONG RAW Datos Binarios Raw
BLOB Datos binaries hasta 4 gigabytes
BFILE Datos binaries almacenados en un archivo externo hasta 4 gigabytes
Unidad 3: Manejo de TABLAS
Tipos de Datos
Yony Richard Montoya Burgos
Cubconsultas para crear TABLASSe pueden crear tablas a partir de subconsultas
Empareja el número de columnas al número de columnas de la consultaDefine las columnas con los nombres de columna y los tipos de las columnas de la consulta.
CREATE TABLE table
[(column, column. . .)]
AS subquery;
Unidad 3: Manejo de TABLAS
60
Yony Richard Montoya Burgos
SQL> CREATE TABLE dept302 AS3 SELECT empno, ename, sal*12 ANNSAL, hiredate4 FROM emp5 WHERE deptno = 30;Table created.
SQL> DESCRIBE dept 30
Name Null Type---------------------------------- ----------- ---------------------EMPNO not null NUMBER(2)ENAME VARCHAR2 (10)ANNSAL NUMBERHIREDATE DATE
Unidad 3: Manejo de TABLAS
Subconsultas
Yony Richard Montoya Burgos
Modificando InformaciónUsar ALTER TABLE para:
Adicionar nuevas columnasModificar columnas existentesDefinir valores por defecto para columnas nuevas
ALTER TABLE tableADD (column datatype [DEFAULT expr]
[, column datatype]. . .);
ALTER TABLE tableMODIFY (column datatype [DEFAULT expr]
[, column datatype]. . .);
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Añadiendo ColumnasUsar la cláusula ADD
La nueva columna es la última columna en la tabla
SQL> ALTER TABLE dept302 ADD (job VARCHAR2 (9));Table altered.
EMPNO ENAME ANNSAL HIREDATE JOB---------- -------------- ------------ ---------------- ------
7689 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 . . .6 rows selected.
Unidad 3: Manejo de TABLAS
61
Yony Richard Montoya Burgos
Modificando ColumnasSe puede modificar el tipo, tamaño y valor por defecto.
El cambio en los valores por defecto sólo es válida para las siguientes inserciones.
SQL> ALTER TABLE dept302 MODIFY (ename VARCHAR2 (15));
Table altered.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
SQL> ALTER TABLE dept302 DROP COLUMN job ;
Table altered.
Unidad 3: Manejo de TABLAS
Borrando una ColumnaSe pueden borrar columans que ya no son utilizadasSólo se pueden borrar columnas que no son llave
Yony Richard Montoya Burgos
ALTER TABLE tableSET UNUSED (column);
OALTER TABLE tableSET UNUSED COLUMN column;
ALTER TABLE tableDROP UNUSED COLUMNS;
Unidad 3: Manejo de TABLAS
Marcar columna como no usadaSe puede marcar una o mas columnas como NO USADASe pueden borrar todas las columnas marcadas como no usadas
62
Yony Richard Montoya Burgos
Borrando una TablaTodos los datos y la estructura de una tabla es borradaTodas las transacciones pendientes son CometidasTodos los índices son borradosNO se puede deshacer esta instrucción (Rollback)
SQL> DROP TABLE dept30;Table dropped.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Renombrando TablasPara cambiar el nombre de una tabla, vista, secuencia, o sinónimo usar la sentencia RENAME
Se debe ser propietario del objeto
SQL> RENAME dept TO depto;
Table altered.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Truncado TablaLa sentencia TRUNCATE TABLE:
Elimina todas las filas de una tablaLibera el espacio de almacenamiento usado por la tabla
No se puede hacer un ROLLBCAK de esta instrucciónSimilar a DELETE FROM <tabla>;
SQL> TRUNCATE TABLE depto
Table truncated.
Unidad 3: Manejo de TABLAS
63
Yony Richard Montoya Burgos
ComentariosSe puede añadir comentarios a una tabla o columna usando COMMENT.
Los comentarios pueden ser vistos con:ALL_COL_COMMENTS,USER_COL_COMMENTSALL_TAB_COMMENTS,USER_TAB_COMMENTS
Para columnas usar: COMMENT ON COLUMN tabla.columna IS ‘texto’;
SQL> COMMENT ON TABLE emp2 IS ‘Employee Information’;
Comment created.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
EjerciciosCrear la tabla EMPLEADO2 basado en la estructura de la tabla EMP. Incluyendo sólo las columnas EMPNO,ENAME y DEPTNO. Nombre las columnas de su nueva tabla ID, LAST_NAME y DEPT_ID respectivamenteAdicione comentarios a las tablas DEPARMENT y EMPLOYEE describiendo la tabla.Confirme sus adiciones en el diccionario de datos.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
RestriccionesLas restricciones refuerzan las reglas al nivel de tabla.Previenen borrar una tabla si todavía tiene dependencias.Los tipos válidos son:
NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK
Unidad 3: Manejo de TABLAS
64
Yony Richard Montoya Burgos
RestriccionesSi no se nombra un CONSTRAINT Oracle generará un nombre usando SYS_Cn, donde n es un entero para crear nombres únicosSe puede crear Constraint
Al momento de Crear la TablaDespués de crear la tabla.
Definir un constraint en columnas o tablasSe puede ver las restricciones en el diccionario de datos.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
CREATE TABLE [schema.]table(column datatype [DEFAULT expr][column_constraint],…[table_constraint] [,…]);
CREATE TABLE emp (empno NUMBER(4),ename VARCHAR2 (10),…deptno NUMBER (2) NOT NULL,CONSTRAINT emp_empno_pk
PRIMARY KEY(EMPNO) );
Unidad 3: Manejo de TABLAS
RestriccionesDefinición de Constraint.
Yony Richard Montoya Burgos
Definiendo RestriccionesRestricciones a nivel de Columna
Restricciones a nivel de Tabla
column [CONSTRAINT constraint_name] constraint_type,
column,…[CONSTRAINT constraint_name] constraint_type(column, …),
Unidad 3: Manejo de TABLAS
65
Yony Richard Montoya Burgos
Restricción NOT NULLAsegura que valores NULOS no son permitidos en la columna.
EMPNO ENAME JOB . . . COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 . . .
EMP
Restricción NOT NULL (ninguna filapuede contener unavalor NULO para este campo)
Ausencia de restricción NOT NULL (cualquier filapuede contenervalores NULOS en esta columna)
Restricción NOT NULL
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
SQL> CREATE TABLE emp(2 empno NUMBER (4),3 ename VARCHAR2 (10) NOT NULL,4 job VARCHAR2 (9),5 mgr NUMBER (4),6 hiredate DATE,7 sal NUMBER (7, 2),8 comm NUMBER (7, 2),9 deptno NUMBER (7,2) NOT NULL);
Unidad 3: Manejo de TABLAS
Restricción NOT NULL
Yony Richard Montoya Burgos
Restricción UNIQUENo permite valores repetidos en la columna, si permite varios NULOS (si no se tiene un NOT NULL).
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
50 SALES DETROIT 60 BOSTON
Insert into No se permitenombre(SALES yaexiste)
Permitido
Restricción UNIQUEDEPT
Unidad 3: Manejo de TABLAS
66
Yony Richard Montoya Burgos
SQL> CREATE TABLE dept (2 deptno NUMBER(2),3 dname VARCHAR2 (14),4 loc VARCHAR2 (14),5 CONSTRAINT dept_dname_uk UNIQUE (dname));
Unidad 3: Manejo de TABLAS
Restricción UNIQUESe define a nivel de Tabla o de columna.
SQL> CREATE TABLE ……….3 dname VARCHAR2 (14) CONSTRAINT dept_dname_ukUNIQUE,………);
Yony Richard Montoya Burgos
Restricción PRIMARY KEYCrea una llave primaria, sólo se puede crear una por tabla, puede ser de varias columnas.
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
20 MARKETING DALLAS
FINANCE NEW YORK
Insert intoNo permitido(DEPTNO 20 yaexiste)No permitido(DEPTNO es null)
PRIMARY KEYDEPT
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Restricción PRIMARY KEYSe define a nivel de Tabla o de columna.Si se usa mas de una columna usar la definición a nivel de tabla.
SQL> CREATE TABLE dept (2 deptno NUMBER(2),3 dname VARCHAR2 (14),4 loc VARCHAR2 (14),5 CONSTRAINT dept_dname_uk UNIQUE (dname),6 CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno));
Unidad 3: Manejo de TABLAS
67
Yony Richard Montoya Burgos
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS . . .
PRIMARY KEY
DEPT
EMPNO ENAME JOB . . . COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 . . .
7 5 7 1 F O R D M A N A G E R . . . 2 0 0 9
7 5 7 1 F O R D M A N A G E R . . . 2 0 0 2 0
Insert into No Válido(DEPTNO 9 no existe)Permitido
FOREIGN KEY
EMP
Unidad 3: Manejo de TABLAS
Restricción FOREIGN KEYSe define a nivel de Tabla o de columna.
Yony Richard Montoya Burgos
Restricción FOREIGN KEYSe define a nivel de Tabla o de columna.
SQL> CREATE TABLE emp (2 empno NUMBER(4),3 ename VARCHAR2 (10) NOT NULL4 job VARCHAR2 (9),5 mgr NUMBER (4),6 hiredate DATE,7 sal NUMBER (7,2),8 comm NUMBER (7,2),9 deptno NUMBER (7,2) NOT NULL,
10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)11 REFERENCES dept (deptno) );
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Restricción FOREIGN KEYFOREIGN KEY: Define la columna en la tabla hijo a la tabla de referencia.REFERENCES: Identifica la tabla y la columna en la tabla padre.ON DELETE CASCADE: Permite borrar la información en la tabla padre y todas las referencias en la tabla hijo
Unidad 3: Manejo de TABLAS
68
Yony Richard Montoya Burgos
…, deptno NUMBER (2),CONSTRAINT emp_deptno_ck
CHECK (DEPTNO BETWEEN 10 AND 99),...
Unidad 3: Manejo de TABLAS
Restricción CHECKDefine una condición que cada fila debe satisfacer.No se permiten:
Pseudocolumnas CURRVAL, NEXTVAL, LEVEL y ROWNUM.Llamadas a funciones SYSDATE, UID, USER y USERENVConsultas que referencian a otros valores en otras filas
Una simple columan puede tener múltiples restricciones CHECK
Yony Richard Montoya Burgos
Aumentando Restricciones
Se puede adicionar o borrar, pero no modificar, una restricción.Se puede habilitar o deshabilitar una restricción.Se puede adicionar una restricción NOT NULL usando la cláusula MODIFY.
ALTER TABLE tabla
ADD [CONSTRAINT constraint] tipo (columna);
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Unidad 3: Manejo de TABLAS
SQL> ALTER TABLE emp2 ADD CONSTRAINT emp_mgr_fk3 FOREIGN KEY (mgr) REFERENCES emp(empno);
Table altered.
Adicionando una restricciónSe puede adicionar un restricción con ALTER TABLE
69
Yony Richard Montoya Burgos
Eliminando restriccionesUsar la cláusual DROP
Se puede elimnar restricciones por el nombre (CONSTRAINT nombre) o por el campo (PRIMARY KEY campo, UNIQUE campo)
SQL> ALTER TABLE emp2 DROP CONSTRAINT emp_mgr_fk;
Table altered.
SQL> ALTER TABLE emp2 DROP PRIMARY KEY CASCADE;
Table altered.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Deshabilitando restriccionesSe puede dsactivar una restricción con la cláusula DISABLE de ALTER TABLE.La opcion CASCADE deshabilita restricciones de integridad dependientes.
SQL> ALTER TABLE emp2 DISABLE CONSTRAINT emp_empno_pk CASCADE;
Table altered.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
SQL> ALTER TABLE emp2 ENABLE CONSTRAINT emp_empno_pk;
Table altered.
Unidad 3: Manejo de TABLAS
Habilitando RestriccionesSe puede habilitar una restricción deshabilitada con la cláusula ENABLE.Al habilitar una restricción todos los datos son validados.Si se habilita una restricción UNIQUE o PRIMARY KEY, un índice es creado automáticamente.
70
Yony Richard Montoya Burgos
Unidad 3: Manejo de TABLAS
SQL> CREATE TABLE test1 (2 pk NUMBER PRIMARY KEY,3 fk NUMBER,4 col1 NUMBER, 5 col2 NUMBER,6 CONSTRAINT fk_c FOREIGN KEY (fk) REFERENCES test1,7 CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),8 CONSTRAINT ck2 CHECK (col2 > 0));
Multiples RestriccionesSe puede crear restricciones múltiples o que involucren a varios campos.
Yony Richard Montoya Burgos
SQL> SELECT constraint_name, constraint_type,2 search_condition3 FROM user_constraints4 WHERE table_name = ‘EMP’;
CONSTRAINT_NAME C SEARCH_CONDITION---------------------------------------------- -- -------------------------------------SYS_C00674 C EMPNO IS NOT NULL
SYS_C00675 C DEPTNO IS NOT NULL
EMP_EMPNO_PK P . . .
Unidad 3: Manejo de TABLAS
Consultando RestriccionesPara consultar las restricciones ver la tabla USER_CONSTRAINTS.
Yony Richard Montoya Burgos
Consultando RestriccionesVer las columnas asociadas en la tabla USER_CONS_COLUMNS.
SQL> SELECT constraint_name, column_name2 FROM user_cons_columns3 WHERE table_name = ‘EMP’;
CONSTRAINT_NAME COLUMN_NAME ---------------------------------------------- ---------------------------------------EMP_DEPTNO_FK DEPTNOEMP_EMPNO_PK EMPNOEMP_MGR_FK MGRSYS_C00674 EMPNOSYS_C00675 DEPTNO
Unidad 3: Manejo de TABLAS
71
Yony Richard Montoya Burgos
Tabla EMPEMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------- ----------- --------------- ------- ---------------- ------------- --------- ----------
20
30
30
30
30
30
30
7900 JAMES CLERK 7698 03-DEC-81 950 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 30
Vista EMP10EMPNO ENAME JOB---------- ---------- -----------------7739 KING PRESIDENT 7782 CLARK MANAGER 7934 MILLER CLERK
Unidad 3: Manejo de TABLAS
VistasRepresentación lógica de una tabla.
Yony Richard Montoya Burgos
Uso de VistasUsar vistas para:
Restringir acceso a la BD.Hacer consultas complejas fácilmente.Permitir independencia de datosPresentar de diferentes formas la misma información.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Características Vistas Simples Vistas Complejas Número de Tablas Una Una o Mas
Contiene Funciones No Si Contiene grupos de datos
No Si
Se permite DML Si No siempre
Unidad 3: Manejo de TABLAS
Vistas Simples y Complejas
72
Yony Richard Montoya Burgos
Creando Vistas Colocar una subconsulta dentro de la sentencia CREATE VIEW
La subconsulta puede contener SELECT complejosLa subconsulta no puede contener un ORDER BY.
CREATE [OR REPLACE] [FORCE/NOFORCE] VIEW vista[(alias[,alias]…)]
AS subconsulta[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY];
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Creación de VistasCrear vista en base a subconsultas
Una vista es una tabla (lógica) y por tanto puede verse su estructura con DESCRIBE
SQL> CREATE VIEW emp102 AS SELECT empno, ename, job3 FROM emp4 WHERE deptno = 10;
Vista creada.
SQL> DESCRIBE emp10
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Creación de VistasSe pueden usar alias en los campos de la subconsulta
La vista se crea con los nombres de los alias
SQL> CREATE VIEW sal302 AS SELECT empno NUMERO_EMPLEADO, ename NOMBRE,3 sal SALARIO4 FROM emp5 WHERE deptno=30;
Vista creada.
Unidad 3: Manejo de TABLAS
73
Yony Richard Montoya Burgos
SQL*PlusSELECT *FROM emp10;
7839 KING PRESIDENT7782 CLARK MANAGER7934 MILLER CLERK EMP
USER_VIEWSEMP10
SELECT empno, ename, jobFROM empWHERE deptno-10;
Unidad 3: Manejo de TABLAS
Consultando VistasUtilizar la tabal USER_VIEWS para ver la información de las vistas
Yony Richard Montoya Burgos
Modificación de VistasPara modificar una vista utilizar la cláusula CREATE OR REPLACE
SQL> CREATE OR REPLACE VIEW EMP102 (numero_empleado, nombre_empleado, titulo_trabajo)3 AS SELECT empno, ename, job4 FROM emp5 WHERE deptno=10;
View created.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Creación de Vistas ComplejasSe pueden usar funciones de grupo, joins y fucniones especiales para crear vistas
Unidad 3: Manejo de TABLAS
SQL> CREATE VIEW dept_sum2 (name, minsal, maxsal, avgsal)3 AS SELECT d.dname, MIN(e.sal),MAX(e.sal),4 AVG(e.sal)5 FROM emp e, dept d6 WHERE e.deptno = d.deptno7 GROUP BY d.dname;
View created.
74
Yony Richard Montoya Burgos
Operaciones DML con VistasSe pueden usar operaciones DML sólo con vistas simplesNo se puede eliminar una fila de una vista conteniendo:
Funciones de GrupoCláusula GROUP BYClaves DISTINCTPseudocolumna ROWNUM
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Operaciones DML con VistasNo se pueden modificar datos en una vista si
esta contiene:Columnas definidas por expresionesCualquier condición de las anteriores
No se pueden adicionar datos si:Cualquiera de las condiciones mencionadas antesExisten columnas NOT NULL en la tabla base que no
son parte de la vista.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
WITH CHECK OPTIONSe puede asegurar que las modificaciones no
afecten la forma en que se muestra la vista
Cualquier intento de cambiar el número de departamento vuelve invlaida la vista por tanto no es permitida
SQL> CREATE OR REPLACE VIEW emp202 AS SELECT *3 FROM emp4 WHERE deptno = 205 WITH CHECK OPTION CONSTRAINT emp20_ck;
View created.
Unidad 3: Manejo de TABLAS
75
Yony Richard Montoya Burgos
Negando operaciones DMLSe puede asegurar que las operaciones DML no
ocurran con la opción WITH READ ONLY
Se tiene un Error del Servidor Oracle al intentar realizar cualquier operación DML.
SQL> CREATE OR REPLACE VIEW emp102 (numero_empleado, nombre_empleado, trabajo)3 AS SELECT empno, ename, job4 FROM emp5 WHERE deptno = 106 WITH READ ONLY;
View created.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Eliminando VistasSe puede borrar las vistas con la instrucción
DROP VIEW
La información de las tablas base no se pierden.
Unidad 3: Manejo de TABLAS
DROP VIEW vista;
SQL> DROP VIEW emp10;View dropped.
Yony Richard Montoya Burgos
Análisis “Top-N”
Las consultas Top-N muestran los valores máximos y mínimos de una columna
Cuales son los 10 productos mas vendidos?
Cuales son los 10 empleados que ganan menos?
Unidad 3: Manejo de TABLAS
76
Yony Richard Montoya Burgos
Unidad 3: Manejo de TABLAS
SQL> SELECT [column_list], ROWNUM2 FROM (SELECT [column_list] FROM table3 ORDER BY Top-N_column)4 WHERE ROWNUM <= N
Realizando Análisis Top-NLa estructura del análisis Top-N es:
Yony Richard Montoya Burgos
Unidad 3: Manejo de TABLAS
SQL> SELECT ROWNUM as NUMERO, ename, sal2 FROM (SELECT ename, sal FROM emp3 ORDER BY sal DESC)4 WHERE ROWNUM <= 3;
NUMERO ENAME SAL1 KING 50002 SCOTT 30003 FORD 3000
Consultas Top-NMostrar los 3 empleados que ganan mas
Yony Richard Montoya Burgos
SecuenciasAutomaticamente generan números únicosEs un objeto compartido.Generalmente se usa para generar llaves primarias.Reemplaza aplicación de código.
Unidad 3: Manejo de TABLAS
77
Yony Richard Montoya Burgos
Creando SecuenciasDefinir una secuencia para generar números secuenciales automaticamente.
CREATE SEQUENCE sequence[INCREMENT BY n][START WITH n][{MAXVALUE n / NOMAXVALUE}][{MINVALUE n / NOMINVALUE}][{CYCLE / NOCYCLE}][{CACHE n / NOCACHE}];
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Creando SecuenciasINCREMENT BY n: Define el incremento entre valores, 1 por defecto.START WITH n: Especifica el primer valor de la secuenciaMAXVALUE n: Valor mayor de la secuenciaNOMAXVALUE: 1027 para secuencias ascendente y –1 en descendenteMINVALUE n: Mínimo valor de la secuenciaNOMINVALUE: 1 para secuencias ascendentes y -1027 para descendentesCYCLE|NOCYCLE: Se genera un ciclo cuando se llegue al valor máximo de la secuenciaCACHE n|NOCACHE: Valores que Oracle puede preasignar y guardar (por defecto 20)
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Creando SecuenciasCrear una secuencia llamada DEPT_DEPTNO para ser usada por la llave primaria de la tabla DEPT.
SQL> CREATE SEQUENCE dept_deptno2 INCREMENT BY 13 START WITH 91 4 MAXVALUE 1005 NOCACHE6 NOCYCLE;Sequence created.
Unidad 3: Manejo de TABLAS
78
Yony Richard Montoya Burgos
Verificando existencia de SecuenciasVerificar los valores de las secuencias en la tabla USER_SEQUENCES
La columna LAST_NUMBER muestra el siguiente valor disponible de la secuencia.
SQL> SELECT sequence_name, min_value, max_value 2 increment_by, last_number3 FROM user_sequences;
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Unidad 3: Manejo de TABLAS
Pseudocolumnas NEXTVAL y CURRVAL
NEXTVAL devuelve el siguiente valor de la secuencia.
CURRVAL obtiene el valor actual de la secuencia.
Yony Richard Montoya Burgos
Uso de SecuenciasInsertar el departamento llamado “MARKETING” en ‘San Diego’.
Ver el valor actual de la secuencia
SQL> INSERT INTO dept(deptno, dname, loc) 2 VALUES (dept_deptno.NEXTVAL,3 FROM ‘MARKETING’, ‘SAN DIEGO’);1 row created.
SQL> SELECT dept_deptno.CURRVAL 2 FROM dual;
Unidad 3: Manejo de TABLAS
79
Yony Richard Montoya Burgos
Unidad 3: Manejo de TABLAS
Uso de SecuenciasAlmacenar los valores de la secuencia en memoria permite acceso a estos valores.Se producen huecos en los valores de la secuencia cuando:
Ocurre un ROLLBACKEl sistema caeUna secuencia es usada en otra tabla
Yony Richard Montoya Burgos
Modificación de SecuenciasSe puede cambiar el valor de incremento, valor máximo, valor mínimo, opción de ciclo o de cache.
SQL> ALTER SEQUENCE dept_deptno2 INCREMENT BY 13 MAXVALUE 999999 4 NOCACHE5 NOCYCLE;Sequence altered.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Guias para modificar SecuenciasSólo los valores de secuencia futuros son afectados.La secuencia debe ser borrada y re-creada para inicializar la secuencia a un número diferente.Se realizan ciertas validaciones antes de modificar la secuencia.
Unidad 3: Manejo de TABLAS
80
Yony Richard Montoya Burgos
SQL> DROP SEQUENCE deptno_deptno;Sequence dropped.
Unidad 3: Manejo de TABLAS
Eliminando SecuenciasSe puede eliminar las secuencias usando la instrucción DROP SEQUENCE.
Yony Richard Montoya Burgos
IndicesObjeto de EsquemaUsados por el servidor Oracle para aumentar la velocidad en la recuperación de filas mediante la utilización de punteros.Reduce I/O de disco usando métodos de acceso rápido para localizar la informaciónEs independiente del indice de la tablaEs usado y mantenido por el servidor.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Creando IndicesSe pueden crear índices :
Automaticamente cuando se definen restricciones PRIMARY KEY o UNIQUEManualmente mediante la instrucción CREATE INDEX.
Unidad 3: Manejo de TABLAS
81
Yony Richard Montoya Burgos
CREATE INDEX indexON table (column[, column]…);
SQL> CREATE INDEX emp_ename_idx2 ON emp(ename);
Index created.
Unidad 3: Manejo de TABLAS
Creando IndicesSe pueden crear índices en una o mas columnas
Crear un índice para acceder de forma mas rápida al nombre de los empleados
Yony Richard Montoya Burgos
Unidad 3: Manejo de TABLAS
Cuando crear IndicesCuando la columna es usada frecuentemente en cláusulas WHERE o en condiciones JOIN.La columna contiene un amplio rango de valores.Contiene gran número de valores nulos.
Yony Richard Montoya Burgos
Unidad 3: Manejo de TABLAS
Cuando no crear IndicesCuando la tabla es pequeñaLas columnas no son a menudo usadas en condicionesLa tabla es actualizada frecuentemente
82
Yony Richard Montoya Burgos
Confirmando la existencia de IndicesLa vista USER_INDEXES contiene la información de índicesLa vista USER_IND_COLUMNS contiene la información de las columnas de los índices
SQL> SELECT ic.index_name, ic.column_name,2 ic.column_position col_pos, ix.uniqueness3 FROM user_indexes it, user_ind_columns ic4 WHERE ic.index_name = ix.index_name5 AND ic.table_name = ‘EMP’;
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
IndicesUn índice basado en funciones es un índice basado en expresiones.Las expresiones son construidas de columnas de tablas, constantes, funciones SQL y funciones de usuario definidas
SQL> CREATE TABLE test (col1 NUMBER);
SQL> CREATE INDEX test_index on test (col1, col1+10);
SQL> SELECT col1+10 FROM test;
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
SQL> DROP INDEX index;
SQL> DROP INDEX emp_ename_idx;Index dropped.
Unidad 3: Manejo de TABLAS
Eliminando IndicesEliminar índices con la instrucción DROP INDEX.
83
Yony Richard Montoya Burgos
SinónimosSimplifican el acceso a los objetosSe refieren a una tabla de otro usuarioAcortan la longitud de los nombres de los objetos
CREATE [PUBLIC] SYNONYM synonym
FOR object;
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Creando y Borrando SinónimosSe pueden crear sinónimos para tablas o para que todos tengan acceso a una tabla (PUBLIC).
Borrar Sinónimos con DROP SYNONYM
SQL> CREATE SYNONYM d_sum2 FOR dept_sum_vu;
Synonym created.
SQL> DROP SYNONYM d_sum;Synonym dropped.
Unidad 3: Manejo de TABLAS
Yony Richard Montoya Burgos
Manejo de UsuariosAdministrador de la Base de Datos
Nombre de Usuario y Contraseña
Usuarios
Unidad 4: Control de Usuarios
84
Yony Richard Montoya Burgos
PrivilegiosSeguridad de la BD
Seguridad del SistemaSeguridad de los datos
Privilegios del Sistema: Acceso a la BDPrivilegios de Objeto: Manipula el contenido de los Objetos de la BDEsquema: Colección de objetos.
Unidad 4: Control de Usuarios
Yony Richard Montoya Burgos
Privilegios de SistemaMas de 80 privilegios son disponiblesEl Administrador de la BD tiene privilegios de sistema de alto nivel:
Crear UsuariosEliminar UsuariosEliminar TablasBack-Up de Tablas
Unidad 4: Control de Usuarios
Yony Richard Montoya Burgos
Creación de UsuariosSe pueden crear usuario mediante el comando CREATE USER
CREATE USER userIDENTIFIED BY password;
SQL> CREATE USER scott2 IDENTIFIED BY tiger;
User created.
Unidad 4: Control de Usuarios
85
Yony Richard Montoya Burgos
Privilegios de UsuariosUna vez creado el usuario se le deben asignar privilegios de sistema
Por lo general se tendran los privilegios de:CREATE SESSIONCREATE TABLECREATE SEQUENCECREATE VIEWCREATE PROCEDURE
GRANT privilege [, privilege. . .]TO user [, user. . .];
Unidad 4: Control de Usuarios
Yony Richard Montoya Burgos
Privilegios de UsuariosSe pueden asignar privilegios específicos por usuario
Unidad 4: Control de Usuarios
SQL> GRANT create table, create sequence, create view2 TO scott;
Grant succeeded.
Yony Richard Montoya Burgos
Usuarios
Admin
Privilegios
Asignando Privilegiossin un Rol
Asignando privilegios con un Rol
Unidad 4: Control de Usuarios
Roles de Usuarios
86
Yony Richard Montoya Burgos
Manejo de RolesCrear un ROL
Asignar privilegios a un ROL y Rol a usuarios
SQL> CREATE ROLE admin;role created.
SQL> GRANT create table, create view2 TO admin;
Grant succeeded.
SQL> GRANT admin TO CURSO_01,CURSO_02;Grant succeeded.
Unidad 4: Control de Usuarios
Yony Richard Montoya Burgos
Manejando ContraseñasEl administrador crea la cuenta e inicializa la contraseñaEl Usuario puede (y debe) cambiar su contraseña
SQL> ALTER USER <usuario>2 IDENTIFIED BY <clave>;
Unidad 4: Control de Usuarios
Yony Richard Montoya Burgos
Privilegios de Objeto Table View Sequence ProcedureALTER X XDELETE X XEXECUTE XINDEX XINSERT X XREFERENCES XSELECT X X XUPDATE X X
Unidad 4: Control de Usuarios
Privilegios de Objeto
87
Yony Richard Montoya Burgos
Privilegios de ObjetoLos privilegios varian de objeto a objetoEl propietario tiene todos los privilegios del objetoEl propietario puede asignar derechos de sus objetos
GRANT object_priv [(columns)]ON objectTO {user/role/PUBLIC}[WITH GRANT OPTION];
Unidad 4: Control de Usuarios
Yony Richard Montoya Burgos
Privilegios de ObjetoAsignando derechos a la tabla EMP
Asignar derechos de actualizar ciertos campos
SQL> GRANT select2 ON emp3 TO CURSO_01,CURSO_02;
Grant succeeded.
SQL> GRANT update (dname, loc)2 ON dept3 TO CURSO_25, CURSO_30;
Grant succeeded.
Unidad 4: Control de Usuarios
Yony Richard Montoya Burgos
Privilegios de ObjetoSe puede asignar privilegios y además permitir que otros asignen ese privvilegio
Para dar derecho a todos, usar la cláusula PUBLIC
SQL> GRANT select, insert2 ON dept3 TO CURSO_374 WITH GRANT OPTION;
Grant succeeded.
SQL> GRANT select2 ON yony.dept3 TO PUBLIC;
Grant succeeded.
Unidad 4: Control de Usuarios
88
Yony Richard Montoya Burgos
Tabla del Diccionario DescripciónROLE_SYS_PRIVS Privilegios del sistema asignadosROLE_TAB_PRIVS Privilegios de Tabla asignados a un
ROLUSER_ROLE_PRIVS Roles de un usuarioUSER_TAB_PRIVS_MADE Privilegios de Objeto asignados a
un usuarioUSER_TAB_PRIVS_RECD Privilegios de objeto de un usuarioUSER_COL_PRIVS_MADE Columnas de los privilegios de
objetoUSER_COL_PRIVS_RECD Privilegios de columna asignaos a
un usuario
Unidad 4: Control de Usuarios
Confirmando Privilegios
Yony Richard Montoya Burgos
Quitando PrivilegiosSe pueden Revocar privilegios usando REVOKELos privilegios asignados a travez de WITH GRANT OPTION también serán eliminados
REVOKE {privilege [, privilege…]/ALL}ON objectFROM {user[, user…]/role/ PUBLIC}[CASCADE CONSTRAINTS];
Unidad 4: Control de Usuarios
Yony Richard Montoya Burgos
Unidad 4: Control de Usuarios
SQL> REVOKE select, insert2 ON dept3 FROM CURSO_37;
Revoke succeeded.
Ejemplo
89
Yony Richard Montoya Burgos
PL/SQLEs una extensión del SQL con características de lenguaje de programaciónSentencias de manipulación de datos y de consulta SQL son incluidas dentro de unidades de código procedural.
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Beneficios de PL/SQLPL/SQL permite mantener librerías compartidas (Paquetes) en el servidor de Base de DatosElimina el proceso de programación de aplicaciones a nivel usuario teniendo toda la funionalidad (interacción con la BD) en el mismo servidor de BD.
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Beneficios de PL/SQLMejora el rendimiento.
Unidad 5: PL/SQL
Aplicación Otros DBMS
Aplicación ORACLE con PL/SQL
SQL
SQL
SQL
SQL
IF ... THEN
SQL
ELSE
SQL
END IF;
SQL
90
Yony Richard Montoya Burgos
Beneficios de PL/SQLDesarrollo modularizado de programas
Unidad 5: PL/SQL
DECLARE
BEGIN
EXCEPTION
END;
Yony Richard Montoya Burgos
Beneficios de PL/SQLEs portable entre entornos que soporten PL/SQL.Se pueden declarar identificadores (variables, cursores, constantes y excepciones).Se puede programar con estructuras de control de lenguaje proceduralSe pueden manipular errores
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Estructura de PL/SQL
Unidad 5: PL/SQL
DECLARE – Opcional
•Variables, Cursores, excepciones definidas por el usuario
BEGIN – Requerido
•Sentencias SQL
•Sentencias PL/SQL
EXCEPTION – Opcional
•Acciones a ejecutar cuando ocurre un error
END; - Requerido
91
Yony Richard Montoya Burgos
Tipos de Bloques PL/SQLPL/SQL maneja tres tipos de bloques:
ANONIMO PROCEDIMIENTO FUNCION
Unidad 5: PL/SQL
[DECLARE]
BEGIN
--Sentencias—
[EXCEPTION]
END;
PROCEDURE nombre
IS
BEGIN
--Sentencias—
[EXCEPTION]
END;
FUNCTION nombre RETURN tipo
IS
BEGIN
--Sentencias—
[EXCEPTION]
END;
Yony Richard Montoya Burgos
Uso de Variables en PL/SQLUsar Variables para:
Almacenamiento Temporalmente de datos.Manipulación de valores almacenadosReusabilidadFácil de mantener
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Manipulando Variables en PL/SQLDeclarar e inicializar variables en la sección de declaración.Asignar nuevos valores a variables en la sección de ejecución.Valores pasados a bloques PL/SQL a través de parámetros.Ver resultados a través
Unidad 5: PL/SQL
92
Yony Richard Montoya Burgos
Tipos de VariablesSe pueden tener:
Escalares
Compuestas
De referencia
LOB
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Declaración de VariablesSintaxis
Ejemplos
Unidad 5: PL/SQL
Identificador [CONSTANT] tipo [NOT NULL]
[:= | DEFAULT expr];
DECLARE
v_fecha DATE;
v_numero_dept NUMBER(2) NOT NULL:=10;
v_direccion VARCHAR2(3) := ‘Prueba’;
c_comision CONSTANT NUMBER := 1400;
Yony Richard Montoya Burgos
Declaración de VariablesInicializar variables definidas como NOT NULLInicializar variables utilizando := o la palabra reservada DEFAULTDeclarar sólo un identificador por línea
Unidad 5: PL/SQL
93
Yony Richard Montoya Burgos
Tipos de Datos EscalaresSe pueden usar:
VARCHAR2NUMBERDATECHARLONGLONG_RAWBOOLEANBINARY_INTEGERPLS_INTEGER
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Atributo %TYPESe puede declarar un variable de acuerdo a:
Una definición de columnad de la BDOtra declaración previa de variable
Indicar con el prefijo %TYPE
Unidad 5: PL/SQL
...
v_nombre emp.ename%TYPE;
v_balance NUMBER(7,2)
v_balance_minimo v_balance%TYPE := 10;
...
Yony Richard Montoya Burgos
Declaración de Variables BoolenasSólo se pueden asignar valores TRUE, FALSE y NULL a variables boolenasSe pueden conectar por operadores AND, OR y NOTExpresiones aritméticas de carácter y de fecha pueden ser usadas para devolver valores Booleanos
Unidad 5: PL/SQL
94
Yony Richard Montoya Burgos
Variables de enlaceCuando se crea una variable de enlace, tanto SQL como SQL*Plus pueden usar su valorEn SQL*Plus se declaran con VARIABLE
Se puede ver el valor de una variable de enlace con PRINT
Unidad 5: PL/SQL
VARIABLE nombre tipo
SQL> VARIABLE mensaje VARCHAR(30)
PRINT variable
SQL> PRINT mensaje
Yony Richard Montoya Burgos
Variables de enlacePara usar variables que no son PL/SQL en el entorno PL/SQL se debe usar el prefijo (:)Se pueden referenciar variables de enlaceSe pueden usar variales de SQL*Plus con (&)
Unidad 5: PL/SQL
:<variable>
Ejemplo:
:mensaje := “Esto es una prueba”;
Yony Richard Montoya Burgos
Ejemplo
Unidad 5: PL/SQL
VARIABLE g_sal_mensual NUMBER
ACCEPT p_sal_anual PROMPT ‘Ingrese Salario Anual: ‘
DECLARE
v_salarioNUMBER(9,2) := &p_sal_anual;
BEGIN
:g_sal_mensual := v_salario / 12;
END;
/
PRINT g_salario_mensual
95
Yony Richard Montoya Burgos
Mostrar Texto desde PL/SQLSe puede mostrar información desde bloques PL/SQL con la función PUT_LINEUsar la funcionalidad del paquete DBMS_OUTPUTPar usarlo con SQL*Plus habilitar con SET SERVEROUTPUT ON
Unidad 5: PL/SQL
SET SERVEROUTPUT ON
ACCEPT p_sal_anual PROMPT “Ingrese salario anual: “
DECLARE
v_salario NUMBER(9,2) := &p_sal_anual;
BEGIN
v_salario:=v_salario/12;
DBMS_OUTPUT.PUT_LINE(‘El salario mensual es: ‘|| TO_CHAR(v_salario));
END;
/
Yony Richard Montoya Burgos
EjerciciosCrear un Bloque PL/SQL para mostrar la siguiente salida:G_MENSAJE--------------------------Mi Bloque PL/SQL FuncionaCrear un bloque que declare dos variables (V_CHAR y V_NUM), asigne el valor (’42 es la respuesta’ y los dos primeros caracteres de V_CHAR respectivamente) de esas variables a variables SQL*Plus e imprima su resultado:
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Comentando el códigoPara comentar una líneas usar (--)Para comentar bloques utilizar /* para abirr un bloque de comentario y */ para cerrarlo
Unidad 5: PL/SQL
...
v_salario NUMBER(9,2);
BEGIN
/* Calcula el salairo anual basado en el salario mensual */
v_salario:=&p_salario_mensual *12
END; -- Fin de la transacción
96
Yony Richard Montoya Burgos
Funciones PL/SQLSe pueden usar todas las funciones que se tienen en SQLNo se pueden utilizar funciones de Grupo (AVG, SUM, MIN, etc.) ni DECODE
Unidad 5: PL/SQL
v_dire_envio := UPPER(v_name)||CHAR(10)||
INITCAP(v_direccion)||CHR(10)||v_estado||
CHR(10)||v_zip;
Yony Richard Montoya Burgos
Conversión de TiposConvertir datos a tipos comparablesLa no conversión puede resultar en error o afectar el rendimientoUsar funciones de conversión:
TO_CHARTO_DATETO_NUMER
Unidad 5: PL/SQL
DECLARE
v_date VARCHAR2(15);
BEGIN
SELECT TO_CHAR(hiredate,’MON. DD,YYYY’)
INTO v_date
FROM emp WHERE empno=7839;
END;
Yony Richard Montoya Burgos
Bloques PL/SQLSe pueden anidar bloquesUna sección de excepción puede contener bloques anidadosUn bloque anidado es una sentenciaUn identificador es visible en el bloque en el que fue declarado y en todos los sub-bloques anidados
Unidad 5: PL/SQL
97
Yony Richard Montoya Burgos
Operadores PL/SQLLos operadores utilizados en SQL son también permitidos en PL/SQLSe tiene el operador exponencial (**)
Unidad 5: PL/SQL
Operadores de incremento
v_contador := vcontador+1;
Operadores de comparación
v_igual := (v_n1 = v_n2);
Operadores de campo
v_valido := (v_empno IS NOT NULL);
Yony Richard Montoya Burgos
Variables en consultasLas variables que forman parte de una consulta deben asignarse con la cláusula INTO
Unidad 5: PL/SQL
DECLARE
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal
FROM emp WHERE empno = 7369;
DBMS_OUTPUT.PUT_LINE(‘El salario es: ‘||
TO_CHAR(v_sal));
END;
Yony Richard Montoya Burgos
Convención de nombrado de Variables
Unidad 5: PL/SQL
Identificador ConvenciónVariable v_nameConstante c_nameCursor name_cursorExcepción e_nameTipo de Tabla name_table_typeTabla name_tableTipo de Registro name_record_typeRegistro name_recordVariable SQL*Plus p_nameVariable de enlace g_name
98
Yony Richard Montoya Burgos
EjerciciosDetermine el ámbito de las variables
Unidad 5: PL/SQL
DECLARE
V_SAL NUMBER(7,2) := 60000;
V_COMM NUMBER(7,2) := v_sal * .20;
V_MENSAJE VARCHAR2(255) := ‘ tiene comisión ‘;
BEGIN
DECLARE
V_SAL NUMBER(7,2) := 50000;
V_COMM NUMBER(7,2) := 0;
V_TOTAL_COMP NUMBER(7,2) := V_SAL+V_COMM;
BEGIN
V_MENSAJE := ‘CLERK no ‘|| V_MENSAJE;
END;
V_MENSAJE := ‘SALESMAN ‘||V_MENSAJE;
END;
Yony Richard Montoya Burgos
EjerciciosCrear un bloque PL/SQL que calcule la compensación total por un año. El salario anual y el porcentaje de bono son pasados al bloque a traves de variable SQL*Plus, el bono necesita ser convertido de un número entero a un decimal (por ejemplo 15 a .15). Si el salario es nulo, darle el valor de 0 antes de calcular la compensación
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Sentencias SQL en PL/SQLRecuperar información de la Base de Datos con la instrucción SELECT
Unidad 5: PL/SQL
SELECT campos
INTO {variable[,variable]...|registro}
FROM tabla
WHERE condicion
99
Yony Richard Montoya Burgos
Sentencias SQL en PL/SQLLa cláusula INTO es requeridaSe debe tener correspondecia de resultados con variablesSólo se debe devolver una fila en la consulta
Unidad 5: PL/SQL
DECLAREv_deptno NUMBER(2);v_loc VARCHAR2(15);
BEGINSELECT deptno,locINTO v_deptno, v_locFROM deptWHERE dname=‘SALES’;DBMS_OUTPUT.PUT_LINE(‘Departamento:’||TO_CHAR(v_deptno)||
’ se ubica en: ‘||v_loc);END;/
Yony Richard Montoya Burgos
Recuperando InformaciónSe puede devolver funciones de grupo como valores de la consulta
Unidad 5: PL/SQL
DECLARE
v_sum_sal emp.sal%TYPE;
v_deptno NUMBER NOT NULL :=10;
BEGIN
SELECT SUM(sal)
INTO v_sum_sal
FROM emp
WHERE deptno=v_deptno;
END;
Yony Richard Montoya Burgos
Instrucciones DML en PL/SQLTodas las instrucciones DML son permitidas en bloques PL/SQLSe pueden usar operadores y funciones en los parámetros de las instrucciones DML junto con código PL/SQL
Unidad 5: PL/SQL
100
Yony Richard Montoya Burgos
CursoresUn cursor es un área de trabajo privadaExisten dos tipos de cursores
ImplícitosExplícitos
El servidor Oracle utiliza cursores implícitos para ejecutar sus sentencias SQLCursores explícitos son declarados por el programador
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Atributos de CursoresSe usan los siguientes atributos
Unidad 5: PL/SQL
Evalua TRUE si la última instrucción no afecta ninguna fila.
SQL%NOTFOUND
Atributo booleano que evalua TRUE si la última instrucción SQL afecta una o mas filas
SQL%FOUND
Número de filas afectadas por la última instrucción SQL
SQL%ROWCOUNT
Yony Richard Montoya Burgos
Atributos de Cursores
Unidad 5: PL/SQL
VARIABLE g_filas_borradas VARCHAR2(30)
DECLARE
v_order_id NUMBER :=605;
BEGIN
DELETE FROM item
WHERE orderid=v_order_id;
:g_filas_borradas:=(SQL%ROWCOUNT||’ fila borradas.’);
END;
/
PRINT g_filas_borradas
101
Yony Richard Montoya Burgos
Estructuras de ControlSe puede cambiar el flujo de información en PL/SQLEstructura IF
IF-THEN-END IFIF-THEN-ELSE_END IFIF-THEN-ELSIF-ENDIF
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Sentencia IFSintaxis
Unidad 5: PL/SQL
IF condicion THEN
sentencias;
[ELSIF condicion THEN
sentencias; ]
[ELSE
sentencias;]
END IF;
Yony Richard Montoya Burgos
Sentencias IF simples
Unidad 5: PL/SQL
.........
IF v_enam=‘MILLER’ THEN
v_job:=‘SALESMAN’;
v_deptno:=35;
v_new_comm:=sal*0.20;
END IF;
.........
102
Yony Richard Montoya Burgos
Sentencia IF-THEN-ELSESe puede usar la sentencia IF-THEN-ELSE.Todas las sentencias IF se pueden anidar.
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Sentencia IF-THEN-ELSIFManeja flujos de información agrupados
Unidad 5: PL/SQL
Acciones THEN
Acciones THEN
Condicion IF
Condicion ELSE
Acciones
ELSE
Yony Richard Montoya Burgos
CiclosSe puede crear ciclos para repetir un conjunto de sentencias varias veces..Se tienes tres tipos de ciclos.
Ciclo Básico LOOPCiclo FORCiclo WHILE
Unidad 5: PL/SQL
103
Yony Richard Montoya Burgos
LOOPSintaxis
Se puede salir del ciclo usando la sentencia EXIT
Unidad 5: PL/SQL
LOOP -- Delimitador
sentencias; -- Sentencias
. . .
EXIT [WHEN condicion]; -- Sentencia EXIT
END LOOP; -- Delimitador
Yony Richard Montoya Burgos
Ejemplo
Unidad 5: PL/SQL
DECLARE
v_orderid item.orderid%TYPE := 601;
v_counterNUMBER(2) := 1;
BEGIN
LOOP
INSERT INTO item(ordid,itemid)
VALUES(v_ordid,v_counter);
v_counter := v_counter+1;
EXIT WHEN v_counter > 10;
END LOOP;
END;
/
Yony Richard Montoya Burgos
Ciclo FORSintaxis
Se incrementa contador de uno en uno
Unidad 5: PL/SQL
FOR contador IN [REVERSE] minimo..maximo
LOOP
sentencias;
. . .
END LOOP;
104
Yony Richard Montoya Burgos
Ejemplo
Unidad 5: PL/SQL
DECLARE
v_orderid item.orderid%TYPE := 601;
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO item(ordid,itemid)
VALUES(v_ordid,i);
END LOOP;
END;
/
Yony Richard Montoya Burgos
Ciclo WHILESintaxis
Permite repetir sentenias de acuerdo a una condición
Unidad 5: PL/SQL
WHILE condicion LOOP
sentencias;
. . .
END LOOP;
Yony Richard Montoya Burgos
Ejemplo
Unidad 5: PL/SQL
ACCEPT p_nueva_orden PROMPT ‘Ingrese el número de orden: ‘
ACCEPT p_items PROMPT ‘Ingrese el número de items de la orden: ‘
DECLARE
v_contador NUMBER(2) := 1;
BEGIN
WHILE v_contador<=&p_items LOOP
INSERT INTO item(ordid,itemid)
VALUES(&p_nueva_orden,v_contador);
v_contador:=v_contador+1;
END LOOP;
COMMIT;
END;
/
105
Yony Richard Montoya Burgos
Ciclos anidados y EtiquetasSe pueden anidar ciclos en múltiples niveles.Para poder distinguir los bloques se debe etiquetarlos usando << >>.Salir del ciclo con la cláusula EXIT referenciado la etiqueta del mismo
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Ejemplo
Unidad 5: PL/SQL
...BEGIN
<<Loop_Externo>>LOOP
v_cont := v_cont+1;
EXIT WHEN v_cont>10;<<Loop_Interno>>
LOOP...
-- Dejar ambos ciclosEXIT Loop_Externo WHEN v=‘SI’;
-- Dejar solo ciclos internoEXIT WHEN cond=‘SI’;
....END LOOP Loop_Interno;
...END LOOP Loop_Externo;
END;
Yony Richard Montoya Burgos
EjerciciosCree un bloque SQL para calcular la comisión de un empleado, ingresar el código del empleado por teclado. Se debe cumplir las siguientes restricciones:
Si el salario del empleado es menor a 1000, la comisión es del 10% del salarioSi el salario esta entre 1000 y 1500 la comisión será del 15%Si el salario excede los 1500 la comisión será del 20%Si no se tiene un salario para el empleado, la comisión será de 0.
Unidad 5: PL/SQL
106
Yony Richard Montoya Burgos
Tipos de Datos CompuestosSe pueden usar tipos:
Registros PL/SQLTablas PL/SQL
Pueden contener componentes internos.Son reusables.
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Registros PL/SQLAlmacenan la información de una fila de alguna tabla.
Donde “campo” puede ser:
Unidad 5: PL/SQL
TYPE nombre_tipo IS RECORD
(campo[,campo]........);
Identificador nombre_tipo;
nombre_campo {tipo_campo|variable%TYPE|tabla.columna%TYPE
|tabla%ROWTYPE}
[[NOT NULL] {:=|DEFAULT} expr]
Yony Richard Montoya Burgos
RegistrosSe pueden utilizar inicializaciones, campos NOT NULL y %TYPE en la declaración de registros
Unidad 5: PL/SQL
...
TYPE emp_record_type IS RECORD
(ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER(9,2));
emp_record emp_record_type
END;
107
Yony Richard Montoya Burgos
Acceso a Registros PL/SQLPara acceder a los campos utilizar la estructura variable.campo
Unidad 5: PL/SQL
DBMS_OUTPUT.PUT_LINE(‘Trabajo: ‘
||emp_record.job);
emp_record.job:=‘CLERK’;
Yony Richard Montoya Burgos
Atributo %ROWTYPESe pueden declarar variables de acuerdo a la estructura de las tablas.Usar el prefijo %ROWTYPELos campos del registro toman el nombre y los tipos de las columnas de la tabla o vista.
Unidad 5: PL/SQL
DECLARE
emp_record emp%ROWTYPE;
BEGIN
emp_record.sal := 10;
Yony Richard Montoya Burgos
Ejemplo
Unidad 5: PL/SQL
DECLARE
emp_recordemp%ROWTYPE;
BEGIN
SELECT * INTO emp_record
FROM emp
WHERE empno = &numero_emp;
DBMS_OUTPUT.PUT_LINE(emp_record.ename||TO_CHAR(emp_record.sal)||emp_record.job);
END;
/
108
Yony Richard Montoya Burgos
Tablas PL/SQLEstan compuestas de:
Llaves Primarias del tipo BINARY_INTEGERColumnas de tipo escalar o registro
Se incrementan dinámicamente pues no tienen restricciones
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Tablas PL/SQLSintaxis
Unidad 5: PL/SQL
TYPE nombre_tipo IS TABLE OF
{tipo_columna|variable%TYPE|tabla.columna%TYPE}
[NOT NULL]
[INDEX BY BINARY_INTEGER];
variable nombre_tipo;
Yony Richard Montoya Burgos
Tablas PL/SQLPara acceder a un valor específico de la tabla usar un índice numérico
La llave primari va desde –2147483647 hasta 2147483647.No necesariamente el índice debe comenzar en 1
Unidad 5: PL/SQL
nombre_tabla(llave_primaria)
109
Yony Richard Montoya Burgos
Métodos de las Tablas de PL/SQLSe tienen los siguientes métodos de tablas:
Unidad 5: PL/SQL
Verdad si el elemento n existe en la tablaEXISTS(n)
Borra todos los elementos de una tablaElimina el último elementoIncrementa el tamaño de la tablaIndice que sucede al índice n
Indice que precede al índice n
Primer y último índice de la tabla o NULL
Número de elementos actuales de la tabla
DELETETRIMEXTEND(n,i)NEXT(n)
PRIOR(n)
FIRST/LAST
COUNT
Yony Richard Montoya Burgos
Ejemplo
Unidad 5: PL/SQL
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE date_table_type IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
hiredate_table date_table_type;
BEGIN
ename_table(1) := ‘Prueba’;
hiredate_table(8) := SYSDATE+7;
DBMS_OUTPUT.PUT_LINE(ename_table.COUNT);
END;
/
Yony Richard Montoya Burgos
Tablas en base a Registros PL/SQLDefinir tablas en base a filas de una tablas de la BD
Se puede acceder a la información de la tabla en la forma:
tabla(indice).campo
Unidad 5: PL/SQL
DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
110
Yony Richard Montoya Burgos
EjerciciosCree un bloque SQL que recupere el nombre de cada departamento e imprima el nombre en la pantalla. Considere que cada número de Departamento es un múltiplo de 10.Recupere toda la información acerca de cada departamento de la tabla DEPT e imprima su información en el formato:
Depto. <deptno>, <dname> esta ubicado en <loc>Puede usar una tabla temporal MI_TABLA_DEPT que
sólo tenga los campos número, nombre y ubicación.
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
EjerciciosCrree un bloque PL/SQL para imprimir la información de una orden (tabla ORD), ingrese el número de orden por teclado, en el formato siguiente:
Orden <id> fue realizada el <fecha> y enviada el <fecha> por un total de <precio>
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
CursoresCada sentencia ejecutada por Oracle tiene su cursor que puede ser:
Cursor ImplicitoCursor Explicito
Unidad 5: PL/SQL
111
Yony Richard Montoya Burgos
Cursores ExplícitosPara usar cursores se debe:
Declarar un cursor (DECLARE)Abrir o hacer activo el cursor (OPEN)Cargar la información de la fíla actual en variables (FETCH)Cerrar el cursor activo (CLOSE)
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Declarando CursoresSintaxis
No incluir la sentencia INTO en la declaración.Si se requiere de ua secuencia específica del orden de datos usar ORDER BY
Unidad 5: PL/SQL
CURSOR nombre IS sentencia_SQL;
Yony Richard Montoya Burgos
Declarando CursoresEjemplo
Unidad 5: PL/SQL
DECLARE
CURSOR emp_cursor IS
SELECT empno, ename FROM emp;
CURSOR dept_cursor IS
SELECT *
FROM dept WHERE deptno=10;
BEGIN
112
Yony Richard Montoya Burgos
Abriendo CursoresSintaxis
Abrir un cursor vuelve activo el mismo.Si no se tienen filas en la consulta no se devuelve ninguna excepciónSe debe usar los atributos de cursores para verificar si se tiene información
Unidad 5: PL/SQL
OPEN nombre;
Yony Richard Montoya Burgos
Recuperando Información de CursoresSintaxis
Obtiene los valores de la fila actual en variablesSe debe incluir el mismo número de variablesEmparejar las variables a las columnas
Unidad 5: PL/SQL
FETCH nombre INTO [var1,var2]|registro;
Yony Richard Montoya Burgos
Recuperando información de CursoresEjemplo
Unidad 5: PL/SQL
....
OPEN cursor;
LOOP
FETCH cursor INTO variables
EXIT WHEN ....;
.....
-- Procesar información
END LOOP;
113
Yony Richard Montoya Burgos
Cerrar un CursorSintaxis
Cerrar el cursosr para liberar memoria después de usarloSe puede reabrir el cursor si fuese necesario
Unidad 5: PL/SQL
CLOSE nombre;
Yony Richard Montoya Burgos
Atributo %ISOPENVerifica si el cursor esta abiertoUtil para no tener errores de recuparación de información
Unidad 5: PL/SQL
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
LOOP
FETCH emp_cursor ...
Yony Richard Montoya Burgos
Atributo %ROWCOUNT y %NOTFOUND%ROWCOUNT permite recuperar un número específico de registrosPermite controlar los ciclos de lectura de cursores
Unidad 5: PL/SQL
114
Yony Richard Montoya Burgos
Cursores y RegistrosSe pueden combinar cursores y registros para manjear mejor la información
Unidad 5: PL/SQL
DECLARE
CURSOR emp_cursor IS SELECT empno,ename
FROM emp;
emp_record emp_cursor%ROWTYPE
BEGIN...............
Yony Richard Montoya Burgos
Ciclos con cursoresSe pueden manejar ciclos para cursores directamente sin usar FETCH
Unidad 5: PL/SQL
FOR registro IN cursor LOOP
comandos;
comandos;
END LOOP;
Yony Richard Montoya Burgos
Ciclos con cursoresEjemplo
Unidad 5: PL/SQL
DECLARE
CURSOR emp_cursor IS SELECT ename,deptno
FROM emp;
BEGIN
FOR emp_record IN emp_cursor LOOP
DBMS_OUPUT.PUL_LINE(emp_record.ename);
END LOOP;
END;
115
Yony Richard Montoya Burgos
Cursores con subconsultasSe pueden utilizar subconsultas para crear ciclos con cursores
Unidad 5: PL/SQL
FOR em_recor IN (SELECT ename,deptno FROM emp) LOOP
comandos;
comandos;
END LOOP;
Yony Richard Montoya Burgos
EjerciciosRecuperar los 5 primeros items (uno por uno) de una orden (introducida por teclado). Como cada producto es procesado por las ordenes, calcular el total general de la orden e imprimirla en la pantalla
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Cursores con parámetrosSintaxis
Donde parámetro puede ser:Nombre [IN] tipo [{:= | DEFAULT} expr]
Unidad 5: PL/SQL
CURSOR nombre
[(parama tipo, param tipo, ...)]
IS
consulta SQL
116
Yony Richard Montoya Burgos
Cursores con ParámetrosLos parámetros pueden utilizarse como variables de entrada del cursor
Unidad 5: PL/SQL
DECLARE
CURSOR emp_cursor
(v_deptno NUMBER, v_job VARCHAR2) IS
SELECT empno,ename FROM emp
WHERE deptno = v_deptno AND job=v_job;
BEGIN
OPEN emp_cursor(10,’CLERK’);
Yony Richard Montoya Burgos
ExcepcionesSe pueden lanzar excepciones cuando ocurre un error, o cuando se envia explicitamente (usuario)La excepción es manejada por un manejar o propagada a las llamadas de entorno
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Tipos de ExcepcionesPredefinidas por el Servidor Oracle, lanzadas implicitamenteNo-Predefinidas por el Servidor Oracle.Definidas por el Usuario
Unidad 5: PL/SQL
117
Yony Richard Montoya Burgos
Manejando ExcepcionesPara procesar una excepción usar el bloque EXCEPTION:
Unidad 5: PL/SQL
EXCEPTION
WHEN excep1 [OR excep2...] THEN
sentencias
[WHEN excep3[OR excep4...] THEN
sentencias]
[WHEN OTHERS THEN
sentencias]
Yony Richard Montoya Burgos
Excepciones Predefinidas por el ServidorEntre las mas usadas se tienen:
NO_DATA_FOUNDTO_MANY_ROWSINVALID_CURSORZERO_DIVIDEDUP_VAL_ON_INDEXCURSOR_ALREADY_OPEN
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
Ejemplo
Unidad 5: PL/SQL
BEGIN
SELECT ..... COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
sentencias;
WHEN TOO_MANY_ROWS THEN
sentencias;
WHEN OTHERS THEN
sentencias;
END;
118
Yony Richard Montoya Burgos
Excepciones No-Predefinidas por el ServidorErrores no predefinidos por el Servidor, Por ejemplo capturar el error –2292 (violación de integridad) y manejarlo dentro del bloque PL/SQL.Asociar el error con una definición de excepcion.
Unidad 5: PL/SQL
Excepcion EXCEPTION
PRAGMA EXCEPTION_INIT(excepcion, error);
Yony Richard Montoya Burgos
Ejemplo
Unidad 5: PL/SQL
DECLARE
e_ejemplo EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ejemplo,-2292);
v_deptno dept.deptno%TYPE:=10;
BEGIN
DELETE FROM dept WHERE deptno=v_deptno;
COMMIT;
EXCEPTION
WHEN e_ejemplo THEN
DBMS_OUTPUT.PUT_LINE(‘No se puede eliminar el departamento ’||
TO_CHAR(v_deptno)||’. Existen Empleados.’);
END;
Yony Richard Montoya Burgos
Excepciones Predefinidas por el UsuarioCrear la exceptionDispararla de acuerdo a una condición
Unidad 5: PL/SQL
DECLARE
Excepcion EXCEPTION
BEGIN
..........
RAISE Excepcion
END;
119
Yony Richard Montoya Burgos
Ejemplo
Unidad 5: PL/SQL
DECLARE
e_ejemplo EXCEPTION;
BEGIN
UPDATE product SET descrip=‘&descripcion’
WHERE prodid=&producto;
IF SQL%NOTFOND THEN
RAISE e_ejemplo;
END IF;
COMMIT;
EXCEPTION
WHEN e_ejemplo THEN
DBMS_OUTPUT.PUT_LINE(‘No existe el producto.’);
END;
Yony Richard Montoya Burgos
Funciones para manipular ExcepcionesSQLCODE
Devuelve el valor numérico del errorSQLERRM
Devuelve el mensaje asociado con el error.
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
RAISE_APPLICATION_ERRORPermite comunicar un error único al usuario asi como un mensaje de errorSe debe manejar solamente valores entre –20000 y –20999El mensaje puede tener hasta 2048 bytes de largo
Unidad 5: PL/SQL
RAISE_APPLICATION_ERROR(numero,mensaje);
120
Yony Richard Montoya Burgos
EjerciciosEscribir un bloque PL/SQLpara imprimir el número de empleados que ganan mas o menos $100 del valor entrado por el usuario
Si no hay empleados con ese rango, imprimir un mensaje al usuario indicando este caso (Usar excepciones)Si hay uno o mas dentro del rango, el mensaje debería de indicar el número de empleados con ese salarioManejar las otras excepciones con la mas indicada. EL mensaje debería de indicar el error ocurrido.
Unidad 5: PL/SQL
Yony Richard Montoya Burgos
PaquetesAgrupar logicamente un conjunto de funciones y procedimientosCrear Módulos con funcionalidad compartidaEn PL/SQL esta compuesto por una definición del paquete y un cuerpo.
Unidad 6: Paquetes
Yony Richard Montoya Burgos
DefiniciónCrear la especificación del paquete
Crear el cuerpo
Unidad 6: Paquetes
CREATE PACKAGE BODY nombre AS
-- Código y encabezados de las funciones y procedimientos
BEGIN
CREATE PACKAGE nombre AS
-- Encabezados de funciones y procedimientos
END nombre;
121
Yony Richard Montoya Burgos
LlamadaSe puede llamar un paquete en cualquier momento mediante la estructura:
Unidad 6: Paquetes
Nombre_paquete.funcion_procedimiento[(parametros)];
manage_employee.hire_emp(‘Pru’,’Otro’,12324,’12-MAR-03’);
Yony Richard Montoya Burgos
TriggersPermiten mantener la integridad de la BDIncrementan las restricciones de forma mas avanzadaEstan asociados a una tabla y son llamados automáticamente de acuerdo a un evento (INSERT, UPDATE, DELETE)
Unidad 7: Triggers
Yony Richard Montoya Burgos
TriggersSintaxis
Unidad 7: Triggers
CREATE [OR REPLACE] TRIGGER nombre BEFORE|AFTER
INSERT OR UPDATE [of columna] OR DELETE ON tabla]
FOR EACH ROW
WHEN condicion
<Bloque PL/SQL>
122
Yony Richard Montoya Burgos
TriggersSe puede accecder a la información que se esta actualizando mediante los atributos:
:old.<campo>: Campo antes de la actualización:new.<campo>: El valor a modificar.
Tambien se pueden usar los identificadores deleting, updatig, inserting para las saber que operación se esta realizando.
Unidad 7: Triggers
Yony Richard Montoya Burgos
TriggersSe puede eliminar un trigger con la instrucción:
DROP nombreTambien se pueden activar o desactivar un trigger usando:
ALTER TRIGGER nombre DISABLE|ENABLEPara desactivar todos los triggers de u tabla usar:
ALTER TABLE nombre ENABE|DISABLE ALL TRIGGER
Unidad 7: Triggers
Yony Richard Montoya Burgos
EjerciciosSuponga que se tiene un campo adicional en la tabla DEPT (el campo BUDGET) que es usado para almacenar presupuesto disponible para cada departamento. Asuma que se tiene una restricción que requiere que el total de todos los salarios en un departamento no debe exceder el presupuesto del departamento. Crre un Trigger para validar esta información en las tablas que considere pertinentes
Unidad 7: Triggers