第一次用存储过程,关于处理待办的,不知道怎么执行和传参数 给存储过程 ,其实就一句话很简单。 @call PRC_MISSIONLIST_QUERY('27020214', '27040000', '27040900'); 或者call PRC_MISSIONLIST_QUERY('27020214', '27040000', '27040900'); 其中的三个参数其实就是存储过程中的in 参数。 存储过程很复杂慢慢看。 (IN | OUT | INOUT parameter-name data-type,...) :传入参数
SPECIFIC specific-name:唯一的特定名称(别名),可以用存储过程名代替,这个特定名称用于dorp存储过程,或者给存储过程添加注视用,但不能调用存储过程。如果不指定,则数据库会自动生成一个yymmddhhmmsshhn时间戳的名字。推荐给出别名。
DYNAMIC RESULT SETS integer:指定存储过程返回结果的最大数量。存储过程中虽然没有return语句,但是却能返回结果集。
CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA: 指定存储过程中的SQL访问级别 DETERMINISTIC or NOT DETERMINISTIC:表示存储过程是动态或者非动态的。动态的返回的值是不确定的。非动态的存储过程每次执行返回的值是相同的。 CALLED ON NULL INPUT:表示可以调用存储过程而不管任何的输入参数是否为NULL,并且,任何的OUT或者INOUT参数可以返回一个NULL或者非空值。检验参数是否为NULL是在过程中进行的。 ----------SQL-variable-declaration:变量声明 ----------Variable declaration statement代码
![]() DECLARE variable-name data-type [DEFAULT value]
variable assign statement:变量赋值语句 常见的变量赋值语句有:SET,VALUES INTO,SELECT INTO,FETCH语句
Set statement代码
![]()
logic statement:逻辑语句
常见的逻辑语句有:IF,CASE,FOR,WHILE,ITERATE,LEAVE,LOOP,REPEAT,GOTO语句 RETURN,OPEN,CLOSE,CALL
说明:一般要使用游标之前,先declare,再open,这样游标才能fetch,最后close
>>--CLOSE--cursor-name-----------------------------------------><1.说明:关闭游标 .说明:调用其它的存储过程,一般IN类型的参数用户传值,OUT类型的参数用"?"代替,如 创建一个求两整数和的存储过程: CAREATE PROCEDURE SUM(IN p_one INT, IN p_two INT, OUT p_result INT) ...... 调用这个存储过程: CALL SUM(2,4,?)或CALL <user-schema>.SUM(2,4,?) 删除一个已创建的存储过程,语法:
Drop procedure statement代码
![]()
DROP SPECIFIC PROCEDURE specific-name DROP PROCEDURE procedure-name(parameter list)example: DROP SPECIFIC PROCEDURE SUM_ab;(假设上面SUM存储过程的SPECIFIC为SUM_ab) DROP PROCEDURE SUM(INT,INT,INT) CREATE PROCEDURE "TRAINING"."PRC_MISSIONLIST_QUERY" ( IN "V_EMPLOYEEID" VARCHAR(60), IN "V_UPPERUNIT2" VARCHAR(60), IN "V_UPPERUNIT3" VARCHAR(60) ) SPECIFIC "SQL121009165611600" DYNAMIC RESULT SETS 2 LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA OLD SAVEPOINT LEVEL begin declare V_SORT varchar(4) default ''; declare v_isCourseAdmain integer default 0; --其它 待办 信息 other:begin DECLARE other_list CURSOR WITH RETURN TO CALLER for /* 考试待办信息*/ select tm.seqcode flowid,'部门考试('||tet.EVALUATE_NAME||')' topic ,tm.created_time limitdate,tet.EVALUATE_SEQCODE flowNum,tm.end_time endDate, 'UTY' flowtype,'3' coalitionflag, '学员' upuserrole,0 missionlistId,(tm.created_time+ 2 day) endDate, case when (tm.created_time + 2 day) <= current timestamp then 1 else 0 end as status from TRAINING.TRAINING_EVALUATE tet inner join TRAINING.TRAINING_MAIN tm on tm.seqcode=tet.TRAINING_SEQCODE inner join TRAINING.TRAINING_PEPINFO tpi on tpi.TRAINING_SEQCODE=tm.seqcode where tm.del_time is null and (tm.STATE='0' or tm.state='3') and tm.kind='5' and tpi.pep_state='1' and (tpi.state='0' or tpi.state='2' or tpi.state='3') and tpi.join_flag='N' and tpi.PEP_ID=V_EMPLOYEEID and current timestamp between tm.start_time and tm.end_time; open other_list; end other; /*判断 用户是否有 课程管理员 权限*/ select count(er.SEQCODE) into v_isCourseAdmain from TRAINING.EMPLOYEE_ROLEINFO er inner join TRAINING.ROLEINFO rlf on rlf.SEQCODE = er.EMPLOYEE_ROLE where rlf.ROLE_NAME='课程管理员' and rlf.AVI_FLAG='1' and rlf.DEL_TIME is null and er.EMPLOYEE_ID =V_EMPLOYEEID and er.CROUSE_SORT_CODE is not null ; if v_isCourseAdmain > 0 then select max(er.CROUSE_SORT_CODE) into V_SORT from EMPLOYEE_ROLEINFO er where er.EMPLOYEE_ID = V_EMPLOYEEID; end if; /*查询 用户 待办*/ cur:begin DECLARE missionlist_list CURSOR WITH RETURN TO CALLER for select ml.FLOWOID flowid,ml.MISSIONLISTOID missionlistid, ml.FLOWNUM flownum,ml.USERROLEOID userroleoid,ml.UPUSERROLE upuserrole, ml.TOPIC topic,ml.USEPAGEURL url,ml.FLOWFLAG flowtype,ml.UPPERNAME uppername,ml.MISSIONDATE limitdate,(ml.MISSIONDATE + ml.DAYS_NUM day) endDate, case when (ml.MISSIONDATE + ml.DAYS_NUM day)<= current date then 1 else 0 end as status, ml.COALITION_FLAG coalitionflag,value(ri.seqcode,5) rolenum from MISSIONLIST_VIEW ml left join roleinfo ri on ml.USERROLEOID = ri.ROLE_NAME where (ml.useroid = V_EMPLOYEEID OR ml.USEROID = '0') AND (ml.upperunit2 = V_UPPERUNIT2 or ml.pepkind = '1') AND ( ( '' <> concat(V_UPPERUNIT3,'') AND concat(V_UPPERUNIT3,'') not in ('27000043','27000066') AND (ml.upperunit3 = V_UPPERUNIT3 or ml.pepkind != '3') ) OR 1=1 ) AND ( EXISTS ( select eri.EMPLOYEE_ROLE from EMPLOYEE_ROLEINFO eri where eri.EMPLOYEE_ID=V_EMPLOYEEID AND ri.SEQCODE = eri.EMPLOYEE_ROLE AND (eri.UNIT_TWO_CODE=V_UPPERUNIT2 OR (eri.UNIT_THREE_CODE=V_UPPERUNIT3 AND eri.UNIT_TWO_CODE=V_UPPERUNIT2)) ) OR ml.USERROLEOID = '学员' ) AND NOT EXISTS( select 1 from Missionlist_pep b where b.ml_seqcode = ml.Missionlistoid AND ( (ml.USERROLEOID='课程管理员' AND ml.FLOWFLAG NOT IN ('NCLF') AND EXISTS( select employee_id from EMPLOYEE_ROLEINFO dd where dd.EMPLOYEE_ID=b.PEP_CODE AND dd.CROUSE_SORT_CODE= V_SORT ) ) OR ( /*ml.USERROLEOID <> '课程管理员' AND ( */ b.pep_code = V_EMPLOYEEID OR (b.PEP_CODE = '0' AND ( b.unit_twocode = V_UPPERUNIT2 ||','|| V_UPPERUNIT3 OR b.unit_twocode = V_UPPERUNIT2 ||',')) ) )AND ml.FLOWFLAG not in ('CP','RGC') ) AND NOT EXISTS( SELECT b.unit_twocode FROM Missionlist_pep b INNER JOIN Missionlist c ON b.ML_SEQCODE = c.Missionlistoid WHERE c.userroleoid = '二级培训管理员' AND c.display_flag = '1' AND c.sparenum = 1 AND ml.USERROLEOID = '学员' and ml.flowflag=c.flowflag AND ml.flowoid = c.flowoid AND b.UNIT_TWOCODE=V_UPPERUNIT2||',' ) AND( ( ml.OBJ_UNITS is NOT NULL AND ( ( ml.OBJ_UNITS like concat('%',concat(V_UPPERUNIT2,'%')) AND ml.USERROLEOID<>'二级培训管理员' AND ml.PEPKIND ='1' -- AND ml.OBJ_UNITS like '%27220000%' ) OR ( ml.USERROLEOID ='二级培训管理员' AND ml.PEPKIND ='1' AND ml.OBJ_UNITS like concat('%',concat(V_UPPERUNIT2,'%')) AND EXISTS ( select aa.OBJ_UNITS from MISSIONLIST aa left join MISSIONLIST bb on aa.BACKMISOID=bb.MISSIONLISTOID where EXISTS ( select bbb.BACKMISOID from MISSIONLIST aaa left join MISSIONLIST bbb on aaa.BACKMISOID=bbb.MISSIONLISTOID where bbb.ISBACK=1 AND bbb.UPUSERROLE='课程管理员' AND aaa.MISSIONLISTOID=ml.MISSIONLISTOID AND bb.BACKMISOID=bbb.BACKMISOID ) AND aa.OBJ_UNITS like concat('%',concat(V_UPPERUNIT2,'%')) ) ) OR( ml.flowflag = 'CP' AND ml.OBJ_UNITS like concat('%',concat(V_UPPERUNIT2,'%')) AND ml.PEPKIND ='1' ) OR ( ml.pepkind ='2' AND ml.obj_units like concat('%',concat(V_UPPERUNIT3,'%')) ) ) ) OR ml.obj_units is null OR (ml.USERROLEOID='系统管理员' AND ml.FLOWNUM=10) ) OPEN missionlist_list ; -- commit; end cur; end
|
|