DBMS_SQL包提供查询 PolarDB PostgreSQL版(兼容Oracle) 动态SQL的功能,可以在应用运行时构建查询。
PolarDB PostgreSQL版(兼容Oracle) 支持查询动态SQL,且以与Oracle兼容的方式来使用动态SQL。
函数/存储过程 | 类型 | 返回类型 | 说明 |
---|---|---|---|
BIND_VARIABLE(c, name, value [, out_value_size ]) | 存储过程 | N/A | 将值绑定到变量。 |
BIND_VARIABLE_CHAR(c, name, value [, out_value_size ]) | 存储过程 | N/A | 将CHAR值绑定到变量。 |
BIND_VARIABLE_RAW(c, name, value [, out_value_size ]) | 存储过程 | N/A | 将RAW值绑定到变量。 |
CLOSE_CURSOR(c IN OUT) | 存储过程 | N/A | 关闭游标。 |
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) | 存储过程 | N/A | 将列值返回到变量中。 |
COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) | 存储过程 | N/A | 将CHAR列值返回到变量中。 |
COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) | 存储过程 | N/A | 将RAW列值返回到变量中。 |
DEFINE_COLUMN(c, position, column [, column_size ]) | 存储过程 | N/A | 在SELECT列表中定义一列。 |
DEFINE_COLUMN_CHAR(c, position, column, column_size) | 存储过程 | N/A | 在SELECT列表中定义CHAR列。 |
DEFINE_COLUMN_RAW(c, position, column, column_size) | 存储过程 | N/A | 在SELECT列表中定义RAW列。 |
DEFINE_ARRAY(c,position,table_variable,cnt, lower_bnd) | 存储过程 | N/A | 将需要获取行的列定义为数组。 |
DESCRIBE_COLUMNS | 存储过程 | N/A | 定义用于保存游标结果集的列。 |
EXECUTE(c) | 函数 | INTEGER | 执行游标。 |
EXECUTE_AND_FETCH(c [, exact ]) | 函数 | INTEGER | 执行一个游标并获取一行。 |
FETCH_ROWS(c) | 函数 | INTEGER | 从游标中获取的行数。 |
IS_OPEN(c) | 函数 | BOOLEAN | 查看游标是否打开。 |
LAST_ROW_COUNT | 函数 | INTEGER | 返回获取的累计行数。 |
OPEN_CURSOR | 函数 | INTEGER | 打开游标。 |
PARSE(c, statement, language_flag) | 存储过程 | N/A | 解析语句。 |
与Oracle版本的DBMS_SQL执行相比, PolarDB 的DBMS_SQL执行是部分执行。 PolarDB 仅支持上述表中列出的函数和存储过程。
下表中列出了DBM_SQL包允许使用的公共变量。
公共变量 | 数据类型 | 取值 | 说明 |
---|---|---|---|
native | INTEGER | 1 | 与Oracle语法兼容。更多信息请参见 DBMS_SQL.PARSE 。 |
V6 | INTEGER | 2 | 与Oracle语法兼容。更多信息请参见 DBMS_SQL.PARSE 。 |
V7 | INTEGER | 3 | 与Oracle语法兼容。更多信息请参见 DBMS_SQL.PARSE 。 |
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 ])
参数
参数名称 | 描述 |
---|---|
c | 与带有绑定变量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 ])
参数
参数名称 | 描述 |
---|---|
c | 与带有绑定变量的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 ])
参数
参数名称 | 描述 |
---|---|
c | 与带有绑定变量的SQL命令相对应的游标ID。 |
name | SQL命令中绑定变量的名称。 |
value | 被分配的类型为RAW的值。 |
out_value_size |
如果
name
是一个IN OUT模式的变量,那么定义输出值的最大长度。如果没有指定这个参数,那么将当前值的长度默认为最大值长度。
|
CLOSE_CURSOR
存储过程
CLOSE_CURSOR
关闭一个已打开的游标。当关闭游标后,释放分配给游标的资源,并且不能再使用这个游标。
CLOSE_CURSOR(c IN OUT INTEGER)
参数
参数名称 | 描述 |
---|---|
c | 需要关闭游标的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 ]])
参数
参数名称 | 描述 |
---|---|
c | 一个游标的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
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,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 ]])
参数
参数名称 | 描述 |
---|---|
c | 游标的ID,用于向被定义的变量返回数据。 |
position | 返回数据在游标内部的位置。在游标中的第一个位置是1。 |
value | 类型为CHAR的变量,通过前面已经进行FETCH操作,从游标中接收数据。 |
column_error | 如果有任何错误发生,这个参数表示与列相关的错误代码。 |
actual_length | 在任何截断的操作前,数据的实际长度。 |
COLUMN_VALUE_RAW
存储过程
COLUMN_VALUE_RAW
定义一个变量,用于从游标中接收一个RAW型的值。
COLUMN_VALUE_RAW(c INTEGER, position INTEGER, value OUT RAW
[, column_error OUT NUMBER [, actual_length OUT INTEGER ]])
参数
参数名称 | 描述 |
---|---|
c | 游标的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 ])
参数
参数名称 | 描述 |
---|---|
c | 与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)
。列
ename
将返回的数据长度可达到调用存储过程
DEFINE_COLUMN
中定义的长度参数,而不是对于
v_varchar
声明中类型
VARCHAR2(1)
。返回数据的实际长度是由存储过程
DEFINE_COLUMN
指定。
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)
参数
参数名称 | 描述 |
---|---|
c | 与SELECT命令相关的游标ID。 |
position | 被定义的列或者表达式在SELECT列表中的位置。 |
column | 一个CHAR类型变量。 |
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)
参数
参数名称 | 描述 |
---|---|
c | 与SELECT命令相关的游标ID。 |
position | 被定义的列或者表达式在SELECT列表中的位置。 |
column | 一个RAW类型变量。 |
column_size | 返回数据的最大长度,当返回数据的长度超过 column_size 时,将会被截断。 |
DEFINE_ARRAY
DEFINE_ARRAY
存储过程将需要获取行的列定义为数组。语法如下:
DEFINE_ARRAY (
c IN INTEGER,
position IN INTEGER,
<table_variable> IN <datatype>,
cnt IN INTEGER,
lower_bnd IN INTEGER);
参数名称 | 描述 |
---|---|
c | 需要绑定数组的游标的ID。 |
position | 列在数组中的相对位置。 |
table_variable |
已定义为<datatype>类型的变量。其中,<datatype>类型的取值如下:
|
cnt | 获取的行的数量。必须为大于0的整数。 |
lower_bnd | 从此下限索引开始将查询结果复制到数组中。 |
示例
t
,并从表
t
中获取2行数据。
create table t as select i as a,2 * i as b,3 * i as c from generate_series(1,3) i;
DECLARE
c INTEGER;
d NUMBER;
n_tab dbms_sql.varchar2_Table;
n_tab1 dbms_sql.varchar2_Table;
BEGIN
c := dbms_sql.open_cursor;
dbms_sql.parse(c,
'select * from t',
dbms_sql.native);
dbms_sql.define_array(c,1,n_tab,2,1);
d := dbms_sql.execute(c);
d := dbms_sql.fetch_rows(c);
dbms_output.put_line('fetch rows is ' || d);
dbms_sql.column_value(c,
n_tab1);
FOR i IN 1 .. d LOOP
dbms_output.put_line(n_tab1(i));
END LOOP;
dbms_sql.close_cursor(c);
END;
显示结果如下:
fetch rows is 2
2
DESCRIBE_COLUMNS
DESCRIBE_COLUMNS
存储过程用于描述游标返回的列。
DESCRIBE_COLUMNS(c INTEGER, col_cnt OUT INTEGER, desc_t OUT
DESC_TAB);
参数
参数名称 | 描述 |
---|---|
c | 游标的ID 。 |
col_cnt | 游标结果集中列的数量。 |
desc_tab | 包含游标所返回的每列的描述的表。描述为DESC_REC类型,包含值请参见 下表 。 |
列名称 | 类型 |
---|---|
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)
参数
参数名称 | 描述 |
---|---|
c | 需要执行的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 ])
参数
参数名称 | 描述 |
---|---|
c | 执行SELECT命令对应的游标ID。 |
exact |
|
status |
|
示例
以下存储过程使用函数
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);
显示结果如下:
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)
参数
参数名称 | 描述 |
---|---|
c | 用于获取行的游标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('----- ---------- ---------- -------- ' ||
'--------');
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,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)
参数
参数名称 | 描述 |
---|---|
c | 待测试游标的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('----- ---------- ---------- -------- ' ||
'--------');
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,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
参数
参数名称 | 描述 |
---|---|
c | 与新创建的游标相关联的游标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)
参数
参数名称 | 描述 |
---|---|
c | 一个已打开游标的ID。 |
statement | 已解析的SQL命令或SPL代码块。SQL命令不能以分号结束,而一个SPL代码块则要求以分号结束。 |
language_flag | 这个参数是为了与Oracle语法兼容而提供的。使用DBMS_SQL.V6、DBMS_SQL.V7 或DBMS_SQL.native,实际上您可以忽略这个标志,所有的语法都假定是以 PolarDB PostgreSQL版(兼容Oracle) 的形式存在的。 |
示例
下面的匿名代码块创建了名称为
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);
显示结果如下:
Number of rows processed: 1
Number of rows processed: 1
以下的匿名代码块使用DBMS_SQL包执行了包含2条INSERT语句的代码块。需要注意的是在代码块结束的地方包含一个分号结束符,而在 OPEN_CURSOR 中的示例中,每个单独的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);