oracle dbms_sql.describe_columns,DBMS

包DBMS_SQL对PolarDB-O的动态SQL查询功能提供用包DBMS_SQL，可以在应用的运行时间构建查询和其它的命令(而不是在编写程序的时候完成这项工作)。

PolarDB-O提供对动态SQL的特有支持。包DBMS_SQL以一种与Oracle兼容的方式来使用动态SQL，这样您就无需修改程序了。

包DBMS_SQL假定当前执行动态SQL的用户拥有相应的权限。

表 1.DBMS_SQL Functions/Procedures

Function/Procedure

Function or Procedure

Return Type

Description

BIND_VARIABLE(c, name, value [, out_value_size ])

Procedure

n/a

Bind a value to a variable.

BIND_VARIABLE_CHAR(c, name, value [, out_value_size ])

Procedure

n/a

Bind a CHAR value to a variable.

BIND_VARIABLE_RAW(c, name, value [, out_value_size ])

Procedure

n/a

Bind a RAW value to a variable.

CLOSE_CURSOR(c IN OUT)

Procedure

n/a

Close a cursor.

COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]])

Procedure

n/a

Return a column value into a variable.

COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT

]])

Procedure

n/a

Return a CHAR column value into a variable.

COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]])

Procedure

n/a

Return a RAW column value into a variable.

DEFINE_COLUMN(c, position, column [, column_size ])

Procedure

n/a

Define a column in the SELECT list.

DEFINE_COLUMN_CHAR(c, position, column, column_size)

Procedure

n/a

Define a CHAR column in the SELECT list.

DEFINE_COLUMN_RAW(c, position, column, column_size)

Procedure

n/a

Define a RAW column in the SELECT list.

DESCRIBE_COLUMNS

Procedure

n/a

Defines columns to hold a cursor result set.

EXECUTE(c)

Function

INTEGER

Execute a cursor.

EXECUTE_AND_FETCH(c [, exact ])

Function

INTEGER

Execute a cursor and fetch a single row.

FETCH_ROWS(c)

Function

INTEGER

Fetch rows from the cursor.

IS_OPEN(c)

Function

BOOLEAN

Check if a cursor is open.

LAST_ROW_COUNT

Function

INTEGER

Return cumulative number of rows fetched.

OPEN_CURSOR

Function

INTEGER

Open a cursor.

PARSE(c, statement, language_flag)

Procedure

n/a

Parse a statement.

与Oracle版本的DBMS_SQL执行相比，PolarDB的DBMS_SQL执行是部分执行。PolarDB仅支持上述表中列出的函数和存储过程。

下面的表中列出了在包DBM_SQL允许使用的公有变量。

表 2.DBMS_SQL Public Variables

Public Variables

Data Type

Value

Description

native

INTEGER

Provided for compatibility with Oracle syntax. See DBMS_SQL.PARSE for more information.

INTEGER

Provided for compatibility with Oracle syntax. See DBMS_SQL.PARSE for more information.

INTEGER

Provided for compatibility with Oracle syntax. See DBMS_SQL.PARSE for more information

BIND_VARIABLE

存储过程BIND_VARIABLE用于将一个值和在SQL命令中的IN或IN OUT绑定变量相关联。BIND_VARIABLE(c INTEGER, name VARCHAR2,

value { BLOB | CLOB | DATE | FLOAT | INTEGER | NUMBER |

TIMESTAMP | VARCHAR2 }

[, out_value_size INTEGER ])

参数

参数名称

描述

与带有绑定变量SQL命令相对应游标的ID。

name

SQL命令中绑定变量的名称。

value

被分配的值。

out_value_size

如果name是一个IN OUT模式的变量，那么定义输出值的最大长度。如果没有指定这个参数，那么将当前value的长度默认为最大值长度。

示例

下面的匿名代码块使用绑定变量向表emp中插入一条记录。DECLARE

curid INTEGER;

v_sql VARCHAR2(150) := 'INSERT INTO emp VALUES ' ||

'(:p_empno, :p_ename, :p_job, :p_mgr, ' ||

':p_hiredate, :p_sal, :p_comm, :p_deptno)';

v_empno emp.empno%TYPE;

v_ename emp.ename%TYPE;

v_job emp.job%TYPE;

v_mgr emp.mgr%TYPE;

v_hiredate emp.hiredate%TYPE;

v_sal emp.sal%TYPE;

v_comm emp.comm%TYPE;

v_deptno emp.deptno%TYPE;

v_status INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);

v_empno := 9001;

