PlSql Avançado

Prévia do material em texto

PL SQL
Avançado
https://www.techonthenet.com/oracle/exists.php
https://docs.oracle.com/cd/B10500_01/appdev.920/a96624/05_colls.htm
http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html
PL SQL - Avançado
Objetivo:
Pré requisito: Conceitos Intermediários.
Possuir familiaridade com conceitos avançados
PL SQL
EXCEÇÕES DO SISTEMA
COLLECTIONS AND RECORDS
CURSORES
SEQUENCES
TRIGGERS
PROCEDURES
FUNCTIONS
PACKAGES
EXCEÇÕES DO SISTEMA
As exceções de sistema são exceções que possuem nomes pré-definidos no Oracle PL/SQL. Elas são nomeadas no pacote STANDARD do PL/SQL e não precisam ser definidos pelo programador.
EXCEÇÕES DO SISTEMA
Oracle Exception Name
Oracle Error
Explanation
DUP_VAL_ON_INDEX
ORA-00001
Você tentou executar uma instrução INSERT ou UPDATE que criou um valor duplicado em um campo restrito por um índice exclusivo.
INVALID_CURSOR
ORA-01001
You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON
ORA-01012
Você tentou executar uma chamada para o Oracle antes de iniciar sessão.
LOGIN_DENIED
ORA-01017
Você tentou se conectar ao Oracle com uma combinação inválida de nome de usuário / senha.
NO_DATA_FOUND
ORA-01403
Você tentou um dos seguintes procedimentos:
1. Você executou uma instrução SELECT INTO e nenhuma linha foi retornada.
2. Você referenciou uma linha não inicializada em uma tabela.
3. Você lê após o final do arquivo com o pacote UTL_FILE.
TOO_MANY_ROWS
ORA-01422
Você tentou executar uma instrução SELECT INTO e mais de uma linha foi retornada.
ZERO_DIVIDE
ORA-01476
Você tentou dividir um número por zero.
INVALID_NUMBER
ORA-01722
Você tentou executar uma instrução SQL que tentou converter uma string para um número, mas não teve êxito.
EXCEÇÕES DO SISTEMA
CREATE [OR REPLACE] PROCEDURE procedure_name
 [ (parameter [,parameter]) ]
IS
 [declaration_section]
BEGIN
 executable_section
EXCEPTION
 WHEN exception_name1 THEN
 [statements]
 WHEN exception_name2 THEN
 [statements]
 WHEN exception_name_n THEN
 [statements]
 WHEN OTHERS THEN
 [statements]
END [procedure_name];
COLLECTIONS AND RECORDS
PL / SQL fornece os tipos de dados TABLE e VARRAY, que permitem que você declare “Index-By Table (matrizes associativas)”, “Nested tables Tabelas Aninhadas” e “VARRAY Matrizes de Tamanho Variável”.
Também pode ser criado o tipo de dados RECORD que permite tratar dados relacionados.
Index-By Table
Permitem pesquisar elementos usando números arbitrários – Ordenado pelo VARCHAR “ordem alfabética”
DECLARE
 TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
 country_population population_type;
 continent_population population_type;
 populacao NUMBER;
 continente VARCHAR2(64);
BEGIN
 country_population('Greenland') := 100000;
 country_population('Iceland') := 750000;
 populacao := country_population('Greenland');
 continent_population('Australia') := 30000000;
 continent_population('Antarctica') := 1000; -- Creates new entry
 continent_population('Antarctica') := 1001; -- Replaces previous value
 continente := continent_population.FIRST; -- Returns 'Antarctica' primeiro alfabeticamente.
 continente := continent_population.LAST; -- Returns 'Australia'
 populacao := continent_population(continent_population.LAST); -- Returns the value corresponding 
 -- to the last key, in this case the population of Australia.
