测试数据来源:http://blog.csdn.net/ochangwen/article/details/51297893
存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
二者 相同点:完成特定功能的程序二者 区别:存储函数可以用return语句返回值,而存储过程不能。
一、存储过程
1-1.创建和使用存储过程
用create procedure命令建立存储过程和存储函数语法:create [or replace] procedure 过程名(参数列表)
as plsql子程序体;
/*第一个存储过程:打印Hello World
调用存储过程:
1.exec sayhelloworld();
2.再写一个存储过程,调用过程
begin
sayhelloworld();
sayhelloworld();
end;
*/
create or replace procedure sayhelloworld
as
--说明部分
begin
dbms_output.put_line('Hello World');
end;
SQL> set serveroutput on;
SQL> exec sayhelloworld();
Hello World
SQL> begin
2 sayhelloworld();
3 sayhelloworld();
4 end;
5 /
Hello World
Hello World
1-2.带参数的存储过程
举例:为指定的员工,涨100块钱的工资;并且打印涨前和涨后的薪水。
drop table emp;
create table emp(empno number(5),ename varchar2(10), sal number(10,2), deptno number(4) );
insert into emp(empno,ename,sal,deptno)values(1001,'Tom1',1000,10);
insert into emp(empno,ename,sal,deptno)values(1002,'Tom2',2000,20);
/*
如何调用:
begin
raiseSalry(1001);
raiseSalry(1002);
commit; //这时提交可以保证上面的操作是在同一个事务里。
end;
*/
--参数需要指定是输入(in)还是输出(out)
create or replace procedure raiseSalry(eno in number)
as
--定义一个变量保存涨前的薪水
psal emp.sal%type;
begin
--得到员工涨前的薪水
select sal into psal from emp where empno=eno;
--给该员工涨100
update emp set sal = sal +100 where empno=eno;
--需不需要commit? 一般不在存储过程/函数中提交或回滚
DBMS_OUTPUT.PUT_LINE('涨工资前的薪水'||psal||'涨工资后的薪水'||(psal+100));
end;
SQL> begin
2 raiseSalry(1001);
3 raiseSalry(1002);
4 commit;
5 end;
6 /
涨工资前的薪水1000涨工资后的薪水1100
涨工资前的薪水2000涨工资后的薪水2100
至于如何调试,想看百度。
二、存储函数
函数(Function)为一命名的存储程序,可带参数,并返回一计算值函数和过程的结构类似,但必须有一个return子句,用于返回函数值
创建存储函数的语法:
create [or replace] function 函数名(参数列表)
return 函数值类型
as plsql子程序体;
--存储函数:查询某个员工的年收入
create or replace function queryemp_income(eno in number)
return number
as
--定义变量接收薪水和奖金
p_sal emp.sal%type;
p_comm emp.comm%type;
begin
select sal,comm into p_sal,p_comm from emp where empno=eno;
--nvl为遇空函数,如果p_comm为空则返回0
return nvl(p_comm,0)+p_sal*12;
end;
三、其它知识点
3-1.in和out参数
一般来讲,存储过程和存储函数的区别在于存储函数可以有一个返回值;而存储过程没有返回值。
存储过程和存储函数都可以有out参数和多个out参数
存储过程可以通过out参数来实现返回值
什么时候用存储过程/函数?
原则:如果只有一个返回值,用存储函数;否则用存储过程。
--out参数:查询某个员工姓名,月薪和职位
create or replace procedure queryEmpInform
(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;
思考:
1.查询某个员工的所有信息 --> out参数太多?
2.查询某个部门所有员工的所有信息 --> out中返回集合?
3-2.在应用程序中访问存储过程
下面一个实例:在java应用程序中访问存储过程/函数1).首先先建一个java工程,将D:\oracle\product\10.2.0\db_3\jdbc\lib\ojdbc14.jar包build pat
工具类:
package demo.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String user = "scott";
private static String password = "tiger";
// 注册数据库的驱动
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获取数据库连接
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
// 释放数据库资源
public static void release(Connection conn,Statement st,ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//回收
rs = null;
}
}
if(st !=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
st = null;
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
conn = null;
}
}
}
}
在应用程序 中访问存储过程
package demo.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.Test;
import demo.utils.JDBCUtils;
import oracle.jdbc.OracleTypes;
public class TestProcedure {
/**
* --out参数:查询某个员工姓名,月薪和职位
* create or replace procedure queryEmpInform
* (eno in number,pename out varchar2, psal out number, pjob out varchar2)
*/
@Test
public void testProcedure() {
String sql = "{call queryempinform(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try {
//得到一个链接
conn = JDBCUtils.getConnection();
//通过链接创建出statment
call = conn.prepareCall(sql);
//对于in参数,赋值
call.setInt(1, 1002);
//对于out参数,申明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
//执行调用
call.execute();
//取出结果
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name+"\t"+sal+"\t"+job);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
}
在应用程序 中访问存储函数
package demo.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import org.junit.Test;
import demo.utils.JDBCUtils;
import oracle.jdbc.OracleTypes;
public class TestFunction {
/**
* --存储函数:查询某个员工的年收入 create or replace function queryemp_income(eno in number) return number
*/
@Test
public void testFunction() throws Exception {
String sql = "{?=call queryemp_income(?)}";
Connection conn = null;
CallableStatement call=null;
try{
//得到数据库连接
conn = JDBCUtils.getConnection();
// 基于连接创建statement
call = conn.prepareCall(sql);
//输出参数,申明
call.registerOutParameter(1, OracleTypes.NUMBER);
//输入参数,赋值
call.setInt(2, 1001);
call.execute();
double income = call.getDouble(1);
System.out.println("编号为1001的员工的年收入是" + income);
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
}
3-3.在out参数中使用光标
案例:查询某个部门中所有员工的所有信息先创建包头和包体
--包头:
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;
在应用中访问包中的存储过程
注意,需要带上包名
package demo.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import org.junit.Test;
import demo.utils.JDBCUtils;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;
public class TestCursor {
/**
* --包头:
* create or replace package mypackage as
* type empcursor is ref cursor;
* procedure queryEmpList(dno in number, emplist out empcursor);
* end mypackage;
*/
@Test
public void testCursor() {
String sql = "{call mypackage.queryEmpList(?,?)}";
Connection conn = null;
CallableStatement call=null;
ResultSet rs = null;
try{
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
//对于in参数,赋值
call.setInt(1, 3);
//对于out参数,申明
call.registerOutParameter(2, OracleTypes.CURSOR);
call.execute();
//取出该部门中所有员工的信息
rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()) {
//只取出员工的员工号,姓名和职位
int empno = rs.getInt("empno");
String name = rs.getString("ename");
String job = rs.getString("job");
System.out.println(empno+":" + name+":"+job);
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
}
3:刘备备:MANAGER 6:张飞飞:CLERK 7:SCOTT:CLERK |