(c) 2002 Visual Studio Magazine 
Fawcette Technical Publications

PL/SQL	Optimize Code With Stored Procedures
Listing 1	This code shows the stored procedures this article's samples use. Using stored procedures lets you save the database the trouble of parsing the SQL statement, compiling it, and deciding on the best query plan. These steps occur once per stored procedure.

CREATE OR REPLACE PACKAGE PAYROLLMAINT AS

	PROCEDURE ADDEMP(
		EMPNO		IN	NUMBER,
		ENAME		IN	VARCHAR2,
		JOB		IN	VARCHAR2,
		MGR		IN	NUMBER,
		HIREDATE	IN	DATE,
		SAL		IN	NUMBER,
		COMM		IN	NUMBER,
		DEPTNO	IN	NUMBER);

	PROCEDURE ADDDEPT(
		 DEPTNO	IN	NUMBER,
		 DNAME	IN	VARCHAR2,
		 LOC		IN	VARCHAR2);

END PAYROLLMAINT; 
/

CREATE OR REPLACE PACKAGE BODY PAYROLLMAINT AS 

	PROCEDURE ADDEMP(
		EMPNO		IN	NUMBER,
		ENAME		IN	VARCHAR2,
		JOB		IN	VARCHAR2,
		MGR		IN	NUMBER,
		HIREDATE	IN	DATE,
		SAL		IN	NUMBER,
		COMM		IN	NUMBER,
		DEPTNO	IN	NUMBER) 
	IS 

	BEGIN	
		INSERT INTO EMP VALUES(
			EMPNO, ENAME, JOB, MGR, 
			HIREDATE, SAL, COMM, DEPTNO);
	END ADDEMP;

	PROCEDURE ADDDEPT(
		DEPTNO	IN	NUMBER,
		DNAME		IN	VARCHAR2,
		LOC		IN	VARCHAR2) 
	IS	

	BEGIN	
		INSERT INTO DEPT VALUES(
			DEPTNO, DNAME, LOC);	 
	END ADDDEPT;

END PAYROLLMAINT; 
/

C#	Set Up Parameters Normally
Listing 2	When you use multiple parameter sets with ODP.NET, you don't make any changes to the way you set up parameters normally. You could use this code with a single parameter set (as with the insert_dept procedure) or multiple parameter sets, as with the insert_emp procedure.

OracleConnection conn = new OracleConnection(
	"data source=mysrv;" + 
	"user id=scott;password=tiger");
OracleCommand insert_dept_cmd = 
	new OracleCommand(
	 "payrollmaint.insert_dept", conn);

// ordinary procedure, 
// 1 set of parameters
insert_dept_cmd.Parameters.Add(
	"DEPTNO", OracleDbType.Decimal);
insert_dept_cmd.Parameters.Add(
	"DNAME", OracleDbType.Varchar2);
insert_dept_cmd.Parameters.Add(
	"LOC", OracleDbType.Varchar2);

// now the employees
OracleCommand insert_emp_cmd = 
 new OracleCommand(
	"payrollmaint.insert_emp", conn);

// add parameters to collection 
// as normal
insert_emp_cmd.Parameters.Add(
	"EMPNO", OracleDbType.Decimal);
insert_emp_cmd.Parameters.Add(
	"ENAME", OracleDbType.Varchar2);
// other parameters 
// deleted for compactness

