Oracle动态执行SQL四种方式的例子

简介: 方式1 CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS     cursor_name INTEGER;     rows_processed INTEGER; BEGIN     cursor_name := dbms_sql.

方式1
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
    cursor_name INTEGER;
    rows_processed INTEGER;
BEGIN
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name, 'DELETE FROM emp WHERE sal > :x',
                   dbms_sql);
    dbms_sql.bind_variable(cursor_name, ':x', salary);
    rows_processed := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
    dbms_sql.close_cursor(cursor_name);
END;

CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS
    cursor_name INTEGER;
    ret INTEGER;
BEGIN
   cursor_name := DBMS_SQL.OPEN_CURSOR;
 
   --DDL statements are executed by the parse call, which
   --performs the implied commit
   DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL);
   ret := DBMS_SQL.EXECUTE(cursor_name);
   DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

execute immediate "drop table tab_temp";

方式2

CREATE OR REPLACE PROCEDURE copy(source      IN VARCHAR2,
                                 destination IN VARCHAR2) is

-- This procedure copies rows from a given source table to a
-- given destination table assuming that both source and
-- destination tables have the following columns:
--   - ID of type NUMBER,
--   - NAME of type VARCHAR2(30),
--   - BIRTHDATE of type DATE.
  id                 NUMBER;
  name               VARCHAR2(30);
  birthdate          DATE;
  source_cursor      INTEGER;
  destination_cursor INTEGER;
  ignore             INTEGER;
BEGIN

  -- prepare a cursor to select from the source table
  source_cursor := dbms_sql.open_cursor;
  DBMS_SQL.PARSE(source_cursor,
       'SELECT id, name, birthdate FROM ' || source,
        DBMS_SQL);
  DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id);
  DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name, 30);
  DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate);
  ignore := DBMS_SQL.EXECUTE(source_cursor);

  -- prepare a cursor to insert into the destination table
  destination_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(destination_cursor,
                'INSERT INTO ' || destination ||
                ' VALUES (:id, :name, :birthdate)',
                 DBMS_SQL);

  -- fetch a row from the source table and
  -- insert it into the destination table
  LOOP
    IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
      -- get column values of the row
      DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id);
      DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name);
      DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate);
   
      -- bind the row into the cursor that inserts into the
      -- destination table
      -- You could alter this example to require the use of
      -- dynamic SQL by inserting an if condition before the
      -- bind.
      DBMS_SQL.BIND_VARIABLE(destination_cursor, 'id', id);
      DBMS_SQL.BIND_VARIABLE(destination_cursor, 'name', name);
      DBMS_SQL.BIND_VARIABLE(destination_cursor, 'birthdate',
                             birthdate);
      ignore := DBMS_SQL.EXECUTE(destination_cursor);
    ELSE
 
    -- no more row to copy
      EXIT;
    END IF;
  END LOOP;

  -- commit and close all cursors
  COMMIT;
  DBMS_SQL.CLOSE_CURSOR(source_cursor);
  DBMS_SQL.CLOSE_CURSOR(destination_cursor);

EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(source_cursor) THEN
      DBMS_SQL.CLOSE_CURSOR(source_cursor);
    END IF;
    IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
      DBMS_SQL.CLOSE_CURSOR(destination_cursor);
    END IF;
    RAISE;
END;

方式3

declare
  stmt varchar2(200);
  dept_no_array dbms_sql.Number_Table;
  c number;
  dummy number;
begin
  dept_no_array(1) := 10; dept_no_array(2) := 20;
  dept_no_array(3) := 30; dept_no_array(4) := 40;
  dept_no_array(5) := 30; dept_no_array(6) := 40;
  stmt := 'delete from emp where deptno = :dept_array';
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, stmt, dbms_sql.native);
  dbms_sql.bind_array(c, ':dept_array', dept_no_array, 1, 4);
  dummy := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);

  exception when others then
    if dbms_sql.is_open(c) then
      dbms_sql.close_cursor(c);
    end if;
    raise;
end;

declare
  stmt varchar2(200);
  empno_array dbms_sql.Number_Table;
  empname_array dbms_sql.Varchar2_Table;
  c number;
  dummy number;
begin
  for i in 0..9 loop
    empno_array(i) := 1000 + i;
    empname_array(I) := get_name(i);
  end loop;
  stmt := 'insert into emp values(:num_array, :name_array)';
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, stmt, dbms_sql.native);
  dbms_sql.bind_array(c, ':num_array', empno_array);
  dbms_sql.bind_array(c, ':name_array', empname_array);
  dummy := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);

  exception when others then
    if dbms_sql.is_open(c) then
      dbms_sql.close_cursor(c);
    end if;
    raise;
