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表

  1. create table user_xxx(
  2.    id       number(4),
  3.    password char(4),
  4.    name     char(20),
  5.    phone    char(20),
  6.    email   varchar2(50));

二,向该表插入数据

  1. insert into user_xxx values(1001,’1234′,’liucs’,’136000000′,’liucs@sina.com’);

三,创建存储过程ProcLogin_jiang

  1. create or replace procedure ProcLogin_jiang(
  2.      p_id in number,p_pwd in char,flag out number)–in表示输入,入口,默认值,out表示输出,出口
  3.         is –定义变量
  4.            v_password char(4);–数据表里面真正的密码
  5.         begin                                –begin和end之间为程序体
  6.            select password into v_password     –过程中的select语句的格式
  7.              from user_xxx
  8.              where id=p_id;                      –当且仅当查询出来一条记录时,不会出异常
  9.                if p_pwd=v_password then
  10.                    flag:=1;
  11.                else
  12.                    flag:=0;
  13.                end if;
  14.      exception                              –当出现异常时,程序会跳到这里执行
  15.          when others then
  16.                    flag:=-1;
  17.         end;
  18. /

四,在sqlplus中用匿名块测试过程

下面这条语句是打开sqlplus工具的输出模式,默认是关闭的

  1. set serveroutput on

匿名块测试过程,找到结果返回1

  1. declare
  2.             v_flag number;
  3.     begin
  4.       ProcLogin_jiang(1001,’1234′,v_flag);
  5.       dbms_output.put_line(v_flag);
  6.       end;
  7.       /

五,存储过程准备就绪,下面测试使用JDBC调用过程ProcLogin_jiang

首先要用到我之前写的一工具类

测试代码如下:

  1. import java.sql.CallableStatement;
  2. import java.sql.Connection;
  3. import java.sql.Types;
  4. import day2.ConnectionUtils;
  5. public class TestProcDemo {
  6.     /**
  7.      * @param args
  8.      */
  9.     public static void main(String[] args) {
  10.         // TODO Auto-generated method stub
  11.        int flag=
  12.                ProcLogin_jiang(1001,”1234″);
  13.        if(flag==1)
  14.            System.out.println(“登录成功”);
  15.        else if(flag==0)
  16.            System.out.println(“密码错误”);
  17.        else if(flag==-1)
  18.            System.out.println(“账户不存在”);
  19.        else
  20.            System.out.println(“其他错误”);
  21.     }
  22. /**
  23.  * 调用ProcLogin_ning,输入两个参数,返回结果
  24.  * id 考生id
  25.  * pwd 考生密码
  26.  * 成功:1;id正确,密码错误:0,没有id:-1*/
  27.     private static int ProcLogin_jiang(int id, String pwd) {
  28.         // TODO Auto-generated method stub
  29.         int flag=-2;
  30.         //前两个?代表in类型,通过参数列表传入
  31.         //第三个?代表out类型,是输出参数
  32.         String sql=”{call ProcLogin_jiang(?,?,?)}”;//调用该过程的语法
  33.         Connection conn=ConnectionUtils.getConnection();
  34.         CallableStatement stmt=null;
  35.         try{
  36.             stmt=conn.prepareCall(sql);
  37.             stmt.setInt(1, id);
  38.             stmt.setString(2,pwd);
  39.             stmt.registerOutParameter(3, Types.INTEGER);
  40.             stmt.execute();
  41.             flag=stmt.getInt(3);
  42.         }catch(Exception e){
  43.             e.printStackTrace();
  44.         }finally{
  45.             ConnectionUtils.close(conn);
  46.             ConnectionUtils.close(stmt);
  47.         }
  48.         return flag;
  49.     }
  50. }

输出结果为1

标签