ORACLE存儲過程

ORACLE存儲過程

前奏：
1：必要的概念：
    ORACLE  提供可以把 PL/SQL  程序存儲在數據庫中，並可以在任何地方來運行它。這樣就叫存儲過
    程或函數。
    過程和函數統稱為 PL/SQL 子程序，他們是被命名的 PL/SQL 塊，均存儲在數據庫中，並
    通過輸入、輸出參數或輸入/輸出參數與其調用者交換信息。過程和函數的唯一區別是函數總向調
    用者返回數據，而過程則不返回數據。
	
2：建立存儲過程
	在  ORACLE  SERVER 上建立存儲過程,可以被多個應用程序調用,可以向存儲過程傳遞參數,也可以向存儲
	過程傳回參數
	
創建過程語法:
	CREATE [OR REPLACE] PROCEDURE Procedure_name
	[ (argment [ { IN | IN OUT }] Type,
	argment [ { IN | OUT | IN OUT } ] Type ]
	[ AUTHID DEFINER | CURRENT_USER ]
	{ IS | AS }
	<類型.變量的說明> 
	BEGIN
	<執行部分>
	EXCEPTION
	<可選的異常錯誤處理程序>
	END;	
	
3：例題：
--定義一個存儲過程，獲取給定部門的工資總和（通過out參數），要求部門號和工資總和定義為參數
		create or replace procedure get_sal3(dept_id number , sum_salary out number)
		is
			cursor salary_cursor is select salary
			from employees where department_id = dept_id;
		begin
			sum_salary := 0;
			for c in salary_cursor loop
				  sum_salary := sum_salary + c.salary;
			end loop;
			dbms_output.put_line(sum_salary);
		end;

		select get_sal3(60,)


--對給定部門的員工進行加薪操作，若其到公司的時間是在（？-95）期間，為其加薪5%
--(95,98)加薪3%，（98，現在）加薪1%，得到以下返回結果：
--此次加薪公司每個月額外付出多少成本（定義一個out型的輸出參數）
		create or replace procedure get_money(dept_id number , temp_sal out number)
		is
			v_i number(4,2):=0;
			cursor salary_cursor is select employee_id , salary ,hire_date
			from employees where department_id = dept_id;
		begin
			temp_sal := 0;
			for c in salary_cursor loop
				 if to_char(c.hire_date,'yyyy')<'1995'  then v_i := 0.05;
				 elsif to_char(c.hire_date,'yyyy')<'1998' then v_i := 0.03;
				 else v_i := 0.01 ;
				 end if;
				 
				 --更新工資
				 update employees set salary = salary * (1+v_i)
				 where employee_id = c.employee_id;
				 --計算付出的成本
				 temp_sal := temp_sal + c.salary*v_i;
			end loop;
			dbms_output.put_line(temp_sal);
		end;	
		
4：調用存儲過程：
        declare
	    v_temp number(10):=0;
		begin
			get_money(80,v_temp);
		end;