CasadoCódigo Prefácio Sempregosteidelivrosque“conversam”comoleitor,principalmenteostéc- nicos. Comosefosseumatrocadeideiasentreoautoreapessoaqueoestá lendo.Procureiescreverdestaforma,poiscreioquecomissoaleiturasetorna maisclaraeamigável, umbatepapoentreamigos, conversandosobrequal- querassunto. Aintençãofoicolocaraquitudooquevocêvaiprecisarsaber sobre a linguagem PL/SQL. O livro aborda conceitos que são utilizados no diaadiadodesenvolvimentoeanálisedesistemasparabancodedadosOra- cle. Demonstreidiversosexemplos,quetambémvãoajudá-locomofontede referênciaparaconsultadecomandosecomoutilizá-los. Tenhaumaotima leitura! Públicoalvo EstelivrosedestinaainicianteseexperientesnalinguagemPL/SQL.Para osiniciantes,sãoabordadosconceitossobreaestruturadalinguagemPL/SQL e suas características. Ele ensina como criar programas, dos mais simples atéosmaiscomplexos,paraatenderàsmaisdiversasnecessidades. Vocêvai aprendercomoestalinguagempodetrazerumaltograudeprodutividadee performanceparaassuasaplicações.Paraosjáexperientes,eleajudarácomo fontedereferênciaepararelembrarconceitosetécnicasdalinguagem. Comoolivroestádividido? Primeiramente,sãoabordadosconceitosbásicosdalinguagem,comosua definição,comoéestruturadaeseusprincipaiscomponentes.Logoapós,en- tramosafundonasuaestrutura,conhecendocadacomandoecadacompo- i CasadoCódigo nentequeelautiliza,explorandosuasfuncionalidadesepropriedades. Oli- vrotambémmostracomoincorporaralinguagemSQLdentrodosprogramas escritos em PL/SQL, para a recuperação e manipulação de dados. Tipos de dados,estruturascondicionaisederepetição,armazenamentodeprogramas atravésdeprocedures(procedimentos)efunctions(funções),emodularização atravésdepackages(pacotes)sãoalgunsdositensmostrados. Todaessaabordagemérealizadanoâmbitoprático,ondesãodetalhadas suasestruturas,seuscomandosecaracterísticas,sempreatravésdeexemplos práticosedefácilcompreensão. Os scripts de base (tabelas e dados) e fontes para a execução dos exemplos do livro estão disponíveis no endereco https://github.com/ eduardogoncalvesbr/livroplsql-casadocodigo. Confiraoutrasobrasdoautor SQL: Uma abordagem para bancos de dados Oracle http://www. casadocodigo.com.br/products/livro-sql-oracle Contatos Para falar com o autor, envie e-mail para eduardogoncal- [email protected] Página no facebook: https://www.facebook.com/ eduardogoncalvesescritor ii CasadoCódigo Sobre o autor FormadoemTecnologiadaInformação,possuimaisde10anosdeexperiên- cia em análise e desenvolvimento de sistema voltados a tecnologias Oracle, trabalhando por grandes empresas como Lojas Renner, Mundial S.A, Tigre S.A,Pernambucanas,TractebelEnergia,PortobelloCeramica,Bematech,en- treoutros. EduardoGonçalvezéinstrutordecursosoficiaisdaOracle–nas linguagensSQLePL/SQL.Tambématuanodesenvolvimentodeaplicações mobileparaaplataformaiOS.Atualmente,atuacomoCoordenadordeOpe- raçõesnaSuperoTecnologia. iii CasadoCódigo Agradecimentos Dedicoestelivroatodasosmeusamigosecolegasdaáreadetecnologia,bem comomeusprofessoresementores,quetiveoprazerdeconhecer,ecomos quaisaprendieatémesmotrabalhei. Duranteestaminhatrajetóriaaprendi muitas coisas com estas pessoas extraordinárias. Trocamos experiências e aprendemosmuito, unscomosoutros, programandoequebrandoacabeça paraescreveralinhadecódigoqueajudasseasolucionarumproblema(re- gadoamuitocafé,namaioriadasvezes... risos),ouatémesmo,apenaspela paixãodeprogramar.Saibamquecadaumdevocêscontribuiuimensamente paraestetrabalho!Semvocêsistonãoseriapossível!Ficamaquimeussince- rosagradecimentos! v CasadoCódigo Sumário Sumário 1 PL/SQL 1 1.1 OqueéPL/SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.2 PorqueaprenderPL/SQL? . . . . . . . . . . . . . . . . . . . . 2 1.3 SQL,SQL*Plus,PL/SQL:Qualédiferença? . . . . . . . . . . 3 2 Programaçãoembloco 7 3 Primeirospassos 13 3.1 ComoiniciarnoPL/SQL . . . . . . . . . . . . . . . . . . . . . 13 4 Pacotedbms_output 19 4.1 Exceçõesparaopacotedbms_output . . . . . . . . . . . . . . 26 5 Variáveisbindedesubstituição 27 5.1 Variáveisbind . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 5.2 Variáveisdesubstituição . . . . . . . . . . . . . . . . . . . . . 31 5.3 Utilizandovariáveisemarquivos . . . . . . . . . . . . . . . . 35 6 AspectosiniciaisdaprogramaçãoPL/SQL 43 6.1 Caractereseoperadores . . . . . . . . . . . . . . . . . . . . . . 44 6.2 Identificadores . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 6.3 Transações . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 6.4 TransaçõesemPL/SQL . . . . . . . . . . . . . . . . . . . . . . 51 6.5 Trabalhandocomvariáveiseconstantes . . . . . . . . . . . . 52 6.6 TiposdedadosemPL/SQL . . . . . . . . . . . . . . . . . . . . 53 vii Sumário CasadoCódigo 7 Exceções 59 7.1 Exceçõespredefinidas . . . . . . . . . . . . . . . . . . . . . . . 60 7.2 Exceçõesdefinidaspelousuário . . . . . . . . . . . . . . . . . 79 8 Estruturasdecondição:if 85 8.1 Estruturasdocomandoif-endif . . . . . . . . . . . . . . . . . 86 8.2 Estruturasdocomandoif-else-endif . . . . . . . . . . . . . . 87 8.3 Estruturasdocomandoif-elsif(-else)-endif . . . . . . . . . . 89 8.4 Formatandoasdeclaraçõesif . . . . . . . . . . . . . . . . . . . 93 8.5 Evitandoerroscomunsnousodeif . . . . . . . . . . . . . . . 94 9 Comandosderepetição 95 9.1 forloop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 9.2 whileloop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 9.3 loop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 9.4 Qualloopdeve-seusar? . . . . . . . . . . . . . . . . . . . . . . 105 10 Cursores 107 10.1 Cursoresexplícitos. . . . . . . . . . . . . . . . . . . . . . . . . 108 10.3 Cursorforloopcomdefiniçãointerna . . . . . . . . . . . . . 118 10.4 Cursoresimplícitos . . . . . . . . . . . . . . . . . . . . . . . . 119 10.5 Atributosdecursorexplícitoeimplícito . . . . . . . . . . . . 121 10.6 Cursoresencadeados . . . . . . . . . . . . . . . . . . . . . . . 128 10.7 Cursorcomforupdate . . . . . . . . . . . . . . . . . . . . . . 130 11 Funçõesdecaractereseoperadoresaritméticos 143 11.1 Funçõesdecaracteres . . . . . . . . . . . . . . . . . . . . . . . 144 11.2 Funçõesdecálculos . . . . . . . . . . . . . . . . . . . . . . . . 149 11.3 Operadoresaritméticos . . . . . . . . . . . . . . . . . . . . . . 154 12 Funçõesdeagregação(grupo) 159 13 Funçõesdedata 177 viii CasadoCódigo Sumário 14 Funçõesdeconversão 183 14.1 to_date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 14.2 to_number . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 14.3 to_char . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204 15 Funçõescondicionais 211 15.1 decodevs.case . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 16 Programasarmazenados 225 16.1 proceduresefunctions . . . . . . . . . . . . . . . . . . . . . . 226 16.2 Usodocomandoreplace . . . . . . . . . . . . . . . . . . . . . 236 16.3 Recompilandoprogramasarmazenados . . . . . . . . . . . . 238 16.4 Recuperandoinformações . . . . . . . . . . . . . . . . . . . . 238 16.5 Recuperandocódigos . . . . . . . . . . . . . . . . . . . . . . . 239 16.6 Visualizandoerrosdecompilação . . . . . . . . . . . . . . . . 240 16.7 Passandoparâmetros . . . . . . . . . . . . . . . . . . . . . . . 243 16.8 Dependênciadeobjetos . . . . . . . . . . . . . . . . . . . . . . 249 17 packages 265 17.1 Estruturadeumpackage . . . . . . . . . . . . . . . . . . . . . 266 17.2 Acessoapackages . . . . . . . . . . . . . . . . . . . . . . . . . 270 17.3 Recompilandopackages. . . . . . . . . . . . . . . . . . . . . . 278 17.4 Recuperandoinformações . . . . . . . . . . . . . . . . . . . . 278 17.5 Recuperandocódigos . . . . . . . . . . . . . . . . . . . . . . . 279 17.6 Visualizandoerrosdecompilação . . . . . . . . . . . . . . . . 280 18 Transaçõesautônomas 283 19 Triggers 293 19.1 Triggerdebancodedados . . . . . . . . . . . . . . . . . . . . 294 19.2 Triggerdetabela . . . . . . . . . . . . . . . . . . . . . . . . . . 294 19.3 Triggerdelinha . . . . . . . . . . . . . . . . . . . . . . . . . . 301 19.4 Mutantetable . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316 19.5 Triggerdesistema . . . . . . . . . . . . . . . . . . . . . . . . . 324 19.6 Triggerdeview . . . . . . . . . . . . . . . . . . . . . . . . . . . 330 ix