Oracle PL/SQL

Oracle PL/SQL

Vamos lá fazer um refresh ao PL/SQL, mesmo desde o inicio, com pouco blá blá blá e com muito código.

.: ANONYMOUS BLOCKS :.

/* 
 Comentário multi-linhas
 Bloco de código sem nome (anónimo)
*/

DECLARE      -- Declaration Section (Optional)
 l_counter NUMBER;
BEGIN      -- Execution Section (Required)
 l_counter := 1;
 -- dbms_output.put_line is used to output to the console
 dbms_output.put_line('l_counter in the inner block is ' || l_counter);
EXCEPTION     -- Exception Section (Optional)
 WHEN OTHERS THEN
  null;
END;

.: NESTING BLOCKS :.

<> -- Labeling para aceder de inner blocks
DECLARE
 l_outer NUMBER;
 l_var NUMBER;
BEGIN
 l_outer := 1;
 l_var := 5;
 DECLARE
  l_inner NUMBER;
  l_var NUMBER;
 BEGIN
  l_inner := 2 / 0;
  l_var = 10;
  dbms_output.put_line('l_var (variável local ao inner block) ' || l_var);
  dbms_output.put_line('parent.l_var (variável local ao outter block) ' || parent.l_var);
 EXCEPTION
  WHEN OTHERS THEN
   null;
 END;
 
 l_outer := 2;

EXCEPTION
 WHEN OTHERS THEN
 null;
END;

.: NUMBER DATATYPE :.

-- NUMBER 25.21
NUMBER (4,2) -- 4 dígitos no total, com arredondamento na 2 casa decimal
NUMBER (5,-2) -- 5 dígitos no total(parte inteira), com arredondamento na 3 digito (ex. 12345 --> 12300)

DECLARE
 l_first NUMBER;
 l_second NUMBER := 2.21; -- OU ASSIM --> l_second NUMBER DEFAULT 2.21;
 l_const CONSTANT NUMBER DEFAULT 9.73;
BEGIN
 l_first := 4;
 l_second := 3.15;
 -- l_const := 5; -- isto gera um erro, pois não é possível modificar o valor de constantes
END;

.: SUBTYPES :.

-- ORACLE SUPPLIED SUBTYPES
DEC / DECIMAL / NUMERIC  -- FIXED POINT, 38 DIGITS
DOUBLE PRECISION / FLOAT  -- FLOATING POINT, 38 DIGITS
REAL     -- FLOATING POINT, 38 DIGITS
INT / INTEGER / SMALLINT  -- INTEGER 38 DIGITS

-- USER DEFINED SUBTYPES
SUBTYPE myinteger IS NUMBER(38,0);

DECLARE
 l_int INTEGER := 1.8;
 SUBTYPE myinteger IS NUMBER(38,0);
 l_myint myinteger := 1.8;
BEGIN
 DBMS_OUTPUT.PUT_LINE('l_int = ' || l_int);
 DBMS_OUTPUT.PUT_LINE('l_myint = ' || l_myint); 
END;

.: %TYPE :.

CREATE TABLE departments (
 dept_id NUMBER NOT NULL PRIMARY KEY,
 dept_name VARCHAR(60)
);

DECLARE
 l_num NUMBER(5,2) NOT NULL DEFAULT 2.21;
 l_num_vartype l_num%TYPE := 1.123;  -- HERDA O DATA TYPE E A CONSTRAINT
 l_num_coltype departments.dept_id%TYPE;  -- HERDA O DATA TYPE (NÃO HERDA AS CONSTRAINTS)
BEGIN
 l_num_vartype := 3.15;
END;

.: PLS_INTEGER :.

NATURAL  -- Valores positivos + zero
NATURALN -- Valores positivos + zero (not null)
POSITIVE -- Nr.s Positivos, não incluí zero
POSITIVEN -- Nr.s Positivos, não incluí zero (not null)
SIGNTYPE -- -1,0,1
SIMPLE_INTEGER -- Permite overflow +2147483647+1 -> -2147483648 (não permite nulls)

.: BINARY_DOUBLE / BINARY_FLOAT :.

-- Boa performance mas não são types para ser usados com moeda
BINARY_DOUBLE -- 64 Bit
BINARY_FLOAT -- 32 Bit

