1.什么是存储过程和存储函数
相同点:存储过程和存储函数都是完成特定功能的程序
区别: 存储函数可用return语句返回值,存储过程不可以
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查看程序包结构
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的版本问题,需要向下兼容。