Oracle PL/SQL动态获取变量

假设有一张员工表fnd_employee 有如下字段employee_id, name, email, phone 四个字段， 然后我定义四个变量v1, v2, v3, v4 来存这四个字段（例: select * from fnd_employee into col1, col2, col3, col4 where rownum = 1）

那么现在我假设要拿到这四个变量中不为空的变量的值，那么我要怎么做呢？

方法一：写四个if…else 进行判断

if v1 is not null then
    ...
end if;
if v2 is not null then
    ...
end if;
...

那么如果有10个变量呢？有100个变量呢？不是要写N多个判断了吗？那么我要怎么获取这些变量比较方便快捷少写代码呢？我想了以下几个方法。

方法二：用数组来存这四个变量，然后用下标控制循环取得各个变量的值。

示例代码

DECLARE
--定义数组类型下标类型为varchar2, 类型值为varchar2
TYPE t_arr IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
v1 VARCHAR2(100);
v2 VARCHAR2(100);
v3 VARCHAR2(100);
v4 VARCHAR2(100);
vn t_arr;
v_index NUMBER := 1;
BEGIN
  SELECT e.employee_id, e.NAME, e.EMAIL, e.PHONE INTO vn('1'), vn('2'), vn('3'), vn('4') FROM fnd_employee e WHERE ROWNUM = 1;
  LOOP
    EXIT WHEN v_index > 4;
    dbms_output.put_line(vn(to_char(v_index)));
    v_index := v_index + 1;
  END LOOP;
END;

简单举个栗子上述程序将值赋给数组之后通过v_index数组下标来循环取得预先赋值的列值。

方法三：将四个变量连接起来中间用特殊符号隔开，然后在循环利用正则分割变量取得相应的值。

示例程序

DECLARE
v1 VARCHAR2(100);
v2 VARCHAR2(100);
v3 VARCHAR2(100);
v4 VARCHAR2(100);
v_s VARCHAR2(1000);
v_index NUMBER := 1;
BEGIN
  SELECT e.employee_id, e.NAME, e.EMAIL, e.PHONE INTO v1, v2, v3, v4 FROM fnd_employee e WHERE ROWNUM = 1;
  --连接四个变量并用逗号隔开
  v_s := v1 || ',' || v2 || ',' || v3 || ',' || v4;
  LOOP 
    EXIT WHEN v_index > 4;
    --函数名 : regexp_substr(str , pattern, position, order) 
    --返回正则匹配的字符串
    --position是指开始匹配的位置，order是指匹配结果中的第几个字符串
    dbms_output.put_line(regexp_substr(v_s, '[^,]+', 1, v_index));
    v_index := v_index + 1;
  END LOOP;
END;

这里用循环控制 正则匹配到第v_index个变量的值

方法四：利用SQL动态语句获取相应的

DECLARE
v1 VARCHAR2(100);
v2 VARCHAR2(100);
v3 VARCHAR2(100);
v4 VARCHAR2(100);
v_n VARCHAR2(100);
v_sql VARCHAR2(1000);
v_index NUMBER := 1;
BEGIN
  SELECT e.employee_id, e.NAME, e.EMAIL, e.PHONE INTO v1, v2, v3, v4 FROM fnd_employee e WHERE ROWNUM = 1;
  LOOP 
    EXIT WHEN v_index > 4;
    v_sql := 'select v' || v_index || ' from (select :v1 v1, :v2 v2, :v3 v3, :v4 v4 from dual)';
    EXECUTE IMMEDIATE v_sql INTO v_n USING v1, v2, v3, v4;
    dbms_output.put_line(v_n);
    v_index := v_index + 1;
  END LOOP;
END;

这里通过拼接sql语句然后动态执行获取v1, v2, v3，v4 并将四个参数以绑定变量的方式传入， 然后拼接sql，
用v_index来控制循环和取得的数据