Calling Stored Procedures
Calling Stored Procedures
________________________________________
Note:
You may need to set up data structures, similar to the following, for certain examples to work:
CREATE TABLE Emp_tab (
Empno NUMBER(4) NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2));
CREATE OR REPLACE PROCEDURE fire_emp1(Emp_id NUMBER) AS
BEGIN
DELETE FROM Emp_tab WHERE Empno = Emp_id;
END;
VARIABLE Empnum NUMBER;
________________________________________
Procedures can be called from many different environments. For example:
• A procedure can be called within the body of another procedure or a trigger.
• A procedure can be interactively called by a user using an Oracle tool.
• A procedure can be explicitly called within an application, such as a SQL*Forms or a precompiler application.
• A stored function can be called from a SQL statement in a manner similar to calling a built-in SQL function, such as LENGTH or ROUND.
This section includes some common examples of calling procedures from within these environments.
See Also:
"Calling Stored Functions from SQL Expressions".
A Procedure or Trigger Calling Another Procedure
A procedure or trigger can call another stored procedure. For example, included in the body of one procedure might be the following line:
. . .
Sal_raise(Emp_id, 200);
. . .
This line calls the Sal_raise procedure. Emp_id is a variable within the context of the procedure. Recursive procedure calls are allowed within PL/SQL: A procedure can call itself.
Interactively Calling Procedures From Oracle Tools
A procedure can be called interactively from an Oracle tool, such as SQL*Plus. For example, to call a procedure named SAL_RAISE, owned by you, you can use an anonymous PL/SQL block, as follows:
BEGIN
Sal_raise(7369, 200);
END;
________________________________________
Note:
Interactive tools, such as SQL*Plus, require you to follow these lines with a slash (/) to run the PL/SQL block.
________________________________________
An easier way to run a block is to use the SQL*Plus statement EXECUTE, which wraps BEGIN and END statements around the code you enter. For example:
EXECUTE Sal_raise(7369, 200);
Some interactive tools allow session variables to be created. For example, when using SQL*Plus, the following statement creates a session variable:
VARIABLE Assigned_empno NUMBER
After defined, any session variable can be used for the duration of the session. For example, you might run a function and capture the return value using a session variable:
EXECUTE :Assigned_empno := Hire_emp('JSMITH', 'President',
1032, SYSDATE, 5000, NULL, 10);
PRINT Assigned_empno;
ASSIGNED_EMPNO
--------------
2893
See Also:
See the SQL*Plus User's Guide and Reference for SQL*Plus information. See your tools documentation for information about performing similar operations using your development tool.
Calling Procedures within 3GL Applications
A 3GL database application, such as a precompiler or an OCI application, can include a call to a procedure within the code of the application.
To run a procedure within a PL/SQL block in an application, simply call the procedure. The following line within a PL/SQL block calls the Fire_emp procedure:
Fire_emp1(:Empnun);
In this case, :Empno is a host (bind) variable within the context of the application.
To run a procedure within the code of a precompiler application, you must use the EXEC call interface. For example, the following statement calls the Fire_emp procedure in the code of a precompiler application:
EXEC SQL EXECUTE
BEGIN
Fire_emp1(:Empnum);
END;
END-EXEC;
See Also:
For more information about calling PL/SQL procedures from within 3GL applications, see the following manuals:
• Oracle Call Interface Programmer's Guide
• Pro*C/C++ Precompiler Programmer's Guide,
• SQL*Module for Ada Programmer's Guide
Name Resolution When Calling Procedures
References to procedures and packages are resolved according to the algorithm described in the "Rules for Name Resolution in SQL Statements" section of Chapter 2, "Managing Schema Objects".
Privileges Required to Execute a Procedure
If you are the owner of a standalone procedure or package, then you can run the standalone procedure or packaged procedure, or any public procedure or packaged procedure at any time, as described in the previous sections. If you want to run a standalone or packaged procedure owned by another user, then the following conditions apply:
• You must have the EXECUTE privilege for the standalone procedure or package containing the procedure, or you must have the EXECUTE ANY PROCEDURE system privilege. If you are executing a remote procedure, then you must be granted the EXECUTE privilege or EXECUTE ANY PROCEDURE system privilege directly, not through a role.
• You must include the owner's name in the call. For example:
________________________________________
Note:
You may need to set up the following data structures for certain examples to work:
CONNECT sys/change_on_install AS Sysdba;
CREATE USER Jward IDENTIFIED BY Jward;
GRANT CREATE ANY PACKAGE TO Jward;
GRANT CREATE SESSION TO Jward;
GRANT EXECUTE ANY PROCEDURE TO Jward;
CONNECT Scott/Tiger
________________________________________
• EXECUTE Jward.Fire_emp (1043);
•
• EXECUTE Jward.Hire_fire.Fire_emp (1043);
________________________________________
Note:
A stored subprogram or package runs in the privilege domain of the owner of the procedure. The owner must be explicitly granted the necessary object privileges to all objects referenced within the body of the code.
________________________________________
Specifying Values for Procedure Arguments
When you call a procedure, specify a value or parameter for each of the procedure's arguments. Identify the argument values using either of the following methods, or a combination of both:
• List the values in the order the arguments appear in the procedure declaration.
• Specify the argument names and corresponding values, in any order.
For example, these statements each call the procedure Sal_raise to increase the salary of employee number 7369 by 500:
Sal_raise(7369, 500);
Sal_raise(Sal_incr=>500, Emp_id=>7369);
Sal_raise(7369, Sal_incr=>500);
The first statement identifies the argument values by listing them in the order in which they appear in the procedure specification.
The second statement identifies the argument values by name and in an order different from that of the procedure specification. If you use argument names, then you can list the arguments in any order.
The third statement identifies the argument values using a combination of these methods. If you use a combination of order and argument names, then values identified in order must precede values identified by name.
If you used the DEFAULT option to define default values for IN parameters to a subprogram (see the PL/SQL User's Guide and Reference),then you can pass different numbers of actual parameters to the first subprogram, accepting or overriding the default values as you please. If an actual value is not passed, then the corresponding default value is used. If you want to assign a value to an argument that occurs after an omitted argument (for which the corresponding default is used), then you must explicitly designate the name of the argument, as well as its value.
Calling Remote Procedures
Call remote procedures using an appropriate database link and the procedure's name. The following SQL*Plus statement runs the procedure Fire_emp located in the database and pointed to by the local database link named BOSTON_SERVER:
EXECUTE fire_emp1@boston_server(1043);
See Also:
For information on exception handling when calling remote procedures, see "Handling Errors in Remote Procedures".
Remote Procedure Calls and Parameter Values
You must explicitly pass values to all remote procedure parameters, even if there are defaults. You cannot access remote package variables and constants.
Referencing Remote Objects
Remote objects can be referenced within the body of a locally defined procedure. The following procedure deletes a row from the remote employee table:
CREATE OR REPLACE PROCEDURE fire_emp(emp_id NUMBER) IS
BEGIN
DELETE FROM emp@boston_server WHERE empno = emp_id;
END;
The following list explains how to properly call remote procedures, depending on the calling environment.
• Remote procedures (standalone and packaged) can be called from within a procedure, an OCI application, or a precompiler application by specifying the remote procedure name, a database link, and the arguments for the remote procedure.
• CREATE OR REPLACE PROCEDURE local_procedure(arg IN NUMBER) AS
• BEGIN
• fire_emp1@boston_server(arg);
• END;
•
• In the previous example, you could create a synonym for FIRE_EMP1@BOSTON_SERVER. This would enable you to call the remote procedure from an Oracle tool application, such as a SQL*Forms application, as well from within a procedure, OCI application, or precompiler application.
• CREATE SYNONYM synonym1 for fire_emp1@boston_server;
• CREATE OR REPLACE PROCEDURE local_procedure(arg IN NUMBER) AS
• BEGIN
• synonym1(arg);
• END;
•
• If you do not want to use a synonym, then you could write a local cover procedure to call the remote procedure.
• DECLARE
• arg NUMBER;
• BEGIN
• local_procedure(arg);
• END;
•
Here, local_procedure is defined as in the first item of this list.
See Also:
"Synonyms for Procedures and Packages"
________________________________________
Caution:
Unlike stored procedures, which use compile-time binding, runtime binding is used when referencing remote procedures. The user account to which you connect depends on the database link.
________________________________________
All calls to remotely stored procedures are assumed to perform updates; therefore, this type of referencing always requires two-phase commit of that transaction (even if the remote procedure is read-only). Furthermore, if a transaction that includes a remote procedure call is rolled back, then the work done by the remote procedure is also rolled back.
A procedure called remotely can usually execute a COMMIT, ROLLBACK, or SAVEPOINT statement, the same as a local procedure. However, there are some differences in behavior:
• If the transaction was originated by a non-Oracle database, as may be the case in XA applications, these operations are not allowed in the remote procedure.
• After doing one of these operations, the remote procedure cannot start any distributed transactions of its own.
• If the remote procedure does not commit or roll back its work, the commit is done implicitly when the database link is closed. In the meantime, further calls to the remote procedure are not allowed because it is still considered to be performing a transaction.
A distributed update modifies data on two or more nodes. A distributed update is possible using a procedure that includes two or more remote updates that access data on different nodes. Statements in the construct are sent to the remote nodes, and the execution of the construct succeeds or fails as a unit. If part of a distributed update fails and part succeeds, then a rollback (of the entire transaction or to a savepoint) is required to proceed. Consider this when creating procedures that perform distributed updates.
Pay special attention when using a local procedure that calls a remote procedure. If a timestamp mismatch is found during execution of the local procedure, then the remote procedure is not run, and the local procedure is invalidated.
Synonyms for Procedures and Packages
Synonyms can be created for standalone procedures and packages to do the following:
• Hide the identity of the name and owner of a procedure or package.
• Provide location transparency for remotely stored procedures (standalone or within a package).
When a privileged user needs to call a procedure, an associated synonym can be used. Because the procedures defined within a package are not individual objects (the package is the object), synonyms cannot be created for individual procedures within a package.
Calling Stored Functions from SQL Expressions
You can include user-written PL/SQL functions in SQL expressions. (You must be using PL/SQL release 2.1 or higher.) By using PL/SQL functions in SQL statements, you can do the following:
• Increase user productivity by extending SQL. Expressiveness of the SQL statement increases where activities are too complex, too awkward, or unavailable with SQL.
• Increase query efficiency. Functions used in the WHERE clause of a query can filter data using criteria that would otherwise need to be evaluated by the application.
• Manipulate character strings to represent special datatypes (for example, latitude, longitude, or temperature).
• Provide parallel query execution: If the query is parallelized, then SQL statements in your PL/SQL function may also be run in parallel (using the parallel query option).
Using PL/SQL Functions
PL/SQL functions must be created as top-level functions or declared within a package specification before they can be named within a SQL statement. Stored PL/SQL functions are used in the same manner as built-in Oracle functions (such as SUBSTR or ABS).
PL/SQL functions can be placed wherever an Oracle function can be placed within a SQL statement, or, wherever expressions can occur in SQL. For example, they can be called from the following:
• The select list of the SELECT statement.
• The condition of the WHERE and HAVING clause.
• The CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses.
• The VALUES clause of the INSERT statement.
• The SET clause of the UPDATE statement.
You cannot call stored PL/SQL functions from a CHECK constraint clause of a CREATE or ALTER TABLE statement or use them to specify a default value for a column. These situations require an unchanging definition.
________________________________________
Note:
Unlike functions, which are called as part of an expression, procedures are called as statements. Therefore, PL/SQL procedures are not directly callable from SQL statements. However, functions called from a PL/SQL statement or referenced in a SQL expression can call a PL/SQL procedure.
________________________________________
Bạn đang đọc truyện trên: Truyen2U.Pro