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

This article shares with you the content of sample analysis of stored procedures and functions in DB. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

6.1 introduction

Procedures and functions (as well as packages and triggers) are named PL/SQL blocks (also the user's schema objects) that are compiled and stored in the database for execution. Therefore, other PL/SQL blocks can be used by name. Therefore, business logic and enterprise rules can be written as functions or procedures to be saved to the database for sharing.

Procedures and functions are collectively referred to as PL/ PL/SQL subroutines. They are named SQL blocks that are stored in the database and exchange information with their callers through input, output parameters, or input / output parameters. The only difference between a procedure and a function is that the function always returns data to the caller, while the procedure does not. In this section, the main contents are as follows:

1. Create stored procedures and functions.

2. Use system-level exception handling and user-defined exception handling correctly.

3. Establish and manage stored procedures and functions.

6.2 create function

1. The syntax for creating a function is as follows:

CREATE [OR REPLACE] FUNCTION function_name

(arg1 [{IN | OUT | IN OUT}] type1 [DEFAULT value1]

[arg2 [{IN | OUT | IN OUT}] type2 [DEFAULT value1]]

.

[argn [{IN | OUT | IN OUT}] typen [DEFAULT valuen]])

[AUTHID DEFINER | CURRENT_USER]

RETURN return_type

IS | AS

BEGIN

Executive part

RETURN expression

EXCEPTION

Exception handling part

END function_name

L IN,OUT,IN

OUT is the mode of formal parameters. If omitted, it is IN mode. The formal parameter of IN mode can only pass the argument to the parameter and enter the interior of the function, but it can only be read but not written, and the value of the argument remains unchanged when the function returns. The formal parameter of OUT mode ignores the value of the argument when it is called (or the initial value of the parameter is always NULL), but can be read or written inside the function, and the value of the parameter is assigned to the argument when the function returns. IN

OUT has the characteristics of the first two modes, that is, when called, the value of the argument is always passed to the parameter, and at the end, the value of the parameter is passed to the parameter. When called, arguments to IN mode can be constants or variables, but for OUT and IN

Arguments to OUT mode must be variables.

Generally, OR is used only when confirming that the function_name function is a new function or a function to be updated

REPALCE keyword, otherwise it is easy to remove useful functions.

Example 1. To obtain the sum of wages in a department:

-- get the total salary of a department

CREATE OR REPLACE

FUNCTION get_salary (

Dept_no NUMBER

Emp_count OUT NUMBER)

RETURN NUMBER

IS

V_sum NUMBER

BEGIN

SELECT SUM (SALARY), count (*) INTO V_sum, emp_count

FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no

RETURN v_sum

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('the data you need does not exist!')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (SQLCODE | |'- -'| | SQLERRM)

END get_salary

two。 Function call

The parameters defined when the function is declared are called formal parameters, and the parameters passed to the function when the application is called are called actual parameters. When an application calls a function, it can pass parameters to the function in three ways:

The first parameter transfer format: position representation.

That is, when calling, according to the order of the parameters, the names of the parameters are written in turn, and the shape is associated with the parameters for transmission. When called with this method, the names of the shape-participating arguments are independent of each other, and it is important to emphasize the order.

The format is:

Argument_value1 [, argument_value2...]

Example 2: calculate the sum of wages in a department:

DECLARE

V_num NUMBER

V_sum NUMBER

BEGIN

V_sum: = get_salary (10, v_num)

DBMS_OUTPUT.PUT_LINE ('Total salary of department number: 10:' | | v_sum | |', the number of people is:'| | v_num)

END

The second parameter passing format: name representation.

That is, in the call, according to the name of the formal parameter and the name of the actual parameter, write out the corresponding formal parameter of the actual parameter, and associate the shape with the actual parameter for transmission. In this method, the names of shape participation arguments are independent of each other, and there is no relationship. The corresponding relationship of names is the most important, and the order is not important.

The format is:

Argument = > parameter [, …]

Where: argument is the formal parameter, it must be the same as the formal parameter name declared when the function is defined, parameter is the actual parameter.

In this format, the situation parameters and the actual parameters appear in pairs, and the relationship between them is only determined, so the order of the parameters can be arranged arbitrarily.

Example 3: calculate the sum of wages in a department:

DECLARE

V_num NUMBER

V_sum NUMBER

BEGIN

V_sum: = get_salary (emp_count = > v_num, dept_no = > 10)

DBMS_OUTPUT.PUT_LINE ('Total salary of department number: 10:' | | v_sum | |', the number of people is:'| | v_num)

END

The third parameter transfer format: combined transfer.

That is, when a function is called, both position representation and name representation are used to pass parameters for the function. When using this parameter passing method, the parameters passed by the positional representation must be placed in front of the parameters passed by the name representation. That is, no matter how many parameters a function has, as long as one of them uses a name representation, all subsequent parameters must use a name representation.

Example 4:

CREATE OR REPLACE FUNCTION demo_fun (

Name VARCHAR2,-- note that VARCHAR2 cannot give precision, such as VARCHAR2 (10), etc.

Age INTEGER

Sex VARCHAR2)

RETURN VARCHAR2

AS

V_var VARCHAR2 (32)

BEGIN

V_var: = name | |':'| | TO_CHAR (age) | | 'year old.' | | sex

RETURN v_var

END

DECLARE

Var VARCHAR (32)

BEGIN

Var: = demo_fun ('user1', 30, sex = >' male')

DBMS_OUTPUT.PUT_LINE (var)

Var: = demo_fun ('user2', age = > 40, sex = >' male')

DBMS_OUTPUT.PUT_LINE (var)

Var: = demo_fun ('user3', sex = >' female', age = > 20)

DBMS_OUTPUT.PUT_LINE (var)

END

No matter which parameter transfer method is adopted, there are only two methods for data transfer between actual parameters and formal parameters: address transfer method and value transfer method. The so-called addressing method means that when the function is called, the address pointer of the actual parameter is passed to the formal parameter, so that the formal parameter and the actual parameter point to the same area in memory, so as to realize the transmission of parameter data. This method is also called the reference method, that is, the formal parameters refer to the actual parameter data. All the input parameters use the address method to transmit data.

The value transfer method refers to copying the data of the actual parameter to the formal parameter, rather than passing the address of the actual parameter. By default, both output parameters and input / output parameters use the value transfer method. When the function is called, ORACLE copies the actual parameter data to the input / output parameters, and when the function runs normally and exits, it copies the output formal parameters and input / output formal parameter data to the actual parameter variables.

3. Parameter default value

You can use the DEFAULT keyword to specify default values for input parameters when declaring function parameters in a CREATE OR REPLACE FUNCTION statement.

Example 5:

CREATE OR REPLACE FUNCTION demo_fun (

Name VARCHAR2

Age INTEGER

Sex VARCHAR2 DEFAULT 'male')

RETURN VARCHAR2

AS

V_var VARCHAR2 (32)

BEGIN

V_var: = name | |':'| | TO_CHAR (age) | | 'year old.' | | sex

RETURN v_var

END

After a function with a default value is created, if no actual parameter value is provided for the parameter with the default value when the function is called, the function uses the default value for that parameter. However, when the caller provides the actual parameter for the default parameter, the function uses the actual parameter value. When you create a function, you can only set default values for input parameters, not for input / output parameters.

DECLARE

Var VARCHAR (32)

BEGIN

Var: =

Demo_fun ('user1', 30)

DBMS_OUTPUT.PUT_LINE (var)

Var: =

Demo_fun ('user2', age = > 40)

DBMS_OUTPUT.PUT_LINE (var)

Var: =

Demo_fun ('user3', sex = >' female'

Age = > 20)

DBMS_OUTPUT.PUT_LINE (var)

END

6.3 stored procedure 6.3.1 creation procedure

Establish a stored procedure

Establishing a stored procedure on ORACLE SERVER can be called by multiple applications, pass parameters to the stored procedure or return parameters to the stored procedure.

Create procedure syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name

([arg1 [IN | OUT | IN OUT]] type1 [DEFAULT value1]

[arg2 [IN | OUT | IN OUT]] type2 [DEFAULT value1]]

.

[argn [IN | OUT | IN OUT]] typen [DEFAULT valuen])

