CREATE OR REPLACE PROCEDURE uf_CP_PAYVOUMASTER( p_id IN varchar2, p_name in VARCHAR2
) AS cursor fmd is select ' ' as BILL_NO , ' SD' as TRANS_TYPE ,' 0000' as CO_CODE ,'01' as PAYMENT_TYPE , TO_DATE(SUBSTR(PAY_DATE,1,10),'YYYY-MM-DD') as PAY_DATETIME ,TO_NUMBER(FISCAL) as FISCAL ,TO_NUMBER(FIS_PERD) as FIS_PERD ,'01 ' as WAY_OF_PAY , '01 ' as CAPT_ORGION ,substr(BG_DEPTCODE,1,1) as PARLOR_CODE /* 前一个字符*/ ,substr(BG_DEPTCODE,1,4) as CHARGE_DEPTCODE /* 前4个字符*/ ,BG_DEPTCODE as BG_DEPTCODE , '单位名称' as RECE_NAME , BG_DEPTCODE as PAY_CODE /*单位代码*/ ,'建设银行工资统发户' as PAY_BANKACCCODE , 'JS ' as PAY_BANKCODE , TO_NUMBER(bi_sum) as PAY_TOTALSUM ,' ' as CDATE , 'system' as INPUTOR , '工资统发' as USAGE , 'Y' as BLAN_STATUS , 'Balanced' as STATUS , PAY_DATE as A_DATE ,'system' as AUDITOR , 'Y' as SINGLE , TO_DATE(PAY_DATE,'YYYY/MM/DD') AS PAY_DATE , 'system' as PAY_MAN , 'N' as IS_RETURN , 'N' as IS_DPB , 'N' as IS_DPL , 'N' as IS_DPR , TO_NUMBER(FISCAL) as PAY_FISCAL ,TO_NUMBER(FIS_PERD) as PAY_FISPERD , TO_NUMBER(FISCAL) as BAL_FISCAL , TO_NUMBER(FIS_PERD) as BAL_FISPERD from uf_gkgz where FISCAL=p_id and FIS_PERD=p_name ; as begin for testfmd in fmd loop insert into CP_PAYVOUMASTER( testfmd.bill_no,testfmd.......); end loop end; 这样很简单的
for testfmd in fmd loop select max(bill_no) into bill_noff from CP_PAYVOUMASTER where FISCAL=P_ID AND FIS_FERD=p_name ; insert into CP_PAYVOUMASTER( testfmd.bill_no,testfmd.......); end loop end; 如果我在这里插入一个查询语句,可以吗
for testfmd in fmd loop select max(bill_no)+1 into bill_noff from CP_PAYVOUMASTER where FISCAL=P_ID AND FIS_FERD=p_name ; /*放这句在这里可以重复读取吗*/ insert into CP_PAYVOUMASTER( testfmd.bill_no,testfmd.......); end loop end; 我想读取表 CP_PAYVOUMASTER单据号bill_no 最大值加一做为下一条记录的单据号插入,这样赋值给变量bil_noff可以吗
p_id IN varchar2,
p_name in VARCHAR2
) AS
cursor fmd is
select
' ' as BILL_NO
, ' SD' as TRANS_TYPE
,' 0000' as CO_CODE
,'01' as PAYMENT_TYPE
, TO_DATE(SUBSTR(PAY_DATE,1,10),'YYYY-MM-DD') as PAY_DATETIME
,TO_NUMBER(FISCAL) as FISCAL
,TO_NUMBER(FIS_PERD) as FIS_PERD
,'01 ' as WAY_OF_PAY
, '01 ' as CAPT_ORGION
,substr(BG_DEPTCODE,1,1) as PARLOR_CODE /* 前一个字符*/
,substr(BG_DEPTCODE,1,4) as CHARGE_DEPTCODE /* 前4个字符*/
,BG_DEPTCODE as BG_DEPTCODE
, '单位名称' as RECE_NAME
, BG_DEPTCODE as PAY_CODE /*单位代码*/
,'建设银行工资统发户' as PAY_BANKACCCODE
, 'JS ' as PAY_BANKCODE
, TO_NUMBER(bi_sum) as PAY_TOTALSUM
,' ' as CDATE
, 'system' as INPUTOR
, '工资统发' as USAGE
, 'Y' as BLAN_STATUS
, 'Balanced' as STATUS
, PAY_DATE as A_DATE
,'system' as AUDITOR
, 'Y' as SINGLE
, TO_DATE(PAY_DATE,'YYYY/MM/DD') AS PAY_DATE
, 'system' as PAY_MAN
, 'N' as IS_RETURN
, 'N' as IS_DPB
, 'N' as IS_DPL
, 'N' as IS_DPR
, TO_NUMBER(FISCAL) as PAY_FISCAL
,TO_NUMBER(FIS_PERD) as PAY_FISPERD
, TO_NUMBER(FISCAL) as BAL_FISCAL
, TO_NUMBER(FIS_PERD) as BAL_FISPERD
from uf_gkgz where FISCAL=p_id and FIS_PERD=p_name ;
as
begin
for testfmd in fmd loop
insert into CP_PAYVOUMASTER( testfmd.bill_no,testfmd.......);
end loop
end;
这样很简单的
select max(bill_no) into bill_noff from CP_PAYVOUMASTER where FISCAL=P_ID AND FIS_FERD=p_name ; insert into CP_PAYVOUMASTER( testfmd.bill_no,testfmd.......);
end loop
end;
如果我在这里插入一个查询语句,可以吗
select max(bill_no)+1 into bill_noff from CP_PAYVOUMASTER where FISCAL=P_ID AND FIS_FERD=p_name ; /*放这句在这里可以重复读取吗*/
insert into CP_PAYVOUMASTER( testfmd.bill_no,testfmd.......);
end loop
end;
我想读取表 CP_PAYVOUMASTER单据号bill_no 最大值加一做为下一条记录的单据号插入,这样赋值给变量bil_noff可以吗