Shulou(Shulou.com)06/01 Report--

A very familiar word function, the first contact with the function is math class, I did not expect that it will become more and more important in the future study. Programming language will give a separate chapter to the function to introduce in detail, we can see the position in the programming language!

So is function really hard to learn? At least for programming (except source code), in addition to a large number of system functions using syntax is difficult to remember, it is actually quite practical and easy to accept.

What are functions and stored procedures? So put the additions, deletions, modifications, etc., together, and perform these operations more efficiently. This code block (combination) is called a stored procedure or function.

Functions must have return values in Oracle, which is also the difference between functions and stored procedures. There are two kinds of functions: 1. System functions 2. Custom functions. This article is a shared custom function (the system will be updated later).

What is a custom function? of course, according to our needs, we will implement some functions according to our wishes, such as

1. Function and division:

The custom function is clear about what this function does and what requirements to accomplish, and each independent function should be implemented separately with multiple functions in the same function, which increases the complexity of the code and is not easy to maintain. it is not easy to maximize reuse and implement functions for functions.

2. The parameters of the function

The function parameter can represent the non-parametric parameter, and the parameter needs to specify the data type. When the parameter is passed to the interior, the parameter is immutable.

3. Return value

The function must have a return value and cannot return collection parameters, so records and recordsets are not types in Oracle.

Format:

Create or replace function fun_one

Return number as

Begin

Return 123

End fun_one

/

Explanation:

Create or replace function creates the keyword of the function, followed by the root return value data type, and the code between begin and end is the function definition.

Format:

Select object_id,object_name,object_type,status from user_objects where lower (object_name) = 'function name'

Select name,type,line,text from user_soucre where lower (name) = 'function name'

Explanation:

To explain, the query is user_objects and user_soucre tables, query function status and detailed function creation process statements, we create the function name regardless of case, stored in the data dictionary will be stored in uppercase, lower () function means uppercase to lowercase, function name can be queried in lowercase.

Format:

Declare msy number

Begin

Msg: = fun_one;-- copies the created function to the msg variable

Dbms_output.put_line (msg)

End

Explanation: the keyword declaer declares a variable, and then outputs the variable function based on the function assignment.\ you can output the result of the previous execution, and set serveroutput on; turns on the Oracle output.

The certainty of the function:

The certainty of the function is that every time the function is called, Oracle will always perform the same steps according to the parameters, and the output result is the same, no matter how many times the execution result is always the same, the parameters will not be immutable in the function code block, and the deterministic function can be created with the keyword deterministic.

For frequently used functions, the use of deterministic functions greatly improves database performance, so when the function is called, it will look for functions that have previously used the same parameters, and if there is a direct use of the previously executed results, instead of performing the function definition process to improve database performance and save computer resources.

Stored procedure:

Stored procedures do not need to return values, unlike functions, functions are suitable for complex calculations to improve database performance, while procedures are more suitable for performing a large number of data updates, queries and other operations.

Three types of processes: 1, IN 2, OUT 3, IN OUT

So what are the benefits of the process?

1. Programming the SQL interface to update the application data may connect to the database frequently, so connecting through the API interface will consume a lot of resources and realize the work delivery process, which greatly reduces the execution efficiency of the database.

2. Security: as an object in the database, the stored procedure can assign permissions to control the whole security. at the same time, the stored procedure actually transfers the database from the programming language to the database, and the integrity of the data has been retained.

3. Then one of the original design ideas of procedure storage is also for reusability.

Note: database definition language, DDL, cannot be directly implemented.

Format:

Create or replace procedurl pr_xx as begin update table set column = 10; commit; end pr_xx

Explanation:

Procedure keyword creation process.

Format:

Execute procedure name

-

Begin

Process name

End

/

-

Explanation:

Execute to implement the calling procedure, can also be achieved through the begin function call method, which actually demonstrates how to call another function or procedure from one function or procedure.

Stored procedure IN:

Format:

Create or replace proceduer update_xx (in_age in number) as begin

Update table set column = in_age

Commit

End update_xx

/

Explanation:

IN parameters, input parameters, only parameters that cannot be entered or outputted. After it is passed to the stored procedure by the caller, it is executed by the stored procedure, and no matter how the parameter is used, the value of the parameter cannot be changed. It is read-only for this stored procedure. If you change the value of IN, you will get an error!

Stored procedure OUT:

Format:

Create or replace proceduer update_xxx (in_age in number,out_age out number) as begin

Update table set column = in_age

Select column into out_age from table where column = 1

End update_xxx

Explanation:

The OUT parameter, as the name implies, assigns the value of table column = 1 to out_age

Format:

Declaer updated_age number

Begin

Update_xxx (20th update)

Dbms_output.ut_line (updated_age)

End

/

Explanation:

So look at the code, we declare a new variable, call the created OUT stored procedure, and pass the variable to out_age, and then output, be sure to specify the variable name for the output, do not use constant highlight or it will report an error.

The parameter of the stored procedure-IN OUT parameter

The IN OUT parameter can be used as either an input parameter or an output parameter. Therefore, IN OUT is generally used to process the parameter values and process the result output.

Give a more meaningful exchange of column sub-parameter variables

Format:

Create or replace proceduer swap (in_out1 in out number,in_out2 in out number) as

Begin

Declaer para number

Begin

Para: = in_out1

In_out1: = in_out2

In_out2: = para

End

End

/

Explanation:

Variable substitution is also used in JAVA bubble sorting, so temporary variables are created to swap with each other.

Although the stored procedure brings great convenience, the same stored procedure can also be called by multiple users, and the variables of the output parameters will be updated frequently and irregularly, so it will be very difficult to control the variables. and can not use constants as input parameters, otherwise compilation errors.

Learn about:

The parameter order of the stored procedure is important-the solution: name representation (not introduced)

Stored procedure parameters-the default values for parameters in the creation of stored procedures are listed as follows:

In_age in number default 20

Function stored procedures, far more than the introduction of this article, with unlimited possibilities such as conditional control, the above is to learn and share functions and stored procedures, I hope it will be helpful to you.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.