Curso de SQL avanzado y PL/SQL básico para Oracle 10g (10 ...

[Pages:102]

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

Curso de SQL avanzado y PL/SQL b?sico para Oracle 10g (10.2)

Manual del alumno

? Copyright C?dric Simon, 2008

Versi?n 1.1

Reproducci?n prohibida

Curso de SQL avanzado y PL/SQL b?sico para Oracle 10g (10.2)

Pagina 2 / 44

?ndice

?ndice....................................................................................................................................................................................2

1 Introducci?n al curso.........................................................................................................................................................4

1.1 Objetivo de este curso................................................................................................................................................4

1.2 Manual del alumno.....................................................................................................................................................4

1.3 Requisitos para atender a este curso........................................................................................................................4

1.4 Soporte despu?s del curso..........................................................................................................................................4

1.5 Herramienta de desarrollo........................................................................................................................................4

2 DML - Sentencias de manipulaci?n de datos...................................................................................................................5

2.1 Objetivo del cap?tulo..................................................................................................................................................5

2.2 Insert............................................................................................................................................................................5

2.3 Update.........................................................................................................................................................................5

2.4 Delete...........................................................................................................................................................................5

2.5 Commit y rollback......................................................................................................................................................5

2.6 Savepoint.....................................................................................................................................................................6

2.7 Select............................................................................................................................................................................6

2.8 Where..........................................................................................................................................................................7 2.8.1 Operadores SQL ...................................................................................................................................................7 2.8.2 La ausencia de valor: NULL ................................................................................................................................8

2.9 Count...........................................................................................................................................................................8

2.10 Sum, avg, min, max..................................................................................................................................................9

2.11 Distinct.......................................................................................................................................................................9

2.12 Order by....................................................................................................................................................................9

2.13 Uniones .....................................................................................................................................................................9

2.14 Subconsultas.............................................................................................................................................................9

2.15 Agrupaciones..........................................................................................................................................................10

2.16 Rollup......................................................................................................................................................................10

2.17 Cube.........................................................................................................................................................................10

2.18 Rendimiento............................................................................................................................................................11 2.18.1 Indices...............................................................................................................................................................11 2.18.2 Dise?o de la base de datos y denormalizaci?n..................................................................................................11 2.18.3 Uso del EXPLAIN PLAN.................................................................................................................................12 2.18.4 Uso de HINTS...................................................................................................................................................12

3 Creaci?n de objetos..........................................................................................................................................................14

3.1 Indices........................................................................................................................................................................14 3.1.1 Creaci?n..............................................................................................................................................................14 3.1.2 Modificaci?n.......................................................................................................................................................15 3.1.3 Eliminaci?n.........................................................................................................................................................16 3.1.4 Ejercicios.............................................................................................................................................................16

3.2 Vistas.........................................................................................................................................................................17 3.2.1 Creaci?n..............................................................................................................................................................17 3.2.2 Modificaci?n.......................................................................................................................................................17

? Copyright C?dric Simon, 2008

Versi?n 1.1

Reproducci?n prohibida

Curso de SQL avanzado y PL/SQL b?sico para Oracle 10g (10.2)

Pagina 3 / 44

3.2.3 Eliminaci?n.........................................................................................................................................................17 3.2.4 Ejercicios.............................................................................................................................................................17

3.3 Vistas materializadas...............................................................................................................................................17

3.4 Secuencias.................................................................................................................................................................18 3.4.1 Creaci?n..............................................................................................................................................................18 3.4.2 Modificaci?n.......................................................................................................................................................18 3.4.3 Eliminaci?n.........................................................................................................................................................18 3.4.4 Ejercicios.............................................................................................................................................................18

3.5 Sin?nimos..................................................................................................................................................................18 3.5.1 Creaci?n..............................................................................................................................................................18 3.5.2 Eliminaci?n.........................................................................................................................................................19 3.5.3 Ejercicios.............................................................................................................................................................19

3.6 DB Link.....................................................................................................................................................................19

4 PL/SQL b?sico.................................................................................................................................................................20

4.1 Introducci?n..............................................................................................................................................................20

4.2 Bloque an?nimo PL/SQL.........................................................................................................................................20

4.3 Identificadores..........................................................................................................................................................20

4.4 Variables...................................................................................................................................................................20 4.4.1 Tipos de variables................................................................................................................................................21 4.4.2 Variables locales.................................................................................................................................................21 4.4.3 Variables globales...............................................................................................................................................21

4.5 Control de flujo.........................................................................................................................................................21

4.5.1 If..elsif...else............................................................................................................................................................21 4.5.2 Case.....................................................................................................................................................................22 4.5.3 Goto.....................................................................................................................................................................23

4.6 Bucles.........................................................................................................................................................................23 4.6.1 LOOP..................................................................................................................................................................23 4.6.2 WHILE................................................................................................................................................................24 4.6.3 FOR.....................................................................................................................................................................24

4.7 Exit.............................................................................................................................................................................24

4.8 NULL.........................................................................................................................................................................24

4.9 EXECUTE IMMEDIATE.......................................................................................................................................24

4.10 Cursores..................................................................................................................................................................25

4.11 Excepciones.............................................................................................................................................................26 4.11.1 Excepciones predefinidas .................................................................................................................................27 4.11.2 Excepciones definidas por el usuario ...............................................................................................................29 4.11.3 RAISE_APPLICATION_ERROR....................................................................................................................30

4.12 Procedimientos, funciones, paquetes, disparadores............................................................................................31 4.12.1 Funciones..........................................................................................................................................................31 4.12.2 Procedimientos..................................................................................................................................................36 4.12.3 Paquetes.............................................................................................................................................................37 4.12.4 Disparadores......................................................................................................................................................38

5 Ejercicios..........................................................................................................................................................................43

6 Dise?o de la base de datos...............................................................................................................................................44

? Copyright C?dric Simon, 2008

Versi?n 1.1

Reproducci?n prohibida

Curso de SQL avanzado y PL/SQL b?sico para Oracle 10g (10.2)

Pagina 4 / 44

1 Introducci?n al curso

1.1 Objetivo de este curso

Este curso brindar? al alumno el conocimiento necesario para hacer programaci?n en PL/SQL en una base de datos Oracle 10g.

Primero vamos a dar una repasadita al lenguaje SQL y ver alguna sentencias avanzadas del SQL. Luego veremos el PL/SQL.

1.2 Manual del alumno

Este manual del alumno es una ayuda para el alumno, para tenga un recuerdo del curso. Este manual contiene un resumen de las materias que se van a estudiar durante el curso, pero el alumno deber?a de tomar notas personales para completas este manual.

1.3 Requisitos para atender a este curso

Se requiere un conocimiento del lenguaje SQL.

1.4 Soporte despu?s del curso

Si tienes preguntas sobre la materia del curso en tus ejercicios pr?cticos, puedes escribir tus preguntas a cedric@ .

1.5 Herramienta de desarrollo

Existen muchas herramientas de desarrollo para el PL/SQL. Lo mas b?sico es un editor de texto y SQL plus.

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 (y licencia), o usar SQL Developper que es gratis.

? Copyright C?dric Simon, 2008

Versi?n 1.1

Reproducci?n prohibida

Curso de SQL avanzado y PL/SQL b?sico para Oracle 10g (10.2)

Pagina 5 / 44

2 DML Sentencias de manipulaci?n de datos

2.1 Objetivo del cap?tulo

Al fin de este cap?tulo el alumno ser? capaz de hacer encuestas de la base de datos. No vamos a ver todas las opciones, ni las encuestas de otros objetos de la base de datos (vistas, funciones, secuencias,...) porque eso 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 de la 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 Insert

La sentencia `Insert' permite de insertar datos en una tabla.

INSERT INTO (,,) VALUES (,,);

Tambi?n existe:

INSERT INTO (,,) ;

2.3 Update

La sentencia `Update' permite de modificar el valor de uno o varios datos en una tabla.

UPDATE SET =,=,;

De costumbre se limita el cambio a ciertos registros, mencionados utilizando una cl?usula WHERE.

UPDATE SET =,=, WHERE ;

2.4 Delete

La sentencia `Delete' permite de borrar un uno o varios registros en una tabla.

DELETE FROM ;

De costumbre se limita el borrado a ciertos registros, mencionados utilizando una cl?usula WHERE.

DELETE FROM WHERE ;

2.5 Commit y rollback

Si 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, 2008

Versi?n 1.1

Reproducci?n prohibida

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 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 la base 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 commit sea hecho.

El commit/rollback permite confirmar o de hacer un lote de transacci?n, para que si una falle, todas las anteriores se anulan tambi?n. Cuando se necesita una integridad de transacci?n, se utiliza en commit/rollback.

Ejemplo: SELECT emp_no,job_grade FROM employee where emp_no=4; update employee set job_grade=6 where emp_no=4; SELECT emp_no,job_grade FROM employee where emp_no=4; rollback; SELECT emp_no,job_grade FROM employee where emp_no=4; update employee set job_grade=6 where emp_no=4; SELECT emp_no,job_grade FROM employee where emp_no=4; commit; SELECT emp_no,job_grade FROM employee where emp_no=4;

2.6 Savepoint

Un savepoint permite identificar un punto en una transacci?n al cual se podr? eventualmente regresar (rollback).

SELECT emp_no,job_grade FROM employee where emp_no=4; START TRANSACTION; update employee set job_grade=5 where emp_no=45; SELECT emp_no,job_grade FROM employee where emp_no=4; savepoint vale_cinco; SELECT emp_no,job_grade FROM employee where emp_no=4; update employee set job_grade=4 where emp_no=45; SELECT emp_no,job_grade FROM employee where emp_no=4; rollback to savepoint vale_cinco; SELECT emp_no,job_grade FROM employee where emp_no=4; rollback;

2.7 Select

El `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 ,, FROM ; SELECT as ,, FROM ;

Para seleccionar todos los campos de la tabla, se utiliza el asterisco en vez de los nombres de campo.

SELECT * FROM ;

Ejemplo: SELECT emp_no,job_grade as nivel FROM employee; SELECT * FROM employee;

? Copyright C?dric Simon, 2008

Versi?n 1.1

Reproducci?n prohibida

Curso de SQL avanzado y PL/SQL b?sico para Oracle 10g (10.2)

Pagina 7 / 44

2.8 Where

La 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 'between ... and ...'. Para comparar caracteres se utiliza la palabra 'like'. El wildcard es '%'. Para compara fecha, se utiliza el signo '=', o '', o'=', o 'between ... and ...'. Para

SELECT * FROM WHERE AND ;

Ejemplo: SELECT emp_no,job_grade FROM employee where emp_no>45; SELECT emp_no,job_grade FROM employee where emp_no=46 or SELECT * FROM employee where emp_no between 1 and 2; SELECT * FROM employee where last_name like 'P%';

emp_no=61;

2.8.1 Operadores SQL

Ya 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 se realizan:

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 lo

Significado

Ejemplo

+

Operaci?n suma

1 + 2

Operaci?n resta

1 2

*

Operaci?n multiplicaci?n 1 * 2

/

Operador divisi?n

1 / 2

Operadores l?gicos Retornan un valor l?gico (verdadero o falso)

S?mbolo = != ^= > < >= 2 1 < 2 1 >= 2 1 = ANY (1,2,3,10) [TRUE]

ALL

a todos los elementos del arreglo de resultados (derecha), Debe ser estar precedido por =, !=, = Hace un AND l?gico entre todos los elementos.

10 = x

AND y

AND op ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download