下面是游标的代码
SQL>
declare
v_hsjg zn.swjg_dm%TYPE;
cursor c_kt_ktsx is
select distinct zn.swjg_dm hsjg, jg.mc title, jg.sjswjg_dm parentid
from t_xt_swjg_zn zn, t_dm_gy_swjg jg
where zn.zn_dm = '4'
and zn.swjg_dm = jg.swjg_dm
and jg.jgjc='6'
and jg.mc not in ('甘肃矿区区级(虚拟)')
connect by prior zn.swjg_dm = zn.sjznjg_dm
and zn.zn_dm = '4'
start with zn.swjg_dm in (select jgfw.swjg_dm
from t_xt_swry_zn_jgfw jgfw
where jgfw.swry_dm = '26200000000'
and jgfw.zn_dm = '4')
begin
open c_kt_ktsx
LOOP FETCH c_kt_ktsx INTO v_hsjg;
EXIT WHEN c_kt_ktsx%NOTFOUND;
db_zgxt.p_kt_ktsxcsh(v_hsjg,'20090301','26200000000'); /*调用过程*/
end loop;
close c_kt_ktsx;
end;
下面是错误信息
ORA-06550: line 16, column 53:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 21, column 11:
PLS-00103: Encountered the symbol "WHEN" when expecting one of the following: constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national ch
ORA-06550: line 22, column 6:
PLS-00103: Encountered the symbol "DB_ZGXT"
ORA-06550: line 22, column 59:
PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( , * % & - + / at mod rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || bulk
修改后的游标代码
SQL>
declarecursor c_kt_ktsx is
select distinct zn.swjg_dm hsjg
from t_xt_swjg_zn zn, t_dm_gy_swjg jg
where zn.zn_dm = '4'
and zn.swjg_dm = jg.swjg_dm
and jg.jgjc='6'
and jg.mc not in ('甘肃矿区区级(虚拟)')
connect by prior zn.swjg_dm = zn.sjznjg_dm
and zn.zn_dm = '4'
start with zn.swjg_dm in (select jgfw.swjg_dm
from t_xt_swry_zn_jgfw jgfw
where jgfw.swry_dm = '26200000000'
and jgfw.zn_dm = '4')begin
for bb in c_kt_ktsx loop insert into T_KT_KTCZXX
(HSJG_DM, QS_SJ,CZRY_DM)
values
('bb.hsjg','200903','26200000000');
end loop;
/*p_kt_ktsxcsh(bb,'200903','26200000000');*/
end;错误信息
ORA-06550: line 17, column 2:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 24, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following: begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
SQL>
declare
v_hsjg zn.swjg_dm%TYPE;
cursor c_kt_ktsx is
select distinct zn.swjg_dm hsjg, jg.mc title, jg.sjswjg_dm parentid
from t_xt_swjg_zn zn, t_dm_gy_swjg jg
where zn.zn_dm = '4'
and zn.swjg_dm = jg.swjg_dm
and jg.jgjc='6'
and jg.mc not in ('甘肃矿区区级(虚拟)')
connect by prior zn.swjg_dm = zn.sjznjg_dm
and zn.zn_dm = '4'
start with zn.swjg_dm in (select jgfw.swjg_dm
from t_xt_swry_zn_jgfw jgfw
where jgfw.swry_dm = '26200000000'
and jgfw.zn_dm = '4')
begin
open c_kt_ktsx
LOOP FETCH c_kt_ktsx INTO v_hsjg;
EXIT WHEN c_kt_ktsx%NOTFOUND;
db_zgxt.p_kt_ktsxcsh(v_hsjg,'20090301','26200000000'); /*调用过程*/
end loop;
close c_kt_ktsx;
end;
下面是错误信息
ORA-06550: line 16, column 53:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 21, column 11:
PLS-00103: Encountered the symbol "WHEN" when expecting one of the following: constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national ch
ORA-06550: line 22, column 6:
PLS-00103: Encountered the symbol "DB_ZGXT"
ORA-06550: line 22, column 59:
PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( , * % & - + / at mod rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || bulk
修改后的游标代码
SQL>
declarecursor c_kt_ktsx is
select distinct zn.swjg_dm hsjg
from t_xt_swjg_zn zn, t_dm_gy_swjg jg
where zn.zn_dm = '4'
and zn.swjg_dm = jg.swjg_dm
and jg.jgjc='6'
and jg.mc not in ('甘肃矿区区级(虚拟)')
connect by prior zn.swjg_dm = zn.sjznjg_dm
and zn.zn_dm = '4'
start with zn.swjg_dm in (select jgfw.swjg_dm
from t_xt_swry_zn_jgfw jgfw
where jgfw.swry_dm = '26200000000'
and jgfw.zn_dm = '4')begin
for bb in c_kt_ktsx loop insert into T_KT_KTCZXX
(HSJG_DM, QS_SJ,CZRY_DM)
values
('bb.hsjg','200903','26200000000');
end loop;
/*p_kt_ktsxcsh(bb,'200903','26200000000');*/
end;错误信息
ORA-06550: line 17, column 2:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 24, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following: begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
select distinct zn.swjg_dm hsjg, jg.mc title, jg.sjswjg_dm parentid
from t_xt_swjg_zn zn, t_dm_gy_swjg jg
where zn.zn_dm = '4'
and zn.swjg_dm = jg.swjg_dm
and jg.jgjc='6'
and jg.mc not in ('甘肃矿区区级(虚拟)')
connect by prior zn.swjg_dm = zn.sjznjg_dm
and zn.zn_dm = '4'
start with zn.swjg_dm in (select jgfw.swjg_dm
from t_xt_swry_zn_jgfw jgfw
where jgfw.swry_dm = '26200000000'
and jgfw.zn_dm = '4')
这个SQL能运行?
v_hsjg zn.swjg_dm hsjg%TYPE;
cursor c_kt_ktsx is
select distinct zn.swjg_dm hsjg
from t_xt_swjg_zn zn, t_dm_gy_swjg jg
where zn.zn_dm = '4'
and zn.swjg_dm = jg.swjg_dm
and jg.jgjc='6'
and jg.mc not in ('甘肃矿区区级(虚拟)')
connect by prior zn.swjg_dm = zn.sjznjg_dm
and zn.zn_dm = '4'
start with zn.swjg_dm in (select jgfw.swjg_dm
from t_xt_swry_zn_jgfw jgfw
where jgfw.swry_dm = '26200000000'
and jgfw.zn_dm = '4')
begin
open c_kt_ktsx
LOOP
FETCH c_kt_ktsx INTO v_hsjg;
EXIT WHEN c_kt_ktsx%NOTFOUND;
db_zgxt.p_kt_ktsxcsh(v_hsjg,'20090301','26200000000');
end loop;
close c_kt_ktsx;
end;
/可以运行啊,后面两个字段取了也行
PRIKEY TITLE PARENTID
1 262020001 ***地方税务局区 262020000
2 262030200 ****地方税务局 262030000
3 262032100 **永昌县地方税务局 262030000
4 26204000013 *地税局重点税源行业征收管理分局 262040000
5 262040200 *地方税务局 262040000
6 262040300 *地方税务局 262040000
7 262042100 *地方税务局 262040000
FETCH c_kt_ktsx INTO v_hsjg;
EXIT WHEN c_kt_ktsx%NOTFOUND;
--db_zgxt.p_kt_ktsxcsh(v_hsjg,'20090301','26200000000');
dbms_output.put_line(v_hsjg);
end loop; 先看看这样执行时是否有值?如果有就要看看你的db_zgxt.p_kt_ktsxcsh存储过程了
declare
v_hsjg zn.swjg_dm%TYPE;
cursor c_kt_ktsx is
select distinct zn.swjg_dm hsjg, jg.mc title, jg.sjswjg_dm parentid
from t_xt_swjg_zn zn, t_dm_gy_swjg jg
where zn.zn_dm = '4'
and zn.swjg_dm = jg.swjg_dm
and jg.jgjc = '6'
and jg.mc not in ('甘肃矿区区级(虚拟)')
connect by prior zn.swjg_dm = zn.sjznjg_dm
and zn.zn_dm = '4'
start with zn.swjg_dm in (select jgfw.swjg_dm
from t_xt_swry_zn_jgfw jgfw
where jgfw.swry_dm = '26200000000'
and jgfw.zn_dm = '4');
begin
open c_kt_ktsx;
LOOP
FETCH c_kt_ktsx
INTO v_hsjg;
EXIT WHEN c_kt_ktsx%NOTFOUND;
db_zgxt.p_kt_ktsxcsh(v_hsjg, '20090301', '26200000000'); /*调用过程*/
end loop;
close c_kt_ktsx;
end;
还有你的zn.swjg_dm%TYPE类型是列类型还是数组的?
如果是列你fetch into肯定不行的。
试试下边的:
DECLARE
V_HSJG ZN.SWJG_DM%TYPE;
CURSOR C_KT_KTSX IS
SELECT DISTINCT ZN.SWJG_DM HSJG/*, JG.MC TITLE, JG.SJSWJG_DM PARENTID*/
FROM T_XT_SWJG_ZN ZN, T_DM_GY_SWJG JG
WHERE ZN.ZN_DM = '4'
AND ZN.SWJG_DM = JG.SWJG_DM
AND JG.JGJC = '6'
AND JG.MC NOT IN ('甘肃矿区区级(虚拟)')
CONNECT BY PRIOR ZN.SWJG_DM = ZN.SJZNJG_DM
AND ZN.ZN_DM = '4'
START WITH ZN.SWJG_DM IN (SELECT JGFW.SWJG_DM
FROM T_XT_SWRY_ZN_JGFW JGFW
WHERE JGFW.SWRY_DM = '26200000000'
AND JGFW.ZN_DM = '4');
BEGIN
OPEN C_KT_KTSX;
LOOP
FETCH C_KT_KTSX
INTO V_HSJG;
EXIT WHEN C_KT_KTSX%NOTFOUND;
DB_ZGXT.P_KT_KTSXCSH(V_HSJG, '20090301', '26200000000'); /*调用过程*/
END LOOP;
CLOSE C_KT_KTSX;
END;
/
declare
v_hsjg varchar2(20);
cursor c_kt_ktsx is
select distinct zn.swjg_dm
from t_xt_swjg_zn zn, t_dm_gy_swjg jg
where zn.zn_dm = '4'
and zn.swjg_dm = jg.swjg_dm
and jg.jgjc='6'
and jg.mc not in ('甘肃矿区区级(虚拟)')
connect by prior zn.swjg_dm = zn.sjznjg_dm
and zn.zn_dm = '4'
start with zn.swjg_dm in (select jgfw.swjg_dm
from t_xt_swry_zn_jgfw jgfw
where jgfw.swry_dm = '26200000000'
and jgfw.zn_dm = '4');
begin
open c_kt_ktsx;
LOOP
FETCH c_kt_ktsx INTO v_hsjg;
EXIT WHEN c_kt_ktsx%NOTFOUND;
db_zgxt.p_kt_ktsxcsh(v_hsjg,'20090301','26200000000');
end loop;
close c_kt_ktsx;
end;
错误信息
ORA-20010: 会统上线初始化失败!ORA-01861: literal does not match format string
ORA-06512: at "DB_ZGXT.P_KT_KTSXCSH", line 47
ORA-06512: at line 22
调用的那个过程是没有问题的,我也贴出来大家看
create or replace procedure db_zgxt.p_kt_ktsxcsh
/************************************************************************************
* 会统上线初始化
*
* 作者: 张大鹏
* 功能: 登记账薄
* 步骤:
* 1.初始化清单操作信息
* 2.初始化账簿操作信息
* 3.初始化结算操作信息
*
* 传入参数列表: as_hsjg 核算单位代码
* as_sxny 上线年月 如:200808表示08年8月上线
* as_czry 操作人员
* 返回值: null
*
************************************************************************************/
(as_hsjg in varchar2, as_sxny in varchar2, as_czry in varchar2) is
as_befor varchar2(20);
as_befor2 varchar2(20);
as_now varchar2(20);
begin
as_now := as_sxny || '01';
as_befor := to_char(to_date(as_now,'yyyy-mm-dd')-1,'yyyy-mm-dd');
as_befor2 := to_char(add_months(to_date(as_now,'yyyy-mm-dd'),-1),'yyyy-mm-dd');
insert into DB_KTHS.T_KT_KTCZXX
(HSJG_DM, KJZZL_DM, KTCZZL, GS_RQ, CZ_SJ, QS_SJ, ZZ_SJ, CZJG_ZT, CZJG_XX, CZRY_DM, LR_SJ, XG_SJ)
values
(as_hsjg, '1', '01', to_date(as_befor, 'yyyy-mm-dd'), to_date(as_now, 'yyyy-mm-dd'),
to_date(as_befor, 'yyyy-mm-dd'), to_date(as_befor||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss'), null, '清单成功!',
as_czry, null, null);
insert into DB_KTHS.T_KT_KTCZXX
(HSJG_DM, KJZZL_DM, KTCZZL, GS_RQ, CZ_SJ, QS_SJ, ZZ_SJ, CZJG_ZT, CZJG_XX, CZRY_DM, LR_SJ, XG_SJ)
values
(as_hsjg, '1', '02', to_date(as_befor, 'yyyy-mm-dd'), to_date(as_now, 'yyyy-mm-dd'),
to_date(as_befor, 'yyyy-mm-dd'), to_date(as_befor||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss'), null, '账簿成功!',
as_czry, null, null);
insert into DB_KTHS.T_KT_KTCZXX
(HSJG_DM, KJZZL_DM, KTCZZL, GS_RQ, CZ_SJ, QS_SJ, ZZ_SJ, CZJG_ZT, CZJG_XX, CZRY_DM, LR_SJ, XG_SJ)
values
(as_hsjg, '1', '03', to_date(as_befor2, 'yyyy-mm-dd'), to_date(as_now, 'yyyy-mm-dd'),
to_date(as_befor, 'yyyy-mm-dd'), to_date(as_befor||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss'), null, '月结成功!',
as_czry, null, null);
commit;
exception
when others then
raise_application_error(-20010, '会统上线初始化失败!' || sqlerrm);
end p_kt_ktsxcsh;
as_sxny 上线年月的格式是YYYYMM
as_now := as_sxny || '01'; YYYYMMDD
你to_date(as_now,'yyyy-mm-dd')的时候用的是YYYY-MM-DD的格式,肯定是不行的,所以会报ORA-01861: literal does not match format string的错,把所有日期格式改成YYYYMMDD吧!