各位大佬，我在存盤程序中定義了一個變數V_LASTDAY取傳入時間的最后一天，然后我再PL/SQL分步除錯的時候，給V_LASTDAY賦值之后再執行了V_SQL2之后，V_LASTDAY的值就變成了(Not a variable)，導致我后面用到V_LASTDAY的動態sql均無法取到值，請問應該如何解決？
uj5u.com熱心網友回復：
V_CXSJ 有東西嗎？uj5u.com熱心網友回復：
有的，一個指定的查詢日期。uj5u.com熱心網友回復：
完整的存盤程序截圖出來，方便分析哦~~。uj5u.com熱心網友回復：
我覺得把那幾個變數put出來 ，把拼接的字串也put出來 看看更清晰uj5u.com熱心網友回復：
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;
uj5u.com熱心網友回復：
完全參考你的寫法，改了點點跟這個變數無關緊要的地方。不會出現你這個情況，這個變數一直都有值啊。如下：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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/73356.html
標籤：開發
上一篇：為什么system創建的表system自己看得到，普通用戶授權了也看不到？
下一篇：怎么取消用戶的創建視圖的權限