-- EXEMPLO
DECLARE
 l_num1 NUMBER := 0.51;
 l_num2 NUMBER;
 l_num3 NUMBER;
 l_bin_float BINARY_FLOAT := 2;
BEGIN
 -- CONVERSAO AUTOMÁTICA PARA BINARY_FLOAT
 l_num2 := l_num1 * l_bin_float;
 DBMS_OUTPUT.PUT_LINE('l_num2 = ' || l_num2);
 
 -- manter o datatype em number
 l_num3 := l_num1 * TO_NUMBER(l_bin_float);
 DBMS_OUTPUT.PUT_LINE('l_num3 = ' || l_num3);
 
 l_bin_float := l_bin_float / 0;
 IF l_bin_float = BINARY_FLOAT_INFINITY THEN
  DBMS_OUTPUT.PUT_LINE('l_bin_float = ' || l_bin_float);
 END IF;
END;

.: CHARACTER DATATYPES :.

/* 
 Database (Global/Universal) Character Set
 - CHAR
 - VARCHAR2
 - CLOB
 
 National Character Set
 - NCHAR
 - NVARCHAR2
 - NCLOB

 -- guarda 4 chars para o valor 'ab'
 CHAR [(size [bytes | char])] -- char size máx = 8191, default 1, armazenamento estático char(4) 
 NCHAR [(size [char])]
 
 -- guarda 2 chars para o valor 'ab'
 VARCHAR2(size [bytes | char]) -- Máx size 32767 Bytes, armazenamento dinamico varchar2(4) 
 NVARCHAR2(size [char])
*/

.: DATETIME DATATYPES :.

-- 11-MAR-2015 15:25:34
l_date date := '10-nov-2013';
l_date := TO_DATE('11-NOV-2013 15:25:34', 'DD-MON-RRRR HH24:MI:SS');
l_date := TO_DATE('11/10/2013 15:25:34', 'MM/DD/RRRR HH24:MI:SS');

CURRENT_DATE -- session date
SYSDATE  -- Database server date

.: TIMESTAMP :.

-- 11-NOV-2013 14:25:34.234
-- TIMESTAMP [(fractional_seconds_precision)]
-- TO_TIMESTAMP

DECLARE
 l_tstmp TIMESTAMP(3);
BEGIN
 l_tstmp := TO_TIMESTAMP('11/10/2013 15:25:34.123', 'MM/DD/RRRR HH24:MI:SS.FF');
END;

-- TIMESTAMP WITH TIMEZONE
-- TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
-- 02-NOV-13 05.16.56.109000000 PM -08:00
-- 02-NOV-13 05.16.56.109000000 PM AMERICA/LOS_ANGELES
-- 02-NOV-13 05.16.56.109000000 PM PST -- PST - PACIFIC STANDARD TIME
l_tstmp := TO_TIMESTAMP_TZ('11/10/2013 15:25:34.123 PST PDT', 'MM/DD/RRRR HH24:MI:SS.FF TZR TZD');

CURRENT_TIMESTAMP -- session TIMESTAMP WITH TIME ZONE
SYSTIMESTAMP   -- Database TIMESTAMP WITH TIME ZONE

-- EXEMPLOS
select CURRENT_TIMESTAMP, SYSTIMESTAMP from dual;
alter session set time_zone = 'EST';

DECLARE
 l_date DATE := CURRENT_DATE;
 l_systimestamp TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
 l_currenttimestamp TIMESTAMP WITH TIME ZONE := CURRENT_TIMESTAMP;
 l_timestamp TIMESTAMP := CURRENT_TIMESTAMP;
BEGIN
 DBMS_OUTPUT.PUT_LINE('l_date = ' || l_date);
 DBMS_OUTPUT.PUT_LINE('l_systimestamp = ' || l_systimestamp);
 DBMS_OUTPUT.PUT_LINE('l_currenttimestamp = ' || l_currenttimestamp);
 DBMS_OUTPUT.PUT_LINE('l_timestamp = ' || l_timestamp);
END;

.: INTERVAL DATETIME DATATYPES :.

-- INTERVAL YEAR [(year_precision)] TO MONTH;
DECLARE
 l_interv INTERVAL YEAR(3) TO MONTH;
BEGIN
 l_interv := INTERVAL '123-2' YEAR TO MONTH;
 l_interv := '123-2';
 l_interv := INTERVAL '123' YEAR;
 l_interv := INTERVAL '12' MONTH;
