PROCEDURES, Stored procedure FUNÇÕES, PL/SQL, …

1 1 PROCEDURES, FUN ES, PL/SQL, CURSORES, VIEW, NDICE eSEQU NCIA Prof. Edson ThizonStored procedure Uma Stored procedure um grupo de comandos SQL e PL/SQL que executam determinada tarefa e podem ser invocados por um A linguagem PL/SQL uma extens o da linguagem SQL que cont m recursos das linguagens mais modernas. Permite construir blocos de comandos SQL para acesso e manipula o de base de dados. Benef cios: permitem mover regras de neg cios de aplicativos para o banco de dados, tornando-as independentes de aplicativos; fornecem um mecanismo eficiente de reutiliza o de c digo; aumentam a performance de aplicativos cliente-servidor;Procedimentos e Fun esProcedimento ( procedure )CREATE [OR REPLACE] procedure [user.]

2 ] procedure [ (argumento [IN |OUT|IN OUT] datatype[,(argumento [IN |OUT|IN OUT] datatype] ..)]{IS/AS}BLOCO;SintaxeProcedureCREATE procedure nome da procedure (par metro modo tipo)IS/AS declara es de vari veisBLOCO PL/SQLpar metro nome da vari vel PL/SQL que ser passado para a IN entrada OUT sa datipo se o par metro n merico, caracter, data ..declara o de vari veis nome, tamanho e tipo das vari veis que eu utilizarei no bloco PL/SQL2 Exemplo:CREATE OR REPLACE procedure consulta(v_cod_empregado in ,v_nome_empregado out ,v_salario_empregado out ,v_com_empregado out )ISBEGINSELECT ename, sal, commINTO v_nome_empregado, v_salario_empregado, v_com_empregadoFROM empWHERE empno = v_cod_empregado;END consulta;ProcedureChamando uma procedure As procedures podem ser chamadas de diversas formas: Vamos supor que n s tenhamos uma procedure chamada proc_teste.)

3 Atrav s do SQL*Plus:SQL> EXECUTE proc_teste; Atrav s de um bloco (ex: procedure ou function):BEGIN proc_teste;END;Exemplo:CREATE OR REPLACE procedure novo_periodo(p_cod_periodo in number,p_data_incial in date,p_data_final in date)ISBEGIN insert into periodo_letivo values(p_cod_periodo, p_data_incial, p_data final);END;ProcedureFun o (function)CREATE [OR REPLACE] FUNCTION [user.]function[(argumento IN datatype[,argumento IN datatype]..)] RETURN datatype{IS/AS}BLOCO;SintaxeFunctionCREA TE FUNCTION nome da fun o(par metro modo tipo)RETURN tipoIS/AS declara es de vari veisBLOCO diferen a entre procedure e fun o que a fun o sempre ir retornar um valor para o ambiente OR REPLACE FUNCTION salario(v_empno in )RETURN numberISv_emp_sal := 0;BEGINSELECT sal INTO v_emp_salFROM empWHERE empno = v_empno;RETURN (v_emp_sal);END salario;Exemplo3 ExemploFunctionCREATE OR REPLACE FUNCTION minuto(dataim DATE, datafm DATE)RETURN NUMBERIS hora NUMBER(8):=0;minu NUMBER(8):=0;minutoh NUMBER(8):=0;BEGIN hora := TRUNC((datafm - dataim)*24);minu := TRUNC(((datafm - dataim)*24-TRUNC((datafm - dataim)*24))*60).

4 Minutoh := (hora*60)+minu;RETURN minutoh;END;Compilando e Removendo Tanto os procedimentos quanto as fun es s o compiladas no instante do comando CREATE, mas se n s necessitarmos compil -las explicitamente, ent o os comandos s o: ALTER procedure nome da procedureCOMPILE; ALTER FUNCTION nome da fun oCOMPILE; Quando n s quisermos remover esses objetos do Banco, os comandos s o: DROP procedure nome da procedure ; DROP FUNCTION nome da fun o;PL/SQL A PL/SQL uma linguagem proceduralda Oracle que estende a SQL com comandos que permitem a cria o de procedimentos de programa o. A linguagem permite a declara o de constantes, vari veis, subprogramas (procedures e fun es), que favorecem a estrutura o de c digo, e possui mecanismos para controle de erros de execu Suporte para SQL (DML e Transa o); Performance (tr fego rede); Portabilidade (SO e plataforma); Produtividade (batch, relat rios, etc).

