psql

1

Yony Richard Montoya Burgos 1

ORACLE

ORACLE SQL - PLSQLLic. Yony Richard Montoya Burgos

[email protected]

Yony Richard Montoya Burgos

TEMARIO

Introducción a ORACLEAccediendo a la InformaciónLenguaje ProceduralEstructura de la BDIntegridad de Datos


Conceptos BásicosDBMS: Sistema Manejador de Bases de DatosOracle esta basado en arquitectura Multi-Servidor

Unidad 1: Introducción a Oracle

2


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.



Diseño de DatosMapeo del diagrama entidad relación.Se consideran campos resultado de las relaciones entre entidades.



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.


3


Notacion en OracleEntidad:

Relación:

Opcional RequeridoUno o mas


NOMBRE

# <campo llave>* <campo requerido>º <campo opcional>

NOMBRE


NOMBRE 1


RELACION


Notación en OracleEntidades:

Nombre en MAYUSCULAAtributos en MINUSCULA

RelacionesNombres en MINUSCULAManeja DEBE SER (requerido) y PUEDE SER (opcional)



EjerciciosCrear un ERD de: (ver doc adjuntos)


4


Procesos y Almacenamiento



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.



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


5


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.



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.



Arquitectura de la BD


6


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.


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)



PL/SQLExtensión de SQL, con características de lenguaje de programaciónLenguajes de manipulación y consulta son incluidas


7



Interaccion SQL y SQL* PlusBuffer

SQL*Plus

Servidor

Comandos

SQL*PlusReporte Formateado

Resultados

Sentencias SQLSentencias SQL


SQL*PLUSEntorno de consultaPropietario de OracleSólo maneja una línea a la vez.


sqlplus [usuario[clave[@db]]]


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


8


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



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



Sentencias SQLSelección

SELECT [DISTINCT] {* | columna [alias],...}FROM tabla

Selecciona un conjunto de columnas (SELECT) de una tabla (FROM).


9


SeleccionSELECT * FROM empleado;

Muestra todas las columnas de una tabla.SELECT nombre,apellido FROM empleado;

Selecciona sólo las columnas indicadas



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



SeleccionSELECT nombre,salario, 0.13*(salario+10) FROM empleado;

Se pueden usar parentesis para manejar la precedencia de operadores.


10



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;



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.


Seleccion

Se puede o no usar [AS] , es opcional


SQL> SELECT ename AS name, sal salary2 FROM emp;

SQL> SELECT ename “Name”,2 sal*12 “Annual Salary”3 FROM emp;

11


Seleccion

Concatena columnas de una tabla.

Caracteres especiales se colocan entre comillas simples


SQL> SELECT ename || job AS “Empleados”2 FROM emp;

SQL> SELECT ename ||‘ es un ’|| job 2 AS “Detalle Empleados”3 FROM emp;


Seleccion

DISTINCT permite eliminar las filas repetidas, si se aplica a mas de una columna se toma la combinación.


SQL> SELECT DISTINCT deptno2 FROM emp;


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>


12


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;



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.



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?


13


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



Operadores de ComparaciónSELECT nombre,salario,nss_supervFROM empleadoWHERE nss_superv IN (‘888665555’,’ 333445555’);

Compara todos los valores con los indicados en la lista



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


14


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)



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.



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;


15


OrdenamientoSintaxis GeneralSELECT expresiónFROM tabla[WHERE condición][ORDER BY {columna, expresión} [ASC|DESC]];



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.



Funciones SQLSe tienen funciones

Simples (afectan a una columna)

Múltiples (afectan a varias columnas)


16


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, ...])



Funciones SQLFunciones Simples

GeneralesConversiónNuméricasFechaCaracter



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


17



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.




También existenRPAD, REPLACE, ASCII, LTRIM, RTRIM, CHR



Funciones SQLSELECT nombre,salarioFROM empleadoWHERE LOWER(nombre)=‘alicia’;----------------------------------------------SELECT UPPER(nombred) FROM departamento;


18


Funciones SQLSELECT nombre,salarioFROM empleadoWHERE LENGHT(nombre)=5;----------------------------------------------SELECT nombre,

CONCAT(INITCAP(‘ vive en ’),direccion) FROM empleadoWHERE INSTR(direccion,’Houston’)>0;



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.



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.


19


Funciones SQLSELECT ROUND(25.293,1) FROM DUAL;

25.3----------------------------------------------SELECT ROUND(25.293,-1) FROM DUAL;

30



Funciones SQLSELECT TRUNC(36.234,0) FROM DUAL;

36----------------------------------------------SELECT TRUNC(36.234,-1) FROM DUAL;

30



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


