Apostila de PL - SQL - Alexandre

PL/SQL
Alexandre Costa Vieira

------------------------------
PL/SQL ORACLE
Contedo
PREFCIO ....................................................................................................................................................2
INTRODUO .............................................................................................................................................3
VIEW ..............................................................................................................................................................4
CREATE VIEW ..............................................................................................................................................4
BLOCO PL/SQL ...........................................................................................................................................5
COMENTRIOS .............................................................................................................................................6
PACOTE DBMS_OUTPUT ..........................................................................................................................6
O ATRIBUTO %TYPE ................................................................................................................................7
CREATE SEQUENCE .................................................................................................................................7
CONTROLE DE FLUXO ............................................................................................................................9
COMANDO IF.........................................................................................................................................9
CREATE TRIGGER ...................................................................................................................................10
DECLARANDO VARIVEIS DENTRO DE UMA TRIGGER .................................................................................12
GERANDO UMA MENSAGEM DE ERRO .........................................................................................................13
GERAO AUTOMTICA DE CHAVE PRIMRIA ............................................................................................15
ALTER TRIGGER .........................................................................................................................................16
DROP TRIGGER ...........................................................................................................................................17
CREATE FUNCTION ................................................................................................................................17
DROP FUNCTION.........................................................................................................................................19
Cursor ....................................................................................................................................................19
Cursos For Loop.................................................................................................................................21
COMANDO WHILE .. LOOP ................................................................................................................22
EXCEPTIONS ...............................................................................................................................................23
Outros EXCEPTIONS............................................................................................................................24
TOO_MANY_ROWS - QUANDO UM SELECT RETORNA MAIS DE UMA LINHA..........................................24
OTHERS - QUALQUER TIPO DE ERRO .......................................................................................................24
RETORNANDO ERROS ........................................................................................................................25
EXCEPTION..........................................................................................................................................25
FUNES DE ERRO ...............................................................................................................................26
CREATE PROCEDURE ..................................................................................................................................26
Alter Procedure .....................................................................................................................................27
Drop Procedure .....................................................................................................................................27
Executando Procedure ..........................................................................................................................27
CREATE PACKAGE .................................................................................................................................28
Create Package Body ............................................................................................................................30
Alter Package ........................................................................................................................................33
[Alexandre Costa Vieira PL/SQL ORACLE]
Pgina 1
PL/SQL ORACLE
Prefcio
Este curso explora toda a potncia da linguagem PL/SQL da ORACLE.
PBLICO
Todos aqueles que esto tecnicamente envolvidos na criao do ORACLE aplicaes ou
manutenes desse aplicativo, Programadores, Administradores de Banco de Dados, Analistas e
Programadores Snior. Usurios finais quem deseja estender seus conhecimentos, pode achar o curso
benfico.
PR-REQUISITOS
Linguagem SQL e noes de banco de dados ORACLE.
OBJETIVOS
No final do curso os alunos estaro aptos para:
Criar cdigos usando a linguagem PL/SQL ORACLE.
Desenvolver PROCEDURES,FUNOES e PACKAGES.
MANEIRA DE APRESENTAO
Este curso direcionado com combinaes tradicionais de sees com mdias projees e sees
de demonstraes usando a tela de um projetor. Os alunos tero ampla oportunidade para praticar os
tpicos trazidos nas sees anteriores.
HORRIO
Este curso exige no mnimo 15 horas divididas em 5 dias, e mais 30 horas de dedicao extra
curso para que o aluno possa assimilar os conceitos apresentados em sala de aula.
Pgina 2
PL/SQL ORACLE
Introduo
Recentemente o perfil de utilizao evoluiu novamente e nos vemos diante de novas
necessidades. Estruturas mais complexas como Banco de Dados Distribudos, grande quantidade
de informao, custo de trfego em rede e Arquitetura Cliente-Servidor so algumas
caractersticas desse novo perfil.
Na Arquitetura Cliente-Servidor, por exemplo, muito importante manter o trfego de rede
a um mnimo e aumentar a performance do RDBMS de modo que ele possa atender rapidamente
as freqentes transaes solicitadas pelo usurio.
Com essas especificaes, organizar o processamento tornando-se fundamental. Por
exemplo, grandes blocos PL/SQL circulando pela rede e sendo compilados e otimizados pelo
servidor, a cada nova execuo, no uma boa soluo. A melhor idia armazenar rotinas
dentro do RDBMS e acionar essas rotinas atravs de chamadas. Em vez de todo um bloco
PL/SQL temos circulando pela rede apenas o nome da rotina e seus parmetros. Alm disso o
Oracle pode guardar a forma compilada e otimizada dessas rotinas melhorando o tempo de
execuo. No Oracle isto um Procedimento Armazenado, ou STORED PROCEDURE.
Pgina 3
PL/SQL ORACLE
View
View uma tabela lgica gerada no instante da execuo do comando. Para criar uma
view a partir de uma ou mais tabelas usamos o comando Create View. Todas alteraes,
inseres ou excluses poderam refletir nas tabelas desde que cada linha da view corresponda a
uma linha real da tabela.
Create View
Sintaxe
CREATE [OR REPLACE][FORCE | NOFORCE] VIEW
[schema.]view[(alias[,alias]...)]
AS subquery
[[WITH CHECK OPTION [CONSTRAINT constraint]] | [WITH READ ONLY]]
Parmetros
OR REPLACE
Recria a view se ela j existir.
FORCE
Cria a view mesmo se o objeto referenciado

