EXECUTE IMMEDIDATE 动态创建表和插入数据
create or replace procedure create_table(i_id number,i_name varchar,i_sal number) as
v_table varchar2(200) := 'emp_test';
v_count number(7);
begin
select count(1) into v_count from user_tables where table_name = upper(v_table);
if v_count = 0 then
execute immediate 'create table ' || v_table || '(
id number(7),
name varchar(200),
sal number(7,2))';
elsif v_count = 1 then
execute immediate 'insert into emp_test values(:1,:2,:3)'
using i_id, i_name, i_sal;
end if;
commit;
end;
declare
v_id number(7);
v_name varchar(200);
v_sal number(7,2);
begin
v_id :=&v_id;
v_name :=&v_name;
v_sal :=&v_sal;
create_table(v_id,v_name,v_sal);
end;