ORACLE存儲函數
前奏:
必要的概念:
ORACLE 提供可以把 PL/SQL 程序存儲在數據庫中,並可以在任何地方來運行它。這樣就叫存儲過
程或函數。
過程和函數統稱為 PL/SQL 子程序,他們是被命名的 PL/SQL 塊,均存儲在數據庫中,並
通過輸入、輸出參數或輸入/輸出參數與其調用者交換信息。過程和函數的唯一區別是函數總向調
用者返回數據,而過程則不返回數據。
1:創建函數
CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type
{ IS | AS }
<類型.變量的說明>
BEGIN
FUNCTION_body
EXCEPTION
其它語句
END;
2:說明:
1) OR REPLACE 為可選. 有了它, 可以或者創建一個新函數或者替換相同名字的函數, 而不會出現沖突
2) 函數名後面是一個可選的參數列表, 其中包含 IN, OUT 或 IN OUT 標記. 參數之間用逗號隔開. IN 參數標記表示傳遞給函數的值在該函數執行中不改變OUT 標記表示一個值在函數中進行計算並通過該參數傳遞給調用語句; IN OUT 標記表示傳遞給函數的值可以變化並傳遞給調用語句. 若省略標記, 則參數隱含為 IN。
3) 因為函數需要返回一個值, 所以 RETURN 包含返回結果的數據類型.
3:例題:
--存儲函數結構
create function func_name(dept_id number , salary number)
return number
is
--函數使用過程中 需要聲明的變量 記錄類型 cursor
begin
--函數的執行體
exception
--處理函數執行過程中異常
end;
--例題1 寫一個返回hellow world的function
create or replace function hello_func(temp varchar2)
return varchar2
is
begin
return 'hello world'|| temp;
end;
--調用函數:
begin
dbms_output.put_line(hello_func);
end;
select hello_func('cui') from dual;
--例二 寫一個返回系統時間的函數 如果函數沒有形參 切勿加()
create function get_sysdate
return date
is
v_date date;
begin
v_date := sysdate;
return v_date;
end;
select get_sysdate() from dual;
--創建兩個參數相加的 存儲函數
create function add_func(temp_A number , temp_B number)
return number
is
temp_sum number(10);
begin
temp_sum := temp_A + temp_B;
return temp_sum;
end;
select add_func(1,1) from dual;
--定義一個函數:獲取給定部門的工資總和,要求:部門號定義為參數,工資總額定義為返回值;
create function get_sal(dept_id number)
return number
is
v_sumsal number(10):=0;
cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
for c in salary_cursor loop
v_sumsal := v_sumsal + c.salary;
end loop;
return v_sumsal;
end;
select get_sal(60) from dual;
--定義一個函數 獲取指定部門的工資總和 和 該部門的員工總數(定義為out類型的參數)
--要求部門號定義為參數,工資總額定義為返回值
create function get_sall(dept_id number , total_count out number)
return number
is
v_sumsal number(10):=0;
cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
total_count := 0;
for c in salary_cursor loop
v_sumsal := v_sumsal + c.salary;
total_count := total_count + 1;
end loop;
return v_sumsal;
end;
4:函數的調用
declare
v_num number(5):=0;
begin
dbms_output.put_line(get_sall(60,v_num));
dbms_output.put_line(v_num);
end;