PL/SQL开发中动态SQL的使用方法(1)

create or replace procedure proc_test 
( 
table_name in varchar2, --表名 
field1 in varchar2, --字段名 
datatype1 in varchar2, --字段类型 
field2 in varchar2, --字段名 
datatype2 in varchar2 --字段类型 
) as 
str_sql varchar2(500); 
begin 
str_sql:=’create table ’||table_name||’(’||field1||’ 
  ’||datatype1||’,’||field2||’ ’||datatype2||’)’; 
execute immediate str_sql; --动态执行DDL语句 
exception 
when others then 
null; 
end ;

SQL> execute proc_test(’dinya_test’,’id’,’number(8) not null’,
  ’name’,’varchar2(100)’); 
PL/SQL procedure successfully completed 
SQL> desc dinya_test; 
Name Type Nullable Default Comments 
---- ------------- -------- ------- -------- 
ID NUMBER(8) 
NAME VARCHAR2(100) Y 
SQL>

create or replace procedure proc_insert 
( 
id in number, --输入序号 
name in varchar2 --输入姓名 
) as 
str_sql varchar2(500); 
begin 
str_sql:=’insert into dinya_test values(:1,:2)’; 
execute immediate str_sql using id,name; --动态执行插入操作 
exception 
when others then 
null; 
end ;

SQL> execute proc_insert(1,’dinya’); 
PL/SQL procedure successfully completed 
SQL> select * from dinya_test; 
ID NAME 
1 dinya

declare 
p_id number:=1; 
v_count number; 
begin 
v_string:=’select count(*) from table_name a where a.id=:id’; 
execute immediate v_string into v_count using p_id; 
end ;