PLS-00103:遇到符号"CREATE".当期望以下之一时: [英] PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

问题描述

create or replace
PROCEDURE USP_SAVE_BUSINESS
AS 
Ids varchar2(100);
BEGIN  
 
 Select MAX(TO_NUMBER(SUBSTR(BusinessId,2,LENGTH(BusinessId)))) into  Ids 
from ph_mstbusiness ;
 
 CREATE temporary TABLE ROWNUMBER
   (ROWNUMBER int(10) ,
   BUSINESS varchar2(100) );
    ON COMMIT PRESERVE ROWS;
/  
    
    
CREATE SEQUENCE ROWNUMBER_SEQ;
INSERT INTO RowNumber (ROWNUMBER, Business) values (ROWNUMBER_SEQ.nextval, SELECT DISTINCT BUSINESS FROM PH_MSTEMPLOYEE WHERE BUSINESS NOT IN (SELECT BUSINESSNAME FROM PH_MSTBUSINESS) 

INSERT INTO PH_MSTBUSINESS
(
BUSINESSID,
BUSINESSNAME
)
VALUES
(
select (case when ((Id + temp.RowNumber) <= 9 
	and (Id + temp.RowNumber) > 0 ) then 
	'B00' + cast( Id+ temp.RowNumber as varchar) 

	when ((Id + temp.RowNumber) <= 99 
	and (Id + temp.RowNumber) > 9 ) then 
	'B0' + cast( Id+ temp.RowNumber as varchar) 
	else 'B'+ cast( Id+ temp.RowNumber as varchar) 

	 end) as RowID,	
	a.Business
	from (Select Distinct Business from ph_mstemployee WHERE BUSINESS not in (select Businessname from ph_mstbusiness) ) a
		join RowNumber temp on a.Business = temp.Business;


)
END usp_save_business ;

有人可以告诉我这段代码出了错PLS-00103怎么了.

can somebody tell me whats wrong with this code its giving error PLS-00103.

推荐答案

您不能在过程内部直接运行DDL.

请尝试以下构造:

You can''t run DDL directly inside the procedure.

Try this construction instead:

create or replace
PROCEDURE USP_SAVE_BUSINESS
AS 
Ids varchar2(100);
BEGIN  
 
 Select MAX(TO_NUMBER(SUBSTR(BusinessId,2,LENGTH(BusinessId)))) into  Ids 
from ph_mstbusiness ;
 
EXECUTE IMMEDIATE 'CREATE temporary TABLE ROWNUMBER
   (ROWNUMBER int(10) ,
   BUSINESS varchar2(100) );
    ON COMMIT PRESERVE ROWS'
    
EXECUTE IMMEDIATE 'CREATE SEQUENCE ROWNUMBER_SEQ'
...

尝试注释掉下面的代码

try to comment out the below code

CREATE SEQUENCE ROWNUMBER_SEQ;

因为已经创建了ROWNUMBER_SEQ，因此可以直接在插入查询中使用.
上面的创建序列代码是错误的.请参考下面的代码来创建序列...

as ROWNUMBER_SEQ muse be already created and can directly be used in insert query.
Above code of creating sequence is wrong. please refer below code for creating sequence...

CREATE SEQUENCE ROWNUMBER_SEQ
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

用于创建表的正确语法是

Correct syntax for creating table is

CREATE TABLE ROWNUMBER (ROWNUMBER int, BUSINESS varchar(100) );

如果要创建临时表，请

and if you want to create temprory table then

DECLARE @ROWNUMBER TABLE(ROWNUMBER int, BUSINESS varchar(100) );

这篇关于PLS-00103:遇到符号"CREATE".当期望以下之一时:的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！