oracle dbms_sql.describe_columns,oralce Dbms_sql封装过程中的主要函数

ORACLE应用经验(3)-DBMS_SQL封装过程

DBMS_SQL封装过程中主要函数

1、OPEN_CURSOR：返回新游标的ID值

2、PARSE：解析要执行的语句

3、BIND_VARIABLE：将给定的数量与特定的变量相连接

4、DEFINE_COLOUMN：定义字段变量，其值对应于指定游标中某个位置元素的值

(仅用于SELECT语句)

5、EXECUTE：执行指定的游标

6、EXECUTE_AND_FETCH：执行指定的游标并取记录

7、FETCH_ROWS：从指定的游标中取出记录

8、COLUMN_VALUE：返回游标中指定位置的元素

9、IS_OPEN：当指定的游标状态为OPEN时返回真值

10、CLOSE_CURSOR：关闭指定的游标并释放内存

11、LAST_ERROR_POSITION：返回出错SQL语句的字节偏移量

12、LAST_ROW_ID：返回最后一条记录的ROWID

13、LAST_SQL_FUNCTION_CODE：返回语句的SQL FUNCTION CODE

例：

CREATE OR REPLACE

procedure dml_sql (the_rq varchar2) as

The_c1         Integer;

The_result     Integer;--dml_sql_result

M_jls          number;

The_xh         varchar2(2);

Begin

The_xh:=lpad(ltrim(the_rq),2,'0');

The_C1 :=Dbms_sql.open_cursor;

Dbms_sql.parse(the_C1,'select count(*) from user_indexes

where table_name =''ZDCHB'''||the_xh,dbms_sql.v7);

Dbms_sql.define_column(the_c1,1,M_jls);

The_result:=Dbms_sql.execute(The_c1);

If dbms_sql.fetch_rows(cursor1) > 0 then

Dbms_sql.column_value(cursor1,1,M_jls);

End if;

Dbms_sql.close_cursor(The_C1);

End;

---------------------------- 例子 二  经典--------------------------

CREATE OR REPLACE PROCEDURE testdml_sql (the_rq VARCHAR2)

IS

the_c1         INTEGER;

the_result     INTEGER;                                   --dml_sql_result

m_jls          VARCHAR (20);

colcnt         INTEGER;                                             --列数

desctbl        DBMS_SQL.desc_tab;                                   --排序

colunmvalue    VARCHAR (3000);                                      --变量

v_price3       VARCHAR (20);

v_sys_type     VARCHAR (20);

the_xh         VARCHAR (9);

TYPE filedvtype_arry IS TABLE OF VARCHAR2 (1000)

INDEX BY VARCHAR2 (100);                                   --定义索引表

pkfiled_arry   filedvtype_arry;                  --存储一般关键字 的索引表

pkfiledcount   NUMBER;                           --索引表集合下标

arrystr        VARCHAR (1000);                   --用于测试临时存储索引表集合的值

BEGIN

the_c1 := DBMS_SQL.open_cursor;

DBMS_SQL.parse (the_c1,

'SELECT uuid, xuhao,price FROM IMS.TABLE1 ',

DBMS_SQL.native

);

--解析游标   (parse :解析)

--取值过程方法 一

-- (describe_columns  描述列)

DBMS_SQL.describe_columns (the_c1, colcnt, desctbl);--找出有多少列

FOR i IN 1 .. colcnt

LOOP

DBMS_SQL.define_column (the_c1, i, colunmvalue, 4000);   --循环定义多列

END LOOP;

the_result := DBMS_SQL.EXECUTE (the_c1);                  --执行游标中的SQL

pkfiledcount := 0;

WHILE (DBMS_SQL.fetch_rows (the_c1) > 0)                         --遍历游标

LOOP

DBMS_SQL.COLUMN_VALUE (the_c1, 1, colunmvalue);        --分别取出列的值

m_jls := colunmvalue;                            --把取的值赋给其它变量

DBMS_SQL.COLUMN_VALUE (the_c1, 2, colunmvalue);        --分别取出列的值

v_price3 := colunmvalue;                         --把取的值赋给其它变量

DBMS_SQL.COLUMN_VALUE (the_c1, 3, colunmvalue);        --分别取出列的值

v_sys_type := colunmvalue;                       --把取的值赋给其它变量

pkfiled_arry (pkfiledcount) := m_jls;     --把值赋给索引表

pkfiledcount := pkfiledcount + 1;         --索引表下标加 1

END LOOP;

--取索引表的数据

FOR i IN 0 .. pkfiledcount - 1

LOOP

arrystr := arrystr || ',' || pkfiled_arry (i);

END LOOP;

IF arrystr IS NOT NULL

THEN

arrystr := SUBSTR (arrystr, 2, LENGTH (arrystr));      --去掉前同面的逗号

END IF;

---  另一种取值过程方法  取值过程方法 二

DBMS_SQL.define_column (the_c1, 1, m_jls, 20);                     --定义列

DBMS_SQL.define_column (the_c1, 2, v_price3, 20);              --单独字义列

DBMS_SQL.define_column (the_c1, 3, v_sys_type, 20);            --单独字义列

the_result := DBMS_SQL.EXECUTE (the_c1);                  --执行游标中的SQL

LOOP

EXIT WHEN DBMS_SQL.fetch_rows (the_c1) <= 0;                 --遍历游标

DBMS_SQL.COLUMN_VALUE (the_c1, 1, m_jls);        --按顺序取值并赋给变量

DBMS_SQL.COLUMN_VALUE (the_c1, 2, v_price3);

DBMS_SQL.COLUMN_VALUE (the_c1, 3, v_sys_type);

END LOOP;

DBMS_SQL.close_cursor (the_c1);

END;

-------------------------------例子 三  -----------------

/* Formatted on 2012/03/16 23:52 (Formatter Plus v4.8.8) */

DECLARE

v_cursor   NUMBER;

v_stat     NUMBER;

v_row      NUMBER;

v_id       NUMBER;

v_no       VARCHAR (100);

v_date     DATE;

v_sql      VARCHAR (200);

s_id       NUMBER;

s_date     DATE;

BEGIN

s_id := 3000;

s_date := SYSDATE;

v_sql :=

'SELECT id,qan_no,sample_date FROM ims.tblno WHERE id > :sid and sample_date <

:sdate';

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

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

DBMS_SQL.bind_variable (v_cursor, ':sid', s_id);          --绑定输入参数；

DBMS_SQL.bind_variable (v_cursor, ':sdate', s_date);

DBMS_SQL.define_column (v_cursor, 1, v_id);                       --定义列

DBMS_SQL.define_column (v_cursor, 2, v_no, 100);

DBMS_SQL.define_column (v_cursor, 3, v_date);

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_id);

--将当前行的查询结果写入上面定义的列中。

DBMS_SQL.COLUMN_VALUE (v_cursor, 2, v_no);

DBMS_SQL.COLUMN_VALUE (v_cursor, 3, v_date);

DBMS_OUTPUT.put_line (v_id || ';' || v_no || ';' || v_date);

END LOOP;

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

END;