PL_SQL Parte II

7/24/2019 PL_SQL Parte II

1/58

PL/SQL-PL/SQL-

FundamentosFundamentos

Oracle Database 11g

1

Instrutor: Mrcio Mendona


2/58

Tratamento deTratamento deExcessesExcesses

ContinuaoContinuao

Oracle Database 11g

2



3/58

Excees Definidas peloExcees Definidas pelo

usuriousurio

O que em erro denota no enecessariamente um erro Oracle por

exemplo, ele pode ser um erro com osdados. or outro lado, as exce!esprede"nidas correspondem aos erroscomuns #$% e da %$%.

'xce!es de"nidas pelo usurio sodeclaradas na seo declarati(a de umbloco %$%. Da mesma )orma como as

(ari(eis, as exce!es tem um tipo*'+'-IO/ e um escopoOracle Database 11g3



4/58

Excees Definidas pelo UsurioExcees Definidas pelo Usurio

Exemplo:

DECLARE

registro funcionarios%ROWTYPE;

Exemplo EXCEPTO!;"E#!

$ELECT

!TO registro

&RO' funcionarios

W(ERE co)*funcionario + , ;

& registro-co)*funcionario ./ 0 T(E! RA$E Exemplo;

E!D &;

EXCEPTO!

W(E! Exemplo

T(E! D"'$*O1TP1T-P1T*L!E23&1!CO!4RO ERRADO35;

E!D;Oracle Database 11g

4



5/58

Tratamento de ExceesTratamento de Excees

D'%0'e2-ooMan3#tudents '+'-IO4

e2-ooMan3#tudents 5 um identi"cador queestar (is6(el at5 o "nal desse bloco.

Obser(e que o escopo de uma exceo 5 o

mesmo que o escopo de qualquer outra(ari(el ou cursor na mesma seodeclarati(a.

Oracle Database 11g

5



6/58

Levantando ExceesLevantando Excees

$uando o erro associado com uma exceoocorrer, a exceo 5 le(antada. 'xce!esde"nidas pelo usurio so le(antadasexplicitamente (ia instruo 0I#', enquantoas exce!es prede"nidas so le(antadasimplicitamente quando seus erros associadoscom Oracle ocorrem. #e ocorrer um erroOracle que no este7a associado com umaexceo, uma exceo tamb5m e le(antada.

Oracle Database 11g

6



7/58

Tratamento de ErrosTratamento de Erros

D'%0' e2'xception1 '+'-IO4

8'9I aise 7ust exception 1. 0I#' e2'xception14'+'-IO ;


8/58

UtilizandoUtilizandoRAISEA!!LI"ATI#$ERR#RRAISEA!!LI"ATI#$ERR#R

?oc@ pode utiliAar a )uno prede"nida0I#'20%I0-IO2'O para criar suas prBpriasmensagens de erro, que podem ser mais descriti(as que

as exce!es identi"cadas. Os erros de"nidos pelousurio so passados para o bloco da mesma maneiracomo os erros do Oracle so passados para o ambientede cCamada.

0 sintaxe 5:0I#'20%I0-IO2'O *numero2do2erro,mensagem2do2erro,manter2erros/4

Onde numero2do2erro e o (alor entre EF.FFF e EF.GGG,

mensagem2do2erro 5 o texto associado, e manter2erros5 um (alor booleano.Oracle Database 11g8



9/58

DECLARE

preco !1'"ER267,5;

"E#! $ELECT Preco*1nitario

!TO preco

&RO' Pro)utos

W(ERE Co)*Pro)uto + 0880;

EXCEPTO!

W(E! ot9ers T(E!

RA$E*APPLCATO!*ERROR2:,88807 Exceo no definid pelo p!o"!mdo!#$E!D;

UtilizandoUtilizandoRAISEA!!LI"ATI#$ERR#RRAISEA!!LI"ATI#$ERR#R

Oracle Database 11g

%



10/58

SQL CursorSQL Cursor

Oracle Database 11g

1&



11/58

ursores so ponteiros de uma rea pri(ada de memBriaalocada no ser(idor Oralce.

'xistem dois tipos de cursores:Implcitoscriado e gerenciado internamente peloprocessador de instruo #$% no ser(idor Oracle. Oprogramador no tem acesso a eles. O ser(idor Oracle oscria quando uma instruo #$% e executada.

Explcitosexplicitamente declarado pelo programador.O programador pode selecionar (rias linCas de umatabela do banco de dados, tendo o controle de cada linCaretornada. 'stes cursores so declarados na seo

declarati(a do bloco. Mais adiante (eremos este tipo decursor mais detalCadamente.

Defini%oDefini%o

Oracle Database 11g

11



12/58

#$%HOJD: 0tributo booleano que retorna -J' caso a maisrecente instruo #$% tenCa retornado pelo menos uma linCa4

#$%HO-OJD: 0tributo booleano que retorna -J' caso a maisrecente instruo #$% no tenCa retornado pelo menos uma linCa4

#$%HO;OJ-: 0tributo num5rico que retorna o nKmero de linCa

a)etadas na mais recente instruo #$%.

Atri&utos dos "ursoresAtri&utos dos "ursores

Impl'citosImpl'citos

Oracle Database 11g

12



13/58

D'%0' qtde2alunos JM8'*L,E/4

retorno JM8'*1F/48'9I #'%'- count*/ I-O qtde2alunos OM alunos4 retorno :N sqlHrocount4

dbms2output.put2line *P -otal de linCas: PQQretorno/4'D4

Exemplo "ursor Impl'citoExemplo "ursor Impl'cito

Oracle Database 11g

13



14/58

Estruturas deEstruturas deControleControle

Oracle Database 11g

14



15/58

Els podem se! combinds de ulue! mnei!necess(!i p! !esol)e! um p!oblem.

* +,-/, d( o usu(!io !ecu!sos p! cont!ol! ofluxo de comndos de um p!o"!m condicionlou no.

Estruturas de "ontroleEstruturas de "ontrole

Oracle Database 11g

15



16/58

# "omando I(# "omando I(

ermite executar uma sequ@ncia de comandoscondicionalmente, isto 5, a sequ@ncia 5 executada ouno dependendo do (alor da condio.


17/58

Loops e DesviosLoops e Desvios

IncondicionaisIncondicionais

* comndo ,**+ pe!mite execut! um seu0nci decomndos mltipls )ees. ( t!0s fo!ms de loop

,**+ ,E ,**+

9*: ,**+

Oracle Database 11g

17



18/58

# "omando L##!# "omando L##!

0 )orma mais simples do %OO 5 estrutura %OO 'D%OO.

0 cada iterao do %OO a sequ@ncia 5 executada e ocontrole retorna ao %OO anterior correspondente.

$uando o comando '+I- 5 encontrado, o %OO termina e ocontrole 5 passado para o prBximo comando apBs o 'D

%OO.

Oracle Database 11g

18



19/58

# "omando L##!# "omando L##!

Exemplo:

:: Este loco calcula o fatorial )e um )a)o n


20/58

# "omando )*ILE L##!# "omando )*ILE L##!

;ssoci um condio um seu0nci de comndosdent!o de um est!utu! ,**+ E )lid se fo!)e!ddei! seu0nci > executd.

e fo! fls os comndos do ,**+ no so executdos e

o cont!ole > pssdo p! o p!?ximo comndo.

Oracle Database 11g

2&



21/58

# "omando (#R L##!# "omando (#R L##!

* comndo 9*: ,**+ > usdo com o mesmo tipo deest!utu! do ,**+ simples com dife!en de pode!mosdete!min! unts )ees ite!o oco!!e!( especificndoum limite de )lo!es.

Sintaxe:

&OR conta)or ! menor*?alor -- maior*?alor LOOP

---

---E!D LOOP;

* limite > )lido undo o 9*: ,**+

come e nunc > )lido no)mente.

Oracle Database 11g

21



22/58

# "omando (#R L##!# "omando (#R L##!

Exemplo:

:: Este loco mostra o ?alor )o conta)or a ca)a itera@o

DECLARE

conta)or !1'"ER;

"E#!

&OR conta)or ! 0 -- 6 LOOP

D"'$*O1TP1T-P1T*L!E2conta)or5;

E!D LOOP;E!D;

Oracle Database 11g

22



23/58

# "omando "ASE# "omando "ASE

O case 5 um comando que retorna um resultadobaseado em uma ou mais alternati(as.

Declare condicao cCar*E/ :N #4 desc2condicao (arcCarE*1FF/48egin condicao :N case condicao

Cen # tCen #im Cen tCen o else o Declarado end4

'nd4Oracle Database 11g

23



24/58

# "omando "ASE# "omando "ASE

O comando 0#' tamb5m pode ser utiliAado pararetornar um (alor num determinado #'%'-.createtableteste @)1 varchar2@2# )2 number )3 number#$

insertintoteste values@';' 1 2#$insertintoteste values@';' 1 3#$insertintoteste values@';' 2 1#$

insertintoteste values@'A' 2 1#$insertintoteste values@'B' 2 1#$

/*declare

cursor c_teste is*/

select)1)2)3 casewhen)1 C ';' then';luno' when)1 C 'A' then'+!ofesso!' else '