END;

-- INTERVAL DAY [(day_precision)] TO SECOND;
DECLARE
 l_interv INTERVAL DAY(2) TO SECOND(2);
BEGIN
 l_interv := '10 01:10:11.12';
 l_interv := INTERVAL '10 01:10:11.12' DAY TO SECOND;
 l_interv := INTERVAL '10' DAY;
 l_interv := INTERVAL '12' SECOND;
END;

-- INTERVAL DAY TO SECOND
DECLARE
 l_tsmp_begin TIMESTAMP(2) WITH TIME ZONE :=
  TO_TIMESTAMP_TZ('02-NOV-2013 10:00:00.00 PST PDT', 'DD-MON-RRRR HH24:MI:SS.FF TZR TZD');
 l_tsmp_end TIMESTAMP(2) WITH TIME ZONE :=
  TO_TIMESTAMP_TZ('03-NOV-2013 10:00:00.00 PST PDT', 'DD-MON-RRRR HH24:MI:SS.FF TZR TZD');
 l_interv INTERVAL DAY(2) TO SECOND(2);
BEGIN
 l_interv := l_tsmp_end - l_tsmp_begin;
 DBMS_OUTPUT.PUT_LINE('interval = ' || l_interv); -- interval = +01 01:00:00.00
END;

.: BOOLEAN DATATYPE :.

-- TRUE, FALSE, NULL
DECLARE
 l_boolean BOOLEAN;
BEGIN
 l_boolean := TRUE;
END;

.: COMPOSITE DATATYPES :.

-- RECORDS, NESTED TABLES, VARRAYS, ASSOCIATIVE ARRAYS
-- RECORD
-- TYPE rec_name IS RECORD ( Field Declaration[, Field Declaration ...] )

CREATE TABLE departments (
 dept_id NUMBER NOT NULL PRIMARY KEY,
 dept_name VARCHAR2(60)
);

DECLARE
 TYPE emp_rec IS RECORD (  emp_name VARCHAR2(60),
        dept_id departments.dept_id%TYPE,
        location VARCHAR2(10) DEFAULT 'CA' );
 l_emprec emp_rec;
BEGIN
 l_emprec.emp_name := 'John';
 l_emprec.dept_id := 10;
 DBMS_OUTPUT.PUT_LINE('l_emprec.emp_name = ' || l_emprec.emp_name);
 DBMS_OUTPUT.PUT_LINE('l_emprec.location = ' || l_emprec.location);
END;

.: %ROWTYPE :.

-- Record based on TABLE, VIEW ou CURSOR
CREATE TABLE departments (
 dept_id NUMBER NOT NULL PRIMARY KEY,
 dept_name VARCHAR2(60)
);


-- EXEMPLO 1
DECLARE
 l_dept_rec departments%ROWTYPE;
BEGIN
 l_dept_rec.dept_id := 10;
 DBMS_OUTPUT.PUT_LINE('l_dept_rec.dept_id = ' || l_dept_rec.dept_id);
END;

-- EXEMPLO 2
DECLARE
 TYPE emp_rec IS RECORD (  emp_name VARCHAR2(60),
        dept_rec departments%ROWTYPE,
        location VARCHAR2(10) DEFAULT 'CA' );
 l_emprec emp_rec;
BEGIN
 l_emprec.l_dept_rec.dept_id := 20;
 DBMS_OUTPUT.PUT_LINE('l_emprec.l_dept_rec.dept_id = ' || l_emprec.l_dept_rec.dept_id);
END;

.: OUTROS DATATYPES :.

-- CLOB - PARA FICHEIROS DE TEXTO
-- BLOB - FICH.OS BINARIOS OU IMAGENS
-- BFILE - GUARDA FICH.OS BINÁRIOS MAS FORA DA B.DADOS (GUARDA UM LINK)

-- COLLECTION'S:
-- VARRAYS
-- NESTED TABLES
-- ASSOCIATIVE ARRAYS

-- ROWID / UROWID

.: LOOPS :.

-- SIMPLE LOOPS
-- FOR LOOPS
-- WHILE LOOPS

-- LOOP TERMINATION'S (nos SIMPLE LOOPS)
-- EXIT
-- EXIT WHEN 
-- RETURN
-- GOTO
-- ocorre uma EXCEPTION