v_ename := 'JONES';

v_job := 'SALESMAN';

v_mgr := 7369;

v_hiredate := TO_DATE('13-DEC-07','DD-MON-YY');

v_sal := 8500.00;

v_comm := 1500.00;

v_deptno := 40;

DBMS_SQL.BIND_VARIABLE(curid,':p_empno',v_empno);

DBMS_SQL.BIND_VARIABLE(curid,':p_ename',v_ename);

DBMS_SQL.BIND_VARIABLE(curid,':p_job',v_job);

DBMS_SQL.BIND_VARIABLE(curid,':p_mgr',v_mgr);

DBMS_SQL.BIND_VARIABLE(curid,':p_hiredate',v_hiredate);

DBMS_SQL.BIND_VARIABLE(curid,':p_sal',v_sal);

DBMS_SQL.BIND_VARIABLE(curid,':p_comm',v_comm);

DBMS_SQL.BIND_VARIABLE(curid,':p_deptno',v_deptno);

v_status := DBMS_SQL.EXECUTE(curid);

DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);

DBMS_SQL.CLOSE_CURSOR(curid);

END;

Number of rows processed: 1

BIND_VARIABLE_CHAR

存储过程BIND_VARIABLE_CHAR将一个CHAR类型值和SQL命令中的IN或IN OUT绑定变量相关联。BIND_VARIABLE_CHAR(c INTEGER, name VARCHAR2, value CHAR

[, out_value_size INTEGER ])

参数

参数名称

描述

与带有绑定变量的SQL命令相对应的游标ID。

name

SQL命令中绑定变量的名称。

value

被分配的类型为RAW的值。

out_value_size

如果name是一个IN OUT模式的变量，那么定义输出值的最大长度。如果没有指定这个参数，那么将当前值的长度默认为最大值长度。

BIND VARIABLE RAW

存储过程BIND_VARIABLE_RAW用于将一个类型为RAW的值和SQL命令中的IN或IN OUT绑定变量相关联。BIND_VARIABLE_RAW(c INTEGER, name VARCHAR2, value RAW

[, out_value_size INTEGER ])

参数

参数名称

描述

与带有绑定变量的SQL命令相对应的游标ID。

name

SQL命令中绑定变量的名称。

value

被分配的类型为RAW的值。

out_value_size

如果name是一个IN OUT模式的变量，那么定义输出值的最大长度。如果没有指定这个参数，那么将当前值的长度默认为最大值长度。

CLOSE_CURSOR

存储过程CLOSE_CURSOR关闭一个已打开的游标。当关闭游标后，释放分配给游标的资源，并且不能再使用这个游标。CLOSE_CURSOR(c IN OUT INTEGER)

参数

参数名称

描述

要关闭游标的ID。

示例

在下面的示例中，关闭了一个先前已打开的游标： DECLARE

curid INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.CLOSE_CURSOR(curid);

END;

COLUMN_VALUE

存储过程COLUMN_VALUE定义了一个变量，用于从游标中接收值。COLUMN_VALUE(c INTEGER, position INTEGER, value OUT { BLOB |

CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }

[, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

参数

参数名称

描述

一个游标的ID，用于将数据返回给被定义变量。

position

在游标中返回数据的位置。在游标中的第一个位置值是1。

value

通过前面的fetch操作在游标中接收返回数据的变量。

column_error

如果发生异常错误，这个参数表示与列相关的错误代码。

actual_length

在进行截断操作前，数据的实际长度。

示例

下面的代码显示一个匿名代码块的一部分。这部分代码的功能是通过存储过程COLUMN_VALUE，从游标中接收数据。DECLARE

curid INTEGER;

v_empno NUMBER(4);

v_ename VARCHAR2(10);

v_hiredate DATE;

v_sal NUMBER(7,2);

v_comm NUMBER(7,2);

v_sql VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||

'comm FROM emp';

v_status INTEGER;

BEGIN

LOOP

v_status := DBMS_SQL.FETCH_ROWS(curid);

EXIT WHEN v_status = 0;

DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);

DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);

DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);

DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);

DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);

DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename,10) || ' ' ||

TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||

TO_CHAR(v_sal,'9,999.99') || ' ' ||

TO_CHAR(NVL(v_comm,0),'9,999.99'));

END LOOP;

DBMS_SQL.CLOSE_CURSOR(curid);

END;

COLUMN_VALUE_CHAR