25/58

Desvio Incondicional +#T#Desvio Incondicional +#T#

=es)i o fluxo de um p!o"!m incondicionlmente p! umlbel.

* lbel de)e se! nico dent!o do escopo e de)e )i! ntes deum comndo execut()el ou de um bloco.

D** lbel$

Oracle Database 11g

25



26/58

Desvio Incondicional +#T#Desvio Incondicional +#T#

D'%0' (2ounter 8I0T2I-'9' :N 148'9I %OO I#'- I-O temp2table

?0%J'# *(2ounter, >%oop count>/4 (2ounter :N (2ounter U 14 I (2ounter VN LF -


27/58

Rotulando LoopsRotulando Loops

Os %oops podem ser rotulados. odemos rotular um loop e utiliAlos em uma instruo '+I- para indicar qual loop de(e serterminado.

AED feit t!)>s dotipo I:*J+E bsedo em um tbel ou )ieK.

Oracle Database 11g

28



29/58

Re,istros em !L-S.LRe,istros em !L-S.L

*s cmpos dent!o do !e"ist!o te!o o nome e o tipo de ddos i"uis os dtbel ou )ieK !efe!encid n decl!o do !e"ist!o.

identificdo! !efe!0nciI:*J+E$

* identificdo! > o nome do !e"ist!o ue est( sendo decl!do$; !efe!0nci > o nome d tbel )ieK ou cu!so! !efe!encido.

