DB2 存储过程，触发器，函数

我很黑！

SP：
CREATE PROCEDURE PROCESS_ADDNOTICE(IN RECORDOID BIGINT,
IN STARTERID BIGINT,
OUT RESULTSTATUS INTEGER,
OUT RESULTMSG VARCHAR(4000))
begin
--第三步 游标定义
--声明变量
--第四步 业务变量定义
--声明表字段变量
declare tempOid bigint default -1;
declare tempOperateTime timestamp;
declare tempOperator bigint;
declare tempYearly varchar(32);
declare tempNoticeName varchar(256);
declare tempReceiveUnitId bigint;
declare tempReceiveUnitName varchar(1024);
declare tempFileId bigint;
declare tempJobId varchar(256);
declare tempJobName varchar(1024) default '';
declare tempContext varchar(1024);
declare tempStartDate timestamp;
declare tempEndDate timestamp;
declare tempindex bigint;
declare tempvalue varchar(1024);
declare tempJobIdT varchar(256);
--第二步 异常变量、异常处理定义
--输出的错误信息和错误标志状态
declare SQLCODE integer default 0;
declare tempSQLCODE integer default 0;
--出错处理
declare continue handler for sqlexception,not found,sqlwarning
begin
set tempSQLCODE = SQLCODE;
end;
--第五步 业务数据查询
--查询申请源数据
select  
C_OID,C_OPERATETIME,C_OPERATOR,C_YEARLY,C_NOTICENAME,C_RECEIVEUNITID,C_FILEID,C_JOBCODE,C_CONTEXT,C_STARTDATE,C_ENDDATE
into tempOid,tempOperateTime,tempOperator,tempYearly,tempNoticeName,tempReceiveUnitId,tempFileId,tempJobId,tempContext,tempStartDate,tempEndDate from TB_INF_ADDNOTICE where C_OID=RecordOID;
select C_NAME into tempReceiveUnitName from TB_ORG_ORGUNIT where C_OID_ORGUNIT = tempReceiveUnitId;
--select C_NAME into tempJobName from TB_ORG_ASSIGNEDJOB where C_OID_ASSIGNEDJOB = tempJobId;
--设置选拔岗位名称
set tempJobIdT = tempJobId;
while tempJobIdT is not null do
set tempindex = locate('|',tempJobIdT);
set tempvalue = (select a.C_NAME from tb_org_assignedjob a where char(a.C_OID_ASSIGNEDJOB) = SUBSTR(tempJobIdT,1,8));
set tempJobName = Concat(tempJobName,Concat(',',tempvalue));
if tempindex > 0 then
set tempJobIdT = SUBSTR(tempJobIdT,integer(tempindex+1)); end if;
if tempindex <= 0 then
set tempJobIdT = null;
end if;
end while;
----增加公告信息
insert into TB_PRE_POST (C_OID,C_YEAR,C_OPERATOR,C_OPERATETIME,C_CAPTION,C_JOBIDS,C_JOBNAMES,C_UNITIDS,C_UNITNAMES,C_BLOBID,C_STARTDATE,C_ENDDATE,C_TYPE,C_STATUS) values  
(tempOid,tempYearly,tempOperator,tempOperateTime,tempNoticeName,tempJobId,SUBSTR(tempJobName,2),char(tempReceiveUnitId),tempReceiveUnitName,tempFileId,tempStartDate,tempEndDate,'1','2');
if tempSQLCODE<>0
then
set ResultStatus=tempSQLCODE;
set ResultMSG='插入公告信息时出错,错误代码:'||char(tempSQLCODE);
rollback;
return -1;
end if;
--如果执行到此
set ResultStatus=1;
set ResultMSG='执行成功';
return 1;
end
Trigger：CREATE TRIGGER TB_TRI_S_INUNIT
BEFORE INSERT
ON TB_INF_INUNIT
REFERENCING NEW AS NEWROW FOR EACH ROW
begin ATOMIC
set NEWROW.c_unitid = ( select a.c_unitid from tb_inf_employee a where a.c_employeeid = NEWROW.c_empoid);
set NEWROW.c_jobid = ( select a.c_jobid from tb_inf_employee a where a.c_employeeid = NEWROW.c_empoid);
end
function：
create function get_positionName(c_empid  bigint) returns varchar(1024)
begin atomic
declare positionName varchar(1024) default '';
declare tempName varchar(1024) default ' ';
declare col_counter smallint default 1;
declare num_of_cols smallint;
set num_of_cols = (
select count(*)
from TB_HNYC.TB_PER_EMPLOYEEPOSITION);
while col_counter <= num_of_cols do
set tempName = (
select c_positionName
from (
select ROW_NUMBER() over() as a, TB_HNYC.TB_PER_EMPLOYEEPOSITION.*
from TB_HNYC.TB_PER_EMPLOYEEPOSITION) as temp
where a=col_counter
and c_przt!='8'
and c_przt!='2'
and c_IFCURRENTPOST = '1'
and c_empoid = c_empid
order by c_jobtype asc,c_operatetime desc);
if tempName is not null then
set positionName = positionName||','||tempName;
end if;
set col_counter = col_counter + 1;
end while;
if length(positionName)>0 then
return right(positionName,length(positionName)-1);
end if;
end