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

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.

Create function

The syntax 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 parameter. 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 constant or variable, but arguments to OUT and IN OUT modes must be variables.

In general, the OR REPALCE keyword is used only when confirming that the function_name function is a new function or a function to be updated, otherwise it is easy to delete useful functions.

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

-- get the total salary of a department

SQL > create or replace

2 function f_get_salary (

3 Dept_no NUMBER

4 Emp_count OUT NUMBER)

5 RETURN NUMBER

6 is

7 V_sum NUMBER

8 BEGIN

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

10 FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no

11 RETURN v_sum

12 EXCEPTION

13 WHEN NO_DATA_FOUND THEN

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

15 WHEN OTHERS THEN

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

17 END f_get_salary

18 /

Function created.

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...]

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.

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.

SQL > DECLARE

2 V_num NUMBER

3 V_sum NUMBER

4 BEGIN

5 V_sum: = f_get_salary (emp_count = > v_num, dept_no = > 10)

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

7 end

8 /

Total salary of department No. 10: 4400, number of people: 1

PL/SQL procedure successfully completed.

SQL > var V_sum NUMBER

SQL > var V_num NUMBER

SQL > exec: V_sum:=f_get_salary (: dept_no,:V_num)

PL/SQL procedure successfully completed.

SQL > print: V_sum: V_num

V_SUM 4400 V_NUM 1SQL > select: V_sum,: V_num from dual;:V_SUM: V_NUM 4400 1SQL > run

1 begin

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

3 * end

Total salary of department No. 10: 4400, number of people: 1

SQL > DECLARE

2 V_num NUMBER

3 V_sum NUMBER

4 BEGIN

5 V_sum: = f_get_salary (10 minutes emptied count = > v_num)

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

7 end

8 /

Total salary of department No. 10: 4400, number of people: 1

PL/SQL procedure successfully completed.

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 2:

CREATE OR REPLACE FUNCTION f_demo_fun (

Name VARCHAR2

Age INTEGER

Sex VARCHAR2 DEFAULT 'male')

RETURN VARCHAR2

IS

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.

SQL > DECLARE

2 Var VARCHAR (32)

3 BEGIN

4 Var: = f_demo_fun ('user1', 30)

5 DBMS_OUTPUT.PUT_LINE (var)

6 Var: = f_demo_fun ('user2', age = > 40)

7 DBMS_OUTPUT.PUT_LINE (var)

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

9 DBMS_OUTPUT.PUT_LINE (var)

10 END

11 /

User1:30, male

User2:40, male

User3:20, female

PL/SQL procedure successfully completed.

3 stored procedure

3.1 creation process

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 3. Delete specified employee record

CREATE OR REPLACE PROCEDURE P_DelEmployees

(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 P_DelEmployees

/

3.2 call stored procedure

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 …)

SQL > exec P_DelEmployees

Warm Tip: the data you need does not exist!

PL/SQL procedure successfully completed.

SQL > declare

2 v_Employeesid number

3 begin

4 v_Employeesid:=300

5 P_DelEmployees (v_Employeesid)

6 end

7 /

Warm Tip: the data you need does not exist!

PL/SQL procedure successfully completed.

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.

Process-related data dictionary

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

Delete 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

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.

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.