oracle之函數：

• 1、函數與過程的區別
• 2、函數的創建
• 3、函數的調用
• • a、在SQL語句中直接調用
  • b、使用輸出語句調用函數
  • c、sqlplus:綁定函數來做
• 4、修改和刪除函數
• 5、查看函數
• 6、練習：
• • a、1. 定義一個函數，用于計算emp表中某個部門的平均工資。
  • b、寫一個函數，傳入員工編號，返回所在部門名稱
  • c、寫一個函數，可以查詢某個員工的年收入，包括獎金
  • d：定義函數，輸入部門編號，查詢出該部門的員工總數。
  • e、定義函數，使用記錄類型作為返回類型，根據指定的部門號返回其對應的部門信息

1、函數與過程的區別

過程可以接收傳遞的值可以沒有返回值

函數	可以接收傳遞的值	必須有返回值

函數必須有return子句
函數中不能包含對數據庫執行操作的語句，如DML語句(增刪改查)

2、函數的創建

格式：

創建 Create or replace function function_name[(argment in|out|in out type)] Return return_type -- return語句必須有，后面跟一個return值的類型(varchar2(10)、number(10)都可以) Is|as [declare_section] --聲明部分，用來聲明變量 BeginFunction_body --也必須要有return語句 Exception … End;

注意：begin語句體中也必須要寫return語句，return的是一個值。

函數來講一般都是用有in參數的，out參數的和in out參數的都不經常使用

創建一個簡單的函數：（輸出一句話）

方案1：

create or replace function sayhello return varcahr2 --varcahr2不能有長度 is say varchar2(20); --這個地方是可以定義長度的。 beginsay := 'hello world';return say; end;

方案2：

create or replace function sayhello2 return varcahr2 --varcahr2不能有長度 is beginreturn 'hello world'; end;

下面是函數的調用：（函數相當于表達式或者說是相當于一個變量），通過函數的調用可以使返回的值輸出。

select sayhello2 from dual;

例：定義一個函數，用于計算emp表中某個部門的平均工資。

create or replace FUNCTION get_avgsal(dno in emp.deptno% TYPE) RETURN NUMBER isf_avgsal emp.sal% TYPE; BEGINSELECT avg(sal) into f_avgsal from emp where deptno=dno;return f_avgsal; END;

3、函數的調用

a、在SQL語句中直接調用

select distinct deptno,get_avgsal(10) from emp where deptno=10; select get_avgsal(10) 平均值　from dual;

b、使用輸出語句調用函數

set serveroutput on BEGINdbms_output.put_line(avgsal(10)); END ;

c、sqlplus:綁定函數來做

var say varchar2(20); exec :say:=sayhello2(); print say; select :say from dual;

4、修改和刪除函數

修改：or replace
刪除：drop function 函數名
注意：刪除時不要加括號和參數

5、查看函數

select object_name from user_objects where object_type='FUNCTION';COL TEXT FORMAT A60 select line,text from user_source where name='GET_ENAME';

6、練習：

a、1. 定義一個函數，用于計算emp表中某個部門的平均工資。

create or replace function get_avgsal(dno emp.deptno%type) return number isf_avgsal emp.sal%type; beginselect avg(sal) into f_avgsal from emp where deptno=dno;return f_avgsal; end;begindbms_output.put_line(get_avgsal(10)); end;select get_avgsal(10) from dual;

b、寫一個函數，傳入員工編號，返回所在部門名稱

create or replace function d_name(eno emp.empno%type) return varchar2 isf_name dept.dname%type; beginselect dname into f_name from dept where deptno = (select deptno from emp where empno=eno);return f_name; end;select d_name(7369) from dual;

c、寫一個函數，可以查詢某個員工的年收入，包括獎金

create or replace function sumsal(eno emp.empno%type) return emp.sal%type issum_num number; beginselect nvl(sal+comm,sal)*12 into sum_num from emp where empno=eno;return sum_num; end;declarev_no emp.empno%type := &a; begindbms_output.put_line(sumsal(v_no)); end;

d：定義函數，輸入部門編號，查詢出該部門的員工總數。

create or replace function emp_sum(dno emp.deptno%type) return number istotal number; beginselect count(*) into total from emp group by deptno having deptno=dno;return total; end;begindbms_output.put_line('該部門總人數為：' || emp_sum(&a)); end;

e、定義函數，使用記錄類型作為返回類型，根據指定的部門號返回其對應的部門信息

方案一：

create or replace function s_dept(dno dept.deptno%type) return dept%rowtype is d_message dept%rowtype; begin select * into d_message from dept where deptno=dno; return d_message; end;declarev_no dept.deptno%type := &a; begin dbms_output.put_line(s_dept(v_no).deptno || ' ' || s_dept(v_no).dname || ' ' || s_dept(v_no).loc); end;

方案二：

create or replace function s_dept(dno in dept.deptno%type) return dept%rowtype isd_message dept%rowtype; beginselect * into d_message from dept where deptno=dno; return d_message; end; declared_no dept.deptno%type:=&a;d_mess dept%rowtype:=s_dept(d_no); begindbms_output.put_line(d_mess.deptno||' '||d_mess.dname||' '||d_mess.loc); end;

總結

以上是生活随笔為你收集整理的oracle 函数的使用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。