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

发布于 2024-09-02 08:26:01 字数 283 浏览 2 评论 0原文

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

我想要这样的内容：

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

RETURNING * INTO 出现错误，如何替换 * ？

原文

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

I want something like this:

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

卸妝后依然美 2024-09-09 08:26:01

如果我们能做类似的事情那就太好了，但是可惜：

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>

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

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>

如果你有很多专栏，那就是坏消息了！

我怀疑其基本原理是，大多数表具有相对较少的派生列（分配给 ID 的序列、分配给 CREATED_DATE 的 sysdate 等），因此大多数值对于插入过程来说应该是已知的（或至少是已知的）。

编辑

我关心如何返回所有
属性无需啰嗦
每列的规范；）也许
这是不可能的。

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

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!

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.

edit

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

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.

回复收藏 0 原文

哆兒滾 2024-09-09 08:26:01

到目前为止，我认为最好的解决方案可能是：

DECLARE  
    v_user USER%ROWTYPE;
    rowid_v rowid;
 BEGIN  
     INSERT INTO User 
     VALUES (1,'Bill','QWERTY') 
     RETURNING ROWID INTO rowid_v; 

     SELECT * INTO v_user WHERE rowid = rowid_v ;
END;

https://oracle- base.com/articles/misc/dml-returning-into-clause

不幸的是，仍然没有返回 * 或返回行之类的功能:) 但会很酷。

So far the best solution I believe could be:

DECLARE  
    v_user USER%ROWTYPE;
    rowid_v rowid;
 BEGIN  
     INSERT INTO User 
     VALUES (1,'Bill','QWERTY') 
     RETURNING ROWID INTO rowid_v; 

     SELECT * INTO v_user WHERE rowid = rowid_v ;
END;

https://oracle-base.com/articles/misc/dml-returning-into-clause

Unfortunately, still there is no such functionality as returning * or returning row :) but will be cool.

回复收藏 0 原文

~没有更多了~