Oracle if inserting or updating then
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 by 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; / with the new row's primary key.This procedure also accepts a CLOB object, corresponding to the body of the blog entry and a collection object corresponding to a list of categories the entry should be filed under.
You may not have to assign values to the PHP variables, even if they are input parameters—if no value is assigned to a scalar type, Oracle will regard it as a NULL value.When calling an Oracle stored procedure, all input and output data is passed as arguments to the procedure.This may be confusing as first, if you are used to the semantics of calling a PHP function with some arguments and having it return a value, but is easiest seen by example.The collection type CREATE OR REPLACE PACKAGE BODY blog AS /*------------------------------------------------*/ PROCEDURE latest( num_entries_in IN NUMBER, entries_cursor_out OUT cursor Type ) AS BEGIN OPEN entries_cursor_out FOR SELECT * FROM blogs WHERE rownum CREATE SEQUENCE blog_id_seq INCREMENT BY 1; / CREATE TABLE blogs ( id NUMBER PRIMARY KEY, title VARCHAR2(200), date_published DATE, text CLOB ); / CREATE SEQUENCE category_id_seq INCREMENT BY 1; CREATE TABLE categories ( id NUMBER PRIMARY KEY, name VARCHAR2(30) UNIQUE ); / CREATE TABLE blogs_to_categories ( blog_id INTEGER NOT NULL REFERENCES blogs(id), category_id INTEGER NOT NULL REFERENCES categories(id), PRIMARY KEY (blog_id, category_id) ); / As this script illustrates, a key issue is how transactions should be handled when dealing with LOBs.Here you have chosen to delegate all transaction handling to the PHP script, as updating a LOB is a two-stage process.Stored procedures typically group a series of related operations behind a single API.
Typically the operations executed by a stored procedure would be mix of SQL statements, to fetch and modify data, as well as PL/SQL statements which act on that data, such performing some mathematical calculations, detailed validation of values and handling error conditions They can be advantageous in improving performance, by reducing the number of "round trips" between a calling program and the database, as well as simplifying data management logic within the client.
In this case the invoking user defines a table in their own schema with the same name as a that accessed from within a procedure they want to execute, and the procedure acts against the local table rather than the one available to the definer.
, which provides an API for fetching and modifying entries in a hypothetical blogging application.
CREATE OR REPLACE PACKAGE blog AS TYPE cursor Type 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 IN NUMBER, entries_cursor_out OUT cursor Type ); /* Edit a blog entry.
If id_inout is NULL, results in an INSERT, otherwise attempts to UPDATE the existing blog entry.
Given the following stored procedure signature: When calling this procedure, the first argument name would contain an input value you supply at call-time, while greeting would be populated by the procedure as a "returned" value for use after the procedure has completed.