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

向右看齐 2022-10-01 04:45 5阅读 0赞

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

--------------------

二.存储过程  
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;

使用上边那个存储过程  
![这里写图片描述][SouthEast]

--------------------

五.包头和包体  
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;

数据库对象:

[SouthEast]: /images/20220705/77a67add888f4709bddbc0d338d9f0e5.png