20


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;



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



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;


21


Funciones SQLFunciones de Conversión

Conversión de Tipos de DatosImplicitaExplicita



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


VARCHAR2VARCHAR2DATENUMBER

A

DATENUMBERVARCHAR2 | CHARVARCHAR2 | CHAR

DE


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.


22


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



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 “ ”



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;


23


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




TO_CHAR(salario,’$99,999’) Salario FROM empleado;------------------------SELECT nombre,

TO_CHAR(salario,’L99999’) Salario FROM empleado;




TO_NUMBER(‘2345’,’99,999’)FROM empleado;

Utiliza el mismo formato de número que para la conversión de caracteres.


24


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.



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.



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;


25


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;-------------------



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



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”


26


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.



Multiples TablasProducto Cartesiano

La condición del Join es omitida

SELECT nombre,nombred FROM empleado,departamento;



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;


27


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;



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;



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(+)


28


Multiples TablasOUTER JOIN

SELECT e.nombre, d.numerod, d.nombredFROM empleado e, departamento dWHERE e.nd(+)=d.numerodORDER BY e.nd;



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;



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.


29


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”



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



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).


30


Funciones de Agregación y de GrupoSELECT AVG(salario), MAX(salario),

MIN(salario), SUM(salario)FROM empleadoWHERE sexo=‘H’;



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.



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


31


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]



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;



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);


32


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



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];



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;


33


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.



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).



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)


34


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’.



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.



SubconsultasSELECT nombre,salarioFROM empleadoWHERE salario = (SELECT MIN(salario)

FROM empleado);Se pueden usar funciones de grupo en las subconsultas.


35


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



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.



SubconsultasSubconsultas de mas de una fila.Usan operadores de comparación de multiples-filas


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


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



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



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


37


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.



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);



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;


38


SubconsultasSe puede utilizar una subconsulta como parte del FROM


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


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’



Reportes Interactivos


Usuario

. . .sal = ? . . .. . .deptno = ?. . .. . .ename = ?. .

Quiero introducir valoresde la consulta en tiempo

de ejecución

39


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.



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).


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


Variables de SubstituciónEl comando SET VERIFY ON fuerza a mostrar el comando antes y después del reemplazo.


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


Variables de SubstituciónUsar comillas simples para campos de tipo carácter o fecha.


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


Variables de SubstituciónUsar variables de substitución para ingresar lo siguiente:

Condiciones WHERE.Claúsulas ORDER BYExpresiones de ColumnaNombres de TablaSentencias SQL completas



Variables de Substitución


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


Variables de SubstituciónUsar && si se quiere reusar la variable.


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.


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



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


42


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]



Variables de SubstituciónTipo: puede ser NUMBER,CHAR,DATEFormato: Especifica el formatoPROMPT: Muestra el texto al usuario.HIDE: Permite ocultar la entrada del usuario.


ACCEPT dept PROMPT ‘Introduzca el departamento: ‘SELECT *FROM deptWHERE dname = UPPER (‘&dept’)/

Introduzca el departamento: SalesDEPTNO DNAME LOC-------------- ------------------- -----------------30 SALES CHICAGO


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


43


Variables de SubstituciónCrear variables en la sesión SQL*Plus

Se puede usar la variable con el prefijo &


SQL> DEFINE deptname = salesSQL> DEFINE deptname

DEFINE DEPTNAME = “sales” (CHAR)

SQL> SELECT *2 FROM dept3 WHERE dname = UPPER ( ‘&deptname’ );


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



•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


Variables de Comando SET

44


•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


Comandos de Formato SQL*PlusSe puede controlar las características del reporte usando:


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]]



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’


45


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


Modelos de Formato de COLUMN


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



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”


BTI[TLE] [text l OFF l ON]

46


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



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



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.


47


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. . .]);



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.



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.


48


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.



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



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.


49


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.



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];



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.


50


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.



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.



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


Integridad de DatosSe controla la integridad de la información si se tuvieran llaves foraneas.

51


Eliminando InformaciónSe puede eliminar filas de una tabla usando la sentencia DELETE.

DELETE [FROM] table[WHERE condition];



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.



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.


52


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.


Integridad de DatosSe considera la integridad de la información si hubiesen llaves foráneas.


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



Ventajas del COMMIT y ROLLBACKAsegura la consistencia de los datos.Muestra los datos antes de hacer los cambios permanentesAgrupa lógicamente operaciones relacionadas


53


INSERT UPDATE INSERT DELETE

Transacción

COMMIT Savepoint A Savepoint B

ROLLBACK a Savepoint B

ROLLBACK a Savepoint A

ROLLBACK


