mysql 查询列名和值_ORACLE中使用DBMS_SQL获取动态SQL执行结果中的列名和值

获取动态SQL中的列名及类型

DECLARE

l_curid INTEGER;

l_cnt NUMBER;

l_desctab dbms_sql.desc_tab;

l_sqltext VARCHAR2(2000);

BEGIN

l_sqltext := 'select owner,object_type,object_name from dba_objects where rownum<= 10'; --可以是任意有效的查询sql文本

l_curid := dbms_sql.open_cursor();

dbms_sql.parse(l_curid, l_sqltext, dbms_sql.native);

dbms_sql.describe_columns(l_curid, l_cnt, l_desctab);

FOR i IN 1 .. l_desctab.count LOOP

dbms_output.put_line(rpad(l_desctab(i).col_name, 30)||rpad(l_desctab(i).col_type, 3));

END LOOP;

dbms_sql.close_cursor(l_curid);

END;

----------------------------------------

OWNER 1

OBJECT_TYPE 1

OBJECT_NAME 1

使用USING方式绑定动态SQL,获取列名及对应的值

DECLARE

TYPE typecursor IS REF CURSOR;

cursrc typecursor;

curid NUMBER;

desctab dbms_sql.desc_tab;

colcnt NUMBER;

vname VARCHAR2(50);

vnum NUMBER;

vdate DATE;

rownumber NUMBER := 5;

sqlstmt VARCHAR2(2000);

BEGIN

sqlstmt := 'SELECT * FROM fnd_user WHERE rownum < :rownumber';

-- 打开光标

OPEN cursrc FOR sqlstmt

USING rownumber;

-- 从本地动态SQL转换为DBMS_SQL

curid := dbms_sql.to_cursor_number(cursrc);

--获取游标里面的数据列项数和每个数据列的属性，比如列名，类型，长度等

dbms_sql.describe_columns(curid, colcnt, desctab);

-- 定义列

FOR i IN 1 .. colcnt LOOP

--此处是定义游标中列的读取类型，可以定义为字符，数字和日期类型，

IF desctab(i).col_type = 2 THEN

dbms_sql.define_column(curid, i, vnum);

ELSIF desctab(i).col_type = 12 THEN

dbms_sql.define_column(curid, i, vdate);

ELSE

dbms_sql.define_column(curid, i, vname, 50);

END IF;

END LOOP;

-- DBMS_SQL包获取行

--从游标中把数据检索到缓存区(BUFFER)中，缓冲区 的值只能被函数COULUMN_VALUE()所读取

WHILE dbms_sql.fetch_rows(curid) > 0 LOOP

--函数column_value()把缓冲区的列的值读入相应变量中。

FOR i IN 1 .. colcnt LOOP

IF desctab(i).col_type = 1 THEN

dbms_sql.column_value(curid, i, vname);

dbms_output.put_line(desctab(i).col_name || ' ' || vname || ', ');

ELSIF (desctab(i).col_type = 2) THEN

dbms_sql.column_value(curid, i, vnum);

dbms_output.put_line(desctab(i).col_name || ' ' || vnum || ', ');

ELSIF (desctab(i).col_type = 12) THEN

dbms_sql.column_value(curid, i, vdate);

dbms_output.put_line(desctab(i).col_name || ' ' || to_char(vdate, 'YYYY-MM-DD HH24:MI:SS') || ', ');

END IF;

END LOOP;

END LOOP;

dbms_sql.close_cursor(curid);

END;

使用DBMS_SQL.BIND_VARIABLE方式传递参数，获取列名及对应的值

DECLARE

v_cursor NUMBER;

v_stat NUMBER;

v_owner VARCHAR(100);

v_object_name VARCHAR(100);

v_sql VARCHAR(200);

s_owner varchar2(100);

BEGIN

s_owner := 'ADMIN';

v_sql := q'[select owner,object_name from dba_objects where object_type='TABLE' and owner=:owner]';

v_cursor := dbms_sql.open_cursor; --打开游标；

dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析动态SQL语句；

dbms_sql.bind_variable(v_cursor, ':owner', s_owner); --绑定输入参数；

dbms_sql.define_column(v_cursor, 1, v_owner, 100); --定义列

dbms_sql.define_column(v_cursor, 2, v_object_name, 100);

v_stat := dbms_sql.execute(v_cursor); --执行动态SQL语句。

LOOP

EXIT WHEN dbms_sql.fetch_rows(v_cursor) <= 0; --fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。