Exemplo:

DECLAREReg*pro) Pro)utos%ROWTYPE;

"E#!

$ELECT !TO reg*pro)

&RO' Pro)utos

W(ERE Co)*Pro)uto + 0;

E!D;

Oracle Database 11g

2%



30/58

CursoresCursoresExplcitosExplcitos

Oracle Database 11g

3&



31/58

"ursores Expl'citos"ursores Expl'citos

+odem se! decl!dos em ulue! p!te decl!ti)@ p?s um=EB,;:E# de um bloco +,-/, subp!o"!m ou pcote.

+odem !eto!n! nenHum um ou )(!is linHs.

*s comndos se"ui! so destindos L mnipulo doscu!so!es.

Oracle Database 11g

31



32/58

"omando "URS#R"omando "URS#R

Msdo p! decl!! um cu!so! explNcito. +!0met!os podem se!definidos p! pe!miti! substituio de )lo!es dent!o d ue!Oundo o cu!so! > be!to.

intxe

:C1R$OR nome*)o*cursor B2parmetro*0 tipo*0 B>+expresso*)efault*07 ---7 parmetro*n tipo*n B>+expresso*)efault*n5 $

1ERY-

*nde /ME:J > l"o como$ELECT coluna*a7---7 coluna*F

&RO' taela*a7---7 taela*F

