Creating a Procedure

A procedure contains a group of SQL and PL/SQL statements. You create a procedure using the CREATE PROCEDURE statement. The simplified syntax for the CREATE PROCEDURE statement: 


CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
    procedure_body
END procedure_name;

IN | OUT | IN OUT is the mode of the parameter.

  • IN parameter, which is the default mode and cannot be changed in the procedure body, must be set to a value when the procedure is run.
  • OUT parameter means the parameter is set to a value in the procedure body.
  • IN OUT combines the mode of IN and OUT.

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);

SQL> CREATE PROCEDURE update_salary(p_id IN emp.empno%TYPE,p_factor IN NUMBER) AS
  2     v_count INTEGER;
  3  BEGIN
  4     SELECT COUNT(*) INTO v_count FROM emp WHERE empno = p_id;
  5
  6     IF v_count = 1 THEN
  7        UPDATE emp SET sal = sal * p_factor WHERE empno = p_id;
  8        COMMIT;
  9     END IF;
 10  EXCEPTION
 11  WHEN OTHERS THEN
 12   ROLLBACK;
 13  END update_salary;
 14  /

Procedure created.

SQL>

Calling a Procedure

You run a procedure using the CALL statement. 


SQL> SELECT sal FROM emp WHERE empno = 1;

       SAL
----------
       800

SQL>
SQL> CALL update_salary(1, 1.5);

Call completed.

SQL>
SQL>
SQL> SELECT sal FROM emp WHERE empno = 1;

       SAL
----------
      1200

SQL>
Home »
Oracle »
PL/SQL » 

Procedures:
  1. Creating a Procedure
  2. Pass parameters using named and mixed notation.
  3. Information on Procedures
  4. Dropping a Procedure
  5. Viewing Errors in a Procedure
Related: