我遇到过这样的情况：我有一个 Oracle 过程，该过程至少从 3 或 4 个不同的地方被调用。我需要能够根据某些数据调用自定义代码。自定义代码是特定于客户的 - 因此，客户 A 可能想要执行 ABC，而客户 B 可能想要执行 6-7-8，而客户 C 不需要执行任何额外操作。当客户 D...Z 出现时，我不想修改我现有的程序。
我希望能够将客户特定的程序输入到表格中。在此现有过程中，检查数据库表中是否存在自定义代码过程，如果存在，则执行它。每个客户代码过程都具有相同的参数。
例如：
- 我的应用程序（超过 3 个地方）调用此“删除”过程
- 在此删除过程中，查找要调用的子过程的名称（如果存在）
- 如果存在，则执行该删除过程（传递参数）
我知道我可以通过构建一个包含对存储过程的调用的字符串来做到这一点。但是，我想知道 Oracle 10g 是否有内置的东西可以做这种事情？
I have a situation where I have an Oracle procedure that is being called from at least 3 or 4 different places. I need to be able to be able to call custom-code depending on some data. The custom-code is customer-specific - so, customer A might want to do A-B-C where customer B might want to do 6-7-8 and customer C doesn't need to do anything extra. When customers D...Z come along, I don't want to have to modify my existing procedure.
I'd like to be able to enter the customer-specific procedure into a table. In this existing procedure, check that database table if a custom-code procedure exists and if so, execute it. Each of the customer-code procedures would have the same parameters.
For instance:
- My application (3+ places) calls this "delete" procedure
- In this delete procedure, look up the name of a child-procedure to call (if one exists at all)
- If one exists, execute that delete procedure (passing the parameters in)
I know I can do this with building a string that contains the call to the stored procedure. But, I'd like to know if Oracle 10g has anything built in for doing this kind of thing?
发布评论
评论（4）
考虑到要求，您的解决方案似乎很合理，所以我投了赞成票。
另一种选择是循环遍历表查找的结果，并将对过程的调用放在一个大的 case 语句中。这将是更多的代码，但它的优点是使依赖链可见，这样您就可以更轻松地捕获丢失的权限和无效的过程。
Your solution seems reasonable given the requirements, so I voted it up.
Another option would be to loop through the results from your table look-up and put calls to the procedures inside a big case statement. It would be more code, but it would have the advantage of making the dependency chain visible so you could more easily catch missing permissions and invalid procedures.
您的每个客户都有自己的数据库吗？如果是这样，最好的选择是使用 。这样做的优点是不需要动态SQL。让主程序始终调用自定义过程，并使用 CC 标志来改变它包含的代码。
否则，Oracle 确实有一个规则引擎，但它并不是真正供我们使用的。
Do each of your customers have their own database? If so the best option would be to use conditional compilation. This has the advantage of not requiring dynamic SQL. Have the main program always call the custom procedure, and use CC flags to vary the code it contains.
Otherwise, Oracle does have a Rule Engine but it is not really intended for our use.
我们采用的最终解决方案是将过程的名称存储在数据库表中。然后我们构建 SQL 调用并使用 EXECUTE 语句。
The final solution that we went with was to store the name of a procedure in a database table. We then build the SQL call and use an EXECUTE statement.
同意 APC 的答案，并在 this 白皮书如果您查找“基于组件的安装”，它描述了通过使用条件编译解决的类似问题。
Agree with APC's answer and just to expand on it, in this white paper if you look for "Component based installation" it describes a similar problem solved by using conditional compilation.