PL/SQL如何返回ROW中的所有属性 [英] PL/SQL How return all attributes in ROW
问题描述
我不知道如何使用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.
这篇关于PL/SQL如何返回ROW中的所有属性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!