Transcription
Reproducción prohibidaVersión 1.1 Copyright Cédric Simon, 2008SolucionJava.comManual del alumnoIng. Cedric Simon – Tel: 2268 0974 – Cel: 8888 2387 – Email: c e d r i c @ s o l u c i o n j a v a . c o m – Web: www.solucionjava.comCurso deSQL avanzado yPL/SQL básico paraOracle 10g (10.2)
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)Pagina 2 / 44ÍndiceÍndice.21 Introducción al curso.41.1 Objetivo de este curso.41.2 Manual del alumno.41.3 Requisitos para atender a este curso.41.4 Soporte después del curso.41.5 Herramienta de desarrollo.42 DML - Sentencias de manipulación de datos.52.1 Objetivo del capítulo.52.2 Insert.52.3 Update.52.4 Delete.52.5 Commit y rollback.52.6 Savepoint.62.7 Select.62.8 Where.72.8.1 Operadores SQL .72.8.2 La ausencia de valor: NULL .82.9 Count.82.10 Sum, avg, min, max.92.11 Distinct.92.12 Order by.92.13 Uniones .92.14 Subconsultas.92.15 Agrupaciones.102.16 Rollup.102.17 Cube.102.18 Rendimiento.112.18.1 Indices.112.18.2 Diseño de la base de datos y denormalización.112.18.3 Uso del EXPLAIN PLAN.122.18.4 Uso de HINTS.123 Creación de objetos.143.1 Indices.143.1.1 Creación.143.1.2 Modificación.153.1.3 Eliminación.163.1.4 Ejercicios.163.2 Vistas.173.2.1 Creación.173.2.2 Modificación.17 Copyright Cédric Simon, 2008Versión 1.1Reproducción prohibida
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)Pagina 3 / 443.2.3 Eliminación.173.2.4 Ejercicios.173.3 Vistas materializadas.173.4 Secuencias.183.4.1 Creación.183.4.2 Modificación.183.4.3 Eliminación.183.4.4 Ejercicios.183.5 Sinónimos.183.5.1 Creación.183.5.2 Eliminación.193.5.3 Ejercicios.193.6 DB Link.194 PL/SQL básico.204.1 Introducción.204.2 Bloque anónimo PL/SQL.204.3 Identificadores.204.4 Variables.204.4.1 Tipos de variables.214.4.2 Variables locales.214.4.3 Variables globales.214.5 Control de flujo.214.5.1 If.elsif.else.214.5.2 Case.224.5.3 Goto.234.6 Bucles.234.6.1 LOOP.234.6.2 WHILE.244.6.3 FOR.244.7 Exit.244.8 NULL.244.9 EXECUTE IMMEDIATE.244.10 Cursores.254.11 Excepciones.264.11.1 Excepciones predefinidas .274.11.2 Excepciones definidas por el usuario .294.11.3 RAISE APPLICATION ERROR.304.12 Procedimientos, funciones, paquetes, disparadores.314.12.1 Funciones.314.12.2 Procedimientos.364.12.3 Paquetes.374.12.4 Disparadores.385 Ejercicios.436 Diseño de la base de datos.44 Copyright Cédric Simon, 2008Versión 1.1Reproducción prohibida
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)Pagina 4 / 441 Introducción al curso1.1 Objetivo de este cursoEste curso brindará al alumno el conocimiento necesario para hacer programación en PL/SQL en unabase de datos Oracle 10g.Primero vamos a dar una repasadita al lenguaje SQL y ver alguna sentencias avanzadas del SQL. Luegoveremos el PL/SQL.1.2 Manual del alumnoEste manual del alumno es una ayuda para el alumno, para tenga un recuerdo del curso. Este manualcontiene un resumen de las materias que se van a estudiar durante el curso, pero el alumno debería detomar notas personales para completas este manual.1.3 Requisitos para atender a este cursoSe requiere un conocimiento del lenguaje SQL.1.4 Soporte después del cursoSi tienes preguntas sobre la materia del curso en tus ejercicios prácticos, puedes escribir tuspreguntas a cedric@solucionjava.com .1.5 Herramienta de desarrolloExisten muchas herramientas de desarrollo para el PL/SQL. Lo mas básico es un editor de texto y SQLplus.Las herramientas mas populares son TOAD, SQL Navigator, PL/SQL Developper, SQL Developper.SQL Developper, desarrollado por Oracle es gratis, los demás necesitan comprar una licencia.Cualquier herramienta se puede usar en el curso. Lo ideal es usar la con la cual tiene mas experiencia (ylicencia), o usar SQL Developper que es gratis. Copyright Cédric Simon, 2008Versión 1.1Reproducción prohibida
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)Pagina 5 / 442 DML Sentencias de manipulaciónde datos2.1 Objetivo del capítuloAl fin de este capítulo el alumno será capaz de hacer encuestas de la base de datos. No vamos a ver todaslas opciones, ni las encuestas de otros objetos de la base de datos (vistas, funciones, secuencias,.) porqueeso sale del cuadro de este curso de iniciación.Existen muchas opciones (top, exists, cube,.) para cada tipo de acción, pero estas opciones dependen dela base de datos utilizadas y/o de su versión. Solo vamos a ver las sentencias básicas.Existen muchos entornos que simplifican las encuestas sobre los datos.2.2 InsertLa sentencia ‘Insert’ permite de insertar datos en una tabla.INSERT INTO nombre de tabla ( campo 1 , campo 2 , . ) VALUES( valor campo 1 , valor campo 2 , valor . );También existe:INSERT INTO nombre de tabla ( campo 1 , campo 2 , . ) SELECT STATEMENT ;2.3 UpdateLa sentencia ‘Update’ permite de modificar el valor de uno o varios datos en una tabla.UPDATE nombre de tabla SET campo 1 valor campo 1 , campo 2 valor campo 2 , . ;De costumbre se limita el cambio a ciertos registros, mencionados utilizando una cláusula WHERE.UPDATE nombre de tabla SET campo 1 valor campo 1 , campo 2 valor campo 2 , . WHERE cláusula where ;2.4 DeleteLa sentencia ‘Delete’ permite de borrar un uno o varios registros en una tabla.DELETE FROM nombre de tabla ;De costumbre se limita el borrado a ciertos registros, mencionados utilizando una cláusula WHERE.DELETE FROM nombre de tabla WHERE cláusula where ;2.5 Commit y rollbackSi la base de datos permite la gestión de transacciones, se puede utilizar ‘Commit’ para confirmar una‘Insert’, ‘Update’, o ‘Delete’, o Rollback para cancelarlos. Ciertas base de datos pueden ser configuradas Copyright Cédric Simon, 2008Versión 1.1Reproducción prohibida
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)Pagina 6 / 44para autocommit, que hace un commit automaticamente despues de cada instrucción, a menos que se hainiciado una transacción de manera explicita (con 'begin transaction xxx;).Hasta que el ‘Commit’ está ejecutado, las modificaciones no están inscritas de manera permanente en labase de datos, y sólo son visible para la sesión en curso del usuario autor de las acciones. Después del‘Commit’, los cambios son definitivos y visible para todos.Cuidado que ciertos objetos pueden quedar bloqueados (bloqueando otros usuarios) hasta que el commitsea hecho.El commit/rollback permite confirmar o de hacer un lote de transacción, para que si una falle, todas lasanteriores se anulan también. Cuando se necesita una integridad de transacción, se utiliza encommit/rollback.Ejemplo:SELECT emp no,job grade FROM employeeupdate employee set job grade 6 whereSELECT emp no,job grade FROM employeerollback;SELECT emp no,job grade FROM employeeupdate employee set job grade 6 whereSELECT emp no,job grade FROM employeecommit;SELECT emp no,job grade FROM employeewhere emp no 4;emp no 4;where emp no 4;where emp no 4;emp no 4;where emp no 4;where emp no 4;2.6 SavepointUn savepoint permite identificar un punto en una transacción al cual se podrá eventualmente regresar(rollback).SELECT emp no,job grade FROM employeeSTART TRANSACTION;update employee set job grade 5 whereSELECT emp no,job grade FROM employeesavepoint vale cinco;SELECT emp no,job grade FROM employeeupdate employee set job grade 4 whereSELECT emp no,job grade FROM employeerollback to savepoint vale cinco;SELECT emp no,job grade FROM employeerollback;where emp no 4;emp no 45;where emp no 4;where emp no 4;emp no 45;where emp no 4;where emp no 4;2.7 SelectEl ‘Select’ permite de seleccionar datos en la base de datos, y visualizarlos.Se puede utilizar un alias para que el campo se pueda llamar con otro nombre.SELECT campo 1 , campo 2 , . FROM nombre tabla ;SELECT campo 1 as alias1 , campo 2 , . FROM nombre tabla ;Para seleccionar todos los campos de la tabla, se utiliza el asterisco en vez de los nombres de campo.SELECT * FROM nombre tabla ;Ejemplo:SELECT emp no,job grade as nivel FROM employee;SELECT * FROM employee; Copyright Cédric Simon, 2008Versión 1.1Reproducción prohibida
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)Pagina 7 / 442.8 WhereLa cláusula ‘Where’ permite de limitar la encuesta a ciertos datos.Se utiliza evaluando un campo versus una condición. Se pueden utilizar varias condiciones, con el uso de‘Or’, ‘And’, y/o paréntesis.Para compara números, se utiliza el signo ' ', o ' ', o' ', o' ', o ' ', o 'between . and .'.Para comparar caracteres se utiliza la palabra 'like'. El wildcard es '%'.Para compara fecha, se utiliza el signo ' ', o ' ', o' ', o' ', o ' ', o 'between . and .'.ParaSELECT * FROM nombre tabla WHERE campo 1 operation condición AND campo 2 operation condición ;Ejemplo:SELECT emp no,job grade FROMSELECT emp no,job grade FROMSELECT * FROM employee whereSELECT * FROM employee whereemployee whereemployee whereemp no betweenlast name likeemp no 45;emp no 46 or1 and 2;'P%';emp no 61;Operadores SQL2.8.1Ya hemos visto anteriormente qué tipos de datos se pueden utilizar en Oracle. Y siempre que haya datos,habrá operaciones entre ellos, así que ahora se describirán qué operaciones y con qué operadores serealizan:Los operadores se pueden dividir en TRES conjuntos: Aritméticos: utilizan valores numéricos Lógicos (o booleanos o de comparación): utilizan valores booleanos o lógicos. Concatenación: para unir cadenas de caracteres.Operadores arítméticos Retornan un valor numérico:Símbo loSignificadoEjemplo Operación suma1 2 Operación resta1 2*Operación multiplicación1*2/Operador división1/2Operadores lógicos Retornan un valor lógico (verdadero o falso)SímboloSignificadoEjemplo Igualdad1 2! Desigualdad1 ! 2 1 2 1 2 Mayor que1 2 Menor que1 2 Mayor o igual que1 2 Menor o igual que1 2IN (RS)Igual a algún elemento del arreglo de resultados.1 IN (1,2) Copyright Cédric Simon, 2008Versión 1.1Reproducción prohibida
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)Pagina 8 / 44[TRUE]ANY SOMEa algún elemento del arreglo de resultados (derecha). Debe ser10 ANY (1,2,3,10)estar precedido por , ! , , , , Hace un OR lógico entre[TRUE]todos los elementos.ALLa todos los elementos del arreglo de resultados (derecha), Debe10 ALL (1,2,3,10)ser estar precedido por , ! , , , , Hace un AND lógico[TRUE]entre todos los elementos.BEETWEEN x Operando de la izquierda entre x e y. Equivalente a op xAND yAND op y10 BETWEEN 1 AND100EXISTSSi la retorna al menos una filaEXISTS( SELECT 1FROM DUAL)LIKE(*)Es como'pepe' LIKE'pe%'IS NULLSi es nulo1 IS NULLIS NOT NULL Si es No nulo1 IS NOT NULLNOT cond.NOT EXISTS NOTBETWEEN NOT INNOT Niega la condición posterioscond AND cond Hace un Y lógico entre dos condiciones1 1 AND 2 IS NULLCond OR cond Hace un O lógico entre dos condiciones1 1 OR 2 IS NULLExisten los siguientes comodines: %: Conjunto de N caracteres (de 0 a ) : Un solo carácterConcatenaciónOracle puede hacer una conversión automática cuando se utilice este operador con valores numéricos: 10 20 '1020'Este proceso de denomina CASTING y se puede aplicar en todos aquellos casos en que se utiliza valoresnuméricos en puesto de valores alfanuméricos o incluso viceversa.2.8.2La ausencia de valor: NULLTodo valor (sea del tipo que sea) puede contener el valor NULL que no es más que la ausencia de valor.Así que cualquier columna (NUMBER, VARCHAR2, DATE ) puede contener el valor NULL, con lo quese dice que la columna está a NULL. Una operación retorna NULL si cualquiera de los operandos esNULL. Para comprobar si una valor es NULL se utiliza el operador IS NULL o IS NOT NULL.2.9 CountPara contar un numero de registros, se utiliza la palabra ‘Count’. Copyright Cédric Simon, 2008Versión 1.1Reproducción prohibida
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)Pagina 9 / 44SELECT COUNT( campo 1 ) FROM nombre tabla ;Ejemplo:SELECT count(*) FROM employee where job grade 4;2.10Sum, avg, min, maxPara una suma, min, max,. de un campo, se utilizan la palabras ‘Sum’, ‘Min’, ‘Max’, ‘Avg’.SELECT SUM( campo 1 ) FROM nombre tabla ;Ejemplo:SELECT avg(salary) FROM employee where job grade 2;2.11DistinctPara tener la lista de valores distingas de un campo, se utiliza la palabra ‘Distinct’.SELECT DISTINCT( campo 1 ) FROM nombre tabla ;Ejemplo:SELECT distinct(job grade) FROM employee;2.12Order byPara ordenar los registros regresados, hay que utilizar la palabre ‘Order by’.SELECT * FROM nombre tabla ORDER BY campo 1 , . ;Ejemplo:SELECT first name,last name FROM employee order by first name,last name;2.13UnionesUniones permiten de unir los resultados de dos consultas. Para poder unirlas, tienen que tener losmismos campos.SELECT campo 1 , campo 2 , . FROM nombre tabla 1 UNIONSELECT campo 1 , campo 2 , . FROM nombre tabla 2 ;Ejemplo:select t.first name,t.last name from employee t where job grade 5unionselect t2.fname,t2.lname from patient t2;2.14SubconsultasSubconsultas son consultas sobre otras consultas. La subconsulta se puede utilizar el la cáusula ‘From’, oel la condición de la clásula ‘Where’. La subconsulta se pone entre paréntesis. En MySQL, lassubconsultas deben tener sus propios alias.SELECT t3. campo 1 , t3. campo 2 FROM (SELECT t. campo 1 , t. campo 2 where cluase ) t3WHERE t3. campo 1 IN (SELECT t2. campo 1 FROM nombre tabla 2 t2);FROM nombre tabla tEjemplo: SELECT t3.first name,t3.last name FROM( Copyright Cédric Simon, 2008Versión 1.1Reproducción prohibida
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)Pagina 10 / 44select t.first name,t.last name from employee t where job grade 5unionselect t2.fname,t2.lname from patient t2) t3 where t3.last name like 'RAMIREZ%';SELECT t3.first name,t3.last name, t3.job country FROM employee t3where t3.job country IN(select t.country from country t where t.currency 'Euro');2.15AgrupacionesLas agrupaciones permiten agrupar datos y saber cuantos datos hay de cada valor.SELECT campo 1 , campo 2 , COUNT(*) FROM nombre tabla GROUP BY campo 1 , campo 2 ;Las agrupaciones se pueden filtrar utilizando la clausula HAVING.Ejemplo:SELECT job grade, count(*) FROM employeewhere emp no 45group by job grade;SELECT job grade, sum(salary) FROM employeewhere emp no 45group by job gradehaving sum(salary) 1000000;2.16RollupRollup de usa en un group by para agregar el total del sub grupo.Ejemplo:selectdept no,job code,count(*),sum(salary)FROMemployeeGROUP BYrollup(dept no,job code);selectdept no,job code,count(*),sum(salary)FROMemployeeGROUP BY dept no,rollup(job code);2.17CubeCube es parecido al rollup pero da los totales de todos los grupos posibles.Ejemplo:selectdept no,job code,count(*),sum(salary)FROMemployeeGROUP BYCUBE(dept no,job code); Copyright Cédric Simon, 2008Versión 1.1Reproducción prohibida
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)Pagina 11 / 442.18RendimientoUn problema común en las encuesta a base de datos es el rendimiento.Las causas de problema de rendimiento son numerosas.Las más comunes son: Instrucción sin o con mala clausula WHERE Falta de indice sobre un campo utilizado como filtro Mal diseño de la base de datos Problema de hardware (falta de memoria, disco ocupado, cpu ocupado por otra aplicación,.) Mala configuración del servidor (mal uso de la memoria, disco, cpu,.) Mala programación en el cliente. Falta de commit, conexión no cerrada, . Red sobrecargada o muy lentaCuando se enfrenta a un problema de rendimiento hay que probar primero de identificar la causa y lossíntomas. Servidor sobrecargado en CPU, disco, memoria? Un cliente afectado o todos? Cuando apareceel problema?Para ayudar a investigar estos problemas existen herramientas. Algunos vienen con la base de datos,otros están desarrollados aparte.2.18.1IndicesLos indices permiten mejorar el rendimiento de las consultas, y entonces del servidor.Un indice es un pequeño archivo que contiene los valores de uno o varios campos de manera ordenada,junto con la información de la ubicación física del registro.Cuando la consulta usa el indice, primero lee el archivo del indice, y luego los datos en la tabla. Si todoslos campos pedidos están en el indice, solo lee el indice.Los indices se usan cuando se busca un valor exacto ( o like '.'), un rango de valores (between and , , ,.) , o los valores que 'inician con' (like '.%').Si se usan funciones (upper(campo), ) no se usara el indice (a menos que existe un indice basado enfunción).Los indices tan poco se usan con clausula is null, is not null, in ( ), not , ! , like '%.',.2.18.2Diseño de la base de datos y denormalizaciónSi por lo general es mejor tener una base de datos normalizada (sin información repetida), a veces hayque denormalizar por razón de rendimiento, es decir copiar un valor en una tabla que no le pertenecedirectamente. Denormalizar tiene un costo a nivel de programación (mantener la integridad de losdatos), pero permite mejorar (en ciertos casos) el rendimiento. Copyright Cédric Simon, 2008Versión 1.1Reproducción prohibida
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2)2.18.3Pagina 12 / 44Uso del EXPLAIN PLANOrac
Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10.2) Pagina 6 / 44 para autocommit, que hace un commit automaticamente despues de cada instrucción, a menos que se ha iniciado una trans
