羲和（Halo）数据库——DBMS_SQL浅尝

数据运维 2024-01-06 大猫 手机阅读

本文将向大家展示如何在羲和（Halo）数据库中使用DBMS_SQL，本文测试案例除部分表结构和部分测试数据之外，其余均来自ORACLE官方测试文档，所有测试案例放置文章末尾以供学习使用。



下面简单介绍一下本文中所使用到的DBMS_SQL部分接口，如果查看更为详细的介绍，建议参考ORACLE官方文档。

  • OPEN_CURSOR：要处理SQL语句，必须有一个打开的游标。通过调用OPEN_CURSOR ，获取一个数据库内部的维护游标编号。当不再需要使用的时候，需要调用CLOSE_CURSOR关闭

  • c := DBMS_SQL.OPEN_CURSOR;

  • PARSE：解析待执行的动态SQL语句，检查语句语法是否存在问题，并将其与程序中的游标进行关联。

  • DBMS_SQL.PARSE(c, 'INSERT INTO tab VALUES (:bnd1, :bnd2) ' || 'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE);

  • BIND_VARIABLE：用于将特定的值或变量与待执行的SQL语句中的占位符关联起来。

  • DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1);

  • BIND_ARRAY：用于将数组变量与待执行的SQL 语句中的占位符关联起来。

  • DBMS_SQL.BIND_ARRAY(c, 'bnd3', r);

  • DEFINE_COLUMN：用于定义待执行的SELECT语句的最终返回结果集中的列。

  • DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var);

  • EXECUTE：用于执行已解析SQL。

  • n := DBMS_SQL.EXECUTE(c);

  • FETCH_ROWS：用于从指定游标中获取一行数据。

  • IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN -- get column values of the row

  • EXECUTE_AND_FETCH：用于执行已解析的查询SQL，并获取一行数据。

  • r := DBMS_SQL.EXECUTE_AND_FETCH(c);

  • VARIABLE_VALUE：用于从带有RETURNING的SQL语句中获取绑定变量的返回数据。

  • DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);

  • COLUMN_VALUE：用于获取查询结果集中指定列的数据。

  • DBMS_SQL.COLUMN_VALUE(c, 1, some_dnames);

  • CLOSE_CURSOR：终章！关闭游标。

  • DBMS_SQL.CLOSE_CURSOR(c);


  • single_Row_update：将数据更新成5，5。

  • single_Row_Delete: 删除一行数据

  • Multiple-row insert: 往c1中插入10、20、30，c2中插入11、21、31

为multi_Row_update做准备，再次运行multi_Row_insert插入重复数据。

  • multi_Row_update：将c2为11，对应的c1更新成100


  • Multiple-row delete：多行删除

  • exec：支持运行DDL和没有绑定参数的DML语句。


  • copy：将源表中的数据复制到目标表，这个存储过程有助于帮助大家理解和使用DBMS_SQL。


有点长...

至此，对于DBMS_SQL的相关简单演示就到此结束了。若文中存在错误或不当之处，敬请指出，以便我进行修正和完善。本文所有测试案例在文末供大家在ORACLE数据库上学习使用。


四、相关网站链接

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQL.html#GUID-E9BAA1FD-DBAC-453F-8674-162B10133505


五、测试SQL语句本文所用SQL语句如下：

-- 单行插入 CREATE TABLE tab(c1 NUMBER, c2 NUMBER); -- 依据ORACLE测试案例 创建tab表</p> <p>CREATE OR REPLACE PROCEDURE single_Row_insert (c1 NUMBER, c2 NUMBER, r OUT NUMBER) is c NUMBER; n NUMBER; begin c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'INSERT INTO tab VALUES (:bnd1, :bnd2) ' || 'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1); DBMS_SQL.BIND_VARIABLE(c, 'bnd2', c2); DBMS_SQL.BIND_VARIABLE(c, 'bnd3', r); n := DBMS_SQL.EXECUTE(c); DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r); -- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; /</p> <p>declare c3 number(7,2); begin single_Row_insert(5, 10, c3); dbms_output.put_line('c3 = '|| c3); end; /</p> <p>select * from tab;</p> <p>-- 单行更新 CREATE OR REPLACE PROCEDURE single_Row_update (c1 NUMBER, c2 NUMBER, r out NUMBER) IS c NUMBER; n NUMBER; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'UPDATE tab SET c1 = :bnd1, c2 = :bnd2 ' || 'WHERE rownum < 2&#039; || &#039;RETURNING c1*c2 INTO :bnd3&#039;, DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(c, &#039;bnd1&#039;, c1); DBMS_SQL.BIND_VARIABLE(c, &#039;bnd2&#039;, c2); DBMS_SQL.BIND_VARIABLE(c, &#039;bnd3&#039;, r); n := DBMS_SQL.EXECUTE(c); DBMS_SQL.VARIABLE_VALUE(c, &#039;bnd3&#039;, r);-- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; /</p> <p>-- 调用single_Row_update declare c3 number(7,2); begin single_Row_update(5, 5, c3);<br /> dbms_output.put_line(&#039;c3 = &#039;|| c3); end; / -- 查看数据是否更新成功 select * from tab;</p> <p>-- 单行删除 CREATE OR REPLACE PROCEDURE single_Row_Delete (c1 NUMBER, r OUT NUMBER) is c NUMBER; n number; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, &#039;DELETE FROM tab WHERE ROWNUM = :bnd1 &#039; || &#039;RETURNING c1*c2 INTO :bnd2&#039;, DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(c, &#039;bnd1&#039;, c1); DBMS_SQL.BIND_VARIABLE(c, &#039;bnd2&#039;, r); n := DBMS_SQL.EXECUTE(c); DBMS_SQL.VARIABLE_VALUE(c, &#039;bnd2&#039;, r);-- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; /</p> <p>-- 调用single_Row_Delete declare c3 number(7,2); begin single_Row_Delete(1, c3); -- ROWNUM = 1 删除一行数据 dbms_output.put_line(&#039;c3 = &#039;|| c3); end; / -- 查看删除结果 select * from tab;