[AUTHID DEFINER | CURRENT_USER]

{IS | AS}

BEGIN

EXCEPTION

END procedure_name

Description: see the syntax description of the function for the related parameters.

Example 6. User connection registration record

CREATE TABLE logtable (userid VARCHAR2 (10), logdate date)

CREATE OR REPLACE PROCEDURE logexecution

IS

BEGIN

INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE)

END

Example 7. Delete specified employee record

CREATE OR REPLACE

PROCEDURE DelEmp

(v_empno IN employees.employee_id%TYPE)

AS

No_result EXCEPTION

BEGIN

DELETE FROM employees WHERE employee_id = v_empno

IF SQL%NOTFOUND THEN

RAISE no_result

END IF

DBMS_OUTPUT.PUT_LINE ('coded as' | v_empno | | 'employee has been deleted!')

EXCEPTION

WHEN no_result THEN

DBMS_OUTPUT.PUT_LINE ('warm reminder: the data you need does not exist!')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (SQLCODE | |'- -'| | SQLERRM)

END DelEmp

Example 8. Insert employee records:

CREATE OR REPLACE

PROCEDURE InsertEmp (

V_empno in employees.employee_id%TYPE

V_firstname in employees.first_name%TYPE

V_lastname in employees.last_name%TYPE

V_deptno in employees.department_id%TYPE

)

