oracle:存储过程、函数
oracle:oracle基础知识(三)：存储过程、函数笔记，赵强老师
对于视频中涉及java部分的章节省略；
存储过程、存储函数
指存储在数据库中供所有用户过程调用的子程序叫存储过程、存储函数。
共同点：完成特定功能的程序
区别：是否用return 语句返回值
存储过程基本语法
语法：
create procedure命令建立存储过程和存储函数
create [or replace ] procedure 过程名(参数列表)
as
plSQL子程序体；
- 调用存储过程
/*
调用存储过程
1.execute(exec) sayhelloworld()
2.begin
sayhelloworld();
end;
/
*/
案例：
create or replace procedure sayhelloworld
as
--说明部分(as 相当于declare)
begin
dbms_output.put_line('hello world');
end;
/
declare
--定义变量
pnum number;
begin
--迭代标志
pnum:=1;
loop
exit when pnum>10;
--调用存储过程
sayhelloworld();
pnum:=pnum+1;
end loop;
end;
/
- 案例：带参数的存储过程
为指定的员工，涨100，并且打印涨前工资和涨后工资
create or replace procedure raiseSalary(eno in number)
as
--定义变量
psal emp.sal%type;
begin
--得到涨前的薪水
select sal into psal from emp where empno=eno;
--涨工资
update emp set sal=sal+100 where empno=eno;
--需不需要commit?
--注意：一般不在存储过程或者存储函数中，commit 和rollback
--打印
dbms_output.put_line('涨前'||psal||'涨后'||(psal+100));
end;
--这里/表示结束符
/
- 存储过程的调用
/*
--调用：
begin
raisesalary(7369);
raisesalary(7499);
commit;
end;
/
*/
存储函数
--可带参数，并返回计算值(return子句)
语法：
create [or replace ] funcetion(参数列表)
return 函数值类型
as
plsql子程序体；
- 调用
/* 函数调用 begin 函数名() end; select 函数名() from dual; */
案例：
--案例：查询某一个员工的年收入
create or replace function queryEmpIncome(eno in number)
return number
as
--定义变量保存员工的薪水和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
--得到该员工的月薪和奖金
select sal,comm into psal,pcomm from emp where empno=eno;
--直接返回年收入
dbms_output.put_line('员工编号为'||eno||'的奖金'||nvl(pcomm,0)||'年薪'||(psal*12+nvl(pcomm,0)));
return psal*12+nvl(pcomm,0);
end;
/
- 调用函数
select queryEmpIncome(7369) from dual;
带in 和out 参数
-
-- 存储过程没有返回值 --过程和函数都可以用out指定一个或者多个参数 两者都可以有out参数；可以有多个out参数； 可以通过out参数来实现返回值； 原则：如果只有一个返回值，用存储函数； 否则，用存储过程。 -
调用
-
案例
--案例查询某个员工的姓名、月薪、和职位；
create or replace procedure queryempinform(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
--得到该员工的姓名、月薪和职位
select ename,sal,job into pename,psal,pjob from emp where empno=eno;
dbms_output.put_line('员工姓名'||pename||'月薪'||psal||'职位'||pjob);
end;
/
- 调用存储过程
declare
pename varchar2(10);
psal number;
pjob varchar2(10);
begin
queryempinform(7369,pename,psal,pjob);
end;
/
- 在out参数中使用光标；
主要用于解决以下问题：
/*
思考：
1.查询员工的所有信息--->out参数太多？
2.查询某个部门中所有员工的所有信息-->out中返回集合？
*/
-
申明包结构-->包头-->包体 -
案例：查询某个部门所有员工的信息；
-- 包头：
create or replace package mypackage as
--ref cursor 引用光标类型
type empCursor is ref cursor;
procedure queryEmpList(dno in numbeer,empList out empCursor);
end mypackage;
-- 包体：
--需要实现包头中声明的所有方法
create or replace package body mypackage as
procedure queryEmpList(dno in number,empList out empCursor) as
begin
--打开光标，并指明它属于哪一个集合
open empList for select * from emp where deptno=dno;
end queryEmpList;
end mypackage;
- 查看包
-使用desc查看程序包的结构
desc mypackage
--清屏
host cls
-在应用程序中访问包
暂时未考虑
