Oracle学习第五天

存储过程

1.存储过程和存储函数
指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
存储过程和存储函数的相同点：完成特点功能的程序
存储过程和存储函数的区别：是否用return语句返回值
2.创建和使用存储过程
用create procedure命令建立存储过程和存储函数。
语法：
create [or replace] procedure 过程名（参数列表）as plsql子程序体；
调用存储过程方式：
①exec salhelloworld();
②
begin 
sayhelloworld();
sayhelloworld();
end;
/


第一个存储过程：Hello World
create or replace procedure salhelloworld
as
--说明部分
begin
dbms_output.put_line("Hello World");
end;
/
3.带参数的存储过程
例：为指定的员工，涨100块钱的工资；并且打印涨前和涨后的薪水；
create or replace procedure raisesalary(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?
--注意：一般不在存储过程或者存储函数中，commit和rollback。
--打印
dbms_output.put_line("涨前："||psal||"涨后："||(psal+100));

end;
/
4.调试存储过程

存储函数

1.简介
函数（Function）为一命名的存储程序，可带参数，并返回铱计算值。
函数和过程结构类型，但必须有一个return子句，用于返回函数值。
2.创建存储函数的语法
create [or replace] function 函数名（参数列表）
return 函数值类型
as
plsql子程序体

例：查询某个员工的年收入
create or replace function queryempincome(eno in 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+nvl(pcomm,0);
end;
/
3.in和out参数
存储过程和存储函数都可以有out参数
存储过程和存储函数都可以有多个out参数
存储过程可以通过out参数来实现返回值

原则：如果只有一个返回值，用存储函数；否则就用存储过程。
例：查询某个员工姓名，月薪和职位
create or replace procedure queryempinfo(eno in number,pename out varchar2,psal out number,pjob out varchar2);
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/
4.在应用程序中访问存储过程和存储函数
①
private static String driver ="oracle.jdbc.OracleDriver";
private static String url="jdbc:oracle:thin:@192.168.56.101:1521:orcl";
private static String user="scott";
private static String password="tiger";


//注册数据库的驱动
static{
Class.forName(driver);//java的反射机制
//DriverManager.registerDriver(driver);
}
//获取数据库连接
public static Connection getConnection(){
return DriverManager.getConnection(url,user,password);
}

//释放数据库资源
public static void release(Connection conn,Statement st,ResultSet rs){
if(rs!=null){
rs.close();
}
finally{
rs=null;
}

if(st!=null){
st.close();
}
finally{
st=null;
}
if(conn!=null){
conn.close();
finally{
conn=null;
}
}
}


Class2
String sql="{call queryempinform(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
//得到一个连接
conn = JDBCUtils.getConnection();
//通过连接创建出statment
call = conn.prepareCall(sql);
//对于in参数，赋值
call.setInt(1,7839);
//对于out参数，申明
call.registerOutParameter(2,OracleTypes.VARCHAR);
call.registerOutParameter(2,OracleTypes.NUMBER);
call.registerOutParameter(2,OracleTypes.VARCHAR);


//执行调用
call.execute();
//取出结果
String name = call.getString(2);
String sal = call.getDouble(3);
String job = call.getString(4);


JDBCUtils.Close();






②
String sql="{?=call queryempincome(?)}";
Connection conn = null;
CallableStatement call = null;
//得到数据库连接
conn = JDBCUtils.getConnection();
//基于连接创建statement
call = conn.prepareCall(sql);

//对于输出参数 申明
call.registerOutParameter(1,OracleType.NUMBER);
//对于输入参数 赋值
call.setInt(2,7839);
//执行调用
call.execute();
//取出年收入的结果
double income = call.getDouble(1);


JDBCUtils.Close();

5.在out参数中使用光标
例：查询某个部门中所有员工的所有信息

包头：
create or replace
paceage 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;


6.在应用程序中访问包中的存储过程

String sql="{call mypackage.queryEmpList(?,？)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
//获取数据库连接
conn =. JDBCUtilsgetConnection();
//创建statement
call = conn.prepareCall(sql);


//对于in参数，赋值
call.setInt(1,10);
//对于out参数，申明
call.registerOutParameter(2,OracleType.CURSOR);
//执行调用
call.execute();
//取出该部门中所有员工的信息
rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
//取出该员工的员工号，姓名
int empno = rs.getInt("empno");
double salary = rs.getDouble("sal");
String job = rs.getString("empjob");
}
JDBCUtils.Close();