Oracle 12c Pl/SQL – Curso Práctico de Formación

El presente documento ofrece una exposición con enfoque práctico y un ritmo de aprendizaje pautado; el contenido de estas páginas se basa en el autoaprendizaje y para ello se utiliza el material y la metodología aplicados por el autor durante años en sus cursos de academias y empresas privadas, en las cuales ha formado de manera exitosa a un gran número de alumnos, muchos de ellos se encuentran actualmente trabajando en el sector de las tecnologías de la información, aplicando los conocimientos adquiridos en el manejo de bases de datos de Oracle. PL/SQL es en la actualidad uno de los lenguajes más usados en el diseño de aplicaciones con bases de datos Oracle, empresa que tiene la mayor expansión y ventas.

El propósito de este texto es actualizar la versión 12c de Oracle, pero se ha mantenido con respecto a la versión 11g del libro todo lo relacionado con el lenguaje PL/SQL, el cual sigue siendo compatible en la versión 12c de la base de datos de Oracle; se añade aquí, además, las nuevas características del lenguaje PL/SQL que se han puesto en funcionamiento para la versión 12c; asimismo, se actualizó la información respecto al proceso de certificación de Oracle.

VENTAJAS

• Contiene 15 supuestos prácticos resueltos que le serán útiles como autoevaluación de lo aprendido.

• Cuenta con numerosos ejemplos que reforzarán la comprensión de los temas abordados.

• Proporciona 55 cuestiones resueltas de certificación de Oracle en el lenguaje PL/SQL.

CONOZCA

• El funcionamiento de las estructuras de control del lenguaje PL/SQL.