END;
VARRAY - MATRIZES DE TAMANHO VARIÁVEL
Varrays (abreviação de matrizes de tamanho variável). Mantêm um número fixo de elementos (embora você possa alterar o número de elementos em tempo de execução), “sem passar o valor máximo fixado”. 
VARRAY - MATRIZES DE TAMANHO VARIÁVEL
DECLARE
	 TYPE typeArrayNumeros IS VARRAY(5) OF NUMBER(10);
	 colecao typeArrayNumeros;
	 indice NUMBER;
 BEGIN
	-- Inicializando a colecao com 2 valores
	colecao := typeArrayNumeros(1, 2);
	-- Ampliar a colecao com valores extras
	<<load_loop>>
	FOR i IN 3 .. 5 LOOP
	 colecao.extend;
	 colecao(colecao.last) := i;
	END LOOP load_loop;
	-- Nao e possível excluir de um VARRAY. “ colecao.DELETE(3); ”
	-- Percorrer a colecao – “POSICAO DO PRIMEIRO ELEMENTO”  1  
	indice := colecao.FIRST;
	<<display_loop>>
	WHILE indice IS NOT NULL LOOP
	 DBMS_OUTPUT.PUT_LINE('Numero = ' || colecao(indice));
	 indice := colecao.NEXT(indice);
	END LOOP display_loop;
 END;
NESTED TABLES - TABELAS ANINHADAS 
Dentro do banco de dados, as tabelas aninhadas podem ser consideradas tabelas de banco de dados de uma coluna. O Oracle armazena as linhas de uma tabela aninhada sem nenhuma ordem específica. Mas, quando você recupera a tabela aninhada em uma variável PL / SQL, as linhas recebem subíndices consecutivos a partir de 1.
NESTED TABLES - TABELAS ANINHADAS 
Para tabelas aninhadas, use a sintaxe:
TYPE type_name IS TABLE OF element_type [NOT NULL];
	Obs: NOT NULL “Opcional”
NESTED TABLES - TABELAS ANINHADAS 
	DECLARE
		TYPE Roster IS TABLE OF VARCHAR2(15) not null;
		names Roster := Roster('J Hamil',
					'D Caruso',
					'R Singh',
					'PL SQL',
					'Nomes');
	BEGIN
	FOR i IN names.FIRST .. names.LAST LOOP
	 dbms_output.put_line(' names(' || i || ') ' || names(i));
	 IF names(i) = 'J Hamil' THEN
	 NULL;
	 END IF;
	END LOOP;
	END;
RECORDS 
Um registro é uma estrutura de dados que pode armazenar itens de dados de diferentes tipos. Os registros consistem em diferentes campos, semelhante a uma linha de uma tabela de banco de dados.
RECORDS 
DECLARE
 TYPE recType_Produto IS RECORD(
 idProduto PROD_JD.SN_PRODUTO.ID_PRODUTO%TYPE,
 descricao PROD_JD.SN_PRODUTO.DESCRICAO%TYPE);
 lstProduto recType_Produto;
 CURSOR busca_prod_cursor IS
 select id_produto, descricao
 from PROD_JD.SN_PRODUTO
 where id_produto in (5012, 5009, 5010, 5011)
 order by id_produto asc;
BEGIN
 OPEN busca_prod_cursor;
 LOOP
 
 FETCH busca_prod_cursor
 INTO lstProduto;
 EXIT WHEN busca_prod_cursor%NOTFOUND;
 
 DBMS_OUTPUT.PUT_LINE('ID: '||lstProduto.idProduto);
 
 END LOOP;
 IF busca_prod_cursor%ISOPEN THEN
 CLOSE busca_prod_cursor;
 END IF;
END;
CURSORES
Um cursor é uma instrução SELECT que está definida dentro da seção de declaração do seu código PLSQL. 
Cursor sem parâmetros. (mais simples)
Cursor com parâmetros.
	
CURSORES - Cursor sem parâmetros
Function FindCourse(pIdProduto IN NUMBER) RETURN number IS
	vIdProduto number;
	CURSOR c1 IS
	SELECT id_produto FROM sn_produto WHERE id_produto = pIdProduto;
