oracle APEX 调用外部存储函数或存储过程

APEX Call external stored function or stored procedure

提问by envoy

Is there any "best practice" on how to call a stored procedure from APEX instead of just using a simple DB-Link?

关于如何从 APEX 调用存储过程而不是仅使用简单的 DB-Link，是否有任何“最佳实践”？

回答by Richard Pascual

Calling PL/SQL Stored Procedures Within Oracle APEX

在 Oracle APEX 中调用 PL/SQL 存储过程

There's a great deal of detail involved with developing robust, maintainable and efficient solutions by paring PL/SQL stored procs with the Application Express web based frame work. Not knowing the current skill level of the author of the OP, I assume that the most helpful explanation is a simple example developed from scratch.

通过将 PL/SQL 存储过程与基于 Web 的 Application Express 框架配对，开发健壮、可维护和高效的解决方案涉及大量细节。不知道OP作者目前的技术水平，我认为最有用的解释是从头开发的一个简单示例。

Getting Started:The APEX environment has a lot of tools that can get you started. If you don't already have an Oracle database environment with APEX installed, consider signing up for a free, hosted trial account through the APEX home pageon Oracle.com.

入门：APEX 环境有很多工具可以帮助您入门。如果您还没有安装了 APEX 的 Oracle 数据库环境，请考虑通过Oracle.com 上的APEX 主页注册一个免费的托管试用帐户。

Application Design Decisions

应用程序设计决策

PL/SQL stored procedures are not necessary to develop applications on Oracle APEX, however they do supply a greater amount of flexibility and customization during the development process.

在 Oracle APEX 上开发应用程序不需要 PL/SQL 存储过程，但是它们在开发过程中提供了更大的灵活性和自定义。

This example will use the popular schema object: EMPtypically available as an optional part of each Oracle database installation. In case you don't have it, here is the DDL and DML source for building the example table:

此示例将使用流行的模式对象：EMP通常作为每个 Oracle 数据库安装的可选部分提供。如果您没有，这里是用于构建示例表的 DDL 和 DML 源：

The EMPTable(A Copy Aliased as: LAB01_SAMPLE_EMP)

的EMP表（A复印别名为：LAB01_SAMPLE_EMP）

 CREATE TABLE  "EMP"
    (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
     "ENAME" VARCHAR2(10),
     "JOB" VARCHAR2(9),
     "MGR" NUMBER(4,0),
     "HIREDATE" DATE,
     "SAL" NUMBER(7,2),
     "COMM" NUMBER(7,2),
     "DEPTNO" NUMBER(2,0),
      PRIMARY KEY ("EMPNO") ENABLE
    )
 /
 ALTER TABLE  "EMP" ADD FOREIGN KEY ("MGR")
       REFERENCES  "EMP" ("EMPNO") ENABLE
 /

If you would like some test data, this is what I had to work with:

如果您想要一些测试数据，这就是我必须使用的：

1. BUILD a SQL-based DML update statement that will change the SAL(Salary) value for one employee at a time based on their ENAME.

2. WRAP the UPDATE DML statement into a reusable, compiled PL/SQL stored procedure. Include parameter arguments for the two data input values for "Name" and "Amount of Increase".

3. TEST the stored procedure and verify it works as required.

4. DESIGN and CODE an APEX Application Page which will:

   (a) Show the current contents of the employee entity table. (b) Accept input values to pass into the PL/SQL Stored Procedure. (c) Utilize native APEX Errorand Successmessage settings to provide more feedback on the outcome of the procedure call.

5. TEST the APEX page and verify it works as specified.

6. REVIEW the discussion and conclusions section at the end for additional comments and more tips to keep you moving on your own path to developing Oracle skills.

1. 构建一个基于 SQL 的 DML 更新语句，该语句将SAL根据他们的ENAME.

2. 将 UPDATE DML 语句包装成一个可重用的、已编译的 PL/SQL 存储过程。包括“名称”和“增加量”的两个数据输入值的参数参数。