.: SIMPLE LOOP :.

-- EXEMPLO 1
DECLARE
 l_counter NUMBER := 0;
 l_sum NUMBER := 0;
BEGIN
 LOOP
  l_sum := l_sum + l_counter;
  l_counter := l_counter + 1;
  EXIT WHEN l_counter > 3;
  DBMS_OUTPUT.PUT_LINE('CONTADOR = ' || l_counter);
 END LOOP;
END;

-- EXEMPLO 2
DECLARE
 l_counter NUMBER := 0;
 l_sum NUMBER := 0;
BEGIN
 LOOP
  l_sum := l_sum + l_counter;
  l_counter := l_counter + 1;
  IF l_counter > 3 THEN
   RETURN;
  END IF;
  DBMS_OUTPUT.PUT_LINE('CONTADOR = ' || l_counter);
 END LOOP;
END;

-- EXEMPLO 3
DECLARE
 l_counter NUMBER := 0;
 l_sum NUMBER := 0;
BEGIN
 LOOP
  l_sum := l_sum + l_counter;
  l_counter := l_counter + 1;
  IF l_counter > 3 THEN
   GOTO fora_do_loop;
  END IF;
  DBMS_OUTPUT.PUT_LINE('CONTADOR = ' || l_counter);
 END LOOP;
 <>
 null; -- ESTA LINHA É IMPORTANTE, POIS DEPOIS DA LABEL TEM QUE EXISTIR ALGO PARA SER EXECUTADO, NEM QUE SEJA null
END;

.: FOR LOOP :.

/*  A var. l_counter é implicitamente declarada como PLS_INTEGER, 
 e não podemos modificar o seu valor
 Esta var. só pode ser acedida no scope do loop
 Para aceder a uma var. com o mesmo nome dentro do loop, temos que usar labels
 O counter do loop apenas incrementa de 1 em 1
*/
DECLARE
 l_sum NUMBER := 0;
BEGIN
 FOR l_counter IN 1..3 LOOP
  l_sum := l_sum + l_counter;
  DBMS_OUTPUT.PUT_LINE('Soma = ' || l_sum);
 END LOOP;
END;

-- REVERSE FOR LOOP
DECLARE
 l_sum NUMBER := 0;
BEGIN
 FOR l_counter IN REVERSE 1..3 LOOP
  l_sum := l_sum + l_counter;
  DBMS_OUTPUT.PUT_LINE('Soma = ' || l_sum);
 END LOOP;
END;

-- VAR. COM MESMO NOME FORA DO LOOP
<>
DECLARE
 l_counter NUMBER := 5;
 l_sum NUMBER := 0;
BEGIN
 FOR l_counter IN 1..3 LOOP
  l_sum := l_sum + l_counter;
  DBMS_OUTPUT.PUT_LINE('Soma = ' || l_sum);
  DBMS_OUTPUT.PUT_LINE('Counter externo ao loop = ' || outer.l_counter);
 END LOOP;
END;

-- NR. ITERAÇÕES = 1
 FOR l_counter IN 3..3 LOOP

-- NR. ITERAÇÕES = 0
 FOR l_counter IN 3..1 LOOP
 
-- USAR VAR.S NOS VALORES DOS BOUNDS DO LOOP
DECLARE
 l_ini NUMBER := 2;
 l_fim NUMBER := 5;
 l_sum NUMBER := 0;
BEGIN
 FOR l_counter IN l_ini..l_fim LOOP
  l_sum := l_sum + l_counter;
  DBMS_OUTPUT.PUT_LINE('Soma = ' || l_sum);
 END LOOP;
END;

-- EXEMPLO 1
CREATE TABLE departments (
 dept_id NUMBER NOT NULL PRIMARY KEY,
 dept_name VARCHAR2(60));
 
INSERT INTO departments (dept_id, dept_name) VALUES (1, 'Sales');
INSERT INTO departments (dept_id, dept_name) VALUES (2, 'IT');

DECLARE
 l_dept_count NUMBER;
BEGIN
 SELECT count(*) INTO l_dept_count FROM departments;
 
 FOR l_counter IN 1..l_dept_count LOOP
  DBMS_OUTPUT.PUT_LINE(l_counter);
 END LOOP;
END;

.: CONTINUE :.

