A versão 2.3 do PL/SQL, disponível com o Oracle Server Versão 7.3, permite que se crie e utilize variáveis do tipo cursor. Da mesma forma que o cursor convencional, a variável do tipo cursor aponta para a linha corrente no conjunto de resultados de uma consulta que retorne múltiplas linhas, mas ao contrário do cursor estático, que está vinculado à uma única consulta, as variáveis cursor podem ser associadas a várias consultas, até mesmo dentro de um mesmo programa. Variáveis do tipo cursor são verdadeiras variáveis PL/SQL, você pode associar novos valores a ela e passa-la como argumento a subprogramas.
Uma variável do tipo cursor é como um ponteiro nas linguagens C e Pascal, ela guarda a posição na memória (endereço) de um objeto ao invés de guardar o próprio objeto. Portanto quando você declara uma variável do tipo cursor você está criando um ponteiro e não um objeto.
O principal benefício da variável do tipo cursor é que ela provê um mecanismo para se passar resultados de consultas entre diferentes programas PL/SQL, ou ainda entre programa PL/SQL cliente e programa servidor. Em um ambiente cliente/servidor, por exemplo, um programa do lado cliente poderia abrir a variável do tipo cursor e começar a extrair seus dados, e então passar essa variável como argumento a uma procedure armazenada no servidor. Este programa poderia então continuar a extrair seus dados e passar a variável de volta ao cliente para que este feche o cursor (vide exemplo 3).
As variáveis do tipo cursor permitem que você:
A criação de variáveis do tipo cursor é feita em duas etapas: primeiro você define um tipo REF CURSOR e então declara a variável como sendo daquele tipo.
A sintaxe para se criar um tipo de referência a cursor é a seguinte:
TYPE nome_tipo_cursor IS REF CURSOR [RETURN tipo_retornado];
Onde nome_tipo_cursor é o nome do tipo e tipo_retornado é a especificação do dado retornado pelo tipo cursor. O tipo_retornado pode ser qualquer estrutura válida para uma clausula RETURN de um cursor normal, definida usando o atributo %ROWTYPE ou referenciando um registro (record) previamente definido.
A clausula RETURN é opcional, e quando usada, o tipo é dito "forte" pois fica atado a um tipo record, ou tipo row. Qualquer variável do tipo cursor declarada de um tipo "forte" pode apenas ser utilizada com declarações SQL que retornem dados do mesmo tipo da declaração usada na clausula RETURN.
Por outro lado, o tipo cursor que não possui a clausula RETURN é dito "fraco" e pode ser utilizado de formas muito mais flexíveis, isto é, pode ser utilizado com consultas que retornem qualquer estrutura de dados.
Uma vez declarado o tipo REF CURSOR você pode declarar a variável daquele tipo, como mostrado no seguinte exemplo:
DECLARE -- Criando o tipo TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE; -- Criando a variável dept_cv DeptCurTyp; BEGIN ... END;
É importante lembrar que a declaração de uma variável do tipo cursor não cria um objeto cursor. Para que isto ocorra é necessário usar a sintaxe OPEN FOR, que cria um novo objeto cursor e o associa à variável.
Outra observação importante é que variáveis cursor não possuem persistência de estado por serem apenas ponteiros, não podendo portanto serem referenciadas após o fim da execução do procedimento que abriu o cursor.
A declaração OPEN - FOR associa o cursor com uma consulta que retorne múltiplas linhas, executa a consulta e identifica o conjunto de respostas. A sintaxe para a declaração é:
OPEN {nome_variável_cursor | :variável_cursor_de_ambiente} FOR declaração_sql;
onde variável_cursor_de_ambiente é uma variável declarada em um ambiente PL/SQL como o SQL*Plus ou programa Pro*C, e declaração_sql é qualquer declaração SELECT que não possua a clausula FOR UPDATE.
Outras declarações OPEN - FOR podem abrir a mesma variável do tipo cursor para diferentes consultas, não sendo necessário para isto fecha-lo antes.
Assim como com cursores estáticos, para se obter o resultado da consulta é utilizada a declaração FETCH que extrai as linhas uma a uma da variável do tipo cursor, e possui a seguinte sintaxe:
FETCH {nome_variável_cursor | :variável_cursor_de_ambiente} INTO registro;
FETCH {nome_variável_cursor | :variável_cursor_de_ambiente} INTO variável1 [,variável2 ...];
Quando a variável do tipo cursor foi declarada como sendo de um tipo "forte", o compilador PL/SQL verifica se a estrutura dos dados após a clausula INTO são compatíveis com a estrutura da consulta associada à variável do tipo cursor, verifica também se o número de variáveis é correspondente ao número de colunas retornadas pela consulta. Caso contrário será gerado um erro.
O erro irá ocorrer em tempo de compilação se a variável for de um tipo "forte" e em tempo de execução se a variável for de um tipo "fraco". Em tempo de execução, a PL/SQL evoca a exception ROWTYPE_MISMATCH.
A declaração CLOSE desabilita a variável do tipo cursor. Depois disto o conjunto de resultados associado é indefinido. A sintaxe é a seguinte:
CLOSE {nome_variável_cursor | :variável_cursor_de_ambiente};
Este exemplo cria uma package chamada LOJA com a procedure PRODUTO que retorna os dados referentes ao produto passado como argumento. Esta procedure utiliza uma variável do tipo cursor para fazer a consulta dependendo do tipo do produto, se o produto for um livro (código 1) o cursor será associado a uma consulta à tabela LIVROS, caso o produto seja um disco (código 2) o cursor será associado a uma consulta à tabela DISCOS.
-- =================================================
-- PACKAGE ESPECIFICATION
-- =================================================
CREATE OR REPLACE PACKAGE LOJA IS
PROCEDURE PRODUTO(título_in IN VARCHAR2);
END LOJA;
/
-- =================================================
-- PACKAGE BODY
-- =================================================
CREATE OR REPLACE PACKAGE BODY LOJA IS
FUNCTION QUAL_CODIGO(título_in IN VARCHAR2) RETURN NUMBER IS
código_produto NUMBER;
BEGIN
SELECT codigo INTO código_produto
FROM TITULOS
WHERE TITULO = título_in;
RETURN código_produto;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END QUAL_CODIGO;
PROCEDURE PRODUTO(título_in IN VARCHAR2) IS
TYPE tipo_cursor IS REF CURSOR; -- Tipo cursor (um tipo "fraco")
var_cursor tipo_cursor; -- A variável cursor
código_produto NUMBER;
livros_rec LIVROS%ROWTYPE; -- Um record
discos_rec DISCOS%ROWTYPE; -- Um record
BEGIN
código_produto := QUAL_CODIGO(título_in);
IF código_produto IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Produto não cadastrado.');
ELSE
IF código_produto = 1 THEN
OPEN var_cursor FOR SELECT * FROM LIVROS WHERE TITULO = título_in;
FETCH var_cursor INTO livros_rec;
DBMS_OUTPUT.PUT_LINE('TÍTULO : '||livros_rec.titulo);
DBMS_OUTPUT.PUT_LINE('EDITORA : '||livros_rec.editora);
DBMS_OUTPUT.PUT_LINE('PREÇO : '||livros_rec.preço);
ELSE
OPEN var_cursor FOR SELECT * FROM DISCOS WHERE TITULO = título_in;
FETCH var_cursor INTO discos_rec;
DBMS_OUTPUT.PUT_LINE('TÍTULO : '||discos_rec.titulo);
DBMS_OUTPUT.PUT_LINE('PREÇO : '||discos_rec.preço);
END IF;
CLOSE var_cursor;
END IF;
END PRODUTO;
END LOJA;
/
A package pode ser criada, supondo-se que o código esteja no arquivo LOJA.SQL e que o ambiente utilizando seja o SQL*Plus, da seguinte forma:
SQL> @LOJA.SQL
E sua utilização é da forma:
SQL> EXECUTE LOJA.PRODUTO('ORACLE PL/SQL PROGRAMMING');
que retornaria:
TÍTULO : ORACLE PL/SQL PROGRAMMING EDITORA : O'REILLY & ASSOCIATES, INC. PREÇO : 12 PL/SQL procedure successfully completed.
Uma outra forma de utilização de variáveis do tipo cursor é mostrada neste segundo exemplo. É criada uma package com declarações de tipos para variáveis cursor e procedimentos para abrir o cursor e para extrair seus dados.
--======================================================================= -- PACKAGE ESPECIFICATION --======================================================================= CREATE OR REPLACE PACKAGE emp_data AS TYPE emp_val_cv_type IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT emp_val_cv_type,dept_number IN INTEGER); PROCEDURE fetch_emp_data (emp_cv IN emp_val_cv_type, emp_row OUT emp%ROWTYPE); END emp_data; / --======================================================================= -- PACKAGE BODY --======================================================================= CREATE OR REPLACE PACKAGE BODY emp_data AS --======================================================================= PROCEDURE open_emp_cv (emp_cv IN OUT emp_val_cv_type, dept_number IN INTEGER) IS BEGIN OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = dept_number; END open_emp_cv; --======================================================================= PROCEDURE fetch_emp_data (emp_cv IN emp_val_cv_type, emp_row OUT emp%ROWTYPE) IS BEGIN FETCH emp_cv INTO emp_row; END fetch_emp_data; END emp_data; /
Agora um bloco PL/SQL que utiliza as procedures da package para realizar consultas.
DECLARE -- Declara uma variável cursor do tipo definido na package emp_curs emp_data.emp_val_cv_type; dept_number dept.deptno%TYPE; emp_row emp%ROWTYPE; BEGIN dept_number := 20; -- "Abre" o cursor usando uma variável emp_data.open_emp_cv(emp_curs, dept_number); -- Extrai os dados e os exibe LOOP emp_data.fetch_emp_data(emp_curs, emp_row); EXIT WHEN emp_curs%NOTFOUND; DBMS_OUTPUT.PUT(emp_row.ename || ' '); DBMS_OUTPUT.PUT_LINE(emp_row.sal); END LOOP; END; /
Esta forma de implementação permite a reutilização do código da package em vários outros blocos e procedures sem a necessidade de se saber como está definida a consulta. Desta forma, se houver a necessidade de se alterar, por exemplo, a clausula WHERE da consulta associada ao cursor, apenas o código da package necessita ser alterado, os blocos e procedures que utilizarem esta package não precisam sofrer alteração.
O exemplo seguinte mostra a passagem de variáveis do tipo cursor como parâmetro à procedures. É criada uma package, contendo apenas os tipos cursores, e duas procedures. A primeira procedure recebe como parâmetro uma variável do tipo cursor e a quantidade de linhas a serem extraidas e exibe o nome do funcionário e seu salário, a segunda procedure que aceita os mesmos parâmetros da primeira exibe o nome, a função e o salário.
CREATE OR REPLACE PACKAGE TIPOS AS
TYPE emp_tipo_cur IS REF CURSOR RETURN EMP%ROWTYPE;
TYPE dept_tipo_cur IS REF CURSOR RETURN DEPT%ROWTYPE;
END TIPOS;
/
CREATE OR REPLACE PROCEDURE EXIBE_SALARIO (emp_vcursor_in IN tipos.emp_tipo_cur,
quant_in IN NUMBER) IS
emp_rec EMP%ROWTYPE;
BEGIN
FOR indice IN 1 .. quant_in LOOP
FETCH emp_vcursor_in INTO emp_rec;
EXIT WHEN emp_vcursor_in%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.ename||' '||emp_rec.sal);
END LOOP;
END EXIBE_SALARIO;
/
CREATE OR REPLACE PROCEDURE EXIBE_COMPLETO(emp_vcursor_in IN tipos.emp_tipo_cur,
quant_in IN NUMBER) IS
emp_rec EMP%ROWTYPE;
BEGIN
FOR indice IN 1 .. quant_in LOOP
FETCH emp_vcursor_in INTO emp_rec;
EXIT WHEN emp_vcursor_in%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.ename||' '||emp_rec.job||' '||emp_rec.sal);
END LOOP;
END EXIBE_COMPLETO;
/
Depois de criadas a package e as procedures (usando @nome_arquivo) o exemplo pode ser testado com o seguinte bloco PL/SQL:
DECLARE emp_cursor tipos.emp_tipo_cur; BEGIN OPEN emp_cursor FOR SELECT * FROM EMP ORDER BY SAL DESC; EXIBE_SALARIO(emp_cursor,4); EXIBE_COMPLETO(emp_cursor,5); CLOSE emp_cursor; END;
Quando a segunda procedure começa a extrair os dados, ela começa a partir do ponto onde a primeira procedure parou. O mesmo aconteceria com qualquer procedure se o bloco PL/SQL já tivesse extraído algum dado antes de chama-la.
* Material encontrado na Internet
http://saudino.home.ml.org
gsf@mandic.com.br