What is the stored procedure of Oracle database?

Oracle database stored procedure: a set of SQL statements to complete specific functions, compiled and stored in the database. A stored procedure is a procedure written by flow control and SQL statements. This procedure is compiled and optimized and stored in the database server. It only needs to be called when the application program uses it.

Stored Procedure (Stored Procedure) is a set of SQL statements to complete specific functions, which are compiled and stored in the database. The user executes a stored procedure by specifying its name and giving parameters (if the stored procedure has parameters). Stored procedures are an important object in the database, and any well-designed database application should use stored procedures.

A stored procedure is a procedure written by flow control and SQL statements. This procedure is compiled and optimized and stored in the database server. It only needs to be called when the application program uses it. In ORACLE, several related procedures can be combined together to form a program package.

Advantages:

1. The stored procedure is only compiled when it is created. There is no need to recompile each time the stored procedure is executed in the future. Generally, SQL statements are compiled once every time they are executed, so Use stored procedures to increase database execution speed.

2. When performing complex operations on the database (such as Update, Insert, Query, and Delete on multiple tables), this complex operation can be encapsulated in a stored procedure and combined with the transaction processing provided by the database. use.

3. Stored procedures can be reused, which can reduce the workload of database developers.

4. High security, you can set that only a certain user has the right to use the specified stored process.

To put it simply, you write a stored procedure on your machine. This stored procedure is placed in a remote database server like the data in those tables, but it is executable code. Others Users who can connect to the database server can call the stored procedure you wrote

Its function is to hide the details. That is to say, the stored procedure code you wrote may be very complicated, but it is very simple for others to call it. There is no need to know specifically how it is done, and multiple instructions can be completed at one time

Basic syntax

CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)] 
{AS|IS} 
[说明部分] 
BEGIN 
可执行部分 
[EXCEPTION 
错误处理部分] 
END [过程名];

1. Optional keyword OR REPLACE means that if the stored procedure already exists, overwrite it with a new stored procedure, usually a reconstruction of the user stored procedure.

2. The parameter part is used to define multiple parameters (if there are no parameters, they can be omitted). Parameters come in three forms: IN, OUT and IN OUT. If the parameter form is not specified, it defaults to IN.

3. The keyword AS can also be written as IS, followed by the description part of the process, where you can define the local variables of the process.

4. You can use any text editor or directly in the SQLPLus environment to write stored procedures. The written stored procedures must be compiled in the SQLPLus environment to generate compiled code. The original code and compiled code are compiled will be stored in the database during the process. The successfully compiled stored procedure can be called in the ORacle environment.

5. A stored procedure can be deleted when it is no longer needed. The person who deletes a stored procedure is the creator of the procedure or someone with the DROP ANY PROCEDURE system permission. The syntax for deleting a stored procedure is as follows:

DROP PROCEDURE 存储过程名；

6. If you want to recompile a stored procedure, you can only be the creator of the procedure or someone with the ALTER ANY PROCEDURE system authority. The syntax is as follows:

ALTER PROCEDURE 存储过程名 COMPILE；

7. The person who executes (or calls) the stored procedure is the creator of the process or the person who has the EXECUTE ANY PROCEDURE system permission, or the person who is granted the EXECUTE permission by the owner. The execution method is as follows:

Method 1:

EXECUTE 模式名.存储过程名[(参数...)];

Method 2:

BEGIN 
模式名.存储过程名[(参数...)]; 
END;

8. The parameters passed must be consistent with the defined parameter type, number and order (if If the parameter defines a default value, the parameter can be omitted when calling). Parameters can be variables, constants, or expressions.

9. The difference between as and is: Both can be used in stored procedures (procedures) and functions (functions), but using IS will not be able to use the debug mode to debug the stored function; in the view you can only Use AS but not IS. In the cursor (CURSOR), you can only use IS but not AS.

10. Decode function in oracle

Explanation of meaning:

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

The meaning of this function is as follows:

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

decode(字段或字段的运算，值1，值2，值3）

The result of this function is, when When the value of the field or field operation is equal to value 1, the function returns value 2, otherwise it returns value 3

Of course, value 1, value 2, and value 3 can also be expressions. This function makes certain sql statements Much simpler

Example:

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

sign() function returns 0, 1, and -1 respectively depending on whether a value is 0, a positive number, or a negative number

For example:

Variable 1=10, variable 2=20

Then sign(variable 1-variable 2) returns -1, and the decode decoding result is "variable 1", achieving comparison Small value purpose.

11. nvl function

The format of the NVL function is as follows: NVL(expr1,expr2)

The meaning is: if oracle is the first If one parameter is empty, the value of the second parameter is displayed. If the value of the first parameter is not empty, the original value of the first parameter is displayed.

NVL2函数的格式如下：NVL2(expr1,expr2, expr3)

含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。

格式如下：

Coalesce(expr1, expr2, expr3….. exprn)

表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。

返回表达式中第一个非空表达式，如有以下语句： 　　

SELECT COALESCE(NULL,NULL,3,4,5) FROM dual

其返回结果为：3

如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值

12、max函数

对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。

13、随机抽取N条记录

https://blog.csdn.net/bbliutao/article/details/7727322

14、untion all

union all，解释为联合所有。

Union解释为联合。union或者Union all实现把前后两个select集合的数据联合起来，组成一个结果集查询输出。这就要求联合前后的结果集，需要分别有相同的输出字段的数目，并且对应的字段类型要相同。
SELECT column1, column2 from table1 union (all) select column1, column2 from table2

以上语句要求量表的column1字段类型相同，column2类型相同。而且每个查询的数目都是一样的。UNION ALL和UNION的差别就在ALL上面，第一个叫联合所有，说明会显示前后两个查询所有的数据，而UNION没有ALL(所有)这个单词，实现将前后两个查询的数据联合到一起后，去掉重复的数据显示

推荐教程：《Oracle教程》

The above is the detailed content of What is the stored procedure of Oracle database?. For more information, please follow other related articles on the PHP Chinese website!