未定义值的顺序时，Oracle Insert Into 如何工作?

问题描述

我遇到了一些看起来像这样的代码.我知道它会返回自动生成的 id，但我不明白的是，当我调用此函数时传递游标数据时，当列顺序不是时，它如何识别要在哪些列中插入哪些值定义?

I came across some code that looks like this. I understand that it will return the auto-generated id, but what I don't understand is when I pass cursor data when I call this function, how does it identify what values are to be inserted in which columns when the column order is not defined?

FUNCTION INSERT_ROW(DATA IN OWNER.TABLE%ROWTYPE)
    RETURN OWNER.TABLE.ID%TYPE
IS
    l_ID OWNER.MY_TABLE.ID%TYPE;
    l_Data OWNER.MY_TABLE%ROWTYPE := DATA;
BEGIN
    INSERT INTO OWNER.MY_TABLE
    VALUES l_Data
    RETURNING ID INTO l_ID;

我试图查找许多示例，但我只遇到过按这样的顺序定义值的示例INSERT INTO my_table (val2, val3, val4) VALUES (2, 3, 4) 返回 val1进入 val1;

I tried to look up many examples and I only come across ones where the values are defined in order like this INSERT INTO my_table (val2, val3, val4) VALUES (2, 3, 4) RETURNING val1 INTO val1;

推荐答案

Oracle IS 定义了表中列的顺序.看看 ALL_TAB_COLUMNS 视图 - 有一个 COLUMN_ID 列，它定义了表中列的顺序.如果在 SELECT(即 SELECT * FROM MY_TABLE)中未给出字段列表，则 MY_TABLE 中的列将按 ALL_TAB_COLUMNS.COLUMN_ID 顺序返回.这也是在 %ROWTYPE 变量中对列进行排序的方式，这也是未指定字段列表的 INSERT 期望对字段进行排序的方式.

The order of columns in a table in Oracle IS defined. Take a look at the ALL_TAB_COLUMNS view - there's a COLUMN_ID column which defines the order of columns within the table. If a field list is not given in a SELECT (i.e. SELECT * FROM MY_TABLE) the columns from MY_TABLE will be returned in ALL_TAB_COLUMNS.COLUMN_ID order. This is also the same way columns are ordered in a %ROWTYPE variable, and it's the way that an INSERT which doesn't have a field list specified expects fields to be ordered.

这篇关于未定义值的顺序时，Oracle Insert Into 如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！