W(ERE con)i@o-Oracle Database 11g

32



33/58

"omando #!E$"omando #!E$

Msdo dent!o do bloco +,-/,. 9 seleo definid n decl!odo cu!so! e pont p! p!imei! linH !eto!nd pel ue!O uedefine o cu!so!.

Sintaxe:OPE! nome*cursor B2 argumento*07---7argumento*n5

Oracle Database 11g

33



34/58

"omando (ET"*"omando (ET"*

Mtilido p! t!e! linH co!!ente pontd pelo cu!so! em)!i()eis +,-/, ou )!i()eis de p!o"!m.

intxe

&ETC( nome*)o*cursor !TO ?ariG?el*07---7?ariG?el*n;

*nde p! cd colun d ue!O ssocid o cu!so! de)eH)e! um )!i()el co!!espondente em )!i()el_1...)!i()el_n.

Oracle Database 11g

34



35/58

De(ese necessariamente dar O' nocursor antes de utiliAlo40pBs se dar o O' no cursor, o '-< traA a

1W linCa do con7unto associado ao cursor.0 cada '-< incrementase a linCa corrente

do cursor para a prBxima.0s (ari(eis usadas no comando (o conter

(alores nulos caso a quer3 do cursor noretorne nenCuma linCa.

So&re o comando (ET"*So&re o comando (ET"*

Oracle Database 11g

35



36/58

"omando "L#SE"omando "L#SE

9ecH explicitmente o cu!so! pe!mitindo ue ele seP !ebe!to p!t!e! no)mente os ddos !eto!ndos po! su ue!O.

Sintaxe:

CLO$E nome*)o*cursor

Oracle Database 11g

36



37/58

Atri&utos dos "ursores Expl'citosAtri&utos dos "ursores Expl'citos

Mm cu!so! explNcito possui um conPunto de )!i()eis ue podem se!usds p! defini! seu sttus.

o els

;# 9ound cont>m o )lo! :ME se o ltimo 9EB do cu!so! obte)eum linH d (!e de ddos. =o cont!(!io !eto!n 9;,E.

Sintaxe:

!ome*)o*cursor%&O1!D

Oracle Database 11g

37



38/58

A# m o )lo! :ME se o ltimo 9EB no ti)e! t!idoum linH do cu!so! e 9;,E cso cont!(!io.

Sintaxe:

!ome*)o*cursor%!OT&O1!D

B# :oKBount Bont>m o nme!o de linHs ue P( fo!m t!ids pelocomndo 9EB.

Sintaxe:

!ome*)o*cursor%ROWCO1!T


Oracle Database 11g

38



39/58

=# s*pen Bont>m o )lo! :ME se o cu!so! est( co!!entementebe!to. 9;,E se o cu!so! foi fecHdo ou ind no foi be!to.

Sintaxe:

!ome*)o*Cursor%$OPE!


Oracle Database 11g

3%



40/58

JtiliAase uma estrutura de %OO para aleitura de cada uma de suas linCas.#e um '-< )or executado antes da

abertura ou apBs o )ecCamento de umcursor, a seguinte mensagem ser exibida:O01FFE: etcC out o) sequence.X importante testar se o '-< )oi bem

sucedido apBs cada interao da estruturade %OO, atra(5s da (ari(el HO-OJD.

"ontrolando a Leitura de um "ursor"ontrolando a Leitura de um "ursor

Oracle Database 11g

4&



41/58

Exemplo 1:

:: Este loco imprime os ?Grios cH)igos )e funcionGrios

DECLARE

C1R$OR func $

$ELECT co)*funcionario &RO' funcionarios;

?*co)*func funcionarios-co)*funcionario%TYPE;

"E#!

OPE! func;

LOOP

&ETC( func !TO ?*co)*func;