存储过程COLUMN_VALUE_CHAR定义一个变量，用于从游标中接收一个CHAR类型的值。COLUMN_VALUE_CHAR(c INTEGER, position INTEGER, value OUT CHAR

[, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

参数

参数名称

描述

游标的ID，用于向被定义的变量返回数据。

position

返回数据在游标内部的位置。在游标中的第一个位置是1。

value

类型为CHAR的变量，通过前面已经进行fetch操作，从游标中接收数据。

column_error

如果有任何错误发生，这个参数表示与列相关的错误代码。

actual_length

在任何截断的操作前，数据的实际长度。

COLUMN VALUE RAW

存储过程COLUMN_VALUE定义一个变量，用于从游标中接收一个RAW型的值。COLUMN_VALUE_RAW(c INTEGER, position INTEGER, value OUT RAW

[, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

参数

参数名称

描述

游标的ID，用于向被定义变量返回数据。

position

返回数据在游标中位置。在游标中的第一个值是1。

value

类型为RAW的变量，通过前面已经进行fetch操作，从游标中接收数据。

column_error

如果有任何错误发生，这个参数表示与列相关的错误代码。

actual_length

在任何截断的操作前，数据的实际长度。

DEFINE_COLUMN

存储过程DEFINE_COLUMN在SELECT列表中定义了一个列或者表达式，这个列或者表达式将在游标中返回和取出。DEFINE_COLUMN(c INTEGER, position INTEGER, column { BLOB |

CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }

[, column_size INTEGER ])

参数

参数名称

描述

与SELECT命令相关联的游标ID。

position

被定义的列或者表达式在SELECT列表中的位置。

column

在SELECT列表中位置为position的列或者表达式数据类型相等的变量。

column_size

返回数据的最大长度。如果列的数据类型是VARCHAR2, 那么必须指定column_size。长度超过column_size的返回数据将被截断为长度是column_size的字符串。

示例

下面显示了如何使用存储过程DEFINE_COLUMN定义表emp的列empno、ename、hiredate、sal和comm。DECLARE

curid INTEGER;

v_empno NUMBER(4);

v_ename VARCHAR2(10);

v_hiredate DATE;

v_sal NUMBER(7,2);

v_comm NUMBER(7,2);

v_sql VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||

'comm FROM emp';

v_status INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);

DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);

DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);

DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);

DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);

DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

END;

下面显示了针对上个示例的另外一种实现方法，产生的结果完全一样。需要注意的是与数据类型的长度无关。列empno、sal和comm返回的数据长度分别等于NUMBER(4)和NUMBER(7,2)，尽管v_num是被定义为NUMBER(1)(假定在存储过程COLUMN_VALUE中的声明是最大长度)。列ename将返回的数据长度可达到调用存储过程DEFINE_COLUMN中定义的长度参数，而不是对于v_varchar声明中类型VARCHAR2(1)。返回数据的实际长度是由存储过程COLUMN_VALUE来规定的。DECLARE

curid INTEGER;

v_num NUMBER(1);

v_varchar VARCHAR2(1);

v_date DATE;

v_sql VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||

'comm FROM emp';

v_status INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);

DBMS_SQL.DEFINE_COLUMN(curid,1,v_num);

DBMS_SQL.DEFINE_COLUMN(curid,2,v_varchar,10);

DBMS_SQL.DEFINE_COLUMN(curid,3,v_date);

DBMS_SQL.DEFINE_COLUMN(curid,4,v_num);

DBMS_SQL.DEFINE_COLUMN(curid,5,v_num);

END;

DEFINE_COLUMN_CHAR

存储过程DEFINE_COLUMN_CHAR在SELECT列表中定义了一个CHAR类型的列或表达式，您在游标中返回并获取这个CHAR类型的列或表达式。DEFINE_COLUMN_CHAR(c INTEGER, position INTEGER, column CHAR, column_size INTEGER)

参数

参数名称

描述

与SELECT命令相关的游标ID。

position

被定义的列或者表达式在SELECT列表中的位置。

column

一个CHAR类型变量。

column_size

返回数据的最大长度，长度超过column_size的返会数据将被截断为长度是column_size的字符串。

DEFINE COLUMN RAW

存储过程DEFINE_COLUMN_RAW在SELECT列表中定义了一个RAW类型的列或表达式，您在游标中返回并获取这个RAW类型的列或表达式。DEFINE_COLUMN_RAW(c INTEGER, position INTEGER, column RAW,

column_size INTEGER)

参数

参数名称

描述

与SELECT命令相关的游标ID。

position

被定义的列或者表达式在SELECT列表中的位置。

