PL/SQL程序设计之存储过程和存储函数(四)

时间：2021-01-16 05:10:30

一.什么是存储过程和存储函数
指存储在数据库*所有用户程序调用的子程序叫存储过程、存储函数。

二.存储过程
1.语法:

create [or replace] PROCEDURE 过程名(参数列表) 
AS (is)
PLSQL子程序体；
调用的方式:
方式一:exec  函数名()         - - execute(exec)    
方式二:begin    
     函数名（）
end

2.注意事项

  • 参数可以为0个,1个,或多个
  • 在存储过程或存储函数中,尽量不要使用commit提交.

3.存储过程的例子:

没有参数的存储过程
CREATE OR REPLACE PROCEDURE SAYHELLO 
AS 
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END SAYHELLO;

为指定的职工在原工资的基础上长10%的工资，并打印涨工资前和涨工资后的工资

有一个参数的存储过程
--in代表的empid是输入参数
create or replace procedure raiseSal(empid in number) as
psal emp.sal%type;
begin
  --查询该员工的工资
select sal into psal from emp where empno = empid;
  --给该员工涨工资
  update emp set sal = sal * 1.1 where empno = empid;

  dbms_output.put_line('涨之前的工资:' || psal);
  dbms_output.put_line('涨之后的工资:' || psal*1.1);
end raiseSal;

为某个员工增加指定的工资额

多个参数的存储过程
create or replace procedure raise2Sal(empid in number,empsal in number) 
as
psal emp.sal%type;
begin
select sal into psal from emp where empno = empid;
  update emp set sal = sal + empsal where empno = empid;

  dbms_output.put_line('涨之前' || psal);
  dbms_output.put_line('涨之后' || psal+empsal);
end raise2Sal;

三.存储函数

1.函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。
2.注意事项:
3.语法结构

CREATE [OR REPLACE] FUNCTION 函数名(参数列表) 
RETURN 函数值类型
AS
PLSQL子程序体；
--查询某个员工的年收入
create or replace function sumSal(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;

  return psal*12+nvl(pcomm,0);
end sumSal;

调用上边那个存储函数

第一种方式:
begin
 dbms_output.put_line(sumSal(7934));
end;
 第二种方式:
 :result是一个绑定变量
begin
 :result := sumsal(eno => 7839);
end;

四.输入参数(in)和输出参数(out)
注意事项
1.存储过程和存储函数都可以通过out指定一个或多个输出参数。
2.我们可以利用out参数，在过程和函数中实现返回多个值
3.如果只有一个返回值，用存储函数；否则，就用存储过程。

查询某个员工的姓名 月薪 职位

思考：
1. 查询某个员工的所有信息 ---> out参数太多
2. 查询某个部门中的所有员工信息  ---> 返回集合
*/
create or replace procedure queryEmpInformation(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;                                               
end;

使用上边那个存储过程
PL/SQL程序设计之存储过程和存储函数(四)

五.包头和包体
1.引入:
当查询某个部门中的所有员工信息 的时候 返回一个集合,也就是out参数输出的是一个集合
怎么输出一个集合呢,就需要在out参数中使用游标,这里就需要使用包头和包体.
2:什么是包头和包体
包头只负责申明,包头只负责实现
3.具体实现:
创建包头:

create or replace package mypackage 
as
type empcursor is ref cursor;
procedure queryEmplist(dno in number,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;

数据库对象:
标签：

相关文章