在 Oracle 中编写动态插入或更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17346367/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing Dynamic Insert or Update in Oracle
提问by Thepallav_abhi
I am working on an application that will be used to populate the address details of for employees. The address structure will be different for every country.
我正在开发一个应用程序，该应用程序将用于填充员工的地址详细信息。每个国家的地址结构都不同。
For the address style mapping I have a table in which I have mapped all the styles.
I have the following requirement:
对于地址样式映射，我有一个表，其中映射了所有样式。
我有以下要求：
As shown in the image, I have an address mapping table in which
col1 is for style (wrt country),
col2 for Field_name (The field to be displayed in front end) and
col3 for column_field_name (The name of the column in which the field in col2 will be stored in transaction table.)
如图所示，我有一个地址映射表，其中
col1 代表样式（国家/地区），
col2 代表 Field_name（要在前端显示的字段）和
col3 代表 column_field_name（字段所在的列的名称）在 col2 中将存储在事务表中。）
![Mapping table Desc][1]
**ADDRESS_STYLE FIELD_NAME COLUMN_FIELD_NAME**
1 US_GLB Address Line1 ADDRESS_LINE1
2 US_GLB Address Line2 ADDRESS_LINE2
3 US_GLB Zip Code POSTAL_CODE
4 US_GLB Tax Zip Code ADD_INFORMATION17
5 US_GLB City TOWN_OR_CITY
6 US_GLB State REGION_2
7 US_GLB Country COUNTRY
8 US_GLB Tax Jurisdiction ADD_INFORMATION15
9 US_GLB Tax Jurisdiction Other ADD_INFORMATION16
10 US_GLB Telephone TELEPHONE_NUMBER_1
11 US_GLB Telephone2 TELEPHONE_NUMBER_2
Now I have to write a procedure or function which will take the all the in parameters and insert those in my transaction table as they are mapped in mapping table.
现在我必须编写一个过程或函数，它将获取所有 in 参数并将它们插入到我的事务表中，因为它们被映射到映射表中。
For Ex-(As shown above)
Field Address Line1 will be stored in ADDRESS_LINE1 of transaction table.
Field State will be stored in REGION_2 of transaction table.
对于Ex-（如上所示）
Field Address Line1 将存储在事务表的ADDRESS_LINE1 中。字段状态将存储在事务表的 REGION_2 中。
回答by APC
So in this Proof of Concept I have made two assumptions (maybe three, depends how you count them).
所以在这个概念证明中，我做了两个假设（可能是三个，取决于你如何计算它们）。
- The target ADDRESSES table has an ID column, populated by a sequence.
- The column mappings are always in the same order as the columns in the table's projection andthat order is guaranteed by a sort column on the mapping table.
- 目标 ADDRESSES 表有一个 ID 列，由序列填充。
- 列映射的顺序始终与表投影中的列顺序相同， 并且该顺序由映射表上的排序列保证。
The first is just a guess and easy enough to fit to whatever your actual process is.
第一个只是一个猜测，很容易适应您的实际过程。
The second assumption has major ramifications, because if you haven't been disciplined about how the data is entered into the mapping table this implementation won't work reliably. You'll need to replace the loop with eleven separate lookups for each value of FIELD_NAME. Obviously that would be far too tedious for me to code.
第二个假设有很大的影响，因为如果您没有严格遵守如何将数据输入到映射表中，则该实现将无法可靠地工作。对于 FIELD_NAME 的每个值，您需要用十一次单独的查找替换循环。显然，这对我来说太乏味了，无法编码。
create or replace procedure pop_addr_details
(i_addr_style mapping_table.address_style%type
, i_Address_Line1 in varchar2
, i_Address_Line2 in varchar2
, i_Zip_Code in varchar2
, i_Tax_Zip Code in varchar2
, i_City in varchar2
, i_State in varchar2
, i_Country in varchar2
, i_Tax_Jurisdiction in varchar2
, i_Tax_Jurisdiction Other in varchar2
, i_Telephone in varchar2
, i_Telephone2 in varchar2 )
is
stmt varchar2(32767);
begin
stmt := 'insert into adddresses values (address_id';
for map_rec in ( select column_field_name
from mapping_table
where address_style = i_addr_style
order by col_order )
loop
stmt := stmt || ',' || map_rec.column_field_name;
end loop;
stmt := stmt || ') values ( address_seq.next_val, :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)';
execute immedate stmt using i_Address_Line1
, i_Address_Line2
, i_Zip_Code
, i_Tax_Zip Code
, i_City
, i_State
, i_Country
, i_Tax_Jurisdiction
, i_Tax_Jurisdiction Other
, i_Telephone
, i_Telephone2;
end pop_addr_details;
/
There's a whole chapter on dynamic SQL in the PL/SQL documentation. Find out more.
PL/SQL 文档中有关于动态 SQL 的一整章。 了解更多。
回答by Thepallav_abhi
------------------------------INSERT PROCEDURE-----------------------------
PROCEDURE GHCM_ADDRESS_INSERT_DTLS_PROC(IN_PERSON_ID PLS_INTEGER,
I_ADDR_STYLE VARCHAR2,
IN_ATRRIBUTE1 VARCHAR2,
IN_ATRRIBUTE2 VARCHAR2,
IN_ATRRIBUTE3 VARCHAR2,
IN_ATRRIBUTE4 VARCHAR2,
IN_ATRRIBUTE5 VARCHAR2,
IN_ATRRIBUTE6 VARCHAR2,
IN_ATRRIBUTE7 VARCHAR2,
IN_ATRRIBUTE8 VARCHAR2,
IN_ATRRIBUTE9 VARCHAR2,
IN_ATRRIBUTE10 VARCHAR2,
IN_ATRRIBUTE11 VARCHAR2,
IN_ATRRIBUTE12 VARCHAR2,
IN_ATRRIBUTE13 VARCHAR2,
IN_ATRRIBUTE14 VARCHAR2,
IN_ATRRIBUTE15 VARCHAR2,
IN_ATRRIBUTE16 VARCHAR2,
IN_ATRRIBUTE17 VARCHAR2,
OUT_SUCCESS OUT VARCHAR2)
IS
V_STATEMENT VARCHAR2(1000);
V_SEQ_ID PLS_INTEGER;
BEGIN
V_STATEMENT := 'INSERT INTO PER_ADDRESS_TEST (ADDRESS_ID,STYLE,IN_PERSON_ID';
FOR MAP_REC IN (SELECT COLUMN_FIELD_NAME
FROM GHCM_ADDRESS_STYLE_MAPING_TBL
WHERE STYLE = I_ADDR_STYLE
AND DISPLAY_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
AND MAPPING_DESC = 'Address Structure'
ORDER BY DISPLAY_ORDER_NO) LOOP
V_STATEMENT := V_STATEMENT || ',' || MAP_REC.COLUMN_FIELD_NAME;
END LOOP;
FOR MAP_REC IN (SELECT DISTINCT (COLUMN_FIELD_NAME)
FROM GHCM_ADDRESS_STYLE_MAPING_TBL
WHERE COLUMN_FIELD_NAME NOT IN
(SELECT COLUMN_FIELD_NAME
FROM GHCM_ADDRESS_STYLE_MAPING_TBL
WHERE STYLE = I_ADDR_STYLE
AND DISPLAY_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
AND MAPPING_DESC = 'Address Structure')) LOOP
V_STATEMENT := V_STATEMENT || ',' || MAP_REC.COLUMN_FIELD_NAME;
END LOOP;
V_STATEMENT := V_STATEMENT ||
') values ( :1, :2, :3, :4, :5, :6, :7, :8, :9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20)';
V_SEQ_ID := ADDRESS_SEQ_TEST.NEXTVAL;
EXECUTE IMMEDIATE V_STATEMENT
USING V_SEQ_ID, I_ADDR_STYLE, IN_PERSON_ID, IN_ATRRIBUTE1, IN_ATRRIBUTE2, IN_ATRRIBUTE3, IN_ATRRIBUTE4, IN_ATRRIBUTE5, IN_ATRRIBUTE6, IN_ATRRIBUTE7, IN_ATRRIBUTE8, IN_ATRRIBUTE9, IN_ATRRIBUTE10, IN_ATRRIBUTE11, IN_ATRRIBUTE12, IN_ATRRIBUTE13, IN_ATRRIBUTE14, IN_ATRRIBUTE15, IN_ATRRIBUTE16, IN_ATRRIBUTE17;
COMMIT;
OUT_SUCCESS := 'Y';
END;
