CREATE OR REPLACE PROCEDURE SP_HSA_Get_Income0421_TEST (
DATA1 OUT SYS_REFCURSOR) IS 
The BEGIN 

DECLARE V_STR VARCHAR2 (4000); 
TABLECOUNT NUMBER; 
TEMP_COUNT NUMBER; 
V_TEMP_TABLENAME_01 VARCHAR (20); 
V_TEMP_TABLENAME_02 VARCHAR (20); 
V_STR_CLOB clob. 
V_STR_01 VARCHAR2 (4000); 

The BEGIN 
V_TEMP_TABLENAME_01:='CT_' | | SUBSTR (SYS_GUID (), 1, 8). 

SELECT COUNT (1) INTO TABLECOUNT FROM USER_TABLES WHERE TABLE_NAME=V_TEMP_TABLENAME_01; 

IF TABLECOUNT> 0 THEN 
V_STR:='TRUNCATE TABLE' | | V_TEMP_TABLENAME_01; 
The EXECUTE IMMEDIATE V_STR; 
V_STR:='DROP TABLE' | | V_TEMP_TABLENAME_01; 
The EXECUTE IMMEDIATE V_STR; 
END the IF; 
The EXECUTE IMMEDIATE '
The CREATE GLOBAL TEMPORARY TABLE '| | V_TEMP_TABLENAME_01 | |' ON the COMMIT PRESERVE ROWS AS 
SELECT 1 AS AA, 2 AS BB FROM DUAL '; 

V_STR_01:='SELECT * FROM' | | V_TEMP_TABLENAME_01; 

The OPEN DATA1 FOR V_STR_01; 

SELECT COUNT (1) INTO TABLECOUNT FROM USER_TABLES WHERE TABLE_NAME=V_TEMP_TABLENAME_01; 
IF TABLECOUNT> 0 THEN 
V_STR:='TRUNCATE TABLE' | | V_TEMP_TABLENAME_01; 
The EXECUTE IMMEDIATE V_STR; 
V_STR:='DROP TABLE' | | V_TEMP_TABLENAME_01; 
The EXECUTE IMMEDIATE V_STR; 
END the IF; 

END; 
END SP_HSA_Get_Income0421_TEST; 

DATA1 can not get data, warrior please help, see if they write wrong, or the temporary table is PLSQL by has a problem!

CodePudding user response:

Session the temporary table, after the end of the session will automatically clean up data, do not need to program control truncate,
When you use the cursor to the examination of the semantic, found the table object does not exist, would be an error,

CodePudding user response:

I now the wording, DATA1 get less than the corresponding values, this is what reason!

CodePudding user response:

A warrior provide train of thought

CodePudding user response:

To add a process used to drop the temporary table, you will now process the table name used as the temporary table, after using the cursor data, and then drop off process

CodePudding user response:

Because you delete this table, the cursor to get the data, is you turn on when he will get the data, and views about the same, will not save the data, the fault is can't find the table

CodePudding user response:

SYS_REFCURSOR type of cursor belongs to the weak type, in the OPEN, to carry out the actual statement, that is to do a grammar check,

Behind you deleted references, certainly won't find it, and at a table does not exist error,



In fact, the use of temporary table standards should be like this: to build a temporary table in the database first, rather than to dynamic to create it in the storage,


Suggestion of the 1 # and 5 # reply,

CodePudding user response:

refer to 6th floor selling fruit net reply:

SYS_REFCURSOR type of cursor belongs to the weak type, in the OPEN, to carry out the actual statement, that is to do a grammar check,

Behind you deleted references, certainly won't find it, and at a table does not exist error,



In fact, the use of temporary table standards should be like this: to build a temporary table in the database first, rather than to dynamic to create it in the storage,


Suggestion of the 1 # and 5 # reply,

According to your way, will not lead to the stored procedure is invoked, at the same time data will be disordered! Lead to remove data has a problem?
# TEMP this temporary table in SQL SERVER, after each use is automatically deleted!

CodePudding user response:

refer to 7th floor mugua604 response:

Quote: refer to the sixth floor selling fruit net reply:

According to your way, will not lead to the stored procedure is invoked, at the same time data will be disordered! Lead to remove data has a problem?
# TEMP this temporary table in SQL SERVER, after each use is automatically deleted!

Won't, oracle's temporary tables, each session data is isolated, namely the session 1 also find no S2 data anyway, is the way of dynamically created and will influence each other in concurrent,


PS: Oracle and SQL Server on the use of temporary table difference is very big still,

CodePudding user response:

Sqlsevr use temporary table must be names, oracle doesn't need