PDF de programación - Oracle PL/SQL

OraclePL/SQLMiguelRodríguezPenabadLaboratoriodeBasesdeDatosUniversidadedaCoruñaEstructuraBloquesanónimosDECLARE/*Seccióndeclarativa-variablesPL/SQL,tipos,cursores,subprogramaslocais*/BEGIN/*Secciónexecutable-ordesPL/SQL*/EXCEPTION/*Seccióndemanexodeexcepcións*/END;ExemplomínimoBEGINNULL;--Necesitamospolomenosunhasentencia.--NULLéunhasentenciaquenonfainadaEND;/OraclePL/SQL2/42 Exemplo1:VeradataactualISETSERVEROUTPUTONIDBMS_OUTPUT.PUT_LINEIAcabamosobloquecon.eexecutamos(RouRUN)ISeacabamoscon/,executadirectamenteSQL>BEGIN2DBMS_OUTPUT.PUT_LINE(SYSDATE);3END;4.SQL>R02/04/06ProcedimientoPL/SQLterminadocorrectamente.OraclePL/SQL3/42VariablesetiposTipospredenidosTiposdeSQLmáisBOOLEAN,BINARY_INTEGERDECLARENomeVARCHAR2(20);"Unhavariablecunnomerarísimo"NUMBER(1);ContadorBINARY_INTEGER;ProcesoFinalizadoBOOLEAN;Tiposdenidospolousuario(rexistros)DECLARETYPEt_RexistroEstudianteISRECORD(NomeVARCHAR2(20),Apelido1VARCHAR2(20),Apelido2VARCHAR2(20));variable_Estudiantet_RexistroEstudiante;OraclePL/SQL4/42 Variablesetipos(ii)Sintaxecompleta<nome_variable>[CONSTANT]<tipo>[NOTNULL][{:=|DEFAULT}<valorpordefecto>];DECLAREnum1NUMBER;fecha1DATE:=SYSDATE;num3NUMBERDEFAULT3;num4CONSTANTNUMBER:=4;num5NUMBERNOTNULLDEFAULT5;num6CONSTANTNUMBERNOTNULL:=6;Usarotipodoutravariable,dunatributooudunhatáboaDECLAREsalarioNUMBER(7,2);--ProblemasecambiaadefiniciónnatáboaMellor:DECLAREsalarioEMP.SAL%TYPE;soldosalario%TYPE;rEmpEMP%ROWTYPE;OraclePL/SQL5/42SecciónexecutableManipularvariablesIAsignación::=IDependendodotipo:IOperaciónsmatemáticas(+,-,*,/,...),concatenación(||)IFuncións(power,upper,length,...)Ietc.DECLAREnBINARY_INTEGER;stringVARCHAR(20);BEGINn:=3;DBMS_OUTPUT.PUT_LINE('nvale'||n);n:=power(2,3);DBMS_OUTPUT.PUT_LINE('nvale2elvadoa3:'||n);string:='UntExTo';DBMS_OUTPUT.PUT_LINE(string||'enmaiúsculasé'||upper(string));END;/nvale3nvale2elvadoa3:8UntExToenmaiúsculaséUNTEXTOOraclePL/SQL6/42 SQLenbloquesPL/SQLIPodemosincluirvariablesnasentenciaSQLIPodemosusarDMLIInsert,delete,updateIParaoSELECTteremosvarioscasosIOusodeDDLserádeformadistintaOraclePL/SQL7/42SQLenbloquesPL/SQL(ii)ExemploICreaunhatáboaPERSOA(IDPERSOA,NOME,IDADE)ICreaunbloquedecódigoparainsertardatosIDirectamenteILendoosdatosealmacenándoosnunrexistrocreatetablepersoa(idpersoachar(12)notnullprimarykey,nomechar(20),idadenumber(3));DECLARErPersoapersoa%ROWTYPE;BEGININSERTINTOPERSOA(IDPERSOA,NOME,IDADE)VALUES('01234567T','Carpanta',50);INSERTINTOPERSOA(IDPERSOA,NOME,IDADE)VALUES('3456789B','Sacarino',17);rPersoa.idpersoa:='12345678A';rPersoa.nome:='Matusalén';rPersoa.idade:=850;INSERTINTOPERSOAVALUESrPersoa;END;OraclePL/SQL8/42 SQLenbloquesPL/SQL(iii)ExemploIActualizaaidadedetodasaspersoas,engadindounanobeginUPDATEPERSOASETIDADE=IDADE+1;end;IBorraaspersoascunidenticadorqueacabeenTbeginDELETEFROMPERSOAWHEREIDPERSOALIKE'%T';end;OraclePL/SQL9/42SQLenbloquesPL/SQL(iv)SeleccióndedatosIUsodacláusulaSELECT...INTOIConvariablesourexistrosPL/SQLIObténeimprimeinformacióndapersoaconIDPERSOA'12345678A'DECLARErPersoaPERSOA%ROWTYPE;BEGINSELECT*INTOrPersoaFROMPERSOAWHEREIDPERSOA='12345678A';DBMS_OUTPUT.PUT_LINE(rPersoa.IDPERSOA||''||rPersoa.NOME||''||rPersoa.IDADE);END;/12345678AMatusalén851OraclePL/SQL10/42 SQLenbloquesPL/SQL(v)Seleccióndedatos(cont.)IObténeimprimeinformaciónda(s)persoa(s)demáisde15anosDECLARErPersoaPERSOA%ROWTYPE;BEGINSELECT*INTOrPersoaFROMPERSOAWHEREIDADE>15;DBMS_OUTPUT.PUT_LINE(rPersoa.IDPERSOA||''||rPersoa.NOME||''||rPersoa.IDADE);END;IExcepciónTOO_MANY_ROWSERRORenlínea1:ORA-01422:larecuperaciónexactadevuelveunnúmeromayordefilasqueelsolicitadoORA-06512:enlínea4OraclePL/SQL11/42SQLenbloquesPL/SQL(vi)Seleccióndedatos(cont.)IObténeimprimeinformaciónda(s)persoa(s)de101anosDECLARErPersoaPERSOA%ROWTYPE;BEGINSELECT*INTOrPersoaFROMPERSOAWHEREIDADE=101;DBMS_OUTPUT.PUT_LINE(rPersoa.IDPERSOA||''||rPersoa.NOME||''||rPersoa.IDADE);END;IExcepciónNO_DATA_FOUNDERRORenlínea1:ORA-01403:nosehanencontradodatosORA-06512:enlínea4OraclePL/SQL12/42 SQLenbloquesPL/SQL(vii)Seleccióndedatos(cont.)IOSELECT...INTOsópodeusarsesesabemosqueimosrecuperarunhalaIExemplos:funciónsdeagregación(SUM,AVG,COUNT,...)DECLAREnumeroNUMBER;mediaNUMBER;BEGINSELECTCOUNT(*),AVG(IDADE)INTOnumero,mediaFROMPERSOA;DBMS_OUTPUT.PUT_LINE('Hai'||numero||'persoas');DBMS_OUTPUT.PUT_LINE('Aidademediaéde'||numero||'anos');END;/Hai3persoasAidademediaéde3anosOraclePL/SQL13/42EstructurasdeControlCondicionalISimpleIF<condición>THEN<secuenciaqueseexecutasicondiciónécerta>ENDIF;ISintaxecompletaIF<cond1>THEN/*Accións*/[ELSIF<cond2>THEN/*Accións*/...][ELSE/*Acciónssetodasascondiciónsanterioresnonsoncertas*/]ENDIF;OraclePL/SQL14/42 EstructurasdeControl(ii)Condicionais.ExercicioIEscribeunbloquedecódigoqueindiqueseamediadeidadeésuperiorounona25anos.IFuncionaIF(SELECTAVG(IDADE)....)?INonIHaiqueusarSELECT...INTODECLAREmediaNUMBER;BEGINSELECTAVG(IDADE)INTOmediaFROMPERSOA;IFmedia>25THENDBMS_OUTPUT.PUT_LINE('Idademediasuperiora25');ELSEDBMS_OUTPUT.PUT_LINE('Nonésuperiora25');ENDIF;END;/Idademediasuperiora25OraclePL/SQL15/42EstructurasdeControl(iii)BuclesISimple:LOOP...ENDLOOPDECLAREcontadorBINARY_INTEGER:=1;BEGINLOOP--Bucleinfinito!DBMS_OUTPUT.PUT_LINE(contador);contador:=contador+1;ENDLOOP;END;OraclePL/SQL16/42 EstructurasdeControl(iv)BuclesIIntroducirunhacondicióneusarEXITDECLAREcontadorBINARY_INTEGER:=1;BEGINLOOPIFcontador>10THENEXIT;ENDIF;DBMS_OUTPUT.PUT_LINE(contador);contador:=contador+1;ENDLOOP;END;IMellor:UsarEXITWHEN<condicion>LOOPEXITWHENcontador>10;...ENDLOOP;OraclePL/SQL17/42EstructurasdeControl(iv)Buclesanidados.EtiquetasEtiqueta:<<nome_etiqueta>>Localizaaseguinteliñadecódigo.DECLAREcont1BINARY_INTEGER:=1;cont2BINARY_INTEGER:=1;BEGIN<<externo>>LOOPDBMS_OUTPUT.PUT_LINE('Bucleexterno:'||cont1);cont2:=1;<<interno>>LOOPDBMS_OUTPUT.PUT('Int:'||cont2);cont2:=cont2+1;EXITinternoWHENcont2>cont1;ENDLOOP;DBMS_OUTPUT.NEW_LINE;cont1:=cont1+1;EXITexternoWHENcont1>10;ENDLOOP;END;OraclePL/SQL18/42 EstructurasdeControl(v)BuclesWHILEISintaxe:WHILE<condicion>LOOP...ENDLOOP:IComprobaacondiciónantesdeentrarnobucleIExemplo:DECLAREcontadorBINARY_INTEGER:=1;BEGINWHILEcontador<=10LOOPDBMS_OUTPUT.PUT_LINE(contador);contador:=contador+1;ENDLOOP;END;OraclePL/SQL19/42EstructurasdeControl(vi)BuclesFORnuméricosISintaxe:FOR<variable>IN[REVERSE]<lim_inf>..<lim_sup>LOOP/*Sentencias*/ENDLOOP;IAvariabledoFORélocalenonsedeclaraISempre<lim_inf>..<lim_sup>(inclusoconreverse)BEGINFORcontadorIN1..10LOOPDBMS_OUTPUT.PUT_LINE(contador);ENDLOOP;--Oseguintecontadoré"distinto"--Imprimiráosnúmerosdo10ó1--½forcontadorin10..1loopseríaincorrecto!FORcontadorINREVERSE1..10LOOPDBMS_OUTPUT.PUT_LINE(contador);ENDLOOP;END;OraclePL/SQL20/42 Consultasecursores(i)UnhalaISesabemosaprioriqueaconsultadevolveexactamenteunhala:SELECT<lista_de_atributos>INTO<lista_de_variables>;SELECT<lista_de_atributos>INTO<rexistroPL/SQL>;NúmeroindeterminadodelasIUsodeCURSORes1.Declararcursor2.Abrircursor3.(Bucle)Procesarlas4.CerrarcursorOraclePL/SQL21/42Consultasecursores(ii)DeclaraciónINaseccióndedeclaracióndevariablesISintaxe:CURSOR<nome_cursor>IS<consulta>;IApareceotipo<nome_cursor>%ROWTYPE.IAconsultapodeincluirvariablesExemplosDECLARECURSORc_xenteISSELECT*FROMPERSOA;CURSORc_xubiladosISSELECT*FROMPERSOAWHEREIDADE>=65;limiteIdadePERSOA.IDADE%TYPE;CURSORc_xente_maior_deISSELECT*FROMPERSOAWHEREIDADE>=limiteIdade;rPersoac_xente_maior_de%ROWTYPE;--SeríaigualquePERSOA%ROWTYPEOraclePL/SQL22/42 Consultasecursores(iii)AperturadocursorIAbrirocursor:OPEN<nome_cursor>;ISeaconsultaincluíavariablesdebemosdarllesovalorantesdeabrirocursor.IOracleexecutaaconsultaeusaaáreaecontextoIInformaciónsobreaconsultaIOsdatosobtidosdaconsulta(conxuntoactivo)ExemploDECLARElimiteIdadePERSOA.IDADE%TYPE;CURSORc_xente_maior_deISSELECT*FROMPERSOAWHEREIDADE>=limiteIdade;BEGINlimiteIdade:=65;OPENc_xente_maior_de;...END;OraclePL/SQL23/42Consultasecursores(iv)CerredocursorICerrarocursor:CLOSE<nome_cursor>;IOracleliberaaáreadecontextoasociadaExemploDECLARElimiteIdadePERSOA.IDADE%TYPE;CURSORc_xente_maior_deISSELECT*FROMPERSOAWHEREIDADE>=limiteIdade;rPersoac_xente_maior_de%ROWTYPE;BEGINlimiteIdade:=35;OPENc_xente_maior_de;...CLOSEc_xente_maior_de;END;OraclePL/SQL24/42 Consultasecursores(v)ProcesarlasISentenciaparaobterunhala:FETCH<nome_cursor>INTO{<listadevariables>|<rexistroPL/SQL>};INormalmentedentrodunbuclePropiedadesdoscursores%ISOPEN:IIndicaseocursorestáaberto.ISe<nomecursor>%ISOPENécerto,podemosconsultarasseguintespropiedades.%FOUNDIOúltimoFETCHobtivounhala.%NOTFOUNDIOúltimoFETCHnonobtivoningunhala.%ROWCOUNTINúmerodelasprocesadasactualmente.IAstresúltimaspropiedadestenenunsignicadoespecialparaocursorSQLOraclePL/SQL25/42Consultasecursores(vi)Procesarlas(bucleestándar)DECLARElimiteIdadePERSOA.IDADE%TYPE;CURSORc_xente_maior_deISSELECT*FROMPERSOAWHEREIDADE>=limiteIdade;rPersoac_xente_maior_de%ROWTYPE;BEGINlimiteIdade:=35;OPENc_xente_maior_de;LOOPFETCHc_xente_maior_deINTOrPersoa;EXITWHENc_xente_maior_de%NOTFOUND;DBMS_OUTPUT.PUT_LINE(rPersoa.idpersoa||','||rPersoa.nome||','||rPersoa.idade);ENDLOOP;CLOSEc_xente_maior_de;END;/01234567T,Carpanta,5112345678A,Matusalén,851OraclePL/SQL26/42 Consultasecursores(vii)Procesarlas(buclewhile)Lecturaadiantada:DECLARElimiteIdadePERSOA.IDADE%TYPE;CURSORc_xente_maior_deISSELECT*FROMPERSOAWHEREIDADE>=limiteIdade;rPersoac_xente_maior_de%ROWTYPE;BEGINlimiteIdade:=35;OPENc_xente_maior_de;FETCHc_xente_maior_deINTOrPersoa;WHILEc_xente_maior_de%FOUNDLOOPDBMS_OUTPUT.PUT_LINE(rPersoa.idpersoa||','||rPersoa.nome||','||rPersoa.idade);FETCHc_xente_maior_deINTOrPersoa;ENDLOOP;CLOSEc_xente_maior_de;END;OraclePL/SQL27/42Consultasecursores(viii)Procesarlas(bucleforconcursorexplícito)ISintaxe:FOR<rexistro>IN<nome_cursor>LOOP/*Procesarfilaactual*/ENDLOOP;IConsideraciónsimportantesIO<rexistro>nonsedeclara,éinternoóFORINonabrimos(OPEN)ocursorantesdobucleINoncerramos(CLOSE)ocursordespoisdobucleINonfacemosFETCHdentrodobucleINonfacemoscomprobación(%NOTFOUND)denalizaciónDECLARElimiteIdadePERSOA.IDADE%TYPE;CURSORc_xente_maior_deISSELECT*FROMPERSOAWHEREIDADE>=limiteIdade;BEGINlimiteIdade:=35;FORrPersoaINc_xente_maior_deLOOPDBMS_OUTPUT.P