英文:
Oracle SQL MERGE Statement using multiple COLUMN_VALUE keywords
问题
我正在使用Oracle SQL进行MERGE语句。我有一个过程，其中有1个输入参数作为VARCHAR2和2个输入参数作为简单的自定义类型。请参阅以下代码：
procedure proc_1
(
in_param_1 IN VARCHAR2,
in_param_array_1 IN CUSTOM_ARRAY_TYPE,
in_param_array_2 IN CUSTOM_ARRAY_TYPE
)
as
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
MERGE INTO 表 T
USING (SELECT in_param_1 param_1, COLUMN_VALUE array_col1, COLUMN_VALUE array_col2 FROM TABLE(in_param_array_1), TABLE(in_param_array_2)) S
ON (T.col1 = S.param_1)
WHEN MATCHED THEN
...
WHEN NOT MATCHED THEN
...
自定义类型定义：
TYPE CUSTOM_ARRAY_TYPE
AS
TABLE OF VARCHAR2(4);
问题出现在我尝试在MERGE语句的SELECT部分使用2个COLUMN_VALUE关键字时。如果我只使用1个COLUMN_VALUE，例如如下所示，就没有问题，它可以正常编译：
USING (SELECT in_param_1 param_1, COLUMN_VALUE array_col1 FROM TABLE(in_param_array_1)) S
我得到的实际错误是：
缺少右括号
如何包括我两个自定义类型数组输入参数，以便我可以在后续的MERGE语句中使用它们存储的值？
英文:
I am doing a MERGE statement using Oracle SQL. I have a procedure which has 1 input parameter as a VARCHAR2 and 2 input parameters which are simple custom types. Please see the code below:
procedure proc_1
(
in_param_1 IN VARCHAR2,
in_param_array_1 IN CUSTOM_ARRAY_TYPE,
in_param_array_2 IN CUSTOM_ARRAY_TYPE
)
as
PRAGMA AUTONOMOUS_TRANSCATION
BEGIN
MERGE INTO table T
USING (SELECT in_param_1 param_1, COLUMN_VALUE array_col1 FROM TABLE(in_param_array_1), COLUMN_VALUE array_col2 FROM TABLE (in_param_array_2)) S
ON (T.col1 = S.param_1)
WHEN MATCHED THEN
...
WHEN NOT MATCHED THEN
...
Custom type definition:
TYPE CUSTOM_ARRAY_TYPE
AS
TABLE OF VARCHAR2(4);
The problem arises when I am trying to use 2 COLUMN_VALUE keywords in the SELECT portion of the MERGE statement. If I only use 1 COLUMN_VALUE, for example like below, there's no issue and it compiles fine:
USING (SELECT in_param_1 param_1, COLUMN_VALUE array_col1 FROM TABLE(in_param_array_1)) S
The actual error I am getting is this:
Missing right parenthesis
How can I include both of my custom type array input parameters so that I can use the values they store later in my MERGE statement?
答案1
得分: 1
你的USING子句应该是：
SELECT in_param_1 param_1, x1.COLUMN_VALUE array_col1, x2.COLUMN_VALUE array_col2
FROM TABLE(in_param_array_1) x1,
TABLE(in_param_array_2) x2
这在语法上应该可以工作，但由于它们之间没有可连接的列，你将得到两个数组的笛卡尔积。虽然你可以尝试在内联块中使用ROWNUM生成一个，但这不太安全。唯一安全的方式是在PL/SQL中或者使用具有命名字段的对象集合，其中之一将是将你的集合中的列关联在一起的序列号。然后，你可以在连接中包含它，你将不再需要COLUMN_VALUE，因为你将有真正的列名来使用。
英文:
Your USING clause should be:
SELECT in_param_1 param_1, x1.COLUMN_VALUE array_col1,x2.COLUMN_VALUE array_col2
FROM TABLE(in_param_array_1) x1,
TABLE(in_param_array_2) x2
That should work syntactically but you will get a Cartesian product between the two arrays, as there is no joinable column between them. While you could risk generating one in an inline block with ROWNUM, that's not very safe. The only way to safely join the right element in one to the same element in the other is either in PL/SQL or by using a collection of object with named fields, one of which would be a sequence number that ties your columns together across your collections. Then you would include that in your join, and you wouldn't need COLUMN_VALUE since you would have real column names to work with.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论