Oracle資料庫---存儲過程、存儲函數

--創建存儲過程
CREATE OR REPLACE PROCEDURE first_proc
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('我是過程');
DBMS_OUTPUT.PUT_LINE('Hello Everyone!');
END;

--創建函數
CREATE OR REPLACE FUNCTION first_func
RETURN VARCHAR2
IS
BEGIN
DBMS_OUTPUT.put_line('我是函數');
RETURN 'Hello Everyone!';
END;

--調用存儲過程
BEGIN
first_proc;
END;

--調用存儲函數
BEGIN
DBMS_OUTPUT.put_line(first_func);
END;

--1：創建輸入參數的存儲過程
--根據員工號刪除指定的員工信息
CREATE OR REPLACE PROCEDURE proc1
(v_empno IN empnew.empno%TYPE)
IS
BEGIN
--根據員工號刪除指定的員工信息
DELETE FROM empnew WHERE empno = v_empno;
--判斷是否刪除成功
IF SQL%NOTFOUND THEN
-- -20000~ -20999之間
RAISE_APPLICATION_ERROR(-20008,'指定刪除的員工不存在！');
ELSE
DBMS_OUTPUT.put_line('刪除成功！');
END IF;
END;

--2;創建帶有輸出參數的存儲過程
--求指定部門的平均工資和總人數
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;

--3：創建帶有輸入輸出參數的存儲過程
--實現兩個數的交換
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 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;

--2:創建帶有輸出參數的存儲函數
--根據員工號輸出員工的姓名和員工的工資，並且返回員工的年收入
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;

--3：創建帶有輸入輸出參數的存儲函數
--求兩個數的平方和，並輸出兩個數的平方

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;

--調用無參的存儲過程
begin
first_proc;
end;

--調用帶有輸入參數的存儲過程
begin
proc1(1234);
end;

--調用帶有輸出參數的存儲過程
DECLARE
v_avgsalary NUMBER;
v_count NUMBER;
BEGIN
PROC2(10,v_avgsalary,v_count);
DBMS_OUTPUT.put_line('平均工資:'||v_avgsalary);
DBMS_OUTPUT.put_line('總人數:'||v_count);
END;

--調用帶有輸入輸出參數的存儲過程
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;

--刪除存儲過程
DROP PROCEDURE proc1;

--調用無參的函數
BEGIN
DBMS_OUTPUT.put_line(first_func);
END;

--調用帶有輸入參數的函數
BEGIN
DBMS_OUTPUT.put_line('部門的工資總額'||func1(&no));
END;

--調用帶有輸出參數的函數
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;

--調用帶有輸入輸出參數的函數
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('平方和：'||v_sum);
END;

--刪除存儲函數
DROP FUNCTION func1;

--求部門的年收入
CREATE OR REPLACE FUNCTION func4
(v_deptno IN NUMBER DEFAULT 10,v_t IN NUMBER)
RETURN NUMBER
IS
v_sumsal NUMBER;
BEGIN
SELECT SUM((sal+nvl(comm,0))*12)
INTO v_sumsal
FROM emp
WHERE deptno = v_deptno;
DBMS_OUTPUT.put_line('測試：'||v_t);
RETURN v_sumsal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('沒有此部門');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;

--調用
declare
v_totalsal number;
begin
v_totalsal := func4(v_t=>1);
dbms_output.put_line(v_totalsal);
end;