Oracle專題14之存儲(chǔ)過程與函數(shù)

1、存儲(chǔ)過程與函數(shù)的概述

  • 存儲(chǔ)過程和存儲(chǔ)函數(shù)：是存儲(chǔ)在數(shù)據(jù)庫中的被命名的PLSQL塊，供所有用戶程序調(diào)用，完成特定功能的子程序。

    a、存儲(chǔ)過程和存儲(chǔ)函數(shù)的區(qū)別

  • 是否使用return返回值。（即存儲(chǔ)過程不返回值，存儲(chǔ)函數(shù)返回值）

    b、第一個(gè)存儲(chǔ)過程與函數(shù)的程序

  • 用存儲(chǔ)過程或者函數(shù)實(shí)現(xiàn)輸出‘Hello Everyone!’。 
        --創(chuàng)建存儲(chǔ)過程
    CREATE OR REPLACE PROCEDURE first_proc
    IS
    BEGIN
        DBMS_OUTPUT.put_line('我的過程');
        DBMS_OUTPUT.put_line('Hello Everyone!');
    END;
    --創(chuàng)建函數(shù)
    CREATE OR REPLACE FUNCTION first_func
    RETURN VARCHAR
    IS 
    BEGIN
        DBMS_OUTPUT.put_line('我的函數(shù)');
        RETURN 'hello everyone';
    END;
    • 可以看到，當(dāng)執(zhí)行完代碼之后，左側(cè)的all objects中已經(jīng)的Functions和Procedures中已經(jīng)分別保存了存儲(chǔ)過程FIRST_FUNC和存儲(chǔ)函數(shù)FREST_PROC。
      Oracle專題14之存儲(chǔ)過程與函數(shù)
  • 調(diào)用存儲(chǔ)過程 
    BEGIN 
    first_proc;
END;
  • 調(diào)用存儲(chǔ)函數(shù) 
    BEGIN
    DBMS_OUTPUT.put_line(first_func);
END;

    2、存儲(chǔ)過程的創(chuàng)建

    a、創(chuàng)建存儲(chǔ)過程的語法

    CREATE [OR REPLACE] PROCEDURE procedure_name 
[(argument1 [{ IN | OUT | IN OUT}] type, argument2 [ { IN | OUT | IN OUT}] type, ...)] （IN 為輸入?yún)?shù)、OUT輸出參數(shù)、IN | OUT為輸入輸出參數(shù)）
{ IS | AS }（即使沒有聲明部分，IS | AS 也不能夠省略，IS或者AS選擇哪一個(gè)都可以）
    聲明部分，類型.變量的說明
BEGIN
    執(zhí)行部分
EXCEPTION
    可選的異常錯(cuò)誤處理部分
END;

    b、代碼示例：創(chuàng)建輸入?yún)?shù)的存儲(chǔ)過程

        CREATE OR REPLACE PROCEDURE proc1 
    (v_empno IN emp01.empno%TYPE)
    IS 
    BEGIN
        --根據(jù)員工號(hào)刪除指定的員工信息
        DECLARE FROM emp01 WHERE empno = v_empno;
        --判斷是否刪除成功
        IF SQL%NOTFOUND THEN
            -- -20000 ~ -20999之間
            RAISE_APPLICATION_ERROR(-20008, '指定刪除的員工不存在');
        ELSE
            DBMS_OUTPUT。put_line('刪除成功');
    END;
  • 在存儲(chǔ)過程或者是存儲(chǔ)函數(shù)中，一般不需要在其中添加COMMIT或ROLLBACK，誰調(diào)用由誰添加COMMIT或ROLLBACK

    c、創(chuàng)建帶有輸出參數(shù)的存儲(chǔ)過程

    CREATE OR REPLACE PROCEDURE proc2
        (v_deptno IN NUMBER, v_avgsal OUT NUMBER, v_cnt out NUMBER)
         IS
BEGIN
         SELECT AVG(sal), count(*)
         INTO v_avgsal, v_cnt
         FROM emp WHERE deptno = v_deptno;
 EXCEPTION
     WHEN NO_DATA_FOUND THEN
         dbms_output.put_line('沒有此部門');
     WHEN OTHERS THEN
         dbms_output.put_line(SQLERRM);
 END;

    d、創(chuàng)建帶有輸入輸出參數(shù)的存儲(chǔ)過程

    CREATE OR REPLACE PROCEDURE proc3
(v_num1 IN OUT NUMBER, v_num2 IN OUT NUMBER)
AS
         v_temp NUMBER := 0;
BEGIN
         v_temp := v_num1;
         v_num1 := v_num2;
         v_num2 := v_temp;
END;

3、存儲(chǔ)函數(shù)的創(chuàng)建