no existir ou o usurio no possuir os
privilgios sobre este objeto.
NOFORCE
Cria a view somente se o objeto referenciado

no existir ou o usurio no possuir os
privilgios sobre este objeto.
Schema
Usurio dono da view
View
Nome da view
alias
Especifica nomes para as expresses

selecionadas atravs da query formadora
AS subquery
Qualquer comando SELECT, sem as

clusulas ORDER BY ou FOR UPDATE , que
dar origem s colunas da view.
WITH CHECK OPTION

Fora a aceitar somente comandos INSERT
ou UPDATE de linhas que continuem
aparecendo na view.
CONSTRAINT
O nome associado check option constraint
WITH READ ONLY

No permite a execuo de comandos
INSERT ou UPDATE sobre a view
Pgina 4
PL/SQL ORACLE
Exemplo I
O comando seguinte cria uma view da tabela EMP. Esta view mostra os funcionrios do
departamento 20 e seu salrio anual:
CREATE VIEW dept20
AS SELECT ename, sal*12 annual_salary
FROM emp
WHERE deptno = 20
Exemplo II
Este exemplo cria uma view chamada ANALISTAS, com restrio na alterao do cargo:
CREATE VIEW analistas (id_number, person, department, position)
AS SELECT empno, ename, deptno, job
FROM emp
WHERE job = 'ANALISTA'
WITH CHECK OPTION CONSTRAINT wco
Exemplo III
Este exemplo cria uma view igual ao exemplo anterior, mas no permite alteraes:
CREATE VIEW clerk (id_number, person, department, position)
AS SELECT empno, ename, deptno, job
FROM emp
WHERE job = 'CLERK'
WITH READ ONLY
Bloco PL/SQL
Um bloco PL/SQL consiste de um conjunto de instrues SQL (SELECT,INSERT,
UPDATE, DELETE) ou comandos PL/SQL, e desempenha uma funo lgica nica, afim de
resolver um problema especfico ou executar um conjunto de tarefas afins. O Bloco PL/SQL
tambm pode ser referenciado com Unidade de Programa PL/SQL. Os blocos PL/SQL so
qualificados em bloco annimo e Stored Procedure.
O bloco annimo
No tem nome
No est armazenado no SGDB
Geralmente est armazenada na aplicao.
Stored SubProgramas
Utiliza a estrutura do bloco annimo com base.
Esto armazenados no SGDB,
A eles atribudo um nome que poder ser utilizado nas aplicaes ou por
outros objetos do banco de dados
A estrutura de um bloco PL/SQL constituida de trs sees:
Pgina 5
PL/SQL ORACLE
a) SEO DE DECLAO (DECLARE) - Nesta seo so definidos os objetos

PL/SQL como variveis, constantes, cursores e excees definidas pelo usurio que
podero ser utilizadas dentro do bloco.
b) SEO DE EXECUES (BEGIN..END;) - Nesta seo contemplar a
sequncia de comandos PL/SQL e instrues SQL do bloco.
c) SEO DE TRATAMENTO DE ERRO (EXCEPTION) - Nesta seo sero
tratados os erros definidos e levantados pelo prprio bloco e os erros gerados pela
execuo do bloco (O captulo 12 abordar o tratamento de exees no PL/SQL)
Exemplo:
[DECLARE
-- declaraes]
BEGIN
-- instrues e comnados
[EXCEPTION
-- tratamentos de erro]
END;
Comentrios
Os comentrios em PL/SQL so de dois tipos
a) Uma Linha: utiliza-se o delimitador --. A partir de dois hfens tudo o que for escrito at o final
da linha considerado comentrio.
b) Mltiplas linhas: utiliza-se o delimitador /* para abrir e */ para fechar. Tudo e todas as linhas
que estiverem entre os dois delimitadores sero ignorados
Exemplo:
BEGIN
-- comentando apenas uma linha
COMANDO1;
/* comentando
vrias
linhas */
COMANDO2;
COMANDO3; -- o resto ser ignorado
END;
Pacote DBMS_OUTPUT
Na programao PL/SQL no existe nenhuma funcionalidade de entrada ou sada. Para
remediar isso, usaremos no aplicativo SQL*Plus o Supplied Package DBMS_OUTPUT que
fornecer apenas a capacidade de dar sadas para mensagens na tela. Isso feito por meio de
dois passos 1.Permitir a sada no SQL*Plus com o comando set serveroutput SET
SERVEROUTPUT {ON | OFF}. Dentro do programa PL/SQL, utilize o procedure
DBMS_OUTPUT.PUT_LINE. Essa procedure adicionar o argumento informado ao buffer de
sada. Com esses passos completados, a sada impressa na tela do SQL*Plus depois que o
bloco for completamente executado. Durante a execuo, o buffer preenchido pelas chamadas
de DBMS_OUTPUT.PUT_LINE. O SQL*Plus no recupera o contedo do buffer e no o imprime
at que o controle retorne para o SQL*Plus, depois que o bloco terminou a execuo.
Exemplo:
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(Hello from PL/SQL);
Pgina 6
PL/SQL ORACLE
3 END;
4 /
Hello from PL/SQL
PL/SQL procedure successfully completed.
SQL>
O atributo %TYPE
Declara a varivel de acordo com uma coluna definida no Banco de Dados;
Exemplo
V_ename
V_balance
V_min_balance
emp.ename%Type;
number(7,2);
v_balance%Type :=10;
Create Sequence
Cria uma sequence. Uma sequence um objeto que permite vrios usurios gerarem
valores inteiros sem repetio.
Sintaxe
CREATE SEQUENCE [schema.]sequence
[INCREMENT BY integer]
[START WITH integer]
[MAXVALUE integer]
[MINVALUE integer]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE]
[ORDER | NOORDER]
Parmetros
schema
Nome do usurio dono da sequence
Sequence
Nome da sequence
INCREMENT BY
Indica o intervalo entre os nmeros gerados.
MINVALUE
Valor mnimo que a sequence pode assumir
MAXVALUE
Valor mximo que a sequence pode assumir
START WITH
O primeiro valor gerado pela sequence
CYCLE
Indica que esta uma sequence cclica. Isto