Controlando las Transacciones


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.



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


54


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.



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.



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.


55


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.



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



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


Implementación de consistencia de lectura

56


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



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


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.


57


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] [, . . .]);



Referencia a tablas de otros UsuariosSe debe usar nombre del propietario como prefijo de la tablaSe debe tener derechos para crear otras tablas.



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,


58


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)


Creando TablasCrear la tabla


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



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;


59


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



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


Tipos de Datos


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;


60


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


Subconsultas


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]. . .);



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.


61


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.



SQL> ALTER TABLE dept302 DROP COLUMN job ;

Table altered.


Borrando una ColumnaSe pueden borrar columans que ya no son utilizadasSólo se pueden borrar columnas que no son llave


ALTER TABLE tableSET UNUSED (column);

OALTER TABLE tableSET UNUSED COLUMN column;

ALTER TABLE tableDROP UNUSED COLUMNS;


Marcar columna como no usadaSe puede marcar una o mas columnas como NO USADASe pueden borrar todas las columnas marcadas como no usadas

62


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.



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.



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.


63


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.



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.



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


64


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.



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) );


RestriccionesDefinición de Constraint.


Definiendo RestriccionesRestricciones a nivel de Columna

Restricciones a nivel de Tabla

column [CONSTRAINT constraint_name] constraint_type,

column,…[CONSTRAINT constraint_name] constraint_type(column, …),


65


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



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);


Restricción NOT NULL


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


66


SQL> CREATE TABLE dept (2 deptno NUMBER(2),3 dname VARCHAR2 (14),4 loc VARCHAR2 (14),5 CONSTRAINT dept_dname_uk UNIQUE (dname));


Restricción UNIQUESe define a nivel de Tabla o de columna.

SQL> CREATE TABLE ……….3 dname VARCHAR2 (14) CONSTRAINT dept_dname_ukUNIQUE,………);


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



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));


67


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


Restricción FOREIGN KEYSe define a nivel de Tabla o de columna.


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) );



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


68


…, deptno NUMBER (2),CONSTRAINT emp_deptno_ck

CHECK (DEPTNO BETWEEN 10 AND 99),...


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


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);




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


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.



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.



SQL> ALTER TABLE emp2 ENABLE CONSTRAINT emp_empno_pk;

Table altered.


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



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.


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 . . .


Consultando RestriccionesPara consultar las restricciones ver la tabla USER_CONSTRAINTS.


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


71


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


VistasRepresentación lógica de una tabla.


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.



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


Vistas Simples y Complejas

72


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];



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



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.


73


SQL*PlusSELECT *FROM emp10;

7839 KING PRESIDENT7782 CLARK MANAGER7934 MILLER CLERK EMP

USER_VIEWSEMP10

SELECT empno, ename, jobFROM empWHERE deptno-10;


Consultando VistasUtilizar la tabal USER_VIEWS para ver la información de las vistas


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.



Creación de Vistas ComplejasSe pueden usar funciones de grupo, joins y fucniones especiales para crear vistas


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


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



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.



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.


75


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.



Eliminando VistasSe puede borrar las vistas con la instrucción

DROP VIEW

La información de las tablas base no se pierden.


DROP VIEW vista;

SQL> DROP VIEW emp10;View dropped.


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?


76



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:



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


SecuenciasAutomaticamente generan números únicosEs un objeto compartido.Generalmente se usa para generar llaves primarias.Reemplaza aplicación de código.


77


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}];



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)



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.


78


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;




Pseudocolumnas NEXTVAL y CURRVAL

NEXTVAL devuelve el siguiente valor de la secuencia.

CURRVAL obtiene el valor actual de la secuencia.


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;


79



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


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.



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.


80


SQL> DROP SEQUENCE deptno_deptno;Sequence dropped.


Eliminando SecuenciasSe puede eliminar las secuencias usando la instrucción DROP SEQUENCE.


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.



Creando IndicesSe pueden crear índices :

Automaticamente cuando se definen restricciones PRIMARY KEY o UNIQUEManualmente mediante la instrucción CREATE INDEX.


81


CREATE INDEX indexON table (column[, column]…);

SQL> CREATE INDEX emp_ename_idx2 ON emp(ename);

Index created.


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



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.



Cuando no crear IndicesCuando la tabla es pequeñaLas columnas no son a menudo usadas en condicionesLa tabla es actualizada frecuentemente

82


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’;



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;



SQL> DROP INDEX index;

SQL> DROP INDEX emp_ename_idx;Index dropped.


Eliminando IndicesEliminar índices con la instrucción DROP INDEX.

83


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;



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.



Manejo de UsuariosAdministrador de la Base de Datos

