Oracle 存储过程的使用
Oralce存储过程的使用,主要包括二个部门,第一部分为编写响应的存储过程;第二部分为调用存储过程
1. 编写存储过程
oracle存储过程的编写一般会采用程序包(即为包头和包体)
包头的作用主要是声明一些存储过程、函数等;
包体是对包头所声明的存储过程进行实现
测试
实例如下:
--编写包头
create or replace package package3 is
TYPE type_cur IS REF CURSOR; --声明游标
PROCEDURE getSystemCatByUserCode (usercode varchar2, p_rc OUT type_cur);--声明存储过程
end package3;
--编写包体
create or replace package body package3 is
PROCEDURE getSystemCatByUserCode (usercode varchar2, p_rc OUT type_cur)
IS
sqlstr VARCHAR2 (1000);
v_sql varchar2(100);
v_num int;
BEGIN
v_sql:='select count(*) from sys_system_customer where usercode='''||usercode||'''';
EXECUTE IMMEDIATE v_sql INTO v_num;
IF v_num = 0 THEN
OPEN p_rc FOR
select * from Sys_System_Type;
ELSE
sqlstr :=
'with tmp as(
select ty.typename as TYPENAME,ty.id as TYPEID, ct.usercode as USERCODE ,ct.SORTID as SORTID,
st.sysname as SYSNAME,st.sysid as SYSID from sys_system_customer ct
left join sys_system st on st.sysid=ct.systemid
left join sys_system_type ty on ty.id=ct.catid where ct.usercode='''||usercode||''')
select t.TYPEID as ID,t.TYPENAME, listagg(t.SYSID,'','') within group(order by SORTID) as SUBSYSTEMIDS from tmp t group by t.TYPEID,t.TYPENAME';
OPEN p_rc FOR sqlstr;
END IF;
END getSystemCatByUserCode;
END package3;
测试
2. 存储过程的调用
public System.Data.DataTable GetSystemCatByUserCode(string userCode)
{
OracleParameter[] parameters = {
new OracleParameter("usercode", OracleType.VarChar, 255),
new OracleParameter("p_rc", OracleType.Cursor)
};
parameters[0].Value = userCode;
parameters[1].Direction = ParameterDirection.Output;
return DbHelperOra.RunProcedure("package3.getSystemCatByUserCode", parameters, "Sys_System_Type").Tables[0];
}