1、oracle在执行静态sql的时候会做语法校验并声称执行计划
错误:
declare
l_sql varchar2(2000);
l_flag int;
begin
select count(*) into l_flag from user_tables where table_name = upper('table_name');
l_sql := 'CREATE TABLE table_name(
code varchar2(4) NOT NULL,
name varchar2(64) NULL,
CONSTRAINT PK_ops_zd_status PRIMARY KEY
(
code
)
)';
IF l_flag = 0 then
execute immediate l_sql;
insert into table_name (code,name)
select '0' code ,' ' name from dual';
end if;
END;
l_sql varchar2(2000);
l_flag int;
begin
select count(*) into l_flag from user_tables where table_name = upper('table_name');
l_sql := 'CREATE TABLE table_name(
code varchar2(4) NOT NULL,
name varchar2(64) NULL,
CONSTRAINT PK_ops_zd_status PRIMARY KEY
(
code
)
)';
IF l_flag = 0 then
execute immediate l_sql;
insert into table_name (code,name)
select '0' code ,' ' name from dual';
end if;
END;
正确:
declare
l_sql varchar2(2000);
l_flag int;
begin
l_flag := 0;
select count(*) into l_flag from user_tables where table_name = upper('table_name');
l_sql := 'CREATE TABLE table_name(
code varchar2(4) NOT NULL,
name varchar2(64) NULL,
CONSTRAINT PK_ops_zd_status PRIMARY KEY
(
code
)
)';
IF l_flag = 0 then
execute immediate l_sql;
EXECUTE IMMEDIATE '
insert into table_name (code,name)
select ''0'' code ,'' '' name from dual';
end if;
END;
l_sql varchar2(2000);
l_flag int;
begin
l_flag := 0;
select count(*) into l_flag from user_tables where table_name = upper('table_name');
l_sql := 'CREATE TABLE table_name(
code varchar2(4) NOT NULL,
name varchar2(64) NULL,
CONSTRAINT PK_ops_zd_status PRIMARY KEY
(
code
)
)';
IF l_flag = 0 then
execute immediate l_sql;
EXECUTE IMMEDIATE '
insert into table_name (code,name)
select ''0'' code ,'' '' name from dual';
end if;
END;
原因:oracle在执行静态sql的时候会做语法校验并声称执行计划,所以insert 语句会在你create 之前校验