存储过程动态sql变量赋值问题

Question

各位大佬，我在存储过程中定义了一个变量V_LASTDAY取传入时间的最后一天，然后我再PL/SQL分步调试的时候，给V_LASTDAY赋值之后再执行了V_SQL2之后，V_LASTDAY的值就变成了(Not a variable)，导致我后面用到V_LASTDAY的动态sql均无法取到值，请问应该如何解决？

Answer 1

V_CXSJ 有东西吗？

Answer 2

我觉得把那几个变量put出来 ，把拼接的字符串也put出来 看看更清晰

Answer 3

CREATE OR REPLACE PACKAGE BODY BI_GK_ZJFX is
  procedure BI_ZJ_ZJFB
  (
    V_CXSJ  VARCHAR2,
    Re_CURSOR out T_CURSOR
  )
  IS
    V_FLAG VARCHAR2(1);
    V_FLAG2 VARCHAR2(1);
    V_LASTDAY VARCHAR2(10);
    V_SQL VARCHAR2(500);
    V_SQL2 VARCHAR2(500);
    V_EXSQL1 VARCHAR2(500);
    V_EXSQL2 VARCHAR2(500);
    V_EXSQL3 VARCHAR2(500);
  BEGIN
    V_SQL:='CREATE TABLE BI_ZJ_ZJFB(
            PXH INT,
            XH  varchar2(10),
            FLBH VARCHAR2(10),
            ZJFL varchar2(100),
            HJ   NUMBER(20,8),
            JTBB  NUMBER(20,8),
            ZZYSYB  NUMBER(20,8),
            ZYGS  NUMBER(20,8),
            ZBGS NUMBER(20,8),
            TZGS   NUMBER(20,8),
            TGGS   NUMBER(20,8),
            HQGS   NUMBER(20,8)
            )';
    
    --根据查询日期返回最后一天的值到V_LASTDAY：调试为20171231
    SELECT TO_CHAR(LAST_DAY(TO_DATE(''||V_CXSJ||'','yyyymmdd')),'yyyymmdd') INTO V_LASTDAY FROM DUAL;
    
    --创建表2用到了V_LASTDAY,此时调试能获取到值并建表
    V_SQL2:='CREATE TABLE BI_ZJ_YTZHZ AS
      SELECT B.ZJZHYT_YTMC,B.ZJZHYT_YTBH,A.* ,C.ZJZH_ZHZ,D.ZJZHRYEB_DQYE
      FROM ZJYTGX A LEFT JOIN ZJZHYT B ON A.ZJYTGX_YTNM=B.ZJZHYT_YTNM
      LEFT JOIN ZJZH C ON A.ZJYTGX_ZHNM=C.ZJZH_ZHNM
      LEFT JOIN ZJZHRYEB D ON A.ZJYTGX_ZHNM=D.ZJZHRYEB_ZHNM AND ZJZHRYEB_RQ='''||V_LASTDAY||'''
      ORDER BY B.ZJZHYT_YTBH';
    
    --判断表是否存在的逻辑。。      
    SELECT COUNT(1) INTO V_FLAG FROM USER_TABLES WHERE TABLE_NAME='BI_ZJ_ZJFB';
    SELECT COUNT(1) INTO V_FLAG2 FROM USER_TABLES WHERE TABLE_NAME='BI_ZJ_YTZHZ';
    IF V_FLAG='0'  AND V_FLAG2='0' THEN
      EXECUTE IMMEDIATE V_SQL;
      EXECUTE IMMEDIATE V_SQL2;
    ELSIF V_FLAG='0' AND V_FLAG2='1' THEN
      EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_YTZHZ';
      EXECUTE IMMEDIATE V_SQL;
      EXECUTE IMMEDIATE V_SQL2;   
    ELSIF V_FLAG='1' AND V_FLAG2='0' THEN
      EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_ZJFB';
      EXECUTE IMMEDIATE V_SQL;
      EXECUTE IMMEDIATE V_SQL2;
    ELSE
      EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_YTZHZ';
      EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_ZJFB';
      EXECUTE IMMEDIATE V_SQL;
      EXECUTE IMMEDIATE V_SQL2;
    END IF;
    
    ---动态SQL更新表数据，调试到这里下面的变量调试的时候全部都变成(Not a variable)，加了dbms输出显示V_LASTDAY为空值？？
    V_EXSQL1:='UPDATE BI_ZJ_ZJFB SET ';  
    V_EXSQL2:='(SELECT SUM(ZJZHRYEB_DQYE) FROM ZJZHRYEB 
    WHERE ZJZHRYEB_ZHNM IN(SELECT ZJYTGX_ZHNM FROM BI_ZJ_YTZHZ WHERE ZJZHYT_YTBH=FLBH)
    AND ZJZHRYEB_RQ='''||V_LASTDAY||''')';
    V_SQL2:=V_EXSQL1||'JTBB='||V_EXSQL2;
    DBMS_OUTPUT.put_line(V_SQL2);
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'ZZYSYB='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'ZYGS='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'ZBGS='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'TZGS='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'TGGS='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'HQGS='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    
    OPEN RE_CURSOR FOR SELECT * FROM BI_ZJ_ZJFB;  END BI_ZJ_ZJFB;
end BI_GK_ZJFX;

Answer 4