BEGIN
 FOR l_counter IN 1..5 LOOP
  IF l_counter = 3 THEN
   CONTINUE;
  END IF;
  DBMS_OUTPUT.PUT_LINE(l_counter);
 END LOOP;
END;

.: NESTING LOOPS :.

BEGIN
 <>
 FOR l_first_counter IN 1..2 LOOP
  DBMS_OUTPUT.PUT_LINE(l_first_counter);
  
  <>
  FOR l_second_counter IN 3..4 LOOP
   DBMS_OUTPUT.PUT_LINE(l_second_counter);
  END LOOP innerLoop;
  
 END LOOP outerLoop;
END;

.: EXIT and CONTINUE with NESTED LOOPS :.

-- EXEMPLO EXIT outerLoop WHEN ...
BEGIN
 <>
 FOR l_first_counter IN 1..2 LOOP
  DBMS_OUTPUT.PUT_LINE('outer ' || l_first_counter);
  
  <>
  FOR l_second_counter IN 1..5 LOOP
   EXIT outerLoop WHEN l_second_counter = 3;
   DBMS_OUTPUT.PUT_LINE('inner ' || l_second_counter);
  END LOOP innerLoop;
  
 END LOOP outerLoop;
END;

output:
 outer 1
  inner 1
  inner 2

-- EXEMPLO CONTINUE
BEGIN
 <>
 FOR l_first_counter IN 1..2 LOOP
  DBMS_OUTPUT.PUT_LINE('outer ' || l_first_counter);
  
  <>
  FOR l_second_counter IN 1..5 LOOP
   CONTINUE outerLoop WHEN l_second_counter = 3;
   DBMS_OUTPUT.PUT_LINE('inner ' || l_second_counter);
  END LOOP innerLoop;
  
 END LOOP outerLoop;
END;

output:
 outer 1
  inner 1
  inner 2
 outer 2
  inner 1
  inner 2

.: WHILE LOOP :.

DECLARE
 l_check INTEGER := 1;
BEGIN
 WHILE l_check < 5 LOOP
  l_check := DBMS_RANDOM.VALUE(1,10);
  DBMS_OUTPUT.PUT_LINE(l_check);
 END LOOP;
END;

.: CONDITIONAL EXECUTION :.

-- EXEMPLO 1
DECLARE
 l_res BOOLEAN := FALSE;
 l_a NUMBER := 20;
 l_b NUMBER := 20;
 l_c NUMBER := 20;
 l_d NUMBER;
BEGIN
 IF (l_a > 5000 AND l_b > 50) OR (l_c < 5000 AND l_d IS NULL) THEN
  l_res := TRUE;
 ELSE
  l_res := FALSE;
 END IF;
END;

-- NVL(l_d, 0) -- Se l_d for NULL devolve 0

-- EXEMPLO 2 (ELSIF)
DECLARE
 l_sales_amt NUMBER := 60000;
 l_commission NUMBER;
BEGIN
 IF l_sales_amt > 50000 THEN
  l_commission := 10;
 ELSIF l_sales_amt > 40000 THEN
  l_commission := 5;
 ELSE
  l_commission := 2;
 END IF;
END;

-- EXEMPLO 3 (ELSIF, ELSIF)
DECLARE
 l_ticket_priority VARCHAR2(8) := 'MEDIUM';
 l_support_tier NUMBER;
BEGIN
 IF l_ticket_priority = 'HIGH' THEN
  l_support_tier := 1;
 ELSIF l_ticket_priority = 'MEDIUM' THEN
  l_support_tier := 2;
 ELSIF l_ticket_priority = 'LOW' THEN
  l_support_tier := 3;
 ELSE
  l_support_tier := 0;
 END IF;
 DBMS_OUTPUT.PUT_LINE(l_support_tier);
END;

.: CASE :.

DECLARE
 l_ticket_priority VARCHAR2(8) := 'MEDIUM';
 l_support_tier NUMBER;
BEGIN
 CASE l_ticket_priority 
  WHEN 'HIGH' THEN
   l_support_tier := 1;
  WHEN 'MEDIUM' THEN
   l_support_tier := 2;
  WHEN 'LOW' THEN
   l_support_tier := 3;
  ELSE
   l_support_tier := 0;
 END CASE;
 DBMS_OUTPUT.PUT_LINE(l_support_tier);
END;

