oracle: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

-在应用程序中访问包
暂时未考虑