PHPOracle Stored Procedure

Welcome to the Oracle community forum and interact with 2 million technical staff. The stored procedure is actually a program in Oracle. Most stored procedures are written using PLSQL. in Oracle Database version 10g 2nd and later, you can write them as external procedures in Java,. NET, or other languages. Stored Procedure usually includes

Welcome to the Oracle community forum and interact with 2 million technical staff> the stored procedure is a program actually located in Oracle. Most stored procedures are written in PL/SQL. in Oracle Database version 10g 2nd and later, you can write them as external processes in Java,. NET, or other languages. Stored Procedure usually includes

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

The stored procedure is a program actually located in Oracle. Most stored procedures are written in PL/SQL. in Oracle Database version 10g 2nd and later, you can write them as external processes in Java,. NET, or other languages. A stored procedure usually combines a series of related operations into an API. Operations performed by stored procedures include operations performed by SQL statements and PL/SQL statements. SQL statements are used to obtain and modify data. PL/SQL statements perform operations on the data, for example, perform some mathematical operations, verify the value in detail, and handle error conditions. They reduce the number of "round-trips" between the calling program and the database, simplify the data management logic in the client, and thus improve performance. If you consider the Code required to manage the many-to-many relationship between tables, you will find that performing updates to existing data usually involves three different queries. By encapsulating the process in a single stored procedure, the traffic between the client and the database is reduced. Operations that need to be performed in multiple steps of the client code are usually reduced to one database call. PHP OCI8 extension supports calling stored procedures. You can bind parameters to procedure statements (same as the method used to bind parameters to common SQL statements ), you can also access the result cursor and the Oracle set. This document provides common operation examples for stored procedures. When the input and output parameters of a stored procedure call an Oracle stored procedure, all input and output data is transmitted to the procedure as parameters. If you are used to calling a PHP function with some parameters and asking it to return a value, you may be confused at first, but you can see it clearly through examples. Assume that the following stored procedure signature is available:

SayHello (name IN VARCHAR2, greeting OUT VARCHAR2)

When this process is called, the first parameter will contain an input value provided during the call, and greeting will be filled by this process as a "return" value, used after the process is completed. Reading the specification PL/SQL programming is not the scope of this method document, but you need to have a general understanding of the stored procedure and be able to read the interface specification, but do not have to go into it. For the source code of the stored procedure, you must first define the accepted parameters, for example:

PROCEDURE edit_entry (

Status_out out number,

Status_msg_out OUT VARCHAR2,

Id_inout in out integer,

Title_in IN VARCHAR2,

Text_out out clob,

Categories_in IN list_of_numbers

);

The process name is edit_entry. Parentheses define the parameters that can be passed to the process (separated by commas ). In each parameter, you will see the name used to reference its value in this process (the same name is not required in the PHP script), the parameter mode (as shown below) and the type of this parameter. For the first parameter in this example:

Status_out out number,

The internal name is status_out, the mode is OUT, and the type is NUMBER (it is a native Oracle data type ). There is an id_inout parameter.

Id_inout in out integer,

Its mode is in out and its type is INTEGER. The last is the categories_in parameter:

Categories_in IN list_of_numbers

The type here is defined by the user (this type will be described in detail later ). Parameter mode parameter mode describes the "stream" direction from the caller to the process:

IN? Parameters in this mode are provided by the caller.

OUT? Parameters can be allocated by the process and returned to the caller.

In out? The parameter can be used in two "Directions". That is, the caller can provide a value for the parameter, and the parameter value can be modified during the process.

