Oracle Base Custom Functions

First, the function

A function is similar to a stored procedure and is also a named Pl-sql block stored in a database. The main feature of a function is that it must have a return value. The return type of the function is specified by return. Anywhere in the function can be returned from the function by a return expression statement, and the return type must be the same as the return type of the declaration.

Grammar:

create [or replace] function function_name

[(Parameter_list)]

return datatype

{Is/as}

[Local_declarations]

Begin

executable_statements;

[exception

Exception_handlers;]

End

Description

Function_name: function name.

Parameter_list: List of functions, optional.

Return: Specifies the return type of the function and cannot specify the size.

Local_declarations: local variable declaration, optional.

Executable_statements: The Pl-sql statement to execute.

Exception_handlers: Exception handling, optional.

or repalce: Whether or not to overwrite, optional.

Example: Get employee salary according to employee number.

--query Sal based on noCREATE OR REPLACE FUNCTIONFun_getsal (Eno Number)RETURN  Number   --return type asv_sal emp.sal%TYPE;BEGIN  SELECTSal intoV_sal fromEmpWHEREEmpno=Eno; RETURNv_sal; EXCEPTION whenOTHERS ThenRaise_application_error (-20012,'the employee does not exist');ENDFun_getsal;

Call Function:

--calling functions, customizing exception handlingDECLAREV_sal Number;  emp_20012 EXCEPTION; PRAGMA Exception_init (emp_20012,-20012);BEGINv_sal:=Fun_getsal (7788); Dbms_output.put_line ('Sal:'||v_sal); EXCEPTION whenemp_20012 ThenDbms_output.put_line ('the employee does not exist');END;

Attention:

　　1. Function parameters and return types only declare parameters and do not specify size.

2, the executable part has at least one return statement.

3. When calling a function, the function cannot be present as a separate statement, and can be used as part of an expression.

Ii. advantages of functions and stored procedures:

1, the common use of the code can only need to be written once, and is required to the code of any application calls (. Net,c++,java, can also make DLL library).

2, this kind of several kinds of writing, several maintenance updates, everybody sharing method, simplifies the application development maintenance, improves the efficiency and the performance.

3, this modular method makes a complex problem, the large program is gradually simplified into a few simple, small program parts, to be written separately, so the structure of the program is more clear, simple, and easy to implement.

4, can provide data processing, control process, prompt information and other aspects of the consistency between the various developers.

5, save memory space. They are stored in external memory in a compressed form and are placed into memory for processing when they are called. And when multiple users call the same stored procedure or function, they only need to be loaded once.

6, improve the security and integrity of data. By putting some manipulation of the data into a stored procedure or function, you can restrict some users from doing this to the database by granting them permission to execute the statement.

Three, the difference between the function and the stored procedure:

1. The stored procedure user completes a specific operation or task (such as inserting, deleting, etc.) in the database, and the function is used to return the specific data.

2, stored procedure declaration with procedure, functions with function.

3. The stored procedure does not require a return type, and the function must return the type.

4. The stored procedure can be executed as a standalone pl-sql, the function cannot be executed as a standalone plsql and must be part of the expression.

5. Stored procedures can only return values through out and in/out, and the function may return a value using return, in addition to Out,in/out.

6. Calling stored procedures is not available in SQL statements (DML or select), and functions can.

Iv. Applicable occasions:

1. If you need to return multiple values and do not return a value, use a stored procedure, or use a function if you only need to return a value.

2. Stored procedures are typically used to perform a specified action, and functions are generally used to calculate and return a value.

3. You can call functions inside SQL to complete complex computational problems, but you cannot call stored procedures.

Oracle Base Custom Functions