--构建数组
create or replace type ACTIONTYPE_VARCHAR2 as table of varchar2(1000);
create or replace type ENTITYID_VARCHAR2 as table of varchar2(1000);
--create or replace type VOUTYPEID_VARCHAR2 as table of varchar2(1000);
create or replace type WFID_VARCHAR2 as table of varchar2(1000);
create or replace type CURRENTNODE_VARCHAR2 as table of varchar2(1000);
create or replace type CURRENTSTATUS_NUMBER as table of number(10);
create or replace type NEXTNODE_VARCHAR2 as table of varchar2(1000);
create or replace type NEXTSTATUS_NUMBER as table of number(10);
create or replace type IS_UNDO_NUMBER as table of number(10);
create or replace type OPERUSER_VARCHAR2 as table of varchar2(1000);
--create or replace type OPERDATE_DATE as table of DATE;
create or replace type INITMONEY_NUMBER as table of number(10);
create or replace type RESULTMONEY_NUMBER as table of number(10);
create or replace type TOLLYFLAG_NUMBER as table of number(10);
create or replace type AUTOAUDITFLAG_NUMBER as table of number(10);
create or replace type TIMESTAMP_VARCHAR2 as table of varchar2(1000);
create or replace type MESSAGE_VARCHAR2 as table of varchar2(1000);
--create or replace type curtask_table as table of t_wfcurrenttasks.operdate%type ;
--type kcdm_table_type is table of T_KC.dm%TYPE index by binary_integer;
--create or replace type REMARK_VARCHAR2 as table of varchar2(1000);
create or replace procedure drivenWorkFlow(
PARAMS_ACTIONTYPE_VARCHAR2 in ACTIONTYPE_VARCHAR2,
PARAMS_ENTITYID_VARCHAR2 in ENTITYID_VARCHAR2,
--PARAMS_VOUTYPEID_VARCHAR2 in VOUTYPEID_VARCHAR2,
PARAMS_WFID_VARCHAR2 in WFID_VARCHAR2,--还原
PARAMS_CURRENTNODE_VARCHAR2 in CURRENTNODE_VARCHAR2,--还原
PARAMS_CURRENTSTATUS_NUMBER in CURRENTSTATUS_NUMBER,
PARAMS_NEXTNODE_VARCHAR2 in NEXTNODE_VARCHAR2,
PARAMS_NEXTSTATUS_NUMBER in NEXTSTATUS_NUMBER,
PARAMS_IS_UNDO_NUMBER in IS_UNDO_NUMBER,
PARAMS_OPERUSER_VARCHAR2 in OPERUSER_VARCHAR2,
--PARAMS_OPERDATE_DATE in OPERDATE_DATE,
PARAMS_INITMONEY_NUMBER in INITMONEY_NUMBER,
PARAMS_RESULTMONEY_NUMBER in RESULTMONEY_NUMBER,
PARAMS_TOLLYFLAG_NUMBER in TOLLYFLAG_NUMBER,
PARAMS_AUTOAUDITFLAG_NUMBER in AUTOAUDITFLAG_NUMBER,
--WFID_VARCHAR2 in varchar2,
--CURRENTNODE_VARCHAR2 in varchar2,
VOUTYPEID_VARCHAR2 in varchar2,
PARAMS_TIMESTAMP_VARCHAR2 in TIMESTAMP_VARCHAR2,
--PARAMS_REMARK_VARCHAR2 in REMARK_VARCHAR2 var_message_array OUT MESSAGE_VARCHAR2,
var_error out varchar2
)
is
--type curtask_table as table of t_wfcurrenttasks.operdate%type INDEX BY BINARY_INTEGER;
--v_operdate t_wfcurrenttasks.operdate%type;
var_nextnode varchar2(100);
var_cur_count number;
var_curstatus number;
var_nextstatus number;
var_nextnodeid varchar2(100);
var_nextnodeidcur varchar2(100);
var_nodetype varchar2(40);
var_count number;
var_cur_loop_count number;
--type v_operdate_table is table of t_wfcurrenttasks.operdate%type index by binary_integer;
--v_operdate v_operdate_table;
type v_timestamp_table is table of t_budgetvoucher.timestamp%type index by binary_integer;
v_timestamp v_timestamp_table;
cursor cur1 is
select NEXTNODEID from t_wfnodeconditions where WFID=PARAMS_WFID_VARCHAR2(i) and NODEID=PARAMS_CURRENTNODE_VARCHAR2(i);
begin
--行级锁
--select operdate bulk collect into v_operdate from t_wfcurrenttasks where WFID=WFID_VARCHAR2 for update wait 10;
for i in 1..PARAMS_ENTITYID_VARCHAR2.count loop
create or replace type ACTIONTYPE_VARCHAR2 as table of varchar2(1000);
create or replace type ENTITYID_VARCHAR2 as table of varchar2(1000);
--create or replace type VOUTYPEID_VARCHAR2 as table of varchar2(1000);
create or replace type WFID_VARCHAR2 as table of varchar2(1000);
create or replace type CURRENTNODE_VARCHAR2 as table of varchar2(1000);
create or replace type CURRENTSTATUS_NUMBER as table of number(10);
create or replace type NEXTNODE_VARCHAR2 as table of varchar2(1000);
create or replace type NEXTSTATUS_NUMBER as table of number(10);
create or replace type IS_UNDO_NUMBER as table of number(10);
create or replace type OPERUSER_VARCHAR2 as table of varchar2(1000);
--create or replace type OPERDATE_DATE as table of DATE;
create or replace type INITMONEY_NUMBER as table of number(10);
create or replace type RESULTMONEY_NUMBER as table of number(10);
create or replace type TOLLYFLAG_NUMBER as table of number(10);
create or replace type AUTOAUDITFLAG_NUMBER as table of number(10);
create or replace type TIMESTAMP_VARCHAR2 as table of varchar2(1000);
create or replace type MESSAGE_VARCHAR2 as table of varchar2(1000);
--create or replace type curtask_table as table of t_wfcurrenttasks.operdate%type ;
--type kcdm_table_type is table of T_KC.dm%TYPE index by binary_integer;
--create or replace type REMARK_VARCHAR2 as table of varchar2(1000);
create or replace procedure drivenWorkFlow(
PARAMS_ACTIONTYPE_VARCHAR2 in ACTIONTYPE_VARCHAR2,
PARAMS_ENTITYID_VARCHAR2 in ENTITYID_VARCHAR2,
--PARAMS_VOUTYPEID_VARCHAR2 in VOUTYPEID_VARCHAR2,
PARAMS_WFID_VARCHAR2 in WFID_VARCHAR2,--还原
PARAMS_CURRENTNODE_VARCHAR2 in CURRENTNODE_VARCHAR2,--还原
PARAMS_CURRENTSTATUS_NUMBER in CURRENTSTATUS_NUMBER,
PARAMS_NEXTNODE_VARCHAR2 in NEXTNODE_VARCHAR2,
PARAMS_NEXTSTATUS_NUMBER in NEXTSTATUS_NUMBER,
PARAMS_IS_UNDO_NUMBER in IS_UNDO_NUMBER,
PARAMS_OPERUSER_VARCHAR2 in OPERUSER_VARCHAR2,
--PARAMS_OPERDATE_DATE in OPERDATE_DATE,
PARAMS_INITMONEY_NUMBER in INITMONEY_NUMBER,
PARAMS_RESULTMONEY_NUMBER in RESULTMONEY_NUMBER,
PARAMS_TOLLYFLAG_NUMBER in TOLLYFLAG_NUMBER,
PARAMS_AUTOAUDITFLAG_NUMBER in AUTOAUDITFLAG_NUMBER,
--WFID_VARCHAR2 in varchar2,
--CURRENTNODE_VARCHAR2 in varchar2,
VOUTYPEID_VARCHAR2 in varchar2,
PARAMS_TIMESTAMP_VARCHAR2 in TIMESTAMP_VARCHAR2,
--PARAMS_REMARK_VARCHAR2 in REMARK_VARCHAR2 var_message_array OUT MESSAGE_VARCHAR2,
var_error out varchar2
)
is
--type curtask_table as table of t_wfcurrenttasks.operdate%type INDEX BY BINARY_INTEGER;
--v_operdate t_wfcurrenttasks.operdate%type;
var_nextnode varchar2(100);
var_cur_count number;
var_curstatus number;
var_nextstatus number;
var_nextnodeid varchar2(100);
var_nextnodeidcur varchar2(100);
var_nodetype varchar2(40);
var_count number;
var_cur_loop_count number;
--type v_operdate_table is table of t_wfcurrenttasks.operdate%type index by binary_integer;
--v_operdate v_operdate_table;
type v_timestamp_table is table of t_budgetvoucher.timestamp%type index by binary_integer;
v_timestamp v_timestamp_table;
cursor cur1 is
select NEXTNODEID from t_wfnodeconditions where WFID=PARAMS_WFID_VARCHAR2(i) and NODEID=PARAMS_CURRENTNODE_VARCHAR2(i);
begin
--行级锁
--select operdate bulk collect into v_operdate from t_wfcurrenttasks where WFID=WFID_VARCHAR2 for update wait 10;
for i in 1..PARAMS_ENTITYID_VARCHAR2.count loop
解决方案 »
- [求助]建一个存储过程
- Oracle日期转化问题
- 高分求一个关于sql的问题,在线等,帮忙解决的我会衷心的感谢!
- 这个简单的事务该怎样写
- 新手学习oracle有什么好书推荐啊
- 一个初学者的问题,请各位高手帮忙,并有高分相送(急,在线等)
- 这个错误如何解决:ORA-01000: 超出打开游标的最大数?
- 连接SQL——PLUS问题,用connect / as sysdba,抱错:服务句柄未初始化
- 请教:oracle 9i for aix 下如何使用OEM?
- 我建立的序列,但自增长有问题。
- pro*c中对于sql_context,sqlda,sqlca作为函数参数出错——急急
- 小菜请教大侠一个ebs forms的问题
cursor cur1 is
select NEXTNODEID from t_wfnodeconditions where WFID=PARAMS_WFID_VARCHAR2(i) and NODEID=PARAMS_CURRENTNODE_VARCHAR2(i);
这样会报错,因为游标中的变量i是for循环中的变量,但是游标必须定义在begin之前,如何得到for循环中的变量呢?
--方法一:最简单最有效也
for i in 1..PARAMS_ENTITYID_VARCHAR2.count loop
for rec in( select NEXTNODEID
from t_wfnodeconditions
where WFID=PARAMS_WFID_VARCHAR2(i)
and NODEID=PARAMS_CURRENTNODE_VARCHAR2(i)) loop
--处理逻辑。 rec.NEXTNODEID 即可引用
end loop;
end loop;--方法二:参数游标
cursor cur1(i number) is
select NEXTNODEID
from t_wfnodeconditions
where WFID=PARAMS_WFID_VARCHAR2(i)
and NODEID=PARAMS_CURRENTNODE_VARCHAR2(i);--引用的时候使用:
for i in 1..PARAMS_ENTITYID_VARCHAR2.count loop
open cur1(i);
.....
end loop;
open cur1(i);
loop
var_nextnodeidcur :=null;
fetch cur1(i) into var_nextnodeidcur ;
EXIT WHEN cur1(i)%NOTFOUND;
但是这么写fetch cur1(i) into var_nextnodeidcur ;
这行报错了
说出现符号(在需要下列之一时:.into bulk
open cur1(i);
loop
var_nextnodeidcur :=null;
fetch cur1 into var_nextnodeidcur ; --这里不要(i)
EXIT WHEN cur1%NOTFOUND; --这里也不要(i)
for i in 1..PARAMS_ENTITYID_VARCHAR2.count loop
for rec in( select NEXTNODEID
from t_wfnodeconditions
where WFID=PARAMS_WFID_VARCHAR2(i)
and NODEID=PARAMS_CURRENTNODE_VARCHAR2(i)) loop
var_nextnodeidcur := null;
var_nextnodeidcur := rec.NEXTNODEID;
.....--其他逻辑,for循环和游标结合,游标不需要人为声明、打开、fetch、关闭。 而是随着for循环自动打开,fetch、关闭的。
end loop;
end loop;
for i in 1..PARAMS_ENTITYID_VARCHAR2.count loop