The Oracle stored procedure basics that you must understand

Business rules and business logic can be stored in Oracle by program, which is a stored procedure.

A stored procedure is a combination of SQL, pl/sql, and Java statements that enables you to move code that executes a business rule from your application to a database. The result is that the code is stored once but can be used by multiple programs.

To create a procedure object (procedural object), you must have the Create PROCEDURE system permission. If this process object needs to be used by other user schemas, then you must have the CREATE any PROCEDURE permission. You may need to excute permissions when performing procedure. Or excute any PROCEDURE permissions. If you assign permissions individually, the following example shows:

Grant execute on My_procedure to Jelly

Invoke an example of a stored procedure:

Execute My_procedure (' one PARAMETER ');

The difference between stored procedures (PROCEDURE) and functions (function).

The function has a return value, and you can reference the function in query directly or use the return value of the function.

There is no difference in nature, both are pl/sql programs, can have a return value. The fundamental difference is that a stored procedure is a command, and a function is part of an expression. Like what:

Select Max (NAME) from

But you cannot exec max (NAME) If Max is a function at this point.

Package is a combination of function,procedure,variables and SQL statements. Package allows multiple procedure to use the same variable and cursor.

To create PROCEDURE syntax:

CREATE [OR REPLACE] PROCEDURE [schema.] Procedure

[(Argument [in | Out | In out] [NO COPY] DataType

[, argument [in | Out | In out] [NO COPY] datatype] ...

）]

[Authid {current_user | definer}]

{is | as} {Pl/sql_subprogram_body |

Language {java name ' String ' | c [name, name] Library Lib_name

}]

SQL Code:

CREATE PROCEDURE Sam.credit (acc_no in number, amount in number) as

BEGIN

UPDATE accounts

SET balance = balance + Amount

WHERE account_id = acc_no;

End;

You can use the Create or replace procedure statement, which is useful in that the Excute permissions you have previously given will be preserved.

In, out, and out is used to decorate the parameters.

In indicates that the variable must be assigned by the caller and then passed into the procedure for processing.

Out means that Prcedure passes the value back to the caller through this variable.

In out is the combination of these two types.

Authid represents two types of permissions:

The Difiner Right (default), performer permissions (Invoker right).

The definition of permissions describes the tables that are involved in this procedure, and the permissions required for objects such as views can be accessed as long as the defined person has permission.

Performer permissions need to call this procedure user to have permissions on the related tables and objects.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/