oracle经典存储过程,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表

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，