如何在Oracle中存储和填充由Execute Immediate返回的数据

Question

在我的项目中，我试图执行以下查询:

DECLARE
  Sid nvarchar2(30) := ''; /*Here the values will come from some other variable>*/
  Bid nvarchar2(30) := ''; /*Here the values will come from some other variable>*/
  ExecuteDSQL varchar2(1000);
  ExecuteDSQLResult varchar2(10000);
BEGIN
  IF Sid IS NULL THEN Sid := '1' ; ELSE Sid := '4' ; END IF;
  IF Bid IS NULL THEN Bid := '1' ; ELSE Bid := '5' ; END IF;
  ExecuteDSQL := '  SELECT * FROM iftlog WHERE serverid='''|| Sid
                 || ''' AND bpid=''' || Bid  || '''  ';
  EXECUTE IMMEDIATE ExecuteDSQL INTO ExecuteDSQLResult;
  DBMS_OUTPUT.PUT_LINE(ExecuteDSQLResult);
END;
/

查询是导致数据类型不一致的错误，除了这个错误的问题是，我们可以存储结果到临时表像MS-SQL ?我是新的oracle数据库。我需要临时表来存储让我们说1到20之间的记录，我将使用Row_Number()，并将在我的项目中相应地修改查询。

Answer 1

Oracle中相当于SQL Server临时表的是PL/SQL集合。我们可以使用%rowtype语法来定义与表的投影匹配的本地类型。可以使用该类型声明一个局部变量作为选择的目标。

唉，没有方便的机制来打印记录:您需要在DBMS_OUTPUT.PUT_LINE()调用中指定每个单独的列。

下面是用习惯PL/SQL重写的代码:

DECLARE 
    Sid nvarchar2(30) := '';/*Here the values will come from some other variable>*/
    Bid nvarchar2(30) := '';/*Here the values will come from some other variable>*/
    ExecuteDSQL varchar2(1000) := 'SELECT * FROM iftlog WHERE serverid= :1 AND bpid= :2';
    type iftlog_nt is table of  iftlog%rowtype;
    ExecuteDSQLResult iftlog_nt;
BEGIN
    IF Sid IS NULL THEN Sid := '1' ; ELSE Sid := '4' ; END IF;
    IF Bid IS NULL THEN Bid := '1' ; ELSE Bid := '5' ; END IF;
    EXECUTE IMMEDIATE ExecuteDSQL 
        bulk collect into ExecuteDSQLResult
        using sid and bid;
    -- loop through all records in PL/SQL table
    for idx in 1..ExecuteDSQLResult.count()
    loop
        DBMS_OUTPUT.PUT_LINE(ExecuteDSQLResult(idx).some_col||'::'||ExecuteDSQLResult(idx).some_other_col);
    end loop;
END;
/

当然，像这样重写代码，很明显根本不需要动态SQL。所以我们可以用一个简单的select语句来代替EXECUTE IMMEDIATE调用:

SELECT * 
bulk collect into ExecuteDSQLResult 
FROM iftlog 
WHERE serverid= sid 
AND bpid= bid;

Answer 2

在Oracle中可以使用

1. Object type/table type或
动态游标

CREATE TYPE obj_typ AS OBJECT ( 
   id          number,
   name        VARCHAR2(20)
);
/
CREATE TYPE tab_typ AS table of  obj_typ;
/
declare
    v_type  tab_typ :tab_typ();
    .....
begin
    .....
    execute immediate 'select obj_typ(id, name) from tbl' into v_type;
    .....
end;

现在你可以使用v_type作为temp table如,

select id from table(v_type);

类型的一个限制是必须在使用它们之前在模式中创建。

使用动态游标

sql_stmt := 'SELECT * FROM emp WHERE job = :j';
OPEN emp_cv FOR sql_stmt USING my_job;

限制，你必须循环通过cursor来打赌数据不像temp table那样灵活

Answer 3

也可以使用全局临时表

 create global temporary table tmp  
 (serverid varchar2(10),
  bpid varchar2(10),
  txt varchar2(100)
  )
 ON COMMIT PRESERVE ROWS  
 ;

.

 DECLARE 
 Sid varchar2(30) := '1'; 
 Bid varchar2(30) := '1'; 
 ExecuteDSQL varchar2(1000);
 BEGIN
 ExecuteDSQL := 'insert into  tmp  
 SELECT serverid,bpid,txt FROM iftlog WHERE serverid=:Sid  AND bpid=:Bid';
 EXECUTE IMMEDIATE ExecuteDSQL  using sid, bid;
 commit;
 END;
 /

插入的数据在整个会话期间可用(在提交保留状态下)，并且可以通过SQL

访问。

select * from tmp;

参见GTT on SO