存储过程的声明顺序和赋值顺序、以及存储过程执行时打印信息

/* 可以通过编辑状态下的运行传递需要的参数点运行，看运行后的状态进行调试*/

create or replace PROCEDURE "XXXXXX"
(hosptalNO in varchar2,userID in number,billno in varchar2)
is
cursor cr_numbers is
select d.depositRate,d.drugid,d.materialsign,(case when nvl(d.WPRICE,0)=0 then nvl(dd.WPRICE,0) else nvl(d.WPRICE,0) end) as WPRICE,nvl(d.SPEC,'') as SPEC,nvl(dd.place,'') as PLACE, (case when nvl(d.packPrice,0)=0 then nvl(dd.packPrice,0) else nvl(d.packPrice,0) end) as packPrice,(case when nvl(d.rprice,0)=0 then nvl(dd.rprice,0) else nvl(d.rprice,0) end) as rprice,0 as MATERIALID
from drug d left outer join drugdetails dd on d.drugid=dd.drugid where d.type=4 and d.MATERIALSIGN='0'
union all
SELECT DEPOSITRATE,nvl(drugid,0) as drugid, '1',PACKPRICE,NVL(SPEC,'') AS SPEC,NVL(PLACE,'//') AS PLACE,PACKPRICE,RPRICE,MATERIALID FROM MATERIAL WHERE MATERIALTYPE='5' ;
num number;
v_depositRate drug.depositRate%type;
v_drugid drug.drugid%type;
v_materialsign drug.materialsign%type;
v_WPRICE drugdetails.WPRICE%type;
v_place drugdetails.place%type;
v_SPEC drug.SPEC%type;
v_packPrice drugdetails.packPrice%type;
v_rprice drugdetails.rprice%type;
v_MATERIALID MATERIAL.MATERIALID%type; ----放置顺序要按照查询结果顺序
drugpaID number;
string varchar2(200);
newbillno number;
v_pa number;
begin
newbillno := to_number(billno);
open cr_numbers;
loop
fetch cr_numbers into v_depositRate,v_drugid,v_materialsign,v_WPRICE,v_place,v_SPEC,v_packPrice,v_rprice,v_MATERIALID; ---放置顺序最好是按照声明的顺序
exit when cr_numbers%notfound;
drugpaID:=getTableMaxID('drugPA');
select count(1) into v_pa from drugpa where (DRUGID = v_drugid or MATERIALID=v_MATERIALID ) and hospitalNO=hosptalNO;
if v_pa <= 0 then
dbms_output.put_line('insert into drugpa (drugpaID,BILLDATE,AUDITSIGN,PAWHY,DRUGTYPESIGN,OLDPRICE,NOWPRICE,DISCOUNT,NOWDISCOUNT,OLDWP,NOWWP,BILLERID,HOSPITALNO,AUDITID,AUDITTIME,updtUserID,UPDATETIME,OPERATERID,OPERATETIME,DRUGID,SPEC,place,NOWPP,OLDPP,DRUGPABILLNO,NOWRPRICE,MATERIALID,MATERIALSIGN,SIGN) values('||drugpaID||',sysdate,1,''费用类导入'',1,'||v_packPrice||','||v_packPrice||','||v_depositRate||','||v_depositRate||','||v_WPRICE||','||v_WPRICE||','||userID||','''||hosptalNO||''','||userID||',sysdate,'||userID||',sysdate,'||userID||',sysdate,'||v_drugid||','''||v_SPEC||''','''||v_place||''','||v_packPrice||','||v_packPrice||','''||newbillno||''','||v_rprice||','||v_MATERIALID||','||v_materialsign||',1)'); -----在调试数据时，最好是把打印部分放需要执行sql的前面。
execute immediate 'insert into drugpa (drugpaID,BILLDATE,AUDITSIGN,PAWHY,DRUGTYPESIGN,OLDPRICE,NOWPRICE,DISCOUNT,NOWDISCOUNT,OLDWP,NOWWP,BILLERID,HOSPITALNO,AUDITID,AUDITTIME,updtUserID,UPDATETIME,OPERATERID,OPERATETIME,DRUGID,SPEC,place,NOWPP,OLDPP,DRUGPABILLNO,NOWRPRICE,MATERIALID,MATERIALSIGN,SIGN) values('||drugpaID||',sysdate,1,''费用类导入'',1,'||v_packPrice||','||v_packPrice||','||v_depositRate||','||v_depositRate||','||v_WPRICE||','||v_WPRICE||','||userID||','''||hosptalNO||''','||userID||',sysdate,'||userID||',sysdate,'||userID||',sysdate,'||v_drugid||','''||v_SPEC||''','''||v_place||''','||v_packPrice||','||v_packPrice||','''||newbillno||''','||v_rprice||','||v_MATERIALID||','||v_materialsign||',1)';
newbillno := newbillno+1;
end if;
end loop;
update billNumber set MAXBillNO = newbillno where hosptalNO = hosptalNO and billtype='drugPAbillNO' and to_char(sysdate,'yyyy-MM-dd')=to_char(billdate,'yyyy-MM-dd');
commit;
close cr_numbers;
end;