oracle中写存储过程1

把存储过程放在Package中时,每条语句结束后需要加分号";".如下:

包头:(定义完存储过程后,在存储过程后加分号";")

create or replace package SysDepartPack AS

       TYPE MYRECORDTYPE IS REF CURSOR;

       PROCEDURE sp_proc_getDepartTable( MYRECORD_ OUT MYRECORDTYPE);//带返回值的存储过程
       procedure sp_proc_AddDepart(deptAdd varchar2,
                                   deptleader varchar2,
                                   deptmanage varchar2,
                                   deptmobile varchar2,
                                   deptname varchar2,
                                   depttel varchar2,
                                   deptparent varchar2,
                                   deptRemark varchar2);//不带返回值

end SysDepartPack;///要加end 结束

包体:(与包头的名称一致,包头定义几个存储过程,在包体中就要实现几个存储过程,而且要与包头中的存储过程参数名字一致)

CREATE OR REPLACE package BODY SysDepartPack AS
PROCEDURE sp_proc_getDepartTable( MYRECORD_ OUT MYRECORDTYPE)
IS///以IS开始,SQL 2005中用AS
begin
 OPEN MYRECORD_ FOR////返回值
            select * from sys_depart ;
end sp_proc_getDepartTable;

procedure sp_proc_AddDepart(deptAdd varchar2,
           deptleader varchar2,
           deptmanage varchar2,
           deptmobile varchar2,
           deptname varchar2,
           depttel varchar2,
           deptparent varchar2,
           deptRemark varchar2)
                                  
IS
  departmentID varchar2(50);
   MaxID varchar2(50);///参数定义,要用";"隔开
begin
if(deptparent='0')
then///如果用if else语句,要在If后加then,在if结束后加end if
select max(to_Number(DEPARTID)) into MaxID from sys_depart where PARENTID='0';

else
 begin

       select count(1) into coun from sys_depart where PARENTID like ''||deptparent||'%';///////oracel中的like用法,把||*参数*||
               if(coun>0) then
                       select max(to_Number(DEPARTID)) into MaxID from sys_depart where PARENTID like ''||deptparent||'%';
               else
                     select CONCAT(deptparent,'000') into MaxID from dual;
                end if;
  end;

end if;
select concat(substr(MaxID,1,LENGTH(MaxID)-3),Lpad(To_char(TO_NUMBER(substr(MaxID,-3,3))+1),3,0)) into departmentID from dual;

INSERT INTO SYS_DEPART (
DEPARTID ,
DEPARTNAME ,
DEPARTADDRESS ,
DEPARTLEADER ,
DEPARTMANAGER ,
DEPARTTEL ,
DEPARTMOBILE ,
REMARK ,
PARENTID ) VALUES (departmentID,deptname,deptAdd,
deptleader,deptmanage,depttel,deptmobile,deptRemark,deptparent);

end sp_proc_AddDepart;
end SysDepartPack;

 

 在程序中调用的时候用"包名+"."+存储过程名".

 

public DataTable getITAMTypeByID(string CategroyID)
{
ReturnMessageModel result = new ReturnMessageModel();
string strSql = "PACK_IAM_CATEGORYCONFIG.sp_GetITAMTypeByID";
OracleParameter[] para = {
new OracleParameter("p_DeptID", OracleDbType.Varchar2),
new OracleParameter("p_Result", OracleDbType.RefCursor) };
para[0].Value = CategroyID;
para[1].Direction = ParameterDirection.Output;
try
{
DataTable dt = OracleConnectDB.GetTable(para, strSql, true);
return dt;
}
catch
{

return null;
}
}