• Cómo operan cada uno de los tres tipos de cursores en PL/SQL (cursores explícitos (Cursor), cursores implícitos (SQL) y cursores dinámicos.

• En qué consisten los procedimientos, funciones y paquetes de PL/SQL.

APRENDA

• A controlar las transacciones mediante las instrucciones Commit, Rollback, Savepoint <nombre_marcador>, Rollback To <nombre_marcador_savepoint>.

• A llevar a cabo la ejecución de los disparadores del lenguaje PL/SQL.

• A trabajar con el lenguaje PL/SQL orientado a objetos.

DESARROLLE SUS HABILIDADES PARA

• Definir tipos en el lenguaje PL/SQL de acuerdo con las estructuras Record (Tipos registro), Table (Tipos table), Varray (Tipos array de elementos).

• Utilizar los tres tipos de bucles (Bucles simples (Loop…End Loop), Bucles While, Bucles For) en un cursor.

• Preparar un examen de certificación de Oracle.

A QUIÉN VA DIRIGIDO

El presente libro está dirigido a todo lector interesado en aprender a manejar el lenguaje PL/SQL de Oracle y no puede acudir personalmente a la oferta formativa ofrecida para este lenguaje.

INDICE

PRÓLOGO ………………………………………………………………………………………………… XI

CAPÍTULO 1. FUNDAMENTOS DEL LENGUAJE PL/SQL ……………………………………………. 1

Introducción …………………………………………………………………………………………………… 1

Unidades léxicas ……………………………………………………………………………………………… 7

Tipos de datos ………………………………………………………………………………………………. 11

Declaración de variables ………………………………………………………………………………… 15

CAPÍTULO 2. ESTRUCTURAS DE CONTROL …………………………………………………………… 21

Introducción …………………………………………………………………………………………………. 21

If-then-else-end if …………………………………………………………………………………………. 22

Case …………………………………………………………………………………………………………….. 23

Loop…end loop …………………………………………………………………………………………….. 24

While…loop…end loop …………………………………………………………………………………… 25

For…loop…end loop ………………………………………………………………………………………. 25

Goto ……………………………………………………………………………………………………………. 26

Null ……………………………………………………………………………………………………………… 27

CAPÍTULO 3. CONTROL DE TRANSACCIONES ………………………………………………………. 29

Introducción …………………………………………………………………………………………………. 29

Commit ………………………………………………………………………………………………………… 29

Rollback ……………………………………………………………………………………………………….. 33

Savepoint …………………………………………………………………………………………………….. 37

Rollback to ……………………………………………………………………………………………………. 37

CAPÍTULO 4. CREACIÓN DE TIPOS ………………………………………………………………………. 41

Introducción …………………………………………………………………………………………………. 41

Creación de un tipo record (registro) ……………………………………………………………….. 41

Creación de un tipo table (pila de elementos) …………………………………………………… 44

Varrays ………………………………………………………………………………………………………… 49

CAPÍTULO 5. SQL vs PL/SQL ……………………………………………………………………………….. 55

Introducción …………………………………………………………………………………………………. 55

Órdenes sql ………………………………………………………………………………………………….. 55

Utilización de sql en pl/sql ……………………………………………………………………………… 57

Sql dinámico …………………………………………………………………………………………………. 61

CAPÍTULO 6. CURSORES ……………………………………………………………………………………… 65

¿Qué es un cursor? ……………………………………………………………………………………….. 65

Cursores explícitos ………………………………………………………………………………………… 66

Cursores implícitos ………………………………………………………………………………………… 70

Cursores sql dinámico ……………………………………………………………………………………. 71

CAPÍTULO 7. SUBPROGRAMAS …………………………………………………………………………… 77

Introducción …………………………………………………………………………………………………. 77

Procedimientos …………………………………………………………………………………………….. 78

Funciones …………………………………………………………………………………………………….. 86

New 12c Restringiendo permisos de uso a subprogramas ……………………………………. 93

CAPÍTULO 8. PAQUETES …………………………………………………………………………………… 97

Introducción …………………………………………………………………………………………………. 97

Especificación o cabecera del paquete …………………………………………………………….. 98

Cuerpo del paquete ……………………………………………………………………………………….. 99

Referenciando a los paquetes ……………………………………………………………………………… 100

Inicialización de un paquete ………………………………………………………………………………… 100

Sobrecarga de paquetes..…………………………………………………………………………………….. 101

Dependencias.……………………………………………………………………………………………………… 103

CAPÍTULO 9. PAQUETES PREDETERMINADOS …………………………………………………… 109

Introducción ……………………………………………………………………………………………….. 109

Lista de paquetes predeterminados ……………………………………………………………….. 109

Dbms_db_version ……………………………………………………………………………………….. 122

Dbms_file_transfer ……………………………………………………………………………………… 123

Dbms_output ……………………………………………………………………………………………… 128

Dbms_random ……………………………………………………………………………………………. 133

New 12c Dbms_utility …………………………………………………………………………………… 138

Utl_file ………………………………………………………………………………………………………. 159

Utl_mail ……………………………………………………………………………………………………… 183

Utl_http ……………………………………………………………………………………………………… 190

CAPÍTULO 10. DISPARADORES O TRIGGERS ……………………………………………………… 243

Introducción ……………………………………………………………………………………………….. 243

Utilidad de los triggers …………………………………………………………………………………. 244

Integridad referencial…………………………………………………………………………………… 244

Espacio de nombres del disparador ……………………………………………………………….. 248

Momento del disparo ………………………………………………………………………………….. 248

Suceso del disparo ………………………………………………………………………………………. 249

Nivel de disparo ………………………………………………………………………………………….. 249

Condición de disparo …………………………………………………………………………………… 249

Sentencias de borrado y alteración de triggers ………………………………………………… 250

Uso de los predicados :old y :new ………………………………………………………………….. 251

Uso de los predicados booleanos …………………………………………………………………… 251

Tablas mutantes ………………………………………………………………………………………….. 252

CAPÍTULO 11. TRATAMIENTO DE ERRORES ………………………………………………………. 263

Introducción ……………………………………………………………………………………………….. 263

Declaración de excepciones ………………………………………………………………………….. 264

Excepciones definidas por el usuario ……………………………………………………………… 264

Provocar excepciones ………………………………………………………………………………….. 266

Sintaxis de la sección exception …………………………………………………………………….. 267

Uso de sqlcode y sqlerrm ……………………………………………………………………………… 268

Utilización de raise_application_error ……………………………………………………………. 269

Utilización de exception_init …………………………………………………………………………. 269

Propagación de excepciones …………………………………………………………………………. 270

CAPÍTULO 12. CURSORES AVANZADOS …………………………………………………………….. 277

Bucles de extracción ……………………………………………………………………………………. 277

Bucles simples (loop … end loop) …………………………………………………………………… 277

Bucles while ……………………………………………………………………………………………….. 278

Bucles for …………………………………………………………………………………………………… 279

Cursores select for update ……………………………………………………………………………. 280

CAPÍTULO 13. OBJETOS ……………………………………………………………………………………. 285

Introducción ……………………………………………………………………………………………….. 285

Bases de la programación orientada a objetos ………………………………………………… 285

Objetos e instancias de los objetos ………………………………………………………………… 287

Bases de datos objeto-relacionales ………………………………………………………………… 288

Definición de los tipos de objetos ………………………………………………………………….. 288

Llamada a un método ………………………………………………………………………………….. 292

Borrar un objeto ………………………………………………………………………………………….. 292

Modificar un objeto …………………………………………………………………………………….. 292

Creación de tablas de objetos ……………………………………………………………………….. 293

Inserción de valores en una tabla de objetos …………………………………………………… 293

CAPÍTULO 14. ENTORNOS DE EJECUCIÓN PL/SQL ……………………………………………… 295

Introducción ……………………………………………………………………………………………….. 295

Sql*plus / isql*plus / sql*worksheet ………………………………………………………………. 295

Ejecución de código sql ………………………………………………………………………………… 296

Ejecución de código pl/sql …………………………………………………………………………….. 296

Definición de variables globales …………………………………………………………………….. 297

Uso de variables globales ……………………………………………………………………………… 297

Cómo se puede llamar a un procedimiento almacenado …………………………………… 298

Cómo se puede llamar a una función almacenada …………………………………………… 298

Envío de resultados a un archivo …………………………………………………………………… 299

Ejecución de scripts (archivos) de comandos …………………………………………………… 300

Mostrar errores de compilación …………………………………………………………………….. 300

Herramientas de diseño ……………………………………………………………………………….. 301

Oracle SQL developer …………………………………………………………………………………… 304

CAPÍTULO 15. CERTIFICACIONES DE ORACLE ……………………………………………………. 309

Introducción ……………………………………………………………………………………………….. 309

Certificaciones de Oracle disponibles …………………………………………………………….. 309

Preguntas tipo examen de certificación SQL ……………………………………………………. 311

ANEXO I. RESOLUCIÓN DE SUPUESTOS PRÁCTICOS ………………………………………….. 335

Supuesto práctico 0 ……………………………………………………………………………………… 335

Supuesto práctico 1 ……………………………………………………………………………………… 337

Supuesto práctico 2 ……………………………………………………………………………………… 338

Supuesto práctico 3 ……………………………………………………………………………………… 340

Supuesto práctico 4 ……………………………………………………………………………………… 343

Supuesto práctico 5 ……………………………………………………………………………………… 348

Supuesto práctico 6 ……………………………………………………………………………………… 352

Supuesto práctico 7 ……………………………………………………………………………………… 355

Supuesto práctico 8 ……………………………………………………………………………………… 357

Supuesto práctico 9 ……………………………………………………………………………………… 358

Supuesto práctico 10 …………………………………………………………………………………… 360

Supuesto práctico 11 …………………………………………………………………………………… 364

Supuesto práctico 12 …………………………………………………………………………………… 366

Supuesto práctico 13 …………………………………………………………………………………… 368

Supuesto práctico 14 …………………………………………………………………………………… 371

Supuesto práctico 15 …………………………………………………………………………………… 373

ANEXO II. RESOLUCIÓN CUESTIONES DE CERTIFICACIÓN ………………………………….. 377

Cuestión 1 ………………………………………………………………………………………………….. 377

Cuestión 2 ………………………………………………………………………………………………….. 378

Cuestión 3 ………………………………………………………………………………………………….. 378

Cuestión 4 ………………………………………………………………………………………………….. 379

Cuestión 5 ………………………………………………………………………………………………….. 379

Cuestión 6 ………………………………………………………………………………………………….. 380

Cuestión 7 ………………………………………………………………………………………………….. 380

Cuestión 8 ………………………………………………………………………………………………….. 381

Cuestión 9 ………………………………………………………………………………………………….. 382

Cuestión 10 ………………………………………………………………………………………………… 383

Cuestión 11 ………………………………………………………………………………………………… 383

Cuestión 12 ………………………………………………………………………………………………… 384

Cuestión 13 ………………………………………………………………………………………………… 384

Cuestión 14 ………………………………………………………………………………………………… 384

Cuestión 15 ………………………………………………………………………………………………… 385

Cuestión 16 ………………………………………………………………………………………………… 386

Cuestión 17 ………………………………………………………………………………………………… 386

Cuestión 18 ………………………………………………………………………………………………… 387

Cuestión 19 ………………………………………………………………………………………………… 387

Cuestión 20 ………………………………………………………………………………………………… 387

Cuestión 21 ………………………………………………………………………………………………… 388

Cuestión 22 ………………………………………………………………………………………………… 388

Cuestión 23 ………………………………………………………………………………………………… 389

Cuestión 24 ………………………………………………………………………………………………… 389

Cuestión 25 ………………………………………………………………………………………………… 390

Cuestión 26 ………………………………………………………………………………………………… 390

Cuestión 27 ………………………………………………………………………………………………… 391

Cuestión 28 ………………………………………………………………………………………………… 391

Cuestión 29 ………………………………………………………………………………………………… 391

Cuestión 30 ………………………………………………………………………………………………… 392

Cuestión 31 ………………………………………………………………………………………………… 392

Cuestión 32 ………………………………………………………………………………………………… 393

Cuestión 33 ………………………………………………………………………………………………… 394

Cuestión 34 ………………………………………………………………………………………………… 394

Cuestión 35 ………………………………………………………………………………………………… 394

Cuestión 36 ………………………………………………………………………………………………… 395

Cuestión 37 ………………………………………………………………………………………………… 396

Cuestión 38 ………………………………………………………………………………………………… 396

Cuestión 39 ………………………………………………………………………………………………… 396

Cuestión 40 ………………………………………………………………………………………………… 397

Cuestión 41 ………………………………………………………………………………………………… 397

Cuestión 42 ………………………………………………………………………………………………… 397

Cuestión 43 ………………………………………………………………………………………………… 398

Cuestión 44 ………………………………………………………………………………………………… 398

Cuestión 45 ………………………………………………………………………………………………… 398

Cuestión 46 ………………………………………………………………………………………………… 399

Cuestión 47 ………………………………………………………………………………………………… 399

Cuestión 48 ………………………………………………………………………………………………… 399

Cuestión 49 ………………………………………………………………………………………………… 400

Cuestión 50 ………………………………………………………………………………………………… 400

Cuestión 51 ………………………………………………………………………………………………… 400

Cuestión 52 ………………………………………………………………………………………………… 400

Cuestión 53 ………………………………………………………………………………………………… 401

Cuestión 54 ………………………………………………………………………………………………… 401

Cuestión 55 ………………………………………………………………………………………………… 401

ANEXO III. FICHERO SCRIPT_BDHOSPITAL …………………………………………………………. 403

Introducción ……………………………………………………………………………………………….. 403

Diagrama entidad/relación del esquema del hospital ………………………………………. 404

Modelo relacional del esquema del hospital …………………………………………………… 405

Contenido del fichero con comentarios ………………………………………………………….. 405

ANEXO IV. REFERENCIAS Y MATERIAL ANEXO EN INTERNET ……………………………… 429

Referencias utilizadas para el curso ……………………………………………………………….. 429

Enlaces a Oracle ………………………………………………………………………………………….. 429

ANEXO V. GUIA DE INSTALACIÓN DE ORACLE 11G XE ……………………………………….. 431

Introducción ……………………………………………………………………………………………….. 431

Requerimientos mínimos ……………………………………………………………………………… 431

Tutorial de instalación ………………………………………………………………………………….. 432

ÍNDICE ALFABÉTICO ………………………………………………………………………………………….. 439