INTRODUÇÃO AO PL/SQL

• PL/SQL é uma linguagem de programação em blocos;
• Uma unidade de programa pode ser Nomeada ou Não Nomeada;
• Blocos Não Nomeados também são conhecidos como blocos anônimos;

Sintaxe geral:

[DECLARE]
declaration_statements
BEGIN
execution_statements
[EXCEPTION]
exception_handling_statements
END;
/  

• Em uma declaração de blocos, podem ser definidos tipos de dados, estruturas, variáveis;
• Estruturas também podem ser localmente chamadas de funções, procedimentos e cursores;
• BEGIN e END são palavras reservadas, com o uso opcional da palavra EXCEPTION

• O mínimo código de um bloco anônimo é:

BEGIN
NULL;
END;
/

• A exceção do bloco de manipulação permite você gerenciar exceções; 

• Blocos nomeados têm uma ligeira diferença dos blocos não nomeados, porque os blocos nomeados ficam armazenados no banco de dados;
• Blocos nomeados tem também uma sessão de declaração, chamado como header (cabeçalho);
• O nome e a lista de parâmetros são chamados de uma assinatura de uma sub-rotina;
• As mesmas regras são aplicadas para os corpos dos objetos (bodies); 

Exemplo de bloco nomeado:

PROCEDURE procedure_name
( parameter1 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type
, parameter2 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type
, parameter(n+1) [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type )
[ AUTHID {DEFINER | CURRENT_USER}]
declaration_statements
BEGIN
  execution_statements
[EXCEPTION]
  exception_handling_statements
END;
/

Habilitando OUTPUT em um bloco PL/SQL:

• Escreva antes do bloco PL/SQL:

  SET SERVEROUTPUT ON;

Use o pacote (package) pré-definido DBMS_OUTPUT   
Exemplo do DBMS_OUTPUT  :

DBMS_OUTPUT.PUT_LINE ('O primeiro nome de funcionário é ' || v_fname);
... 

• Escreva antes do bloco PL/SQL:

  SET SERVEROUTPUT ON;

Use o pacote (package) pré-definido DBMS_OUTPUT.PUT_LINE(‘string’) 

Exemplo:

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE  ('IMPRESSAO DE LINHA  USANDO PUT_LINE');
END;
/  

CONFIGURAÇÃO DO SERVEROUTPUT

–CONFIGURANDO O AMBIENTE, QTD DE BUFFER SET SERVEROUTPUT ON SIZE 1000000;

DBMS_OUTPUT.ENABLE(1000000); 
DBMS_OUTPUT.DISABLE; 

–EXEMPLO

BEGIN DBMS_OUTPUT.PUT('LINHA '); 
  DBMS_OUTPUT.PUT('UM.'); 
  DBMS_OUTPUT.NEW_LINE; 
  DBMS_OUTPUT.PUT_LINE('LINHA DOIS.'); 
END; 
/

TIPO BOLEANO

• O tipo boleado de dados por ter três possibilidade de valores: TRUE, FALSE e NULL;
• Pode ser declarado como e exemplo a seguir;

SET SERVEROUTPUT ON;
DECLARE
  var1 BOOLEAN;
  var2 BOOLEAN NOT NULL := TRUE;
  var3 BOOLEAN NOT NULL := FALSE;
BEGIN
  DBMS_OUTPUT.PUT_LINE(CASE WHEN var1 THEN 'TRUE'
                         WHEN var1 IS NULL THEN 'NULL'
                         ELSE 'FALSE'
                       END );

  DBMS_OUTPUT.PUT_LINE(CASE WHEN var2 THEN 'TRUE'
                       WHEN var2 IS NULL THEN 'NULL'
                       ELSE 'FALSE'
                     END);

  DBMS_OUTPUT.PUT_LINE(CASE WHEN var3 THEN 'TRUE'
                       WHEN var3 IS NULL THEN 'NULL'
                       ELSE 'FALSE'
                     END);
END;
/

CARACTERES E STRINGS

• O tamanho da string é declarado pelo número de bytes ou caracteres;
• Qualquer tentativa de armazenamento maior que o tamanho suportado, pode ser tratado com exceção;

SET SERVEROUTPUT ON;
DECLARE
  c CHAR(32767) := ' ';
  v VARCHAR2(32767) := ‘ ';
BEGIN
  DBMS_OUTPUT.PUT_LINE(' c é ['|| LENGTH(c)||']');
  DBMS_OUTPUT.PUT_LINE(' v é ['|| LENGTH(v)||']');
  v := v || ' ';
  DBMS_OUTPUT.PUT_LINE(' v é ['|| LENGTH(v)||']');
END;
/

O TIPO CHAR E VARCHAR

• O tipo CHAR é baseado em um tamanho fixo de strings. O padrão é o tamanho de 1 byte, podendo chegar até a 32.767;
• Em colunas CHAR, o máximo permitido são 4000 bytes;
• A Oracle recomenda utilizar CLOB ou LONG para colunas;
• O tipo VARCHAR2 tem base em tamanhos variados;
• VARCHAR2 pode chegar até 32.767 bytes de tamanho;

EXEMPLO DO TIPO CHAR

--tipo char
SET SERVEROUTPUT ON;
DECLARE
  var1 CHAR;
  var2 CHAR(10);
  var3 CHAR(10 BYTE);
  var4 CHAR(10 CHAR);
BEGIN
  var1 := 'a'; var2 := 'b'; var3 := 'c'; var4 := 'd';
  DBMS_OUTPUT.PUT_LINE(' var1 é ['|| LENGTH(var1)||']');
  DBMS_OUTPUT.PUT_LINE(' var2 é ['|| LENGTH(var2)||']');
  DBMS_OUTPUT.PUT_LINE(' var3 é ['|| LENGTH(var3)||']');
  DBMS_OUTPUT.PUT_LINE(' var4 é ['|| LENGTH(var4)||']');
END;
/

EXEMPLO DO TIPO VARCHAR2
--tipo varchar2
SET SERVEROUTPUT ON;
DECLARE
  var1 VARCHAR2; --erro
  var2 VARCHAR2(10);
  var3 VARCHAR2(10 BYTE);
  var4 VARCHAR2(10 CHAR);
BEGIN
  var1 := 'A'; var2 := 'AB'; var3 := 'ABC'; var4 := 'ABCD';
  DBMS_OUTPUT.PUT_LINE(' var1 é ['|| LENGTH(var1)||']');
  DBMS_OUTPUT.PUT_LINE(' var2 é ['|| LENGTH(var2)||']');
  DBMS_OUTPUT.PUT_LINE(' var3 é ['|| LENGTH(var3)||']');
  DBMS_OUTPUT.PUT_LINE(' var4 é ['|| LENGTH(var4)||']');
END;
/

OS TIPOS DATE, TIME, INTERVAL

EXEMPLO DO DATE

SET SERVEROUTPUT ON;
DECLARE
  var1 DATE;
  var2 DATE := SYSDATE;
  var3 DATE := SYSDATE +1;
  var4 DATE := '29-FEB-08';
BEGIN
  DBMS_OUTPUT.PUT_LINE(' var1 é ['|| var1 ||']');
  DBMS_OUTPUT.PUT_LINE(' var2 é ['|| var2 ||']');
  DBMS_OUTPUT.PUT_LINE(' var3 é ['|| var3 ||']');
  DBMS_OUTPUT.PUT_LINE(' var4 é ['|| var4 ||']');
END;
/

O TIPO TIMESTAMP

• É um subtipo extendido de DATE, que oferece mais precisão no tempo;
• SYSTIMESTAMP oferece o tempo mais preciso dependendo da plataforma;

EXEMPLO DO TIMESTAMP

SET SERVEROUTPUT ON;
DECLARE
  d DATE := SYSTIMESTAMP;
  t TIMESTAMP(3) := SYSTIMESTAMP;
BEGIN
  DBMS_OUTPUT.PUT_LINE('DATE      ['||d||']');
  DBMS_OUTPUT.PUT_LINE('TO_CHAR['||TO_CHAR(d,'DD-MON-YY HH24:MI:SS') ||']');
  DBMS_OUTPUT.PUT_LINE('TIMESTAMP ['||t||']');
END;
/

CARACTERES UNICODES

• Caracteres unicodes servem para suportar globalização;
• Globalização é a utilização de caracteres que suportam múltiplos conjuntos de caracteres;
• A codificação AL16UTF16 ou UTF8 são oferecidas pelo Oracle;
• AL16UTF16 utiliza 2 bytes por caracter;
• UTF8 utiliza 3 bytes por caracter;
• NCHAR é o tipo de dado UNICODE;
• Pode armazenar até o tamanho de 16.383 (32.767 / 2);
• NVARCHAR2 é o equivalente ao VARCHAR2;

Ref: https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm

O TIPO NUMBER

• Há 4 principais tipos de números no Oracle: BINARY_INTEGER, IEEE 754 (BINARY_DOUBLE e BINARY_FLOAT), O NUMBER e PLS_INTEGER;
• BINARY_INTEGER e PLS_INTEGER são idênticos, suportando a -2.147.483.648 a 2.147.483.647;

ATRIBUINDO VALORES À VARIÁVEIS DINAMICAMENTE

• No ambiente SQL Developer, podemos fazer a atribuição de variáveis através do símbolo “&” (e comercial sem aspas);

--EXEMPLO DE ATRIBUIÇÃO DE VARIÁVEIS
SET SERVEROUTPUT ON;
DECLARE
  var1  number;   var2  number;   soma  number;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Digite valor 1:');
  var1 := &Valor1;
  
  DBMS_OUTPUT.PUT_LINE('Digite valor 2:');
  var2 := &Valor2;
  
  DBMS_OUTPUT.PUT_LINE('Valor 1:' || var1);
  
  DBMS_OUTPUT.PUT_LINE('Valor 2:' || var2);
  
  soma := var1 + var2;
  
  DBMS_OUTPUT.PUT_LINE('Soma:' || soma);
END;
/

EXEMPLO DE DECLARAÇÃO DE TIPOS DE VARIÁVEIS
SET serveroutput ON;
DECLARE
  nIdFuncionario binary_integer;
  nSalario NUMBER(10, 2);  nAcrescimo FLOAT;
BEGIN
  nidfuncionario := 25;
  nSalario       := 1500;
  nAcrescimo     := 0.25;
  dbms_output.put_line('O novo salário do funcionário: ' || nSalario * (1 + nAcrescimo));
END;

--VARIAVEIS NUMBER, FLOAT, BINARY_INTEGER

SET SERVEROUTPUT ON;
DECLARE
  v_FUNCIONARIO_ID  BINARY_INTEGER;
  v_SALARIO NUMBER(10,2); v_ACRESCIMO  FLOAT;
BEGIN
  v_FUNCIONARIO_ID := 10; v_SALARIO := 2000;
  v_ACRESCIMO := 0.10;
 
DBMS_OUTPUT.PUT_LINE('O novo salario do funcionario: '
  || v_FUNCIONARIO_ID || ' terá acrescimo de '
  || v_SALARIO * v_ACRESCIMO);
END;

--VARIAVEIS BOOLEAN
SET SERVEROUTPUT ON;
DECLARE
  v_FUNCIONARIO_ID  BINARY_INTEGER;
  v_SALARIO         NUMBER(10,2);
  v_SAL_EM_REAL     BOOLEAN;
BEGIN
  v_FUNCIONARIO_ID := 10;
  v_SALARIO := 2000;
  v_SAL_EM_REAL := TRUE;
  IF v_SAL_EM_REAL THEN
    DBMS_OUTPUT.PUT_LINE('O salario do funcionario: '
      || v_FUNCIONARIO_ID || ' é de  '
      || v_SALARIO || ' em reais');
  ELSE
    DBMS_OUTPUT.PUT_LINE('O salario do funcionario: '
      || v_FUNCIONARIO_ID || ' é de  '
      || v_SALARIO || ' em dolares');
  END IF;
END;
/

CONSTANTES

• É similar a uma variável, porém valores não podem ser atribuídos durante o bloco de código;
• A palavra CONSTANT reserva é colocada depois do nome da variável e antes do tipo da variável;
• Pode ser definida por qualquer tipo de dados disponível para variáveis;
• Para utiliza-la, tenha certeza de que o valor não será alterado;

EXEMPLO DE CONSTANTES
--irá gerar erro
SET SERVEROUTPUT ON;
DECLARE
  v_SALARIO CONSTANT number(4);
BEGIN
  v_SALARIO := 1000;
  dbms_output.put_line (v_SALARIO);
END;
/

 
--irá funcionar
SET SERVEROUTPUT ON;
DECLARE
  v_SALARIO CONSTANT number(4) := 1000;
BEGIN
  dbms_output.put_line (v_SALARIO);
END;
/

VARIÁVEIS NOT NULL

• É possível declarar em PL/SQL variáveis NOT NULL;
• No decorrer do código, esta variável deverá ter algum valor, não podendo ser nula;
• É declarado na seção DECLARE, sendo que a variável precisa ter um valor associado;

EXEMPLO DE VARIÁVEIS NOT NULL

--erro
SET SERVEROUTPUT ON;
DECLARE
  v_FUNCIONARIO_ID BINARY_INTEGER NOT NULL := 10;
  v_SALARIO        NUMBER(10,2) := 1000;
BEGIN
  v_FUNCIONARIO_ID := NULL;
  DBMS_OUTPUT.PUT_LINE('Funcionario: ' || v_FUNCIONARIO_ID);
  v_SALARIO := NULL;
  DBMS_OUTPUT.PUT_LINE('Salario : ' || v_SALARIO);
END;
/ 

--correto
SET SERVEROUTPUT ON;
DECLARE
  v_FUNCIONARIO_ID BINARY_INTEGER NOT NULL := 10;
  v_SALARIO        NUMBER(10,2) := 1000;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Funcionario: ' || v_FUNCIONARIO_ID);
  v_SALARIO := NULL;
  DBMS_OUTPUT.PUT_LINE('Salario : ' || v_SALARIO);
END;
/

EXEMPLO DE IF..THEN.. ELSE

-- IF THEN ELSE
SET SERVEROUTPUT ON;
DECLARE
  v_SALARIO         NUMBER(10,2);
BEGIN
  v_SALARIO := &Salario;
  IF v_SALARIO BETWEEN 0 AND 1000 THEN
    DBMS_OUTPUT.PUT_LINE('Salario baixo:  ' || v_SALARIO);
  ELSIF v_SALARIO BETWEEN 1001 AND 2000 THEN
    DBMS_OUTPUT.PUT_LINE('Salario médio:  ' || v_SALARIO);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario alto:  ' || v_SALARIO);
  END IF;
END;
/

Exercícios

1. Crie uma bloco anônimo que leia três variáveis do tipo number e aponte mostre os números digitados e o maior número dentre os três;

2. Crie um bloco anônimo que leia 2 variáveis do tipo number, mostre os dois números digitados e mostre o resultado da soma, multiplicação, subtração e divisão desses dois números;

3. Crie um bloco anônimo que leia 2 variáveis do tipo number, e leia qual operação o usuário deseja fazer, escolhendo entre os símbolos (+, -, /, *). O símbolo escolhido deve ser apresentado o resultado dentre os dois números;

4. Crie um bloco anônimo que leia 3 frases de até 100 caracteres, mostre as frases digitadas, mostre quantos caracteres cada frase possui, e mostre a soma da quantidade de caracteres total das tres frases;

5. Crie um bloco anônimo que leia 2 datas, mostrando a quantidade de dias, meses e anos dentre essas datas. (Pode fazer arredondamento caso necessário). Observação, a Data Inicial precisa ser menor que a data final;

6. Crie um bloco nomeado que tenha 3 parametros de entrada do tipo number e que mostre o menor número dos 3 argumentos passados;

7. Crie um bloco nomeado que tenha 2 parametros de entrada do tipo nvarchar, e mostre quantos bytes cada parâmetro está ocupando;

8. Crie um bloco nomeado que tenha 2 parametros de entrada, uma do tipo number e outra do tipo date, e que mostre a data mais o parâmetro do tipo number e a data menos o parâmetro do tipo number;

PROCEDURES EM PL/SQL

• São tipos de sub-programas que executam uma ação;
• Pode ser armazenado em um banco como um objeto de schema;
• Provê reusabilidade e fácil manutenção;
• Pode aceitar parâmetros;
• A procedure é compilada e armazenada no banco de dados como um schema de objeto;
• Um bloco de comandos PL/SQL pode aceitar variáveis de substituição;

SET SERVEROUTPUT ON
DECLARE
  my_var VARCHAR2(30);
BEGIN
  my_var := '&input';
  dbms_output.put_line('Hello '|| my_var );
END;
/

• O operador de atribuição é “:=” (sem aspas);
• Strings são delimitadas por aspas simples;
• Declara-se variáveis na seção DECLARE;
• Os tipos de variáveis utilizadas são as mesmos tipos de variáveis utilizados nas tabelas do Oracle;

SET SERVEROUTPUT ON
DECLARE
  my_var VARCHAR2(10);
BEGIN
  my_var := '&input';
  dbms_output.put_line('Hello '|| my_var );
EXCEPTION
  WHEN others THEN
    dbms_output.put_line(SQLERRM);
END;
/

• Em PL/SQL, é possível fazer tratamentos de erros com a cláusula EXCEPTION;
• A função SQLERRM irá capturar o erro, e é possível mostrar os erros através do comando de impressão;
• SQLERRM retorna a mensagem de erro associado ao número do erro;
• Quando estamos codificando em linguagens de programação, é normal que tenhamos que seguir algumas regras de convenção de nomes;
• Elas servem para que se tenha padronização em na codificação dos programas;
• Não há uma regra clara sobre o uso de nomeações padrão. Caso não tenha, é importante adotar uma;

EXEMPLO DE CONVENSÃO DE NOMES EM PL/SQL

PARAMETROS E SEUS MODOS

• São declaradas depois dos subprogramas no header da procedure;
• Passa dados entre o ambiente de chamada e o subprograma;
• São usados como variáveis locais, mas são dependentes do modo em que são tratados
• IN é o modo padrão que passa os valores do ambiente de chamada para o subprograma;
• OUT é o modo de retorno do valor para o ambiente chamador;
• IN OUT é o modo que passa valores do ambiente de chamada, a qual pode retornar o valor modificado (o mesmo parâmetro);

PARAMETRO FORMAL

• Variáveis locais declaradas na lista de parâmetros da especificação do subprograma;

PARAMETRO ATUAL

• Ou também conhecidos como argumentos: Valores literais, variáveis e expressões são usadas na lista de parâmetros que chamam o subprograma (procedure)

MODOS DOS PARAMETROS

• O tipo dos parâmetros pode ser especificado sem o tamanho do seu tipo;
• Podem ser utilizados:
• O tipo explicito do dado;
• Usando a definição %TYPE;
• Usando a definição %ROWTYPE;
• Um ou mais parâmetros formais podem ser declarados, cada um separados por virgula;

COMPARAÇÃO DOS MODOS DOS PARAMETROS

• IN é o padrão se não há especificação na declaração;
• OUT e IN OUT precisam ser explicitamente especificados na declaração dos parâmetros;
• No parâmetro formal IN não pode ser atribuído um valor e não pode ser modificado no corpo da procedure. Por padrão, o parâmetro IN é passado por referencia
• Um parâmetro IN pode ser atribuído por um valor padrão na declaração do parâmetro formal, em caso do ambiente chamador não fornecer um valor para o parâmetro se o valor padrão é aplicado;
• OUT ou IN OUT precisam ser atribuídos um valor antes do retorno do ambiente chamador.
• OUT e IN OUT não podem ser atribuídos valores padrão;

COMPARAÇÃO DOS MODOS DOS PARAMETROS

Não pode ser atribuído um valor padrão

IN	OUT	IN OUT
Modo padrão	Precisa ser especificado	Precisa ser especificado
Valor é passado para o subprograma	Valor é retornado para o ambiente chamador	Valor é passado para o subprograma; valor é retornado para o ambiente chamador
Parâmetros formais e atuais como uma constante	Variáveis não inicializáveis	Variáveis inicializáveis
Parâmetro atual pode ser literal, expressão, constantes, ou variáveis inicializáveis	Precisa ser uma variável	Precisa ser uma variável
Pode ser atribuído um valor padrão	Não pode ser atribuído um valor padrão

EXEMPLO DE USO DO PARÂMETRO IN

--EXEMPLO DO PARAMETRO IN
CREATE OR REPLACE PROCEDURE previsao_salario (p_id   in employees.employee_id%TYPE, p_percent in NUMBER)
IS
BEGIN
  UPDATE  EMPLOYEES
  SET     SALARY = SALARY * (1 + p_percent /100)
  WHERE  EMPLOYEE_ID = p_id;
END previsao_salario;
/
 
--CONFERINDO O VALOR ANTERIOR
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 176;

--ESTA É A CHAMADA
EXECUTE previsao_salario(176,10);

--CONVERINDO O VALOR POSTERIOR
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 176;

--ABANDOMANDO ALTERAÇÕES
ROLLBACK;

EXEMPLO DE USO DO PARÂMETRO OUT 
--EXEMPLO DO PARAMETRO OUT
CREATE OR REPLACE PROCEDURE QUERY_EMP (p_id     IN  employees.employee_id%TYPE,
                                       p_name   OUT employees.last_name%TYPE,
                                       p_salary OUT employees.salary%TYPE ) IS
BEGIN
  SELECT  last_name, salary
  INTO    p_name,    p_salary
  FROM    employees
  WHERE   employee_id = p_id;
END QUERY_EMP;
/

--CHAMADA DA PROCEDURE EXEMPLO OUT

SET SERVEROUTPUT ON
DECLARE
  v_emp_name employees.last_name%TYPE;
  v_emp_sal  employees.salary%TYPE;
BEGIN
  QUERY_EMP(171,v_emp_name, v_emp_sal);
  DBMS_OUTPUT.PUT_LINE(v_emp_name || ' ganha ' ||
    to_char(v_emp_sal, '$999,999.00'));
END;
/

PARÂMETRO IN OUT

• O parametro IN OUT pode ser passado um valor para a procedure que pode ser atualizado, ou seja, o valor do mesmo parametro pode ser atualizado durante o processamento nas linhas da procedure;

EXEMPLO DE PARÂMETRO IN OUT

CREATE OR REPLACE PROCEDURE format_phone
(p_phone_no IN OUT VARCHAR2) IS
BEGIN
p_phone_no := '('   || SUBSTR(p_phone_no,1,3) ||
')  ' || SUBSTR(p_phone_no,4,3) ||
'-'   || SUBSTR(p_phone_no,7);
END format_phone;
/

–CHAMADA DA PROCEDURE IN OUT

VARIABLE b_phone_no VARCHAR2(15)
EXECUTE :b_phone_no := '8006330575'
PRINT b_phone_no
EXECUTE format_phone(:b_phone_no)
PRINT b_phone_no

PASSAGEM DE PARAMETROS

• Quando chamamos um subprograma, você pode escrever os parâmetros atuais usando as seguintes notações:
  • Posicional: lista dos parâmetros atuais na mesma ordem do que os parâmetros formais;
  • Nomeado: Lista dos parâmetros atuais em ordem arbitrária, usando o operador => para associar a nomeação do parâmetro formal com o parâmetro atual;
  • Misto: Lista de alguns parâmetros atuais como posicionais e alguns como nomeados;
• Antes do Oracle Database 11g, somente a notação posicional era suportada nas chamadas das SQL´s;
• A partir da versa 11g, notações nomeadas e mistos podem ser usadas para especificar argumentos na chamada para sub-rotinas de declarações do PL/SQL;

–PASSAGEM DE PARAMETRO ATUAIS

CREATE OR REPLACE PROCEDURE add_dept(p_name in  departments.department_name%TYPE,
                                     p_loc  in  departments.location_id%TYPE) IS
BEGIN
  INSERT INTO departments(department_id,
                          department_name,
                          location_id)
  VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;


--PASSAGEM DE NOTAÇÃO POSICIONAL
EXECUTE add_dept('TRAINING',2500);

--PASSAGEM DE NOTAÇÃO NOMEADA
EXECUTE add_dept(p_loc=>2400, p_name=>'EDUCATION');

USANDO O PARAMETRO OPCIONAL DEFAULT

• Define um valor padrão para o parâmetro;
• Provê flexibilidade por combinação posicional e nomeada na sintaxe da passagem de parâmetros;

ALTERANDO A PROCEDURE add_dept

–PARAMETRO DEFAULT

CREATE OR REPLACE PROCEDURE add_dept
(p_name departments.department_name%TYPE :='Desconhecido',
p_loc  departments.location_id%TYPE DEFAULT 1700 )
IS
BEGIN
INSERT INTO departments  (department_id,
department_name,
location_id)
VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;
/

CHAMADA DA PROCEDURE add_dept

--chamando a procedure
EXECUTE add_dept;

EXECUTE add_dept('PROPAGANDA',p_loc=>1200);

EXECUTE add_dept(p_loc=>1200);

--conferindo os dados inseridos
select * from departments;

• Você pode chamar procedures usando blocos anônimos;
• Voce precisa ser dono da procedure ou ter privilégio de EXECUTE;

Exercícios

1. Crie uma bloco nomeado (procedure), que passado 2 parâmetros do tipo number, (o valor do primeiro parâmetro deve ser menor que o segundo) retorne todos os funcionários código, nome concatenado com o sobrenome, salário e departamento que o salário esteja entre esses dois parametros
2. Crie um bloco nomeado (procedure), que passado um parâmetro do tipo data, retorne o código, sobrenome, nome, data de admissão e departamento de funcionários, onde a data de admissão seja maior ou igual ao parâmetro passado. O parâmetro deve ser uma data válida;
3. Crie um bloco nomeado (procedure), que passado um parâmetro do tipo string, retorne o código, nome concatenado com o sobrenome dos funcionários, onde a letra do parâmetro deve ser substituída pela string “STAR”; Exemplo: o usuário passa a letra A, todos as letras do nome e sobrenome QUE CONTENHAM A deve ser substituída por STAR.
4. Crie um bloco nomeado (procedure), que passado um parâmetro do string, retorne o código e nome concatenado com o sobrenome dos funcionários, onde o nome deve ser iniciado com o parâmetro digitado. Deve-se mostrar quantos caracteres tem o nome e separar cada sílaba com o caracter “-“;
5. Crie um bloco nomeado que não tenha passagem de parâmetros, que faça a inclusão automática de um departamento cada vez que for acionada; Observação: o nome do departamento pode ser concatenado com outro dado para não se repetir (mesmo que o nome não tenha tanto sentido);
6. Crie um bloco nomeado que passe um parâmetro do tipo inteiro e outro do tipo string, e que esses parâmetros tenham os valores padrão 100 e ‘IP_PROG’, retornando os registros que tenham o employee_id e job_id iguais aos dos parâmetros passados. Caso o usuário passe valor aos parâmetros, é necessário respeitar o valor passado para se ter maior prioridade. Caso não sejam passados, os parâmetros padrões serão utilizados. Arrume a procedure para que pelo menos se tenha um registro retornado caso não seja passado nenhum parâmetro;

 

Referência:
ORACLE DATABASE 11g PL/SQL Programming
Develop Robust, Database-Driven
PL/SQL Application
Michael McLaughlin – Mc Graw Hill

Vídeo 1

Vídeo 2