根据慕课网课程Oracle存储过程和自定义函数整理

1.概念

（1）定义：存储在数据库中供所有用户程序调用的子程序
（2）相同点：完成特定功能
（3）区别：存储函数可以return一个值
（4）使用场景：

• 当没有返回值时用存储过程
• 需要一个返回值时用存储函数
• 需要多个返回值时用存储过程（多个out参数）

2（1）.存储过程（示例1：打印helloworld）（无参）

create or replace procedure sayHelloworld
as
declare
begin
    dbms_oupput.put_line("Hello World");
end
/

2（2）.存储过程（示例2：给一个员工号，给他涨薪水）（带参数）

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.sal set sal = sal + 100 where empno = eno;
    dbms_oupput_put_line(psal||(pasl + 100));
--不用commit 由调用这个过程的程序来commit
end;

/

3.调用存储过程方式

（1） exec sayHelloworld() (命令行)
（2）其他处处过程过程或PL/SQL语句中调。

begin
  sayHelloworld();
    sayHelloworld();
end

4.调试存储过程

（1）右击存储过程 --> 调试 --> 红色调试按钮 --> 会生成调用程序
（2）用sys登陆给scott用户授权

grant debug connect session, debug any procedure to scott;

5.存储函数

• 必须有返回值

• 示例：给定一个员工号，返回他的年薪（月薪乘12加奖金）

  create or replace function querysal(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 + pcomm;
  end;
  

6.需要返回多个值时用存储过程

create or replace procedure quarya(eno in number,
                              pname out varchar2,
                              psal out number,
                              pjob out varchar2)
as
begin
select ename, sal, empjob into pname, psal, pjob from emp where     empno = eno;
end;

7.java调用存储过程核心代码

• //这个存储过程第一个参数用于接受员工号，后三个用于存返回的结果

• //quarya(eno in number, pname out varchar2, psal out number, pjob out varchar2)

  String sql = "{call quarya(?,?,?,?)}";
  Connection connection = null;
  CallableStatement statement = null;
  connection = JDBCUtils.getConn();
  statement = connection.prepareCall(sql);
  
  statement.setInt(1, 7839); 
  statement.registerOutParameter(2, OracleTypes.VARCHAR);
  statement.registerOutParameter(3, OracleTypes.NUMBER);
  statement.registerOutParameter(4, OracleTypes.VARCHAR);
  
  statement.execute();
  
  String name = statement.getString(2);
  String job = statement.getString(4);
  double sal = statement.getDouble(3);
  
  System.out.println(name);
  System.out.println(job);
  System.out.println(sal);
  

8.java调用存储函数核心代码

• //传入员工号

• //querysal(eno in number)

• //return number

  String sql = "{?= call querysal(?)";
  Connection connection = null;
  CallableStatement statement = null;
  connection = JDBCUtils.getConn();
  statement = connection.prepareCall(sql);
  
  statement.registerOutParameter(1, OracleTypes.NUMBER);
  statement.setInt(2, 7839);
  
  statement.execute();
  
  double salary = statement.getDouble(1);
  System.out.print(salary);
  

9.包（包住了存储过程）

（1）包头

 create or replace package mypackage as
    type empcursor is ref cursor;(定义一种类型)
    procedure queryEmpList(dno in number, empList out empcursor)
end mypackage;

（2）包体

  create or replace package body mypackage as
    procedure queryEmpList(dno in number, empList out empcursor)
    begin
        open empList for select * from emp where deptno = dno;
    end queryEmpList;
  end mypackage;

10.java调用包中的存储过程核心代码

 Connection connection = null;
CallableStatement statement = null;
ResultSet resultSet = null;

String sql = "{call mypackage.queryEmpList(?, ?)";

connection = JDBCUtils.getConn();
statement = connection.prepareCall(sql);

statement.setInt(1, 20);
statement.registerOutParameter(2, OracleTypes.CURSOR);

statement.execute();

resultSet = ((OracleCallableStatement)statement).getCursor(2);
while(resultSet.next())
{
    int empno = resultSet.getInt("empno");
    String empname = resultSet.getString("ename");
    System.out.println(empno + "\t" + empname);
}