create or replace function fun(i_str varchar2)
return varchar2 isii varchar2(100);
li_a varchar2(1000);
li_b varchar2(100);
begin.....
li_b := case li_a -- 这个根据不同的参数选不同表(通过前面的运算得到li_a 这里就不详写了)
when '1' then t1 -- t1 t2 t3都是表名
when '2' then t2
when '3' then t3
...
end;for ii in (select * from li_b) loop....end loop;end;不知道大家是否看明白我的用意。我是想直接通过参数传递表名。不知道在function是是否可以这样实现。如果可以,请问如何操作呀?
return varchar2 isii varchar2(100);
li_a varchar2(1000);
li_b varchar2(100);
begin.....
li_b := case li_a -- 这个根据不同的参数选不同表(通过前面的运算得到li_a 这里就不详写了)
when '1' then t1 -- t1 t2 t3都是表名
when '2' then t2
when '3' then t3
...
end;for ii in (select * from li_b) loop....end loop;end;不知道大家是否看明白我的用意。我是想直接通过参数传递表名。不知道在function是是否可以这样实现。如果可以,请问如何操作呀?
解决方案 »
- 我电脑上安装了Oracle服务器,之前用的好好的,可是今天不能用了,我看了看服务,监听器没打开,我试了下,可是打不开,求解,俺穷人没什么分,对不住各位了
- pd 导出数据库表结构问题
- 如何把record table里的数据放到object table里
- 在线等 SQLplus 执行问题
- job执行时间过长导致下次执行时间推迟
- 菜鸟请教用select返回前10条数据的写法
- 一条sql语句,能否优化?
- 客户端登陆到Oracle Management Server时的错误!!!
- 请问ORACLE有自增数值型吗?
- 关于DBMS_CRYPTO.HASH计算BLOB字段MD5的问题
- Oracle 锁表
- 4个表连接问题。
PROCEDURE PRO_MXB_TJ(P_TYPE IN VARCHAR2,
P_GLDW_LEN IN NUMBER,
P_TJTW_LEN IN VARCHAR2,
P_TJTW IN VARCHAR2,
P_KSRQ IN DATE,
P_JSRQ IN DATE,
p_err OUT VARCHAR2) AS
V_CONTION VARCHAR2(2000);
BEGIN
BEGIN
V_CONTION := 'delete from GLO_TEMP_MXB';
EXECUTE IMMEDIATE V_CONTION;
COMMIT;
CASE P_TYPE
WHEN '2' THEN
V_CONTION :=
'INSERT INTO GLO_TEMP_MXB
(DWMC,TJFW,GXYCS,GXYSZ,TNBCS,TNBSZ,CJCS,CJSZ)
SELECT V.DWMC, GXY.*,TNB.TNBCS, TNB.TNBSZ, CJ.CJCS, CJ.CJSZ
FROM (SELECT SUBSTR(XQBH, 1, :1) TJFW,
SUM(DECODE(STATE, 1, 1, 0)) GXYCS,
SUM(DECODE(STATE, 2, 1, 0)) GXYSZ
FROM WV_MBTJ A
WHERE BSMC = ' || CHR(39) || '高血压' || CHR(39) || '
AND SUBSTR(XQBH, 1, :3) = :2
AND JDRQ BETWEEN :4 AND :5
GROUP BY SUBSTR(XQBH, 1, :1) ) GXY,
(SELECT SUBSTR(XQBH, 1, :1) TJFW,
SUM(DECODE(STATE, 1, 1, 0)) TNBCS,
SUM(DECODE(STATE, 2, 1, 0)) TNBSZ
FROM WV_MBTJ
WHERE BSMC = ' || CHR(39) || '糖尿病' || CHR(39) || '
AND SUBSTR(XQBH, 1, :3) = :2
AND JDRQ BETWEEN :4 AND :5
GROUP BY SUBSTR(XQBH, 1, :1)) TNB,
(SELECT SUBSTR(XQBH, 1, :1) TJFW,
SUM(DECODE(STATE, 1, 1, 0)) CJCS,
SUM(DECODE(STATE, 2, 1, 0)) CJSZ
FROM WV_MBTJ
WHERE BSMC = ' || CHR(39) || '残疾' || CHR(39) || '
AND SUBSTR(XQBH, 1, :3) = :2
AND JDRQ BETWEEN :4 AND :5
GROUP BY SUBSTR(XQBH, 1, :1)) CJ,
VIEW_DWXX V
WHERE GXY.TJFW = V.DWHM
AND GXY.TJFW = TNB.TJFW
AND GXY.TJFW = CJ.TJFW';
WHEN '1' THEN --服务站
V_CONTION :=
'INSERT INTO GLO_TEMP_MXB
SELECT V.DWMC, GXY.*,TNB.TNBCS, TNB.TNBSZ, CJ.CJCS, CJ.CJSZ
FROM (SELECT SUBSTR(SQBH||GLDW, 1, :1) TJFW,
A.GLDW,
SUM(DECODE(STATE, 1, 1, 0)) GXYCS,
SUM(DECODE(STATE, 2, 1, 0)) GXYSZ
FROM WV_MBTJ A
WHERE BSMC =' || CHR(39) || '高血压' || CHR(39) || '
AND SUBSTR(SQBH||GLDW, 1, :3) = :2
AND JDRQ BETWEEN :4 AND :5
GROUP BY SUBSTR(SQBH||GLDW, 1, :1), GLDW) GXY,
(SELECT SUBSTR(SQBH||GLDW, 1, :1) TJFW,
GLDW,
SUM(DECODE(STATE, 1, 1, 0)) TNBCS,
SUM(DECODE(STATE, 2, 1, 0)) TNBSZ
FROM WV_MBTJ
WHERE BSMC = ' || CHR(39) || '糖尿病' || CHR(39) || '
AND SUBSTR(SQBH||GLDW, 1, :3) = :2
AND JDRQ BETWEEN :4 AND :5
GROUP BY SUBSTR(SQBH||GLDW, 1, :1), GLDW) TNB, (SELECT SUBSTR(SQBH||GLDW, 1, :1) TJFW,
GLDW,
SUM(DECODE(STATE, 1, 1, 0)) CJCS,
SUM(DECODE(STATE, 2, 1, 0)) CJSZ
FROM WV_MBTJ
WHERE BSMC = ' || CHR(39) || '残疾' || CHR(39) || '
AND SUBSTR(SQBH||GLDW, 1, :3) = :2
AND JDRQ BETWEEN :4 AND :5
GROUP BY SUBSTR(SQBH||GLDW, 1, :1), GLDW) CJ,
VIEW_DWXX V
WHERE GXY.TJFW = V.DWBH
AND GXY.TJFW = TNB.TJFW
AND GXY.TJFW = CJ.TJFW';
END CASE;
EXECUTE IMMEDIATE V_CONTION
USING P_GLDW_LEN, P_TJTW_LEN, P_TJTW, P_KSRQ, P_JSRQ, P_GLDW_LEN,
P_GLDW_LEN, P_TJTW_LEN, P_TJTW, P_KSRQ, P_JSRQ, P_GLDW_LEN,
P_GLDW_LEN, P_TJTW_LEN, P_TJTW, P_KSRQ, P_JSRQ, P_GLDW_LEN;
COMMIT;
END;
EXCEPTION
WHEN OTHERS THEN
p_err := SQLERRM;
END PRO_MXB_TJ;
declare
type qq is ref cursor;
aa qq;
tb varchar2(20):='qc_pivot';
ss qc_pivot%rowtype;
begin
open aa for 'select * from '||tb;
loop
fetch aa into ss;
exit when aa%notfound;
dbms_output.put_line(ss.fee);
end loop;
close aa;
end;
/