Oracle Stored Procedures

Storage functions

1. Introduction to Stored Procedures

Let's take a brief look at the syntax of Oracle's stored procedures, as follows:

　　　　

Simple syntax for stored procedures

Here's a brief look at the advantages of using stored procedures:

(1) easy to use. Creating a store is to put the named object directly into the database, because the code is not saved locally, and the user can use or invoke the stored procedure on any client computer.

(2) security. A stored procedure is a security guarantee provided by a database that a user must be authorized by the owner of the stored procedure to invoke or modify the stored procedure before it can be used.

(3) transitivity. The stored procedure can be set with parameters or multiple parameters, or there is no return value type. The return value of the stored procedure must be brought back through the parameter (out output type).

the difference between a stored procedure and a stored function:

A stored function can return a function using return, and the stored procedure may not.

2. Defining and Using variables
There are four types of PL/sql: Scalar type, composite type, reference type (reference), LOB (Large obejct) type

One, scalar type
The most common is scalar types, which are variables that can hold only a single numeric value, including numeric types, character types, date types, and Boolean types, each of which contains the corresponding subtypes. such as ": =" to replace the "=", using the method as follows: V_rate integer:1;

Second, compound variables:
Variables for storing multiple values are called composite variables, including PL/SQL records, PL/SQL tables, nested tables, and Varray four types

Iii. reference variables (reference)
Similar to pointers in C + + or concepts referenced in Java, variables that hold numeric pointers, use this variable to allow applications to share the same objects and reduce space consumption. There are two types of this class: cursor (ref CURSOR) and object type (ref object)

Iv. type of LOB
LOB types are variables that are used to store large amounts of data, including 3 internal (CLOB,BLOB,NCLOB) and external lobs (BFILE).
Clob,nclob is used to store large amounts of character data.
BLOBs are used to store large batches of binary data (like).

　　

3. Set permissions for the user

1.1 Users can only modify the permissions, the syntax is as follows:

Grant execute on stored procedure name to user name;

1.2 Users can only query for permissions:

Grant select on stored procedure name to user name;

4. How stored procedures are referenced

(1) The Execute method is followed by the name of the stored procedure, and execute can be abbreviated as exec.

(2) Begin end; The block writes the stored procedure name for invocation.

Storage functions

1. Simple Introduction to storage functions

A stored function is also a named stored program that can pass in parameters and perform a series of operations. A stored function is similar to a stored procedure structure, unlike a stored procedure, where a function must be followed by a return statement to return a function value.

2. Syntax

　　　

1 Create or Replace function Tony_function2 (3 num in number,4 Sum out number5 )6 return Number7  as8 begin9     NULL;Ten     return0; OneEnd

basic syntax for stored functionsWhat is the difference between a stored procedure and an int (input parameter) of a stored function and an out (output parameter)?

(1) Stored procedures and stored functions can have out parameters;

(2) Stored procedures and storage functions can have multiple out parameters;

(3) Stored procedures can use out parameters to implement the return value;

(4) The storage function uses return to implement the return value;

　　PS: If there is only one return value using the stored function, there are multiple return values that use the stored function.

Oracle Stored Procedures