oracle之存储过程__经典案例
一,存储过程(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表
[sql] view plaincopy
- create table user_xxx(
- id number(4),
- password char(4),
- name char(20),
- phone char(20),
- email varchar2(50));
二,向该表插入数据
[sql] view plaincopy
- insert into user_xxx values(1001,’1234′,’liucs’,’136000000′,’liucs@sina.com’);
三,创建存储过程ProcLogin_jiang
[sql] view plaincopy
- 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工具的输出模式,默认是关闭的
[sql] view plaincopy
- set serveroutput on
匿名块测试过程,找到结果返回1
[sql] view plaincopy
- declare
- v_flag number;
- begin
- ProcLogin_jiang(1001,’1234′,v_flag);
- dbms_output.put_line(v_flag);
- end;
- /
五,存储过程准备就绪,下面测试使用JDBC调用过程ProcLogin_jiang
首先要用到我之前写的一工具类。
测试代码如下:
[java] view plaincopy
- 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