Oracle Procedures (Parte 06)

Procedures

Com procedures podemos definir linhas de comandos que serão chamadas sempre que executarmos a procedure não só no ambiente Oracle, mas por programas externos, economizando em código, a sintaxe básica de uma procedure é:

CREATE OR REPLACE PROCEDURE nome_procedure
  (
  nome_parametro1 IN TIPO,
  nome_parametro2 IN TIPO,
  )
IS (ou AS)
  [declara variáveis]
BEGIN
  --comandos sql plsql
EXCEPTION
  --tratamento
END;

INSERT USANDO PROCEDURE

Vamos para um exemplo pratico de criação de procedure, nela vamos criar um rotina de inserção de empregados:

CREATE OR REPLACE PROCEDURE PRC_INSERE_EMPREGADO
  (pfirst_name    IN VARCHAR2,
   plast_name     IN VARCHAR2,
   pemail         IN VARCHAR2,
   pphone_number  IN VARCHAR2,
   phire_date     IN DATE DEFAULT SYSDATE,
   pjob_id        IN VARCHAR2,
   pSALARY        IN NUMBER,
   pCOMMICION_PCT IN NUMBER,
   pMANAGER_ID    IN NUMBER,
   pDEPARTMENT_ID IN NUMBER)
IS 
  -- Nenhuma variável declarada
BEGIN
  INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    commission_pct,
    manager_id,
    department_id )
  VALUES (
    employees_seq.nextval,
    pfirst_name,
    plast_name,
    pemail,
    pphone_number,
    phire_date,
    pjob_id,
    psalary,
    pcommicion_pct,
    pmanager_id,
    pdepartment_id );
EXCEPTION
  WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20001, 'Erro Oracle ' || SQLCODE || SQLERRM);
END;

EDITANDO UMA PROCEDURE

Um vez criada a procedure, ela ficará gravada no banco de dados e para editar futuramente uma procedure, podemos procura-la pelo nome e com o botão direito do mouse -> editar, (figura 01).

Vejamos como chamar a procedure depois de criada:

BEGIN
  PRC_INSERE_EMPREGADO('David', 'Bowie','DBOWIE','515.127.4861',SYSDATE,'IT_PROG',15000,NULL,103,60);
  COMMIT;
END;

chamando pelo execute:

EXEC PRC_INSERE_EMPREGADO('Greg', 'Lake','GLAKE','515.127.4961',SYSDATE,'IT_PROG',15000,NULL,103,60)
COMMIT;

Get Daniel Amorim’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

TIPOS DE PARÂMETROS

• IN: DEFINE UM PARAMETRO DE ENTRADA
• OUT: PARAMETRO DE SAIDA
• OUT IN: PARAMETRO DE SAIDA

No exemplo anterior usamos um exemplo com passagem de parâmetros IN, Vejamos como usar parâmetros de saída OUT.

SELECT USANDO PROCEDURE

CREATE OR REPLACE PROCEDURE PRC_CONSULTA_EMPREGADO
  (pemployee_id   IN NUMBER,
   pfirst_name    OUT VARCHAR2,
   plast_name     OUT VARCHAR2,
   pemail         OUT VARCHAR2,
   pphone_number  OUT VARCHAR2,
   phire_date     OUT DATE,
   pjob_id        OUT VARCHAR2,
   pSALARY        OUT NUMBER,
   pCOMMISSION_PCT OUT NUMBER,
   pMANAGER_ID    OUT NUMBER,
   pDEPARTMENT_ID OUT NUMBER)
IS 
  -- Nenhuma variavel declarada
BEGIN
  SELECT
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    commission_pct,
    manager_id,
    department_id
  INTO 
    pfirst_name,
    plast_name,
    pemail,
    pphone_number,
    phire_date,
    pjob_id,
    psalary,
    pcommission_pct,
    pmanager_id,
    pdepartment_id
  FROM employees
  WHERE employee_id = pemployee_id;
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
     RAISE_APPLICATION_ERROR(-20001, 'Empregado Não existe: ' || pemployee_id);
  WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20002, 'Erro Oracle ' || SQLCODE || SQLERRM);
END;

Agora vamos chamar esta procedure, e ver os dados retornados:

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE 
  employees_record  employees%ROWTYPE;
BEGIN
  PRC_CONSULTA_EMPREGADO(100, employees_record.first_name, employees_record.last_name, employees_record.email,
    employees_record.phone_number, employees_record.hire_date, employees_record.job_id, employees_record.salary, 
    employees_record.commission_pct, employees_record.manager_id, employees_record.department_id);
    DBMS_OUTPUT.PUT_LINE(employees_record.first_name || ' ' || 
                         employees_record.last_name || ' - ' ||
                         employees_record.department_id || ' - ' ||
                         employees_record.job_id || ' - ' ||
                         employees_record.phone_number || ' - ' ||
                         LTRIM(TO_CHAR(employees_record.salary, 'L99G999G999D99')));
END;

DICA:

Procedures podem por algum motivo aparecer com status INVALID, mesmo com o código fonte ok, então para corrigir isso podemos compilar novamente a procedure com o comando:

ALTER PROCEDURE nome_procedure COMPILE;

REMOVENDO PROCEDURES DE UM BANCO DE DADOS

Atenção ao remover, pois ela será excluída permanentemente:

DROP PROCEDURE nome_procedure;

ou também pelo comando do mouse, procura a procedure botão direito e eliminar: