 | |||
|
|
||
| |||
| |||
| |
|
Home > Stored procedures in PL/SQL |
| |
|
|
|
|
| Learning Guide: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
||||||||||||||||||||||||||||||||||||
Table of contents:
Stored procedures in PL/SQL A PL/SQL program that is stored in a database in compiled form and can be called by name is referred to as a stored procedure. A stored procedure can be shared by a number of programs. Stored procedures are helpful in controlling access to data, preserving data integrity ad improving productivity. Browse through these tips to learn more about using PL/SQL stored procedures in Oracle, including stored procedure errors, PL/SQL stored procedure examples and more: Get an introduction to stored procedures for Oracle and learn how to create a PL/SQL stored procedure that uses REF CURSORS in this step-by-step example from Oracle's Database 2 Day + .NET Developer's Guide.
Read an example of an Oracle stored procedure that can be used to return multiple rows. ...
Q: Expert Greg Williams: 1) There are many types of procedures that you can use. Here is an example of a REF CURSOR: 2) Try calling this procedure in a select statement in VB.NET. Since VB.NET is not my strong suit, this is all of the help I can give you on VB.NET. 3) Dbms_sql.native is a mandatory parameter for DBMS_SQL.PARSE. Oracle8i introduced native dynamic SQL, an alternative to DBMS_SQL. Using native dynamic SQL, you can place dynamic SQL statements directly into PL/SQL blocks. In most situations, native dynamic SQL can replace DBMS_SQL. Native dynamic SQL is easier to use. For more info on dynamic SQL statements see Note:198306.1 at metalink.oracle.com.
Q: How can I find out which stored procedure is currently running for a session? Expert Brian Peasland: The answer is to really find out which SQL statement a session is currently running. Stored procedures will appear as an SQL statement. To do this, we'll use the following query:
In my example, I limited this to just queries issued by MYUSER. But you can do other limitations on the V$SESSION view as well.
Q: I want to ask you how to make a stored procedure to autotransfer updated data from a remote server to a central server. Can I autotransfer using stored procedures? Expert Brian Peasland: Basically, you need to set up a database link and have your stored procedure insert data through the database link. Chapter 29 of the Oracle 10g Administrator's Guide discusses this topic. Under the section titled "Schema Object Name Resolution," the documentation shows an example of using an INSERT statement in the db link. Your stored procedure can contain this sort of INSERT statement.
Q: How do you invoke a stored procedure in a SELECT statement? Expert Brian Peasland: You cannot invoke a stored procedure in a SELECT statement. However, you can call a function in a SELECT statement. The function can be a wrapper for the stored procedure. For instance, assume that I have a stored procedure called FOO. Let's create a wrapper function BAR that calls the stored proc: Now I can use the function above to execute the stored proc in my SELECT statement: Q: How do you create a link between two databases inside a stored procedure? Expert Brian Peasland: Normally, one does not create database objects inside stored procedures. But on the rare occasion that you do have this requirement, you can use the EXECUTE IMMEDIATE command to run the CREATE DATABASE LINK command similar to the following:
Expert Brian Peasland: You sure can call a procedure from within that same procedure. Such an operation is called "recursion". One procedure recursively calls itself. The big thing to keep in mind is that you need an ending point otherwise the recursive calls will be infinite and never stop. A common recursive procedure or function is to compute the factorial of a number. N factorial (written N!) is defined as N*(N-1)*(N-2)*...*2*1. This also means that N! = N*(N-1)!. So a recursive function is born. Such a recursive function would appear as follows:
Notice that in the above example, I have a terminating condition (n=1). If this condition is not met, then the function is called again. The same concept applies to procedures. But ensure that you have a terminating condition. |
|
|
|
||||||||||||||||||||||||||||||||||
|
|
 |  |  | |  | |  | |  | |  | |  | |  | |  | |
|
|
About Us | Contact Us | For Advertisers | For Business Partners | Site Index | RSS |
|
|
||
|
|||||||