, recomear a contagem quando atingir os
Pgina 7
PL/SQL ORACLE
extremos
NOCYCLE
Ira incrementando
estipulado
ate
atingir
limite
CACHE
Indica quantos valores da sequence o

ORACLE ira manter na memria para um
acesso mais rpido.
NOCACHE
No ter valores pr alocados
ORDER
Garante que o numero gerado pela sequence

obedece a ordem de requisio
NOORDER
No garante que os nmeros seqenciais

gerados obedecem ordem de requisio
Depois de criada a sequence, voc pode acessar seus valores nos comandos SQL
atravs das pseudo-colunas abaixo:
CURRVAL Retorna o ultimo valor da sequence gerado para a sesso

atual
NEXTVAL
Incrementa a sequence e retorna o novo valor
Exemplo I
O comando seguinte cria uma sequence com incremento de 10 comeando do 1:
CREATE SEQUENCE teste_seq

INCREMENT BY 10
Exemplo II
O comando a seguir cria uma sequence com incremento de 1 comeando do 11:
CREATE SEQUENCE seq_cod_func

INCREMENT BY 1
START WITH 11;
SELECT seq_cod_func.currval FROM dual;
SELECT seq_cod_func.nextval FROM dual;
Exemplo III
O comando a seguir cria uma sequence comeando do 30:
CREATE SEQUENCE seq_cod_cli
Pgina 8
PL/SQL ORACLE
START WITH 30;
SELECT seq_cod_cli.currval FROM dual;

SELECT seq_cod_cli.nextval FROM dual;
CONTROLE DE FLUXO
COMANDO IF
1. IF <condio> THEN
<comandos>
END IF;
2. IF <condio> THEN
<comandos>
ELSE
<comandos>
END IF;
3. IF <condio> THEN
<comandos>
ELSIF <condio> THEN
<comandos>
END IF;
4. IF <condio> THEN
<comandos>
ELSIF <condio> THEN
<comandos>
ELSE
<comandos>
END IF;
5. IF <condio> THEN
IF <condio> THEN
<comandos>
END IF;
END IF;
EXEMPLO
DECLARE
QUANT NUMBER(3);
BEGIN
SELECT ES.NR_QTD INTO QUANT
FROM ESTOQUE ES
WHERE CD_PROD = 30;
IF QUANT > 0 AND QUANT < 3000 THEN
UPDATE ESTOQUE SET NR_QTD = QUANT + 1
Pgina 9
PL/SQL ORACLE
WHERE CD_PROD = 30;

ELSIF QUANT >= 3000 THEN
INSERT INTO ALERTA(PROD,ERRO) VALUES(30,MXIMO);
ELSE INSERT INTO ALERTA(PROD,ERRO) VALUES(30,MNIMO);
END IF;
END;
Create Trigger
Cria e habilita triggers. Trigger um bloco de cdigo PL/SQL associado a uma tabela. Se
um comando SQL for executado sobre esta tabela as triggers so disparadas automaticamente
pelo Oracle. Normalmente as regras de negcio so implementadas em triggers.
Sintaxe
CREATE [OR REPLACE] TRIGGER [schema.]trigger

{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF column [, column] ...]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}] ...

ON [schema.]table
[ [REFERENCING { OLD [AS] old [NEW [AS] new]
| NEW [AS] new [OLD [AS] old] } ]
FOR EACH ROW
[WHEN (condition)] ]
pl/sql_block
Parmetros
OR REPLACE
Recria a trigger se esta j existir.
schema
Nome do usurio dono da trigger
table
Nome da tabela qual a trigger esta associada
trigger
Nome da trigger que esta sendo criada.
BEFORE
Indica ao ORACLE para disparar a trigger antes de

executar o comando
AFTER
Indica ao ORACLE para disparar a trigger depois de

executar o comando
DELETE
Indica que esta trigger deve ser disparada quando um

comando DELETE apagar uma linha da tabela.
INSERT

comando INSERT adicionar uma linha da tabela.
UPDATE OF

comando UPDATE alterar uma das colunas
especificadas na clausula OF
REFERENCIN
Usado para nomes correlacionados. Voc pode usar
Pgina 10
PL/SQL ORACLE
correlao de nomes em blocos PL/SQL e em

clusulas WHEN de uma trigger para fazer referncia
de antigos e novos valores da linha corrente.
FOR EACH
ROW
Indica que a trigger deve ser disparada a cada linha

afetada pelo comando
WHEN
Contm uma condio SQL que deve ser verdadeira

para permitir o disparo da trigger.
pl/sql_block
Bloco PL/SQL que indica a ao a ser executada pela