Parameters are required. During the PHP call process, you must bind the PHP variable to all the parameters it defines. You don't have to assign values to PHP variables, even if they are input parameters? If a value is not assigned to a scalar type, Oracle treats it as NULL. It is worth noting that stored procedures can be "reloaded" in Oracle ". In other words, there can be two processes with the same name but different parameter signatures. The process to be called is determined based on the number and type of parameters bound to the PHP variable. Parameters used for complex type stored procedures are not limited to scalar types such as VARCHAR2 and INTEGER. You can also pass and receive complex data types, such as the value list or result cursor corresponding to the row set selected from the table. Generally, if there are data rows to be iterated, you will usually receive the cursor returned from the stored procedure. If you need to input a Value List, the set is usually used. The following example demonstrates these complex types through PHP. Caller and definer permissions. Oracle distinguishes the caller (the user who executes the Stored PROCEDURE) from the definer (the user who executes the create procedure statement as the user. By default, stored procedures are executed with the permissions of the definer, even if the caller is a different user. This means that all access permissions to the table (for example, access permissions during the process) will be controlled by the definer's permissions, therefore, the caller only needs the permission to execute the process, not the permission of the table it uses. You can use the keyword AUTHID CURRENT_USER in the process definition to change this model. After this command is set, the required permissions for executing a stored procedure are determined by the current user who executes the procedure during running. One purpose of this method is to test a process of modifying table data without actually modifying real-time data. In this case, the caller defines a table in their own mode (the table has the same name as the table accessed from the process they need to execute ), the process is executed based on the Local table rather than the table that provides the given semantics. Calling stored procedures from PHP for SQL statements that need to be executed in PHP to call the process, you will usually be in Oracle BEGIN... END; embedded call in a block (called an anonymous block. For example:

　　

// Etc.

$ SQL = 'in in sayHello (: name,: message); END ;';

Then, bind_by_name () is called to bind the parameter to the PHP variable. If you use the following DDL statement to define sayHello

:

CREATE OR REPLACE PROCEDURE

SayHello (name IN VARCHAR2, greeting OUT VARCHAR2)

AS

BEGIN

Greeting: = 'hello' | name;

END;

/

Note: You can run the preceding statement using the SQL * Plus command line. Save the statement to the file (SAYHELLO. SQL ). Next, use SQL * Plus to log on:

$ Sqlplus username @ SID

Then, use the START command to create the process:

SQL> START/home/username/SAYHELLO. SQL

The following PHP script calls this process:

　　

$ Conn = oci_connect ('Scott ', 'tiger') or die;

$ SQL = 'in in sayHello (: name,: message); END ;';

$ Stmt = oci_parse ($ conn, $ SQL );

// Bind the input parameter

Oci_bind_by_name ($ stmt, ': name', $ name, 32 );

// Bind the output parameter

Oci_bind_by_name ($ stmt, ': message', $ message, 32 );

// Assign a value to the input

$ Name = 'Harry ';

Oci_execute ($ stmt );

// $ Message is now populated with the output value

Print "$ message \ n ";

?>

Blog sample package. To demonstrate some tips for calling stored procedures, you will use the following blog package here, which provides an API for obtaining and modifying entries in the hypothetical log application. A package is used to encapsulate processes, functions, and data in its own namespace through its own scope, and make them independent from other processes in the global data base namespace. When a process in a package is called, a period is used to separate the package name and process name. You can use the following statement to specify the blog package:

Create or replace package blog

TYPE cursorType is ref cursor return blogs % ROWTYPE;

/*

Fetch the latest num_entries_in from the blogs table, populating

Entries_cursor_out with the result

*/

PROCEDURE latest (

Num_entries_in NUMBER,

Entries_cursor_out cursorType

);

/*

Edit a blog entry. If id_inout is NULL, results in an INSERT, otherwise

Attempts to UPDATE the existing blog entry. status_out will have the value

1 on success, otherwise a negative number on failure with status_msg_out

Containing a description

Categories_in is a collection where list_of_numbers is described

TYPE list_of_numbers as varray (50) of number;

*/

PROCEDURE edit_entry (

Status_out out number,

Status_msg_out OUT VARCHAR2,

Id_inout in out integer,

Title_in IN VARCHAR2,

Text_out out clob,

Categories_in IN list_of_numbers

);

END blog;

/

[1] [2] [3]