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

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