TRIGGER
Quando uma trigger criada para mais que uma operao DML, pode-se utilizar
constantes condicionais no corpo da trigger para executar determinado bloco de comandos,
dependendo do comando que a disparou. As constantes condicionais so as seguintes:
INSERTING
TRUE se o comando que disparou a trigger foi um

INSERT.
DELETING

DELETE.
UPDATING

UPDATE.
UPDATING (column_name)
UPDATE e a coluna column_name foi alterada.
Obtendo o contedo dos campos em triggers:
Se a operao for
INSERT retornar o
BEFORE
Se a trigger for valor que poder ser
inserido pelo comando
disparada
BEFORE
os SQL. No UPDATE o
dados podem ser comportamento ser o
:NEW.Nome_do_Campo
mesmo, pois retornar o
alterados.
valor do campo com as
alteraes que podero
Retorna o contedo novo do campo.
ser feitas.
No DELETE no utilizado pois
AFTER
no h alterao de valores,
consequentemente no h valor Se a trigger for
novo.
disparada AFTER
os dados no
podem
ser
alterados.
Se a operao for
INSERT retornar o
valor que foi inserido
pelo comando SQL. No
UPDATE
o
comportamento ser o
mesmo, pois retornar o
valor do campo com as
alteraes que foram
feitas.
Pgina 11
PL/SQL ORACLE
BEFORE
:OLD.Nome_do_Campo
Retorna o contedo antigo do
campo e nunca pode ser alterado.
No INSERT no utilizado pois no
h valores antigos a insero.
AFTER
Se a operao for
UPDATE retornar o
valor do campo antes da
alterao. No DELETE
retornar o contedo do
campo atual.
Se a operao for
UPDATE retornar o
valor do campo antes da
alterao. No DELETE
retornar o contedo do
campo atual.
Quando existem diversas triggers a serem executadas (before ou after), no se pode

dizer qual ser a ordem de execuo dessas triggers.
Triggers
BEFORE
Constraint
s
Gravao
Triggers
AFTER
Recomenda-se o uso das triggers before para correo dos dados gravados em :new,
pois esse o momento ideal para isso. Consistncia de valores, checagem de dados aps a
gravao e operaes similares deve-se usar triggers after.
Declarando variveis dentro de uma trigger

A varivel V1 do tipo numrica.
A varivel V2 do tipo coluna de uma tabela, no exemplo, da tabela ITEM campo
VAL_UNITARIO.
A varivel V3 do tipo linha de uma tabela, no exemplo, da tabela ITEM.
CREATE OR REPLACE trg_nome
BEFORE INSERT OR DELETE OR UPDATE ON tabela
FOR EACH ROW
DECLARE
V1 NUMBER;
V2 ITEM.VAL_UNITARIO%TYPE;
V3 ITEM%ROWTYPE;
BEGIN
...
END;
Pgina 12
PL/SQL ORACLE
Gerando uma mensagem de erro