完全参考你的写法，改了点点跟这个变量无关紧要的地方。不会出现你这个情况，这个变量一直都有值啊。如下：create or replace package BI_GK_ZJFX is  
procedure BI_ZJ_ZJFB(V_CXSJ  VARCHAR2);  
end BI_GK_ZJFX;  CREATE OR REPLACE PACKAGE BODY BI_GK_ZJFX is
  procedure BI_ZJ_ZJFB
  (
    V_CXSJ  VARCHAR2
  )
  IS
    V_FLAG VARCHAR2(1);
    V_FLAG2 VARCHAR2(1);
    V_LASTDAY VARCHAR2(10);
    V_SQL VARCHAR2(500);
    V_SQL2 VARCHAR2(500);
    V_EXSQL1 VARCHAR2(500);
    V_EXSQL2 VARCHAR2(500);
    V_EXSQL3 VARCHAR2(500);
  BEGIN
    V_SQL:='CREATE TABLE BI_ZJ_ZJFB(
            PXH INT,
            XH  varchar2(10),
            FLBH VARCHAR2(10),
            ZJFL varchar2(100),
            HJ   NUMBER(20,8),
            JTBB  NUMBER(20,8),
            ZZYSYB  NUMBER(20,8),
            ZYGS  NUMBER(20,8),
            ZBGS NUMBER(20,8),
            TZGS   NUMBER(20,8),
            TGGS   NUMBER(20,8),
            HQGS   NUMBER(20,8)
            )';    --根据查询日期返回最后一天的值到V_LASTDAY：调试为20171231
    SELECT TO_CHAR(LAST_DAY(TO_DATE(''||V_CXSJ||'','yyyymmdd')),'yyyymmdd') INTO V_LASTDAY FROM DUAL;
   dbms_output.put_line(V_LASTDAY);
    --创建表2用到了V_LASTDAY,此时调试能获取到值并建表
    V_SQL2:='CREATE TABLE BI_ZJ_YTZHZ AS
      select * from t_objects t where t.CREATED>to_date('''||V_LASTDAY||''',''YYYYMMDD'')';
    dbms_output.put_line(V_SQL2);
    dbms_output.put_line(V_LASTDAY);
 
   
    --判断表是否存在的逻辑。。
    SELECT COUNT(1) INTO V_FLAG FROM USER_TABLES WHERE TABLE_NAME='BI_ZJ_ZJFB';
    SELECT COUNT(1) INTO V_FLAG2 FROM USER_TABLES WHERE TABLE_NAME='BI_ZJ_YTZHZ';
    IF V_FLAG='0'  AND V_FLAG2='0' THEN
      EXECUTE IMMEDIATE V_SQL;
      EXECUTE IMMEDIATE V_SQL2;
    ELSIF V_FLAG='0' AND V_FLAG2='1' THEN
      EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_YTZHZ';
      EXECUTE IMMEDIATE V_SQL;
      EXECUTE IMMEDIATE V_SQL2;
    ELSIF V_FLAG='1' AND V_FLAG2='0' THEN
      EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_ZJFB';
      EXECUTE IMMEDIATE V_SQL;
      EXECUTE IMMEDIATE V_SQL2;
    ELSE
      EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_YTZHZ';
      EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_ZJFB';
      EXECUTE IMMEDIATE V_SQL;
      EXECUTE IMMEDIATE V_SQL2;
    END IF;
   dbms_output.put_line(V_LASTDAY);    ---动态SQL更新表数据，调试到这里下面的变量调试的时候全部都变成(Not a variable)，加了dbms输出显示V_LASTDAY为空值？？
    V_EXSQL1:='UPDATE BI_ZJ_ZJFB SET ';
    V_EXSQL2:='(select sum(t.object_name) from BI_ZJ_YTZHZ t where t.last_ddl_time>to_date('''||V_LASTDAY||''',''YYYYMMDD''))';
    dbms_output.put_line('V_EXSQL2:'||V_EXSQL2);    V_SQL2:=V_EXSQL1||'JTBB='||V_EXSQL2;
    DBMS_OUTPUT.put_line(V_SQL2);
    dbms_output.put_line('V_LASTDAY:'||V_LASTDAY);
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'ZZYSYB='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'ZYGS='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'ZBGS='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'TZGS='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'TGGS='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    V_SQL2:=V_EXSQL1||'HQGS='||V_EXSQL2;
    EXECUTE IMMEDIATE V_SQL2;
    dbms_output.put_line('V_LASTDAY:'||V_LASTDAY);
  END BI_ZJ_ZJFB;
end BI_GK_ZJFX;begin
BI_GK_ZJFX.BI_ZJ_ZJFB('20171225');
end;输出：
20171231
CREATE TABLE BI_ZJ_YTZHZ AS
      select * from t_objects t where t.CREATED>to_date('20171231','YYYYMMDD')
20171231
20171231
V_EXSQL2:(select sum(t.object_name) from BI_ZJ_YTZHZ t where t.last_ddl_time>to_date('20171231','YYYYMMDD'))
UPDATE BI_ZJ_ZJFB SET JTBB=(select sum(t.object_name) from BI_ZJ_YTZHZ t where t.last_ddl_time>to_date('20171231','YYYYMMDD'))
V_LASTDAY:20171231
V_LASTDAY:20171231