oracle存储过程定义function,Oracle存储过程和自定义函数

概述

存储过程和存储函数是指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。

异同点：

存储过程和存储函数的相同点：完成特定功能的程序。

存储过程和存储函数的区别：是否用return语句返回值。

存储过程的创建和调用

***个存储过程： 打印hello world

createorreplaceproceduresayhelloword

as

--说明部分,as一定要写

begin

dbms_output.put_line('Hello World');

end;

/

调用存储过程

1.execsayhelloworld()

2.2

​-- 调用两次

begin

sayhelloworld();

sayhelloworld();

end;

/

oracle 带参数的存储过程

--创建一个带参数的存储过程

--给指定的员工涨100块钱的工资，并且打印涨前后涨后的薪水

createorreplace procedureraisesalary(enoinnumber)--in这是一个输入参数

as

--定义一个变量保存涨前的薪水

psal emp.sal%type;

begin

--得到员工涨前的薪水

selectsalintopsalfromempwhereempno=eno;

--给该员工涨100块钱

updateemp setsal=sal+100whereempno=eno;

--一般，这里不需要commit！也不需要rollback

--注意：一般不在存储过程或者存储函数中，commit和rollback

--打印

dbms_output.put_line('涨前：'||psal||'，涨后：'||(psal+100));

end;

/

--调用：

begin

raisesalary(7839);

raisesalary(7566);

end;

/

如何调试存储过程

调试存储过程***放到Oracle数据库所在的系统或虚拟机上，解压SQL developer ，双击运行。

为了确保存储过程或函数是可调试的，右键“以编译并进行调试”，点击红色按钮“调试”

利用已写好的调用函数进行调试。

给调试账户授权

grantDEBUGCONNECTSESSION ,DEBUGANYPROCEDUREtoscott;

存储函数

函数的定义

是一个命名的存储程序，可带参数，并返回一个计算值。必须有return 子句，用于返回函数值。

创建存储函数语法

createorreplacefunction函数名(参数列表)

return函数值类型

as

begin

PLSQL子程序体;

end;

​

注意 表达式中某个字段为空时，表达式返回值为空。为防止含有表达式的返回值错误，在可能为空的字段上加上NVL(字段名，0)。

--查询某个员工的年收入

--查询某个员工的年收入

createorreplacefunctionqueryemp_income(enoinnumber)

returnnumber

as

--定义变量接收薪水和奖金

p_sal emp.sal%type;

p_comm emp.comm%type;

begin

selectsal,commintop_sal,p_commfromempwhereempno=eno;

--nvl为遇空函数，如果p_comm为空则返回0

returnnvl(p_comm,0)+p_sal*12;

end;

/

​

out 参数

存储过程和存储函数都可以有多个输入(in)和输出参数(out)，都可以通过out参数实现返回多个值。

-- out参数：查询某个员工姓名、月薪和职位

-- 原则： 如果只有一个返回值，用存储函数；否则，就用存储过程。

createorreplaceprocedurequeryempinfor(enoinnumber,penameoutvarchar2,

psal outnumber,pjoboutvarchar2)

as

begin

-- 得到该员工的姓名、月薪和职位

selectename,sal,empjobintopename,psal,pjobfromempwhereempno=eno;

end;

/

​

在 out 参数中访问光标

申明包结构

包头(申明)

包体(实现)

案例

-- 查询某个部门中所有员工的所有信息  //ref(reference引用) cursor(光标)

#包头

createorreplacepackage mypackageas

type empcursor isrefcursor;

procedurequeryEmpList(dnoinnumber,empListoutempcursor);

endmypackage;

#包体

createorreplacepackage body mypackageas

procedurequeryEmpList(dnoinnumber,empListoutempcursor)as

begin

openempListforselect*fromempwheredeptno=dno;

endqueryEmpList;

endmypackage;

***********包体需要实现包头中声明的所有方法*********************

​

在应用程序中访问

在java应用程序中访问存储过程和存储函数以及访问包下的存储过程，可以查看java API文档。

【编辑推荐】

【责任编辑：武晓燕 TEL：(010)68476606】

点赞 0