end;

declare
  stmt varchar2(200);
  emp_no_array dbms_sql.Number_Table;
  emp_addr_array dbms_sql.Varchar2_Table;
  c number;
  dummy number;
begin
  for i in 0..9 loop
    emp_no_array(i) := 1000 + i;
    emp_addr_array(I) := get_new_addr(i);
  end loop;
  stmt := 'update emp set ename = :name_array
    where empno = :num_array';
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, stmt, dbms_sql.native);
  dbms_sql.bind_array(c, ':num_array', empno_array);
  dbms_sql.bind_array(c, ':name_array', empname_array);
  dummy := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);
 
  exception when others then
    if dbms_sql.is_open(c) then
      dbms_sql.close_cursor(c);
    end if;
    raise;
end;


方式4

declare
  c       number;
  d       number;
  n_tab   dbms_sql.Number_Table;
  indx    number := -10;
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, 'select n from t order by 1', dbms_sql);

  dbms_sql.define_array(c, 1, n_tab, 10, indx);

  d := dbms_sql.execute(c);
  loop
    d := dbms_sql.fetch_rows(c);

    dbms_sql.column_value(c, 1, n_tab);

    exit when d != 10;
  end loop;

  dbms_sql.close_cursor(c);

  exception when others then
    if dbms_sql.is_open(c) then
      dbms_sql.close_cursor(c);
    end if;
    raise;
end;

declare
  c       number;
  d       number;
  n_tab  dbms_sql.Number_Table;
  d_tab1 dbms_sql.Date_Table;
  v_tab  dbms_sql.Varchar2_Table;
  d_tab2 dbms_sql.Date_Table;
  indx number := 10;
begin

  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, 'select * from multi_tab order by 1', dbms_sql);

  dbms_sql.define_array(c, 1, n_tab,  5, indx);
  dbms_sql.define_array(c, 2, d_tab1, 5, indx);
  dbms_sql.define_array(c, 3, v_tab,  5, indx);
  dbms_sql.define_array(c, 4, d_tab2, 5, indx);

  d := dbms_sql.execute(c);

  loop
    d := dbms_sql.fetch_rows(c);

    dbms_sql.column_value(c, 1, n_tab);
    dbms_sql.column_value(c, 2, d_tab1);
    dbms_sql.column_value(c, 3, v_tab);
    dbms_sql.column_value(c, 4, d_tab2);
 
    exit when d != 5;
  end loop;

  dbms_sql.close_cursor(c);

目录
相关文章
|
2月前
|
SQL 监控 Oracle
Oracle SQL性能优化全面指南
在数据库管理领域,Oracle SQL性能优化是确保数据库高效运行和数据查询速度的关键
|
2月前
|
SQL 存储 Oracle
Oracle数据库SQL语句详解与应用指南
在数字化时代,数据库已成为各类企业和组织不可或缺的核心组件。Oracle数据库作为业界领先的数据库管理系统之一,广泛应用于各种业务场景。掌握Oracle数据库的SQL语句是数据库管理员、开发人员及运维人员的基本技能。本文将详细介绍Oracle数据库SQL语句的基本概念、语法、应用及最佳实践。一、Or
76 3
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
65 1
|
5月前
|
SQL Oracle 关系型数据库
|
5月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
367 3
|
5月前
|
SQL Java 数据库连接
mybatis动态SQL常用语法总结
MyBatis 使用 OGNL 表达式语言处理动态SQL,如 `if` 标签进行条件判断,`choose`、`when`、`otherwise` 实现多条件选择,`where`、`set` 管理SQL关键字,`trim` 提供通用修剪功能,`foreach` 遍历集合数据。`sql` 和 `include` 用于代码重用,`selectKey` 处理插入后的返回值。参数传递支持匿名、具名、列表、Map、Java Bean和JSON方式。注意SQL转义及使用合适的jdbcType映射Java类型。
106 7
|
6月前
|
SQL XML 数据库
后端数据库开发高级之通过在xml文件中映射实现动态SQL
后端数据库开发高级之通过在xml文件中映射实现动态SQL
58 3
|
5月前
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
131 0
|
6月前
|
SQL 缓存 Java
Java框架之MyBatis 07-动态SQL-缓存机制-逆向工程-分页插件
Java框架之MyBatis 07-动态SQL-缓存机制-逆向工程-分页插件
|
6月前
|
SQL Java 数据库连接
MyBatis动态SQL
MyBatis动态SQL
67 0

推荐镜像

更多