Oracle Stored Procedures examples and notes
I used SP for a long time; then, stopped using it; then, used it again, then, stopped, then, again ... you got the idea. It seems that it will be a while that we can say we will never use it for ordinary development, and it will certainly be here "forever" for special situations. So, here are the minimalist examples and notes.
My stand of using SP? Do not use it if you can use OR mapping, or, if it is a new project (even you do not use OR mapping -- and in that case, non-parameterized sqls are also fine -- as long as you parse the sql to prevent sql injection -- you need to parse it for the single quote anyway. Remember, developer productivity is number one, everything else should be treated as explicit feature request); however, it does not hurt that much, just a little bit waste.
The key to prevent SP from being harmful is to keep it super simple. It should be purely a pure thin wrapper of CRUD sqls.
The following has more info than necessary; however, it is clear that the following shows the minimalist CRUD approach.
-------------------------------CRUD examples
-------------------------------
-------------------------------
http://www.devshed.com/c/a/Oracle/Developing-Simple-PL-SQL-Stored-Procedures-for-CRUD-Operations/1/
http://www.c-sharpcorner.com/UploadFile/john_charles/CallingOraclestoredproceduresfromMicrosoftdotNET06222007142805PM/CallingOraclestoredproceduresfromMicrosoftdotNET.aspx
---------------insert
create or replace procedure p_emp_insert (p_empno emp.empno%type,
p_ename emp.ename%type, p_sal emp.sal%type, p_deptno emp.deptno%
type) as
begin
insert into emp
(
empno,
ename,
sal,
deptno
)
values
(
p_empno,
p_ename,
p_sal,
p_deptno
);
Commit;
exception
when dup_val_on_index then
raise_application_error(-20001, 'Employee already
exists');
when others then
raise_application_error(-20011, sqlerrm);
end;
/
---------------we could add validation in the above SP, but do we really want to do that? My vote is no. We do not want that! validation should be the responsibility of facade and entity.
create or replace procedure p_emp_insert (p_empno emp.empno%type,
p_ename emp.ename%type, p_sal emp.sal%type, p_deptno emp.deptno%
type) as
Invalid_sal exception;
Invalid_deptno exception;
begin
if p_sal<100>10000 then
raise invalid_sal;
end if;
declare
dummy_var varchar(10);
begin
select 'exists' into dummy_var
from dept
where deptno = p_deptno;
exception
when no_data_found then
raise Invalid_deptno;
when others then
raise_application_error(-20011, sqlerrm);
end;
insert into emp
(
empno,
ename,
sal,
deptno
)
values
(
p_empno,
p_ename,
p_sal,
p_deptno
);
Commit;
exception
when invalid_sal then
raise_application_error(-20001, 'Salary must be in
between 100 and 10000');
when invalid_deptno then
raise_application_error(-20001, 'Department doesn't
exist');
when dup_val_on_index then
raise_application_error(-20001, 'Employee already
exists');
when others then
raise_application_error(-20011, sqlerrm);
end;
/
---------------update
create or replace procedure p_emp_update (p_empno emp.empno%type,
p_ename emp.ename%type, p_sal emp.sal%type, p_deptno emp.deptno%
type) as
Invalid_sal exception;
Invalid_deptno exception;
Invalid_empno exception;
begin
if p_sal<100>10000 then
raise invalid_sal;
end if;
declare
dummy_var varchar(10);
begin
select 'exists' into dummy_var
from dept
where deptno = p_deptno;
exception
when no_data_found then
raise Invalid_deptno;
when others then
raise_application_error(-20011, sqlerrm);
end;
update emp set
ename=p_ename,
sal=p_sal,
deptno=p_deptno
where empno=p_empno;
if sql%notfound or sql%rowcount=0 then
rollback;
raise Invalid_empno;
end if;
commit;
exception
when invalid_sal then
raise_application_error(-20001, 'Salary must be in
between 100 and 10000');
when invalid_deptno then
raise_application_error(-20001, 'Department doesn't
exist');
when invalid_empno then
raise_application_error(-20001, 'Employee does not
exist');
when others then
raise_application_error(-20011, sqlerrm);
end;
/
---------------delete
create or replace procedure p_emp_delete (p_empno emp.empno%type) as
Invalid_empno exception;
begin
delete from emp
where empno=p_empno;
if sql%notfound or sql%rowcount=0 then
rollback;
raise Invalid_empno;
end if;
commit;
exception
when invalid_empno then
raise_application_error(-20001, 'Employee does not
exist');
when others then
raise_application_error(-20011, sqlerrm);
end;
/
---------------select without ref cursors
create or replace procedure p_emp_details(p_empno emp.empno%type,
p_ename OUT emp.ename%type, p_sal OUT emp.sal%type, p_deptno OUT
emp.deptno%type)
as
begin
select ename, sal, deptno
into p_ename, p_sal, p_deptno
from emp
where empno = p_empno;
exception
when no_data_found then
raise_application_error(-20001, 'Employee not
found');
when too_many_rows then
/* this would not happen generally */
raise_application_error(-20002, 'More employees exist
with the same number');
when others then
raise_application_error(-20003, SQLERRM);
end;
/
----------driver of the above sp:
declare
s emp.sal%type;
en emp.ename%type;
d emp.deptno%type;
begin
p_emp_details(7369, en,s,d);
dbms_output.put_line('name of employee: ' || en);
dbms_output.put_line('Salary: ' || s);
dbms_output.put_line('Deptno: '||d);
end;
/
---------------example of select without ref cursors
create or replace procedure count_emp_by_dept(pin_deptno number, pout_count out number)
is
begin
select count(*) into pout_count
from scott.emp
where deptno=pin_deptno;
end count_emp_by_dept;
------
Using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace CallingOracleStoredProc
{
class Program
{
static void Main(string[] args)
{
using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
{
OracleCommand objCmd = new OracleCommand();
objCmd.Connection = objConn;
objCmd.CommandText = "count_emp_by_dept";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20;
objCmd.Parameters.Add("pout_count", OracleType.Number).Direction = ParameterDirection.Output;
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
System.Console.WriteLine("Number of employees in department 20 is {0}", objCmd.Parameters["pout_count"].Value);
}
catch (Exception ex)
{
System.Console.WriteLine("Exception: {0}",ex.ToString());
}
objConn.Close();
}
}
}
}
--------------another example select without ref cursors, with a function
create or replace function get_count_emp_by_dept(pin_deptno number)
return number
is
var_count number;
begin
select count(*) into var_count
from scott.emp
where deptno=pin_deptno;
return var_count;
end get_count_emp_by_dept;
---------note it uses "return_value" as the parameter name
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace CallingOracleStoredProc
{
class Program
{
static void Main(string[] args)
{
using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
{
OracleCommand objCmd = new OracleCommand();
objCmd.Connection = objConn;
objCmd.CommandText = "get_count_emp_by_dept";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20;
objCmd.Parameters.Add("return_value", OracleType.Number).Direction = ParameterDirection.ReturnValue;
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
System.Console.WriteLine("Number of employees in department 20 is {0}", objCmd.Parameters["return_value"].Value);
}
catch (Exception ex)
{
System.Console.WriteLine("Exception: {0}",ex.ToString());
}
objConn.Close();
}
}
}
}
---------------use select with ref cursors
create or replace package human_resources
as
type t_cursor is ref cursor;
procedure get_employee(cur_employees out t_cursor);
end human_resources;
-------
create or replace package body human_resources
as
procedure get_employee(cur_employees out t_cursor)
is
begin
open cur_employees for select * from emp;
end get_employee;
end human_resources;
------
Using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace CallingOracleStoredProc
{
class Program
{
private static void prvPrintReader(OracleDataReader objReader)
{
for (int i = 0; i < i =" 0;" objconn =" new" source="ORCL;" id="scott;" password="tiger" objcmd =" new" connection =" objConn;" commandtext = "human_resources.get_employee" commandtype =" CommandType.StoredProcedure;" direction =" ParameterDirection.Output;" objreader =" objCmd.ExecuteReader();" i =" 0;" i =" 0;" objconn =" new" source="ORCL;" id="scott;" password="tiger" objcmd =" new" connection =" objConn;" commandtext = "human_resources.get_employee_department" commandtype =" CommandType.StoredProcedure;" direction =" ParameterDirection.Output;" direction =" ParameterDirection.Output;" objreader =" objCmd.ExecuteReader();" ename="p_ename," job="p_job," mgr="p_mgr," hiredate="p_hiredate," sal="p_sal," comm="p_comm," deptno="p_deptno" empno="p_empno;" empno="p_empno;" objconn =" new" source="ORCL;" id="scott;" password="tiger" objadapter =" new" objselectcmd =" new" connection =" objConn;" commandtext = "human_resources.select_employee" commandtype =" CommandType.StoredProcedure;" direction =" ParameterDirection.Output;" selectcommand =" objSelectCmd;" objinsertcmd =" new" connection =" objConn;" commandtext = "human_resources.insert_employee" commandtype =" CommandType.StoredProcedure;" insertcommand =" objInsertCmd;" objupdatecmd =" new" connection =" objConn;" commandtext = "human_resources.update_employee" commandtype =" CommandType.StoredProcedure;" updatecommand =" objUpdateCmd;" objdeletecmd =" new" connection =" objConn;" commandtext = "human_resources.delete_employee" commandtype =" CommandType.StoredProcedure;" deletecommand =" objDeleteCmd;" dtemp =" new" count =" {0}" deptno =" p_deptno" p_deptno =""> 30,
p_recordset => v_cursor);
LOOP
FETCH v_cursor
INTO v_ename, v_empno, v_deptno;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_ename || ' | ' || v_empno || ' | ' || v_deptno);
END LOOP;
CLOSE v_cursor;
END;
/
-------vb.net
Dim conn, cmd, rs
Set conn = Server.CreateObject("adodb.connection")
conn.Open "DSN=TSH1;UID=scott;PWD=tiger"
Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "GetEmpRS"
cmd.CommandType = 4 'adCmdStoredProc
Dim param1
Set param1 = cmd.CreateParameter ("deptno", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = 30
Set rs = cmd.Execute
Do Until rs.BOF Or rs.EOF
-- Do something
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = nothing
Set param1 = nothing
Set cmd = nothing
Set conn = nothing
---------------strong typed ref cursor
create or replace package REFCURSOR_PKG as
TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;
-------------another example with VB.net code
CREATE OR REPLACE PACKAGE curspkg_join AS
TYPE t_cursor IS REF CURSOR ;
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor);
END curspkg_join;
/
CREATE OR REPLACE PACKAGE BODY curspkg_join AS
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)
IS
v_cursor t_cursor;
BEGIN
IF n_EMPNO <> 0
THEN
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = n_EMPNO;
ELSE
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
END IF;
io_cursor := v_cursor;
END open_join_cursor1;
END curspkg_join;
-------------
Imports System.Data.OracleClient
Dim Oraclecon As New OracleConnection("Password=pwd;" & _
"User ID=uid;Data Source=MyOracle;")
Oraclecon.Open()
Dim myCMD As New OracleCommand()
myCMD.Connection = Oraclecon
myCMD.CommandText = "curspkg_join.open_join_cursor1"
myCMD.CommandType = CommandType.StoredProcedure
myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output
myCMD.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 123
Dim myReader As OracleDataReader
Try
myCMD.ExecuteNonQuery()
Catch myex As Exception
MsgBox(myex.Message)
End Try
myReader = myCMD.Parameters("io_cursor").Value
Dim x, count As Integer
count = 0
Do While myReader.Read()
For x = 0 To myReader.FieldCount - 1
Console.Write(myReader(x) & " ")
Next
Console.WriteLine()
count += 1
Loop
MsgBox(count & " Rows Returned.")
myReader.Close()
Oraclecon.Close()
/
-----------rowtype
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
er emp%rowtype;
begin
open c_emp for select * from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.ename || ' - ' || er.sal);
end loop;
close c_emp;
end;
---------- "record"
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
begin
open c_emp for select ename,sal from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
end;
------------ref cursor is dynamic
------------(this is crucial for practical use in SP)
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
begin
open c_emp for select ename,sal from emp where deptno = 10;
dbms_output.put_line('Department: 10');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
open c_emp for select ename,sal from emp where deptno = 20;
dbms_output.put_line('Department: 20');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
end;
--------------ref cursor within a loop
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
begin
for i in (select deptno,dname from dept)
loop
open c_emp for select ename,sal from emp where deptno = i.deptno;
dbms_output.put_line(i.dname);
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
end loop;
end;
-----------------nested proc (it is like class's static method. it is just like package)
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
procedure PrintEmployeeDetails is
begin
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
end;
begin
for i in (select deptno,dname from dept)
loop
open c_emp for select ename,sal from emp where deptno = i.deptno;
dbms_output.put_line(i.dname);
dbms_output.put_line('--------------');
PrintEmployeeDetails;
close c_emp;
end loop;
end;
------------------by passing parameters, we can move the print proc outside
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
procedure PrintEmployeeDetails(p_emp r_cursor) is
er rec_emp;
begin
loop
fetch p_emp into er;
exit when p_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
end;
begin
for i in (select deptno,dname from dept)
loop
open c_emp for select ename,sal from emp where deptno = i.deptno;
dbms_output.put_line(i.dname);
dbms_output.put_line('--------------');
PrintEmployeeDetails(c_emp);
close c_emp;
end loop;
end;
-------------------------------notes
-------------------------------
-------------------------------
-------------------------------
A. Environment
1. create or replace will keep all grants, so use it, instead of drop then create again
2. execute PPP.XXXX('para1'); or BEGIN addtuple1(99); END; . then \
3. errors is in user_errors
(in sqlplus: show errors procedure
;
4. source is in all_source (or dba_source), user_objects
5. sometimes need to recompile: alter procedure/package XXXX compile
6. sqlplus: "." (period) means the end
7. sqlplus: "/" means "run;"
B. Package, Procedures
1. always use package to organize procedures, as namespace.
2. also, package has an extra feature: it is like static class, its static initializer is at the bottom of the body: immediately before the end, you add a begin block for package initializer.
3. note that "package body" is the meat; package itself is just a useless trace of C++ heritage.
4. in package body, you put pl/sql block. A block always has: declare (optional), begin, exception (optional), end.
5. in exception section: you use "when ZERO_DIVIDE, then", you can use "when others". To raise exception, you can use "RAISE" (typically used outside exception section, to handle it yourself in the SP's exception section later), or (to let it bubble up by the system), RAISE_APPLICATION_ERROR (from -20001 to -20999)
6. in command section: you can use if-then-elsif-then-else, and loop (exit when, or, while XXX, or, for-XXX-in).
7. The most interesting thing is its cursor related stuff: <======================= (a) a cursor is just like a "reader" in ADO. For example, we can declare: "cursor rad_cursor is select * from RADIUS_VALS". <======================================== (b) note that rad_cursor is an instance (not a type); however, we can have a type based on the instance: ----(i) rad_val rad_cursor%ROWTYPE; ----(ii) radius rad_val.Radius%TYPE; (c) to loop through a cursor, we can use simplified syntax, so that we do not need to write open, fetch-XX-into-YY, and close, or test rad_cursor%NOTFOUND (or rad_cursor%ROWCOUNT), we simply say: "for rad_val in rad_cursor". (d) in the loop, we can have some pl/sql's special sqls. For example: ----SELECT e,f INTO a,b FROM T1 WHERE e>1;
----DELETE FROM T1 WHERE CURRENT OF T1Cursor;
(e) note: (b)(c)(d) are not really necessary if you do things right. All we really need is to wrap basic CRUD sqls with SPs, with absolutely no processing (i.e. logic) at all (the so-called "data logic" is really just performance optimization -- as a result, in normal development, we do not even think about it -- we deal with it with systematic measurement etc. In general, in almost all projects, if you do it right, you do not need to do any "data logic" optimizations).
C. Other things like Functions, Triggers, etc.
----. comment /* */
----. usually we use procedures (use out parameter for return values), not functions. However, function can return a value. Function can be used in sql (it should well-behaved, i.e., no out parameter, it should not change things).
----. trigger naming convention: BU_ROW_XXXXXX, "B" means before, "U" means update, "ROW" means row-level, XXXX for table name. We can combine I and U etc, in the body, we can use "if inserting than" to differentiate them.
----. trigger can be triggered by a column; further, we use "when" to add extra conditions to be triggered.
----. in trigger, we can use :new.YYY, :old.XXX to get the column values.
