ITPub博客

首页 > 数据库 > Oracle > PLSQL Language Referenc-PL/SQL动态SQL-DBMS_SQL包-DBMS_SQL.TO_CURSOR_NUMBER()函数

PLSQL Language Referenc-PL/SQL动态SQL-DBMS_SQL包-DBMS_SQL.TO_CURSOR_NUMBER()函数

原创 Oracle 作者:LuiseDalian 时间:2014-03-28 01:04:22 0 删除 编辑

DBMS_SQL.TO_CURSOR_NUMBER()函数

将一个强或弱的REF CURSOR变量 转换成SQL光标数字,这个SQL光标数字可以被传递给DBMS_SQL子程序。

REF CURSOR传递给DBMS_SQL.TO_CURSOR_NUMBER()函数之前需要先OPEN

在把REF CURSOR变量转换为SQL光标数字之后,本地动态SQL语句不能再访问它。

 

练习:从本地动态SQL转换为DBMS_SQL

-- 11g12_07_07.prc

CREATE OR REPLACE PROCEDURE do_query_2 (

  sqlStmt VARCHAR2

)

IS

    TYPE TypeCursor IS REF CURSOR;

    curSrc      TypeCursor;

    curID       NUMBER;

    descTab     DBMS_SQL.DESC_TAB;

    colCnt      NUMBER;

    vName       VARCHAR2(50);

    vNum        NUMBER;

    vDate       DATE;

    empNo       NUMBER := 100;

BEGIN

    -- sqlStmt := 'SELECT ... FROM employees WHERE employee_id = :b1';

    -- 打开光标

    OPEN curSrc FOR sqlStmt USING empNo;   

    -- 从本地动态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包获取行

    WHILE DBMS_SQL.FETCH_ROWS(curID) > 0 LOOP

        FOR i IN 1 .. colCnt LOOP

            IF (descTab(i).col_type = 1) THEN

                DBMS_SQL.COLUMN_VALUE(curID, i, vName);

                DBMS_OUTPUT.PUT_LINE(vName || ', ');

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

                DBMS_SQL.COLUMN_VALUE(curID, i, vNum);

                DBMS_OUTPUT.PUT_LINE(vNum || ', ');

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

                DBMS_SQL.COLUMN_VALUE(curID, i, vDate);           

                DBMS_OUTPUT.PUT_LINE(to_char(vDate) || ', ');

            END IF;           

        END LOOP;

    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(curID);

END;

-- 11g12_07_07.tst

BEGIN

    do_query_2('SELECT employee_id, first_name FROM employees where employee_id = :b1');

    do_query_2('SELECT employee_id, first_name, last_name FROM employees where employee_id = :b1');

END;   

结果:

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

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    1986
  • 访问量
    5678495