PL/SQL 如何返回 ROW 中的所有属性

2022-01-09 00:00:00 sql insert oracle plsql sql-returning

我不知道如何使用 RETURNING 子句返回所有属性

I don't know how I can return all attributes with the RETURNING clause

我想要这样的东西:

DECLARE  
    v_user USER%ROWTYPE  
 BEGIN  
     INSERT INTO User 
     VALUES (1,'Bill','QWERTY') 
     RETURNING * INTO v_user;  
END;

RETURNING * INTO 出错，如何替换 * ?

RETURNING * INTO gets an error , how can I replace * ?

推荐答案

如果我们能做这样的事情就好了，但是唉:

It would be neat if we could do something like that but alas:

SQL> declare
  2      v_row t23%rowtype;
  3  begin
  4      insert into t23
  5          values (my_seq.nextval, 'Daisy Head Maisy')
  6          returning * into v_row;
  7  end;
  8  /
        returning * into v_row;
                  *
ERROR at line 6:
ORA-06550: line 6, column 19:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored


SQL>

我相信此功能可能会记录更改请求，因为我知道很多人都想要它.但目前我们所能做的就是对每一列进行冗长的规范:

I believe there may be a logged change request for this feature, because I know lots of people want it. But for the moment all we can do is the long-winded specification of every column: 

SQL> declare
  2      v_row t23%rowtype;
  3  begin
  4      insert into t23
  5          values (my_seq.nextval, 'Daisy Head Maisy')
  6          returning id, person_name into v_row;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>

如果您有很多列，那就是个坏消息！

Bad news if you have a lot of columns!

我怀疑其基本原理是，大多数表的派生列相对较少(分配给 ID 的序列，分配给 CREATED_DATE 的 sysdate 等)，因此大多数值应该已经知道(或至少知道)插入过程.

I suspect the rationale is, most tables have relatively few derived columns (sequence assigned to an ID, sysdate assigned to a CREATED_DATE, etc) so most values should already be known (or at least knowable) to the inserting process.

编辑

我很关心如何归还所有属性不啰嗦每列的规格；)也许这是不可能的.

I was care how returning all attributes without long-winded specification of every column ;) Maybe it's impossible.

我以为我已经说清楚了，但无论如何:是的，目前不可能在 RETURNING 子句中使用 * 或一些类似的非特定机制.

I thought I had made it clear, but anyway: yes currently it is impossible to use * or some similar unspecific mechanism in a RETURNING clause.

相关文章