无法在PL/SQL过程中使用PL/SQL表名作为变量? [英] Unable to use PL/SQL table name as a variable in PL/SQL procedure?

问题描述

我无法在PL/SQL过程中使用表名作为变量，这里的表每个月都在变化，在我的代码中，我尝试使用的两个表名变量分别来自' || v_PRM_TABLE1 || '和from ' || v_PRM_TABLE2 || '这个问题中提到的语法，但对我不起作用

I'm unable to use table name as a variable in my PL/SQL procedure, the table here change every month, in my code, there are two table name variable from ' || v_PRM_TABLE1 || ' and from ' || v_PRM_TABLE2 || ' I tried to use the syntax that was mentioned in this question but not working for me

Oracle SQL:用于代替表名的变量

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
--FIRST DAY YEAR
v_SG_FYEAR VARCHAR(50);
--FIRST DAY MONTH
v_SG_FMNTH VARCHAR(50);
--FIRST DAY START DAY
v_SG_FDAY VARCHAR(50);

--LAST DAY MONTH
v_SG_LYEAR VARCHAR(50);
--LAST DAY MONTH
v_SG_LMNTH VARCHAR(50);
--LAST DAY START DAY
v_SG_LDAY VARCHAR(50);

v_JOBID number(20);
v_PRM_TABLE1 varchar(200);
v_PRM_TABLE2 varchar(200);
BEGIN
/*Getting last JOB_ID*/
SELECT MAX(JOB_ID) INTO v_JOBID from ON_SGSN_TAPOUT;
case when v_JOBID IS NULL then v_JOBID:=1;
 when v_JOBID IS NOT NULL THEN 
      SELECT MAX(JOB_ID)+1 INTO v_JOBID FROM ON_SGSN_TAPOUT;
 END CASE;

v_SG_FYEAR:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYY');
v_SG_FMNTH:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'MM');
v_SG_FDAY:='01';

v_SG_LYEAR:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYY');
v_SG_LMNTH:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'MM');
v_SG_LDAY:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'DD');

v_PRM_TABLE1:='prmdb.CDR_TAPOUT_'||v_SG_FYEAR||v_SG_FMNTH||'@prmdb';
v_PRM_TABLE2:='prmdb.CDR_TAPOUT_'||TO_CHAR(SYSDATE,'YYYYMM')||'@prmdb';

FOR REC IN (
        select * from(
        select * from (
        SELECT TO_CHAR(START_TIME,'YYYYMMDD') AS "SGSN_DATE",
        SUM(SUM_UP_DOWN/1024/1024) SGSN_MB
        FROM OPS$DEBIT.FACT_HUA_SGSN_INBOUND@RADB 
        WHERE START_TIME BETWEEN TO_DATE(v_SG_FMNTH||'/'||v_SG_FDAY||'/'||v_SG_FYEAR||' 
'||'00:00:00','mm/dd/yyyy hh24:mi:ss')
        AND  TO_DATE(v_SG_LMNTH||'/'||v_SG_LDAY||'/'||v_SG_LYEAR||' '||'23:59:59','mm/dd/yyyy hh24:mi:ss')
        GROUP BY TO_CHAR(START_TIME,'YYYYMMDD')) A
        left outer join 
        (
        select PRM_MNTH,PRM_ACT_MB,PRM_RND_MB,PRM_CHRG_USD from(
        select PRM_MNTH,SUM(PRM_ACT_MB) PRM_ACT_MB,SUM(PRM_RND_MB) PRM_RND_MB,SUM(PRM_CHRG_USD) PRM_CHRG_USD  from(
        select substr(START_TIME,1,8) PRM_MNTH,sum(TOTAL_BYTE)/1024/1024 PRM_ACT_MB, sum(charged_units)/1024/1024 PRM_RND_MB, sum(tap_fee_usd) PRM_CHRG_USD
        from ' || v_PRM_TABLE1 || '
        where start_time between v_SG_FYEAR||v_SG_FMNTH||'01000000' and v_SG_LYEAR||v_SG_LMNTH||v_SG_LDAY||'235959' 
        and CDR_TYPE in ('GPRSO')
        group by substr(START_TIME,1,8)
        UNION ALL
        select substr(START_TIME,1,8) PRM_MNTH,sum(TOTAL_BYTE)/1024/1024 PRM_ACT_MB, sum(charged_units)/1024/1024 PRM_RND_MB, sum(tap_fee_usd) PRM_CHRG_USD
        from ' || v_PRM_TABLE2 || '
        where start_time between v_SG_FYEAR||v_SG_FMNTH||'01000000' and v_SG_LYEAR||v_SG_LMNTH||v_SG_LDAY||'235959' 
        and CDR_TYPE in ('GPRSO')
        group by substr(START_TIME,1,8))
        group by
        PRM_MNTH)
            ) B
        ON A.SGSN_DATE=B.PRM_MNTH)
        )
        LOOP
        INSERT INTO ON_SGSN_TAPOUT values(v_JOBID,REC.SGSN_DATE,REC.SGSN_MB,REC.PRM_MNTH,REC.PRM_ACT_MB,REC.PRM_RND_MB,REC.PRM_CHRG_USD,REC.PRM_ACT_MB-REC.SGSN_MB,SYSDATE);
        commit;
        END LOOP;  
END;

推荐答案

我不使用EXECUTE IMMEDIATE即可解决此问题，我不知道此解决方案是否考虑了动态SQL，最困难的部分是将这个复杂的大型查询串联起来字符串变量.

I solve it without using EXECUTE IMMEDIATE, I don't know if this solution considered dynamic SQL or not, the hardest part was concatenating this big complex query into string variable.

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
--FIRST DAY YEAR
v_SG_FYEAR VARCHAR(50);
--FIRST DAY MONTH
v_SG_FMNTH VARCHAR(50);
--FIRST DAY START DAY
v_SG_FDAY VARCHAR(50);

--LAST DAY MONTH
v_SG_LYEAR VARCHAR(50);
--LAST DAY MONTH
v_SG_LMNTH VARCHAR(50);
--LAST DAY START DAY
v_SG_LDAY VARCHAR(50);

v_JOBID number(20);
v_PRM_TABLE1 varchar(200);
v_PRM_TABLE2 varchar(200);
--craeting a data type
type t_rec is record(SGSN_DATE VARCHAR2(200),
                 SGSN_MB VARCHAR2(200),
                 PRM_MNTH VARCHAR2(200),
                 PRM_ACT_MB VARCHAR2(200),
                 PRM_RND_MB VARCHAR2(200),
                 PRM_CHRG_USD VARCHAR2(200));
v_REC t_rec;
C1 sys_refcursor;
v_sql varchar2(32767);
BEGIN
/*Getting last JOB_ID*/
SELECT MAX(JOB_ID) INTO v_JOBID from ON_SGSN_TAPOUT;
case when v_JOBID IS NULL then v_JOBID:=1;
 when v_JOBID IS NOT NULL THEN 
      SELECT MAX(JOB_ID)+1 INTO v_JOBID FROM ON_SGSN_TAPOUT;
 END CASE;
v_SG_FYEAR:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYY');
v_SG_FMNTH:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'MM');
v_SG_FDAY:='01';
v_SG_LYEAR:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYY');
v_SG_LMNTH:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'MM');
v_SG_LDAY:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'DD');
v_PRM_TABLE1:='prmdb.CDR_TAPOUT_'||v_SG_FYEAR||v_SG_FMNTH||'@prmdb';
v_PRM_TABLE2:='prmdb.CDR_TAPOUT_'||TO_CHAR(SYSDATE,'YYYYMM')||'@prmdb';

