oracle存储过程删除临时表,请问在PL/SQL的存储过程中，可不可以创建临时表，然后删除？谢谢...

先谢谢，稍等一下，我试试

我把所有的代码拿上来，大伙帮看看，谢谢

CREATE OR REPLACE PROCEDURE FAIS.CS_AUTH_P_CREATEORG

(

IN_ORGANIZATION_ID IN VARCHAR2,

IN_ORGANIZATION_NAME IN NVARCHAR2,

IN_ORG_NAME_DESCRIPTION IN NVARCHAR2,

IN_ORGANIZATION_TYPE_ID IN NUMBER,

INOUT_EXCEPTION IN OUT NVARCHAR2

)

IS

P_EXIST_FLAG NUMBER(1):= 0;

P_SQLTEXT VARCHAR2(4000);

BEGIN

INOUT_EXCEPTION := '';

IF IN_ORGANIZATION_ID IS NULL OR TRIM(IN_ORGANIZATION_ID) = '' THEN

INOUT_EXCEPTION := '您的单位编号为空或者不存在';

RETURN;

END IF;

IF IN_ORGANIZATION_NAME IS NULL OR TRIM(IN_ORGANIZATION_NAME) = '' THEN

INOUT_EXCEPTION := '您的单位名称为空或者不存在';

RETURN;

END IF;

SELECT COUNT(*) INTO P_EXIST_FLAG FROM AUTH_ORGANIZATION WHERE ORGANIZATION_NAME = TRIM(IN_ORGANIZATION_NAME);

IF P_EXIST_FLAG > 0 THEN

INOUT_EXCEPTION := '您的单位名称已经存在，请确认';

RETURN;

END IF;

P_EXIST_FLAG := 0;

SELECT COUNT(*) INTO P_EXIST_FLAG FROM AUTH_ORGANIZATIONTYPE WHERE ORGANIZATION_TYPE_ID = IN_ORGANIZATION_TYPE_ID;

IF P_EXIST_FLAG = 0 THEN

INOUT_EXCEPTION := '您输入的单位类型不存在';

RETURN;

END IF;

--        首先向单位表中进行插入操作

INSERT INTO AUTH_ORGANIZATION

VALUES(TRIM(IN_ORGANIZATION_ID),TRIM(IN_ORGANIZATION_NAME),TRIM(IN_ORG_NAME_DESCRIPTION),IN_ORGANIZATION_TYPE_ID );

--        然后在角色表中进行复制操作

INSERT INTO AUTH_ROLE(ROLE_NAME, ROLE_DESCRIPTION, ORGANIZATION_ID, ORGANIZATION_TYPE_ID)

SELECT ROLE_NAME, ROLE_DESCRIPTION, IN_ORGANIZATION_ID, ORGANIZATION_TYPE_ID

FROM AUTH_ROLE

WHERE UPPER(ORGANIZATION_ID) = 'SYSTEM' AND ORGANIZATION_TYPE_ID = TRIM(IN_ORGANIZATION_TYPE_ID)

;

--        再次，在角色权限表中进行复制操作，需要用到临时表

P_SQLTEXT := 'CREATE GLOBAL TEMPORARY TABLE TEMP_ROLE_AUTHORITY

(

ROLE_ID                                NUMBER(4) NOT NULL,

ROLE_NAME                        NVARCHAR2(25) NOT NULL,

AUTHORITY_ID  NUMBER(4) NOT NULL

) ON COMMIT DELETE ROWS';

EXECUTE IMMEDIATE P_SQLTEXT;

--        向临时表中插入数据

/*

INSERT INTO TEMP_ROLE_AUTHORITY

SELECT A.*, B.ROLE_NAME

FROM AUTH_ROLE_AUTHORITY A, AUTH_ROLE B

WHERE UPPER(B.ORGANIZATION_ID) = 'SYSTEM'

AND ORGANIZATION_TYPE_ID = IN_ORGANIZATION_TYPE_ID

AND A.ROLE_ID = B.ROLE_ID;

*/

P_SQLTEXT := 'INSERT INTO TEMP_ROLE_AUTHORITY

SELECT A.*, B.ROLE_NAME

FROM AUTH_ROLE_AUTHORITY A, AUTH_ROLE B

WHERE B.ORGANIZATION_ID = 'System'

AND ORGANIZATION_TYPE_ID = IN_ORGANIZATION_TYPE_ID

AND A.ROLE_ID = B.ROLE_ID';

EXECUTE IMMEDIATE P_SQLTEXT;

/*

UPDATE TEMP_ROLE_AUTHORITY

SET ROLE_ID =

( SELECT　ROLE_ID FROM AUTH_ROLE D

WHERE TEMP_ROLE_AUTHORITY.ROLE_NAME = D.ROLE_NAME AND D.ORGANIZATION_ID =IN_ORGANIZATION_ID )

;

*/

P_SQLTEXT := 'UPDATE TEMP_ROLE_AUTHORITY

SET ROLE_ID =

( SELECT　ROLE_ID FROM AUTH_ROLE D

WHERE TEMP_ROLE_AUTHORITY.ROLE_NAME = D.ROLE_NAME AND D.ORGANIZATION_ID =IN_ORGANIZATION_ID )'

;

EXECUTE IMMEDIATE P_SQLTEXT;

P_SQLTEXT := 'INSERT INTO AUTH_ROLE_AUTHORITY

SELECT ROLE_ID, AUTHORITY_ID

FROM TEMP_ROLE_AUTHORITY'

;

EXECUTE IMMEDIATE P_SQLTEXT;

/*

INSERT INTO AUTH_ROLE_AUTHORITY

SELECT ROLE_ID, AUTHORITY_ID

FROM TEMP_ROLE_AUTHORITY

;

*/

--        删除临时表

P_SQLTEXT :='DROP TABLE TEMP_ROLE_AUTHORITY';

EXECUTE IMMEDIATE P_SQLTEXT;

COMMIT;

END CS_AUTH_P_CREATEORG;