-- OUTRA FORMA DE ESCREVER
DECLARE
 l_ticket_priority VARCHAR2(8) := 'MEDIUM';
 l_support_tier NUMBER;
BEGIN
 CASE 
  WHEN l_ticket_priority = 'HIGH' THEN
   l_support_tier := 1;
  WHEN l_ticket_priority = 'MEDIUM' THEN
   l_support_tier := 2;
  WHEN l_ticket_priority = 'LOW' THEN
   l_support_tier := 3;
  ELSE
   l_support_tier := 0;
 END CASE;
 DBMS_OUTPUT.PUT_LINE(l_support_tier);
END;

-- AINDA OUTRA FORMA DE ESCREVER
DECLARE
 l_ticket_priority VARCHAR2(8) := 'MEDIUM';
 l_support_tier NUMBER;
BEGIN
 l_support_tier :=
 CASE l_ticket_priority
  WHEN 'HIGH'  THEN 1
  WHEN 'MEDIUM'  THEN 2
  WHEN 'LOW'   THEN 3
  ELSE     0 -- se não tiver esta linha, retorna/atribui o valor null à var. l_support_tier
 END; -- Não fecha com END CASE
 DBMS_OUTPUT.PUT_LINE(l_support_tier);
END;

.: CURSOR TYPES :.

/*
 Implicit Cursors - SELECT, INSERT, UPDATE ou DELETE -- Abre, executa e fecha o cursor automáticamente
 Explicit Cursors - Deve-se usar quando vamos ler/modificar mais de que uma linha
 
 Cursor Life Cycle
  1- OPEN
   1.1- É reservada memória
   1.2- Statement SQL é Parsed e Binded
   1.3- Statement SQL é executado
   1.4- O apontador é movido para a 1ª linha/1º registo
  2- FETCH
   2.1- Row Fetched (recolhe os dados, apontados)
  3- CLOSE
   3.1- A memória é libertada
*/

CREATE TABLE departments (
 dept_id NUMBER NOT NULL PRIMARY KEY,
 dept_name VARCHAR2(60));

CREATE TABLE employee (
 emp_id NUMBER NOT NULL PRIMARY KEY,
 emp_name VARCHAR2(60),
 emp_dept_id NUMBER,
 emp_loc VARCHAR2(20),
 emp_sal NUMBER,
 CONSTRAINT emp_dept_fk FOREIGN KEY (emp_dept_id)
 REFERENCES departments(dept_id)
);

INSERT INTO departments VALUES (1, 'HELPDESK');
INSERT INTO departments VALUES (2, 'DEV');
INSERT INTO departments VALUES (3, 'REDES');

INSERT INTO employee VALUES (1, 'Ana', 2, 'Sala 103', 1174);
INSERT INTO employee VALUES (2, 'Rui', 1, 'Sala 103', 1001);
INSERT INTO employee VALUES (3, 'Pedro', 2, 'Sala 201', 4501);
INSERT INTO employee VALUES (4, 'Nuno', 3, 'Sala 505', 3650);

DROP TABLE employee;
DROP TABLE departments;

/*
SQL%FOUND - Retorna TRUE se o update/delete modificou pelo menos 1 linha ou um select modificou 1 só linha (se leu mais que 1 ou nenhuma dá erro)
SQL%NOTFOUND - O contrário do SQL%FOUND
SQL%ROWCOUNT - Dá o nr. de linhas lidas ou modificadas
*/

DECLARE
 l_dept_id departments.dept_id%TYPE;
 l_dept_name departments.dept_name%TYPE;
BEGIN
 SELECT dept_id, dept_name
 INTO l_dept_id, l_dept_name
 FROM departments
 WHERE dept_id = 1;
 IF SQL%FOUND THEN
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
 END IF;
END;


BEGIN
 UPDATE departments SET dept_name = 'RH' WHERE dept_id = 2;
 IF SQL%FOUND THEN
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
 END IF;
END;

BEGIN
 DELETE FROM departments;
 IF SQL%FOUND THEN
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
 END IF;
END;

.: COMMIT e ROLLBACK :.

BEGIN
 UPDATE departments SET dept_name = 'RH' WHERE dept_id = 2;
 COMMIT;
EXCEPTION WHEN OTHERS THEN
 ROLLBACK;
END;

.: EXPLICIT CURSORS :.

