子程序的優點:
模塊化 將程序分解為邏輯模塊 可重用性 可以被任意數目的程序調用 可維護性 簡化維護操作 安全性 通過設置權限,使數據更安全
一個購票過程可以分為很多個子過程,分別完成。
創建過程的語法:
CREATE [OR REPLACE] PROCEDURE <procedure name> [(<parameter list>)] IS|AS <local variable declaration> BEGIN <executable statements> [EXCEPTION <exception handlers>] END;
CREATE OR REPLACE PROCEDURE find_emp (emp_no NUMBER) AS empname VARCHAR2(20); BEGIN SELECT ename INTO empname FROM EMP WHERE empno = emp_no; DBMS_OUTPUT.PUT_LINE('雇員姓名是 '|| empname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('雇員編號未找到'); END find_emp;
我們還可以:
--查詢用戶所定義的存儲過程 select distinct name from user_source where type = 'PROCEDURE'; --查看存儲過程定義的源碼內容(PL/SQL語句) select text from user_source where name = 'P_TEST'; --如果定義有錯誤,查看錯誤原因 Show error procedure 存儲過程名 --刪除存儲過程 Drop procedure 存儲過程名;
--統計滿足指定工資數的員工的數量:帶輸入參數in的存儲過程
Create or replace procedure p_total_sal(var_sal in int) is Var_count int; Begin Select count(*) into var_count from emp where sal > var_sal; Dbms_output.put_line(' 符合要求的員工總數為: ' || var_count); Exception When others then Dbms_output.put_line('未知錯誤'); End;
--定義一個存儲過程返回指定部門的員工總數:帶返回值out的存儲過程
Create or replace procedure p_get_emp(var_deptno int, var_total out int) as Var_n int; Begin Select count(*) into var_n from emp where deptno = var_deptno; Var_total := var_n; --總數由參數返回 End;
--使用 Declare Var_s int; Begin p_get_emp(10, var_s); Dbms_output.put_line('返回的值為' || var_s); End;
--定義一個存儲過程,通過該存儲過程能返回一個結果集(游標)。
Create or replace procedure p_get_datas(mycur out sys_refcursor) is Begin Open mycur for select * from emp where deptno = 10; End;
--調用: Declare Var_cur sys_refcursor; --接收參數 Row emp%rowtype; Begin p_get_datas(var_cur); --無需再次打開,因為在存儲過程中已經打開過了 Loop Fetch var_cur into row; Exit when var_cur%notfound; Dbms_output.put_line(row.ename || ' ' || row.job); End loop; End;
--輸入輸出參數
--根據員工編號返回他的工資的存儲過程 Create or replace procedure p_get_sal(var_n in out int) is Begin Select sal into var_n from emp where empno = var_n); End;
--調用: Declare Var_s int; Begin Var_s := &n; p_get_sal(var_s); Dbms_output.put_line('他的工資為:' || var_s); End;
--存儲過程的使用
1 命令方式:execute 存儲過程名;
2 在PL/SQL中:直接使用 存儲過程名 即可
調用存儲過程時傳遞參數的方式。
1、按照位置方式傳遞。
Swap(num1,num2);
2、按名稱方式傳遞。
swap(p2=>num2,p1=>num1);
(p1,p2是定義存儲過程時參數名字)
GRANT EXECUTE ON find_emp TO MARTIN; GRANT EXECUTE ON swap TO PUBLIC;(所有數據庫用戶)
CREATE [OR REPLACE] FUNCTION <function name> [(param1,param2)] RETURN <datatype> IS|AS [local declarations] BEGIN Executable Statements; RETURN result; EXCEPTION Exception handlers; END;
定義函數的限制: 函數只能接受 IN 參數,而不能接受 IN OUT 或 OUT 參數 形參不能是 PL/SQL 類型 函數的返回類型也必須是數據庫類型 訪問函數的兩種方式: 使用 PL/SQL 塊 使用 SQL 語句
創建函數
CREATE OR REPLACE FUNCTION fun_hello RETURN VARCHAR2 IS BEGIN RETURN '朋友,您好'; END;從 SQL 語句調用函數:
SELECT fun_hello FROM DUAL;
例
CREATE OR REPLACE FUNCTION item_price_range (price NUMBER) RETURN VARCHAR2 AS min_price NUMBER; max_price NUMBER; BEGIN SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price FROM itemfile; IF price >= min_price AND price <= max_price THEN RETURN '輸入的單價介於最低價與最高價之間'; ELSE RETURN '超出范圍'; END IF; END;
DECLARE P NUMBER := 300; MSG VARCHAR2(200); BEGIN MSG := item_price_range(300); DBMS_OUTPUT.PUT_LINE(MSG); END;
過 程
函 數
作為 PL/SQL 語句執行
作為表達式的一部分調用
在規格說明中不包含 RETURN 子句
必須在規格說明中包含 RETURN 子句
不返回任何值
必須返回單個值
可以包含 RETURN 語句,但是與函數不同,它不能用於返回值
必須包含至少一條 RETURN
語句
程序包規范
CREATE [OR REPLACE] PACKAGE package_name IS|AS [Public item declarations] [Subprogram specification] END [package_name];
程序包主體
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS [Private item declarations] [Subprogram bodies] [BEGIN Initialization] END [package_name];
例
CREATE OR REPLACE PACKAGE pack_me IS PROCEDURE order_proc (orno VARCHAR2); FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2; END pack_me;
CREATE OR REPLACE PACKAGE BODY pack_me AS PROCEDURE order_proc (orno VARCHAR2) IS stat CHAR(1); BEGIN SELECT ostatus INTO stat FROM order_master WHERE orderno = orno; …… END order_proc; FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2 IS icode VARCHAR2(5); ocode VARCHAR2(5); BEGIN …… END order_fun; END pack_me;
CREATE OR REPLACE PACKAGE cur_pack IS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE; PROCEDURE ord_pro(vcode VARCHAR2); END cur_pack;
CREATE OR REPLACE PACKAGE BODY cur_pack AS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE IS SELECT * FROM order_master WHERE VENCODE=vcode; PROCEDURE ord_pro(vcode VARCHAR2) IS or_rec order_master%ROWTYPE; BEGIN OPEN ord_cur(vcode); LOOP FETCH ord_cur INTO or_rec; EXIT WHEN ord_cur%NOTFOUND; DBMS_OUTPUT.PUT_LIne('返回的值為' || or_rec.orderno); END LOOP; END ord_pro; END cur_pack;
USER_OBJECTS 視圖包含用戶創建的子程序和程序包的信息
SELECT object_name, object_type FROM USER_OBJECTS WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');
USER_SOURCE 視圖存儲子程序和程序包的源代碼
SELECT line, text FROM USER_SOURCE WHERE NAME='TEST';