Olá, bem vindo ao artigo sobre Cursores Implícitos em Oracle PL/SQL ! Hoje vamos mergulhar fundo no fascinante mundo dos cursores implícitos em Oracle PL/SQL. Preparem-se para um conteúdo detalhado, repleto de exemplos práticos e insights valiosos!
Preparação do Ambiente
Para executar os exemplos relacionados a cursores, é preciso criar os objetos abaixo.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
-- Tabela ZIPCODE CREATE TABLE zipcode ( zip VARCHAR2(5), city VARCHAR2(50), state VARCHAR2(2), CONSTRAINT pk_zipcode PRIMARY KEY (zip) ); -- Tabela STUDENT CREATE TABLE student ( id_estudante NUMBER, first_name VARCHAR2(50), last_name VARCHAR2(50), zip VARCHAR2(5), CONSTRAINT pk_student PRIMARY KEY (id_estudante), CONSTRAINT fk_student_zipcode FOREIGN KEY (zip) REFERENCES zipcode(zip) ); -- Tabela COURSE CREATE TABLE course ( numero_curso NUMBER, descricao VARCHAR2(100), pre_requisito NUMBER, creditos NUMBER, departamento VARCHAR2(10), custo NUMBER(8,2), CONSTRAINT pk_course PRIMARY KEY (numero_curso) ); -- Tabela INSTRUCTOR CREATE TABLE instructor ( id_instrutor NUMBER, primeiro_nome VARCHAR2(50), sobrenome VARCHAR2(50), salario NUMBER(10,2), data_contratacao DATE, departamento VARCHAR2(10), CONSTRAINT pk_instructor PRIMARY KEY (id_instrutor) ); -- Tabela GRADE_TYPE CREATE TABLE grade_type ( grade_type_code VARCHAR2(2), description VARCHAR2(50), CONSTRAINT pk_grade_type PRIMARY KEY (grade_type_code) ); -- Tabela ENROLLMENT CREATE TABLE enrollment ( id_estudante NUMBER, id_curso NUMBER, nota NUMBER(5,2), CONSTRAINT pk_enrollment PRIMARY KEY (id_estudante, id_curso), CONSTRAINT fk_enrollment_student FOREIGN KEY (id_estudante) REFERENCES student(id_estudante), CONSTRAINT fk_enrollment_course FOREIGN KEY (id_curso) REFERENCES course(numero_curso) ); -- Inserir dados na tabela ZIPCODE INSERT INTO zipcode (zip, city, state) VALUES ('06483', 'Oxford', 'CT'); INSERT INTO zipcode (zip, city, state) VALUES ('06605', 'Bridgeport', 'CT'); INSERT INTO zipcode (zip, city, state) VALUES ('06798', 'Woodbury', 'CT'); INSERT INTO zipcode (zip, city, state) VALUES ('06830', 'Greenwich', 'CT'); INSERT INTO zipcode (zip, city, state) VALUES ('06850', 'Norwalk', 'CT'); INSERT INTO zipcode (zip, city, state) VALUES ('06851', 'Norwalk', 'CT'); INSERT INTO zipcode (zip, city, state) VALUES ('06905', 'Stamford', 'CT'); INSERT INTO zipcode (zip, city, state) VALUES ('06907', 'Stamford', 'CT'); -- Inserir dados na tabela STUDENT INSERT INTO student (id_estudante, first_name, last_name, zip) VALUES (1, 'John', 'Ancean', '06483'); INSERT INTO student (id_estudante, first_name, last_name, zip) VALUES (2, 'Mike', 'Madej', '06605'); INSERT INTO student (id_estudante, first_name, last_name, zip) VALUES (3, 'David', 'Thares', '06798'); INSERT INTO student (id_estudante, first_name, last_name, zip) VALUES (4, 'Dawn', 'Dennis', '06830'); INSERT INTO student (id_estudante, first_name, last_name, zip) VALUES (5, 'Victor', 'Meshaj', '06830'); INSERT INTO student (id_estudante, first_name, last_name, zip) VALUES (6, 'J.', 'Dalvi', '06830'); INSERT INTO student (id_estudante, first_name, last_name, zip) VALUES (7, 'Edwin', 'Allende', '06850'); INSERT INTO student (id_estudante, first_name, last_name, zip) VALUES (8, 'David', 'Essner', '06851'); INSERT INTO student (id_estudante, first_name, last_name, zip) VALUES (9, 'Rita', 'Archor', '06905'); INSERT INTO student (id_estudante, first_name, last_name, zip) VALUES (10, 'Charles', 'Caro', '06907'); -- Inserir dados na tabela COURSE INSERT INTO course (numero_curso, descricao, pre_requisito, creditos, departamento, custo) VALUES (10, 'Technology Concepts', NULL, 3, 'CS', 1200.00); INSERT INTO course (numero_curso, descricao, pre_requisito, creditos, departamento, custo) VALUES (20, 'Intro to Information Systems', NULL, 3, 'CS', 1200.00); INSERT INTO course (numero_curso, descricao, pre_requisito, creditos, departamento, custo) VALUES (25, 'Intro to Programming', 140, 4, 'CS', 1500.00); INSERT INTO course (numero_curso, descricao, pre_requisito, creditos, departamento, custo) VALUES (80, 'Programming Techniques', 204, 4, 'CS', 1500.00); INSERT INTO course (numero_curso, descricao, pre_requisito, creditos, departamento, custo) VALUES (100, 'Hands-On Windows', 20, 2, 'CS', 1000.00); -- Inserir dados na tabela INSTRUCTOR INSERT INTO instructor (id_instrutor, primeiro_nome, sobrenome, salario, data_contratacao, departamento) VALUES (1, 'Fernand', 'Hanks', 65000.00, TO_DATE('2010-01-01', 'YYYY-MM-DD'), 'CS'); INSERT INTO instructor (id_instrutor, primeiro_nome, sobrenome, salario, data_contratacao, departamento) VALUES (2, 'Tom', 'Wojick', 70000.00, TO_DATE('2012-03-15', 'YYYY-MM-DD'), 'MATH'); INSERT INTO instructor (id_instrutor, primeiro_nome, sobrenome, salario, data_contratacao, departamento) VALUES (3, 'Nina', 'Schorin', 75000.00, TO_DATE('2015-07-01', 'YYYY-MM-DD'), 'CS'); INSERT INTO instructor (id_instrutor, primeiro_nome, sobrenome, salario, data_contratacao, departamento) VALUES (4, 'Gary', 'Pertez', 68000.00, TO_DATE('2013-09-01', 'YYYY-MM-DD'), 'MATH'); INSERT INTO instructor (id_instrutor, primeiro_nome, sobrenome, salario, data_contratacao, departamento) VALUES (5, 'Anita', 'Morris', 72000.00, TO_DATE('2014-11-15', 'YYYY-MM-DD'), 'CS'); -- Inserir dados na tabela GRADE_TYPE INSERT INTO grade_type (grade_type_code, description) VALUES ('FI', 'Final'); INSERT INTO grade_type (grade_type_code, description) VALUES ('HM', 'Homework'); INSERT INTO grade_type (grade_type_code, description) VALUES ('MT', 'Midterm'); INSERT INTO grade_type (grade_type_code, description) VALUES ('PA', 'Participation'); INSERT INTO grade_type (grade_type_code, description) VALUES ('PJ', 'Project'); INSERT INTO grade_type (grade_type_code, description) VALUES ('QZ', 'Quiz'); -- Inserir dados na tabela ENROLLMENT INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (1, 10, 85.5); INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (1, 20, 88.0); INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (2, 10, 90.0); INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (3, 25, 87.5); INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (4, 20, 91.0); INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (5, 25, 89.5); INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (6, 80, 93.0); INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (7, 100, 86.0); INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (8, 20, 88.5); INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (9, 25, 92.0); INSERT INTO enrollment (id_estudante, id_curso, nota) VALUES (10, 80, 90.5); -- Gravando as instruções definitivamente COMMIT; |
O que são Cursores Implícitos?
Cursores implícitos são como assistentes invisíveis que o PL/SQL cria e gerencia automaticamente para você sempre que você executa uma instrução SQL. Eles são a mágica por trás de cada operação SQL que você realiza em seu código PL/SQL.
Quando os Cursores Implícitos entram em ação?
Os cursores implícitos são criados automaticamente nas seguintes situações:
- Quando você executa um comando SELECT … INTO
- Para cada instrução INSERT, UPDATE ou DELETE
- Em comandos SQL dinâmicos executados com EXECUTE IMMEDIATE
Como funcionam os Cursores Implícitos?
Quando você executa uma instrução SQL, o PL/SQL:
- Cria um cursor implícito
- Processa a instrução SQL
- Armazena informações sobre a execução
- Fecha automaticamente o cursor
Tudo isso acontece nos bastidores, sem que você precise gerenciar explicitamente.
Atributos de Cursores Implícitos: Seus Superpoderes Ocultos
Os cursores implícitos têm atributos especiais que nos permitem obter informações valiosas sobre a execução da última instrução SQL. Esses atributos são prefixados com ‘SQL%':
- SQL%ISOPEN: Sempre retorna FALSE para cursores implícitos, pois o PL/SQL os fecha imediatamente após a execução.
- SQL%FOUND: Retorna TRUE se a instrução SQL afetou uma ou mais linhas, FALSE se nenhuma linha foi afetada.
- SQL%NOTFOUND: É o oposto lógico de SQL%FOUND. Retorna TRUE se nenhuma linha foi afetada, FALSE se uma ou mais linhas foram afetadas.
- SQL%ROWCOUNT: Retorna o número de linhas afetadas pela instrução SQL.
Vamos ver cada um desses atributos em ação com exemplos detalhados!
Exemplos Práticos de Cursores Implícitos
Exemplo 1: SELECT … INTO
Vamos começar com um exemplo simples de SELECT … INTO:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE v_nome VARCHAR2(50); v_salario NUMBER; BEGIN SELECT primeiro_nome || ' ' || sobrenome, salario INTO v_nome, v_salario FROM funcionarios WHERE id_funcionario = 100; DBMS_OUTPUT.PUT_LINE('Nome: ' || v_nome); DBMS_OUTPUT.PUT_LINE('Salário: $' || v_salario); -- Usando atributos do cursor implícito DBMS_OUTPUT.PUT_LINE('SQL%FOUND: ' || CASE WHEN SQL%FOUND THEN 'TRUE' ELSE 'FALSE' END); DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: ' || SQL%ROWCOUNT); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Funcionário não encontrado'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Mais de um funcionário encontrado'); END; |
Neste exemplo, o PL/SQL cria um cursor implícito para o SELECT … INTO. Se um funcionário for encontrado, SQL%FOUND será TRUE e SQL%ROWCOUNT será 1.
Exemplo 2: UPDATE com Cursor Implícito
Agora, vamos ver como os atributos funcionam com um UPDATE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE v_aumento NUMBER := 1000; v_departamento NUMBER := 10; BEGIN UPDATE funcionarios SET salario = salario + v_aumento WHERE id_departamento = v_departamento; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('Funcionários atualizados: ' || SQL%ROWCOUNT); ELSE DBMS_OUTPUT.PUT_LINE('Nenhum funcionário atualizado'); END IF; END; |
Aqui, SQL%FOUND nos diz se algum funcionário foi atualizado, e SQL%ROWCOUNT nos informa quantos foram afetados.
Exemplo 3: DELETE com Cursor Implícito
Vamos explorar um exemplo com DELETE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE v_data_limite DATE := TO_DATE('01-JAN-2000', 'DD-MON-YYYY'); BEGIN DELETE FROM pedidos_antigos WHERE data_pedido < v_data_limite; CASE WHEN SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('Pedidos excluídos: ' || SQL%ROWCOUNT); ELSE DBMS_OUTPUT.PUT_LINE('Nenhum pedido antigo encontrado'); END CASE; END; |
Este script demonstra como usar SQL%FOUND e SQL%ROWCOUNT em uma operação DELETE.
Exemplo 4: INSERT com Cursor Implícito
Vejamos como os atributos funcionam com INSERT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE v_id_dept NUMBER := 50; v_nome_dept VARCHAR2(30) := 'Novo Departamento'; BEGIN INSERT INTO departamentos (id_departamento, nome_departamento) VALUES (v_id_dept, v_nome_dept); IF SQL%ROWCOUNT = 1 THEN DBMS_OUTPUT.PUT_LINE('Departamento inserido com sucesso'); ELSE DBMS_OUTPUT.PUT_LINE('Erro na inserção'); END IF; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Departamento já existe'); END; |
Este exemplo mostra como verificar se uma inserção foi bem-sucedida usando SQL%ROWCOUNT.
Exemplo Completo: Utilizando Todos os Atributos
Agora, vamos ver um exemplo que utiliza todos os atributos de cursor implícito em diferentes operações:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
DECLARE v_id_funcionario NUMBER := 100; v_novo_salario NUMBER := 5000; v_nome VARCHAR2(50); BEGIN -- SELECT BEGIN SELECT primeiro_nome || ' ' || sobrenome INTO v_nome FROM funcionarios WHERE id_funcionario = v_id_funcionario; DBMS_OUTPUT.PUT_LINE('Funcionário encontrado: ' || v_nome); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Funcionário não encontrado'); END; -- UPDATE UPDATE funcionarios SET salario = v_novo_salario WHERE id_funcionario = v_id_funcionario; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('Salário atualizado. Linhas afetadas: ' || SQL%ROWCOUNT); ELSE DBMS_OUTPUT.PUT_LINE('Nenhum salário atualizado'); END IF; -- INSERT INSERT INTO log_alteracoes (id_funcionario, data_alteracao, novo_salario) VALUES (v_id_funcionario, SYSDATE, v_novo_salario); IF SQL%ROWCOUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('Log inserido com sucesso'); END IF; -- DELETE (exemplo hipotético) DELETE FROM funcionarios_temporarios WHERE id_funcionario = v_id_funcionario; DBMS_OUTPUT.PUT_LINE('Funcionários temporários removidos: ' || SQL%ROWCOUNT); -- Verificando SQL%ISOPEN (sempre será FALSE para cursores implícitos) IF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('Cursor ainda está aberto (isso nunca será impresso)'); ELSE DBMS_OUTPUT.PUT_LINE('Cursor está fechado, como esperado'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Erro: ' || SQLERRM); END; |
Este script demonstra:
- Uso de SELECT … INTO com tratamento de exceção NO_DATA_FOUND.
- Utilização de SQL%FOUND e SQL%ROWCOUNT após UPDATE.
- Verificação de SQL%ROWCOUNT após INSERT.
- Uso de SQL%ROWCOUNT após DELETE.
- Demonstração de que SQL%ISOPEN sempre retorna FALSE para cursores implícitos.
Dicas Avançadas para Cursores Implícitos
- Sempre verifique SQL%FOUND após operações DML para garantir que a operação afetou as linhas esperadas.
- Use SQL%ROWCOUNT para auditar o número de linhas afetadas por operações em massa.
- Lembre-se de que os atributos de cursor implícito são atualizados após cada instrução SQL, então use-os imediatamente após a operação relevante.
- Em blocos PL/SQL aninhados, os atributos referem-se sempre à instrução SQL mais recentemente executada.
- Para operações SQL dinâmicas usando EXECUTE IMMEDIATE, os atributos de cursor implícito ainda funcionam da mesma forma.
Conclusão
Ufa! Chegamos ao fim desta jornada pelos cursores implícitos em PL/SQL. Vimos que, embora sejam “invisíveis”, os cursores implícitos são ferramentas poderosas que nos fornecem informações valiosas sobre nossas operações SQL.
Pontos-chave para lembrar:
- Cursores implícitos são criados automaticamente para cada instrução SQL.
- Use SQL%FOUND, SQL%NOTFOUND, e SQL%ROWCOUNT para obter informações sobre a execução da instrução.
- SQL%ISOPEN sempre retorna FALSE para cursores implícitos.
- Esses atributos são essenciais para validar e auditar suas operações SQL dentro do PL/SQL.
