Introdução ao PL/SQL
SUMÁRIO
1 FUNDAMENTOS... 4
1.1 O
QUE É OPL/SQL ? ... 4
1.2 C
OMO É EXECUTADO UMPL/SQL
NOORACLE SERVER ... 4
1.3 E
STRUTURA DE UM BLOCOPL/SQL ... 5
1.4 T
IPOS DE BLOCOSPL/SQL ... 6
1.5 D
ECLARANDO VARIÁVEIS NOPL/SQL... 7
1.6 A
TRIBUINDO VALORES A VARIÁVEIS... 8
1.7 T
IPOS DE VARIÁVEIS... 9
1.8 O A
TRIBUTO%TYPE... 9
1.9 C
ONTROLANDO O FLUXO DE EXECUÇÃO(IF) ... 9
1.10 LOOP... 11
1.10.1 BASIC LOOP... 11
1.10.2 FOR LOOP ... 12
1.10.3 WHILE LOOP... 12
1.10.4 LOOPS Encadeados e LABELS... 13
2 TRABALHANDO COM CURSORES ... 14
2.1.1 Implicit Cursor ... 14
2.1.2 Explicit Cursor ... 14
2.1.2.1 Controlando um Cursor Explicito ... 15
2.1.2.2 Declarando um Cursor... 15
2.1.2.3 Abrindo um Cursor... 15
2.1.2.4 Buscando os dados do Cursor ... 16
2.1.2.5 Fechando o Cursor ... 16
2.1.2.6 Atributos para Cursores Explícitos... 16
2.1.2.7 Cursor Explícito com Parâmetros... 17
2.1.3 Cursor FOR LOOPS... 17
2.1.3.1 Cursor FOR LOOPS usando SUBQUERIES ... 18
3 TRABALHANDO COM VARIÁVEIS COMPOSTAS... 19
3.1 PL/SQL RECORDS... 19
3.1.1 Referenciando e Inicializando RECORDS ... 20
3.1.2 O atributo %ROWTYPE ... 20
3.2 PL/SQL TABLES ... 20
3.2.1 Criando uma PL/SQL TABLE ... 21
3.2.2 Referenciando uma PL/SQL TABLE... 21
3.3 PL/SQL TABLES
DERECORDS ... 22
3.3.1 Referenciando uma PL/SQL TABLE de RECORD. ... 22
3.3.2 Métodos PL/TABLE ... 23
3.3.3 Exceções de usuário ... 23
3.3.4 RAISE_APPLICATION_ERROR ... 24
4 STORED PROCEDURES ... 25
4.1 PROCEDURES ... 25
4.1.1 Executando uma PROCEDURE no SQL*PLUS ... 26
4.1.2 Opção DEFAULT para parâmetros ... 27
4.1.3 Métodos para especificar parâmetros ... 27
4.1.4 Chamando um procedimento a partir de um bloco PL/SQL ... 28
4.1.5 Removendo um procedimento do banco de dados... 28
4.2 FUNCTIONS... 29
4.2.1 Executando uma FUNCTION no SQL*PLUS ... 30
4.2.2 Removendo uma função do banco de dados... 30
4.2.3 Diferenças entre Procedures e Functions... 32
5 PACKAGES (PACOTES)... 33
5.1 E
SCOPOP
ÚBLICO... 33
5.2 E
SCOPOP
RIVADO... 33
5.3 C
OMPONENTES DE UMAPACKAGE ... 33
5.4 C
RIANDO UMA ESPECIFICAÇÃO DEP
ACOTE... 34
5.5 C
RIANDO O CORPO DOP
ACOTE... 34
5.6 E
XECUTANDO UM PROCEDIMENTO DE UMAPACKAGE
NOSQL*PLUS ... 36
5.7 V
ARIÁVEISG
LOBAIS... 36
5.8 R
EMOVENDOP
ACOTES... 36
6 TRIGGERS (GATILHOS) ... 37
6.1 U
SANDOP
REDICADOSC
ONDICIONAIS(DELETING/INSERTING/UPDATING)... 38
6.2 U
SANDO OSQ
UALIFICADORES:NEW
E:OLD ... 39
6.3 G
ERENCIANDOG
ATILHOS... 39
7 EXCEÇÕES NO PL/SQL ... 39
7.1 O
QUE É UMA EXCEÇÃO? ... 39
7.2 T
RATANDO UMA EXCEÇÃO... 39
7.3 P
ROPAGANDO EXCEÇÕES... 39
7.4 T
IPOS DE EXCEÇÕES... 39
7.4.1 Pré-definidas ... 39
7.4.2 Exceções não pré-definidas ... 39
7.4.3 Funções para tratamento de exceções... 39
7.4.3.1 Exemplos de valores do SQLCODE... 39
8 EXERCÍCIOS... 39
C
ONTROLE DO FLUXO DE EXECUÇÃO(IF/LOOP) ... 39
C
URSORES... 39
PLSQL R
ECORDS,PLSQL T
ABLES, F
UNCTIONSP
ROCEDURES... 39
P
ACKAGES, T
RIGGERS... 39
E
XCEPTIONS... 39
1 FUNDAMENTOS
1.1 O que é o PL/SQL ?
PROCEDURAL LANGUAGE/SQL (PL/SQL) é uma linguagem PROCEDURAL da ORACLE estendida do SQL. PL/SQL incorpora muitas características das linguagens de programação desenvolvidas durante os anos 1970 e 1980. Esta permite que a manipulação de dados e consultas seja incluída em unidades de programas, fazendo o PL/SQL uma poderosa linguagem de processamento de transação.
1.2 Como é executado um PL/SQL no ORACLE SERVER
Blocos de PL/SQL são passados e processados por uma PL/SQL ENGINE,
que pode residir dentro de uma ferramenta ORACLE ou dentro do ORACLE
SERVER. A PL/SQL ENGINE utilizada depende de onde o bloco PL/SQL é
invocado.
O PL/SQL ENGINE filtra os comandos SQL e manda individualmente o comando SQL para o SQL STATEMENT EXECUTOR no ORACLE SERVER. O PROCEDURAL STATEMENTS EXECUTOR processa o PL/SQL com os dados retornados do ORACLE SERVER. Isto reduz o trabalho de envio para o ORACLE SERVER (especialmente em CLIENT/SERVER).
Resumo
PL/SQL ENGINE :Pode estar contida dentro de ferramentas ORACLE como no ORACLE SERVER. Filtra os comandos SQL e envia para o ORACLE SERVER (SQL STATEMENT EXECUTOR).
PROCEDURE STATEMENT EXECUTOR :Recebe blocos PL/SQL e dados oriundos do ORACLE SERVER e processa o PL/SQL.
SQL STATEMENT EXECUTOR : Executa comandos SQL.
1.3 Estrutura de um bloco PL/SQL
Um bloco PL/SQL é composto por três seções : DECLARATIVE (opcional), EXECUTABLE (obrigatória) e EXCEPTION HANDLING (opcional). Unicamente as palavras BEGIN e END são exigidas.
Seção Significado
DECLARATIVE Contém todas as variáveis constantes, cursores e exceções dos usuários que são utilizadas nas outras seções.
EXECUTABLE Contém comandos SQL e PL/SQL
EXCEPTION HANDLING Especifica ações a serem executadas quando
um erro ocorre dentro de seção EXECUTABLE
Ex.
DECLARE
...(Variáveis, cursores, constantes, etc.) BEGIN
...(Comandos SQL e PL/SQL) EXCEPTION
...(Ações a serem executadas caso um erro aconteça) END;
1.4 Tipos de blocos PL/SQL
ANONYMOUS BLOCK: ANONYMOUS BLOCK são blocos não nomeados.
Eles são declarados em um ponto de uma aplicação onde eles serão executados e passados para o PL/SQL ENGINE executar.
SUBPROGRAMS : SUBPROGRAMS são blocos PL/SQL nomeados que podem ser executados e invocados. Você pode declarar ele geralmente como PROCEDURES ou FUNCTIONS. Você pode armazenar SUBPROGRAMS no SERVER ou a nível de aplicação.
ANONYMOUS PROCEDURE FUNCTIONS
[DECLARE]
BEGIN
- comandos [EXCEPTION]
END;
PROCEDURE nome IS
BEGIN
- comandos [EXCEPTIONS]
END;
FUNCTION nome RETURN DATATYPE IS
BEGIN - comandos [EXCEPTIONS]
END;
Obs. A função é similar a PROCEDURE, exceto que a função retorna
valor.
1.5 Declarando variáveis no PL/SQL
Variáveis são unidades de armazenamento de dados em um bloco PL/SQL, podendo seus valores serem alterados de acordo com a lógica do programa.O escopo de uma variável é o escopo da unidade de programa onde foi declarada.Uma variável deve necessariamente ser declarada, definindo seu tipo e tamanho(opcional dependendo do tipo)
Você precisa declarar todas as variáveis dentro da seção declarativa antes de referenciar dentro de um bloco PL/SQL. Você tem a opção de atribuir um valor inicial.
Sintaxe :
identifier [CONSTANT] DATATYPE [NOT NULL] [:= | DEFAULT expr]
Identifier é o nome da variável
CONSTANT contém uma variável que não pode ser alterada (precisam ser inicializadas)
DATATYPE é um scalar, composite ou lob
NOT NULL determina que a variável não pode ser nula.
(Precisam de um valor inicial)
expr é qualquer expressão PL/SQL. Uma literal, outra variável ou uma expressão envolvendo operações ou FUNCTIONS
Exemplos Declare
data_nascimento DATE;
cod_departamento NUMBER(2) NOT NULL := 10;
cidade VARCHAR2(30) := 'PORTO ALEGRE';
dolar CONSTANT NUMBER := 1.8;
cod_funcionario NUMBER (10) DEFAULT 10;
Obs.
♦ Identifiers não podem ser maiores que 30 caracteres e o primeiro caracter deve ser uma letra.
♦ Por DEFAULT variáveis são inicializadas com NULL
1.6 Atribuindo valores a variáveis
Para atribuir valores a variáveis, use o operador :=.
Sintaxe.
identifier := expr;
identifier : o nome da variável
expr : pode ser variável, literal ou uma FUNCTION;
Exemplos
BEGIN
dolar := 1.4;
data_nascimento := '16-MAY-2000';
cod_departamento := 10;
data_atual1 := SYSDATE;
data_atual2 := to_date ('16-MAY-2000');
END;
Obs.
- Literais STRINGS são atribuídas com aspas simples - por
exemplo ´olá´. Se você deseja colocar uma aspa dentro da
STRING, coloque duas. - ' olá '' '.
1.7 Tipos de variáveis
TIPO DESCRIÇÃO
VARCHAR2(SIZE) Tipo base para variáveis caracter de Tamanho variável. até 32767. Não há tamanho DEFAULT para a
definição de variáveis e constantes.
NUMBER (PRECISION, SCALE) Números fixos e com ponto flutuante
DATE Tipo base para data e hora
CHAR[(SIZE)] Tipo base para variáveis de tamanho
LONG Tipo base para variáveis caracter de
tamanho variável. acima de 32760 bytes. O máximo é 2147483647 bytes.
LONG ROW Tipo base para variáveis binárias e bytes. Não é implementada no PL/SQL;
BOOLEAN TRUE,FALSE ou NULL
BINARY_INTEGER Tipo base para números inteiros entre -2147483647 e -2147483647.
PLS_INTEGER Tipo base para números inteiros entre -2147483647 e -2147483647
1.8 O Atributo %TYPE
Serve para declarar uma variável de acordo com uma coluna de uma tabela ou com base em outra variável já declarada.
1.9 Controlando o fluxo de execução (IF)
Você pode alterar o fluxo de execução usando o comando condicional IF.
Segue abaixo as três formas para o IF.
- IF-THEN-END IF
- IF-THEN-ELSE-END IF
- IF-THEN-ELSIF-END IF
Sintaxe
IF condição THEN comandos;
[ELSIF condição THEN comandos ]
[ELSE
comandos;]
END;
Exemplos 1)
...
IF ESTADO= 'RS' THEN
NOME := 'RIO GRANDE DO SUL';
END IF;
...
2)
...
IF ESTADO = 'RS' THEN
NOME := 'RIO GRANDE DO SUL';
ELSE
NOME := 'OUTRO QUALQUER';
END IF;
...
3)
...
IF ESTADO = 'RS' THEN
NOME := 'RIO GRANDE DO SUL';
ELSIF ESTADO = 'SP' THEN NOME := 'SÃO PAULO';
END IF;
...
4)
...
IF ESTADO = 'RS' THEN
NOME := 'RIO GRANDE DO SUL';
ELSIF ESTADO = 'SP' THEN NOME := 'SÃO PAULO';
ELSE
NOME := 'OUTRO QUALQUER';
END IF;
...
1.10 LOOP
Existem três tipos de LOOP :
♦ BASIC LOOP
♦ FOR LOOP
♦ WHILE LOOP
1.10.1 BASIC LOOP
Sintaxe LOOP
comandos
EXIT [WHEN condição];
END LOOP;
Ex.
DECLARE
contador NUMBER (2) := 1;
BEGIN LOOP
comandos;
contador := contados + 1;
EXIT WHEN contador > 10;
END LOOP;
1.10.2 FOR LOOP
Sintaxe
FOR counter IN [REVERSE] lower_bound..upper_bound LOOP comandos;
END LOOP;
counter é uma variável implicitamente declarada do tipo inteiro (INTEGER) com valores automaticamente incrementados ou decrementadas por 1 para cada execução do LOOP.
REVERSE causa um decremento no counter. O DEFAULT é Ex.
FOR I IN 1..10 LOOP comandos;
END LOOP;
1.10.3 WHILE LOOP
Sintaxe
WHILE condition LOOP comandos;
END LOOP;
Condition é uma variável BOOLEAN ou uma expressão
Ex.
WHILE I <=10 LOOP I := I + 1;;
END LOOP;
1.10.4 LOOPS Encadeados e LABELS
Você pode encadear LOOPS. Por exemplo, colocar um FOR LOOP dentro de um WHILE LOOP. Normalmente a terminação do LOOP mais interno não termina a execução do LOOP mais externo. No entanto, você pode usar um LABEL para o LOOP mais externo e forçar a saída através do comando EXIT.
Exemplo BEGIN
<<LOOP_EXTERNO>>
LOOP
contador := contador + 1;
EXIT WHEN contador > 10;
<<LOOP_INTERNO>>
LOOP
EXIT LOOP_EXTERNO WHEN...
EXIT LOOP_INTERNO WHEN...
END LOOP LOOP_INTERNO;
END LOOP LOOP_EXTERNO;
END;
2 TRABALHANDO COM CURSORES
O ORACLE SERVER usa uma área chamada SQL áreas para executar um comando SQL e armazenar as informações. Você pode usar cursores PL/SQL para nomear um SQL e acessar as informações armazenadas.
Tipos de cursores
♦ Implicit são declarados por um PL/SQL implicitamente para todos os DML e comandos SELECT que retornam um
linha.
♦ Explicit para consultas que retornam mais de uma linha.
Cursores explícitos são declarados e nomeados pelo programados e manipulados através de comandos específicos.
2.1.1 Implicit Cursor
O ORACLE SERVER implicitamente abre um cursor para processar cada comandos SQL não associado com um cursor explicitamente declarado.
2.1.2 Explicit Cursor
Um cursor explícito individualmente processa cada linha retornada por um comando SELECT. O conjunto de linhas retornadas por SELECT é chamado por result set.
Ex.
Result Set 2234 JOÃO 4543 PEDRO
Cursor==> 3233 MARIA Linha corrente
2221 CARLOS
2.1.2.1 Controlando um Cursor Explicito
Não
|---|
| | Sim
DECLARE ==> OPEN ==> FETCH ==>EMPTY ?==> CLOSE
Cria um Abre o Lê a corrente Testa a Fecha
SQL area cursor. linha para existência de cursor
Seta como variáveis Linhas
ativo Retorna para
o FETCH se foram encontradas linhas
2.1.2.2 Declarando um Cursor
Sintaxe
CURSOR cursor_name IS comando_select;
cursor_name é um identificador PL/SQL
comando_select é um comando SELECT (sem o INTO) Ex.
DECLARE
CURSOR c1 IS
SELECT empno, ename FROM emp;
BEGIN ...
2.1.2.3 Abrindo um Cursor Sintaxe
OPEN cursor_name;
2.1.2.4 Buscando os dados do Cursor
Sintaxe
FETCH cursor_name INTO [variable1, variable2,...]
| record_name];
cursor_name o nome do cursor previamente declarado variable a variável de saída para armazenar o retorno record_name o nome do RECORD onde os dados serão
armazenados. O RECORD pode ser declarado usando o %ROWTYPE.
Ex.
FETCH c1 INTO v_empno, v_ename;
2.1.2.5 Fechando o Cursor
O comando CLOSE fecha o cursor. Desabilita o cursor e o resultado tornando-o como indefinido. Feche o cursor após completar o processamento do comando SELECT. Isto permite que o cursor seja reaberto se necessário. Se você executar o FETCH em um cursor já fechado, acontecerá a exceção INVALID_CURSOR.
2.1.2.6 Atributos para Cursores Explícitos
Segue abaixo alguns atributos que podemos utilizar para auxiliar no processamento de cursores.
NOME RETORNO Descrição
%ISOPEN BOOLEAN Retorna TRUE e o cursor está aberto
%NOTFOUND BOOLEAN Retorna TRUE se o FETCH não retornou linhas
%FOUND BOOLEAN Retorna TRUE se o FETCH retornou linhas
%ROWCOUNT NUMBER Retorna o total de linhas
retornadas
2.1.2.7 Cursor Explícito com Parâmetros
Você pode passar parâmetros para o cursor antes que este seja aberto.
Isto significa que você pode abrir e fechar um cursor várias vezes em um bloco PL/SQL, retornando diferentes informações.
Sintaxe
CURSOR cursor_name
[(parameter_name DATATYPE,...)] IS comando_select;
cursor_name é um identificador PL/SQL de um cursor previamente declarado.
parameter_name o nome do parâmetro DATATYPE um scalar DATATYPE comando_select um SELECT sem o INTO
Ex.
DECLARE
CURSOR c1 (v_deptno NUMBER, v_job VARCHAR2) IS SELECT empno,ename
FROM emp
WHERE deptno = v_deptno AND job = v_job;
2.1.3 Cursor FOR LOOPS
Um cursor FOR LOOP processa linhas como um cursor explícito. Ele é
um fácil de utilizar porque o cursor quando aberto, as linhas são retornadas
para cada interação do LOOP, e é fechado automaticamente quando todas as
linhas foram processadas. O LOOP termina automaticamente no fim da ultima
interação quando a ultima linha é retornada.
Sintaxe
FOR record_name IN cursor_name LOOP comando1;
comando2;
END LOOP;
record_name é um nome de um RECORD declarado implicitamente
cursor_name é um identificador PL/SQL de u cursor previamente declarado.
Ex.
DECLARE
CURSOR c1 IS
SELECT empno,ename FROM emp;
BEGIN
FOR emp_record IN c1 LOOP
-- O OPEN e o FETCH são implicitamente executados IF emp_record.empno = 7839 THEN
....
END IF;
END LOOP; -- O CLOSE é executado implicitamente.
END;
2.1.3.1 Cursor FOR LOOPS usando SUBQUERIES
Exemplo BEGIN
FOR emp_record IN ( SELECT empno,ename FROM emp) LOOP
-- O OPEN e o FETCH são implicitamente executados IF emp_record.empno = 7839 THEN
....
END IF;
END LOOP; -- O CLOSE é executado implicitamente.
END;
3 TRABALHANDO COM VARIÁVEIS COMPOSTAS
COMPOSITE DATATYPES
COMPOSITE DATATYPES (também conhecida como COLLECTIONS) são RECORD, TABLE,NESTED TABLE e VARRAY.
3.1 PL/SQL RECORDS
Um RECORD é um grupo de itens armazenados em um campo. Por exemplo, suponha que você tenha diferentes tipos de dados de um empregado como nome, salário, data de nascimento. Estes dados possuem diferentes tipos mas estão logicamente ligados a um empregado. Sendo assim, defini-se um RECORD para agrupar os campos do empregado. Segue abaixo algumas considerações :
- cada RECORD pode ter muitos campos
- RECORD pode ter atribuído valores DEFAULT e podem ser definidos como NOT NULL
- campos sem valor DEFAULT são inicializados com NULL
- você pode declarar e referenciar RECORDS encadeados. Um RECORD pode ser compone nte de outro RECORD.
Sintaxe
TYPE type_name IS RECORD
(field_declaration [, field_declaration]....) identifier type_name
O tipo do field_declaration pode ser definido de acordo a sintaxe abaixo:
field_name {field_type | variable%TYPE
| table.column.%TYPE | table%ROWTYPE } [[NOT NULL] {:= | DEFAULT : expr]
type_name o nome do tipo RECORD. Este identificador é usado para declarar registros.
field_name este é o nome de um campo dentro de um RECORD.
field type este é o tipo do campo.
Exemplo DECLARE
TYPE emp_record_type IS RECORD
(nome VARCHAR2(30)
,cargo VARCHAR2(10)
,salario NUMBER (13,2));
emp_record emp_record_type;
3.1.1 Referenciando e Inicializando RECORDS
Campo de uma variável do tipo RECORD são acessados pelo nome. Para referenciar ou inicializar um campo de um RECORD, você precisa especificar o nome do RECORD seguido do nome do campo.
Ex. emp_record.nome := 'PEDRO';
3.1.2 O atributo %ROWTYPE
Para declarar uma variável baseada em tabela ou VIEW, você deve usar o %ROWTYPE.
Ex.
DECLARE
emp_record empregados%ROWTYPE
3.2 PL/SQL TABLES
Objetos do tipo TABLE são chamados de PL/SQL TABLES. Eles são
modelados como uma TABLE (DATABASE TABLE). PL/SQL TABLE usam uma
PRIMARY KEY que permite acessar os registros.
Uma PL/SQL TABLE : - é similar a um ARRAY
- Precisa ter dois componentes
- Uma PRIMARY KEY do tipo BINARY_INTEGER que indexa a PL/SQL TABLE
- Uma coluna do tipo SCALAR ou RECORD
Sintaxe
TYPE type_name IS TABLE OF
{ column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
[INDEX BY BINARY INTEGER];
identifier type_name
3.2.1 Criando uma PL/SQL TABLE
Há dois passos para criação de uma PL/SQL TABLE.
1. Declare um tipo TABLE
2. Declare uma variável do tipo criado (passo 1)
DECLARE
TYPE date_table_type IS TABLE OF DATE INDEX BY BINARY INTEGER;
date_table date_table_type;
3.2.2 Referenciando uma PL/SQL TABLE
Sintaxe
PL/SQL_TABLE_NAME(primary_key_value) := value;
Ex.
date_table (1) := SYSDATE;
3.3 PL/SQL TABLES de RECORDS
Você pode definir uma PL/SQL TABLE com base na estrutura de RECORD. Para isto, você pode usar o atributo %ROWTYPE ou um RECORD já definido.
Exemplo DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
3.3.1 Referenciando uma PL/SQL TABLE de RECORD.
Exemplo
dept_table(15).location := 'Porto Alegre';
3.3.2 Métodos PL/TABLE
Método Descrição
EXISTS (n) Retorna TRUE se o elemento(n) existe na PL/SQL tabela
COUNT Retorna o número de elementos da PL/SQL TABLE FIRST/LAST Retorna a primeira linha e ultima da PL/SQL TABLE.
Retorna NULL se a PL/SQL TABLE está vazia PRIOR(n) Retorna o índice que precede o índice n em uma
PL/SQL TABLE
NEXT(n) Retorna o índice que sucede o índice n em uma PL/SQL TABLE
EXTEND(n,i) Para aumentar o TABLE de uma PL/SQL TABLE.
EXTEND coloca um elemento NULL no final da PL/SQL TABLE
EXTEND(n) coloca n elementos NULL na PL/SQL TABLE
EXTEND(n,i) coloca n copias do elemento i no final da PL/SQL TABLE
TRIM Remove um elemento do final da PL/SQL TABLE DELETE DELETE deleta todas as linhas da PL/SQL
TABLE
DELETE (n) deleta o n elemento da PL/SQL TABLE
DELETE (m,n) remove os elementos no intervalo entre m e n.
3.3.3 Exceções de usuário
Estas exceções não são vinculadas a um erro ORACLE e são levantadas
através do comando RAISE. Geralmente são utilizadas para o tratamento de
erros de regra de negócio do sistema .
Ex.
DECLARE
e_limite_credito EXCEPTION;
BEGIN ...
RAISE e_limite_credito;
...
EXCEPTION
WHEN e_limite_credito;
...
END;
3.3.4 RAISE_APPLICATION_ERROR
É uma PROCEDURE que permite gerar um erro e mensagem.
Sintaxe
raise_application_error (error number, message [, {TRUE | FALSE}];
error_number : erro definido pelo usuário entre -20000 e -20999.
message : a mensagem do erro.
Ex.
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20001,'Dolar não cadastrado');
END;
4 STORED PROCEDURES
4.1 PROCEDURES
Podemos definir uma PROCEDURE como um bloco PL/SQL nomeado que pode obter parâmetros (argumentos) e executar alguma ação. Uma PROCEDURE pode ficar armazenada dentro de forms ou no banco de dados (STORED PROCEDURE). Sendo armazenada no banco de dados, estas provêm a reutilização. Uma vez validados, eles podem ser usados em várias aplicações, Se a definição for alterada, somente o procedimento será afetado, o que simplifica bastante a manutenção.
Sintaxe (criação no banco de dados)
CREATE [OR REPLACE] PROCEDURE nome_procedimento [parâmetro1 [modo1] tipodedados1,
parâmetro2 [modo2] tipodedados2, . . . )
IS|AS
PL/SQL BLOCK
nome_procedimento Nome da PROCEDURE parâmetro Nome da variável PL/SQL
modo IN (DEFAULT)
OUT IN OUT
tipo de dados Tipo de dados do parâmetro - Number,varchar2,date,etc.(sem
tamanho) - %TYPE - %ROWTYPE
- Um tipo pré-definido em uma PACKAGE PL/SQL Block Bloco PL/SQL que define as ações executadas
pelo procedimento
Ex.
CREATE OR REPLACE PROCECURE aumenta_salario (p_cd_empregado IN emp.cd_empregado%TYPE, p_percentual IN NUMBER)
IS BEGIN
UPDATE emp
SET salario = salario * p_percentual WHERE cd_empregado = p_cd_empregado;
END;
/
PROCEDURE created
4.1.1 Executando uma PROCEDURE no SQL*PLUS
Utilize o comando EXECUTE para executar uma PROCEDURE SQL> EXECUTE aumenta_salario (10,1.10);
PL/SQL PROCEDURE successfull completed.
Obs.
- Para executar uma PROCEDURE com parâmetro OUT ou IN OUT é necessário declarar uma variável e referenciá-la na chamada da PROCEDURE com dois pontos para receber o retorno.
Ex.
SQL> VARIABLE g_name VARCHAR2(30) SQL> EXECUTE retorna_nome (10,:g_name);
PL/SQL PROCEDURE successfull completed.
- O SQL*PLUS não permite a declaração de variáveis BOOLEAN
4.1.2 Opção DEFAULT para parâmetros
Você pode especificar um valor DEFAULT para um parâmetro caso este não seja informado. Para isto é necessário acrescentar a clausula DEFAULT após a definição do tipo de dados do parâmetro.
CREATE OR REPLACE PROCECURE aumenta_salario (p_cd_empregado IN emp.cd_empregado%TYPE, p_percentual IN NUMBER DEFAULT 1.10) IS
BEGIN
UPDATE emp
SET salario = salario * p_percentual WHERE cd_empregado = p_cd_empregado;
END;
/
4.1.3 Métodos para especificar parâmetros
Existem três formas de especificar os parâmetros na chamada de um procedimento : posicional, nomeado e combinação
♦ Posicional Assume os valores na ordem que são passados
♦ Nomeado Assume o valor associado ao nome do parâmetro através do uso do =>.
♦ Combinada Assume os primeiro parâmetros como posicional e os outros através do nomeado.
Exemplos
Considere a seguinte PROCEDURE
prc_aumenta_precos ( cd_empresa NUMBER
,cd_filial NUMBER
,cd_produto NUMBER);
chamada posicional
EXECUTE prc_aumenta_precos (1,12,1223);
chamada nomeada
EXECUTE prc_aumenta_preco
(cd_produto=>1223,cd_empresa=>1,cd_filial=>12);
chamada combinada
EXECUTE prc_aumenta_preco
(1,cd_produto=>1223,cd_filial=>12);
4.1.4 Chamando um procedimento a partir de um bloco PL/SQL
Você pode chamar um procedimento dentro de um bloco PL/SQL (anônimo ou armazenado no banco de dados.
Exemplo BEGIN
prc_aumenta_precos (1,12,1223);
END;
4.1.5 Removendo um procedimento do banco de dados
Uma vez criado (CREATE) a PROCEDURE fica armazenada no banco de dados. Para remover, utilize o comando DROP no SQL*PLUS.
Exemplo
SQL> DROP PROCEDURE prc_aumenta_precos;
PROCEDURE dropped;
4.2 FUNCTIONS
Assim como a PROCEDURE, podemos definir uma função como um bloco PL/SQL nomeado que pode obter parâmetros (argumentos) e executar alguma ação. A única diferença é que a função necessita o retorno de um valor.
Sintaxe (criação no banco de dados)
CREATE [OR REPLACE] FUNCTION nome_procedimento [parâmetro1 [modo1] tipodedados1,
parâmetro2 [modo2] tipodedados2, . . . )
RETURN tipo de dados IS|AS
PL/SQL Block
nome_procedimento Nome da PROCEDURE parâmetro Nome da variável PL/SQL
modo IN (DEFAULT)
OUT IN OUT
tipo de dados Tipo de dados do parâmetro - Number,varchar2,date, etc.(sem
tamanho) - %TYPE - %ROWTYPE
- Um tipo pré-definido em uma PACKAGE
RETURN tipo de dados Tipo de dados do retorno da função.
Segue as mesmas regras do tipo de dados dos parâmetros
PL/SQL Block Bloco PL/SQL que define as ações
executadas pela função
Ex.
CREATE OR REPLACE FUNCTION retorna_nome
(p_cd_empregado IN emp.cd_empregado%TYPE) RETURN emp.nome%TYPE
IS
v_nome emp.nome%TYPE BEGIN
SELECT nome INTO v_nome FROM emp
WHERE cd_empregado = p_cd_empregado;
RETURN v_nome;
END;
/
PROCEDURE created
4.2.1 Executando uma FUNCTION no SQL*PLUS
Utilize o comando EXECUTE para executar uma função e PRINT para visualizar o valor a variável de retorno (g_nome).
Ex.
SQL> VARIABLE g_nome VARCHAR2(30) SQL> EXECUTE :g_nome retorna_nome (10);
PL/SQL PROCEDURE successfull completed.
SQL> PRINT g_nome
4.2.2 Removendo uma função do banco de dados
Uma vez criado (CREATE) a PROCEDURE fica armazenada no banco de dados. Para remover, utilize o comando DROP no SQL*PLUS.
Ex.
SQL> DROP FUNCTION retorna_nome;
FUNCTION dropped;
4.2.3 Diferenças entre Procedures e Functions
Ambas possuem funcionalidades semelhantes,porém a function por si só representa um valor, ou seja, mesmo não tendo parâmetros do tipo OUT ela retorna um valor.Procedures só retornam valores se possuírem parâmetros do tipo OUT.
Outra diferença importante é a possibilidade de se executar funções dentro de um comando SELECT desde que elas não possuam parâmetros OUT.
Já procedures não oferecem essa funcionalidade.
Ex.: SELECT FNC_VALIDA(‘76345489020’) CGC_VALIDO FROM DUAL;
CGC_VALIDO
---
TRUE
5 PACKAGES (PACOTES)
Uma PACKAGE (pacote) tem a finalidade de agrupar itens FUNCTIONS / PROCEDURES/variáveis) relacionadas. Por exemplo, podemos agrupar todos as funções e PROCEDURES relacionadas ao sistema financeiro.
Um componente de uma PACKAGE (FUNCTION/PROCEDURE/variável) pode ser público ou privado.
5.1 Escopo Público
Quando um elemento de um PACKAGE pode ser referenciado ou executado por outros objetos ORACLE (PROCEDURE/FUNCTION, etc.).
Para isto, o componente (FUNCTION,PROCEDURE) deve ser declarado na especificação do pacote.
5.2 Escopo Privado
Só pode ser referenciado por outras construções que são parte do mesmo pacote.
5.3 Componentes de uma PACKAGE 1 - Variável pública (global)
2 - Procedimento público 3 - Procedimento privado
4 - Variável privada (local do pacote)
5 - Variável local (local do procedimento)
5.4 Criando uma especificação de Pacote
Sintaxe
CREATE OR REPLACE PACKAGE nome_pacote IS | AS
tipo público e declarações de item especificações do SUBPROGRAM END nome_pacote
nome_pacote Nome do pacote
tipo público e declarações de item Declara variáveis, constantes, cursores, exceções e tipos especificações do SUBPROGRAM declara os SUBPROGRAMS
PL/SQL Obs.
- Todos os itens declarados na especificação são do escopo público. Ou seja, são itens (PROCEDURES, FUNCTIONS) que podem ser chamados repetitivamente por outras construções no mesmo pacote ou de fora do pacote
Ex.
CREATE OR REPLACE PACKAGE pck_financeiro IS
PROCEDURE prc_gera_cobranca (p_data IN date);
END pck_financeiro;
/
5.5 Criando o corpo do Pacote
O corpo de uma PACKAGE é o local onde são definidas as ações para cada
programa (público ou privada). É no corpo que encontra-se o programa
(PL/SQL) de cada elemento (PROCEDURE, FUNCTIONS). Também podem ser
definidas as variáveis privadas da PACKAGE.
CREATE [OR REPLACE] PACKAGE BODY nome_pacote IS | AS
tipo público e declarações de item especificações do SUBPROGRAMA.
END nome_pacote
nome_pacote Nome do pacote
tipo público e declarações de item Declara variáveis, constantes, cursores, exceções e tipos especificações do SUBPROGRAMA declara os SUBPROGRAMS PL/SQL públicos e privados
Obs.
- a ordem que os SUBPROGRAMS privados (PROCEDURES, FUNCTIONS) são definidos no corpo é de grande importância.
Você deve definir ou declarar SUBPROGRAMS privados antes de chamá-los a partir de outros. No caso de SUBPROGRAMS públicos tal definição não faz-se necessária.
Ex.
CREATE OR REPLACE PACKAGE BODY pck_financeiro IS
PROCEDURE prc_gera_cobranca (p_data IN date) IS BEGIN
INSERT INTO COBRANCAS
(SELECT nome_cliente,cpf,valor,dt_vencimento FROM DEBITOS
WHERE dt_vencimento> p_data AND dt_pagamento IS NULL);
END;
END pck_financeiro;
/
5.6 Executando um procedimento de uma PACKAGE no SQL*PLUS
Para executar uma PROCEDURE/FUNCTIONS no SQL*PLUS utilize o comando EXECUTE seguido do nome_do_pacote.nome_do_SUBPROGRAMA.
SQL> EXECUTE pck_financeiro.prc_gera_cobranca(SYSDATE);
Obs. somente podem ser executados os SUBPROGRAMS que são públicos. Ou seja, estão declarados na especificação.
5.7 Variáveis Globais
Você pode declarar variáveis públicas (globais) que irão existir durante a sessão do usuário.
Exemplo
CREATE OR REPLACE PACKAGE pck_parametros IS cd_empresa_DEFAULT NUMBER := 1;
cd_filial_DEFAULT NUMBER := 12;
END pck_parametros;
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE dbms_output.put_line -
('Empresa DEFAULT = '||pck_parametros.cd_empresa_DEFAULT);
Empresa DEFAULT=12
PL/SQL PROCEDURE sucessfull completed.
5.8 Removendo Pacotes
Para remover uma especificação e o corpo utilize o comando DROP PACKAGE.
Ex. DROP PACKAGE nome_do_pacote
Para remover somente o corpo utilize o comando DROP PACKAGE BODY.
Ex. DROP PACKAGE BODY nome_do_pacote
6 TRIGGERS (gatilhos)
Uma TRIGGER é um bloco PL/SQL executado de forma implícita sempre que um determinado evento ocorre. Por exemplo, pode-se definir uma TRIGGER que é disparada sempre quando um funcionário é incluído.
SINTAXE
CREATE OR [REPLACE] TRIGGER nome_gatilho tempo
evento1 [OR evento2 or evento3]
ON nome_tabela [FOR EACH ROW]
[WHEN condição]
BEGIN
corpo_gatilho;
END;
nome_gatilho é o nome do gatilho
tempo Indica o tempo de disparo em relação ao evento ocorrido
BEFORE
UPDATE [OF column]
DELETE
tabela Indica a tabela ou VIEW
FOR EACH ROW Determina que o gatilho deve ser disparado a cada linha inserida
WHEN Especifica a restrição de gatilho (Esse predicado é condicional e avaliado para cada linha a fim de determinar se o corpo do gatilho é executado ou não corpo do gatilho É o corpo do gatilho que define as ações executadas
(comandos PL/SQL)
Ex
CREATE OR REPLACE TRIGGER insert_emp BEFORE INSERT ON EMP
BEGIN
IF TO_CHAR(SYSDATE,'D') IN (7,1) THEN RAISE_APPLICATION_ERROR
(-20050, 'Você só pode incluir empregados em dias úteis');
END IF;
END;
6.1 Usando Predicados Condicionais (DELETING/INSERTING/UPDATING)
Em algumas situações faz-se necessário descobrir o evento que foi disparado. Utilize os predicados DELETING/INSERTING e UPDATING para isto.
Ex.
CREATE OR REPLACE TRIGGER insert_emp
BEFORE INSERT OR UPDATE OR DELETE ON EMP BEGIN
IF TO_CHAR(sysdate,'D') IN (7,1) THEN IF INSERTING
RAISE_APPLICATION_ERROR (-20050,
'Você só pode incluir empregados em dias úteis');
ELSIF UPDATING('SAL')
RAISE_APPLICATION_ERROR (-20051,
'Você só pode alterar salário de empregados em dias úteis');
ELSIF DELETING
RAISE_APPLICATION_ERROR (-20052,
'Você só pode excluir empregados em dias úteis');
END IF;
END IF;
END;
6.2 Usando os Qualificadores :NEW e :OLD
Você pode usar os valores das colunas da tabela. Para isto utilize os qualificadores NEW e OLD. Em uma TRIGGER faça referencia ao valor de uma coluna antes e depois da alteração dos dados.
OPERAÇÃO Valor antigo Valor novo
INSERT NULL Valor inserido
UPDATE Valor antes de atualizar
Valor após atualizar DELETE Valor antes da
deletar
NULL
Ex.
CREATE OR REPLACE TRIGGER insert_emp BEFORE INSERT ON EMP
FOR EACH ROW BEGIN
IF :NEW.SALARIO = 0 THEN
RAISE_APPLICATION_ERROR(-20050, 'Salário não pode ser 0');
END IF;
END;
Obs.
- Os qualificadores OLD e NEW estão disponíveis nos gatilhos ROW (FOR EACH ROW).
- Preceda esses qualificadores como dois pontos em cada instrução SQL e PL/SQL
- Não há prefixo dois pontos (:) caso os qualificadores sejam
referenciados à condição de restrição WHEN.
6.3 Gerenciando Gatilhos
Segue abaixo alguns comandos utilizados para o gerenciamento de Gatilhos :
♦ Habilitando / Desabilitando um Gatilho
ALTER TRIGGER nome_gatilho DISABLE/ENABLE
♦ Habilitando/Desabilitando todos os Gatilhos de uma tabela ALTER TRIGGER nome_gatilho ALL_TRIGGERS
♦ Recompilando um TRIGGER
ALTER TRIGGER nome_gatilho COMPILE
♦ Removendo um gatilho
DROP TRIGGER nome_gatilho
7 EXCEÇÕES NO PL/SQL
7.1 O que é uma exceção ?
Identificador em PL/SQL que é levantado durante a execução de um bloco PL/SQL. Tal exceção pode acontecer através de um erro ORACLE ou da execução do comando RAISE. Um bloco sempre é finalizado quando uma exceção acontece. Mas você pode declarar uma exceção para manipular e executar ações caso um erro aconteça.
Ex.
Um erro ORACLE (ORA-01403) acontece quando nenhum registro é retornado de um SELECT. Para este erro, é gerada a exceção NO_DATA_FOUND.
7.2 Tratando uma exceção
Se uma exceção acontece durante a execução de um PL/SQL e esta é tratada, a exceção não propaga para seções externas. O PL/SQL bloco é executado com sucesso.
Ex.
DECLARE BEGIN
a exceção é levantada EXCEPTION
a exceção é tratada
END
7.3 Propagando exceções
Quando uma exceção não tratada no bloco PL/SQL em que ela acontece, esta é propagada a blocos mais externos. Caso não seja encontrada a execução do PL/SQL é finalizada com falha.
Ex.
DECLARE BEGIN
BEGIN
a exceção é levantada EXCEPTION
a exceção não é tratada
END;
EXCEPTION
a exceção não é tratada END;
Obs. A propagação de exceção acontece também em STORED PROCEDURE. Ou seja, caso uma PROCEDURE retorne uma exceção, esta pode ser tratada no bloco PL/SQL que a chamou. Caso aconteça, o bloco PL/SQL é finalizado com erro.
7.4 Tipos de exceções
Pré-definida
Não pré-definidas
