数据库编程入门(二)-存储过程和存储函数

2017-03-10

1.什么是存储过程和存储函数

相同点：存储过程和存储函数都是完成特定功能的程序
区别：存储函数可用return语句返回值，存储过程不可以

1
2
3

CREATE OR REPLACE PROCUDURE [PNAME(PARAMS)]
AS -- AS相当于PLSQL的DECLARE
PLSQL程序体

2.最简单的存储过程

--SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE HELLOWORLD
AS
-- 说明部分
BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
END;
/
/*
调用存储过程：
1.exec HELLOWORLD();
2.begin
  HELLOWORLS();
  end;
*/
exec HELLOWORLD;

3.带参数的存储过程

-- 创建一个带参数的存储过程
-- 给指定员工涨工资
create or replace procedure raise_salary(uno in tb_user.no%type,usalary in tb_user.salary%type)
as
old_salary tb_user.salary%type;
begin
  select salary into old_salary from tb_user where no = uno;
  dbms_output.put_line('Before rasing salary:'||old_salary||'   After rasing salary:'||  (500.0 + old_salary));
  update tb_user set  salary = old_salary + 500 where no = uno;
end;
/
exec raise_salary(1,500);

4.debug存储过程

这里写图片描述

5.存储函数

CREATE OR REP;ACE FUNCTIOPN 函数名(参数列表)
return 函数值类型
as
plsql程序体
-- 根据员工编号查询员工的年薪
create or replace function check_year_salary(pno in tb_user.no%type)
return tb_user.no%type
as
vsalary tb_user.salary%type;
begin
  select salary into vsalary from tb_user where no = pno;
  return vsalary * 12;
end;
/
begin
  dbms_output.put_line(CHECK_YEAR_SALARY(1));
end;

6.in和out参数

我们知道存储过程和存储函数的区别主要在于函数可以return一个结果，那么如何使存储过程和存储函数返回多个值呢？

存储过程和存储函数都可以有out参数
存储过程和存储函数都可以有多个out参数
存储过程可以通过out参数来实现返回值

原则：如果只有一个返回值，就用存储函数；否则，就用存储过程。

-- out参数：查询某个员工的姓名月薪和职位
create or replace procedure get_user_info(
  pno in number,
  pname out varchar2,
  psalary out number,
  pjob out varchar2)
as
begin
  select name,salary,job into pname,psalary,pjob from tb_user where no = pno;
end;
/
declare
pname varchar2(200);
psalary number;
pjob varchar2(200);
begin
  get_user_info(1,pname,psalary,pjob);
  dbms_output.put_line(pname||' '||psalary||' '||pjob);
end;

7.Java调用存储过程

1) 从Oracle客户端找到ojdbc14.jar
2) 创建java项目，将jdbc驱动加载到buildpath
3) 编写JDBC连接代码和调用存储过程代码

示例调用上面查询用户信息的存储过程

import oracle.jdbc.OracleTypes;
import org.junit.Test;
import java.sql.*;
/**
 * Created by NikoBelic on 2016/11/23.
 */
