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.