Functions in Oracle

Basic Application of a function

1. Create a function (in the SQL window)

Create or replace function get_hello_msg
Return varchar2
Begin
Return 'Hello world ';
End get_hello_msg;

The function must have a return value. The return value of this function is of the varchar2 type.

2. View function information in the data dictionary (SQL window)

Select object_name, object_type, status from user_objects where lower (object_name) = 'get _ hello_msg'

Check the status column. If VALID is displayed, the function is available. If INVALID is displayed, the function is INVALID.

The reason for unavailability may be a syntax error. For example, if a semicolon is missing during function creation, remember that each end must be followed by a semicolon.

3. View function return values (Command window)

Set serverout on;
Declare msg varchar2 (20 );
Begin
Msg: = get_hello_msg;
Dbms_output.put_line (msg );
End;
/

The set serverout on statement indicates that the server output information is displayed in the window.

Function with two parameters

1. Create a function (SQL window)

Create or replace function get_stu_grade (stu_grade number) return number
Begin
Declare standard_grade number;
Begin
Standard_grade: = stu_grade-60;
If standard_grade <0 then
Return 0;
End if;
Return 1;
End;
End get_stu_grade;

2. Call a function (Command window or SQL window)

Select get_stu_grade (90) from dual; // 1
Select get_stu_grade (60) from dual; // 1
Select get_stu_grade (59) from dual; // 0

Certainty of the three functions

Create or replace function get_stu_grade (stu_grade number) return number
Deterministic
Begin
Declare standard_grade number;
Begin
Standard_grade: = stu_grade-60;
If standard_grade <= 0 then
Return 0;
End if;
Return 1;
End;
End get_stu_grade;

Deterministic adds the certainty of the function. This means that we enter the same score, and the returned results should be consistent. If you enter a 90-point value for the first time and then enter a 90-point value for the second time, the returned value must be the same as that for the first time. Then, Oracle will directly obtain the first result and no longer execute this function again, improved efficiency. When cannot this keyword be used? For example, this function uses the system time and the system time affects the return value. Therefore, the time of each execution system is theoretically different, so the last result cannot be taken directly.