column

一个RAW类型变量。

column_size

返回数据的最大长度，当返回数据的长度超过column_size时，将长度截断到column_size。

DESCRIBE COLUMNS

DESCRIBE_COLUMNS 存储过程用于描述游标返回的列。DESCRIBE_COLUMNS(c INTEGER, col_cnt OUT INTEGER, desc_t OUT

DESC_TAB);

参数

参数名称

描述

游标的ID 。

col_cnt

游标结果集中列的数量。

desc_tab

包含游标所返回的每列的描述的表。描述为DESC_REC 类型，包含值见下表。

Column Name

Type

col_type

INTEGER

col_max_len

INTEGER

col_name

VARCHAR2(128)

col_name_len

INTEGER

col_schema_name

VARCHAR2(128)

col_schema_name_len

INTEGER

col_precision

INTEGER

col_scale

INTEGER

col_charsetid

INTEGER

col_charsetform

INTEGER

col_null_ok

BOOLEAN

EXECUTE

函数EXECUTE用于执行一个已解析SQL语句或SPL代码块。status INTEGER EXECUTE(c INTEGER)

参数

参数名称

描述

要执行的SQL语句或SPL代码块的游标ID，其中SQL命令已解析。

status

如果SQL命令是DELETE、INSERT或UPDATE，那么这个参数代表已处理的记录数。如果是其它命令，那么这个参数没有意义。

示例

下面的匿名代码块向表dept中插入了一条记录。DECLARE

curid INTEGER;

v_sql VARCHAR2(50);

v_status INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

v_sql := 'INSERT INTO dept VALUES (50, ''HR'', ''LOS ANGELES'')';

DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);

v_status := DBMS_SQL.EXECUTE(curid);

DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);

DBMS_SQL.CLOSE_CURSOR(curid);

END;

EXECUTE_AND_FETCH

函数EXECUTE_AND_FETCH用于执行一条已解析的SELECT命令，并且取回一条记录。status INTEGER EXECUTE_AND_FETCH(c INTEGER

[, exact BOOLEAN ])

参数

参数名称

描述

与要执行SELECT命令对应的游标ID。

exact

如果把这个参数设置为”true”，那么当结果集中的记录数不等于1的时候，就会产生一个异常。如果设置为”false”，则不会产生异常。这个参数缺省值是”false”。如果这个参数设置为”true”并且在结果集中没有记录，那么会产生一个NO_DATE_FOUND的异常。而在exact参数为”true”，并且在结果集中有多条记录的情况下，就会产生TOO_MANY_ROWS的异常。

status

如果成功取回一条记录，那么这个参数返回1。如果没有取回记录，那么这个参数返回为0。而如果产生异常的话，则这个参数不返回值。

示例

下面这个存储过程使用函数EXECUTE_AND_FETCH 根据雇员的名称来取出一条雇员记录。如果没有找到相应的雇员记录，或者找到多个具有相同的名字的雇员，那么都会产生异常。CREATE OR REPLACE PROCEDURE select_by_name(

p_ename emp.ename%TYPE

)

curid INTEGER;

v_empno emp.empno%TYPE;

v_hiredate emp.hiredate%TYPE;

v_sal emp.sal%TYPE;

v_comm emp.comm%TYPE;

v_dname dept.dname%TYPE;

v_disp_date VARCHAR2(10);

v_sql VARCHAR2(120) := 'SELECT empno, hiredate, sal, ' ||

'NVL(comm, 0), dname ' ||

'FROM emp e, dept d ' ||

'WHERE ename = :p_ename ' ||

'AND e.deptno = d.deptno';

v_status INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);

DBMS_SQL.BIND_VARIABLE(curid,':p_ename',UPPER(p_ename));

DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);

DBMS_SQL.DEFINE_COLUMN(curid,2,v_hiredate);

DBMS_SQL.DEFINE_COLUMN(curid,3,v_sal);

DBMS_SQL.DEFINE_COLUMN(curid,4,v_comm);

DBMS_SQL.DEFINE_COLUMN(curid,5,v_dname,14);

v_status := DBMS_SQL.EXECUTE_AND_FETCH(curid,TRUE);

DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);

DBMS_SQL.COLUMN_VALUE(curid,2,v_hiredate);

DBMS_SQL.COLUMN_VALUE(curid,3,v_sal);

DBMS_SQL.COLUMN_VALUE(curid,4,v_comm);

DBMS_SQL.COLUMN_VALUE(curid,5,v_dname);

v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY');

