Oracle中的函數

一函數的基本應用

1 建立函數(SQL視窗中)

create or replace function get_hello_msg
return varchar2 as
begin
       return 'hello world';
end get_hello_msg;

函數必須有傳回值，該函數的傳回值是varchar2類型。

2 在資料字典查看函數資訊(SQL視窗)

select object_name,object_type,status from user_objects where lower(object_name) = 'get_hello_msg'

注意看status這一欄，若顯示VALID說明該函數可用；若顯示INVALID則說明該函數不合法。

停用原因可能是語法錯誤，比如在建立函數時少了分號，記住每一個end後面都要有分號。

3 查看函數傳回值(Command視窗)

set serverout on;
declare msg varchar2(20);
begin
 msg:=get_hello_msg;
 dbms_output.put_line(msg);
end;
/

其中set serverout on語句表示在視窗中顯示伺服器輸出資訊。

二帶參數的函數

1 建立函數(SQL視窗)

create or replace function get_stu_grade(stu_grade number) return number as
begin
       declare standard_grade number;
       begin
               standard_grade:=stu_grade - 60;
               if standard_grade < 0 then
                  return 0;
               end if;
               return 1;
       end;
end get_stu_grade;

2 調用函數(Command視窗或SQL視窗)

select get_stu_grade(90) from dual; // 1
select get_stu_grade(60) from dual; // 1
select get_stu_grade(59) from dual; // 0

三函數的確定性

create or replace function get_stu_grade(stu_grade number) return number
deterministic as
begin
       declare standard_grade number;
       begin
               standard_grade:=stu_grade - 60;
               if standard_grade <=0 then
                  return 0;
               end if;
               return 1;
       end;
end get_stu_grade;

deterministic增加了函數的確定性。意思就是我們輸入相同的一個分數，其返回的結果應該一致。如果第一次輸入了一個90分，第二次再輸入90分的時候傳回值肯定與第一次一樣，那麼Oracle就會直接拿到第一次的結果，不再重複執行該函數，提高的效率。什麼時候不能用該關鍵字呢？比如該函數使用了系統時間而系統時間影響了傳回值。那麼每一次執行系統時間理論上是不一樣的，所以不能直接拿上次的結果。