5 Caracter sticas cada comando SQL deve terminar com um ponto e v rgula, exceto BEGIN, DECLARE e EXCEPTION; um bloco PL/SQL n o uma unidade de transa o. Os comandos COMMIT e ROLLBACK devem ser usados conforme a necessidade da aplica o; Comandos DDL n o s o permitidos; Comandos SELECT que n o retornam apenas uma linha causam uma EXCEPTION que pode ser tratada pelo usu rio; Comandos DML podem processar v rias linhas ao mesmo tempo; Se um erro n o previsto for encontrado na execu o de um bloco PL/SQL, ent o o Oracle emitir uma mensagem indicando o erro e o c digo do erro ocorrido e em qual linha ocorreu o PL/SQL estruturada em blocos. Cada bloco pode conter outros blocos.

6 Em cada um desses blocos, podemos declarar vari veis que deixam de existir quando o bloco terminaDECLARE - OpcionalVariaveis, cursores, exceptions definidas pelo usu rioBEGIN- Obrigat rio- SQL - PL/SQLEXCEPTION OpcionalA es que s o executadas quando ocorem os errosEND obrigat rio4 BLOCO PL/SQL COM SUB-BLOCODECLAREDEFINI O DE VARI VEISBEGINCOMANDOSDECLAREDEFINI O DE VARI VEISBEGINCOMANDOSEXCEPTIONTRATAMENTO DE ERROSEND;END;ExemploDeclareV_variavel varchar2(5);BeginSelect nome_coluna Into v_vari velFrom table_name;ExeptionWhen exception_name ;Obs: Sempre coloque um (;) no fim de SQL ou um SQL SELECT, INSERT,UPDADE,DELETE, ROLLBACK,COMMIT,SAVEPOINTDATATYPES MAIS UTILIZADOSCHARVARCHAR2 INTERGERNUMBERDATEBOOLEAND eclareV_datadate;V_deptno number(2) := 10;V_location varchar2(13) := Atlanta ;V_comm contant number :=1400;EXEMPLODECLARENOMECHAR(30);SALARI O NUMBER(11,2);DEPART NUNBER(4);DTNASC DATE;SIM BOOLEAN;CONT NUMBER(6) :=0;PERC CONSTANT NUMBER(4,2):= ;%TypeO atributo %TYPED eclara a vari vel de acordo com uma coluna definida no Banco de Dados;ExemploV_ename ;V_balancenumber(7,2);V_min_balance v_balance%Type :=10.

7 5 FUN ES UTILIZADAS EM PL/SQLP odemos contar com o uso de fun es de Caracteres, Num ricas, Data, Convers o, dentre :DeclareCargo_atual char(10);BeginSelect upper(substr(cargo,1,10)) intocargo_atualfrom funcionariowhere cd_func = 2150;End;FUN ES DE ERRO sqlerrm, sqlcodeFUN ES DE CARACTERES ascii, chr, initcap, length, lower, lpad,rpad, ltrim, rtrim, substr, ES NUM RICASabs, mod, round, trunc, COMANDOS PL/SQL PODEMOS UTILIZAR AS SEGUINTES FUN ES:CONTROLE DE FLUXOCOMANDO IF1. IF <condi o> THEN<comandos> END IF;2. IF <condi o> THEN<comandos>ELSE<comandos>END IF;Comando If3. IF <condi o> THEN<comandos>ELSIF <condi o> THEN<comandos>END IF;4. IF <condi o> THEN<comandos>ELSIF <condi o> THEN<comandos>ELSE<comandos>END IF;Comando If5.

