oracle 函数的使用

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]      --声明部分，用来声明变量
Begin
  Function_body   --也必须要有return语句
Exception
…
End;

注意：begin语句体中也必须要写return语句，return的是一个值。

函数来讲一般都是用有in参数的，out参数的和in out参数的都不经常使用

创建一个简单的函数：（输出一句话）

方案1：

create or replace function sayhello return varcahr2 --varcahr2不能有长度
is
say varchar2(20); --这个地方是可以定义长度的。
begin
	say := 'hello world';
	return say;
end;

方案2：

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

下面是函数的调用：（函数相当于表达式或者说是相当于一个变量），通过函数的调用可以使返回的值输出。

select sayhello2 from dual;

例：定义一个函数，用于计算emp表中某个部门的平均工资。

create or replace FUNCTION get_avgsal(dno in emp.deptno% TYPE)
RETURN NUMBER
is
	f_avgsal emp.sal% TYPE;
BEGIN
	SELECT 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
BEGIN
	  dbms_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
is
	f_avgsal emp.sal%type;
begin
    select avg(sal) into f_avgsal from emp where deptno=dno;
    return f_avgsal;
end;

begin
    dbms_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
is
	f_name dept.dname%type;
begin
    select 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
is
	sum_num number;
begin
    select nvl(sal+comm,sal)*12 into sum_num from emp where empno=eno;
    return sum_num;
end;

declare
    v_no emp.empno%type := &a;
begin
    dbms_output.put_line(sumsal(v_no));    
end;

d：定义函数，输入部门编号，查询出该部门的员工总数。

create or replace function emp_sum(dno emp.deptno%type)
return number
is
	total number;
begin
    select count(*) into total from emp group by deptno having deptno=dno;
    return total;
end;

begin
    dbms_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;

declare
	v_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
is
    d_message dept%rowtype;
begin
    select * into d_message from dept where deptno=dno; 
    return d_message;
end;
declare
    d_no dept.deptno%type:=&a;
    d_mess dept%rowtype:=s_dept(d_no);
begin
    dbms_output.put_line(d_mess.deptno||'   '||d_mess.dname||'    '||d_mess.loc);
end;