Nombre de Usuario y Contraseña

Usuarios

Unidad 4: Control de Usuarios

84


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.



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



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.


85


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. . .];



Privilegios de UsuariosSe pueden asignar privilegios específicos por usuario


SQL> GRANT create table, create sequence, create view2 TO scott;

Grant succeeded.


Usuarios

Admin

Privilegios

Asignando Privilegiossin un Rol

Asignando privilegios con un Rol


Roles de Usuarios

86


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.



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>;



Privilegios de Objeto Table View Sequence ProcedureALTER X XDELETE X XEXECUTE XINDEX XINSERT X XREFERENCES XSELECT X X XUPDATE X X


Privilegios de Objeto

87


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];



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.



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.


88


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


Confirmando Privilegios


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];




SQL> REVOKE select, insert2 ON dept3 FROM CURSO_37;

Revoke succeeded.

Ejemplo

89


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


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


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


Beneficios de PL/SQLDesarrollo modularizado de programas

Unidad 5: PL/SQL

DECLARE

BEGIN

EXCEPTION

END;


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


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


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;


Uso de Variables en PL/SQLUsar Variables para:

Almacenamiento Temporalmente de datos.Manipulación de valores almacenadosReusabilidadFácil de mantener

Unidad 5: PL/SQL


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


Tipos de VariablesSe pueden tener:

Escalares

Compuestas

De referencia

LOB

Unidad 5: PL/SQL


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;


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


Tipos de Datos EscalaresSe pueden usar:

VARCHAR2NUMBERDATECHARLONGLONG_RAWBOOLEANBINARY_INTEGERPLS_INTEGER

Unidad 5: PL/SQL


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;

...


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


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


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”;


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


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;

/


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


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


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;


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;


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


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);


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;


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


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;


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


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


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;/


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;


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


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


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


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


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


Sentencia IFSintaxis

Unidad 5: PL/SQL

IF condicion THEN

sentencias;

[ELSIF condicion THEN

sentencias; ]

[ELSE

sentencias;]

END IF;


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


Sentencia IF-THEN-ELSESe puede usar la sentencia IF-THEN-ELSE.Todas las sentencias IF se pueden anidar.

Unidad 5: PL/SQL


Sentencia IF-THEN-ELSIFManeja flujos de información agrupados

Unidad 5: PL/SQL

Acciones THEN

Acciones THEN

Condicion IF

Condicion ELSE

Acciones

ELSE


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


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


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;

/


Ciclo FORSintaxis

Se incrementa contador de uno en uno

Unidad 5: PL/SQL

FOR contador IN [REVERSE] minimo..maximo

LOOP

sentencias;

. . .

END LOOP;

104


Ejemplo

Unidad 5: PL/SQL

DECLARE

v_orderid item.orderid%TYPE := 601;

BEGIN

FOR i IN 1..10 LOOP


VALUES(v_ordid,i);

END LOOP;

END;

/


Ciclo WHILESintaxis

Permite repetir sentenias de acuerdo a una condición

Unidad 5: PL/SQL

WHILE condicion LOOP

sentencias;

. . .

END LOOP;


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


VALUES(&p_nueva_orden,v_contador);

v_contador:=v_contador+1;

END LOOP;

COMMIT;

END;

/

105


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


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;


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


Tipos de Datos CompuestosSe pueden usar tipos:

Registros PL/SQLTablas PL/SQL

Pueden contener componentes internos.Son reusables.

Unidad 5: PL/SQL


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]


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


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’;


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;


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


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


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;


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


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


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


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;

/


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


dept_table dept_table_type;

110


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


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


CursoresCada sentencia ejecutada por Oracle tiene su cursor que puede ser:

Cursor ImplicitoCursor Explicito

Unidad 5: PL/SQL

111


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


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;


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


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;


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;


Recuperando información de CursoresEjemplo

Unidad 5: PL/SQL

....

OPEN cursor;

LOOP

FETCH cursor INTO variables

EXIT WHEN ....;

.....

-- Procesar información

END LOOP;

113


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;


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 ...


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


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...............


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;


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


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;


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


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


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’);


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


Tipos de ExcepcionesPredefinidas por el Servidor Oracle, lanzadas implicitamenteNo-Predefinidas por el Servidor Oracle.Definidas por el Usuario

Unidad 5: PL/SQL

117


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]


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


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


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);


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;


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


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;


Funciones para manipular ExcepcionesSQLCODE

Devuelve el valor numérico del errorSQLERRM

Devuelve el mensaje asociado con el error.

Unidad 5: PL/SQL


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


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


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


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


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’);


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


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


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


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


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

psql

Documents

Transcript of psql