Oracle:Database Procedures

Main Page >> Oracle and SQL >> Workbook >> Advanced Features >> Database Procedures

An Introduction to Database Procedures

Whereas Triggers are fired based on an event occurring, Procedures are programs that must be explicitly called. These too can be stored in the database and made available to application developers. The following PL/SQL script takes a number of parameters and inserts a row into the EMP table.

CREATE PROCEDURE create_emp(
  nm_empno	NUMBER,
  vc_ename	VARCHAR2,
  vc_job	VARCHAR2,
  nm_mgr	NUMBER,
  dt_hiredate	DATE,
  nm_sal	NUMBER,
  nm_comm	NUMBER,
  nm_deptno	NUMBER)
AS
BEGIN
 INSERT INTO EMP
 VALUES (nm_empno,vc_ename,vc_job,nm_mgr, dt_hiredate,nm_sal,nm_comm,nm_deptno
);
END create_emp;
/

To execute this procedure you can type (note it must be typed as one line):

execute create_emp(1234,'SMITH','MANAGER',NULL,SYSDATE,2000,NULL,10);

The parameters in procedures may also be given default values.

> Check the EMP table to see that the new row has been added.

---

Return to the Workbook.