我想实现的功能是:五个变量,都是过程B的变量,我通过过程A调用过程B,首先是从account表中找出与变量account_相关的数据,放在临时表ACCOUNT_TEMP里,然后通过循环,把临时表中的数据当做过程B的参数,调用过程BProcedure A(sfdate_ in date, stodate_ in date, company_ in varchar2 ,account_ in varchar2 ,tjfg_ in varchar2)
is
CA_ account.account%type;
CB DATE;
CC DATE;
CD VARCHAR2(20);
CE VARCHAR2(20);
CF VARCHAR2(20);
CG VARCHAR2(20);
CH VARCHAR2(20);
CI VARCHAR2(20);
CJ VARCHAR2(20);
CURSOR get_attr IS
SELECT CA,CB,CC,CD,CE
FROM ACCOUNT_TEMP;
beginif account_ is not null then
select account into CA_ from account where account like account_||'%';
insert into ACCOUNT_TEMP (
CA,
CB,
CC,
CD,
CE,
CF,
CG,
CH,
CI,
CJ)
values (CA_,
sfdate_,
stodate_,
company_,
tjflag_,
'',
'',
'',
'',
'');
end if;
OPEN get_attr;
LOOP
fetch get_attr into CA_,CB,CC,CD,CE;
EXIT when get_attr%NOTFOUND;
A(CB , CC , CD ,CA_ ,CE);
END LOOP;
end TJSJH;
is
CA_ account.account%type;
CB DATE;
CC DATE;
CD VARCHAR2(20);
CE VARCHAR2(20);
CF VARCHAR2(20);
CG VARCHAR2(20);
CH VARCHAR2(20);
CI VARCHAR2(20);
CJ VARCHAR2(20);
CURSOR get_attr IS
SELECT CA,CB,CC,CD,CE
FROM ACCOUNT_TEMP;
beginif account_ is not null then
select account into CA_ from account where account like account_||'%';
insert into ACCOUNT_TEMP (
CA,
CB,
CC,
CD,
CE,
CF,
CG,
CH,
CI,
CJ)
values (CA_,
sfdate_,
stodate_,
company_,
tjflag_,
'',
'',
'',
'',
'');
end if;
OPEN get_attr;
LOOP
fetch get_attr into CA_,CB,CC,CD,CE;
EXIT when get_attr%NOTFOUND;
A(CB , CC , CD ,CA_ ,CE);
END LOOP;
end TJSJH;
所有的变量都是传入的参数
还要先去account表找出account=account_的account值,再赋值给ca
你又不做判断是否存在,如果没找到,此过程会出错
如果找到了 那ca实际上等于account_
另外不知道你account怎么控制,用like去判断可能会出现多条纪录的情况,也会出错
如果不需要判断account是否存在,也没必要去查询account表你的TEMP表不删除的?
这样的话,每次调用此过程,循环次数就会加1
你用LIKE就不对了,into只能是单一数据,你这样会出现多条数据,肯定报错的
多条数据要用游标的
照你的意思
游标要改一下,临时表不需要了
你这里变的只是account,其它参数都不变的
sfdate_ IN DATE,
stodate_ IN DATE,
company_ IN VARCHAR2,
account_ IN VARCHAR2,
tjfg_ IN VARCHAR2
)
IS
ca_ ACCOUNT.ACCOUNT%TYPE; CURSOR get_attr
IS
SELECT ACCOUNT
FROM ACCOUNT
WHERE ACCOUNT LIKE account_ || '%';
BEGIN
OPEN get_attr; LOOP
FETCH get_attr
INTO ca_; EXIT WHEN get_attr%NOTFOUND;
a (sfdate_, stodate_, company_, ca_, ce, tjflag_);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END tjsjh;
sfdate_ IN DATE,
stodate_ IN DATE,
company_ IN VARCHAR2,
account_ IN VARCHAR2,
tjfg_ IN VARCHAR2
)
IS
ca_ ACCOUNT.ACCOUNT%TYPE; CURSOR get_attr
IS
SELECT ACCOUNT
FROM ACCOUNT
WHERE ACCOUNT LIKE account_ || '%';
BEGIN
OPEN get_attr; LOOP
FETCH get_attr
INTO ca_; EXIT WHEN get_attr%NOTFOUND;
a (sfdate_, stodate_, company_, ca_, tjflag_);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END tjsjh;
select
into要求select的结果只能是一条纪录,不能出现多条纪录,否则会出错的
所以
select account into CA_ from account where account like account_ ¦ ¦'%';
这条语句按你的数据,输入10,出来就是5条数据,就肯定会报错