-- 插入多行 CREATE OR REPLACE PROCEDURE multi_Row_insert (c1 DBMS_SQL.NUMBER_TABLE, c2 DBMS_SQL.NUMBER_TABLE, r OUT DBMS_SQL.NUMBER_TABLE) is c NUMBER; n NUMBER; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'insert into tab VALUES (:bnd1, :bnd2) ' || 'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY(c, 'bnd1', c1); DBMS_SQL.BIND_ARRAY(c, 'bnd2', c2); DBMS_SQL.BIND_ARRAY(c, 'bnd3', r); n := DBMS_SQL.EXECUTE(c); DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; / -- 调用multi_Row_insert DECLARE my_number_table DBMS_SQL.NUMBER_TABLE; my_number_table2 DBMS_SQL.NUMBER_TABLE; my_number_table3 DBMS_SQL.NUMBER_TABLE; BEGIN my_number_table(1) := 10; my_number_table(2) := 20; my_number_table(3) := 30; my_number_table2(1) := 11; my_number_table2(2) := 21; my_number_table2(3) := 31; multi_Row_insert(my_number_table,my_number_table2,my_number_table3); -- 查看my_number_table3数据 for i in 1 .. my_number_table3.count loop dbms_output.put_line('c1*c2 = ' || my_number_table3(i)); end loop; END; / -- 查看数据是否插入成功 select * from tab; -- 再次调用multi_Row_insert 为multi_Row_update做准备 DECLARE my_number_table DBMS_SQL.NUMBER_TABLE; my_number_table2 DBMS_SQL.NUMBER_TABLE; my_number_table3 DBMS_SQL.NUMBER_TABLE; BEGIN my_number_table(1) := 10; my_number_table(2) := 20; my_number_table(3) := 30; my_number_table2(1) := 11; my_number_table2(2) := 21; my_number_table2(3) := 31; multi_Row_insert(my_number_table,my_number_table2,my_number_table3); -- 查看my_number_table3数据 for i in 1 .. my_number_table3.count loop dbms_output.put_line('c1*c2 = ' || my_number_table3(i)); end loop; END; / -- 查看数据 select * from tab; -- 多行更新 CREATE OR REPLACE PROCEDURE multi_Row_update (c1 NUMBER, c2 NUMBER, r OUT DBMS_SQL.NUMBER_TABLE) IS c NUMBER; n NUMBER; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'UPDATE tab SET c1 = :bnd1 WHERE c2 = :bnd2 ' || 'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1); DBMS_SQL.BIND_VARIABLE(c, 'bnd2', c2); DBMS_SQL.BIND_ARRAY(c, 'bnd3', r); n := DBMS_SQL.EXECUTE(c); DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; / -- 调用multi_Row_update DECLARE my_number_table3 DBMS_SQL.NUMBER_TABLE; BEGIN multi_Row_update(100, 11, my_number_table3); for i in 1 .. my_number_table3.count loop dbms_output.put_line(' cur value := ' || my_number_table3(i)); end loop; END; / -- 查看是否更新成功 select * from tab; -- 多行删除 CREATE OR REPLACE PROCEDURE multi_row_delete (c1 DBMS_SQL.NUMBER_TABLE, r OUT DBMS_SQL.NUMBER_TABLE) is c NUMBER; n NUMBER; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'DELETE FROM tab WHERE c1 = :bnd1 ' || 'RETURNING c1*c2 INTO :bnd2', DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY(c, 'bnd1', c1); DBMS_SQL.BIND_ARRAY(c, 'bnd2', r); n := DBMS_SQL.EXECUTE(c); DBMS_SQL.VARIABLE_VALUE(c, 'bnd2', r);-- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; / -- 调用multi_Row_delete DECLARE my_number_table DBMS_SQL.NUMBER_TABLE; my_number_table3 DBMS_SQL.NUMBER_TABLE; BEGIN my_number_table(1) := 100; my_number_table(2) := 20; my_number_table(3) := 30; multi_Row_delete(my_number_table, my_number_table3); for i in 1 .. my_number_table3.count loop dbms_output.put_line('c1*c2 = ' || my_number_table3(i)); end loop; END; / -- 查看是否删除成功 select * from tab;