-- CURSOR  IS select_stmt;
/*
 O nr. e o tipo de colunas tem que bater certo com o nr. e tipo das variaveis
 Mesmo que o SELECT não returne nada, não dá erro (ao contrário do IMPLICIT CURSOR)
 
*/

-- CURSOR para (INTO) var's locais
DECLARE
 l_dept_id departments.dept_id%TYPE;
 l_dept_name departments.dept_name%TYPE;
 CURSOR cur_get_department IS
  SELECT dept_id, dept_name FROM departments WHERE dept_id = 1;
BEGIN
 OPEN cur_get_department;
 FETCH cur_get_department INTO l_dept_id, l_dept_name;
  DBMS_OUTPUT.PUT_LINE(l_dept_name);
 CLOSE cur_get_department;
END;

-- Multi Row SELECT
-- CURSOR sem var's locais, usa uma var. do tipo do cursor
-- usa menos código e mais limpo
DECLARE
 CURSOR cur_get_employees IS
  SELECT emp_id, emp_sal * 0.1 bonus FROM employee;
 cur_get_employees_var cur_get_employees%ROWTYPE; -- é preciso usar uma nova var. pq se usou uma coluna nova, bonus.
BEGIN
 OPEN cur_get_employees;
 LOOP
  FETCH cur_get_employees INTO cur_get_employees_var;
  EXIT WHEN cur_get_employees%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(cur_get_employees_var.emp_id || ' - ' || cur_get_employees_var.bonus);
  DBMS_OUTPUT.PUT_LINE(cur_get_employees%ROWCOUNT);
 END LOOP;
 IF cur_get_employees%ISOPEN THEN --não era necessário, usado aqui como exemplo
  CLOSE cur_get_employees; -- só se pode fechar depois de sair do loop
 END IF;
END;

.: CURSOR PARAMETERS :.

DECLARE
 CURSOR cur_get_departments ( p_rows NUMBER DEFAULT 5 ) IS
  SELECT dept_id, dept_name FROM departments WHERE ROWNUM <= p_rows;
 cur_get_departments_var cur_get_departments%ROWTYPE;
BEGIN
 OPEN cur_get_departments(2); -- só vai buscar 2 linhas
 LOOP
  FETCH cur_get_departments INTO cur_get_departments_var;
(...)

ROWNUM -- nr. de linhas lidas pelo select

.: CURSOR FOR LOOP :.

/*
 FOR cur IN  LOOP
  statements;
 END LOOP;
 
 - var. do loop implicitamente do tipo Cursor%ROWTYPE
 - Código compacto
 - OPEN e CLOSE do cursor é automático
 - pode-se usar com cursores explicitos ou implicitos (se em vez de um cursor passarmos uma query sql)
*/

DECLARE
 CURSOR cur_get_employees IS
  SELECT emp_id, emp_sal * 0.1 bonus
  FROM employee;
BEGIN
 FOR cur IN cur_get_employees LOOP
  DBMS_OUTPUT.PUT_LINE(cur.emp_id || ' - ' || cur.bonus);
 END LOOP;
END;

.: IMPLICIT CURSOR FOR LOOP :.

DECLARE
 l_num NUMBER := 2;
BEGIN
 FOR cur IN (SELECT dept_id, dept_name FROM departments WHERE ROWNUM <= l_num) LOOP
  DBMS_OUTPUT.PUT_LINE(cur.dept_id || ' - ' || cur.dept_name);
 END LOOP;
END;

.: NESTED CURSORS :.

DECLARE
 CURSOR cur_dept (p_rownum NUMBER) IS
  SELECT dept_id
  FROM departments
  WHERE ROWNUM <= p_rownum;
 CURSOR cur_emp (p_dept_id departments.dept_id%TYPE) IS
  SELECT emp_name
  FROM employee
  WHERE emp_dept_id = p_dept_id;
BEGIN
 <>
 FOR cur_dept_var IN cur_dept (2) LOOP
  DBMS_OUTPUT.PUT_LINE('Dept id: ' || cur_dept_var.dept_id);
  <>
  FOR cur_emp_var IN cur_emp (cur_dept_var.dept_id) LOOP
   DBMS_OUTPUT.PUT_LINE('Emp Name: ' || cur_emp_var.emp_name);
  END LOOP emp_loop;
 END LOOP dept_loop;
END;

.: CURSOR FOR UPDATE or DELETE :.