Usando trigger para restringir operaes em uma tabela. No exemplo abaixo, ir exibir
uma mensagem de erro quando a operao diferente de uma insero.
CREATE OR REPLACE TRIGGER trg_nome
BEFORE INSERT OR DELETE OR UPDATE ON tabela
FOR EACH ROW
BEGIN
IF NOT INSERTING THEN
RAISE_APPLICATION_ERROR (-20000, 'Erro: no permitida est
operao!');
END IF;
END;
Exemplo I
O exemplo seguinte usa as constantes condicionais para determinar qual tipo de
comando SQL foi feito na tabela classified_table:
CREATE TRIGGER audit_trigger
BEFORE INSERT OR DELETE OR UPDATE ON classified_table
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO audit_table
VALUES (USER || ' inseriu ' || ' novo valor: ' ||
:new.campo);
ELSIF DELETING THEN
VALUES (USER || ' apagou ' || ' valor antigo: ' ||
:old.campo);
ELSIF UPDATING('FORMULA') THEN
VALUES (USER || ' alterou ' || ' formula antiga: ' ||
:old.formula || ' formula nova: ' || :new.formula);
ELSIF UPDATING THEN
Pgina 13
PL/SQL ORACLE
VALUES (USER || ' alterou ' || ' valor antigo: ' ||

:old.campo || ' valor novo: ' || :new.campo);
END IF;
END;
Exemplo II
Este exemplo cria uma trigger que s permite alteraes na tabela EMP sejam feitas
somente no horrio comercial:
CREATE TRIGGER scott.emp_permit_changes
BEFORE DELETE OR INSERT OR UPDATE ON scott.emp
DECLARE
dummy
INTEGER;
BEGIN
/* Se for Sbado ou Domingo retorna ERROR.*/
IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR TO_CHAR(SYSDATE, 'DY') =
'SUN') THEN
raise_application_error( -20501, 'Voc no pode alterar
funcionrio no fim de semana');
END IF;
/*Se o horrio atual for menor que 8:00AM ou maior 6:00PM, ento
retorna ERRO. */
IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR TO_CHAR(SYSDATE, 'HH24') >=
18)
THEN
raise_application_error(
funcionrio no horrio do expediente');
-20502,
'S
pode
alterar
END IF;
END;
Exemplo III
Este exemplo cria uma trigger que quando um novo funcionrio adicionado ou o
salrio de um j existente alterado, garante que o valor do salrio esteja na faixa da funo do
funcionrio:
CREATE TRIGGER scott.salary_check
BEFORE INSERT OR UPDATE OF sal, job ON scott.emp
FOR EACH ROW
WHEN (new.job <> 'PRESIDENT')
DECLARE
Pgina 14
PL/SQL ORACLE
minsal NUMBER;
maxsal NUMBER;
BEGIN
/* Pega o menor e o maior salrio da funo.
SELECT minsal, maxsal
*/
INTO minsal, maxsal FROM sal_guide
WHERE job = :new.job;

/* Se o salrio menor que o mnimo ou maior que o mximo gera
um ERRO */
IF (:new.sal < minsal OR :new.sal > maxsal) THEN
raise_application_error( -20601, 'Salario ' || :new.sal || '
fora da faixa para ' || :new.job || ' do funcionario ' || :new.ename );
END IF;
END;
Gerao automtica de chave primria

Os valores NEXTVAL e CURRVAL de uma sequence podem ser usados em uma trigger
para gerar chave primria automtica.
Exemplo I
O comando seguinte incrementa a sequence ZSEQ e retorna o novo valor:
CREATE TRIGGER GeraNumeroNota
BEFORE INSERT ON Nota_Fiscal
FOR EACH ROW
BEGIN
SELECT seq_nota_fiscal.nextval INTO :new.nr_nota FROM dual;
END;
Exemplo II
Gera chave primria para a tabela Cliente caso no tenha sido informado no comando
de insero.
CREATE OR REPLACE TRIGGER trg_cliente_pk
BEFORE INSERT ON CLIENTE
FOR EACH ROW
BEGIN
IF :NEW.cod_cli is null THEN
SELECT seq_cliente.nextval INTO :new.cod_cli FROM dual;
Pgina 15
PL/SQL ORACLE
END IF;
END;
Exemplo III
Gera chave primria para a tabela Fatura , mas dependendo da ltima fatura da filial na tabela
FILIAL.
CREATE OR REPLACE TRIGGER trg_fatura_pk
BEFORE INSERT ON FATURA
FOR EACH ROW
BEGIN
SELECT nvl(curr_fat, 0) + 1 INTO :next_nr FROM FILIAL
WHERE cod_fil = :new.cod_fil;
END;
Outra maneira de implementar a trigger anterior:
CREATE OR REPLACE TRIGGER trg_fatura_pk
BEFORE INSERT ON FATURA
FOR EACH ROW
BEGIN
UPDATE filial SET curr_fat = curr_fat +1
WHERE cod_fil = :new.cod_fil
RETURNING curr_fat INTO :NEW.nr_fat;
END;
Alter Trigger
Para habilitar ou desabilitar uma trigger.
Sintaxe
ALTER TRIGGER [schema.]trigger

{ ENABLE
| DISABLE }
Pgina 16
PL/SQL ORACLE
Parmetros
schema
Usurio dono da trigger.
Trigger
Nome da trigger a ser alterada.
ENABLE
Habilita a trigger.
DISABLE
Desabilita a trigger.
Exemplo
ALTER TRIGGER reorder
DISABLE;
ALTER TRIGGER reorder
ENABLE;
Drop Trigger
Para remover uma trigger da base.
Sintaxe
DROP TRIGGER [schema.]trigger

Parmetros
schema
Usurio dono da trigger.
trigger
Nome da trigger a ser removida.
Exemplo
Este exemplo remove uma trigger do usurio RUTH:
DROP TRIGGER ruth.reorder
Create Function
Cria uma funo de usurio.
Uma funo um conjunto de comandos PL/SQL normalmente utilizados para efetuar
um clculo ou manipular um valor. Podem ser utilizadas dentro de comandos SQL. Nas functions
Pgina 17
PL/SQL ORACLE
no aconselhavel fazer alteraes no status da base, por exemplo, incluir, alterar, deletar
registros. Ao alterar o status do banco, esta funo no poder ser utilizada dentro de comandos
SQL. Assim a utilidade da function descaracterizada, assumindo a funo da procedure que
prpria para alterar dados.
Sintaxe
CREATE [OR REPLACE] FUNCTION [schema.]function
[ (argument [IN] datatype
[, argument [IN] datatype] ...)]
RETURN datatype
{IS | AS} pl/sql_subprogram_body
Parmetros
OR REPLACE
Recria a funo se esta j existir.
schema
Dono da funo.
function
Nome da funo
argument
Nome de um parmetro da funo.
IN
Indica que voc pode fornecer um valor no

momento da chamada da funo.
datatype
o tipo de dado do parmetro
RETURN
datatype
Especifica o tipo de dado que a funo deve

retornar.
pl/sql_subprogram_body
o cdigo da funo.
Exemplo I
Funo que calcula a media dos salrio de um determinado departamento:
CREATE FUNCTION cal_media(p_deptno IN NUMBER)
RETURN NUMBER IS v_media number;
BEGIN
SELECT avg(sal)
INTO v_media
FROM emp
WHERE dept_no = p_deptno;
RETURN(v_media);
END;
A utilizao da funo criada acima em um comando SQL:
Pgina 18
PL/SQL ORACLE
SELECT cal_media(deptno) FROM dept;

Exemplo II
CREATE OR REPLACE FUCTION media(p_dept number DEFAULT -1)
RETURN NUMBER IS v_media number;
BEGIN
IF p_dept = -1 then
SELECT avg(salario) INTO v_media
FROM funcionario;
ELSE
SELECT avg(salario) INTO v_media
FROM funcionario WHERE cod_dep = p_dept;
END IF;
RETURN (v_media);
END;
Drop Function
Remove uma funo da base.
Exemplo
DROP FUNCTION cal_media;
Cursor
Permite uma navegao registro a registro nas linhas retornadas por um comando
SELECT.
Sintaxe
CURSOR cursor_name [(parameter[,parameter]...)]

IS select_statement;
Pgina 19
PL/SQL ORACLE
Exemplo I
DECLARE
my_sal emp.sal%TYPE;
factor INTEGER:=2;
CURSOR c1(p_Job varchar2) IS SELECT factor*sal FROM emp
WHERE job=p_Job;
BEGIN
...
OPEN c1(ANALISTA);
LOOP
FETCH c1 INTO my_sal;
EXIT WHEN c1%NOT FOUND;
...
END LOOP;
CLOSE c1;
END;
Pgina 20
PL/SQL ORACLE
Exemplo II
DECLARE
Cursor c_cliente IS
Select * from Clientes order by Nome_Cli;
Cursor c_Fatura(p_cli number) IS
Select * from Fatura where cod_cli = p_cli;
R_Cliente c_cliente%rowtype;
R_Fatura c_fatura%rowtype;
BEGIN
OPEN c_cliente;
LOOP
FETCH c_cliente INTO R_cliente;
EXIT When c_cliente%NOT FOUND;
OPEN c_fatura(R_cliente.cod_cli);
DBMS_OUTPUT.PUT_LINE (R_cliente.Nome);
LOOP
FETCH c_fatura INTO R_fatura;
EXIT When c_Fatura%NOT FOUND;
DBMS_OUTPUC.PUT_LINE (R_Fatura.Data);
END LOOP;
CLOSE c_Fatura;
END LOOP;
CLOSE c_Cliente;
END;
Cursos For Loop

Permite uma navegao registro a registro nas linhas retornadas por um comando
SELECT, sem a necessidade de uma definio prvia.
Exemplo I
DECLARE
BEGIN
...
For R1 in (SELECT factor*sal FROM emp WHERE job=p.job) LOOP
...
Pgina 21
PL/SQL ORACLE
END LOOP;
END;
Exemplo II
DECLARE
BEGIN
For R_cliente IN (Select * from Cliente order by Nome_cli)
LOOP
DBMS_OUTPUT.PUT_LINE(R_cliente.Nome_Cli);
For R_Fatura IN (Select * from Fatura
Where cod_cli = R_Cliente.cod_cliente) LOOP
DBMS_OUTPUT.PUT_LINE(...);
END LOOP;
END LOOP;
END;
COMANDO WHILE .. LOOP

DECLARE
X NUMBER(3);
Y VARCHAR2(30);
K DATE;
J NUMBER(3);
BEGIN
X:= 0;
WHILE X<= 100 LOOP
K:= SYSDATE-X;
Y := 30;
INSERT INTO TESTE VALUES
(X,Y,K);
X := X + 1;
END LOOP;
COMMIT;
END;
Pgina 22
PL/SQL ORACLE
Exceptions
Bloco de tratamento de erros.
Sintaxe
DECLARE
past_due EXCEPTION;
BEGIN
...
...
IF ... THEN
RAISE past_due; - esse no tratado
END IF;
...
EXCEPTION
WHEN past_due THEN - no suporta excees HANDLE
...
WHEN OTHERS THEN
...
END;
Exemplo
DECLARE
deadlock_detected EXCEPTION ;
PRAGMA EXCEPTION_INIT (deadlock_detected,-60);
BEGIN
...
raise_application_error(-60,Mensagem do Usuario);
...
EXCEPTION
WHEN deadlock_detected THEN
... - tratamento do erro
...
END;
DECLARE minha_excep EXCEPTION;
No so todos os erros que

tem exception, para poder
fazer o tratamento de erro
deles
preciso
criar
Pgina 23
PL/SQL ORACLE
exception e vincular o nmero

do erro com a exception
criada.
PRAGMA EXCEPTION_INIT
(nome_da_exception,
num_erro_relacionado)
Relaciona o nmero do erro

com a exception criada no
declare.
RAISE_APPLICATION_ERROR(
Provoca o erro mostrando a

mensagem e o nmero
fornecidos.
-60,Mensagem de Erro);
Outros EXCEPTIONS
NO_DATA_FOUND -
Quando um select no retorna nenhuma linha
TOO_MANY_ROWS - Quando um select retorna mais de uma linha

OTHERS - Qualquer tipo de erro
DECLARE
NOME CHAR(15);
CARGO CHAR(10);
BEGIN
SELECT ENAME, JOB INTO NOME, CARGO
FROM EMP
WHERE EMPNO = 1111;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO PROBLEMA (ERRO,DTERRO)
VALUES (REGISTRO INEXISTENTE,SYSDATE);
WHEN TOO_MANY_ROWS THEN
VALUES (MUITOS REGISTROS,SYSDATE);
WHEN OTHERS THEN
VALUES (OUTRO ERRO QUALQUER,SYSDATE);
END;
Pgina 24
PL/SQL ORACLE
OUTRO EXEMPLO
DECLARE
NM VARCHAR2(30);
BEGIN
SELECT NOME INTO NM
FROM TESTE
WHERE IDADE=30;
DBMS_OUTPUT.PUT_LINE(NM);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('IDADE INEXISTENTE');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('MULTIPLAS LINHAS');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OUTRA SAIDA');
END;
RETORNANDO ERROS
SQLERR - Retorna o nmero do erro
SQLERRM Retorna o nmero e a descrio do erro
Exemplo
BEGIN
INSERT INTO TESTE VALUES
(50,45,SYSDATE);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('ERRO - '||SQLERRM);
END;
EXCEPTION
DUP_VAL_ON_INDEX - Chave Duplicada

INVALID_CURSOR - Operao Ilegal com Cursor
INVALID_NUMBER - Converso invlida p/numrico
LOGIN_DENIED - Usurio/Senha Invlida
NO_DATA_FOUND - Nenhuma linha retornada
NOT_LOGGED_ON - Usurio no conectado
OTHERS - Erro no declarado em exceptions
PROGRAM_ERROR - Problema Interno
STORAGE_ERROR - Falta de Memria
TIMEOUT_ON_RESOURCE - Tempo de espera
TOO_MANY_ROWS - Retorna Muitas Linhas
TRANSACTION_BACKED_OUT - Volta Atrs uma transao
VALUE_ERROR - Erro Converso,Expresso
ZERO_DIVIDE - Diviso por zero
Pgina 25
PL/SQL ORACLE
FUNES DE ERRO
sqlerrm, sqlcode
Create Procedure
Para criar uma procedure. Uma procedure um grupo de comandos PL/SQL que
realizam uma tarefa.
Sintaxe
CREATE [OR REPLACE] PROCEDURE [schema.]procedure

[(argument[IN I OUT I IN OUT] datatype
[,argument [ IN I OUT I IN OUT] datatype]...)]
{IS I AS} pl/sql_subprogram_body
Parmetros
OR REPLACE
Recria a procedure se esta j existir.
schema
Dono da procedure.
procedure
Nome da procedure.
argument
Parmetro da procedure.
IN
Indica que voc deve especificar o valor deste parmetro

na chamada da procedure.
OUT
Indica que a procedure ira retornar um valor por este

parmetro.
IN OUT
Atravs deste parmetro deve-se informar um valor para

procedure e este voltara modificado.
datatype
Tipo de dado do parmetro
pl/sql_subprogram_body
Codificao da procedure
Pgina 26
PL/SQL ORACLE
Exemplo que cria a procedure totaliza_nota:

CREATE PROCEDURE totaliza_nota (p_cod_cliente IN number)
AS BEGIN
UPDATE nota_fiscal
SET val_total = val_total + (SELECT sum(valor * qtd) FROM itens WHERE itens.nr_nota =
nota_fiscal.nr_nota)
dt_processamento = sysdate
WHERE dt_processamento is NULL and cod_cliente = p_cod_cliente;
COMMIT;
END;
Alter Procedure
Para recompilar uma procedure.
Sintaxe
ALTER PROCEDURE [schema.]procedure COMPILE
Drop Procedure
Para remover uma procedure da base.
Sintaxe
DROP PROCEDURE [schema.]procedure
Executando Procedure
Exemplo
BEGIN
NomeProc(Param);
END;
Ou
EXEC NomeProc(Param);
Pgina 27
PL/SQL ORACLE
Dentro de um bloco:
BEGIN
NomeProc(Param);
END;
Create Package
Cria uma package. Uma package a especificao de uma coleo de procedures, functions,
e outros objetos de programa com objetivos afins. Parte pblica da Package, visvel para o
mundo externo.
Sintaxe
CREATE [OR REPLACE] PACKAGE [schema].package

{IS I AS} pl/sql_package_spec
Parmetros
OR REPLACE
Recria a package se esta j existe
schema
Nome do usurio dono da package.
package
Nome da packge.
pl/sql_package_s
pec
a especificao da package.
Exemplo
Este comando cria a package EMP_MGMT:
CREATE PACKAGE emp_mgmt AS
FUNCTION hire(ename VARCHAR2, job VARCHAR2, mgr NUMBER,
sal NUMBER, comm NUMBER, deptno NUMBER)
RETURN NUMBER;
FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2)
RETURN NUMBER;
PROCEDURE remove_emp(empno NUMBER);
PROCEDURE remove_dept(deptno NUMBER);
PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER);
PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER);
no_comm EXCEPTION;
no_sal EXCEPTION;
END emp_mgmt;
Pgina 28
PL/SQL ORACLE
Pgina 29
PL/SQL ORACLE
Create Package Body

