关于动态SQL的问题

Question

程序如下：
declare
  2  type ename_table_type is table of emp.ename%type ;--index by binary_integer;
  3  type sal_table_type is table of emp.sal%type; --index by binary_integer;
  4  ename_table ename_table_type;
  5  sal_table sal_table_type;
  6  sql_stat varchar2(200);
  7  begin
  8    ename_table:=ename_table_type('SCOTT','SMITH','CLERK');
  9    sql_stat:='update emp set sal=sal*1.1 where ename=:1 returning into :2';
 10    forall i in 1..ename_table.count
 11    execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;
 12    for j in 1..ename_table.count loop
 13      dbms_output.put_line('雇员：'||ename_table(j)||'的薪水：'||sal_table(j));
 14    end loop;
 15  end;
问题1：定义表类型时，加index by binary_integer后，在给emp_table赋值时，会出现这个错误：PLS-00222: 在此范围中不存在名为 'ENAME_TABLE_TYPE' 的函数，请问是什么原因？
问题2:第11行处，using ename_table（i）是什么意思？为什么会出现此错误：ORA-00936: 缺失表达式？能说说using的用法吗？

Answer 1

问题1：
发生这个错误是由于INDEX BY子句构造了一个联合数组，而不是构造一个嵌套表。虽然嵌套表类型的定义显式地定义了一个构造函数，但是联合数组却不需要这种构造函数。

Answer 2

问题2：
using ename_table（i）相当于实参！在动态SQL拼接中，如果有形参，如你上面的:1和:2这两个形参占位符，用using ename_table（i）就相当于using ename_table（1）去替代:1，用using ename_table（2）去替代:2
这是execute immediate 执行动态SQL的用法

Answer 3

出现问题2的原因，我想是因为你循环forall i in 1..ename_table.count 你动态sql_stat需要两个参数，而你只用using ename_table（i），应该实参不够，因为你每次只有一个i参数

Answer 4

sql_stat只需要一个参数：1需要using传递值，那不就一个参数够了吗？

Answer 5

--问题1：
--如果你要那样用，可以这样解决：type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;type varray_tab is varray(3) of varchar2(10);
var varray_tab :=varray_tab('SCOTT','SMITH','CLERK'); --这样赋值
if ename_table.count <> 0 then
    for i in var.first..var.last loop
        ename_table(i) := var(i);
    end loop;
end if;
....
....--另外，问题2，你动态SQL里returning into :2返回参数是什么啊？既然你用到了占位符:2就需要参数了啊，且，你语法有问题吧，你返回什么啊？ 要这样：returning 栏位名称 into :2'
你应该是想这样：
sql_stat:='update emp set sal=sal*1.1 where ename=:1 returning ename,sal';forall i in 1..ename_table.count execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;

Answer 6

if ename_table.count = 0 then
    for i in var.first..var.last loop
        ename_table(i) := var(i);
    end loop;
end if;
哎哟，这个问题才多呢。。脑壳大了

Answer 7

（1）update 后的returning 语句缺少 返回列值 的选项  返回类型不明确.参看update 语句命令
（2）使用联合数组是,个人觉得在以数字作为下标时,不建议使用(使用嵌套表很好).当必须也字符作为下标时.那就不得不用了.

Answer 8

--看这个 你的有多处错误，联合数组跟嵌套表你的概念没弄清以及returning
SQL> edi
已写入 file afiedt.buf  1  declare
  2  type ename_table_type is table of emp.empname%type ;--index by binary_integer;
  3  type sal_table_type is table of emp.sal%type; --index by binary_integer;
  4  ename_table ename_table_type:=ename_table_type('SCOTT','SMITH','CLERK');
  5  sal_table sal_table_type:=sal_table_type();
  6  sql_stat varchar2(200);
  7  begin
  8  sql_stat:='update emp set sal=sal*1.1 where empname=:1 returning sal into :2';
  9  forall i in 1..ename_table.count
 10  execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;
 11  for j in 1..ename_table.count loop
 12  dbms_output.put_line('雇员：'||ename_table(j)||'的薪水：'||sal_table(j));
 13  end loop;
 14* end;
SQL> /
雇员：SCOTT的薪水：5500
雇员：SMITH的薪水：6600
雇员：CLERK的薪水：8800PL/SQL 过程已成功完成。
--你去看这个吧
http://blog.csdn.net/jxst051665/archive/2009/08/10/4430244.aspx

Answer 9

最初的疑惑基本已经明白，但是又出现了新问题。
按#8的程序执行、出现了这个错误：ORA-06533: 下标超出数量 

Answer 10

你把你SQL贴上来看看，数组越界了撒

Answer 11

