如何创建删除表并创建表的Oralce存储过程 [英] How to create Oralce Stored Procedure which drops table and create tables
本文介绍了如何创建删除表并创建表的Oralce存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试创建删除表(如果存在)并创建临时表的Oracle存储过程。然后将数据转储到临时表中
它给我编译器错误:
错误(12,1):PLS-00103 :遇到符号创建
有人可以提供一些关于如何解决这个错误的指导或指示吗?
创建 或替换 PROCEDURE SP_GET_USERS(STARTDATE_IN IN DATE ,ENDDATE_IN IN DATE )
IS
CANTIDAD整数;
BEGIN
SELECT COUNT(*) INTO CANTIDAD FROM USER_TABLES WHERE TABLE_NAME = TEMP_TABLE1;
IF (CANTIDAD> 0)那么
执行立即' DROP TABLE' || TEMP_TABLE1
END IF ;
- END;
执行立即
' CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE1
(USER_ID数字,
角色CHAR(30),
MONTH_PER CHAR(20),
COUNT个数字)
ON COMMIT DELETE ROWS;'
END IF ;
INSERT INTO TEMP_TABLE1
(USER_ID, ROLE ,MONTH_PER,COUNT)
SELECT *
FROM TABLE1
WHERE MONTH_PER BETWEEN STARTDATE_IN AND ENDDATE_IN
END ;
解决方案
< blockquote>创建存储过程及其正在做的事情是两件完全不同的事情。
http:// www。 w3schools.com/sql/ [ ^ ]
你的代码需要一些检查,我看到一个 IF
和两个 END IF
我猜错误信息只是第一个。
I am trying to create Oracle Stored procedure which drops table (if exists) and creates a temp table. Then dumps a data into the temp table
It is giving me complier error:
"Error(12,1): PLS-00103: Encountered the symbol "CREATE""
Can someone please provides some guidance or directions on how to get around this error?
create or replace PROCEDURE SP_GET_USERS(STARTDATE_IN IN DATE, ENDDATE_IN IN DATE)
IS
CANTIDAD integer;
BEGIN
SELECT COUNT(*) INTO CANTIDAD FROM USER_TABLES WHERE TABLE_NAME = TEMP_TABLE1;
IF (CANTIDAD >0) THEN
execute immediate 'DROP TABLE ' || TEMP_TABLE1
END IF;
--END;
Execute Immediate
'CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE1
(USER_ID number,
Role CHAR(30),
MONTH_PER CHAR(20),
COUNT number)
ON COMMIT DELETE ROWS;'
END IF;
INSERT INTO TEMP_TABLE1
(USER_ID, ROLE, MONTH_PER, COUNT)
SELECT *
FROM TABLE1
WHERE MONTH_PER BETWEEN STARTDATE_IN AND ENDDATE_IN
END;
解决方案
Creating a stored procedure and what it is doing are 2 completely different things.
http://www.w3schools.com/sql/[^]
Your code need some checking, I see oneIF
and twoEND IF
I guess the error message is only the first one.
这篇关于如何创建删除表并创建表的Oralce存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文