DBMS_OUTPUT.PUT_LINE('Number : ' || v_empno);

DBMS_OUTPUT.PUT_LINE('Name : ' || UPPER(p_ename));

DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date);

DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);

DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm);

DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname);

DBMS_SQL.CLOSE_CURSOR(curid);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Employee ' || p_ename || ' not found');

DBMS_SQL.CLOSE_CURSOR(curid);

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('Too many employees named, ' ||

p_ename || ', found');

DBMS_SQL.CLOSE_CURSOR(curid);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');

DBMS_OUTPUT.PUT_LINE(SQLERRM);

DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');

DBMS_OUTPUT.PUT_LINE(SQLCODE);

DBMS_SQL.CLOSE_CURSOR(curid);

END;

EXEC select_by_name('MARTIN')

Number : 7654

Name : MARTIN

Hire Date : 09/28/1981

Salary : 1250

Commission: 1400

Department: SALES

FETCH_ROWS

函数FETCH_ROWS从一个游标中获取一条记录。status INTEGER FETCH_ROWS(c INTEGER)

参数

参数名称

描述

用于获取记录的游标ID。

status

如果成功获取一条记录，这个参数返回1，如果没有取回记录，那么返回0。

示例

在下面的示例中，您从表emp中取出记录，并且显示结果。DECLARE

curid INTEGER;

v_empno NUMBER(4);

v_ename VARCHAR2(10);

v_hiredate DATE;

v_sal NUMBER(7,2);

v_comm NUMBER(7,2);

v_sql VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||

'comm FROM emp';

v_status INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);

DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);

DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);

DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);

DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);

DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

v_status := DBMS_SQL.EXECUTE(curid);

DBMS_OUTPUT.PUT_LINE('EMPNO ENAME HIREDATE SAL COMM');

DBMS_OUTPUT.PUT_LINE('----- ---------- ---------- -------- ' ||

'--------');

LOOP

v_status := DBMS_SQL.FETCH_ROWS(curid);

EXIT WHEN v_status = 0;

DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);

DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);

DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);

DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);

DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);

DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename,10) || ' ' ||

TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||

TO_CHAR(v_sal,'9,999.99') || ' ' ||

TO_CHAR(NVL(v_comm,0),'9,999.99'));

END LOOP;

DBMS_SQL.CLOSE_CURSOR(curid);

END;

EMPNO ENAME HIREDATE SAL COMM

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

7369 SMITH 1980-12-17 800.00 .00

7499 ALLEN 1981-02-20 1,600.00 300.00

7521 WARD 1981-02-22 1,250.00 500.00

7566 JONES 1981-04-02 2,975.00 .00

7654 MARTIN 1981-09-28 1,250.00 1,400.00

7698 BLAKE 1981-05-01 2,850.00 .00

7782 CLARK 1981-06-09 2,450.00 .00

7788 SCOTT 1987-04-19 3,000.00 .00

7839 KING 1981-11-17 5,000.00 .00

7844 TURNER 1981-09-08 1,500.00 .00

7876 ADAMS 1987-05-23 1,100.00 .00

7900 JAMES 1981-12-03 950.00 .00

7902 FORD 1981-12-03 3,000.00 .00

7934 MILLER 1982-01-23 1,300.00 .00

IS_OPEN

函数IS_OPEN用于测试是否已打开一个游标。status BOOLEAN IS_OPEN(c INTEGER)

参数

参数名称

描述

要测试游标的ID。

status

如果游标是打开状态，这个参数设为TRUE，如果游标没有打开，那么这个值设为FALSE。

LAST ROW COUNT

函数LAST_ROW_COUNT返回当前已取回记录的总数。rowcnt INTEGER LAST_ROW_COUNT

参数

参数名称

描述

rowcnt

已取回记录的总数。

示例

在下面的示例中，您使用函数LAST_ROW_COUNT显示了查询中已取回记录的总数。DECLARE

curid INTEGER;

v_empno NUMBER(4);

v_ename VARCHAR2(10);

v_hiredate DATE;

v_sal NUMBER(7,2);

v_comm NUMBER(7,2);

v_sql VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||

'comm FROM emp';

v_status INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);

DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);

DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);

DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);

DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);

DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

v_status := DBMS_SQL.EXECUTE(curid);

DBMS_OUTPUT.PUT_LINE('EMPNO ENAME HIREDATE SAL COMM');

DBMS_OUTPUT.PUT_LINE('----- ---------- ---------- -------- ' ||

'--------');

LOOP