AS

Empno_remaining EXCEPTION

PRAGMA EXCEPTION_INIT (empno_remaining,-1)

/ *-1 is the error code that violates the unique constraint * /

BEGIN

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID)

VALUES (v_empno, sysdate, v_deptno)

DBMS_OUTPUT.PUT_LINE ('warm reminder: data record inserted successfully!')

EXCEPTION

WHEN empno_remaining THEN

DBMS_OUTPUT.PUT_LINE ('warm reminder: violation of data integrity constraints!')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (SQLCODE | |'- -'| | SQLERRM)

END InsertEmp

Example 9. Use stored procedures to insert data into the departments table.

CREATE OR REPLACE

PROCEDURE insert_dept

(v_dept_id IN departments.department_id%TYPE

V_dept_name IN departments.department_name%TYPE

V_mgr_id IN departments.manager_id%TYPE

V_loc_id IN departments.location_id%TYPE)

IS

Ept_null_error EXCEPTION

PRAGMA EXCEPTION_INIT (ept_null_error,-1400)

Ept_no_loc_id EXCEPTION

PRAGMA EXCEPTION_INIT (ept_no_loc_id,-2291)

BEGIN

INSERT INTO departments

(department_id, department_name, manager_id, location_id)

VALUES

(v_dept_id, v_dept_name, v_mgr_id, v_loc_id)

DBMS_OUTPUT.PUT_LINE ('insert department' | | v_dept_id | | 'successful')

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

RAISE_APPLICATION_ERROR (- 20000, 'Department code cannot be duplicated')

WHEN ept_null_error THEN

RAISE_APPLICATION_ERROR (- 20001, 'department code, department name cannot be empty')

WHEN ept_no_loc_id THEN

RAISE_APPLICATION_ERROR (- 20002,'no such location')

END insert_dept

/ * call instance 1:

DECLARE

Ept_20000 EXCEPTION

PRAGMA EXCEPTION_INIT (ept_20000,-20000)

Ept_20001 EXCEPTION

PRAGMA EXCEPTION_INIT (ept_20001,-20001)

Ept_20002 EXCEPTION

PRAGMA EXCEPTION_INIT (ept_20002,-20002)

BEGIN

