oracle之动态sql

语法：EXECUTE IMMEDIATE SQL_STRING [INTO   DEFINE_VARIABLE,DEFINE_VARIABLE.../record]
      [USING IN /OUT BIND_ARGUMENT,IN /OUT BIND_ARGUMENT..]

SQL_STRING:sql语句或PL/SQL代码块
DEFINE_VARIABLE:表示用来接收查询结果的变量
record:表示用来接收查询结果的记录型变量
bind_argument:表示一个表达式，表达式的值传给sql_string
into 单行查询
using 给sql_string 绑定字符串

1：利用动态sql进行ddl语句

declare
mycode varchar2(100);

begin
mycode:='create table student(sid number)';
Execute immediate mycode;
end;

2:创建一个函数返回某个表的记录数，在过程里判断当记录数大于5时，显示记录数过多

create function tab_count(tablename varchar2)
return integer
is
mycode varchar2(200):='select count(*) from '||tablename;
cnt integer;
begin
Execute immediate mycode into cnt;
return cnt;

end;

begin
 if(tab_count('readerinfo')>5) then
    dbms_output.put_line('人数过多');
 end if;
end;

 

 

3:使用动态游标实现

declare
type cursor_type is ref cursor;
c_readerinfo cursor_type;
mycode varchar2(200):='select readerid,readername from readerinfo';
p_id readerinfo.readerid%type;
p_name varchar2(6);
begin
open c_readerinfo for mycode;
loop
 fetch c_readerinfo into p_id,p_name;
 exit when c_readerinfo%notfound;
 dbms_output.put_line('id:'||p_id||'  name:'||p_name);
end loop;
close c_readerinfo;
end;

4:使用using 给动态语句传值
declare
p_id char(4):='9710';
p_name varchar(6):='朱小';
mycode varchar2(200);
begin
mycode:='insert into readerinfo(readerid,readername) values(:1,:2)';
execute immediate mycode using p_id,p_name;

end;