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

[lvr295]

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

[卖水果的net]

V_CXSJ 有东西吗？

[liu志坚]

完全参考你的写法，改了点点跟这个变量无关紧要的地方。不会出现你这个情况，这个变量一直都有值啊。如下： 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

[lvr295]

引用 1 楼 wmxcn2000 的回复:

V_CXSJ 有东西吗？

有的，一个指定的查询日期。

[liu志坚]

完整的存储过程截图出来，方便分析哦~~。

[学海无涯-回头是岸]

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

[lvr295]

引用 3 楼 liuzhijian2008x 的回复:

完整的存储过程截图出来，方便分析哦~~。

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;