a、創(chuàng)建存儲(chǔ)函數(shù)的語法

    CREATE [OR REPLACE] FUNCTION function_name
    [ argument1[ { IN | OUT | IN OUT }] type, 
    argument2[{IN | OUT | IN OUT }] type, ...)]
    RETURN return_type
    { IS | AS}
        聲明部分，類型.變量的說明
    BEGIN
        執(zhí)行部分，函數(shù)體
    EXCEPTION
        可選的異常錯(cuò)誤處理部分
    END;

b、創(chuàng)建帶有輸入?yún)?shù)的存儲(chǔ)函數(shù)

  • 根據(jù)部門編號(hào)返回該部門的總工資： 
    CREATE OR REPLACE FUNCTION func1
(v_deptno IN NUMBER)
RETURN NUMBER
IS
             v_sumsal NUMBER;
BEGIN
             SELECT SUM(SAL) INTO v_sumsal FROM emp WHERE deptno = v_deptno;
             RETURN v_sumsal;
EXCEPTION
             WHEN NO_DATA_FOUND THEN
                 DBMS_OUTPUT.put_line('沒有此部門');
             WHEN OTHERS THEN 
                 DBMS_OUTPUT.put_line(SQLERRM);
END;

    c、創(chuàng)建帶有輸出參數(shù)的存儲(chǔ)函數(shù)

  • 根據(jù)員工號(hào)輸出員工的姓名和員工的工資，并且返回員工的年收入： 
    CREATE OR REPLACE FUNCTION func2
(v_empno IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE)
RETURN NUMBER
IS 
        v_salsum NUMBER;
BEGIN
        SELECT ename, sal, (sal + nvl(comm, 0)) * 12 INTO v_name, v_sal, v_salsum
        FROM emp WHERE empno = v_empno;
        RETURN v_salsum;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.put_line('沒有此員工');
        WHEN OTHERS THEN 
            DBMS_OUTPUT.put_line(SQLERRM);
END;

    d、創(chuàng)建帶有輸入輸出參數(shù)的存儲(chǔ)函數(shù)

  • 求兩個(gè)數(shù)的平方和，并輸出兩個(gè)數(shù)的平方 
    CREATE OR REPLACE FUNCTION func3
(n1 IN OUT NUMBER, n2 IN OUT NUMBER)
RETURN NUMBER
AS
BEGIN
    n1 := n1*n1;
    n2 := n2*n2;
    RETURN n1+n2;
END;

4、存儲(chǔ)過程的調(diào)用和刪除

a、調(diào)用存儲(chǔ)過程

  • 方法一：ORACLE使用EXECUTE語句來實(shí)現(xiàn)對(duì)存儲(chǔ)過程的調(diào)用：（屬于SQLPlus命令） 
    EXEC[UTE] Procedure_name(parameter1, parameter2...)
  • 方法二：在PL/SQL代碼中直接調(diào)用，如： 
    BEGIN
    procedure_name(parameter1, parameter2...)
END;

    b、調(diào)用存儲(chǔ)過程的不同情況

    注意：本專題第2節(jié)存儲(chǔ)過程的創(chuàng)建后，Oracle數(shù)據(jù)庫中保存了4個(gè)存儲(chǔ)過程。
    Oracle專題14之存儲(chǔ)過程與函數(shù)

    10余年的四方臺(tái)網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對(duì)設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對(duì)一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。成都全網(wǎng)營(yíng)銷的優(yōu)勢(shì)是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整四方臺(tái)建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)公司從事“四方臺(tái)網(wǎng)站設(shè)計(jì)”,“四方臺(tái)網(wǎng)站推廣”以來，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

  • 調(diào)用無參數(shù)的存儲(chǔ)過程：直接引用過程名即可。
  • 調(diào)用帶有輸入?yún)?shù)的存儲(chǔ)過程。
  • 調(diào)用帶有輸出參數(shù)的參數(shù)過程。

  • 調(diào)用帶有輸入輸出參數(shù)的存儲(chǔ)過程。

  • SQLPlus環(huán)境： 
    -- 調(diào)用無參的存儲(chǔ)過程
SQL> set serveroutput on
SQL> exec first_proc
我的過程
Hello Everyone!
PL/SQL procedure successfully completed
-- 調(diào)用帶有輸入?yún)?shù)的存儲(chǔ)過程
SQL> exec proc1(1234);
begin proc1(1234); end;
ORA-20008: 指定刪除的員工不存在
ORA-06512: 在 "SCOTT.PROC1", line 9
ORA-06512: 在 line 1
  • PL/SQL環(huán)境： 
    -- 調(diào)用無參的存儲(chǔ)過程