Cria o corpo de uma package, onde corpo a implementao do cdigo. Parte privada
da Package, o mundo externo poder utilizar as funcionalidades da Package declaradas no
passo anterior, mas no visualizar o seu cdigo. No Body da Package poder ter mais
funcionalidades do que a declarao da Package, assim estas no estaro disponveis para o
mundo externo sero visveis apenas dentro da Package Body.
Sintaxe
CREATE [OR PEPLACE] PACKAGE BODY [schema.]package

{IS | AS} pl/sql_package_body
Parmetros
OR REPLACE
Recria o corpo da package se este j existir
schema
Usurio dono da package
package
Nome da package a ser implementado.
pl/sql_package_body
Codificao do corpo da package
Exemplo
Este exemplo cria o corpo da packge EMP_MGMT:
CREATE PACKAGE BODY emp_mgmt AS
tot_emps
NUMBER;
tot_depts NUMBER;
FUNCTION hire(ename VARCHAR2, job VARCHAR2, mgr NUMBER,
sal NUMBER, comm NUMBER, deptno NUMBER)
RETURN NUMBER IS
new_empno NUMBER(4);
BEGIN
SELECT empseq.NEXTVAL
INTO new_empno
FROM DUAL;
INSERT INTO emp
VALUES (new_empno, ename, job, mgr, sal, comm, deptno,
tot_emps := tot_emps + 1;
RETURN(new_empno);
END;
Pgina 30
PL/SQL ORACLE
FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2)

