如何在查询中使用过程参数

发布于 2024-07-14 06:31:40 字数 465 浏览 7 评论 0原文

如何使用查询访问同一过程中的过程参数
例如：请参阅此过程

procedure game(left in tab.left%type,right in tab.right%type,...)  
is  
--some local variables  
begin  
merge into tgt_table
using subquery --(here is what i need to use the parameters)  
on some condition  
when matched then  
update the tgt table  
when not matched then  
insert the table;  
end game;

在上面的过程和合并语句中，我需要一个查询，以便它使用参数值作为表引用，并根据给定的条件使用这些值更新或插入到表中。

请帮帮我。提前致谢

原文

how do i access the procedure parameters inside the same procedure using a query
for example: see this procedure

procedure game(left in tab.left%type,right in tab.right%type,...)  
is  
--some local variables  
begin  
merge into tgt_table
using subquery --(here is what i need to use the parameters)  
on some condition  
when matched then  
update the tgt table  
when not matched then  
insert the table;  
end game;

In above procedure and in merge statement, i need a query such that it uses the parameters value as a table reference and using those values it either updates or inserts into the table based on the condition given.

Help me please. Thanks in advance

分享到QQ

分享到微博

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

发布评论

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

巨坚强 2024-07-21 06:31:40

如果您的参数定义了要使用的表，您将需要使用动态 SQL - 类似于：

procedure game(left in tab.left%type,right in tab.right%type,...)  
is  
    --some local variables  
    l_sql long;
begin  
    l_sql := 'merge into tgt_table'
             || ' using ' || left
             || ' on some condition'  
             || ' when matched then'  
             || ' update ' || right
             || ' when not matched then'  
             || ' insert the table';  
    execute immediate l_sql;
end game;

但是，您还有很多工作要做，因为条件、更新和插入子句都需要根据所使用的表进行更改。我不相信这种采购实际上会特别有用。

You would need to use dynamic SQL if your parameters define the table to use - something like:

procedure game(left in tab.left%type,right in tab.right%type,...)  
is  
    --some local variables  
    l_sql long;
begin  
    l_sql := 'merge into tgt_table'
             || ' using ' || left
             || ' on some condition'  
             || ' when matched then'  
             || ' update ' || right
             || ' when not matched then'  
             || ' insert the table';  
    execute immediate l_sql;
end game;

However, you have a lot more work left to do, since the condition, update and insert clauses all need to change according to the tables being used. I'm not convinced this procure will be particularly useful in fact.

回复收藏 0 原文