BEGIN
first_proc;
END;
-- 調(diào)用帶有輸入?yún)?shù)的存儲(chǔ)過程
BEGIN
proc1(1234);
END;
-- 調(diào)用帶有輸出參數(shù)的存儲(chǔ)過程
DECLARE
v_avgsal NUMBER;
v_count NUMBER;
BEGIN
PROC2(10, v_avgsal, v_count);
DBMS_OUTPUT.put_line('平均工資:' || v_avgsal);
DBMS_OUTPUT.put_line('總?cè)藬?shù)：' || v_count);
END;
--調(diào)用帶有輸入輸出參數(shù)的存儲(chǔ)過程
DECLARE
v_n1 NUMBER := 5;
v_n2 NUMBER := 10;
BEGIN
PROC3(v_n1, v_n2);
DBMS_OUTPUT.put_line('N1:' || v_n1);
DBMS_OUTPUT.put_line('N2:' || v_n2);
END;

    c、刪除存儲(chǔ)過程的語法格式

  • 使用DROP PROCEDURE命令，語法如下： 
    DROP PROCEDURE [user.] Procedure_name

    5、存儲(chǔ)函數(shù)的調(diào)用和刪除

  • 注意：本專題第3節(jié)存儲(chǔ)函數(shù)的創(chuàng)建后，Oracle數(shù)據(jù)庫中保存了4個(gè)存儲(chǔ)函數(shù)。

a、調(diào)用存儲(chǔ)函數(shù)

  • 存儲(chǔ)過程和存儲(chǔ)函數(shù)的調(diào)用都是一樣，可分為：
    1、調(diào)用無參數(shù)的存儲(chǔ)函數(shù)；
    2、調(diào)用帶有輸入?yún)?shù)的存儲(chǔ)函數(shù)；
    3、調(diào)用帶有輸出參數(shù)的存儲(chǔ)函數(shù)；
    4、調(diào)用帶有輸入輸出參數(shù)的存儲(chǔ)函數(shù)。

  • PL.SQL環(huán)境： 
    --調(diào)用無參數(shù)的函數(shù)
BEGIN
dbms_output.put_line(first_func);
END;
-- 調(diào)用帶有輸入?yún)?shù)的函數(shù)
BEGIN
dbms_output.put_line('部門的工資總額：' || func1(&no));
END;
-- 調(diào)用帶有輸出參數(shù)的函數(shù)
DECLARE
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
v_salsum NUMBER;
BEGIN
v_salsum := func2(&no, v_name, v_sal);
DBMS_OUTPUT.put_line('姓名：' || v_name);
DBMS_OUTPUT.put_line('工資：' || v_sal);
DBMS_OUTPUT.put_line('年收入：' || v_salsum);
END;
-- 調(diào)用帶有輸入輸出參數(shù)的函數(shù)
DECLARE
v_n1 NUMBER := 5;
v_n2 NUMBER := 6;
v_sum number;
BEGIN
v_sum := func3(v_n1, v_n2);
DBMS_OUTPUT.put_line('n1的平方：' || v_n1);
DBMS_OUTPUT.put_line('n2的平方：' || v_n2);
DBMS_OUTPUT.put_line('n1和n2的平方和：' || v_sum);
END;

    b、刪除函數(shù)過程

  • 可以使用DROP FUNCTION命令，語法如下： 
    DROP FUNCTION [user.] Function_name

    6、附加說明

  • 參數(shù)默認(rèn)值的問題：
  • 過程和函數(shù)都可以在聲明過程或者函數(shù)參數(shù)時(shí)，使用DEFAULT關(guān)鍵字為輸入?yún)?shù)指定默認(rèn)值。
  • 示例代碼：求部門的年收入。（存儲(chǔ)函數(shù)） 
    CREATE OR REPLACE FUNCTION func2
(v_empno IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE)
RETURN NUMBER
IS 
        v_salsum NUMBER;
BEGIN
        SELECT ename, sal, (sal + nvl(comm, 0)) * 12 INTO v_name, v_sal, v_salsum
        FROM emp WHERE empno = v_empno;
        RETURN v_salsum;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.put_line('沒有此員工');
        WHEN OTHERS THEN 
            DBMS_OUTPUT.put_line(SQLERRM);
END;
  • 調(diào)用此存儲(chǔ)函數(shù)： 
    --調(diào)用
DECLARE
    v_totalsal NUMBER;
BEGIN
    v_totalsal := func5;
    DBMS_OUTPUT.put_line(v_totalsal);
END;

分享標(biāo)題：Oracle專題14之存儲(chǔ)過程與函數(shù)
網(wǎng)址分享：http://bm7419.com/article42/jcsehc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、軟件開發(fā)、建站公司、云服務(wù)器、小程序開發(fā)、面包屑導(dǎo)航

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

成都app開發(fā)公司