sugar 动态建表 索引 sql动态表

一、引言

       前面介绍的所有内容，所操作的数据库表必须存在，否则执行子程序时就会出现问题，这种方法叫做静态SQL。PLSQL中有一种能够定义程序时不指定具体的操作对象，在执行时动态传入对象的技术，叫做动态SQL。

二、动态的创建表格并返回表格行数

       下面的代码，我们定义了一个名叫get_table_count_fun的function，该函数接收一个字符串格式的表名作为参数，首先在user_tables中检查存不存在该表，若不存在就创建表，然后获取表中数据行的个数。

create or replace function get_table_count_fun(p_table_name varchar2) return number
as
 v_sql varchar2(200);
 v_count number;
begin
 select count(*) into v_count from user_tables where table_name=upper(p_table_name);
 if v_count=0 then
   v_sql:='create table '||p_table_name||'(empno number,ename varchar2(20) )';
   execute immediate v_sql;
 end if;
 v_sql:='select count(*) from '||p_table_Name;
 execute immediate v_sql into v_count;
 return v_count;
end;
/

declare
 v_count number;
begin
  v_count:=get_table_count_fun('myemp');
  dbms_output.put_line(v_count);
end;
/

三、设置占位符

declare
  v_empno myemp.empno%type :=1;
  v_ename myemp.ename%type :='hyman';
  v_sql varchar2(100);
begin
  v_sql:='insert into myemp values(:eno,:enm)';
  execute immediate v_sql using v_empno,v_ename;
end;
/

四、接收DML更新的字段

declare
 v_sql varchar2(100);
 v_name myemp.ename%type;
 v_no myemp.empno%type;
begin
 v_no:=1;
 v_sql:='update myemp set ename=''Liuzhihui'' where empno=:eno returning ename into :enm';
 execute immediate v_sql using v_no returning into v_name;
 dbms_output.put_line(v_name);
end;
/

        结果如下：

五、批量接收更新的字段

        我们要实现的效果是，删除表中所有的数据，但是要使用索引表批量接收ename并打印出来：

declare
  type ename_index is table of myemp.ename%type index by pls_integer;
  v_ename ename_index;
  v_sql varchar2(100);
begin
  v_sql:='delete from myemp returning ename into :ena';
  execute immediate v_sql returning bulk collect into v_ename;
  for x in 1..v_ename.count loop
    dbms_output.put_line(v_ename(x));
  end loop;
end;
/

       首先我们需要再次插入刚才删除的数据，然后使用动态sql进行查询操作并接收查询到的人员的ename：

declare
  type ename_varray is varray(100) of myemp.ename%type;
  v_ename ename_varray;
  v_sql varchar2(100);
begin
  v_sql:='select ename from myemp';
  execute immediate v_sql bulk collect into v_ename;
  for x in 1 .. v_ename.count loop
    dbms_output.put_line(v_ename(x));
  end loop;
end;
/

       上面两段代码的执行结果如下：

六、游标操作中使用动态SQL

        

        游标操作中同样可以使用动态SQL，如下面的代码，我们利用动态SQL的游标操作查询empno为1的雇员信息并打印出来：

declare
  cur_emp sys_refcursor;
  v_emprow myemp%rowtype;
  v_empno myemp.empno%type:=1;
begin
  open cur_emp for 'select * from myemp where empno = :eno' using v_empno;
  loop
     fetch cur_emp into v_emprow;
     exit when cur_emp%notfound;
     dbms_output.put_line(v_emprow.empno||' '||v_emprow.ename);
  end loop;
  close cur_emp;
end;
/

declare
  type emp_table is table of myemp%rowtype index by pls_integer;
  cur_emp sys_refcursor;
  v_emps emp_table;
begin
  open cur_emp for 'select * from myemp';
  fetch cur_emp bulk collect into v_emps;
  close cur_emp;
  for x in 1..v_emps.count loop
    dbms_output.put_line(v_emps(x).empno||' '||v_emps(x).ename);
  end loop;
end;
/