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
怎么办,还有别的方法吗?
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;