PL-SQL-05_Pacotes

SCC0141 - Bancos de Dados e
Suas Aplicações
Prof. José Fernando Rodrigues Júnior
Pacotes
Material original: Profa. Elaine Parros Machado de Sousa
Packages

Pacote: objeto do esquema que armazena tipos,
variáveis, exceções e subprogramas relacionados





modularidade
funcionalidade
informação pública e privada
performance
2 partes armazenadas separadamente no dicionário de
dados:

especificação do pacote – público


interface para as aplicações
corpo do pacote – privado

pode não existir
Packages

Instanciação do pacote


ocorre na primeira vez em que um elemento do
pacote é referenciado
alocação de memória para:


código objeto
variáveis definidas no pacote

cada sessão tem sua própria cópia das variáveis
empacotadas
Especificação e Corpo do Pacote
/* especificação do pacote */
CREATE OR REPLACE PACKAGE
PacoteAluno AS
/* pode ser IS*/
e_naoEncontrado EXCEPTION;
PROCEDURE retira_aluno (p_aluno Aluno.NUSP%TYPE);
END PacoteAluno;
/* para compilar especificação e corpo juntos*/
/* corpo do pacote */
CREATE OR REPLACE PACKAGE BODY PacoteAluno AS
PROCEDURE retira_aluno (p_aluno Aluno.NUSP%TYPE) AS
BEGIN
DELETE FROM Aluno WHERE NUSP = p_aluno;
IF SQL%NOTFOUND THEN RAISE e_naoEncontrado;
END IF;
END retira_aluno;
END PacoteAluno;
Usando o pacote…
DEClARE
v_aluno Matricula.Aluno%TYPE;
BEGIN
v_aluno := 222;
PacoteAluno.retira_aluno(v_aluno);
dbms_output.put_line('Aluno removido!');
EXCEPTION
WHEN PacoteAluno.e_naoEncontrado THEN
dbms_output.put_line('Aluno não encontrado');
END;
Exemplo – Público vs. Privado Escopo
/* Especificação do pacote*/
CREATE OR REPLACE PACKAGE
PacoteAluno AS
/*Itens PÚBLICOS – visíveis dentro e fora do pacote*/
TYPE T_MEDIA IS RECORD (
limpa NUMBER,
completa NUMBER);
PROCEDURE relatorio_aluno(p_aluno Aluno.NUSP%TYPE);
END PacoteAluno;
CREATE OR REPLACE PACKAGE BODY PacoteAluno AS
/*Itens PRIVADOS – visíveis somente dentro do pacote, pois não estão na
especificação, apenas no corpo*/
v_media T_MEDIA;
/*Forward declaration – função privada*/
FUNCTION media (p_aluno Matricula.Aluno%TYPE) RETURN T_MEDIA;
/*Procedimento presente na definição do pacote - publica*/
PROCEDURE relatorio_aluno (p_aluno Aluno.NUSP%TYPE) AS
BEGIN
CURSOR c_aluno IS SELECT ...; /*local*/
v_media := media(p_aluno);
....
END relatorio_aluno;
/*Função privada*/
FUNCTION media (p_aluno Matricula.Aluno%TYPE) RETURN T_MEDIA IS
BEGIN
.....
END media;
/*Código de Inicialização do Pacote*/
BEGIN
v_media := 0;
END PacoteAluno;
Packages

Funções e procedimentos dentro do pacote
podem ser sobrecarregados


útil para aplicar a mesma operação a diferentes
tipos de dados
restrições:



parâmetros não podem diferir apenas no nome ou no
modo (IN – OUT)
funções não podem diferir apenas no tipo de retorno
parâmetros não podem diferir apenas em tipos da
mesma família (ex: CHAR e VARCHAR2)
REF CURSORS

Cursor normal em um pacote:
create or replace PACKAGE CURSORES AS
CURSOR c_cursor_normal IS
SELECT COUNT(*)
FROM L01_MORADOR
WHERE MCPF > 3;
PROCEDURE UsaCursor(pTotal OUT NUMBER);
END;
REF CURSORS
Cursor normal em um pacote:

