一个存储过程，百思不解啊

[JAVA3VB]

create or replace procedure test(lrqqq in varchar2, lrqqz in varchar2, l_gjjgdm in varchar2 )
as
begin

INSERT INTO sb_jks
SELECT to_number(zj.jk_xh || zj.jklsmxh_xh_char) xtspxh,
to_number(yzmx.pz_xh || yzmx.yzmx_xh_char) zsxh,
b.swdjzh nsrdzdah,
null,
yzmx.sfssq_qsrq sssq_q,
yzmx.sfssq_zzrq sssq_z,
yzmx.zsxm_dm,
yzmx.zspm_dm,
yzmx.sksx1_dm skzl_dm,
yzmx.sksx2_dm sksx_dm,
yzpz.sbfs_dm sbfs_dm,
yzmx.pz_xh yzpzxh,
yzmx.yzfs_rq yzfsrq,
0 kssl,
yzmx.js_yj xssr,
yzmx.sl,
0 dwse,
yzmx.yzsf_je sjse,
' ' spzl_dm,
pz.kpry_dm kpr_dm,
pz.tf_sj kprq,
'0' xtsphm,
pz.pzhm yssphm,
b.zclx_dm djzclx_dm,
b.hy_dm,
' ' yskm_dm,
' ' ysfpbl_dm,
'' ZSFS_DM,
zj.skzhid SKGK_DM,
'' YHZL_DM,
'' YH_DM,
'' YHZH,
to_date('1900-01-01','yyyy-mm-dd') spjkqx,
' ' hzjksbz,
'' BZ1,
'' BZ2,
zj.jkfs_dm,
'' YSJYH,
'' YSCZZT_DM,
'' YSBL_YJFSRQ,
'' YSBL_FSRQ,
'' BLCGRQ,
'' ZZZYWSZ_DYRQ,
'' ZZZYWSZ_DYR_DM,
'' RKRQ,
yzmx.tzlx_dm,
'' TZRQ,
'' ZFR_DM,
pz.zf_rq zfrq,
'' SPZFLX_DM,
'' JDXZ_DM,
'' ZGSWRY_DM,
' ' sk_ssswjg_dm,
' ' zsjg_dm,
b.gljg_dm nsr_swjg_dm,
g.swjg_dm swjg_dm,
yzmx.lrry_dm lrr_dm,
yzmx.lr_sj lrrq,
yzmx.xgry_dm xgr_dm,
yzmx.xg_sj xgrq
FROM (SELECT a.pz_xh,
a.yzmx_xh,
CASE
WHEN a.yzmx_xh < 10 THEN
'0' || a.yzmx_xh
ELSE
'' || a.yzmx_xh
END AS yzmx_xh_char,
a.nsrnbm,
a.sfssq_qsrq,
a.sfssq_zzrq,
a.zsxm_dm,
a.zspm_dm,
a.sksx1_dm,
a.sksx2_dm,
a.dzsx_dm,
a.tzlx_dm,
a.js_yj,
a.sl,
a.yzsf_je,
a.yzfs_rq,
a.jk_qx,
a.zsfs_dm,
a.zsjg_dm,
a.hsjg_dm,
a.lr_sj,
a.lrry_dm,
a.xg_sj,
a.xgry_dm
FROM t_zs_yzmx@dblink_ctais2 a
WHERE a.lr_sj >= to_date(lrqqq,'yyyy-mm-dd')
AND a.lr_sj < to_date(lrqqz,'yyyy-mm-dd')
AND a.gljg_dm = l_gjjgdm) yzmx,
(SELECT f.pz_xh, f.pzzl_dm, f.sbfs_dm
FROM t_sb_yzpz@dblink_ctais2 f
WHERE f.lr_sj >= to_date(lrqqq,'yyyy-mm-dd')
AND f.lr_sj < to_date(lrqqz,'yyyy-mm-dd')
AND f.gljg_dm = l_gjjgdm) yzpz,
(SELECT e.dzsph_xh,
e.pzzl_dm,
e.tf_sj,
e.pzhm,
e.zf_rq,
e.kpry_dm
FROM t_zs_pzsyqk@dblink_ctais2 e
WHERE e.lr_sj >= to_date(lrqqq,'yyyy-mm-dd')
AND e.lr_sj < to_date(lrqqz,'yyyy-mm-dd')
AND e.zf_rq is null
and e.zfr_dm is null) pz,
(SELECT c.pz_xh,
c.yzmx_xh,
c.jk_xh,
c.jklsmxh_xh,
CASE
WHEN c.jklsmxh_xh < 10 THEN
'0' || c.jklsmxh_xh
ELSE
'' || c.jklsmxh_xh
END AS jklsmxh_xh_char,
c.kj_rq,
'' hzjksh,
c.rk_rq,
c.xgry_dm,
c.rk_rq,
c.jkfs_dm,
c.yskm_dm,
c.ysfpbl_dm,
c.skzhid
FROM t_zs_zjjkmx@dblink_ctais2 c
WHERE c.lr_sj >= to_date(lrqqq,'yyyy-mm-dd')
AND c.lr_sj < to_date(lrqqz,'yyyy-mm-dd')) zj,
t_dj_jgnsr@dblink_ctais2 b,
t_dm_gy_swry@dblink_ctais2 g,
t_zs_pzmx@dblink_ctais2 d
WHERE yzmx.pz_xh = yzpz.pz_xh
AND yzmx.pz_xh = zj.pz_xh(+)
AND yzmx.yzmx_xh = zj.yzmx_xh(+)
AND zj.jk_xh = d.jk_xh
AND zj.jklsmxh_xh = d.jklsmxh_xh
AND d.dzsph_xh = pz.dzsph_xh
AND yzmx.nsrnbm = b.nsrnbm
AND b.zgy = g.swry_dm;
end ;
各位大侠，这是一个简单的存储过程，目的是从另一个库里，按需要抽取一些数据，如果单独执行插入操作是可成功插入的，但放到存储过程里，这个插入操作就会死掉，可执行的例子如下：