/*
 CURSOR  [(param1, param2..paramN)] IS select_stmt
 FOR UPDATE [of column list] [NOWAIT];
 
 [of column list] - lista de campos a serem bloqueados para update deste cursor. esta lista
   não limita o update apenas a estes campos, mas sim apenas bloqueia o 
   acesso a estes campos, até ao fim do loop/cursor.
   Caso não se use este parametro, o oracle vai bloquear TODOS os campos
   presentes na query de update.
 NOWAIT - indica que o update não deve ficar à espera, caso encontre a tabela esteja bloqueada.

 Cuidados a ter, desde que o cursor á aberto, até ao momento do update, 
 os dados podem ser alterados por outro user.
 Para evitar isso, devemos usar um exclusive lock.
 
  WHERE CURRENT OF 
*/
DECLARE
 CURSOR cur_move_emp (p_emp_loc employee.emp_loc%TYPE) IS
  SELECT emp_id, dept_name
  FROM departments, employee
  WHERE emp_dept_id = dept_id AND emp_loc = p_emp_loc

  -- permite "proteger"/bloquear este cursor até fazer os updates
  FOR UPDATE OF emp_loc NOWAIT; 
BEGIN
 FOR cur IN cur_move_emp('Sala 201') LOOP
  UPDATE employee SET emp_loc = 'Sala 330'
  WHERE CURRENT OF cur_move_emp;
 END LOOP;
 COMMIT; -- torna as alterações permanentes e liberta os locks das linhas.
END;

.: REF CURSORS / CURSOR VARIABLES :.

/*
 TYPE  IS REF CURSOR [RETURN return_type]
 
 Cursor Attributes: 
  %FOUND
  %NOTFOUND
  %ISOPEN
  %ROWCOUNT
  
 - Apontador para um CURSOR
 - Pode ser usar para várias querys
 - Pode ser usado como parametro para uma PROCEDURE ou FUNCTION
 - Podemos atribuir valores
 - Não podem recever parametros, nem ser usados em FOR LOOP's
 - Podem ser ou não TIPADAS (Strongly|Weakly Typed)
 
*/

.: STRONGLY TYPED CURSOR VARIABLES :.

DECLARE
 TYPE rc_dept IS REF CURSOR RETURN departments%ROWTYPE;
 rc_dept_cur rc_dept;
 l_dept_rowtype departments%ROWTYPE;
 l_id departments.dept_id%TYPE := 1;
 l_dept_id departments.dept_id%TYPE;
 l_dept_name departments.dept_name%TYPE;
BEGIN
 OPEN rc_dept_cur FOR
  SELECT * FROM departments
  WHERE dept_id = l_id;
 l_id := 2;
 LOOP
  FETCH rc_dept_cur INTO l_dept_rowtype;
  EXIT WHEN rc_dept_cur%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(l_dept_rowtype.dept_id);
 END LOOP;
 OPEN rc_dept_cur FOR
  -- SELECT * FROM departments -- Funcionaria porque neste caso a tabelo só tem 2 campos e coincide com 2 var's
  SELECT dept_id, dept_name FROM departments -- funciona sempre :), bate certo com o nr. var's do fetch
  WHERE dept_name = 'DEV';
 LOOP
  FETCH rc_dept_cur INTO l_dept_id, l_dept_name;
  EXIT WHEN rc_dept_cur%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(l_dept_id);
 END LOOP;
 CLOSE rc_dept_cur;
END;

.: USER DEFINED RECORD TYPES :.

DECLARE
 TYPE dept_rec IS RECORD (dept_id departments.dept_id%TYPE,
        dept_name departments.dept_name%TYPE);
 TYPE rc_dept IS REF CURSOR RETURN dept_rec;
 rc_dept_cur rc_dept;
 l_dept_rec dept_rec;
 l_choice NUMBER :=1;
BEGIN
 IF l_choice = 1 THEN
  OPEN rc_dept_cur FOR
   SELECT * FROM departments WHERE dept_id = 1;
 ELSE
  OPEN rc_dept_cur FOR
   SELECT * FROM departments WHERE dept_name = 'DEV';
 END IF;
 LOOP
  FETCH rc_dept_cur INTO l_dept_rec;
  EXIT WHEN rc_dept_cur%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(l_dept_rec.dept_id);
 END LOOP;
 CLOSE rc_dept_cur;
END;