ストアドファンクション

[kandlly]

CREATE OR REPLACE FUNCTION RIVUS.PRODUCT ( P_NUM1 IN NUMBER, P_NUM2 IN NUMBER ) RETURN NUMBER IS vSum NUMBER(2) DEFAULT 0; -- vSum NUMBER(2) := 0; BEGIN FOR i IN 1..P_NUM2 LOOP vSum := vSum + P_NUM1; END LOOP; RETURN vSum ; END; /

[kandlly]

SELECT PRODUCT(5,6) FROM DUAL ;

[kandlly]

関数の構文

CREATE OR REPLACE FUNCTION ファンクション名 　[　(パラメータ変数１ [ IN | OUT | INT OUT ] データ型, 　　　: 　　パラメータ変数n [ IN | OUT | INT OUT ] データ型）　]　 　RETURN データ型 { AS | IS }

　　/　宣言セクション / BEGIN 　　/　実行可能セクション / EXCEPTION 　　/　例外セクション / END [ ファンクション名 ];

[kandlly]

create or replace function get_dept( 　v_dept_id in char) 　return varchar2 as

　v_dept_name varchar2(10);

begin

　select dept_name into v_dept_name from dept 　　where dept_id = v_dept_id; 　return v_dept_name;

end get_dept;

---

SQL> select get_dept('D01') from dual;

GET_DEPT('D01')

[kandlly]

+､-､*､/ 加算、減算、乗算、除算 := 代入 => 結合 || 連結 ** 指数 =､<､>､<=､>=､<>､!=､~=､^=､IS NULL､LIKE､BETWEEN､IN 比較 NOT､AND､OR 論理否定､論理積、論理和

[kandlly]

変数名　表名.列名%TYPE; テーブルの列の型と同じ型で変数が定義される。 テーブルの型変更に、PL/SQLを変更しないで済むメリットがある。 NOT NULL制約のある列でも、変数にNOT NULLは適用されないのでNULLの代入が可能

変数名 表名%ROWTYPE; %ROWTYPE は表から取得したレコード全体を定義できる。

[kandlly]

EXCEPTION 　WHEN 例外名１ TEHN 　　例外１の場合のエラー処理; 　WHEN 例外名２ TEHN 　　例外２の場合のエラー処理; 　　： 　　： 　WHEN OTHERS THEN 　　上記以外の例外の場合のエラー処理; END; エラーが発生すると、例外セクションに制御が渡されます。

例外セクションでは、WHEN句で指定された、対応する例外処理が実行されます。

どの例外にも該当しない場合は、WHEN OTHERSで定義された例外処理が実行されます。

[kandlly]

exception 　　when TOO_MANY_ROWS then 　　　-- 例外「TOO_MANY_ROWS」が発生した場合の処理 　　　dbms_output.put_line('複数レコード取得されました'); 　　when others then 　　　-- 「TOO_MANY_ROWS」以外の例外が発生した場合の処理 　　　dbms_output.put_line('その他エラー');

[kandlly]

IF 条件１ THEN 　　条件１がTRUEの場合に処理実行 ELSEIF 条件２ THEN 　　条件２がTRUEの場合に処理実行 ELSE 　　条件がFALSEの場合に処理実行 END IF;

---

eclare sarary number(5); begin 　： 　： 　if sarary >= 2000 then 　　dbms_output.put_line('2000以上'); 　elsif sarary >= 1000 then 　　dbms_output.put_line('1000～2000'); 　else 　　dbms_output.put_line('1000未満'); 　end if;

end;

[kandlly]

--获取某部门的工资总和 CREATE OR REPLACE FUNCTION get_salary( Dept_no NUMBER, Emp_count OUT NUMBER) RETURN NUMBER IS V_sum NUMBER; BEGIN SELECT SUM(SALARY), count(*) INTO V_sum, emp_count FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no; RETURN v_sum; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END get_salary;

[kandlly]

CREATE OR REPLACE PROCEDURE QueryEmp (v_empno IN employees.employee_id%TYPE, v_ename OUT employees.first_name%TYPE, v_sal OUT employees.salary%TYPE) AS BEGIN SELECT last_name || last_name, salary INTO v_ename, v_sal FROM employees WHERE employee_id = v_empno; DBMS_OUTPUT.PUT_LINE('温馨提示:编码为'||v_empno||'的员工已经查到!'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END QueryEmp; --调用 DECLARE v1 employees.first_name%TYPE; v2 employees.salary%TYPE; BEGIN QueryEmp(100, v1, v2); DBMS_OUTPUT.PUT_LINE('姓名:'||v1); DBMS_OUTPUT.PUT_LINE('工资:'||v2); QueryEmp(103, v1, v2); DBMS_OUTPUT.PUT_LINE('姓名:'||v1); DBMS_OUTPUT.PUT_LINE('工资:'||v2); QueryEmp(104, v1, v2); DBMS_OUTPUT.PUT_LINE('姓名:'||v1); DBMS_OUTPUT.PUT_LINE('工资:'||v2); END;

[kandlly]

CREATE OR REPLACE PROCEDURE proc_demo ( dept_no NUMBER DEFAULT 10, sal_sum OUT NUMBER, emp_count OUT NUMBER ) IS BEGIN SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count FROM employees WHERE department_id = dept_no; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END proc_demo;

DECLARE V_num NUMBER; V_sum NUMBER(8, 2); BEGIN Proc_demo(30, v_sum, v_num); DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和：'||v_sum||',人数：'||v_num); Proc_demo(sal_sum => v_sum, emp_count => v_num); DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和：'||v_sum||',人数：'||v_num); END;