Function (learning notes), function learning notes

Function (learning notes), function learning notes

Function (storage function)It is also a convenient storage structure. User-defined functions can be directly called by SQL statements or PL/SQL. The biggest difference between a function and a process is that a function can return values,
The process can only rely on OUT or in out to return data.
Define function Syntax:
CREATE [or replace] FUNCTION ([parameter,...])
RETURN Value Type
[AUTHID [DEFINER | CURRENT_USER]
AS | IS
[PRAGMA AUTONOMOUS_TRANSACTION;]
Declaration part;
BEGIN
Program Section;
[RETURN value;]
EXCEPTION
Always process;
END [function name];
The parameter defines the parameter mode to indicate the data receiving operations IN the process, which are generally divided into three types: IN, OUT, and in out.
CREATE [or replace]: indicates the creation OR replacement process. If the process exists, it is replaced. If it does not exist, a new
The AUTHID clause defines the owner permission of a process. DEFINER (default) indicates that the DEFINER permission is executed, or the default behavior of the program is overwritten by CURRENT_USER, Which is changed to user permission.
PRAGMA AUTONOMOUS_TRANSACTION: indicates that an autonomous transaction is started during the process. The autonomous transaction can suspend the main transaction. After the SQL statement is executed during the process, the user processes and submits or rolls back the autonomous transaction,
Then restore the master transaction
Similar to the process syntax, the only difference is that a RETURN value type (RETURN value type) declaration is required when defining a function.

Define a function to return system time

Create or replace function datetime_funRETURN VARCHAR2ASBEGIN RETURN to_char (SYSDATE, 'yyyy-mm-dd hh24: mi: ss'); END; -- call DECLAREBEGIN dbms_output.put_line (datetime_fun); END;

The create or replace function getinfo_fun (eno emp. empno % type, job out emp. job % type) return varchar2 is v_name emp. ename % TYPE; v_count NUMBER; begin select count (eno) INTO v_count FROM emp WHERE empno = eno; IF v_count> 0 then select ename, job INTO v_name, job FROM emp WHERE empno = eno; end if; RETURN v_name; end getinfo_fun; -- call DECLARE v_id emp. empno % TYPE: = & empno; v_name emp. ename % TYPE; v_job emp. job % TYPE; BEGIN v_name: = getinfo_fun (v_id, v_job); dbms_output.put_line ('employee ID: '| v_id | 'name:' | v_name | 'position: '| v_job); END;

 

 

 

Example 1,The definition function uses employee numbers to query employees' salaries.

Create or replace function GET_SAL_FUN (F_NO EMP. EMPNO % TYPE) return number as V_SAL EMP. SAL % TYPE; begin select sal into V_SAL from emp where empno = F_NO; RETURN V_SAL; END; -- call DECLARE v_id emp. empno % TYPE: = & empno; v_sal emp. sal % TYPE; BEGIN v_sal: = get_sal_fun (v_id); dbms_output.put_line ('employee ID: '| v_id |' salary: '| v_sal); END;

Second

-Use the PROCEDURE to call create or replace procedure invoke_procASv_id emp. empno % TYPE: = & empno; v_sal emp. sal % TYPE; BEGIN v_sal: = get_sal_fun (v_id); dbms_output.put_line ('employee ID: '| v_id |' salary: '| v_sal); END; EXEC invoke_proc;

Third

Use an SQL statement to call SELECT get_sal_fun (& v_id) FROM dual;

Parameter Mode

IN Mode

Example 1: Define a function using IN

 

Create or replace function in_fun (f_ B VARCHAR2 DEFAULT 'java development practices classic ', -- the DEFAULT parameter mode is in f_a IN VARCHAR2 DEFAULT 'learn' -- specify the IN parameter mode) RETURN VARCHAR2ASBEGIN RETURN f_a; END; run DECLARE v_a VARCHAR2 (50); v_ B VARCHAR2 (50); BEGIN v_ B: = in_fun (v_a); BEGIN (v_ B); dbms_output.put_line (SQLERRM ); results: good learning ORA-0000: normal, successful completion

OUT Mode

Example 2,Define the function to use OUT

Create or replace function out_fun (f_a OUT Varchar2, f_ B OUT VARCHAR2) RETURN VARCHAR2ASBEGIN f_a: = 'java development classic '; f_ B: = 'oracle development classic'; RETURN f_ B; END; -- call DECLARE v_a VARCHAR2 (100); v_ B VARCHAR2 (100); v_result VARCHAR2 (100); BEGIN v_result: = out_fun (v_a, v_ B); dbms_output.put_line (v_result ); dbms_output.put_line (SQLERRM); END;

Example 3,Add a department using the function

Create or replace function dept_inser_fun (f_dno dept. deptno % TYPE, f_dname dept. dname % TYPE, f_loc dept. loc % TYPE) return numberas v_count NUMBER; begin select count (deptno) INTO v_count FROM dept WHERE deptno = f_dno; IF v_count> 0 then return-1; -- else insert into dept (deptno, dname, loc) VALUES (f_dno, f_dname, f_loc); COMMIT; RETURN 0; end if; END; -- call DECLARE v_result NUMBER; BEGIN v_result: = dept_inser_fun (& deptno, '& dname',' & loc '); IF V_RESULT = 0 THEN DBMS_OUTPUT.PUT_LINE ('new Department added successfully '); ELSE DBMS_OUTPUT.PUT_LINE ('new Department addition failed'); end if; END; VAR v_sal NUMBER; CALL get_sal_fun (& v_id) INTO v-sal; print v_sal;