8 IF <condi o> THENIF <condi o> THEN<comandos>END IF;END IF;SQL> VARIABLE MSG VARCHAR2(100);SQL> DECLAREVALOR NUMBER(7,2) := BEGINIFVALOR > 0 THEN:MSG := Valor maior que zero ;ELSIFVALOR = 0 THEN:MSG := Valor igual a zero ;ELSE :MSG := Valor menor que zero ;END IF;END;SQL>Entre o valor para val: 5antigo 2: VALOR NUMBER(7,2) := novo 2: VALOR NUMBER(7,2) := 5;Procedimento PL/SQL conclu do com > print msgMSG---------------------------------- -------------Valor maior que zero6 COMANDOS DE REPETI OS O UTILIZADOS PARA EXECUTAR REPETIDAMENTE O C DIGO ESCRITO DENTRO DELES. LOOP FOR LOOPs WHILE LOOP CURSOR FOR LOOPsEXEMPLO COMANDO LOOPSQL> SET SERVEROUT ON ; (ATIVA O DBMS)SQL> DECLAREXNUMBER := 0;COUNTER NUMBER := 0;BEGINLOOPX := X + 1000;COUNTER := COUNTER + 1;IF COUNTER > 4 THEN EXIT; (sai do loop)END IF;:MSG := X || || COUNTER || LOOP ;END LOOP;END;EXEMPLO COMANDO FOR.

9 LOOPDECLAREA,BNUMBER(3):= 0;BEGINFOR A IN LOOPB:= B + 1; ('LOOP1 -'||B); END LOOP;END;EXEMPLO COMANDO WHILE .. LOOPSQL> CREATE TABLE TESTE (X NUMBER(3), Y VARCHAR2(30), K DATE);SQL> DECLAREX NUMBER(3);Y VARCHAR2(30);K DATE;J NUMBER(3);BEGINX:= 0;WHILE X<= 100 LOOPK:= SYSDATE-X;Y := 30;INSERT INTO TESTE VALUES (X,Y,K);X := X + 1;END LOOP;COMMIT;END;SQL> SELECT * FROM TESTE;CURSORCURSOR < nome-cursor > [par metro tipo,..] IS< comando select >EXEMPLO:CURSOR MEU_CURSOR ISSELECT ENAME, EMPNO, SAL FROM EMPORDER BY SAL DESC;DECLARECURSOR C1 ISSELECT ENAME, EMPNO, SAL FROM EMPORDER BY SAL DESC;MY_ENAME CHAR(10);MY_EMPNO NUMBER (4);MY_SAL NUMBER (7,2);BEGINOPEN C1;FOR I IN LOOPFETCH C1 INTO MY_ENAME, MY_EMPNO, MY_SAL;EXIT WHEN C1%NOTFOUND; (MY_EMPNO || || MY_ENAME|| || MY_SAL);END LOOP;CLOSE C1;END;OPEN CURSOR / FETCH CURSOR7 FOR.

10 LOOP - CURSORDECLARECURSOR C1 ISSELECT ENAME, EMPNO, SAL FROM EMPORDER BY SAL DESC;BEGINFOR R1 IN C1 (R1. EMPNO || || R1. ENAME|| || R1. SAL);END LOOP;END;EXCE ESS O USADAS NO PL/SQL PARA LIDAR COM QUAISQUER ERROS QUE OCORRAM DURANTE A EXECU O DE UM DOIS TIPOS DE EXECE ES, AS DEFINIDAS INTERNAMENTE PELA PL/SQL E AS DEFINIDAS PELO USU PARTE VEREMOS APENAS ALGUMAS DELASSINTAXE:EXCEPTIONWHEN <nome-exeception> THEN<comandos>;WHEN <nome-execetion> THEN<comandos>;EXEMPLO EXCEPTIONNO_DATA_FOUND -Quando um select n o retorna nenhuma linhaTOO_MANY_ROWS -Quando um select retorna mais de uma linhaOTHERS -Qualquer tipo de erroDECLARENOME CHAR(15);CARGO CHAR(10);BEGINSELECT ENAME, JOB INTO NOME, CARGOFROM EMPWHERE ENAME = KONG.