时间戳

首页 > 解决方案 > 动态 sql、绑定变量和动态 USING

oracle - 动态 sql、绑定变量和动态 USING

问题描述

我在 .net 应用程序中有一些动态表单。根据表单的不同，插入/更新中的字段会有所不同。我正在尝试构建一个动态 sql 语句，但字符串可能超过 4000 个字符，这对于字符串文字不起作用，所以我正在尝试使用绑定变量。由于保存的字段是动态的，我不知道如何处理 USING 块。下面是我正在尝试做的一个简化版本。如果它有助于解释动态表单，您可以查看我询问的关于动态获取数据的旧问题。收集到 sys_refcursor 的记录

ps 我知道我可以在每个占位符中插入空值，但这意味着当我向表中添加一个字段时，我必须更新该过程，这不是可行的方法。

procedure bindTest(oCur out sys_refcursor)

    as 
      vFirst varchar2(50) := 'Joe';
      vMiddle varchar2(50) := 'Vs'
      vLast varchar2(50) := 'Volcano';
      vVars varchar2(50) := 'vFirst, vLast';
      vSql varchar2(1000) := '';
    begin
    -- This form does not use the middle name so there are only 2 bind vars. 
    -- The field exists in the table but not in the web form so it would not be passed to the procedure.
    -- I've included it here to show data I want to ignore. 
    -- vVars includes the list of valid fields to save. 
    -- This would be the sql created by my script. 

    vSql := 'insert into tbl_users (firstName, lastName) values (:a, :b)'; 
    -- depending on the form, vSql might look like 
    ---- 'insert into tbl_users (firstName, middle, lastName) values (:a,:b,:c)'
    ---- 'insert into tbl_users (lastName) values (:a)'
    ---- etc

    execute immediate vSql using {what goes here? or how do I handle this?};
    -- I understand normally it would be `USING vFirst, vLast` but what about when it's dynamic?

    open oCur for
        select 
            ID
          , firstName
          , lastName
        from
          tbl_users
        where 
          rownum = 1
        order by 
          id desc;


end bindTest;

标签： oracleplsqloracle11g

解决方案

一个简单但静态的解决方案假定存在已知的绑定变量列表及其数据类型，并且动态查询只能使用这些绑定变量的子集。

VARCHAR这是五个绑定变量的示例。你生成这个 PL/SQL 块：

DECLARE
L_VC1 VARCHAR2(4000) := :VC1;
L_VC2 VARCHAR2(4000) := :VC2;
L_VC3 VARCHAR2(4000) := :VC3;
L_VC4 VARCHAR2(4000) := :VC4;
L_VC5 VARCHAR2(4000) := :VC5;
BEGIN
  -- here an statement using L_VC1 up to L_VC5
  -- eg
  INSERT INTO test (vc1,vc2,vc3) values (L_VC1, L_VC2, L_VC3);
END;

并通过完整的值列表（其中一些 left NULL）执行它。

EXECUTE IMMEDIATE my_generated_block USING vc1, vc2, vc3, vc4, vc5;

一个不错的特性是，动态 SQL 可以多次使用一个绑定变量，而无需扩展USING参数。

如果出现新变量，这当然必须保持。

什么是替代方案？

在我看来，要在绑定变量列表中真正动态化，你无法解决这个问题EXECUTE IMMEDIATE，你必须向DBMS_SQL.

这里的想法，没有详细说明如何在 PL/SQL 中实现它：

DECLARE
    cursor_name INTEGER;
    rows_processed INTEGER;
BEGIN
    cursor_name := dbms_sql.open_cursor;
    DBMS_SQL.PARSE(cursor_name, 'INSERT INTO test (vc1,vc2,vc3) values (:vc1, :vc2, :vc3)',
                   DBMS_SQL.NATIVE);
    -- call in a loop for each BV               
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':vc1', 'x');
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':vc2', 'y');
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':vc3', 'z');
    ---
    rows_processed := DBMS_SQL.EXECUTE(cursor_name);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/

您必须DBMS_SQL.BIND_VARIABLE在循环中为每个绑定变量名称和值调用。

请注意，我完全忽略了绑定变量的数据类型，这也应该考虑在内，并且可能会使解决方案更加复杂，但可以解决。

哪种方案更可行？

如果您的关系数据库设计是真正的键-值（即您可以在没有 的情况下引入新的绑定变量DDL），您将不得不遵循第二个选项。否则，即如果需要修改表结构以获取新的绑定变量并且更改频率较低，我更喜欢第一个选项。

推荐阅读