Oracle processes and functions

1. Benefits of using procedures or functions

1) ensure data security:For example, if a data table can be updated in an existing process, the Administrator does not have to grant the user the permission to directly access the data table, but grant the user the permission to access the process.

2) performance improvement:When using stored procedures, the amount of data transmitted over the network is small, reducing the amount of network transmission.

3) reduce memory:The stored procedure can use the shared memory feature of Oracle. When multiple users perform the same procedure, they only need to load a copy of the process to the memory. By sharing the same code among multiple users, You can significantly reduce the Oracle memory required by applications.

4) Development integrity and improvement of development efficiency.

2. similarities and differences between the two

1) The function must have a return type instead of a process.

2) functions can be executed independently, for example:Dbms_output.put_line ('5 + 6 = '| Tadd (5, 6 ));-- Tadd is a function.

 

The process cannot be executed in units, but the process cannot.;

 

3) functions can be embedded into SQL statements for execution, but not procedures. For example:Select my_pak.tadd (1, 2) from dual;

 

4) both input parameters are in, out, and in out. The default values are in.

3. Function example

1 -- Define a function
2 create or replace function numadd (num1 number, num2 number, num3 out number) return number
3
4 num number;
5 begin
6 num3: = num1-num2; -- outgoing parameter of out Parameter
7 num: = num1 + num2;
8 return num; -- Return Parameter
9 end;
10
11 -- use function return values
12 declare
13 num number;
14 num2 number;
15 begin
16 num: = numadd (5, 2, num2 );
17 dbms_output.put_line ('num = '| num); -- result: 7
18 dbms_output.put_line ('num2 = '| num2); -- result: 3
19 end;

 

3. Process example

3.1 stored procedure without parameters and return values

-- Stored procedure without parameters and return values
Create or replace procedure pro_test
As
Begin
Dbms_output.put_line ('stored procedure without parameters and return values ');
End;

-- Use
Declare
Begin
Pro_test;
End;

3.1 stored procedures with returned values

-- Enter the class id cid to return the maxagename and c_stulist of the maximum age of the class.
Create or replace procedure pro_test (CID varchar2, maxagename out student. name % Type
, C_stulist out sys_refcursor)
As
C_stu sys_refcursor;
Type record_stu is record (
Tname student. name % type,
Tage student. Age % Type
);
Re_stu record_stu;

Agetemp student. Age % type;
Nametemp student. name % type;
Begin
Open c_stu for select name, age from student where classid = CID;
C_stulist: = c_stu; -- Because c_stu is used in the process and no data is found after use, so...
Agetemp: = 0;
Loop
Fetch c_stu into re_stu;
Exit when c_stu % notfound;
Dbms_output.put_line ('name = '| re_stu.tname | 'Age =' | re_stu.tage );
If agetemp <re_stu.tage then
Agetemp: = re_stu.tage;
Nametemp: = re_stu.tname;
End if;
End loop;
Maxagename: = agetemp;
End;

-- Call a stored procedure
Declare
C_stu sys_refcursor;
Maxagename student. name % type;
Tname student. name % type;
Tage student. Age % type;
Begin
Pro_test ('c001', maxagename, c_stu );
Dbms_output.put_line ('maximum age: '| maxagename );
Loop
Fetch c_stu into tname, Tage;
Exit when c_stu % notfound;
Dbms_output.put_line ('name = '| tname | 'Age =' | Tage );
End loop;
End;