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;

Cyber Security Certifications

Após pesquisar sobre o foco e conteúdos de algumas das mais conceituadas certificações na área da segurança informática, entre elas:

• GIAC Security Essentials (GSEC)
• SSCP - Systems Security Certified Practitioner
• Certified Information Security Manager (CISM)
• Certified Information Systems Auditor (CISA)
• Certified Ethical Hacker (CEH)
• EC-Council Certified Security Analyst (ECSA)
• CompTIA Security+
• CISSP - Certified Information Systems Security Professional

Considerei que a mais interessante para quem já possui conhecimentos de administração de sistemas, configuração de redes e routers, protocolos de routing, criptografia, exploits, e uma ideia dos diferentes tipos de ataques informáticos, desde do explorar de vulnerabilidades, passando por disseminar vírus e malware, ate à "simples" obtenção de informação via Engenharia Social era a ...

CISSP - Certified Information Systems Security Professional

O próximo passo foi tentar encontrar quem fornecesse o curso a um preço competitivo, quando para meu espanto encontrei a Cybrary que para além do CISSP, disponibiliza outros cursos online (em video) gratuitamente, tendo apenas algumas aulas extras que podemos adquirir.

Dito isto, já comecei a "frequência" online de 2 dos cursos, o CISSP e o "Python for Security Professionals".

GIT

Quem necessita um repositório com controlo de versões (p.ex. git) partilhado entre vários utilizadores encontra nos serviços Bitbucket ou GitHub uma solução simples, mas essa aparente simplicidade traz limitações e falta de privacidade.

Estes serviços, na sua opção gratuita, limitam a utilização dos repositórios até X utilizadores ou apenas alojam os projectos de forma pública. Estas limitações fazem com que se tenha de optar pelos serviços pagos que também nos disponibilizam.

Para quem, sem custos extra, não quer ter limite de utilizadores e quer a privacidade de ter o seu código dentro da sua rede (ou quando muito, apenas partilhado via internet com utilizadores por nós controlados), tem a seguinte opção:

- Configurar um repositório GIT num servidor na sua rede interna. (Como o acesso ao nosso repositório "interno" é feito normalmente via SSH, este serviço pode também ser aberto ao acesso remoto via internet)

Ora como o podemos configurar:

# No servidor do repo ...
# Temos que criar um grupo para os utilizadores do repo. git
sudo groupadd gitrepo

# adicionar o(s) users ao novo grupo
sudo adduser nunolima gitrepo
sudo adduser sofia gitrepo

# para criar um repo que pode ser usada/montada remotamente (sem um dir. worker) tem que se usar o param. --bare
# o param. --shared define as permissões de acesso dos users, groups e others (no minimo 660)
# o nome do dir., por convenção, termina em .git ( p.ex. projecto.git )
git init --bare --shared=0660 gitrepo-proj01.git

# para um cliente local usar a repo deve fazer:
git clone file:////home/nunolima/dev/gitrepo-proj01.git nome_dir_do_proj

# para um cliente remoto, usa-se
git clone nunolima@<SERVER_IP>:/home/nunolima/dev/gitrepo-proj01.git nome_dir_do_proj

# o comando anterior criou uma cópia local, num dir. com o nome "nome_dir_do_proj"
cd nome_dir_do_proj

# caso o sincronismo do nosso clone tenha falhado, sermos avisados para executar o seguinte comando:
git branch --unset-upstream

# depois fazemos uma utilização (local) normal:
git status
git add .
git commit -m "comentários"
#etc

# para enviar/aplicar o nosso estado LOCAL no SERVIDOR
git remote # lista os nomes dos servidores remotos conhecidos pelo nosso git local
git push origin master # origin é o nome default, dado a um servidor remoto quando foi feito o clone, e master é o nosso master local

# para actualizar (merge) o nosso projecto local com o existente no servidor, executamos ...
git pull origin master # faz um merge do que está no servidor com a nossa versão master

# para não fazer um merge automático em cima do nosso master local
# podemos criar uma branch (p.ex. updates-do-servidor), para isso executamos ...
git fetch origin updates-do-servidor

Experimentem. :)