oracle之存储过程--------经典案例

brbwxdnwal

　　一，存储过程(stored procedure,简称sp,也称为Procedure，过程)存储过程是在大型数据库系统中，一组完成特定功能的sql语句集，经过编译后存储在数据库中，用户通过存储过程的名字及参数来执行它
　　二，函数(function)
　　1，to_char/nvl,coalesc等都是oracle数据库的内置函数
　　自己写的函数和Procedure功能类似，类似于java中的方法
　　过程和函数都是驻留在数据库中的程序块
　　过程可以有返回值，也可以没有，函数必须有返回值
　　三，数据库编程
　　在数据库中编译好程序块，实现特定的功能
　　四，优势，提高效率
　　最大限度的减少io
　　已经编译过的过程，减少编译时间
　　五，每条sql语句对应一个执行计划Explain Plan，缓存中保持一定数量的sql语句的解析结果和它的执行计划
　　案例如下
　　要求
　　1，输入参数：用户id/用户pwd

2,输出参数：

正确： 返回 1（flag=1）

密码错：返回 0 flag=0

没有这个用户： 返回-1 flag=-1
　　步骤如下：
　　一，创建user_xxx表

create table user_xxx(
id       number(4),
password char(4),
name     char(20),
phone    char(20),
email   varchar2(50));

　　二，向该表插入数据

insert into user_xxx values(1001,'1234','liucs','136000000','liucs@sina.com');
　　三，创建存储过程ProcLogin_jiang

create or replace procedure ProcLogin_jiang(
p_id in number,p_pwd in char,flag out number)--in表示输入，入口，默认值，out表示输出，出口
is --定义变量
v_password char(4);--数据表里面真正的密码
begin                                --begin和end之间为程序体
select password into v_password     --过程中的select语句的格式
from user_xxx
where id=p_id;                      --当且仅当查询出来一条记录时，不会出异常
if p_pwd=v_password then
flag:=1;
else
flag:=0;
end if;
exception                              --当出现异常时，程序会跳到这里执行
when others then
flag:=-1;      
end;
/
　　四，在sqlplus中用匿名块测试过程
　　下面这条语句是打开sqlplus工具的输出模式，默认是关闭的

set serveroutput on
　　匿名块测试过程，找到结果返回1

declare
v_flag number;
begin
ProcLogin_jiang(1001,'1234',v_flag);
dbms_output.put_line(v_flag);
end;
/
　　五，存储过程准备就绪，下面测试使用JDBC调用过程ProcLogin_jiang
　　首先要用到我之前写的一工具类。
　　测试代码如下：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import day2.ConnectionUtils;
public class TestProcDemo {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
int flag=
ProcLogin_jiang(1001,"1234");
if(flag==1)
System.out.println("登录成功");
else if(flag==0)
System.out.println("密码错误");
else if(flag==-1)
System.out.println("账户不存在");
else
System.out.println("其他错误");
}
/**
* 调用ProcLogin_ning，输入两个参数，返回结果
* id 考生id
* pwd 考生密码
* 成功：1；id正确，密码错误：0，没有id:-1*/
private static int ProcLogin_jiang(int id, String pwd) {
// TODO Auto-generated method stub
int flag=-2;
//前两个？代表in类型，通过参数列表传入
//第三个？代表out类型，是输出参数
String sql="{call ProcLogin_jiang(?,?,?)}";//调用该过程的语法
Connection conn=ConnectionUtils.getConnection();
CallableStatement stmt=null;
try{
stmt=conn.prepareCall(sql);
stmt.setInt(1, id);
stmt.setString(2,pwd);
stmt.registerOutParameter(3, Types.INTEGER);
stmt.execute();
flag=stmt.getInt(3);
}catch(Exception e){
e.printStackTrace();
}finally{
ConnectionUtils.close(conn);
ConnectionUtils.close(stmt);
}
return flag;
}
}
　　

　　输出结果为1，