SQL> conn system/manager 已连接。 SQL> DECLARE 2 N Integer; 3 BEGIN 4 SELECT COUNT(*) 5 INTO N 6 FROM USER_CATALOG 7 WHERE TABLE_NAME = 'TEMP_0001'; 8 IF N<0 THEN 9 CREATE TABLE TEMP_0001(QUESTION_ID VARCHAR2(8) NOT NULL) 10 TABLESPACE USERS; 11 END IF; 12 END; 13 / CREATE TABLE TEMP_0001(QUESTION_ID VARCHAR2(8) NOT NULL) * ERROR 位于第 9 行: ORA-06550: 第 9 行, 第 5 列: PLS-00103: 出现符号 "CREATE"在需要下列之一时: begindeclareexitforgoto ifloopmodnullpragmaraisereturnselectupdatewhile <an identifier><a double-quoted delimited-identifier> <a bind variable><<closecurrentdeletefetchlockinsertopen rollbacksavepointsetsqlexecutecommitforall <a single-quoted SQL string> SQL> edit 已写入文件 afiedt.buf 1 DECLARE 2 N Integer; 3 BEGIN 4 SELECT COUNT(*) 5 INTO N 6 FROM USER_CATALOG 7 WHERE TABLE_NAME = 'TEMP_0001'; 8 IF N<0 THEN 9 execute immediate('CREATE TABLE TEMP_0001(QUESTION_ID VARCHAR2(8) NOT NULL)'); 10 END IF; 11* END; SQL> /PL/SQL 过程已成功完成。
SELECT COUNT(*) INTO N FROM USER_CATALOG WHERE TABLE_NAME = 'TEMP_0001'; =============== 这句话个人建议使用 ... Select Nvl(Count(*),0) N Into N From User_CataLog WHERE TABLE_NAME = 'TEMP_0001'; IF N = 0 then ...或者Sql不修改 ... If Sql%NotFound then ... end if;
已连接。
SQL> DECLARE
2 N Integer;
3 BEGIN
4 SELECT COUNT(*)
5 INTO N
6 FROM USER_CATALOG
7 WHERE TABLE_NAME = 'TEMP_0001';
8 IF N<0 THEN
9 CREATE TABLE TEMP_0001(QUESTION_ID VARCHAR2(8) NOT NULL)
10 TABLESPACE USERS;
11 END IF;
12 END;
13 /
CREATE TABLE TEMP_0001(QUESTION_ID VARCHAR2(8) NOT NULL)
*
ERROR 位于第 9 行:
ORA-06550: 第 9 行, 第 5 列:
PLS-00103: 出现符号 "CREATE"在需要下列之一时:
begindeclareexitforgoto
ifloopmodnullpragmaraisereturnselectupdatewhile
<an identifier><a double-quoted delimited-identifier>
<a bind variable><<closecurrentdeletefetchlockinsertopen
rollbacksavepointsetsqlexecutecommitforall
<a single-quoted SQL string>
SQL> edit
已写入文件 afiedt.buf 1 DECLARE
2 N Integer;
3 BEGIN
4 SELECT COUNT(*)
5 INTO N
6 FROM USER_CATALOG
7 WHERE TABLE_NAME = 'TEMP_0001';
8 IF N<0 THEN
9 execute immediate('CREATE TABLE TEMP_0001(QUESTION_ID VARCHAR2(8) NOT NULL)');
10 END IF;
11* END;
SQL> /PL/SQL 过程已成功完成。
===============
这句话个人建议使用
...
Select Nvl(Count(*),0) N
Into N
From User_CataLog
WHERE TABLE_NAME = 'TEMP_0001'; IF N = 0 then
...或者Sql不修改 ... If Sql%NotFound then
...
end if;
即使没有符合条件的记录,也会返回0。所以加NVL无意义。
如上,要execute immediate 'create ....'
Oracle不支持在存储过程或函数中创建、删除表或视图等。如果必须这样做,请使用execute 语句来执行
2 from dual
3 where 1<>1; COUNT(*)
----------
0