EXT W(E! func%!OT&O1!D;

D"'$*O1TP1T-P1T*L!E2?*co)*func5;

E!D LOOP;

CLO$E func;

E!D;

Uso dos "ursoresUso dos "ursores

Oracle Database 11g

41



42/58

Exemplo 2: Este exemplo utiliza a estrutura WHILELOOP

'ste bloco imprime os (rios cBdigos de )uncionriosD'%0'J#O )unc I#

#'%'- cod2)uncionario OM )uncionarios4(2cod2)unc )uncionarios.cod2)uncionarioH-T'48'9IO' )unc4 '-< )unc I-O (2cod2)unc4

;


43/58

Exemplo 3: Este exemplo utiliza a estrutura FORLOOP

'ste bloco imprime os (rios cBdigos de )uncionriosD'%0'J#O )unc I#

#'%'- cod2)uncionario OM )uncionarios4(2cod2)unc8'9IO (2cod2)unc in )unc loop D8M#2OJ-J-.J-2%I'*(2cod2)unc/4

'D %OO4'D4

Uso dos "ursoresUso dos "ursores

Oracle Database 11g

43



44/58

Atri&utos de "ursoresAtri&utos de "ursores

Expl'citosExpl'citos

0ssim como os cursores impl6citos, existe atributosque permitem identi"car o status do cursor apBs suaexecuo.

Mas a )orma de utiliAlo muda pois o atributo de(eestar associado diretamente com o nome do cursor

declarado.

'stes atributos so: Hisopen, Hnot)ound, H)ound,Hrocount.

Oracle Database 11g

44



45/58



Exemplo 1

=ecl!e

cu!so! c_luno is select codi"o f!om luno$Ae"in if not c_lunoIisopen tHen

open c_luno$end if$End$

Oracle Database 11g

45



46/58



Exemplo 2

Declare cursor c2aluno is select codigo

)rom aluno4

(2aluno aluno.codigoHt3pe4

8egin open c2aluno4

)etcC c2aluno into (2aluno4 Cile c2alunoH)ound loop dbms2output.put2line *(2aluno/4

fetcH c_luno into )_luno$ end loop4 close c2aluno

'nd4Oracle Database 11g

46



47/58

"ursores e Re,istros"ursores e Re,istros

Q possN)el fe! um ue!O c!!e"! um linH de um cu!so!di!etmente p! um !e"ist!o definido com est!utu! de um cu!so!

sendo os )lo!es ds linHs c!!e"dos di!etmente sob!e os cmposdo !e"ist!o.

Oracle Database 11g

47



48/58

"ursores e Re,istros"ursores e Re,istros

Exemplo:

:: Este loco imprime os )a)os )os funcionGrios com oIeti?o )e ?en)a maior Jue K888

DECLARE

C1R$OR func $

$ELECT

&RO' &1!CO!ARO$

W(ERE oIeti?o*?en)a / K888;

reg*func func%ROWTYPE;

"E#!

OPE! func;

LOOP

&ETC( func !TO reg*func; EXT W(E! func%!OT&O1!D;

D"'$*O1TP1T-P1T*L!E2reg*func-nome5;

D"'$*O1TP1T-P1T*L!E2reg*func-salario5;

D"'$*O1TP1T-P1T*L!E2reg*func-oIeti?o*?en)a5;

D"'$*O1TP1T-P1T*L!E2reg*func-ramal5;

E!D LOOP;

CLO$E func;E!D;

Oracle Database 11g

48



49/58

"ursores (#R Loops"ursores (#R Loops

utilizando Su&/ueriesutilizando Su&/ueries

Jma outra maneira de implementar cursores 5

utiliAando diretamente uma subquerie nocomando O. or5m (oc@ no poder utiliAar osatributos de cursores, porque no Cou(e adeclarao do nome do cursor.

Oracle Database 11g

4%



50/58

"ursores (#R Loops"ursores (#R Loops

utilizando Su&/ueriesutilizando Su&/ueries

Exemplo

Ae"in

fo! )_luno in @ select codi"o nome f!om luno# loop dbms_output.put_line @)_luno.codi"oRR S RR)_luno.nome#$

end loop$End$

Oracle Database 11g

5&



51/58

"ursores !arametrizados"ursores !arametrizados

+!Tmet!os pe!mitem pss! )lo!es p! um cu!so! undo ele> be!to e usdo dent!o de su ue!O undo el > executd.sto si"nific ue um cu!so! pode se! be!to di)e!ss )eesdent!o de um bloco !eto!nndo um conPunto dife!ente de linHs cd )e ue > be!to.

*s p!Tmet!os so pssdos posicionlmente undo o cu!so!> be!to sendo !efe!encidos dent!o d ue!O ue compUe o

cu!so! e t!tdos como um )!i()el +,-/,.

Oracle Database 11g

51



52/58

Exemplo:

:: Este loco imprime o nome e o salario )os funcionarios com

:: salario maior Jue um certo ?alor a ser entra)o