public class TestProcedure
{
    private static String driver = "oracle.jdbc.driver.OracleDriver";
    private static String url = "jdbc:oracle:thin:@192.168.1.176:1521:orcl";
    private static String user = "learn";
    private static String password = "learn";
    /**
     * 注册数据库驱动
     * @Author NikoBelic 2016/11/23 15:56
     */
    static
    {
        try
        {
            Class.forName(driver); // 反射注册oracle驱动
        } catch (ClassNotFoundException e)
        {
            throw new ExceptionInInitializerError(e);
        }
    }
    /**
     * 获取数据库连接
     *
     * @Author NikoBelic 2016/11/23 15:56
     */
    public static Connection getConnection()
    {
        try
        {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 关闭连接
     * @Author NikoBelic 2016/11/23
     */
    public static void release(Connection conn, Statement stat, ResultSet rs)
    {
        try
        {
            if (rs != null)
                rs.close();
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            rs = null;
        }
        try
        {
            if (stat != null)
                stat.close();
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            stat = null;
        }
        try
        {
            if (conn != null)
                conn.close();
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            conn = null;
        }
    }
    @Test
    public void test()
    {
        Connection conn = null;
        CallableStatement stat = null;
        String sql = "{call GET_USER_INFO(?,?,?,?)}";
        try
        {
            // 获取连接
            conn = getConnection();
            // 创建Statement
            stat = conn.prepareCall(sql);
            // 对in参数赋值
            stat.setInt(1,1);
            // 对out参数声明
            stat.registerOutParameter(2, OracleTypes.VARCHAR);
            stat.registerOutParameter(3, OracleTypes.NUMBER);
            stat.registerOutParameter(4, OracleTypes.VARCHAR);
            // 执行调用
            stat.execute();
            // 取出结果
            String name = stat.getString(2);
            Double salary = stat.getDouble(3);
            String job = stat.getString(4);
            System.out.println(name + "  " + salary + "  " + job);
        }catch (Exception e)
        {
            e.printStackTrace();
        }finally
        {
            release(conn,stat,null);
        }
    }
}

结果
这里写图片描述

8.Java调用存储函数

数据库连接方法同上，新增一个Test方法如下

/**
     * 调用存储函数的测试：查询员工的年收入
     * @Author NikoBelic 2016/11/23 16:33
     */
    @Test
    public void testFunction()
    {
        Connection conn = getConnection();
        CallableStatement stat = null;
        String sql = "{?=call CHECK_YEAR_SALARY(?)}";
        try
        {
            stat = conn.prepareCall(sql);
            stat.registerOutParameter(1,OracleTypes.NUMBER);
            stat.setInt(2,1);
            stat.execute();
            Double yearSalary = stat.getDouble(1);
            System.out.println("该员工的年薪是 " + yearSalary);
        }catch (Exception e)
        {
            e.printStackTrace();
        }finally
        {
            release(conn,stat,null);
        }
    }

结果
这里写图片描述

9.如何在out参数中使用光标(cursor)

场景：加入我需要获取所有员工的信息，总不能传几十个参数到存储过程中的out参数吧？
在out参数中使用光标需要以下三个步骤
1) 声明一个包结构
2) 创建包头
3) 创建包体

9.1新建程序包，自动生成包头

这里写图片描述

CREATE OR REPLACE
PACKAGE MYPACKAGE AS
  /* TODO enter package declarations (types, exceptions, methods etc) here */
  type cur_user is ref cursor; -- 自定义一个类型(光标)
  procedure queryUserList(dno in number,userList out cur_user); -- 声明一个存储过程
END MYPACKAGE;

9.2创建包体

这里写图片描述

CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS
  procedure queryUserList(dno in number,userList out cur_user) AS
  BEGIN
    -- TODO: procedure MYPACKAGE.queryUserList所需的实施
    open userList for select * from tb_user where DEPTNO = dno;
  END queryUserList;
END MYPACKAGE;

使用desc查看程序包结构

1	desc MYPACKAGE

这里写图片描述

9.3调用包中的存储过程(sqldeveloper工具不支持直接调用)

/**
     * 调用包中的存储过程，返回一个结果集(光标)
     * 查询指定部门下的所有员工信息
     * @Author NikoBelic 2016/11/23 17:08
     */
    @Test
    public void testCursor()
    {
        Connection conn = null;
        CallableStatement stat = null;
        ResultSet rs = null;
        String sql = "{call MYPACKAGE.queryUserList(?,?)}"; // 第一个参数是in类型部门编号 第二个参数是out类型光标结果集;
        try
        {
            conn = getConnection();
            stat = conn.prepareCall(sql);
            stat.setInt(1,10);
            // 将out参数这是为Cursor类型
            stat.registerOutParameter(2,OracleTypes.CURSOR);
            stat.execute();
            // 这里必须强制类型转换，CallableStatement接口没有getCursor这个方法
            rs = ((OracleCallableStatement) stat).getCursor(2);
            while (rs.next())
            {
                // 获取结果有两种方式 一种是用角标过去rs.getIndex(int),另一种就是通过字段名称获取
                String name = rs.getString("name");
                Double salary = rs.getDouble("salary");
                Date hireDate = rs.getDate("hireDate");
                String job = rs.getString("job");
                System.out.println(name + "  " + salary + "  " + hireDate + "  " + job);
            }
        }catch (Exception e)
        {
        }finally
        {
            release(conn,stat,rs);
        }
    }

结果
这里写图片描述

10.总结

存储过程和存储函数主要区别在于return。
但是有了out参数，存储过程完全可以替代存储函数。

那为什么还有存储函数这个垃圾？
因为Oracle的版本问题，需要向下兼容。