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

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

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

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

2.最简单的存储过程

--SET SERVEROUTPUT ON;CREATE OR REPLACE PROCEDURE HELLOWORLDAS-- 说明部分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)asold_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 函数值类型asplsql程序体-- 根据员工编号查询员工的年薪create or replace function check_year_salary(pno in tb_user.no%type)return tb_user.no%typeasvsalary 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)asbegin  select name,salary,job into pname,psalary,pjob from tb_user where no = pno;end;/declarepname 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 REPLACEPACKAGE 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 REPLACEPACKAGE 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查看程序包结构

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的版本问题，需要向下兼容。