BEGIN
 	 OPEN c1;
	 FETCH c1
	 INTO vIdProduto;
	if c1%notfound then
	 vIdProduto := 9999;
	end if;
	
	CLOSE c1;
	
	RETURN vIdProduto;
 END;
CURSORES - Cursor com parâmetro
Declaração:
	
	CURSOR c1 (pIdProdursor NUMBER) IS
	 SELECT id_produto FROM sn_produto WHERE id_produto = pIdProdursor;
Uso:
	 OPEN c1 (pIdProduto);
	 FETCH c1
	 INTO vIdProduto;
SEQUENCES
No Oracle, você pode criar um campo autonumérico usando sequências. Uma sequência é um objeto no Oracle que é usado para gerar uma sequência de números. Isso pode ser útil quando você precisa criar um número exclusivo para atuar como uma chave primária.
SEQUENCES
Criando:
CREATE SEQUENCE supplier_seq
 MINVALUE 0
 MAXVALUE 999999999999999999999999999
 START WITH 0
 INCREMENT BY 1
 CACHE 2;
TRIGGERS - AFTER DELETE
Uma trigger AFTER DELETE significa que a Oracle disparará a trigger depois que uma operação DELETE for executada.
TRIGGERS - AFTER DELETE
CREATE OR REPLACE TRIGGER tr_after_del_pedido
	AFTER DELETE
	 ON tb_pedido
	 FOR EACH ROW
 DECLARE
		v_username varchar2(10);
 BEGIN
	-- Find username of person performing the DELETE on the tableSELECT user INTO v_username FROM dual;
	INSERT INTO log_pedido ( id_pedido, custo_total, data_acao, deletado_por)
	 VALUES ( :old.id_pedido, :old.custo_total, sysdate, v_username );
	END;
TRIGGERS - AFTER INSERT
Uma trigger AFTER INSERT significa que a Oracle disparará a trigger depois que a operação INSERT for executada.
TRIGGERS - AFTER INSERT
CREATE OR REPLACE TRIGGER tr_after_ins_pedido
	AFTER INSERT
	 ON tb_pedido
	 FOR EACH ROW
	DECLARE
	.
	.
	.
	INSERT INTO auditoria_pedido
		( order_id, total_cost, username )
	VALUES ( :new.order_id, :new.total_cost, v_username );
TRIGGERS - AFTER UPDATE
Uma trigger AFTER UPDATE significa que a Oracle disparará a trigger depois que a operação UPDATE for executada.
TRIGGERS - AFTER UPDATE
CREATE OR REPLACE TRIGGER tr_after_upd_pedido
	AFTER UPDATE
	 ON tb_pedido
	 FOR EACH ROW
	DECLARE
	.
	.
	.
	INSERT INTO auditoria_pedido
	(order_id, quantity_before, quantity_after, username)
	VALUES (:new.order_id, :old.quantity, :new.quantity, v_username);
TRIGGERS – BEFORE – DEL, INS e UPD
BEFORE DELETE
BEFORE INSERT
BEFORE UPDATE
PROCEDURES
Assim como em outras linguagens, você pode criar suas próprias Procedures Oracle.
CREATE [OR REPLACE] PROCEDURE procedure_name
	 [ (parameter [,parameter]) ]
 IS
	 [declaration_section]
 BEGIN
	 executable_section
 [EXCEPTION
	 exception_section]
 END [procedure_name]; 
IN – Parâmetro de entrada da procedure.
OUT - Parâmetro de saída da procedure.
IN OUT - Parâmetro de entrada e saída da procedure.
FUNCTIONS
Assim como em outras linguagens, você pode criar suas próprias Functions Oracle.
CREATE [OR REPLACE] FUNCTION function_name
	 [ (parameter [,parameter]) ]
	 RETURN return_datatype
 IS | AS
	 [declaration_section]
 BEGIN
	 executable_section
	 return xxxxx;
 [EXCEPTION
	 exception_section]
 END [function_name];
IN – Não obrigatório, Parâmetro de entrada da procedure.
OUT – Não pode ser utilizado em uma função.
IN OUT - Não pode ser utilizado em uma função.