oracle 动态添加字段名,oracle – 如何动态获取记录中的字段名称和值

这是一种方法.包规范包含一个变量,其类型与我们将在过程中使用的变量匹配.

SQL> set serveroutput on

SQL> create or replace package pkg_xxx

2 as

3 dept_rec dept%rowtype;

4 end;

5 /

Package created.

SQL> create or replace procedure xxx (pi_record in dept%rowtype)

2 as

3 text varchar2 (10000) := null;

4 l_str varchar2 (200);

5 l_var varchar2 (200);

6 begin

7 pkg_xxx.dept_rec := pi_record;

8

9 for cur_r in ( select column_name

10 from user_tab_columns

11 where table_name = 'DEPT'

12 order by column_id)

13 loop

14 l_str :=

15 'begin '

16 || ':x := to_char(pkg_xxx.dept_rec.'

17 || cur_r.column_name

18 || '); '

19 || 'end; ';

20

21 execute immediate l_str using out l_var;

22

23 text := text || chr (10) || cur_r.column_name || ' = ' || l_var;

24 end loop;

25

26 dbms_output.put_line (text);

27 end;

28 /

Procedure created.

现在,让我们将一些东西传递给程序,看看会发生什么：

SQL> declare

2 cursor c1

3 is

4 select *

5 from dept

6 where deptno = 10;

7

8 c1r c1%rowtype;

9 begin

10 open c1;

11 fetch c1 into c1r;

12 close c1;

13

14 xxx (c1r);

15 end;

16 /

DEPTNO = 10

DNAME = ACCOUNTING

LOC = NEW YORK

PL/SQL procedure successfully completed.

SQL>

嗯,有点作品(如果这就是你问的那个).当然,这只是一个例子,如果你想获得一些非常聪明的东西,你必须修改它(提示：DATE列).