declare
tabr tab%rowtype;
empr emp%rowtype;
cursor curemp is select * from emp for update;
begin
select * into tabr from tab where tname='upemp';
if SQL%found then null;
else execute immediate 'create table upemp select * from emp where 2<1';
end if ;
end;
我想先找是否存在Upemp表,存在则不用再建,不存在刚建一张新表
可以这样会有错误
ORA-01403: 未找到数据
ORA-06512: 在line 10
怎么办,还有别的方法吗?
tabr tab%rowtype;
empr emp%rowtype;
cursor curemp is select * from emp for update;
begin
select * into tabr from tab where tname='upemp';
if SQL%found then null;
else execute immediate 'create table upemp select * from emp where 2<1';
end if ;
end;
我想先找是否存在Upemp表,存在则不用再建,不存在刚建一张新表
可以这样会有错误
ORA-01403: 未找到数据
ORA-06512: 在line 10
怎么办,还有别的方法吗?
解决方案 »
- Linux于Windows7 双系统问题
- 求助:如何把主表中一个字段内容拆分到明细表中;
- tnsnames.ora文件指向问题
- 存储文件到db中时,是存url还是存内容好?
- 求一个更新语句
- 安装不成功
- odbc连接oracle问题
- 有没有办法开机之后即让ORACLE数据库实例自动启动——长期病痛
- Oracle Enterprise Manager的初始用户和密码是什么啊?
- 怎样判断某天所属的星期的星期一和星期天对应的日期分别是什么?
- 这个SQL查询怎么写?????????????????????????????????????????????????????????????????????
- 游标修改
declare
tabr tab%rowtype;
empr emp%rowtype;
cursor curemp is select * from emp for update;
begin
select * into tabr from tab where tname= 'UPEMP ';
if SQL%found then null;
else execute immediate 'create table upemp select * from emp where 2 <1 ';
end if ;
end;
判断方法错了,应该用count(*):
DECLARE
TABR INT;
EMPR SCOTT.EMP%ROWTYPE;
CURSOR CUREMP IS
SELECT * FROM SCOTT.EMP FOR UPDATE;
BEGIN
SELECT COUNT(*) INTO TABR FROM TAB WHERE TNAME = 'PEMP';
IF TABR = 0 THEN
EXECUTE IMMEDIATE 'create table pemp as select * from scott.emp where 2 <1 ';
END IF;
END;
/
是CREATE TABLE TABLE_NAME AS SELECT * FROM TABLE_NAME
TABR INT;
EMPR SCOTT.EMP%ROWTYPE;
CURSOR CUREMP IS
SELECT * FROM SCOTT.EMP FOR UPDATE;
BEGIN
SELECT COUNT(*) INTO TABR FROM TAB WHERE TNAME = 'PEMP';
IF TABR = 0 THEN
EXECUTE IMMEDIATE 'create table pemp as select * from scott.emp where 2 <1 ';
END IF;
END;