Insert_dept (300, 'Department 300, 100, 2400)

Insert_dept (310, NULL, 100,2400)

Insert_dept (310, 'Department 310, 100,900)

EXCEPTION

WHEN ept_20000 THEN

DBMS_OUTPUT.PUT_LINE ('ept_20000 department code cannot be duplicated')

WHEN ept_20001 THEN

DBMS_OUTPUT.PUT_LINE ('ept_20001 department code, department name cannot be empty')

WHEN ept_20002 THEN

DBMS_OUTPUT.PUT_LINE ('ept_20002 does not have the location')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('other exception errors occurred in others')

END

Call instance 2:

DECLARE

Ept_20000 EXCEPTION

PRAGMA EXCEPTION_INIT (ept_20000,-20000)

Ept_20001 EXCEPTION

PRAGMA EXCEPTION_INIT (ept_20001,-20001)

Ept_20002 EXCEPTION

PRAGMA EXCEPTION_INIT (ept_20002,-20002)

BEGIN

Insert_dept (v_dept_name = > 'Department 310, v_dept_id = > 310

V_mgr_id = > 100, v_loc_id = > 2400)

Insert_dept (320,320 departments, v_mgr_id = > 100, v_loc_id = > 900)

EXCEPTION

WHEN ept_20000 THEN

DBMS_OUTPUT.PUT_LINE ('ept_20000 department code cannot be duplicated')

WHEN ept_20001 THEN

DBMS_OUTPUT.PUT_LINE ('ept_20001 department code, department name cannot be empty')

WHEN ept_20002 THEN

DBMS_OUTPUT.PUT_LINE ('ept_20002 does not have the location')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('other exception errors occurred in others')

END

, /

6.3.2 calling stored procedures

After the stored procedure is established, the user can call and run it in SQLPLUS, ORACLE development tools or third-party development tools as long as it is authorized. There are also three ways to pass parameters: by location, by name and by combination, the method of passing is the same as that of the function. ORACLE uses EXECUTE statements to implement calls to stored procedures:

EXEC [UTE] procedure_name (parameter1, parameter2 …)

Example 10:

EXECUTE logexecution

Example 11: query specified employee records

CREATE OR REPLACE

PROCEDURE QueryEmp

(v_empno IN employees.employee_id%TYPE

V_ename OUT employees.first_name%TYPE

V_sal OUT employees.salary%TYPE)

AS

BEGIN

SELECT last_name | | last_name, salary INTO v_ename, v_sal

FROM employees

WHERE employee_id = v_empno

DBMS_OUTPUT.PUT_LINE ('warm reminder: employees whose code is' | v_empno | | 'have been found!')

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('warm reminder: the data you need does not exist!')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (SQLCODE | |'- -'| | SQLERRM)

END QueryEmp

-- call

DECLARE

V1 employees.first_name%TYPE

V2 employees.salary%TYPE

BEGIN

QueryEmp (100,1, v2)

DBMS_OUTPUT.PUT_LINE ('name:' | | v1)

DBMS_OUTPUT.PUT_LINE ('salary:' | | v2)

QueryEmp (103, v1, v2)

DBMS_OUTPUT.PUT_LINE ('name:' | | v1)

DBMS_OUTPUT.PUT_LINE ('salary:' | | v2)

QueryEmp (104, v1, v2)

DBMS_OUTPUT.PUT_LINE ('name:' | | v1)

DBMS_OUTPUT.PUT_LINE ('salary:' | | v2)

END

Example 12. Calculate the total salary of the designated department and count the number of employees in it.

CREATE OR REPLACE

PROCEDURE proc_demo

(

Dept_no NUMBER DEFAULT 10

Sal_sum OUT NUMBER

Emp_count OUT NUMBER

)

IS

BEGIN

SELECT SUM (salary), COUNT (*) INTO sal_sum, emp_count

FROM employees WHERE department_id = dept_no

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('warm reminder: the data you need does not exist!')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (SQLCODE | |'- -'| | SQLERRM)

END proc_demo

DECLARE

V_num NUMBER

V_sum NUMBER (8,2)

BEGIN

Proc_demo (30, v_sum, v_num)

DBMS_OUTPUT.PUT_LINE ('warm Tip: total salary of Department No. 30:' | | v_sum | |', number of people:'| | v_num)

Proc_demo (sal_sum = > v_sum, emp_count = > v_num)

DBMS_OUTPUT.PUT_LINE ('warm Tip: total salary of Department No. 10:' | | v_sum | |', number of people:'| | v_num)

END

In PL/SQL programs, you can also create local functions and procedures within blocks that are not stored in the database but can be called repeatedly in the PL/SQL program that created them. Local functions and procedures are defined in the declaration section of the PL/SQL block and have the same syntax format as stored functions and procedures, but cannot use CREATE

OR REPLACE keyword.

Example 13: establish a local process to calculate the sum of wages in a designated department and count the number of employees in it

DECLARE

V_num NUMBER

V_sum NUMBER (8,2)

PROCEDURE proc_demo

(

Dept_no NUMBER DEFAULT 10

Sal_sum OUT NUMBER

Emp_count OUT NUMBER

)

IS

BEGIN

SELECT SUM (salary), COUNT (*) INTO sal_sum, emp_count

FROM employees WHERE department_id=dept_no

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('the data you need does not exist!')

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (SQLCODE | |'- -'| | SQLERRM)

END proc_demo

-- call the method:

BEGIN

Proc_demo (30, v_sum, v_num)

DBMS_OUTPUT.PUT_LINE (Total salary of Department'30:'| | v_sum | |', number of people:'| | v_num)

Proc_demo (sal_sum = > v_sum, emp_count = > v_num)

DBMS_OUTPUT.PUT_LINE (Total salary of Department No. 10:'| | v_sum | |', number of people:'| | v_num)

END

6.3.3 AUTHID

The AUTHID instruction in the procedure can tell ORACLE whose permission to run the procedure under. By default, the stored procedure will run as the caller's procedure, but with the privileges of the designer. This is called the designer right to run.

Example 14: set up the process, using AUTOID

DEFINER

Connect HR/qaz

DROP TABLE logtable

CREATE table logtable (userid VARCHAR2 (10), logdate date)

CREATE OR REPLACE PROCEDURE logexecution

AUTHID DEFINER

IS

BEGIN

INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE)

END

GRANT EXECUTE ON logexecution TO PUBLIC

CONNECT / AS SYSDBA

GRANT CONNECT TO testuser1 IDENTIFIED BY userpwd1

CONNECT testuser1/userpwd1

INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE)

EXECUTE HR.logexecution

CONNECT HR/qaz

SELECT * FROM HR.logtable

Example 15: set up the process, using AUTOID

CURRENT_USER

CONNECT HR/qaz

CREATE OR REPLACE PROCEDURE logexecution

AUTHID CURRENT_USER

IS

BEGIN

INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE)

