Prof. José Fernando Rodrigues Júnior Pacotes

1. SCC0141 - Bancos de Dados e Suas Aplicações Prof. José Fernando Rodrigues Júnior Pacotes Material original: Profa. Elaine Parros Machado de Sousa

2. Packages • Pacote: objeto do esquema que armazena tipos, variáveis, exceções e subprogramasrelacionados • 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

3. 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

4. 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 BODYPacoteAlunoAS PROCEDUREretira_aluno (p_aluno Aluno.NUSP%TYPE) AS BEGIN DELETE FROM Aluno WHERE NUSP = p_aluno; IF SQL%NOTFOUND THEN RAISE e_naoEncontrado; END IF; ENDretira_aluno; ENDPacoteAluno;

5. 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;

6. 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;

7. 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;

8. 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)

9. 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;

10. 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;

11. 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; • Dois problemas: • O cursor normal não é parametrizável, eu não posso definir um outro valor de CPF para a seleção dos dados • Se eu quiser os dados selecionados pelo cursor (além apenas da contagem), eu não consigo

12. 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;

13. 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;

14. 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; Aqui calculamos o total. Aqui recuperamos dados.

15. 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;

16. Packages • Manuais de Consulta • SQL Reference • PL-SQLUser’s Guide and Reference • PL/SQL Packages and Types Reference