RETURN NUMBER IS
new_deptno NUMBER(4);
BEGIN
SELECT deptseq.NEXTVAL
INTO new_deptno
FROM dual;
INSERT INTO dept
VALUES (new_deptno, dname, loc);
tot_depts := tot_depts + 1;
RETURN(new_deptno);
END;
PROCEDURE remove_emp(empno NUMBER) IS
BEGIN
DELETE FROM emp
WHERE emp.empno = remove_emp.empno;
tot_emps := tot_emps - 1;
END;
PROCEDURE remove_dept(deptno NUMBER) IS

BEGIN
DELETE FROM dept
WHERE dept.deptno = remove_dept.deptno;
tot_depts := tot_depts - 1;
SELECT COUNT(*)
INTO tot_emps
FROM emp;
END;
Pgina 31
PL/SQL ORACLE
PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER) IS

curr_sal NUMBER(7,2);
BEGIN
SELECT sal
INTO curr_sal
FROM emp
WHERE emp.empno = increase_sal.empno;
IF curr_sal IS NULL
THEN RAISE no_sal;
ELSE
UPDATE emp
SET sal = sal + sal_incr
WHERE empno = empno;
END IF;
END;
PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER) IS
curr_comm NUMBER(7,2);
BEGIN
SELECT comm
INTO curr_comm
FROM emp
WHERE emp.empno = increase_comm.empno
IF curr_comm IS NULL
THEN RAISE no_comm;
ELSE
UPDATE emp
SET comm = comm + comm_incr;
END IF;
END;
END emp_mgmt
Pgina 32
PL/SQL ORACLE
Alter Package
Recompila uma package.
Sintaxe
ALTER PACKAGE [schema.]package