CREATE OR REPLACE PACKAGE BODY CURSORES AS
PROCEDURE UsaCursor(pTotal OUT NUMBER) AS
BEGIN
OPEN c_cursor_normal;
FETCH c_cursor_normal INTO pTotal;
CLOSE c_cursor_normal;
END UsaCursor;
END CURSORES;
REF CURSORS
Doisproblemas:
Cursor normal
em um pacote:
CREATE OR REPLACE PACKAGE BODY CURSORES AS
1) O
cursor normal não é parametrizável, eu não posso
definir um outro valor de CPF para a seleção dos
PROCEDURE UsaCursor(pTotal OUT NUMBER) AS
dados
BEGIN
OPEN c_cursor_normal;
FETCH c_cursor_normal INTO pTotal;
2) Se eu
quiser
os dados selecionados pelo cursor (além
CLOSE
c_cursor_normal;
apenas da contagem), eu não consigo
END UsaCursor;
END CURSORES;
REF CURSORS


Solução: REF CURSORS
Continuando nosso pacote CURSORES
create or replace PACKAGE CURSORES AS
TYPE TIPO_REF_CURSOR IS REF CURSOR;
CURSOR c_cursor_normal IS
SELECT COUNT(*)
FROM L01_MORADOR
WHERE MCPF > 3;
PROCEDURE UsaCursor(pTotal OUT NUMBER);
PROCEDURE UsaCursor(pTotal OUT NUMBER,
p_cursor_de_saida OUT
TIPO_REF_CURSOR,
p_valor_CPF IN NUMBER);
END;
REF CURSORS

Cursor normal em um pacote:
CREATE OR REPLACE PACKAGE BODY CURSORES AS
PROCEDURE UsaCursor(pTotal OUT NUMBER) AS
BEGIN
...
END UsaCursor;
PROCEDURE UsaCursor(pTotal OUT NUMBER, p_cursor_de_saida OUT TIPO_REF_CURSOR,
p_valor_CPF IN NUMBER) AS
BEGIN
OPEN p_cursor_de_saida FOR
SELECT COUNT(*)
FROM L01_MORADOR
WHERE MCPF > p_valor_CPF;
FETCH p_cursor_de_saida INTO pTotal;
CLOSE p_cursor_de_saida;
OPEN p_cursor_de_saida FOR
SELECT *
FROM L01_MORADOR
WHERE MCPF > p_valor_CPF;
--não feche o cursor aqui dentro, senão ele será inútil lá fora
--CLOSE p_cursor_de_saida;
END UsaCursor;
END CURSORES;
REF CURSORS

Cursor normal em um pacote:
CREATE OR REPLACE PACKAGE BODY CURSORES AS
PROCEDURE UsaCursor(pTotal OUT NUMBER) AS
BEGIN
...
END UsaCursor;
PROCEDURE UsaCursor(pTotal OUT NUMBER, p_cursor_de_saida OUT TIPO_REF_CURSOR,
p_valor_CPF IN NUMBER) AS
BEGIN
OPEN p_cursor_de_saida FOR
SELECT COUNT(*)
FROM L01_MORADOR
WHERE MCPF > p_valor_CPF;
FETCH p_cursor_de_saida INTO pTotal;
CLOSE p_cursor_de_saida;
Aqui calculamos o total.
OPEN p_cursor_de_saida FOR
SELECT *
FROM L01_MORADOR
WHERE MCPF > p_valor_CPF;
--não feche o cursor aqui dentro, senão ele será inútil lá fora
--CLOSE p_cursor_de_saida;
Aqui recuperamos dados.
END UsaCursor;
END CURSORES;
REF CURSORS
E como se usa isso?
set serveroutput on;
DECLARE
vTotal NUMBER;
vTotal2 NUMBER;
vTuplas CURSORES.TIPO_REF_CURSOR;
cTemp L01_MORADOR%ROWTYPE;
BEGIN
CURSORES.UsaCursor(vTotal);
dbms_output.put_line('Total: '|| vTotal);

CURSORES.UsaCursor(vTotal2,vTuplas,5);
dbms_output.put_line('Total: '|| vTotal2);
LOOP
FETCH vTuplas INTO cTemp;
EXIT WHEN vTuplas%NOTFOUND;
dbms_output.put_line(cTemp.mcpf);
END LOOP;
CLOSE vTuplas;
END;
Packages

Manuais de Consulta



SQL Reference
PL-SQL User’s Guide and Reference
PL/SQL Packages and Types Reference