Baixe o app para aproveitar ainda mais
Prévia do material em texto
Oracle 12c Fundamentals II Linguagem PL/SQL TOR12CF2 Agosto/2015 Apostila desenvolvida especialmente para a Target Trust Treinamento e Tecnologia Ltda. Sua cópia ou reprodução é expressamente proibida. Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia I Sumário 1. Introdução ao PL/SQL ........................................................................................... 1 Objetivos ........................................................................................................................................................ 2 Linguagem PL/SQL ..................................................................................................................................... 3 Ambiente PL/SQL ........................................................................................................................................ 4 PL/SQL Engine e o Servidor Oracle ............................................................................................... 4 Benefícios do PL/SQL ................................................................................................................................. 5 Integração .............................................................................................................................................. 5 PL/SQL em Ferramentas Oracle ...................................................................................................... 5 Melhor Desempenho ......................................................................................................................... 6 Estrutura dos Blocos PL/SQL ................................................................................................................... 7 Construção de Programas PL/SQL ................................................................................................ 7 Modularização do Desenvolvimento de Programas............................................................... 7 Portabilidade ......................................................................................................................................... 8 Declaração de Identificadores ........................................................................................................ 8 Programe com Estruturas de Controle de Linguagem Procedural ................................... 8 Tratamento de Erros ........................................................................................................................... 8 Desenvolvendo utilizando PL/SQL ........................................................................................................ 9 Blocos PL/SQL Anônimos ...................................................................................................................... 10 Estrutura básica de um bloco PL/SQL anônimo. ................................................................... 10 Blocos PL/SQL Nomeados: Subprogramas ..................................................................................... 12 Vantagens do Uso de Procedimentos e Funções ......................................................................... 13 Diferenças entre Subprogramas de Banco de Dados e Subprogramas de Aplicação .... 14 Conectando com o SQL*Plus ............................................................................................................... 15 A partir do ambiente Windows ................................................................................................... 15 A partir da linha de comando ...................................................................................................... 15 Conectando com o SQLDeveloper .................................................................................................... 17 Criando uma conexão no SQL Developer ............................................................................... 17 Propriedades da conexão no SQLDeveloper .......................................................................... 19 Utilizando o SQLDeveloper .................................................................................................................. 20 Executando um comando SQL no SQLDeveloper ................................................................ 20 Executando um Script de comandos SQLPLUS, SQL e PL/SQL no SQLDeveloper .. 20 Utilizando o Histórico de Comandos no SQLDeveloper .................................................... 20 Voltando e Avançando Comandos no SQLDeveloper utilizando teclas ....................... 20 Criando Procedimentos e Funções de Banco de Dados ............................................................ 21 Executando Subprogramas .................................................................................................................. 22 Espaço para anotações ................................................................................................................... 23 2. Construindo Blocos Anônimos e Definindo Identificadores .......................... 24 Objetivos ..................................................................................................................................................... 25 Estrutura de um Bloco PL/SQL ............................................................................................................ 26 Executando Comandos e Blocos PL/SQL a partir do SQL Plus ........................................ 27 Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia II Tipos de Programas ................................................................................................................................ 28 Blocos anônimos ............................................................................................................................... 28 Subprogramas ................................................................................................................................... 28 Construções de Programas .................................................................................................................. 30 Utilização de Variáveis............................................................................................................................ 32 Unidades Léxicas ...................................................................................................................................... 33 Delimitadores ..................................................................................................................................... 33 Identificadores ........................................................................................................................................... 36 Identificadores Pré-definidos ....................................................................................................... 36 Quoted Identifiers ............................................................................................................................ 36 Variáveis definadas pelo usuário, Subrotinas e Tipos de Dados ..................................... 36 Literais .......................................................................................................................................................... 37 Literal caracter ................................................................................................................................... 37 Literal String ....................................................................................................................................... 37 Literais numéricos............................................................................................................................. 37 Literais Boolean .................................................................................................................................38 Literais tipo Date ............................................................................................................................... 38 Controlando Variáveis em PL/SQL ..................................................................................................... 40 Tipos de Variáveis .................................................................................................................................... 41 Tipos de Dados Escalar - Scalar Datatypes ..................................................................................... 42 Boolean ................................................................................................................................................ 42 CHAR and CHARACTER Datatypes ............................................................................................. 42 Tipo de dado LONG e LONG RAW............................................................................................. 44 Tipo de dado ROWID e UROWID ............................................................................................... 45 Tipo de dado Date ........................................................................................................................... 46 Subtipo TIMESTAMP ....................................................................................................................... 46 Caracteres e Strings Unicode ....................................................................................................... 48 Tipo de dado BINARY_INTEGER .................................................................................................. 48 Tipos de dados formato IEEE 754 - BINARY_DOUBLE e BINARY_FLOAT ..................... 48 Tipo de dado NUMBER .................................................................................................................. 48 Declarando Variáveis PL/SQL ............................................................................................................... 51 Diretrizes .............................................................................................................................................. 51 Regras de Nomenclatura ....................................................................................................................... 53 Atribuindo Valores para Variáveis ...................................................................................................... 54 Utilizando Variáveis e Palavras Chaves ............................................................................................ 55 Atributo %TYPE ......................................................................................................................................... 56 Declarando Variáveis com o Atributo %TYPE ................................................................................ 57 Declarando Variáveis BOOLEAN ......................................................................................................... 58 Tipos de Dados Compostos ................................................................................................................. 59 Variáveis com Tipos de Dados LOB ................................................................................................... 60 Variáveis Tipo BIND ................................................................................................................................. 61 Criando Variáveis BIND .................................................................................................................. 61 Exibindo Variáveis BIND no SQL Plus ........................................................................................ 61 Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia III Referenciando Variáveis Bind .............................................................................................................. 62 Sintaxe e Diretrizes de um Bloco PL/SQL ........................................................................................ 63 Identificadores ................................................................................................................................... 63 Literais ................................................................................................................................................... 63 Comentando o Código .......................................................................................................................... 65 Funções SQL em PL/SQL ....................................................................................................................... 66 Conversão de Tipos de Dados............................................................................................................. 67 Blocos Aninhados e Escopo de Variáveis ........................................................................................ 70 Blocos Aninhados ............................................................................................................................. 70 Escopo de Variáveis ......................................................................................................................... 70 Identificadores ................................................................................................................................... 70 Blocos Aninhados e Escopo de Variáveis ................................................................................. 70 Identificando blocos através de labels ............................................................................................. 72 Operadores em PL/SQL ......................................................................................................................... 73 Exemplos .............................................................................................................................................. 73 Regras de Precedência ........................................................................................................................... 74 Diretrizes de Programação ................................................................................................................... 75 Convenções de Código .................................................................................................................. 75 Convenções de Nomenclatura ............................................................................................................ 76 Indentação de código ............................................................................................................................ 77 Package DBMS_OUTPUT ....................................................................................................................... 78 Depuração utilizando DBMS_OUPUT ........................................................................................ 78 Determine o Escopo das Variáveis ..................................................................................................... 80 Exercícios 2 ................................................................................................................................................. 81 Espaço para anotações ................................................................................................................... 83 3. Interagindo com o Banco de Dados Oracle ...................................................... 84 Objetivos ..................................................................................................................................................... 85 Comandos SQL em PL/SQL .................................................................................................................. 86 Comparando Tipos de Comando SQL e PL/SQL ................................................................... 86 Comando SELECTem PL/SQL .............................................................................................................. 87 A Cláusula INTO ................................................................................................................................ 87 SELECT deve Retornar uma e Somente uma Linha .............................................................. 88 Recuperando Dados em PL/SQL ........................................................................................................ 89 Diretrizes .............................................................................................................................................. 89 Manipulando Dados Utilizando PL/SQL .......................................................................................... 90 Comando INSERT em PL/SQL .............................................................................................................. 91 Comando Update em PL/SQL ............................................................................................................. 92 Comando DELETE em PL/SQL ............................................................................................................. 93 Evitando Ambigüidade ........................................................................................................................... 94 Comandos COMMIT e ROLLBACK ..................................................................................................... 95 COMMIT ............................................................................................................................................... 95 ROLLBACK ........................................................................................................................................... 95 Cursor SQL .................................................................................................................................................. 96 Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia IV Atributos do Cursor SQL ....................................................................................................................... 97 Exercícios 3 ................................................................................................................................................. 98 Espaço para anotações ................................................................................................................... 99 4. Escrevendo Estruturas de Controle .................................................................. 100 Objetivos ................................................................................................................................................... 101 Operadores em PL/SQL ....................................................................................................................... 102 Regras de Precedência em PL/SQL .................................................................................................. 103 Tabelas Lógicas ....................................................................................................................................... 104 Condições Boleanas .............................................................................................................................. 105 Controlando o Fluxo de Execução PL/SQL.................................................................................... 106 Comando IF .............................................................................................................................................. 107 Comandos IF Simples ........................................................................................................................... 108 Diretrizes ............................................................................................................................................ 108 Fluxo de Execução do Comando IF-THEN-ELSE ......................................................................... 109 Comandos IF Aninhados .............................................................................................................. 109 Comandos IF-THEN-ELSE .................................................................................................................... 110 Fluxo de Execução do Comando IF-THEN-ELSIF ........................................................................ 111 Condições Lógicas ................................................................................................................................. 113 Nulos em Expressões e Comparações ............................................................................................ 114 Comando CASE ....................................................................................................................................... 115 Controle de Repetições: Comandos de LOOP ............................................................................. 117 LOOP Básico ............................................................................................................................................. 118 Comando EXIT ................................................................................................................................. 118 FOR LOOP ................................................................................................................................................. 119 Diretrizes ............................................................................................................................................ 119 WHILE LOOP ............................................................................................................................................ 121 LOOPs Aninhados e Labels ................................................................................................................. 122 Exercícios 4 ............................................................................................................................................... 123 Espaço para anotações ................................................................................................................. 124 5. Tipos Compostos - Records e Collections ....................................................... 125 Objetivos ................................................................................................................................................... 126 PL/SQL Records ...................................................................................................................................... 127 Estrutura de um PL/SQL Record ................................................................................................ 127 Criando um PL/SQL Record ................................................................................................................ 128 Referenciando e Inicializando Registros ........................................................................................ 130 Atribuindo Valores para Registros ........................................................................................... 130 Atributo %ROWTYPE............................................................................................................................. 131 Vantagens da Utilização de %ROWTYPE ....................................................................................... 132 O que é uma Collection? ..................................................................................................................... 133 Associative Arrays .................................................................................................................................. 134 Criando um Associative Array ........................................................................................................... 135 Estrutura deum Associative Array ................................................................................................... 136 Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia V Utilizando um Associative Array ....................................................................................................... 137 Referenciando um Associative Array ....................................................................................... 137 Associative Array de Registros .......................................................................................................... 138 Referenciando um Associative Array com Registros ......................................................... 138 Nested Table ............................................................................................................................................ 139 Criando uma Nested Table ................................................................................................................. 140 Utilizando Nested Tables .................................................................................................................... 141 Referenciando uma Nested Table ............................................................................................ 141 Varrays ....................................................................................................................................................... 142 Criando um Varray ................................................................................................................................. 143 Utilizando Varrays .................................................................................................................................. 144 Referenciando um Varray ............................................................................................................ 144 Utilizando Métodos de Collections ................................................................................................. 145 Exemplo de utilização dos métodos de Collections .................................................................. 146 Exercícios 5 ............................................................................................................................................... 147 Espaço para anotações ................................................................................................................. 148 6. Cursores Explícitos ............................................................................................. 149 Objetivos ................................................................................................................................................... 150 Cursores ..................................................................................................................................................... 151 Cursores Explícitos ................................................................................................................................. 152 Funções do Cursor Explícito ....................................................................................................... 152 Controlando Cursores Explícitos....................................................................................................... 153 Controlando Cursores Explícitos Utilizando Quatro Comandos ................................... 153 Contralando o cursor ............................................................................................................................ 155 Declarando o Cursor ............................................................................................................................. 156 Abrindo um Cursor ................................................................................................................................ 157 Recuperando linhas do Cursor .......................................................................................................... 158 Diretrizes ............................................................................................................................................ 158 Fechando um Cursor ............................................................................................................................. 159 Atributos de Cursores Explícitos ....................................................................................................... 160 Controlando Múltiplos FETCHs ......................................................................................................... 161 Atributo %ISOPEN ................................................................................................................................. 162 Atributos %NOTFOUND e %ROWCOUNT .................................................................................... 163 Cursores e Variáveis records .............................................................................................................. 164 Cursor FOR LOOP ................................................................................................................................... 165 Diretrizes ............................................................................................................................................ 165 Cursor FOR LOOP Utilizando Subconsultas .................................................................................. 167 Cursores com Parâmetros ................................................................................................................... 168 Cláusula FOR UPDATE .......................................................................................................................... 170 Cláusula WHERE CURRENT OF .......................................................................................................... 171 Exercícios 6 ............................................................................................................................................... 172 Espaço para anotações ................................................................................................................. 173 Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia VI 7. Tratamento de Exceções ................................................................................... 174 Objetivos ................................................................................................................................................... 175 Tratando Exceções ................................................................................................................................. 176 Tipos de exceções: ......................................................................................................................... 176 Tratando Exceções com PL/SQL ................................................................................................ 176 Métodos de Disparo de Exceções ............................................................................................ 176 Tratamento de Exceções .............................................................................................................. 177 Tratando uma Exceção ................................................................................................................. 177 Exceção Propagada ........................................................................................................................ 177 Seção Exception .............................................................................................................................. 177 Tratamento de Exceções com WHEN OTHERS .................................................................... 178 Diretrizes para o Tratamento de Exceções ............................................................................ 178 TratandoErros Pré-Definidos do Servidor Oracle ...................................................................... 179 Interrupções de Programa .................................................................................................................. 180 SQLCODE e SQLERRM .......................................................................................................................... 183 Exceções Definidas pelo Desenvolvedor ....................................................................................... 184 Exceções Disparadas pelo Desenvolvedor ............................................................................ 184 Tratamento de Erros Oracle utilizando Pragma .......................................................................... 185 Exercícios 7 ............................................................................................................................................... 186 Espaço para anotações ................................................................................................................. 187 8. Procedures de Banco de Dados ........................................................................ 188 Objetivos ................................................................................................................................................... 189 Criando Procedures de Banco de Dados ....................................................................................... 190 Parâmetros ............................................................................................................................................... 191 Parâmetros tipo IN ................................................................................................................................ 192 Opção DEFAULT .............................................................................................................................. 192 Parâmetros tipo OUT ............................................................................................................................ 193 Parâmetros tipo IN OUT ...................................................................................................................... 194 Parâmetros OUT e IN OUT por referência .................................................................................... 195 Métodos de passagem de Parâmetros .......................................................................................... 196 Passando parâmetros - Método Posicional .......................................................................... 196 Passando Parâmetros - Método Nomeado .......................................................................... 196 Passando Parâmetros - Método Combinado ....................................................................... 197 Executando Procedures ....................................................................................................................... 198 Executando procedures no SQL Plus ....................................................................................... 198 Executando procedures em Blocos PL/SQL .......................................................................... 198 Removendo Procedures de Banco de Dados .............................................................................. 199 Exercícios 8 ............................................................................................................................................... 200 Espaço para anotações ................................................................................................................. 201 9. Funções de Banco de Dados ............................................................................. 202 Objetivos ................................................................................................................................................... 203 Criando Funções de Banco de Dados ............................................................................................. 204 Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia VII Utilizando Funções em comandos SQL ......................................................................................... 206 Utilizando Parâmetros em Funções ................................................................................................. 207 Executando Funções ............................................................................................................................. 208 Executando Funções a partir do SQL Plus ............................................................................. 208 Executando Funções a partir de um Bloco PL/SQL ............................................................ 208 Removendo Funções de Banco de Dados .................................................................................... 209 Comparando Procedures X Funções ............................................................................................... 210 Quando uma função é apropriada, e quando um procedimento é apropriado? ... 210 Exercícios 9 ............................................................................................................................................... 211 Espaço para anotações ................................................................................................................. 212 10. Gerenciando Subprogramas e Dependências................................................. 213 Objetivos ................................................................................................................................................... 214 Gerenciando Procedures e Funções - Visão Geral ..................................................................... 215 Informações sobre Procedures e Funções Armazenadas no Banco de Dados ........ 215 Consultando objetos tipo Procedure e Function ................................................................ 215 Consultando o Código Fonte ............................................................................................................ 217 Exemplos de Consultas ................................................................................................................. 217 Consultando a Lista de Parâmetros ................................................................................................. 218 Consultando Erros ................................................................................................................................. 219 Gerenciando Dependências ............................................................................................................... 220 Tipos de Dependências ........................................................................................................................ 221 Dependências Diretas ................................................................................................................... 221 Dependências Indiretas ................................................................................................................ 221 Dependência Local ......................................................................................................................... 223 Dependência Remota .................................................................................................................... 223 Tratamento de Dependências Locais Diretas .............................................................................. 224 Consultando a visão USER_DEPENDENCIES ......................................................................... 224 Tratamento de Dependências Locais Indiretas ........................................................................... 225 Consultando as tabelas DEPTREE e IDEPTREE ..................................................................... 225 Consultando a visãoDEPTREE ................................................................................................... 226 Consultando a visão IDEPTREE .................................................................................................. 226 Re-compilando Procedures e Funções ........................................................................................... 228 Compilando um Procedure ......................................................................................................... 228 Compilando uma Função ............................................................................................................ 228 Exemplos de Compilações de Procedimentos e Funções ....................................................... 229 Verificando o Status de Procedimentos e Funções ................................................................... 230 Re-compilação automática de Procedures e Funções ............................................................. 231 Mecanismo de Re-compilação Automática para Dependências Locais ..................... 231 Tratamento Explícito de Dependências Remotas ....................................................................... 232 Exercícios 10 ............................................................................................................................................. 233 Espaço para anotações ................................................................................................................. 234 Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia VIII 11. Desenvolvendo e Utilizando Packages ............................................................ 235 Objetivos ................................................................................................................................................... 236 O Que são Packages ............................................................................................................................. 237 Desenvolvendo Packages - Visão Geral ......................................................................................... 238 Componentes (construções) de uma Package .................................................................... 238 Construções Públicas em Packages ......................................................................................... 238 Construções Privadas em Packages ......................................................................................... 238 Criando Packages de Banco de Dados ........................................................................................... 239 Passos para a criação de uma Package .................................................................................. 239 Criando o Package Specification ...................................................................................................... 240 Criando o Package Body ..................................................................................................................... 241 Procedimento de Única Execução .................................................................................................... 242 Removendo Packages .......................................................................................................................... 243 Removendo o Package Body ............................................................................................................. 244 Referenciando Componentes de Packages .................................................................................. 245 Referenciando componentes da package a partir de construções da própria Package ...................................................................................................................................................... 245 Referenciando componentes da Package a partir de construções externas à Package ...................................................................................................................................................... 246 Benefícios do Uso de Packages ........................................................................................................ 247 Gerenciando Dependências em Packages .................................................................................... 248 Dependências em referências a construções de uma package ..................................... 248 Dependências em referências de uma Package a Procedimentos e Funções normais ...................................................................................................................................................................... 248 Re-compilando Packages ............................................................................................................ 248 Exercícios 11 ............................................................................................................................................. 249 Espaço para anotações ................................................................................................................. 250 12. Database DML Triggers ..................................................................................... 251 Objetivos ................................................................................................................................................... 252 Database DML Triggers - Visão Geral ............................................................................................ 253 Restrições em Database DML Triggers ................................................................................... 253 Criando Database DML Triggers ...................................................................................................... 255 Elementos de uma Database Trigger ...................................................................................... 255 DML Triggers em Nível de Linha e em Nível de Comando .................................................... 256 Sequência de disparo das Triggers.................................................................................................. 257 Criando uma Trigger em Nível de Comando ............................................................................... 258 Tempos de Disparo da Trigger .................................................................................................. 258 Criando uma Trigger Combinando Vários Eventos ................................................................... 260 Triggers em Nível de Linha ................................................................................................................. 261 Criando Triggers em Nível de Linha ................................................................................................ 262 Utilizando pseudo fields OLD e NEW ...................................................................................... 263 Acessando valores OLD e NEW em nível de linha .............................................................. 263 Execução Condicional: - Cláusula WHEN ............................................................................... 265 Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia IX Cláusula Referencing ..................................................................................................................... 266 Triggers INSTEAD OF ............................................................................................................................ 267 Criando Triggers INSTEAD OF ........................................................................................................... 268 Mutating Tables ...................................................................................................................................... 269 Regras para Uso de Triggers – Mutating Tables.........................................................................270 Regra 1 de Mutating Table ......................................................................................................... 270 Regra 2 de Mutating Table ......................................................................................................... 271 Resolvendo o problema de Mutating Tables............................................................................... 272 Habilitando e Desabilitando Database DML Triggers .............................................................. 274 Removendo uma Database DML Trigger ...................................................................................... 275 Gerenciando Database Triggers ....................................................................................................... 276 Consultando o Código Fonte de Database Triggers ................................................................. 277 Exercícios 12 ............................................................................................................................................. 278 Espaço para anotações ................................................................................................................. 279 13. Package UTL_FILE ............................................................................................... 280 Objetivos ................................................................................................................................................... 281 Package UTL_FILE ................................................................................................................................... 282 Procedures e Funções da Package UTL_FILE ................................................................................ 283 Exceções da package UTL_FILE ......................................................................................................... 284 Utilizando a Package UTL_FILE .......................................................................................................... 285 Escrevendo Arquivos utilizando UTL_FILE ............................................................................. 285 Lendo Arquivos utlizando UTL_FILE ......................................................................................... 287 Executando as procedures .......................................................................................................... 288 Exercícios - 13 .......................................................................................................................................... 289 Espaço para anotações ................................................................................................................. 291 Apêndice 1 - Package UTL_SMTP .............................................................................. 292 Package UTL_SMTP ............................................................................................................................... 293 Simple Mail Transfer Protocol (SMTP) .................................................................................... 293 Procedures e Funções da Package UTL_SMTP ............................................................................ 294 Exceções da package UTL_SMTP .............................................................................................. 294 Códigos de Retorno SMTP ................................................................................................................. 295 Utilizando a Package UTL_SMTP ...................................................................................................... 296 Espaço para anotações ................................................................................................................. 297 Apêndice 2 - Manipulando Objetos Grandes (LOB) ................................................ 298 Tipos de Objetos Grandes (Large Objects – LOB) ...................................................................... 299 De onde vêm os 128 terabytes? ................................................................................................ 299 Características Gerais de Campos LOB ........................................................................................... 300 Criando uma Tabela com Campos LOB ......................................................................................... 301 LOBs Internos .......................................................................................................................................... 302 Armazenamento In-Line e Out-Of-Line ................................................................................. 302 LOBs Externos .......................................................................................................................................... 303 Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia X O Alias de Diretório ............................................................................................................................... 304 Comparação entre Tipos de Dados LOB e LONG ...................................................................... 305 Trabalhando com LOBs Utilizando SQL ......................................................................................... 306 Trabalhando com LOBs Utilizando PL/SQL:Package DBMS_LOB ......................................... 307 Rotinas da Package DBMS_LOB ........................................................................................................ 308 Tipos de Dados da Package DBMS_LOB ....................................................................................... 309 Manipulando LOBs ................................................................................................................................ 310 Carregando um BFILE .................................................................................................................... 310 Utilizando LOBs Internos ............................................................................................................. 313 Escrita em um CLOB utilizando DBMS_LOB.WRITE ............................................................ 313 Exibição de um CLOB utilizando DBMS_LOB.READ ........................................................... 314 Exemplo de manipulação de um CLOB .................................................................................. 316 Armazenando Arquivos Binários no Banco de Dados .............................................................. 318 Importando um Arquivo Binário para um BLOB ................................................................. 319 Exportando um BLOB .................................................................................................................... 321 Removendo LOBs ................................................................................................................................... 322 Enviando e-mail com conteúdo anexo .......................................................................................... 323 Exercício Apêndice 2 – Manipulando Objetos Grandes (LOB) ............................................... 326 Solução Apêndice 2 – Manipulando Objetos Grandes (LOB) ................................................ 327 Espaço para Anotações ................................................................................................................ 329 Apêndice 3 – Soluções dos Exercícios ....................................................................... 330 Soluções Exercícios 2 – Declarando Variáveis ............................................................................. 331 Soluções Exercícios 3 – Interagindo com o Servidor Oracle .................................................. 333 Soluções Exercícios 4 – Escrevendo Estruturas de Controle ...................................................336 Soluções Exercícios 5 – Tipo de Dado Record e Collections .................................................. 338 Soluções Exercícios 6 – Escrevendo Cursores Explícitos .......................................................... 339 Soluções Exercícios 7 – Tratamento de Exceções ....................................................................... 340 Soluções Exercícios 8 – Procedimentos de Banco de Dados ................................................. 341 Soluções Exercícios 9 – Funções de Banco de Dados ............................................................... 342 Soluções Exercícios 10 – Gerenciando Subprogramas e Dependências ............................ 343 Soluções Exercícios 11 – Desenvolvendo e Utilizando Packages ......................................... 344 Soluções Exercícios 12 – Database DML Triggers ...................................................................... 348 Soluções Exercícios 13 – Package UTL_FILE .................................................................................. 350 Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia 1 Introdução ao PL/SQL Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 2 Objetivos Conhecer a linguagem PL/SQL; Conhecer os tipos de construções PL/SQL existentes para banco de dados Oracle; Analisar as vantagens do uso dessas construções; Diferenciar PL/SQL usado em banco de dados de PL/SQL usado em aplicações cliente. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 3 Linguagem PL/SQL PL/SQL é uma linguagem procedural desenvolvida pela Oracle que estende as funcionalidades do SQL, linguagem padrão para acesso aos bancos de dados objeto- relacionais. PL/SQL oferece uma engenharia de software moderna que cria características como, por exemplo, o tratamento de exceções dentro dos programas Oracle. PL/SQL incorpora muitas das características avançadas desenvolvidas nas linguagens de programação projetadas durante o período de 1970 a 1980. Permite a inclusão de comandos SQL de manipulação de dados e pesquisa em blocos estruturados e unidades procedurais de código, tornando o PL/SQL uma poderosa linguagem de processamento transacional. Com PL/SQL, você pode usar comandos SQL para recuperar dados do Oracle e comandos de controle PL/SQL para processar os dados. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 4 Ambiente PL/SQL Figura 1-1: Ambiente PL/SQL PL/SQL Engine e o Servidor Oracle PL/SQL não é um produto isolado da Oracle. É uma tecnologia empregada pelo Servidor Oracle e por certas ferramentas da Oracle. Blocos de PL/SQL são passados para o PL/SQL Engine onde são processados, que pode residir dentro da ferramenta ou dentro do Servidor Oracle. O executor (engine) utilizado depende de onde o bloco PL/SQL foi invocado. Quando você submete blocos PL/SQL a partir de um Pro* program, user-exit, SQL*Plus ou Server Manager, o PL/SQL Engine do Servidor Oracle é quem executa o processamento. Ele divide as declarações SQL dentro do bloco em comandos separados e os envia ao SQL Statement Executor. Isto significa que uma única transferência é necessária para enviar o bloco a partir da aplicação para o Servidor Oracle, melhorando a desempenho, especialmente em uma rede cliente-servidor. Programas armazenados dentro do banco de dados (stored procedures) podem ser referenciados por qualquer aplicação conectada ao Servidor Oracle. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 5 Benefícios do PL/SQL Figura 1-2: Benefícios do PL/SQL Integração PL/SQL representa um papel central tanto para o Servidor Oracle (através de stored procedures, stored functions, database triggers e packages) como para as ferramentas de desenvolvimento Oracle (através dos componentes de triggers do Oracle Developer). Aplicações desenvolvidas com Oracle Developer podem fazer uso de bibliotecas compartilhadas que armazenam código (procedures e funções) e podem ser acessadas localmente ou remotamente. Tipo de dados SQL também podem ser utilizados em PL/SQL. Combinado com o acesso direto que o SQL provê, estes tipos de dados compartilhados integram o PL/SQL com o dicionário de dados do Servidor Oracle. O PL/SQL preenche a lacuna entre o acesso conveniente para tecnologia de banco de dados e a necessidade de características de programação procedural. PL/SQL em Ferramentas Oracle Muitas ferramentas Oracle, incluindo o Oracle Developer, possuem seu próprio PL/SQL Engine, que é independente do PL/SQL Engine presente no Servidor Oracle. O engine filtra os comandos SQL e os envia individualmente para o SQL Statement Executor do Servidor Oracle, processando os comandos procedurais restantes no Procedural Statement Executor, que está dentro do PL/SQL Engine. O Procedural Statement Executor processa os dados que são locais à aplicação (o que já está dentro do ambiente do cliente, em lugar do banco de dados). Isto reduz o trabalho enviado ao Servidor Oracle e o número de cursores de memória necessários. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 6 Melhor Desempenho Figura 1-3: Melhor desempenho do PL/SQL PL/SQL pode melhorar o desempenho de uma aplicação. Os benefícios diferem dependendo do ambiente de execução. PL/SQL pode ser usado para agrupar comandos SQL dentro de um único bloco enviando-o ao servidor em uma única chamada, reduzindo-se de forma considerável o tráfico de rede. Sem PL/SQL, os comandos SQL seriam processados um de cada vez. Cada comando SQL resultaria em outra chamada ao Servidor Oracle e aumento do tráfego da rede. Em um ambiente de rede, o tráfego pode tornar-se significativo. Como a figura ilustra, se sua aplicação utiliza SQL de forma intensiva, você pode utilizar blocos PL/SQL e subprogramas para agrupar comandos SQL antes de enviá-los ao Servidor Oracle para execução. PL/SQL também podem cooperar com Servidor Oracle em ferramentas para o desenvolvimento de aplicações como o Oracle Developer (Forms e Reports). Somando poder de processamento procedural para estas ferramentas, o PL/SQL impulsiona o melhoramento do desempenho. Nota: Procedures e funções declaradas como parte de uma aplicação Oracle Developer são distintas das armazenadas no banco de dados, embora a estrutura geral delas seja a mesma. Procedures e funções armazenadas no banco de dados (stores procedures e stored functions) são objetos do banco de dados que são armazenados no Dicionário de Dados do Oracle. Eles podem ser acessados por qualquer aplicação, incluindo aplicações desenvolvidas com o Oracle Developer. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 7 Estrutura dos Blocos PL/SQL Toda unidade de PL/SQL inclui um ou mais blocos. Estes blocos podem ser completamente separados ou aninhados um dentro do outro. As unidades básicas (procedures, funções e blocos anônimos) compõem um programa PL/SQL e formam blocos lógicos, que podem conter vários sub-blocos aninhados. Portanto, um bloco pode representar uma pequena parte de outro bloco, que por sua vez pode ser parte de uma unidade inteira de código. Construção de Programas PL/SQL Figura 1-4: Construção de programas PL/SQL A figura acima mostra uma variedade de diferentes tipos de construções PL/SQL que utilizam blocos PL/SQL básicos. Eles estão disponíveis dependendo do ambiente onde forem executados. Modularização do Desenvolvimento de Programas Agrupe logicamente comandos relacionados dentro de blocos. Aninhe sub-blocos dentro de blocos maiores para construir programas poderosos. Resolva um problema complexo com um conjunto de módulos lógicos bem definidos e implemente os módulos com blocos. Coloque códigos PL/SQL reutilizáveis em bibliotecas compartilhadas entre as aplicações desenvolvidas com as ferramentas do Oracle Developerou então armazene em um Servidor Oracle para torná-los acessíveis para qualquer aplicação que possa interagir com um banco de dados Oracle. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 8 Portabilidade Devido ao fato de o PL/SQL ser nativo ao Servidor Oracle, você pode mover programas para qualquer ambiente servidor (sistema operacional ou plataforma) que suporte o Servidor Oracle e PL/SQL. Em outras palavras, programas PL/SQL podem ser executados em qualquer lugar onde o Servidor Oracle estiver rodando; você não precisa efetuar modificações para cada ambiente novo. Você também pode mover código entre o Servidor Oracle e sua aplicação. Você pode escrever pacotes de programas portáveis e pode criar bibliotecas que podem ser reutilizadas em ambientes diferentes. Declaração de Identificadores Declare variáveis, constantes, cursores e exceções e os utilize em comandos SQL e procedurais. Declare variáveis com tipos de dados escalares, de referência, compostos, e de objetos grandes (LOB). Declare variáveis dinamicamente baseadas nas estruturas de dados de tabelas e colunas do banco de dados. Programe com Estruturas de Controle de Linguagem Procedural Controle uma sequência de comandos condicionalmente. Controle uma sequência de comandos interativamente em um loop. Controle o processamento das linhas de uma consulta individualmente através do uso de cursores explícitos. Tratamento de Erros Processe erros do Servidor Oracle com rotinas de tratamento de exceções. Declare condições de erro definidas pelo usuário e processe-as também com rotinas de tratamento de exceções. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 9 Desenvolvendo utilizando PL/SQL Construções PL/SQL podem tomar diferentes formas dependendo de onde são desenvolvidas e utilizadas. Normalmente, são desenvolvidos os chamados blocos anônimos ou então subprogramas. Neste curso, serão vistas as seguintes construções PL/SQL: Blocos anônimos. Procedimentos (procedures) e Funções (funtions) PL/SQL. Pacotes (packages). Gatilhos de banco de dados (database triggers). Todas elas armazenadas e executadas pelo servidor de banco de dados Oracle. Dessas estruturas, os procedimentos e funções de aplicação funcionam de forma análoga aos procedimentos e funções de banco, a serem vistos nesse curso, porém são usadas em aplicativos desenvolvidos no conjunto de ferramentas do Oracle Developer. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 10 Blocos PL/SQL Anônimos Blocos PL/SQL anônimos são criados para serem executados imediatamente após sua compilação, como no caso do uso de scripts no SQLPlus; ou para fazerem parte de uma aplicação que os executará em um momento pré-determinado, como no caso de triggers (a serem vistas mais adiante, neste curso). Blocos anônimos possuem uma estrutura que contém as palavras-chave “BEGIN” e “END” como delimitadores obrigatórios de início e fim da seção de comandos executáveis, e as palavras-chave “DECLARE” e “EXCEPTION” como delimitadores opcionais das seções de declaração de variáveis, constantes, cursores e exceções e de tratamento de exceções, respectivamente. Estrutura básica de um bloco PL/SQL anônimo. Na seção de declaração de variáveis, são declarados identificadores e estruturas de memória a serem usados ao longo do programa (seção executável), tais como variáveis escalares, cursores, exceções do usuário, etc. E na seção de declaração de exceções, são especificadas que ações devem ser tomadas no caso de determinadas circunstâncias, normalmente erros, ocorrerem durante a execução do programa. Exemplo: [DECLARE Seção de declaração de variáveis, constantes, cursores e exceções] BEGIN Seção de comandos executáveis [EXCEPTION Seção de tratamento de exceções] END; SET serveroutput ON ACCEPT pid PROMPT 'Digite o codigo do curso: ' DECLARE vpreco tcursos.preco%type; BEGIN SELECT preco INTO vpreco FROM tcursos WHERE id = &pid; DBMS_OUTPUT.PUT_LINE('Preço = ' || to_char(vpreco, 'L99G999G999D99')); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Código do curso inválido'); END; Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 11 Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 12 Blocos PL/SQL Nomeados: Subprogramas Subprogramas são programas PL/SQL identificados por cabeçalhos que permitem a uma aplicação disparar chamadas destes programas em tempo de execução. Esses subprogramas podem ser executados inúmeras vezes e compõe pedaços de uma aplicação maior, daí o seu nome. Além disso, subprogramas podem receber argumentos (parâmetros) de forma a executarem comandos em contextos diferentes a cada chamada, tornando seu código mais flexível. Subprogramas são divididos em duas categorias: procedimentos (procedures) e funções (functions). Essa categoria é determinada pelas palavras-chave PROCEDURE e FUNCTION no início do cabeçalho que precede o código PL/SQL, o qual declara também os parâmetros do subprograma, e, para funções, o tipo de retorno. A sintaxe completa será vista mais adiante. Exemplo: CABEÇALHO IS [Seção de declaração de variáveis, constantes, cursores e exceções] BEGIN Seção de comandos executáveis (SQL e PL/SQL) [EXCEPTION Seção de tratamento de exceções] END; CREATE OR REPLACE PROCEDURE helloworld IS BEGIN DBMS_OUTPUT.PUT_LINE('Hello World'); END; execute helloworld Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 13 Vantagens do Uso de Procedimentos e Funções Modularização da aplicação. A modularização dos processos que compõe uma aplicação é característica fortemente presente e recomendada no desenvolvimento de aplicações. A criação de procedimentos e funções armazenadas no banco de dados provê uma forma de modularização alternativa àquelas presentes nos ambientes de desenvolvimento, tais como o Oracle Forms. Segurança e integridade de dados estabelecida pelo procedimento ou função, e não diretamente pelos objetos referenciados pelo procedimento. O controle de acesso às tabelas e outros objetos do banco pode ser feito através da distribuição de acessos aos procedimentos e funções, ao invés da distribuição de acessos às tabelas diretamente. Dessa forma, um usuário poderá inserir um registro em uma tabela, por exemplo, se tiver acesso à procedure que executa tal ação, porém não poderá fazê-lo diretamente através de um comando INSERT. Note que, para executar um procedimento ou função que execute ações sobre objetos do banco, o usuário não necessita ter privilégios sobre tais objetos. Ele necessita apenas de privilégio de execução sobre o procedimento ou função. Além disso, é possível garantir que ações relacionadas sejam executadas sempre em conjunto, fazendo com que os procedimentos executem mais de uma ação. Maior desempenho. Procedimentos e funções armazenados no banco de dados possuem melhor desempenho que aqueles armazenados em scripts ou mesmo em módulos Oracle Forms. Uma das razões se deve ao fato do processo de parsing (análise sintática) dos comandos PL/SQL serem executado no momento da compilação, e não no momento da execução, como ocorre com scripts. Em segundo lugar, permite que usuários façam uso da área de memória compartilhada do servidor Oracle, que armazena código SQL, visto que vários usuários executam o mesmo bloco de código. Em terceiro lugar, estando os procedimentos e funções no banco, evita-se tráfego na rede e diminui-se o número de chamadas feitas ao servidor. Manutenção de um único código. Com apenas um bloco de código utilizado por vários usuários e, possivelmente, por vários módulos de uma aplicação ou aplicações, o processo de manutenção se torna mais fácil. Outra característica é a possibilidadede ser feita a manutenção de um procedimento sem interferir com a utilização deste por outros usuários. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 14 Diferenças entre Subprogramas de Banco de Dados e Subprogramas de Aplicação Subprogramas de Banco de Dados Subprogramas de Aplicação Armazenados no banco de dados Oracle. Armazenados nos módulos Oracle Forms, Oracle Reports, e Oracle Graphics da aplicação. Documentados no dicionário de dados Oracle. Documentados nos módulos da aplicação. Executados a partir de qualquer ambiente que acesse o banco de dados Oracle. Executados a partir dos módulos da aplicação. Podem referenciar outros subprogramas de banco de dados. Podem referenciar subprogramas da aplicação e subprogramas de banco de dados. Disponível para aplicações a partir de comandos de controle (DCL) de segurança do banco de dados. Disponível para aplicações por meio de segurança ao nível do módulo na aplicação. Tabela 1-1: Diferenças entre subprogramas de banco de dados e subprogramas de aplicação Nota: No decorrer do curso, os termos procedimento ou procedure serão utilizados genericamente para descrever procedimentos ou funções de banco de dados, para simplificação. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 15 Conectando com o SQL*Plus A partir do ambiente Windows Exemplo: Utilizando Oracle 10g Client for Windows na versão 10g Figura 2-4: Conectando com o SQL* Plus A forma como você executa o SQL*Plus depende do tipo de sistema operacional ou ambiente Windows que você está executando. Para conectar através do ambiente Windows através do Menu: 1. Clique Iniciar—>Programas—>Oracle - OracleClient11g_home1 — >Desenvolvimento de Aplicações —>SQL Plus. 2. Preencha o nome do usuário, senha e o string de conexão com o banco de dados. A partir da linha de comando Para conectar em um ambiente de linha de comando: 1º. Conecte com sua máquina. 2º. Entre o comando SQL*Plus como mostrado na figura acima. No comando: nomeusuario: é o nome do seu usuário no banco de dados. senha: é sua senha no banco de dados; se você colocar sua senha na linha de comando, ela estará visível. @database: é a string de conexão para o banco de dados. sqlplus [nomeusuario[/senha[@database]]] Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 16 Exemplo na linha de comando utilizando o Oracle 11g: Nota: Para assegurar a integridade de sua senha, não coloque-a no prompt do sistema operacional. Ao contrário, coloque apenas seu nome de usuário. Entre com sua senha somente no prompt de senha do SQL*Plus. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 17 Conectando com o SQLDeveloper Criando uma conexão no SQL Developer Selecione a entrada Connections, efetue um clique com o botão direito do mouse e selecione New Connection. Exemplo: Connection Type TNS Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 18 Exemplo: Connection Type Basic Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 19 Propriedades da conexão no SQLDeveloper Propriedade Descrição Connection Name Nome de identificação da sua conexão Username Usuário do banco de dados Password Senha do usuário do banco de dados Role Defaul = Conexão normal SYSDBA = Conexão utilizando privilégio especial SYSDBA Connection Type TNS = utilizando definições do arquivo TNSNAMES.ORA BASIC = Definições da conexão diretamente no SQLDeveloper sem utilizar o TNSNAMES.ORA Network Alias (Para connection type = TNS) STRING de Conexão com o banco de dados definido no arquivo TNSNAMES.ORA Hostname Nome do servidor ou endereço IP do servidor Port Porta utilizada pelo Listener SID ID da instancia do banco de dados Service Name Nome do Serviço Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 20 Utilizando o SQLDeveloper Executando um comando SQL no SQLDeveloper 1. Digite o comando SQL. 2. Posicione o cursor no comando ou selecione o comando. 3. Efetue um clique no botão Execute Statement da toobar ou a tecla F9. Executando um Script de comandos SQLPLUS, SQL e PL/SQL no SQLDeveloper 1. Digite o script comandos SQL e SQLPLUS. 2. Posicione o cursor na área de trabalho comando ou selecione o script a ser executado. 3. Efetue um clique no botão Run Script da toobar ou a tecla F5. Utilizando o Histórico de Comandos no SQLDeveloper 1. Efetue um clique no botão SQL History ou a tecla F5. 2. Selecione o comando do histórico. 3. Selecione a opção desejada: APPEND ou REPLACE. 4. APPEND adiciona o comando selecionado na área de trabalho. REPLACE substitui o conteúdo da área de trabalho pelo comando selecionado. Voltando e Avançando Comandos no SQLDeveloper utilizando teclas 1. Pressione as Teclas CTRL + Shift + Down para voltar comandos anteriores. 2. Pressione as Teclas CTRL + Shift + Up para avançar comandos. Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 21 Criando Procedimentos e Funções de Banco de Dados Procedimentos e funções de banco de dados são considerados objetos do banco assim como tabelas, sinônimos, visões, etc. Dessa forma podemos criar procedures do mesmo jeito que criamos estes outros objetos, através do SQL*Plus. Exemplo: Criando uma procedure via SQL Plus: Figura 1-5: Exemplo de criação de procedimento CREATE OR REPLACE PROCEDURE aumenta_preco_curso IS BEGIN UPDATE tcursos SET preco = preco * 1.1; END; / Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 22 Executando Subprogramas O método de execução de subprogramas depende do ambiente a partir do qual ele está sendo executado. No SQL Plus, dispara-se um procedimento ou função através do comando EXECUTE. A partir de outro bloco PL/SQL, seja em um módulo Oracle Forms, Oracle Reports, ou outro subprograma, devem ser feitas chamadas diretas ao nome do subprograma. Executando um procedimento a partir do SQL Plus: Figura 1-6: Exemplo de execução de procedimento SQL> execute aumenta_preco_curso Introdução ao PL/SQL TargetTrust Treinamento e Tecnologia 23 Espaço para anotações Oracle 12c Fundamentals II Linguagem PL/SQL TargetTrust Treinamento e Tecnologia 24 Construindo Blocos Anônimos e Definindo Identificadores Construindo Blocos Anônimos e Definindo Identificadores TargetTrust Treinamento e Tecnologia 25 Objetivos Reconhecer um bloco PL/SQL básico e suas seções; Entender o significado de identificadores (variáveis e constantes) em PL/SQL; Declarar identificadores (variáveis e constantes) em PL/SQL; Descrever as regras do alinhamento de blocos; Executar e testar um bloco PL/SQL; Utilizar convenções de codificação. Construindo Blocos Anônimos e Definindo Identificadores TargetTrust Treinamento e Tecnologia 26 Estrutura de um Bloco PL/SQL Figura 2-1: Estrutura de um bloco PL/SQL PL/SQL é uma linguagem de blocos estruturados, significando que programas podem ser divididos em blocos lógicos. Um bloco PL/SQL é composto de até três seções: declarativa (opcional), executável (obrigatória) e de tratamento de exceções (opcional). Somente as palavras chaves BEGIN e END são obrigatórias. Você pode declarar variáveis locais no bloco que as utiliza. Condições de erro (conhecidas como exceções) podem ser tratadas especificamente dentro do bloco para a qual elas se aplicam. Você pode armazenar e modificar valores dentro de um bloco PL/SQL declarando e referenciando variáveis e outros identificadores. A tabela abaixo descreve as três seções de bloco. Seção Descrição Inclusão Declarativa Contém a declaração de variáveis, constantes, cursores e exceções
Compartilhar