[JAVA3VB]

INSERT INTO sb_jks
SELECT to_number(zj.jk_xh || zj.jklsmxh_xh_char) xtspxh,
to_number(yzmx.pz_xh || yzmx.yzmx_xh_char) zsxh,
b.swdjzh nsrdzdah,
null,
yzmx.sfssq_qsrq sssq_q,
yzmx.sfssq_zzrq sssq_z,
yzmx.zsxm_dm,
yzmx.zspm_dm,
yzmx.sksx1_dm skzl_dm,
yzmx.sksx2_dm sksx_dm,
yzpz.sbfs_dm sbfs_dm,
yzmx.pz_xh yzpzxh,
yzmx.yzfs_rq yzfsrq,
0 kssl,
yzmx.js_yj xssr,
yzmx.sl,
0 dwse,
yzmx.yzsf_je sjse,
' ' spzl_dm,
pz.kpry_dm kpr_dm,
pz.tf_sj kprq,
'0' xtsphm,
pz.pzhm yssphm,
b.zclx_dm djzclx_dm,
b.hy_dm,
' ' yskm_dm,
' ' ysfpbl_dm,
'' ZSFS_DM,
zj.skzhid SKGK_DM,
'' YHZL_DM,
'' YH_DM,
'' YHZH,
to_date('1900-01-01','yyyy-mm-dd') spjkqx,
' ' hzjksbz,
'' BZ1,
'' BZ2,
zj.jkfs_dm,
'' YSJYH,
'' YSCZZT_DM,
'' YSBL_YJFSRQ,
'' YSBL_FSRQ,
'' BLCGRQ,
'' ZZZYWSZ_DYRQ,
'' ZZZYWSZ_DYR_DM,
'' RKRQ,
yzmx.tzlx_dm,
'' TZRQ,
'' ZFR_DM,
pz.zf_rq zfrq,
'' SPZFLX_DM,
'' JDXZ_DM,
'' ZGSWRY_DM,
' ' sk_ssswjg_dm,
' ' zsjg_dm,
b.gljg_dm nsr_swjg_dm,
g.swjg_dm swjg_dm,
yzmx.lrry_dm lrr_dm,
yzmx.lr_sj lrrq,
yzmx.xgry_dm xgr_dm,
yzmx.xg_sj xgrq
FROM (SELECT a.pz_xh,
a.yzmx_xh,
CASE
WHEN a.yzmx_xh < 10 THEN
'0' || a.yzmx_xh
ELSE
'' || a.yzmx_xh
END AS yzmx_xh_char,
a.nsrnbm,
a.sfssq_qsrq,
a.sfssq_zzrq,
a.zsxm_dm,
a.zspm_dm,
a.sksx1_dm,
a.sksx2_dm,
a.dzsx_dm,
a.tzlx_dm,
a.js_yj,
a.sl,
a.yzsf_je,
a.yzfs_rq,
a.jk_qx,
a.zsfs_dm,
a.zsjg_dm,
a.hsjg_dm,
a.lr_sj,
a.lrry_dm,
a.xg_sj,
a.xgry_dm
FROM t_zs_yzmx@dblink_ctais2 a
WHERE a.lr_sj >= to_date('2008-04-02','yyyy-mm-dd')
AND a.lr_sj < to_date('2008-04-03','yyyy-mm-dd')
AND a.gljg_dm = '15490000300') yzmx,
(SELECT f.pz_xh, f.pzzl_dm, f.sbfs_dm
FROM t_sb_yzpz@dblink_ctais2 f
WHERE f.lr_sj >= to_date('2008-04-02','yyyy-mm-dd')
AND f.lr_sj < to_date('2008-04-03','yyyy-mm-dd')
AND f.gljg_dm = '15490000300') yzpz,
(SELECT e.dzsph_xh,
e.pzzl_dm,
e.tf_sj,
e.pzhm,
e.zf_rq,
e.kpry_dm
FROM t_zs_pzsyqk@dblink_ctais2 e
WHERE e.lr_sj >= to_date('2008-04-02','yyyy-mm-dd')
AND e.lr_sj < to_date('2008-04-03','yyyy-mm-dd')
AND e.zf_rq is null
and e.zfr_dm is null) pz,
(SELECT c.pz_xh,
c.yzmx_xh,
c.jk_xh,
c.jklsmxh_xh,
CASE
WHEN c.jklsmxh_xh < 10 THEN
'0' || c.jklsmxh_xh
ELSE
'' || c.jklsmxh_xh
END AS jklsmxh_xh_char,
c.kj_rq,
'' hzjksh,
c.rk_rq,
c.xgry_dm,
c.rk_rq,
c.jkfs_dm,
c.yskm_dm,
c.ysfpbl_dm,
c.skzhid
FROM t_zs_zjjkmx@dblink_ctais2 c
WHERE c.lr_sj >= to_date('2008-04-02','yyyy-mm-dd')
AND c.lr_sj < to_date('2008-04-03','yyyy-mm-dd')) zj,
t_dj_jgnsr@dblink_ctais2 b,
t_dm_gy_swry@dblink_ctais2 g,
t_zs_pzmx@dblink_ctais2 d
WHERE yzmx.pz_xh = yzpz.pz_xh
AND yzmx.pz_xh = zj.pz_xh(+)
AND yzmx.yzmx_xh = zj.yzmx_xh(+)
AND zj.jk_xh = d.jk_xh
AND zj.jklsmxh_xh = d.jklsmxh_xh
AND d.dzsph_xh = pz.dzsph_xh
AND yzmx.nsrnbm = b.nsrnbm
AND b.zgy = g.swry_dm;

另外，我觉得这个查询操作，效率也是慢的可以了，希望高手给看看。

[yongletianya]

好长啊，可望不可即啊

[vber1010]

这种问题我也遇到过，当时没有解决，今天又看到类似的问题，查了下资料。可能的原因是，两个sql走的执行计划是不一样的，你将两个查询语句加上hint提示，都选择cbo或者rbo的优化器试试是不是一样的效率。