create or replace procedure ct is
begin
execute immediate 'create table emp(id number,name varchar2(10),salary number)';
insert into EMP values (100,'jacky',5600);
insert into EMP values (101,'rose',3000);
insert into EMP values (102,'john',4500);
end ct;
/
编译出错,要怎么改啊?
begin
execute immediate 'create table emp(id number,name varchar2(10),salary number)';
insert into EMP values (100,'jacky',5600);
insert into EMP values (101,'rose',3000);
insert into EMP values (102,'john',4500);
end ct;
/
编译出错,要怎么改啊?
下面的插入也用动态SQL来INSERT。
还有最后没有COMMIT。
begin
execute immediate 'create table emp(id number,name varchar2(10),salary number)';
commit;
insert into EMP values (100,'jacky',5600);
insert into EMP values (101,'rose',3000);
insert into EMP values (102,'john',4500);
commit;
end ct;
v_tablecount int :=0;
begin
select count(1)
into v_tablecount
from user_tables
where table_name=upper('emp');
--判断表是否存在,如果存在,则drop表
if v_tablecount>1 then
execute immediate 'drop table emp';
else null;
--建立表
execute immediate 'create table emp(id number,name varchar2(10),salary number)';
--插入数据
execute immediate 'insert into EMP values (100,'jacky',5600)';
execute immediate 'insert into EMP values (101,'rose',3000)';
execute immediate 'insert into EMP values (102,'john',4500)';
commit;--提交数据
end ct;
create or replace procedure ct is
begin
execute immediate 'create table emp(id number,name varchar2(10),salary number)';
execute immediate 'insert into EMP values (100,'||'''jacky'''||',5600)';
execute immediate 'insert into EMP values (101,'||'''rose'''||',3000)';
execute immediate 'insert into EMP values (102,'||'''john'''||',4500)';
end ct;