Oracle如何自定義函數(shù)

更新時間：2025年06月09日 09:43:11 作者：雷神樂樂

這篇文章主要介紹了Oracle如何自定義函數(shù)的問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

1.系統(tǒng)預定義函數(shù)

---聚合函數(shù) sum max min count avg median wm_concat
---常用函數(shù)：數(shù)學函數(shù) 字符串函數(shù) 時間 trunc (date,'q')
---分析函數(shù)： over()

ratio_to_report() NTILE()OVER() LEAD() LAG() wm_concat() first_value()
row_number() RANK() dense_rank()

2.Oracle自定義函數(shù)語法

CREATE OR REPLACE FUNCTION 函數(shù)名(參數(shù)1 數(shù)據(jù)類型，參數(shù)2 [IN | OUT] 數(shù)據(jù)類型……)
    RETURN 返回的數(shù)據(jù)類型 ---返回的如果是  VARCHAR2 類型，也不能給長度
    IS ---IS 或者 AS 隨便寫一個都可以
    聲明變量 ---聲明變量的時候記得給長度
BEGIN
    函數(shù)的具體邏輯;
    RETURN 聲明變量;
    --里面必須要有一個RETURN子句
---異常處理
END;

3.什么是編譯

編譯，就是把函數(shù)在數(shù)據(jù)庫里創(chuàng)建。

編譯就是將寫好的代碼放在數(shù)據(jù)庫里某個文件里存著，調用函數(shù)的時候，就會到數(shù)據(jù)庫里執(zhí)行存放的函數(shù)邏輯。

檢查函數(shù)編譯(創(chuàng)建)的時候有沒有報錯選中函數(shù)的名稱 > 鼠標右鍵 > view/edit

4.自定義函數(shù)練習題

示例：創(chuàng)建計算 1到 N 的和的函數(shù)

create or replace function fun_N(N number) return number
as
    f_sum number := 0; -- 存放臨時累加的和
begin
    for i in 1..N
        loop
            f_sum := f_sum + i;
        end loop;
    return f_sum;
    -- 定義異常
exception
    when others then
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
    -- 使用 DBMS_OUTPUT.PUT_LINE 過程輸出異常的錯誤消息。
    -- SQLERRM 是一個Oracle預定義的異常變量，它包含了最近一次數(shù)據(jù)庫運行時錯誤的描述。
end;
select fun_N(10)
FROM DUAL;

示例：假如 Oracle沒有 POWER 這個函數(shù),需要人工自己開發(fā)函數(shù)POWER_A

create or replace function POWER_A(N1 number, N2 number) return number
as
    f_power number := 1; -- 存放臨時累加的和
begin
    for i in 1..N2
        loop
            f_power := f_power * N1;
        end loop;
    return f_power;
    -- 定義異常
exception
    when others then
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;

select POWER_A(3,3) FROM DUAL;

第1次循環(huán)：v1 = 1 * 3 = 3

第2次循環(huán)：v1 = 3 * 3 = 9

第3次循環(huán)：v1 = 9 * 3 = 27 → 最終結果

示例：創(chuàng)建一個函數(shù)，根據(jù)傳遞給函數(shù)的員工編號返回員工的姓名；

create or replace function F_ENAME(P_EMPNO number) 
return varchar2 -- 參數(shù)不能定義長度
as
    v_ename varchar2(10); -- 變量要定義長度
begin
    select ename into v_ename from emp where EMPNO = P_EMPNO;
    return v_ename;
    -- 定義異常
exception
    when others then
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
select F_ENAME(7369)
FROM DUAL;

開發(fā)自定義函數(shù)，返回數(shù)字的階乘，例如：SELECT F_factorial(4) FROM DUAL;

create or replace function F_factorial(P_N number) return number -- 參數(shù)不能定義長度
as
    v1 number := 1; -- 臨時存放累加值
begin
    for i in 1..P_N
        loop
            v1 := v1 * i;
        end loop;
    return v1;
    -- 定義異常
exception
    when others then
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;

SELECT F_factorial(4) FROM DUAL;

開發(fā)自定義函數(shù)，比較兩個數(shù)字的大小 P_MAX_MIN(M NUMBER,N NUMBER)返回比較大的值；

create or replace function P_MAX_MIN(M NUMBER, N NUMBER) return number
as
    temp number;
begin
    if M > N THEN
        temp := M;
    else
        temp := N;
    end if;
    return temp;
end;

select P_MAX_MIN(12, 9) from DUAL;

創(chuàng)建一個函數(shù)，函數(shù)的功能是根據(jù)傳入的一個崗位名稱，將這個崗位的員工數(shù)量返回

create or replace function F_AMOUNT(P_JOB VARCHAR2) return number
as
    v_ct number;
begin
    select count(1) into v_ct from emp where JOB = P_JOB;
    return v_ct;
end;

select F_AMOUNT('MANAGER') from DUAL;