3. 测试存储过程并验证它是否按要求工作。

4. 设计和编码一个 APEX 应用程序页面，它将：

   (a) 显示雇员实体表的当前内容。(b) 接受输入值以传递到 PL/SQL 存储过程。(c) 利用本机 APEXError和Success消息设置来提供有关过程调用结果的更多反馈。

5. 测试 APEX 页面并验证它是否按规定工作。

6. 查看最后的讨论和结论部分，以获取更多评论和更多提示，让您在自己的 Oracle 技能发展道路上继续前进。

Programming a SQL DML Process

对 SQL DML 进程进行编程

This is the initial example created to accomplish this task.

这是为完成此任务而创建的初始示例。

    UPDATE LAB01_SAMPLE_EMP
       SET SAL = SAL + 1250
     WHERE ENAME = 'KING';

A quick revision reveals how we can parametrize the approach to make this statement

快速修订揭示了我们如何对做出此声明的方法进行参数化

    UPDATE LAB01_SAMPLE_EMP
       SET SAL = SAL + p_salary_increase
     WHERE ENAME = p_ename;

If you are not sure where to go next, this is where a lesson on "best-practices" is available thanks to APEX. Navigate to the OBJECT BROWSERand CREATE a Procedure Object. The application will walk through every step to set up a PL/SQL stored proc.

如果您不确定下一步该往哪里走，这正是 APEX 提供的“最佳实践”课程。导航到OBJECT BROWSER并创建过程对象。该应用程序将完成设置 PL/SQL 存储过程的每个步骤。

The Working PL/SQL Procedure Source Code:

工作 PL/SQL 过程源代码：

After walking through the wizard setup, this is the cleaned-up stored procedure. There were some additional changes after debugging some compile-time error warnings:

完成向导设置后，这是清理过的存储过程。在调试了一些编译时错误警告后，还有一些额外的变化：

 create or replace procedure "PROC_UPDATE_SALARY"
    (p_ename IN VARCHAR2, p_salary_increase IN VARCHAR2)
 is

 v_new_salary   lab01_sample_emp.sal%TYPE;       

 begin

       UPDATE LAB01_SAMPLE_EMP
          SET SAL = SAL + p_salary_increase
        WHERE ENAME = p_ename
    RETURNING SAL INTO v_new_salary;

 commit;

 dbms_output.put_line('INCREASED SALARY FOR: ' || p_ename ||
     ' TO THE NEW AMOUNT OF: $ ' || to_char(v_new_salary));

 end;

Best Practices, a Quick Aside and Discusssion:You just have to keep doing it... coding that is. There is just no way around it. Look for examples at work or in life and try your hand at developing schemas and designs to satisfy made-up but realistic requirements. For beginning developers, the PL/SQL stored procedure above may already show some "unfamiliar" or odd coding syntax and commands.

That is only the tip of what is possible out there. Coding style is also only a part of it as you get deeper into things, you may notice a few things:

1. ORGANIZATIONis important. Learn quickly some conventions in naming and notation to use in the code. This will keep things organized and easy to find or reference elsewhere.

2. RECYCLE and REUSEmeans your code should be developed with reuse in mind. Common routines and processes should be bundled together to avoid redundancy.

3. ALWAYS TESTyour work suggests that less frustration is found when initial, fundamental steps in your process or application have been carefully tested first before proceeding.

最佳实践、快速旁白和讨论：您只需要继续这样做……就是编码。没有办法解决它。在工作或生活中寻找示例，并尝试开发模式和设计以满足虚构但现实的要求。对于初学者来说，上面的 PL/SQL 存储过程可能已经显示出一些“不熟悉”或奇怪的编码语法和命令。

这只是那里可能发生的事情的一小部分。当您深入了解事物时，编码风格也只是其中的一部分，您可能会注意到以下几点：

1. 组织很重要。快速了解代码中使用的命名和符号的一些约定。这将使事情井井有条，易于在其他地方找到或参考。