declare
type ename_table_type is table of emp.ename%type ;
type sal_table_type is table of emp.sal%type; 
ename_table ename_table_type;
sal_table sal_table_type:=sal_table_type();
sql_stat varchar2(200);
begin 
  ename_table:=ename_table_type('SCOTT','SMITH','CLERK');
  sql_stat:='update emp set sal=sal*1.1 where ename=:1 returning sal into :2';
  forall i in 1..ename_table.count
  execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;
  for j in 1..ename_table.count loop
      sal_table.extend;----
    dbms_output.put_line('雇员：'||ename_table(j)||'的薪水：'||sal_table(j));
  end loop;
end;

Answer 12

--说了嵌套表才显式地定义了一个构造函数，因此初始化的时候用构造函数进行初始化
declare
type ename_table_type is table of emp.ename%type ;
type sal_table_type is table of emp.sal%type;  
ename_table ename_table_type:=ename_table_type();  --注意这里在初始化
sal_table sal_table_type:=sal_table_type();
sql_stat varchar2(200);
begin  
  ename_table:=ename_table_type('SCOTT','SMITH','CLERK');
  sql_stat:='update emp set sal=sal*1.1 where ename=:1 returning sal into :2';
  forall i in 1..ename_table.count
  execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;
  for j in 1..ename_table.count loop
  sal_table.extend;----
  dbms_output.put_line('雇员：'||ename_table(j)||'的薪水：'||sal_table(j));
  end loop;
end;

Answer 13

它不是自动初始化为NULL吗？跟你这个效果一样呀，而且即使初始化了，结果还是一样
雇员：SCOTT的薪水：990
雇员：SMITH的薪水：
雇员：CLERK的薪水：
 
PL/SQL procedure successfully completed
还有一个奇怪的现象：我把emp表的Scott删了，但还能输出Scott的sal

Answer 14

CLERK的sal确实为空，但是SMITH的sal不该为空呀、、、

Answer 15

你确保emp表中ename in('SCOTT','SMITH','CLERK')是有数据的吗，且sal不为null?
select ename,sal from emp where ename in('SCOTT','SMITH','CLERK') and sal is not null;

Answer 16

确定、执行你写的语句后结果是：
ENAME            SAL
---------- ---------
SMITH        1089.00
EMP表中没有CLERK、SCOTT的信息
SQL> select ename,sal from emp;
 
ENAME            SAL
---------- ---------
SMITH        1089.00
ALLEN        1870.00
WARD         1485.00
JONES        2975.00
MARTIN       1485.00
BLAKE        3135.00
CLARK        3240.13
KING         6612.50
TURNER       1760.00
ADAMS        1200.00
JAMES        1155.00
FORD         3000.00
MILLER       1851.50
 
13 rows selected

Answer 17

  原因：数组越界，
  这么给你详细解释下原因吧：
  假设情况： 
  (1)。ename in('SCOTT','SMITH','ALLEN')  EMP表中这三个员工都有记录，且工资SAL不为空，
  这种情况下嵌套表ename_table(i)和sal_table(j)才一一对应上，即上面的代码不会报错！
  (2)。如果其中一个员工不存在，如你所说为什么吧SCOTT删除了，执行结果还有SCOTT！其实这个是SCOTT工资是SMITH对应的工资
  这点就是你问的下表超出数量的错误。
  详细点来说：你首先构造的嵌套表 ename_table为三个，即 ('SCOTT','SMITH','ALLEN')，程序是根据姓名来更新对应工资，返回更新后的工资。
  但是此时你emp表没有SCOTT对应的记录，因此返回结果就只有两个，即('SMITH','ALLEN')的工资，这时sal_table只有两个值，
  接下来，你想打印出三个员工更新后的工资，循环三次，而 嵌套表sal_table只有两个，因此报下表超出数量的错误！（这点明白了吧？）
  再来说说为什么你删除了SCOTT，结果还有SCOTT的工资：前面已经说清楚了，你ename_table打印出的是三个员工，但是sal_table只有两个('SMITH','ALLEN')的工资，
  此时两个嵌套表的维数不同，并不是跟以前员工和对应工资是一一对应的，此时SCOTT对应的是sal_table(1),即对应的是SMITH的工资 。  说到这个份上，楼主应该理解了吧！

Answer 18

最后再补充一点，假设SCOTT记录存在，但是SAL为空，这样也不会出错，也是对的，只是返回SCOTT更新后的工资也是NULL结果就会是这样：原数据：
SMITH   800
ALLEN  1600
SCOTT  NULL执行后：    PL/SQL block, executed in 0 sec.
    雇员：SCOTT的薪水：             
    雇员：SMITH的薪水：880          
    雇员：ALLEN的薪水：1760         
    Total execution time 0.015 sec.