函数与存储过程是数据库操作中很重要的一部分，为了更好的理解,我就不做太多的理论讲解,下面是我为了把今天的内容融合到一起自己设计的一个需求:
用我们现在手上有的两张表:emp、dept。emp表中有员工工资,奖金等信息,dept表中有部门信息
有时候我们会遇到这样的业务:财务部门将统计出来的工资分发到每个部门,由各个部门自己发工资(个人虚构的),在这个过程中我们就需要将每个部门的总工资算出来,于是就有了今天的代码:
准备部分(复习):
①准备表:
创建一个存放最终数据的表:
CREATE TABLE getmoney(
deptno NUMBER, --部门编号
d_name VARCHAR2(20), --部门名字
money NUMBER, --该部门工资总额
isget VARCHAR(20) --是否领取
)
②准备知识点:
如果只是计算数据用function,如果需要修改数据用procedure
③主体部分:
function部分:
CREATE OR REPLACE FUNCTION get_result(
p_deptno IN NUMBER
)
RETURN NUMBER
AS
result_money NUMBER;
BEGIN
result_money := 0;
FOR my_cursor IN (SELECT * FROM emp WHERE emp.deptno=p_deptno)
LOOP
result_money := result_money+nvl(my_cursor.sal,0)+nvl(my_cursor.comm,0);
END LOOP;
RETURN result_money;
END;
说明 :function必须返回一个结果,在定义部分"return 返回类型",在块中用"return 返回值",该值必须是前面定义的数据类型
procedure部分:
CREATE OR REPLACE PROCEDURE p_add_data(
p_deptno NUMBER,
p_name VARCHAR2,
p_money NUMBER,
p_isget VARCHAR2 DEFAULT '否'
)
AS
BEGIN
INSERT INTO getmoney(deptno,d_name,money,isget)
VALUES(p_deptno,p_name,p_money,p_isget);
COMMIT;
END;
说明 :在定义参数的时候可以使用默认值(如p_isget VARCHAR2 DEFAULT '否'),表示如果在调用该过程时没有传入该参数的值,则系统为使用默认值来代替,定义默认值时必须放在最后定义.注意在procedure中没有return,如果想对外返回一个结果可以定义参数为out或者in out,见下行:
不论是function还是procedure都有3种参数:
输入参数 in:表示该参数只能接收数据,不能对外提供数据读取
输出参数 out:表示该参数只能被读取,不能修改
输入输出参数 in out:功能结合了上面两者,注意不能写为out in
接下来我们就可以直接从外部通过方法调用来完成我们需要的业务了:
DECLARE
money NUMBER;
BEGIN
money := 0;
FOR my_deptno IN (SELECT * FROM dept)
LOOP
money := scott.get_result(my_deptno.deptno);
scott.p_add_data(my_deptno.deptno,my_deptno.dname,money);
END LOOP;
END;
这里用到了游标FOR循环(见本人前1,2篇文章所述,这里不再详谈),定义好了函数和过程后我们就可以当系统函数来使用了,使用方法与使用系统函数一样,传入我们需要的参数即可,本例是自动传参的.
通过上面的例子,我们可以看到函数和过程不是我们想象中的那么复杂,所以我们应该有足够的理由将ORACLE学好.
其他:
包的创建和使用,如下图:
注意:包相当于JAVA中的包+接口,我们可以看到在上部分定义包中,只定义了函数而没有具体的实现部分,在body部分做具体的实现,外部调用的时候用'包名.函数名'的方式调用
ORACLE中的命名规范:
存储过程：strore procedure,所以用 sp_
函数:function,所以用 func_或f_
触发器:trigger,所以用 tr_
包：package，所以用pkg_
参数：parameter,所以用 p_
变量：variable,所以用 v_
表:table,t_
说明：
每个公司都有一些自定义开发规范。
每日一问:
常见问题：
面对复杂业务，用其它语言(如JAVA)开发，还是用数据库脚本(存储过程、函数、触发器)开发
分析?
①如果开发的系统所使用数据库平台有更换的可能，则其它语言开发比较好，因为不同的数据库，脚本语言是完全不同的(方言)。
②通常电力，电信，移动等数据库平台相对固定，业务又相当复杂，所以数据库脚本编程已经在大量使用。
③说明：在流行的J2EE的架构中，不建议在数据库端处理业务，而是通过“OR映射” (如Hibernate)的方式处理。