DECLARE

C1R$OR C0 2p0 !1'"ER5 $

$ELECT &RO' funcionarios

W(ERE salario / p0;

?*c0 C0%ROWTYPE;

?*aux !1'"ER;

"E#!

OPE! C028885;

LOOP

&ETC( C0 !TO ?*c0;

EXT W(E! C0%!OT&O1!D;

D"'$*O1TP1T-P1T*L!E2?*c0-nome5;

D"'$*O1TP1T-P1T*L!E2?*c0-salario5;

E!D LOOP;

CLO$E C0;E!D;

"ursores !arametrizados"ursores !arametrizados

Oracle Database 11g

52



53/58

"lusula (#R U!DATE"lusula (#R U!DATE

Bomo existem muits sessUes num simples bnco de ddosexiste possibilidde de um dete!mindd linH se! lte!ddepois do cu!so! te! sido be!to. E )oc0 no )e!( est lte!o

enunto no !eb!i! o cu!so!.

+! !esol)e! est uesto usse cl(usul fo! updte ue i!(d! um locV ns linHs do cu!so!. =est mnei! nenHum out!sesso pode!( lte!! ou delet! os !e"ist!os ue fo!m!eto!ndos do cu!so!.

Oracle Database 11g

53



54/58


#intaxe:#'%'-Y OMYO JD0-'RO O%JM2''''SRO;0I-Q;0I-S4

Onde : column#re$erence 5 a coluna da consulta quedeterminar o controle de locZ*pode ser uma lista decolunas/

no(aitretorna um erro Oracle se a linCa )orlocZada em outra sesso.

O O JD0-' 5 a Kltima clusula do #'%'-. De(e (irdepois do order b3 *caso exista/.

Oracle Database 11g

54



55/58


declare

cursorc_teste is select)1)2)3 fromteste forupdateof)3$begin

...end$

Oracle Database 11g55



56/58

"lusula 01ere current of"lusula 01ere current of

Est cl(usul > usd em conPunto com fo! updte p! se !efe!i! o!e"ist!o co!!ente do cu!so!. * KHe!e cu!!ent of pode se! usdo em updte edelete.

Exemplodeclare

cursorc_teste is select)1)2)3 fromteste forupdateof)3$begin

for)_teste inc_teste loop updateteste set)3 C )3 W 1 wherecurrentofc_teste$endloop$

end$




57/58

"ursor com Su&/ueries"ursor com Su&/ueries

Jm outro tipo de instruo #$% 5 a que permite que uma

estrutura de #'%'- tenCa na sua clusula OM umoutro #'%'-. 'sta subquerie )uncionar como uma basetemporria para o #'%'- principal.




58/58

"ursor com Su&/ueries"ursor com Su&/ueries

Exemplo

=ecl!e cu!so! c_luno is select .codi"o .nome p!of.desc!ico f!om lunos

@select codi"o desc!ico f!om p!ofissoes# p!of KHe!e .codi"o_p!ofisso C p!of.codi"o$Ae"in fo! )_luno in c_luno loop dbms_output.put_line @)_luno.codi"oRRXXRR)_luno.nome RRXXRR )_luno.desc!ico#$ end loop$

End$

Documents

PL_SQL Parte II