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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/775341/viewspace-718803/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/775341/viewspace-718803/