函數(學習筆記)，函數學習筆記

函數(學習筆記)，函數學習筆記

函數（存儲函數）也是一種較為方便的存儲結構，用戶定義函數可以被SQL語句或者PL/SQL直接調，函數和過程最大的區別在於，函數可以有返回值，
而過程只能依靠OUT 或者IN OUT返回數據
定義函數語法：
CREATE [OR REPLACE] FUNCTION 函數([參數，...]])
RETURN 返回值類型
[AUTHID [DEFINER | CURRENT_USER]]
AS || IS
[PRAGMA AUTONOMOUS_TRANSACTION;]
聲明部分;
BEGIN
程序部分;
[RETURN 返回值;]
EXCEPTION
導常處理;
END [函數名];
參數中定義參數模式表示過程的數據的接收操作，一般分為IN,OUT,IN OUT 3類
CREATE [OR REPLACE]：表示創建或者替換過程，如果過程存在則替換，如果不存在就創建一個新的
AUTHID子句定義了一個過程的所有者權限，DEFINER（默認）表示定義者權限執行，或者用CURRENT_USER覆蓋程序的默認行為，變為使用者權限
PRAGMA AUTONOMOUS_TRANSACTION:表示過程啟動一個自治事務，自治事務可以讓主事掛起，在過程中執行完SQL後，由用戶處理提交或者回滾自治事務，
然後恢復主事務
和過程的語法基本相似，唯一不同的是在定義函數時候需要有返回值類型(RETURN 返回值類型)聲明

定義一個函數返回系統時間

CREATE OR REPLACE FUNCTION datetime_fun
RETURN VARCHAR2
AS
BEGIN
   RETURN to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss');
END;
--調用
DECLARE
BEGIN
  dbms_output.put_line(datetime_fun);
END;

帶出輸入和輸出的函數
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;
--調用
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('員工編號是:'||v_id||' 姓名:'||v_name||' 職位:'||v_job);
END;

示例一、定義函數通過員工編號來查詢員工的工資

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;

--調用
DECLARE
 v_id           emp.empno%TYPE:=&empno;
 v_sal          emp.sal%TYPE;
 BEGIN
   v_sal:=get_sal_fun(v_id);
   dbms_output.put_line('員工編號：'||v_id||' 的工資為: '||v_sal);
 END;

第二種

-使用過程來調用
CREATE OR REPLACE PROCEDURE invoke_proc
AS
v_id           emp.empno%TYPE:=&empno;
v_sal              emp.sal%TYPE;
BEGIN
   v_sal:=get_sal_fun(v_id);
   dbms_output.put_line('員工編號：'||v_id||' 的工資為: '||v_sal);
END;

EXEC invoke_proc ;

 第三種

使用sql語句來調用
 SELECT get_sal_fun(&v_id) FROM dual;

參數模式

IN模式

示例一、定義函數使用IN

CREATE OR REPLACE FUNCTION in_fun(
    
    f_b  VARCHAR2 DEFAULT 'Java開發實戰經典',                     --默認的參數模式為in
     f_a IN VARCHAR2 DEFAULT '好好學習'                --明確定義IN參數模式
) 
RETURN VARCHAR2
AS
BEGIN
  RETURN f_a;
END;

執行
DECLARE
    v_a VARCHAR2(50);
    v_b VARCHAR2(50);
BEGIN
   v_b:=in_fun(v_a);
   dbms_output.put_line(v_b);
   dbms_output.put_line(SQLERRM);
END;
結果:
好好學習
ORA-0000: normal, successful completion

OUT模式

示例二、定義函數使用OUT

CREATE OR REPLACE FUNCTION out_fun(
       f_a OUT Varchar2,
       f_b OUT VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
  f_a:='Java開發實戰經典';
  f_b:='Oracle開發實戰經典';
  RETURN f_b;
END;

--調用
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;

示例三、通過函數完成部門增加

CREATE OR REPLACE FUNCTION dept_inser_fun(
  f_dno             dept.deptno%TYPE,
  f_dname           dept.dname%TYPE,
  f_loc             dept.loc%TYPE
)
RETURN NUMBER
AS 
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;

--調用
DECLARE
      v_result     NUMBER;
BEGIN
  v_result:=dept_inser_fun(&deptno,'&dname','&loc');
   IF V_RESULT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('新部門增加成功');
  ELSE
    DBMS_OUTPUT.PUT_LINE('新部門增加失敗');
  END IF;
END;

 VAR v_sal NUMBER;
 CALL get_sal_fun(&v_id) INTO v-sal;
 print v_sal;