oracleチュートリアル27ストアドプロシージャと関数（ストアドプロシージャとストアド関数の作成）

Oracle Tutorial 27 Stored Procedures

ストアドプロシージャの作成：

例：

--1: Create a stored procedure for input parameters -Delete the specified employee information according to the employee number CREATE OR REPLACE PROCEDURE proc1 (v_empno IN empnew.empno%TYPE) IS BEGIN -Delete the specified employee information according to the employee number DELETE FROM empnew WHERE empno = v_empno -Determine whether the deletion is successful IF SQL%NOTFOUND THEN - Between -20000~ -20999, the range of custom exception specified error number RAISE_APPLICATION_ERROR(-20008,'The employee specified for deletion does not exist!') ELSE DBMS_OUTPUT.put_line('Delete successfully!') END IF END

--2 Create a stored procedure with output parameters -Find the average salary and total number of people in the specified department 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('There is no such department!') WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLERRM) END --3: Create a stored procedure with input and output parameters --Realize the exchange of two numbers 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

ストレージ機能の作成：

例：

--1: Create a stored function with input parameters --Return the total salary of the department according to the department number 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('There is no such department!') WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM) END
--2: Create a stored function with output parameters --According to the employee number, output the employee's name and employee's salary, and return the employee's annual income 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('There is no such employee!') WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLERRM) END --3: Create a stored function with input and output parameters -Find the sum of the squares of two numbers, and output the squares of the two numbers 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

（注：ストアドプロシージャとストアド関数は通常、シートサブルーチンによって呼び出され、トランザクション操作は呼び出し元によって実行される必要があるため、一般的なストアドプロシージャとストアド関数には、コミットまたはロールバックは必要ありません（特別な場合を除く）。