END

GRANT EXECUTE ON logexecution TO PUBLIC

CONNECT testuser1/userpwd1

INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE)

EXECUTE HR.logexecution

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

ORACLE8i can support the concept of transaction in transactions. this subtransaction can do its own work, committing or rolling back independently of the parent transaction. by using this method, developers can successfully execute such a process, regardless of whether the parent transaction is committed or rolled back.

Example 16: establishment process, logging using automatic transaction processing

DROP TABLE logtable

CREATE TABLE logtable (

Username varchar2 (20)

Dassate_time date

Mege varchar2 (60)

);

CREATE TABLE temp_table (N number)

CREATE OR REPLACE PROCEDURE log_message (p_message varchar2)

AS

PRAGMA AUTONOMOUS_TRANSACTION

BEGIN

INSERT INTO logtable VALUES (user, sysdate, p_message)

COMMIT

END log_message

BEGIN

Log_message ('About to insert into temp_table')

INSERT INTO temp_table VALUES (1)

Log_message ('Rollback to insert into temp_table')

ROLLBACK

END

SELECT * FROM logtable

SELECT * FROM temp_table

Example 17: the establishment process, without using automatic transaction processing for logging

CREATE OR REPLACE PROCEDURE log_message (p_message varchar2)

AS

BEGIN

INSERT INTO logtable VALUES (user, sysdate, p_message)

COMMIT

END log_message

BEGIN

Log_message ('About to insert into temp_table')

INSERT INTO temp_table VALUES (1)

Log_message ('Rollback to insert into temp_table')

ROLLBACK

END

SELECT * FROM logtable

SELECT * FROM temp_table

6.3.5 steps for developing stored procedures

The steps for developing stored procedures, functions, packages and triggers are as follows:

6.3.5.1 Editing the source code of stored procedures using word editing software

Use word editing software to edit the source code of the stored procedure, and save the source code in text format when editing with a similar WORD word processing software.