dbms_sql.column_value(v_cursor, 1, v_owner); --将当前行的查询结果写入上面定义的列中。

dbms_sql.column_value(v_cursor, 2, v_object_name);

dbms_output.put_line(v_owner || ';' || v_object_name);

END LOOP;

dbms_sql.close_cursor(v_cursor); --关闭游标。

END;

---------------------------------------------------------

ADMIN;TEST_BLOB

ADMIN;EMP

ADMIN;DEPT

ADMIN;BONUS

ADMIN;SALGRADE

ADMIN;DUMMY

ADMIN;X_DUMP

ADMIN;MY_EMP

通过 SQL文本 获取 字段名

DECLARE

l_curid INTEGER;

l_cnt NUMBER;

l_desctab dbms_sql.desc_tab;

l_sqltext VARCHAR2(2000);

BEGIN

l_sqltext := 'select owner,object_type,object_name from dba_objects where rownum <= 10'; --可以是任意有效的查询sql文本

l_curid := dbms_sql.open_cursor();

dbms_sql.parse(l_curid, l_sqltext, dbms_sql.native);

dbms_sql.describe_columns(l_curid, l_cnt, l_descTab);

FOR i IN 1 .. l_desctab.count

LOOP

dbms_output.put_line(l_desctab(i).col_name);

END LOOP;

dbms_sql.close_cursor(l_curId);

END;

----------------------------------------------

OWNER

OBJECT_TYPE

OBJECT_NAME

通过 REF CURSOR类型变量 获取 字段名(11g及以上)

--11g及以上

DECLARE

TYPE ref_cursor IS REF CURSOR;

l_cursor ref_cursor;

l_curid NUMBER;

l_col_cnt NUMBER;

l_desc_tab dbms_sql.desc_tab;

BEGIN

OPEN l_cursor FOR 'select owner,object_type,object_name from dba_objects where rownum<= 10';

l_curid := dbms_sql.to_cursor_number(l_cursor);

dbms_sql.describe_columns(l_curid, l_col_cnt, l_desc_tab);

FOR i IN 1 .. l_col_cnt LOOP

dbms_output.put_line(rpad(l_desc_tab(i).col_name, 30)||rpad(l_desc_tab(i).col_type, 3));

END LOOP;

dbms_sql.close_cursor(l_curid);

END;

------------------------------------------------

OWNER 1

OBJECT_TYPE 1

OBJECT_NAME 1

通过 REF CURSOR类型变量 获取 字段名(11g及以下)

--低于11G

DECLARE

l_cursor SYS_REFCURSOR;

i NUMBER := 0;

CURSOR get_columns IS

SELECT t2.column_value.getrootelement() NAME,

EXTRACTVALUE(t2.column_value, 'node()') VALUE

FROM (SELECT * FROM TABLE(XMLSEQUENCE(l_cursor))) t1,

TABLE(XMLSEQUENCE(EXTRACT(t1.column_value, '/ROW/node()'))) t2;

BEGIN

OPEN l_cursor FOR 'select owner,object_type,object_name from dba_objects where rownum <= 10';

FOR rec_ IN get_columns

LOOP

i := i + 1;

DBMS_OUTPUT.put_line(i || ':' || rec_.name || ': ' || rec_.VALUE);

END LOOP;

CLOSE l_cursor;

END;

---------------------------------------------------------------

1:OWNER: SYS

2:OBJECT_TYPE: INDEX

3:OBJECT_NAME: I_FILE#_BLOCK#

4:OWNER: SYS

5:OBJECT_TYPE: INDEX

6:OBJECT_NAME: I_OBJ3

7:OWNER: SYS

8:OBJECT_TYPE: INDEX

9:OBJECT_NAME: I_TS1

10:OWNER: SYS

11:OBJECT_TYPE: INDEX

12:OBJECT_NAME: I_CON1

13:OWNER: SYS

14:OBJECT_TYPE: TABLE

15:OBJECT_NAME: IND$

16:OWNER: SYS

17:OBJECT_TYPE: TABLE

18:OBJECT_NAME: CDEF$

19:OWNER: SYS

20:OBJECT_TYPE: CLUSTER

21:OBJECT_NAME: C_TS#

22:OWNER: SYS

23:OBJECT_TYPE: INDEX

24:OBJECT_NAME: I_CCOL2

25:OWNER: SYS

26:OBJECT_TYPE: INDEX

27:OBJECT_NAME: I_PROXY_DATA$

28:OWNER: SYS

29:OBJECT_TYPE: INDEX

30:OBJECT_NAME: I_CDEF4