COMPILE [ PACKAGE | BODY]
Pgina 33

Apostila de PL - SQL - Alexandre

Enviado por

Direitos autorais:

Formatos disponíveis

Apostila de PL - SQL - Alexandre

Enviado por

Dados do documento

Descrição original:

Título original

Direitos autorais

Formatos disponíveis

Compartilhar este documento

Compartilhar ou incorporar documento

Opções de compartilhamento

Você considera este documento útil?

Este conteúdo é inapropriado?

Direitos autorais:

Formatos disponíveis

Apostila de PL - SQL - Alexandre

Enviado por

Direitos autorais:

Formatos disponíveis

PL/SQL

Alexandre Costa Vieira

[Alexandre Costa Vieira PL/SQL ORACLE]

[Alexandre Costa Vieira PL/SQL ORACLE]

[Alexandre Costa Vieira PL/SQL ORACLE]

Recria a view se ela j existir.

Cria a view mesmo se o objeto referenciado

Cria a view somente se o objeto referenciado

Usurio dono da view

Especifica nomes para as expresses

Qualquer comando SELECT, sem as

WITH CHECK OPTION

O nome associado check option constraint

WITH READ ONLY

[Alexandre Costa Vieira PL/SQL ORACLE]

[Alexandre Costa Vieira PL/SQL ORACLE]

a) SEO DE DECLAO (DECLARE) - Nesta seo so definidos os objetos

Nome do usurio dono da sequence

Indica o intervalo entre os nmeros gerados.

Valor mnimo que a sequence pode assumir

Valor mximo que a sequence pode assumir

O primeiro valor gerado pela sequence

Indica que esta uma sequence cclica. Isto

[Alexandre Costa Vieira PL/SQL ORACLE]

Indica quantos valores da sequence o

No ter valores pr alocados

Garante que o numero gerado pela sequence

No garante que os nmeros seqenciais

CURRVAL Retorna o ultimo valor da sequence gerado para a sesso

Incrementa a sequence e retorna o novo valor

CREATE SEQUENCE teste_seq

CREATE SEQUENCE seq_cod_func

START WITH 30;

SELECT seq_cod_cli.currval FROM dual;

WHERE CD_PROD = 30;

CREATE [OR REPLACE] TRIGGER [schema.]trigger

[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}] ...

Recria a trigger se esta j existir.

Nome do usurio dono da trigger

Nome da tabela qual a trigger esta associada

Nome da trigger que esta sendo criada.

Indica ao ORACLE para disparar a trigger antes de

Indica ao ORACLE para disparar a trigger depois de

Indica que esta trigger deve ser disparada quando um

Indica que esta trigger deve ser disparada quando um

Indica que esta trigger deve ser disparada quando um

Usado para nomes correlacionados. Voc pode usar

[Alexandre Costa Vieira PL/SQL ORACLE]

correlao de nomes em blocos PL/SQL e em

Indica que a trigger deve ser disparada a cada linha

Contm uma condio SQL que deve ser verdadeira

Bloco PL/SQL que indica a ao a ser executada pela

TRUE se o comando que disparou a trigger foi um

TRUE se o comando que disparou a trigger foi um

TRUE se o comando que disparou a trigger foi um

[Alexandre Costa Vieira PL/SQL ORACLE]

Quando existem diversas triggers a serem executadas (before ou after), no se pode

Declarando variveis dentro de uma trigger