2. RECYCLE 和 REUSE意味着您的代码应该在开发时考虑到重用。应将通用例程和流程捆绑在一起以避免冗余。

3. 始终测试您的工作表明，如果您的流程或应用程序中的初始、基本步骤在继续之前先经过仔细测试，就会发现较少的挫败感。

TESTING the Oracle PL/SQL Procedure

测试 Oracle PL/SQL 过程

I used the built-in APEX scripting engine found in the SQL WORKSHOPsection of the environment. Below is a screenshot of the output logs of my testing script.

我使用SQL WORKSHOP了环境部分中的内置 APEX 脚本引擎。下面是我的测试脚本的输出日志的屏幕截图。

Bringing it Together: Designing an APEX Application Page with a Procedure Call

整合：使用过程调用设计 APEX 应用程序页面

Create or open up an APEX Application and start out by making a new page. There are system wizard processes that will help you get started if you haven't done this before.

创建或打开 APEX 应用程序，然后创建一个新页面。如果您以前没有这样做过，系统向导过程将帮助您开始。

Select the option to BUILDa FORMon top of a STORED PROCEDURE. There will be prompts for permission to build the page items needed for input parameters. Follow the wizard to completion to make sure all the dependent page design elements are included.

选择BUILDaFORM上的选项STORED PROCEDURE。将提示是否允许构建输入参数所需的页面项。按照向导完成以确保包含所有相关页面设计元素。

My near-finalized design is below:

我接近完成的设计如下：

There are a few extras added, such as the REPORTregion to provide immediate visibility to the table and any applied changes to its data.

添加了一些附加功能，例如REPORT提供对表的即时可见性和对其数据的任何应用更改的区域。

The Form in Action: Testing Data Input and Results

表格在行动：测试数据输入和结果

The SUCCESSalert message is a feature available for certain page elements to inform the user of any significant events conducted on the database.

该SUCCESS警报消息是适用于某些页面元素通知数据库上进行任何显著事件的用户功能。

Closing Comments and Discussion

结束评论和讨论

The immediate answer to the question of the OP is YES, there are best practices. It is such a huge subject that the only realistic way of handling it is by walking through different examples to see the ways that these practices are "commonly" applied.

OP 问题的直接答案是YES，有最佳实践。这是一个如此庞大的主题，处理它的唯一现实方法是通过不同的示例来了解这些实践“普遍”应用的方式。

There were a few shortcuts involved in this solution (based on several assumptions) and it might be helpful to bring them up as a parting discussion on the possibility of revisiting this walk-through to make an improved, EMP-2.0project.

此解决方案中涉及一些捷径（基于几个假设），将它们作为关于重新审视此演练以进行改进的EMP-2.0项目的可能性的分会讨论可能会有所帮助。

• The procedure works on the EMPtable based on searches by ENAME. What is the REALkey of this table? Are there any risks involved with this approach- possibly with respect to larger data sets?

• Most Oracle PL/SQL Objects used in production ready environments have some level of error trapping or exception handling through a PL/SQL EXCEPTION BLOCK. What kind of errors would you want to trap? How?

• 该过程EMP基于搜索方式在表上工作ENAME。该表的REAL键是什么？这种方法是否存在任何风险——可能与更大的数据集有关？

• 在生产就绪环境中使用的大多数 Oracle PL/SQL 对象都具有某种级别的错误捕获或通过PL/SQL EXCEPTION BLOCK. 你想捕获什么样的错误？如何？

Don't underestimate the resources available within the APEX tool. There are lots of wizards that walk developers through the process of creating different, functioning modules of code. These automated guides provide solutions that can also be reverse-engineered to understand how the code was generated and also what general design approaches make compliant design patterns.

不要低估 APEX 工具中可用的资源。有许多向导可以引导开发人员完成创建不同功能的代码模块的过程。这些自动化指南提供的解决方案也可以进行逆向工程，以了解代码是如何生成的，以及哪些通用设计方法使设计模式符合要求。