v_status := DBMS_SQL.FETCH_ROWS(curid);

EXIT WHEN v_status = 0;

DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);

DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);

DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);

DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);

DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);

DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename,10) || ' ' ||

TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||

TO_CHAR(v_sal,'9,999.99') || ' ' ||

TO_CHAR(NVL(v_comm,0),'9,999.99'));

END LOOP;

DBMS_OUTPUT.PUT_LINE('Number of rows: ' || DBMS_SQL.LAST_ROW_COUNT);

DBMS_SQL.CLOSE_CURSOR(curid);

END;

EMPNO ENAME HIREDATE SAL COMM

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

7369 SMITH 1980-12-17 800.00 .00

7499 ALLEN 1981-02-20 1,600.00 300.00

7521 WARD 1981-02-22 1,250.00 500.00

7566 JONES 1981-04-02 2,975.00 .00

7654 MARTIN 1981-09-28 1,250.00 1,400.00

7698 BLAKE 1981-05-01 2,850.00 .00

7782 CLARK 1981-06-09 2,450.00 .00

7788 SCOTT 1987-04-19 3,000.00 .00

7839 KING 1981-11-17 5,000.00 .00

7844 TURNER 1981-09-08 1,500.00 .00

7876 ADAMS 1987-05-23 1,100.00 .00

7900 JAMES 1981-12-03 950.00 .00

7902 FORD 1981-12-03 3,000.00 .00

7934 MILLER 1982-01-23 1,300.00 .00

Number of rows: 14

OPEN_CURSOR

函数OPEN_CURSOR用于创建一个新的游标。您必须使用一个游标来解析和执行动态SQL语句。当打开游标后，可以以相同或不同的SQL语句来重复使用这个游标。您不需要通过关闭然后重新打开的方式实现对游标进行重用。c INTEGER OPEN_CURSOR

参数

参数名称

描述

与新创建的游标相关联的游标ID。

示例

在下面的示例中创建了一个新的游标：DECLARE

curid INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

END;

PARSE

存储过程PARSE用于解析一条SQL命令或SPL代码块。如果SQL语句是一条DDL命令，那么将立即执行这条命令，而不要求运行函数EXECUTE。PARSE(c INTEGER, statement VARCHAR2, language_flag INTEGER)

参数

参数名称

描述

一个已打开游标的ID。

statement

已解析的SQL命令或SPL代码块。SQL命令不能以分号结束，而一个SPL代码块则要求以分号结束。

language_flag

这个参数是为了与Oracle语法兼容而提供的。使用DBMS_SQL.V6、DBMS_SQL.V7 或DBMS_SQL.native，实际上您可以忽略这个标志，所有的语法都假定是以PolarDB-O的形式存在的。

示例

下面的匿名代码块创建了名称为job的表。需要注意的是DDL语句是由存储过程PARSE立即执行，不要求单独运行函数EXECUTE这个步骤。DECLARE

curid INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(curid, 'CREATE TABLE job (jobno NUMBER(3), ' ||

'jname VARCHAR2(9))',DBMS_SQL.native);

DBMS_SQL.CLOSE_CURSOR(curid);

END;

下面向表job中插入两条记录。DECLARE

curid INTEGER;

v_sql VARCHAR2(50);

v_status INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

v_sql := 'INSERT INTO job VALUES (100, ''ANALYST'')';

DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);

v_status := DBMS_SQL.EXECUTE(curid);

DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);

v_sql := 'INSERT INTO job VALUES (200, ''CLERK'')';

DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);

v_status := DBMS_SQL.EXECUTE(curid);

DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);

DBMS_SQL.CLOSE_CURSOR(curid);

END;

Number of rows processed: 1

下面的匿名代码块使用包DBMS_SQL执行了包含2条INSERT语句的代码块。需要注意的是在代码块结束的地方包含一个分号结束符，而在前面的一个示例中，每个单独的INSERT语句没有分号结束符。DECLARE

curid INTEGER;

v_sql VARCHAR2(100);

v_status INTEGER;

BEGIN

curid := DBMS_SQL.OPEN_CURSOR;

v_sql := 'BEGIN ' ||

'INSERT INTO job VALUES (300, ''MANAGER''); ' ||

'INSERT INTO job VALUES (400, ''SALESMAN''); ' ||

'END;';

DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);

v_status := DBMS_SQL.EXECUTE(curid);

DBMS_SQL.CLOSE_CURSOR(curid);

END;

oracle dbms_sql.describe_columns,DBMS_SQL