Dear bosses, I define a variable in the process of storage V_LASTDAY take the last day of the incoming time, then I PL/SQL debugging, step by step to V_LASTDAY assignment before execution after V_SQL2 V_LASTDAY value becomes (Not a variable), or the cause behind me use V_LASTDAY dynamic SQL to value, could you tell me how should solve?

CodePudding user response:

V_CXSJ something?

CodePudding user response:

reference 1st floor wmxcn2000 response:

V_CXSJ something?

yes, the date of a specified query,

CodePudding user response:

Complete screenshots of the stored procedure, convenient analysis oh ~ ~,

CodePudding user response:

I feel put out the several variables, the joining together of the string is put out to see more clearly

CodePudding user response:

reference liuzhijian2008x reply: 3/f

full screenshots of the stored procedure, convenient analysis oh ~ ~,

The 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);
The 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)
) ';

- according to the query returns the value of the last day of the date to V_LASTDAY: debugging of 20171231
The SELECT TO_CHAR (LAST_DAY (TO_DATE (' | | V_CXSJ | | ', 'yyyymmdd')), 'yyyymmdd') INTO V_LASTDAY FROM DUAL;

- used to create table 2 V_LASTDAY, debugging can access to the value attribute of table
V_SQL2:='CREATE TABLE BI_ZJ_YTZHZ AS
The SELECT B.Z JZHYT_YTMC, B.Z JZHYT_YTBH, a. *, C.Z JZH_ZHZ, D.Z JZHRYEB_DQYE
The FROM ZJYTGX A LEFT JOIN ZJZHYT B ON A.Z JYTGX_YTNM=B.Z JZHYT_YTNM
LEFT the JOIN ZJZH C ON A.Z JYTGX_ZHNM=C.Z JZH_ZHNM
LEFT the JOIN ZJZHRYEB D ON A.Z JYTGX_ZHNM=D.Z JZHRYEB_ZHNM AND ZJZHRYEB_RQ=' ' '| | V_LASTDAY | |' "'
The ORDER BY B.Z JZHYT_YTBH ';

Judge whether there is logic,,
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
The EXECUTE IMMEDIATE V_SQL;
The EXECUTE IMMEDIATE V_SQL2;
ELSIF V_FLAG='0' AND V_FLAG2='1' THEN
The EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_YTZHZ';
The EXECUTE IMMEDIATE V_SQL;
The EXECUTE IMMEDIATE V_SQL2;
ELSIF V_FLAG='1' AND V_FLAG2='0' THEN
The EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_ZJFB';
The EXECUTE IMMEDIATE V_SQL;
The EXECUTE IMMEDIATE V_SQL2;
The ELSE
The EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_YTZHZ';
The EXECUTE IMMEDIATE 'DROP TABLE BI_ZJ_ZJFB';
The EXECUTE IMMEDIATE V_SQL;
The EXECUTE IMMEDIATE V_SQL2;
END IF;

Update the data in the table - dynamic SQL, debugging to debug the following variables when all become (Not a variable), and the DBMS output display V_LASTDAY null values??
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);
The EXECUTE IMMEDIATE V_SQL2;
V_SQL2:=V_EXSQL1 | | 'ZZYSYB=' | | V_EXSQL2;
The EXECUTE IMMEDIATE V_SQL2;
V_SQL2:=V_EXSQL1 | | 'ZYGS=' | | V_EXSQL2;
The EXECUTE IMMEDIATE V_SQL2;
V_SQL2:=V_EXSQL1 | | 'ZBGS=' | | V_EXSQL2;
The EXECUTE IMMEDIATE V_SQL2;
V_SQL2:=V_EXSQL1 | | 'TZGS=' | | V_EXSQL2;
The EXECUTE IMMEDIATE V_SQL2;
V_SQL2:=V_EXSQL1 | | 'TGGS=' | | V_EXSQL2;
The EXECUTE IMMEDIATE V_SQL2;
V_SQL2:=V_EXSQL1 | | 'HQGS=' | | V_EXSQL2;
The EXECUTE IMMEDIATE V_SQL2;

The OPEN RE_CURSOR FOR SELECT * FROM BI_ZJ_ZJFB;

END BI_ZJ_ZJFB;
End BI_GK_ZJFX;

CodePudding user response:

Completely reference you writing, changed little with the variable doesn't matter, you this will not happen, this variable has value, as follows:

The create or replace package BI_GK_ZJFX is
Procedure BI_ZJ_ZJFB (V_CXSJ VARCHAR2);
End BI_GK_ZJFX;

The 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);
The 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)
) ';

- according to the query returns the value of the last day of the date to V_LASTDAY: debugging of 20171231
The SELECT TO_CHAR (LAST_DAY (TO_DATE (' | | V_CXSJ | | ', 'yyyymmdd')), 'yyyymmdd') INTO V_LASTDAY FROM DUAL;
Dbms_output. Put_line (V_LASTDAY);
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull