DBMS_SQL包¶
包DBMS_SQL对动态SQL查询功能提供一个与Oracle兼容的应用程序接口。
类型：
下面的表中列出了DBMS_SQL包中的扩展类型：
| 类型 | 描述 |
|---|---|
| NUMBER_TABLE | 元素为NUMBER类型的索引集合 |
| DATE_TABLE | 元素为DATE类型的索引集合 |
过程/函数：
下表展示了DBMS_SQL包的子程序。
| 子程序 | 描述 | 过程/函数 |
|---|---|---|
| OPEN_CURSOR | 打开一个游标 | 函数 |
| PARSE | 解析一条语句 | 过程 |
| EXECUTE | 执行一个游标 | 函数 |
| CLOSE_CURSOR | 关闭一个游标 | 过程 |
| FETCH_ROWS | 获取SELECT语句的一行结果 | 函数 |
| IS_OPEN | 给定游标是否打开，是返回TRUE | 函数 |
| DESCRIBE_COLUMNS | 描述打开和解析的游标的列 | 过程 |
| DEFINE_COLUMN | 在select列表中定义一个列 | 过程 |
| COLUMN_VALUE | 将一个列的值返回到变量中 | 过程 |
| BIND_VARIABLE | 将一个值绑定在一个变量上 | 过程 |
| LAST_ERROR_POSITION | 返回发生错误字符位置 | 过程 |
以下介绍了各种函数和过程的语法、参数和说明。
示例
--DBMS_SQL.NUMBER_TABLE示例
DECLARE
n DBMS_SQL.NUMBER_TABLE;
BEGIN
n(1) := 2; --按下标给集合中元素赋值，元素为NUMBER类型
n(2) := 3;
for i in 1 .. n.count loop
DBMS_OUTPUT.PUT_LINE(n(i));
end loop;
END;
/
--DBMS_SQL.DATE_TABLE示例
DECLARE
d DBMS_SQL.DATE_TABLE;
BEGIN
d(1) := to_date('2022.12.7'); --按下标给集合中元素赋值，元素为DATE类型
d(2) := to_date('2022.12.8');
for i in 1 .. d.count loop
DBMS_OUTPUT.PUT_LINE(d(i));
end loop;
END;
/
--子程序示例
DROP TABLE phone_infor;--清理环境
create table phone_infor(phone_name VARCHAR(20),producer VARCHAR(20),price number);
CREATE OR REPLACE PROCEDURE p_1 IS
v_cursor INTEGER;
sqlstring VARCHAR2(200);
v_phone_name VARCHAR(20);
v_producer VARCHAR(20);
v_price INTEGER :=500;
v_count INT;
BEGIN
sqlstring :='INSERT INTO phone_infor values(:a,:b,:c)';
v_phone_name := 'TEST';
v_producer := 'sony';
v_price := 999;
v_cursor :=dbms_sql.open_cursor;
dbms_sql.parse(v_cursor ,sqlstring,dbms_sql.native);
dbms_sql.bind_variable(v_cursor,'a',v_phone_name,4);
dbms_sql.bind_variable(v_cursor,'b',v_producer);
dbms_sql.bind_variable(v_cursor,'c',v_price);
v_count := dbms_sql.EXECUTE(v_cursor);
dbms_sql.close_cursor(v_cursor);
dbms_output.put_line(' INSERT ' || to_char(v_count) ||' row ');
COMMIT;
END;
/
exec p_1;
select * from phone_infor;
PHONE_NAME(varchar) |PRODUCER(varchar) |PRICE(numeric) |
--------------------------------------------------------------------
TEST |sony |999 |
总数目：1
drop table phone_infor;
drop procedure p_1 cascade;
drop procedure equal_int cascade;
create or replace procedure equal_int(a integer, b integer) is
error_sql varchar(1000);
begin
if a <> b then
error_sql := 'error: '||a||' != '||b;
RAISE_APPLICATION_ERROR(-20010, error_sql);
end if;
end;
/
drop procedure equal_var cascade;
create or replace procedure equal_var(a varchar2, b varchar2) is
error_sql varchar(2000);
v1 varchar2(1000) := trim(a);
v2 varchar2(1000) := trim(b);
begin
if v1 <> v2 then
error_sql := 'error: '||a||' != '||b;
RAISE_APPLICATION_ERROR(-20010, error_sql);
end if;
end;
/
drop table test cascade;
create table test(c1 int, c2 int2, c3 int8, c4 number, c5 decimal, c6 float, c7 varchar(10));
insert into test values(1, 2, 3, 4, 5, 6.0, 'var7');
CREATE OR REPLACE PROCEDURE p_2 IS
sql_id INTEGER;
sql_stmt VARCHAR2(600);
v1 INT := -1;
v2 INT2 := -1;
v3 INT8 := -1;
v4 NUMBER := -1;
v5 DECIMAL := -1;
v6 FLOAT := -1;
v7 VARCHAR(20) := '';
BEGIN
sql_id := DBMS_SQL.OPEN_CURSOR();--打开游标
sql_stmt := 'select * from test';
DBMS_SQL.PARSE(sql_id, sql_stmt, DBMS_SQL.NATIVE); --解析语句
DBMS_SQL.DEFINE_COLUMN(sql_id, 1, v1); ----定义列
DBMS_SQL.DEFINE_COLUMN(sql_id, 2, v2);
DBMS_SQL.DEFINE_COLUMN(sql_id, 3, v3);
DBMS_SQL.DEFINE_COLUMN(sql_id, 4, v4);
DBMS_SQL.DEFINE_COLUMN(sql_id, 5, v5);
DBMS_SQL.DEFINE_COLUMN(sql_id, 6, v6);
DBMS_SQL.DEFINE_COLUMN(sql_id, 7, v7, 20);
equal_int(DBMS_SQL.EXECUTE(sql_id), 0); --执行sql语句
equal_int(DBMS_SQL.FETCH_ROWS(sql_id), 1);--fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。
DBMS_SQL.COLUMN_VALUE(sql_id, 1, v1); --将当前行的查询结果写入上面定义的列中。
DBMS_SQL.COLUMN_VALUE(sql_id, 2, v2);
DBMS_SQL.COLUMN_VALUE(sql_id, 3, v3);
DBMS_SQL.COLUMN_VALUE(sql_id, 4, v4);
DBMS_SQL.COLUMN_VALUE(sql_id, 5, v5);
DBMS_SQL.COLUMN_VALUE(sql_id, 6, v6);
DBMS_SQL.COLUMN_VALUE(sql_id, 7, v7);
equal_int(v1, 1);
equal_int(v2, 2);
equal_int(v3, 3);
equal_int(v4, 4);
equal_int(v5, 5);
equal_int(v6, 6);
equal_var(v7, 'var7');
DBMS_SQL.CLOSE_CURSOR(sql_id);--关闭游标
END;
/
exec p_2;
drop table test cascade;
drop procedure equal_int cascade;
drop procedure equal_var cascade;
drop procedure p_2 cascade;