Functions (Learning Notes)

function (storage function) is also a more convenient storage structure, user-defined functions can be directly modulated by SQL statement or PL, function and process the biggest difference is that the function can have a return value,
The process can only rely on out or in-out to return data
Define function Syntax:
CREATE [OR REPLACE] Function functions ([parameters, ...])
return value type
[AUTHID [Definer | Current_User]]
As | | Is
[PRAGMA Autonomous_transaction;]
Statement part;
BEGIN
Procedure section;
[return value;]
EXCEPTION
Guide treatment;
END [function name];
Parameters that define the data receiving operation of the parameter pattern representation process, generally divided into in,out,in out 3 classes
create [or Replace]: represents the creation or substitution process, if the procedure exists, replaces it, creates a new one if it does not exist.
The AUTHID clause defines the owner permission for a procedure, Definer (the default) means that the definition is executed, or overrides the default behavior of the program with Current_User, which becomes the user permission
PRAGMA autonomous_transaction: Indicates that the process initiates an autonomous transaction, that an autonomous transaction can have the principal hang, that after the SQL is executed in the process, the user processes the commit or rolls back the autonomous transaction.
Then restore the master transaction
is basically similar to the syntax of a procedure, except that a return value type (return value type) declaration is required when defining a function

Define a function to return the system time

 create  or  replace  function   Datetime_fun  return  varchar2  as    begin  return  to_char (sysdate, " yyyy-mm-dd hh24:mi:ss   );  end   --  call  declare  begin   Dbms_output.put_line (datetime_fun);  end ; 

functions that bring out inputs and outputsCreate or Replace functionGetinfo_fun (Eno Emp.empno%Type, job out Emp.job%Typereturn varchar2  isv_name Emp.ename%TYPE; V_count Number;BEGIN    SELECT COUNT(ENO) intoV_count fromEmpWHEREEmpno=Eno; IFV_count>0  Then     SELECTEname,job intoV_name,job fromEmpWHEREEmpno=Eno; END IF; RETURNV_name;EndGetinfo_fun;--calledDECLAREv_id Emp.empno%TYPE:=&empno; V_name Emp.ename%TYPE; V_job Emp.job%TYPE;BEGINV_name:=Getinfo_fun (v_id,v_job); Dbms_output.put_line ('the employee number is:'||v_id||'Name:'||V_name||'Position:'||v_job);END;

example One, define a function to query employee's salary by employee number

CREATE OR REPLACE FUNCTIONGet_sal_fun (F_no EMP. EMPNO%TYPE)RETURN  Number    asv_sal EMP. SAL%TYPE;BEGIN  SELECTSAL intoV_sal fromEmpWHEREEMPNO=F_no; RETURNv_sal;END;--calledDECLAREv_id Emp.empno%TYPE:=&empno, V_sal emp.sal%TYPE;BEGINv_sal:=Get_sal_fun (v_id); Dbms_output.put_line ('Employee Number:'||v_id||'The salary is:'||v_sal); END;

The second Kind

-use procedure to invokeCREATE OR REPLACE PROCEDUREInvoke_proc asv_id Emp.empno%TYPE:=&empno;v_sal emp.sal%TYPE;BEGINv_sal:=Get_sal_fun (v_id); Dbms_output.put_line ('Employee Number:'||v_id||'The salary is:'||v_sal);END;EXECInvoke_proc;

Third Kind

SELECT get_sal_fun (& from dual;

Parameter mode

In mode

Example one, defining a function using the in

CREATE OR REPLACE FUNCTIONIn_fun (F_bVARCHAR2 DEFAULT 'Java Development Combat Classic',--The default parameter mode is inF_ainch VARCHAR2 DEFAULT 'Study Hard'                --explicitly define in parameter mode) RETURN VARCHAR2 asBEGIN  RETURNf_a;END; executionDECLAREv_aVARCHAR2( -); V_bVARCHAR2( -);BEGINV_b:=In_fun (v_a);   Dbms_output.put_line (V_b); Dbms_output.put_line (SQLERRM);END; Result: study ora well-0000: Normal, successful completion

Out mode

example Two, defining functions using out

CREATE OR REPLACE FUNCTIONOut_fun (f_a outVarchar2, F_b outVARCHAR2)RETURN VARCHAR2 asBEGINf_a:='Java Development Combat Classic'; F_b:='Oracle Development Classic'; RETURNF_b;END;--calledDECLAREv_aVARCHAR2( -); V_bVARCHAR2( -); V_resultVARCHAR2( -);BEGINV_result:=Out_fun (V_a,v_b);   Dbms_output.put_line (V_result); Dbms_output.put_line (SQLERRM);END;

example Three, through the function to complete the department increase

CREATE OR REPLACE FUNCTIONDept_inser_fun (F_dno dept.deptno%TYPE, F_dname dept.dname%TYPE, F_loc dept.loc%TYPE)RETURN  Number asV_count Number;BEGIN  SELECT COUNT(DEPTNO) intoV_count fromDeptWHEREDeptno=F_dno; IFV_count>0  Then    RETURN -1;--return Failure    ELSE      INSERT  intoDept (Deptno,dname,loc)VALUES(F_dno,f_dname,f_loc); COMMIT; RETURN 0; END IF;END;--calledDECLAREV_result Number;BEGINV_result:=Dept_inser_fun (&Deptno'&dname','&loc'); IFV_result= 0  ThenDbms_output. Put_Line ('new sector increases success'); ELSEDbms_output. Put_Line ('new sector increases failure'); END IF;END; VARV_sal Number; Call Get_sal_fun (&V_ID) intoV-Sal;PrintV_sal;

Functions (Learning Notes)