/*putting the SQL query into a variable*/
v_sql:='select * from('||
   'select * from ('||
   'SELECT TO_CHAR(START_TIME,''YYYYMMDD'') AS "SGSN_DATE",'||
   'SUM(SUM_UP_DOWN/1024/1024) SGSN_MB'||
   ' FROM OPS$DEBIT.FACT_HUA_SGSN_INBOUND@RADB'|| 
   ' WHERE START_TIME BETWEEN 
TO_DATE('''||v_SG_FMNTH||'/'||v_SG_FDAY||'/'||v_SG_FYEAR||' 
'||'00:00:00'||''','||'''mm/dd/yyyy hh24:mi:ss'||''')'||
' AND  TO_DATE('''||v_SG_LMNTH||'/'||v_SG_LDAY||'/'||v_SG_LYEAR||' 
'||'23:59:59'||''','||'''mm/dd/yyyy hh24:mi:ss'||''')'||
' GROUP BY TO_CHAR(START_TIME,''YYYYMMDD'')) A '||
'left outer join '|| 
'('||
'select PRM_MNTH,PRM_ACT_MB,PRM_RND_MB,PRM_CHRG_USD from ('||
'select PRM_MNTH,SUM(PRM_ACT_MB) PRM_ACT_MB,SUM(PRM_RND_MB) 
 PRM_RND_MB,SUM(PRM_CHRG_USD) PRM_CHRG_USD  from ('||
 'select substr(START_TIME,1,8) PRM_MNTH,sum(TOTAL_BYTE)/1024/1024 PRM_ACT_MB, 
 sum(charged_units)/1024/1024 PRM_RND_MB, sum(tap_fee_usd) PRM_CHRG_USD'||
 ' from '|| v_PRM_TABLE1 ||
 ' where start_time between '''||v_SG_FYEAR||v_SG_FMNTH||v_SG_FDAY||'000000'' and 
 '''||v_SG_LYEAR||v_SG_LMNTH||v_SG_LDAY||'235959'''|| 
 ' and CDR_TYPE in (''GPRSO'') '||
 ' group by substr(START_TIME,1,8) '||
 ' UNION ALL '||
 ' select substr(START_TIME,1,8) PRM_MNTH,sum(TOTAL_BYTE)/1024/1024 PRM_ACT_MB, 
 sum(charged_units)/1024/1024 PRM_RND_MB, sum(tap_fee_usd) PRM_CHRG_USD '||
 ' from '||v_PRM_TABLE2||
 ' where start_time between '''||v_SG_FYEAR||v_SG_FMNTH||v_SG_FDAY||'000000'' and 
 '''||v_SG_LYEAR||v_SG_LMNTH||v_SG_LDAY||'235959'''|| 
 ' and CDR_TYPE in (''GPRSO'')'||
 ' group by substr(START_TIME,1,8))'||
 ' group by'||
 ' PRM_MNTH)'||
 ') B'||
 ' ON A.SGSN_DATE=B.PRM_MNTH)';  
   OPEN C1 FOR v_sql;
   LOOP
   FETCH C1 INTO v_REC;
   EXIT WHEN C1%NOTFOUND;
   dbms_output.put_line(v_REC.SGSN_DATE||' '||v_REC.SGSN_MB||' '||v_REC.PRM_MNTH||' 
   '||v_REC.PRM_ACT_MB||' '||v_REC.PRM_RND_MB||' '||v_REC.PRM_CHRG_USD||' '||SYSDATE);
   END LOOP;
 END;
 /

这篇关于无法在PL/SQL过程中使用PL/SQL表名作为变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！