6.3.5.2 interpret stored procedure programs in SQLPLUS or with debugging tools

Interpret stored procedure programs in SQLPLUS or with debug tools

When debugging under SQL >, you can use ORACLE commands such as START or GET to start the interpretation. Such as:

SQL > START c:\ stat1.sql

If you use the tuning tool, you can directly edit and click the appropriate button to generate the stored procedure.

6.3.5.3 debug the source until it is correct

We cannot guarantee that the stored procedure written at once will be correct. So the mode here is one of the tasks that every programmer must do. The main methods used in SQLPLUS are as follows:

L use SHOW

ERROR command to prompt the wrong location of the source code

Use the user_errors data dictionary to view the error locations of each stored procedure.

6.3.5.4 Authorization of execution rights to relevant users or roles

If the correctly modulated stored procedure is not authorized, only the creator can run it. Therefore, stored procedures as part of the application system must also be authorized to meet the requirements. Under SQL*PLUS, you can use the GRANT command to authorize the operation of a stored procedure.

GRANT syntax:

GRANT system_privilege | role

TO user | role | PUBLIC [WITH ADMIN OPTION]

GRANT object_privilege | ALL ON schema.object

TO user | role | PUBLIC [WITH GRANT OPTION]

-- example:

CREATE OR REPLACE PUBLIC SYNONYM dbms_job FOR dbms_job

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

6.3.5.5 Dictionary of process related data

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS

ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

Related permissions:

CREATE ANY PROCEDURE

DROP ANY PROCEDURE

In SQL*PLUS, you can use the DESCRIBE command to view the name of the procedure and its parameter table.

DESC [RIBE] Procedure_name

6.3.6 deleting procedures and functions

1. Delete process

You can use the DROP PROCEDURE command to delete unwanted procedures, with the following syntax:

DROP PROCEDURE [user.] Procudure_name

2. Delete function

You can use the DROP FUNCTION command to delete unwanted functions, with the following syntax:

DROP FUNCTION [user.] Function_name

-- delete the stored procedures and functions created by the above example

DROP PROCEDURE logexecution

DROP PROCEDURE delemp

DROP PROCEDURE insertemp

DROP PROCEDURE fireemp

DROP PROCEDURE queryemp

DROP PROCEDURE proc_demo

DROP PROCEDURE log_message

DROP FUNCTION demo_fun

DROP FUNCTION get_salary

6.3.7 comparison of procedures and functions

Using procedures and functions has the following advantages:

1, common code can be written and tested only once, and be called by any application that needs the code (such as: .NET, C++, JAVA, VB program, or DLL library).

2. This method of centralized writing, centralized maintenance and sharing (or reuse) simplifies the development and maintenance of the application and improves the efficiency and performance.

3. With this modular method, a complex problem and a large program can be gradually simplified into several simple and small program parts, which can be written and debugged respectively. Therefore, it makes the structure of the program clear, simple and easy to implement.

4. It can provide consistency in dealing with data, control flow, prompt information and so on among various developers.

5. Save memory space. They are stored in external memory in a compressed form and are put into memory for processing when called. And, if multiple users want to execute the same procedure or function, only one of them needs to be loaded in memory.

6. Improve the security and integrity of the data. By putting some operations on data into a procedure or function, you can restrict some users from performing these operations on the data by granting users permission to execute the process or not.

The same functions of procedures and functions are:

1. All use the parameters of IN mode to pass in data, and the parameters of OUT mode to return data.

2. Input parameters can accept default values, and can be passed or guided.

3. The actual parameters can be called using position representation, name representation or combination method.

4. There are declaration part, execution part and exception handling part.

5. Its management process includes creating, compiling, authorizing, deleting, displaying dependencies and so on.

Principles for using procedures and functions:

1. If you need to return multiple values and no values, use the procedure; if you only need to return one value, use the function.

2. The procedure is generally used to perform a specified action, and the function is generally used to calculate and return a value.

3. Functions can be called inside SQL statements (such as expressions) to complete complex calculation problems, but procedures cannot be called. So this is a feature of the function.

Thank you for reading! This is the end of this article on "sample analysis of stored procedures and functions in DB". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.