17,382
社区成员




CREATE OR REPLACE PROCEDURE SP_HSA_Get_Income0421_TEST(
DATA1 OUT SYS_REFCURSOR) IS
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);
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;
EXECUTE IMMEDIATE V_STR;
V_STR :='DROP TABLE '||V_TEMP_TABLENAME_01;
EXECUTE IMMEDIATE V_STR;
END IF;
EXECUTE IMMEDIATE '
CREATE GLOBAL TEMPORARY TABLE '||V_TEMP_TABLENAME_01||' ON COMMIT PRESERVE ROWS AS
SELECT 1 AS AA, 2 AS BB FROM DUAL';
V_STR_01:='SELECT * FROM '||V_TEMP_TABLENAME_01;
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;
EXECUTE IMMEDIATE V_STR;
V_STR :='DROP TABLE '||V_TEMP_TABLENAME_01;
EXECUTE IMMEDIATE V_STR;
END IF;
END;
END SP_HSA_Get_Income0421_TEST;