Difference between revisions of "Oracle:Database Procedures"
From mi-linux
Jump to navigationJump to search (Created page with " Main Page >> Oracle and SQL >> Workbook >> Advanced Features >> Database Procedures == An Introduction to Dat...") |
|||
| (One intermediate revision by the same user not shown) | |||
| Line 6: | Line 6: | ||
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. | 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 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 | AS | ||
BEGIN | BEGIN | ||
| − | + | INSERT INTO EMP | |
| − | + | VALUES (nm_empno,vc_ename,vc_job,nm_mgr, dt_hiredate,nm_sal,nm_comm,nm_deptno | |
); | ); | ||
END create_emp; | END create_emp; | ||
| Line 30: | Line 30: | ||
> Check the EMP table to see that the new row has been added. | > Check the EMP table to see that the new row has been added. | ||
| + | |||
---- | ---- | ||
Return to the [[Oracle_Workbook|Workbook]]. | Return to the [[Oracle_Workbook|Workbook]]. | ||
Latest revision as of 16:54, 23 June 2016
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.
