要求是: 从
t_credit 查询出所有的记录,循环,把CREDIT_NUM=lcNum执行以下方法.
update t_credit tc
set tc.used_amt = (select sum(nvl(til.amount, 0))
from t_invoice ti,t_invoice_line til
where ti.inv_id = til.inv_id
and ti.lc_num=#lcNum#
and til.pay_type like 'L/C%'
)
where tc.credit_num = #lcNum#我的写法:create or replace procedure test(CREDIT_NUM in varchar2, errMessage out varchar2) is
DECLARE
CURSOR CREDIT_NUM_emp IS SELECT CREDIT_NUM FROM t_credit;
V_CREDIT_NUM t_credit.Credit_Num%TYPE;
BEGIN
OPEN CREDIT_NUM_emp;
FETCH CREDIT_NUM_emp INTO V_CREDIT_NUM;
EXIT WHEN CREDIT_NUM_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('NUM'||'is'|| V_CREDIT_NUM); loop
-- ×ö¸üÐÂ
update t_credit tc
set tc.used_amt = (select sum(nvl(til.amount, 0))
from t_invoice ti,t_invoice_line til
where ti.inv_id = til.inv_id
and ti.lc_num=V_CREDIT_NUM
and til.pay_type like 'L/C%'
)
END LOOP;
CLOSE CREDIT_NUM_emp;
exception
when NO_DATA_FOUND then
errMessage := 'no data';
return;
end;end test;
报错情况:Compilation errors for PROCEDURE IEMIS.TESTError: PLS-00103: ³出现符号declare在需要之一º
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
external language
·ûºÅ "begin" ±»Ì滻Ϊ "DECLARE" ºó¼ÌÐø¡£
Line: 3
Text: DECLAREError: PLS-00103: ³出现符号END在下列需要之一º
, ; return returning where
·ûºÅ ";" ±»Ì滻Ϊ "END" ºó¼ÌÐø¡£
Line: 21
Text: END LOOP;Compilation errors for PROCEDURE IEMIS.TESTError: PLS-00103: ³öÏÖ·ûºÅ "DECLARE"ÔÚÐèÒªÏÂÁÐ֮һʱ£º
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
external language
·ûºÅ "begin" ±»Ì滻Ϊ "DECLARE" ºó¼ÌÐø¡£
Line: 3
Text: DECLAREError: PLS-00103: ³öÏÖ·ûºÅ "END"ÔÚÐèÒªÏÂÁÐ֮һʱ£º
, ; return returning where
·ûºÅ ";" ±»Ì滻Ϊ "END" ºó¼ÌÐø¡£
Line: 21
Text: END LOOP;
t_credit 查询出所有的记录,循环,把CREDIT_NUM=lcNum执行以下方法.
update t_credit tc
set tc.used_amt = (select sum(nvl(til.amount, 0))
from t_invoice ti,t_invoice_line til
where ti.inv_id = til.inv_id
and ti.lc_num=#lcNum#
and til.pay_type like 'L/C%'
)
where tc.credit_num = #lcNum#我的写法:create or replace procedure test(CREDIT_NUM in varchar2, errMessage out varchar2) is
DECLARE
CURSOR CREDIT_NUM_emp IS SELECT CREDIT_NUM FROM t_credit;
V_CREDIT_NUM t_credit.Credit_Num%TYPE;
BEGIN
OPEN CREDIT_NUM_emp;
FETCH CREDIT_NUM_emp INTO V_CREDIT_NUM;
EXIT WHEN CREDIT_NUM_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('NUM'||'is'|| V_CREDIT_NUM); loop
-- ×ö¸üÐÂ
update t_credit tc
set tc.used_amt = (select sum(nvl(til.amount, 0))
from t_invoice ti,t_invoice_line til
where ti.inv_id = til.inv_id
and ti.lc_num=V_CREDIT_NUM
and til.pay_type like 'L/C%'
)
END LOOP;
CLOSE CREDIT_NUM_emp;
exception
when NO_DATA_FOUND then
errMessage := 'no data';
return;
end;end test;
报错情况:Compilation errors for PROCEDURE IEMIS.TESTError: PLS-00103: ³出现符号declare在需要之一º
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
external language
·ûºÅ "begin" ±»Ì滻Ϊ "DECLARE" ºó¼ÌÐø¡£
Line: 3
Text: DECLAREError: PLS-00103: ³出现符号END在下列需要之一º
, ; return returning where
·ûºÅ ";" ±»Ì滻Ϊ "END" ºó¼ÌÐø¡£
Line: 21
Text: END LOOP;Compilation errors for PROCEDURE IEMIS.TESTError: PLS-00103: ³öÏÖ·ûºÅ "DECLARE"ÔÚÐèÒªÏÂÁÐ֮һʱ£º
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
external language
·ûºÅ "begin" ±»Ì滻Ϊ "DECLARE" ºó¼ÌÐø¡£
Line: 3
Text: DECLAREError: PLS-00103: ³öÏÖ·ûºÅ "END"ÔÚÐèÒªÏÂÁÐ֮һʱ£º
, ; return returning where
·ûºÅ ";" ±»Ì滻Ϊ "END" ºó¼ÌÐø¡£
Line: 21
Text: END LOOP;
update table_a a set a.cola=(select b.colb from table_b b where b.colc=a.colc);
在loop .. end loop 中间除了一个update再无其它,这会死循环的.
其次,如上楼说.
第三 ,update语句后面应该跟上分号.这是目前发现的.
CREATE OR REPLACE PROCEDURE TEST (
credit_num IN VARCHAR2,
errmessage OUT VARCHAR2
)
IS
CURSOR credit_num_emp
IS
SELECT credit_num
FROM t_credit; v_credit_num t_credit.credit_num%TYPE;
BEGIN
OPEN credit_num_emp; FETCH credit_num_emp
INTO v_credit_num; dbms_out.put.put_line ('NUM ' || 'is ' || v_credit_num); LOOP
-- exit when 要在loop中。
EXIT WHEN credit_num_emp%NOTFOUND; UPDATE t_credit tc
SET tc.used_amt =
(SELECT SUM (NVL (til.amount, 0))
FROM t_invoice ti, t_invoice_line til
WHERE ti.inv_id = til.inv_id
AND ti.lc_num = v_credit_num
AND til.pay_type LIKE 'L/C% ');
--语句结束要有';'
END LOOP; CLOSE credit_num_emp;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT ('no data');
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END TEST;
/