SQL> declare flag integer default 0; 2 begin 3 select 1 into flag from v$tablespace a, v$datafile b where a.ts#=b.ts# and a.NAME = 'TEST02'; 4 dbms_output.put_line('Already Exists TableSpace'); 5 exception when no_data_found then 6 execute immediate 'CREATE TABLESPACE TEST02 LOGGING 7 DATAFILE ''C:\ORACLE\ORADATA\HASL\TEST02.ora'' SIZE 20M EXTENT 8 MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO '; 9 end; 10 /PL/SQL procedure successfully completed
单独执行select 1 into flag from v$tablespace a, v$datafile b where a.ts#=b.ts# and a.NAME = 'TEST02';时报错, ORA-00905: 缺少关键字
你没有声明变量flag当然报错了,去掉into flag就可以单独执行了。 select 1 into flag from v$tablespace a, v$datafile b where a.ts#=b.ts# and a.NAME = 'TEST02' 改为: select 1 into flag from user_tablespaces where tablespace_name = 'TEST02'; 更简单一些,而且不需要select catalog权限
2 begin
3 select 1 into flag from v$tablespace a, v$datafile b where a.ts#=b.ts# and a.NAME = 'TEST02';
4 dbms_output.put_line('Already Exists TableSpace');
5 exception when no_data_found then
6 execute immediate 'CREATE TABLESPACE TEST02 LOGGING
7 DATAFILE ''C:\ORACLE\ORADATA\HASL\TEST02.ora'' SIZE 20M EXTENT
8 MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ';
9 end;
10 /PL/SQL procedure successfully completed
ORA-00905: 缺少关键字
select 1 into flag from v$tablespace a, v$datafile b where a.ts#=b.ts# and a.NAME = 'TEST02'
改为:
select 1 into flag from user_tablespaces where tablespace_name = 'TEST02';
更简单一些,而且不需要select catalog权限
谢谢
这个查询的结果最多只有一条记录,所以flag是单个变量,记录条数大于1时flag需要声名为数组