16.存储过程

★过程(存储过程)

存储过程其实就是一种命名的PL/SQL块，它可以接收0到多个参数，或者输出0到多个参数。存储过程被保存在数据中，可以在应用程序中(Java中...)或者在sql plus中被调用.

 

●语法

create procedure 过程名[参数1，参数2，....] is

[变量]

begin

过程代码;

[exception

异常代码]

end [过程名];

 

▼案例一:

create procedure pro_1 is

begin

   dbms_output.put_line('Hello World!');

end pro_1;--end 后面可以不用带过程名，一般都不带

 

在sql plus中调用过程

SQL>exec[ute] pro_1; --方法一(在sql plus中执行)

SQL>begin       --方法二(在pl/sql快中执行)

pro_1;

    end;

 

 

▼案例二:

create procedure pro_2(v_no in emp.empno%type,v_name out emp.ename%type) is

begin

   select ename into v_name from emp where empno=v_no;

    dbms_output.put_line(v_name);

exception

    when no_data_found then

        dbms_output.put_line('没有该员工');

end;

如果创建过程中有错误，查看过程中错误使用SQL>show errors；

 

说明:过程中如果是输入参数用in，输出参数用out，如果没有指名in或者out默认为in。如果带有参数，调用该过程时一定要传入相应类型的数据。如果过程中有out参数返回值，那么在调用该过程时需要提供能够接受该返回值的变量。在sql plus中执行过程时需要使用variable命令绑定参数值。在pl/sql块中执行过程时需要定义变量接受相应值。

 

--方法一（在sql plus中执行）

SQL>variable name varchar2(20）;

SQL>exec pro_2('7788',:name);

SQL>print name; //可以使用print打印出name中的值

name

---------

SMITH

 

SQL>select :name from dual;   //也可以只用该命令查询出name中的值

name

---------

SMITH

 

 

--方法二(在pl/sql块中调用)

declare

  name varchar2(20);

begin

      pro_2('7788',name);

  dbms_output.put_line(name);

end;

●Java调用存储过程

▼ 案例一:没有out参数的存储过程

编写存储过程 pro_4，完成输入员工编号，新工资，将该编号的员工工资改为新的工资

create procedure pro_4(v_no number,v_sal number) is

begin

update emp set sal=v_sal where empno=v_no;

end;

 

/**

  * 调用没有out参数的存储过程

  */

package test3_11;

import java.sql.*;

public class Test1 {

public Test1(){

try {

//1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2.获得连接

Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");

//3.创建CallableStatement对象，调用存储过程

CallableStatement cs = conn.prepareCall("{call pro_4(?,?)}");

//4.给？号赋值

cs.setInt(1,7788);

cs.setInt(2,3000);

//5.执行

cs.execute();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void main(String[] args){

new Test1();

}

}

 

▼ 案例二:有out参数的存储过程

编写存储过程 pro_3 ,完成 输入员工编号，查询出员工姓名，工资，部门编号

create procedure pro_3(v_no number,v_name out varchar2,v_sal out number,v_deptno out number) is

begin

select ename,sal,deptno into v_name,v_sal,v_deptno from emp where empno=v_no;

end;

/**

 * 调用有out参数的存储过程

  */

package test3_11;

import java.sql.*;

public class Test2 {

public Test2(){

try {

//1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2.获得连接

Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");

//3.创建CallableStatement对象，调用存储过程

CallableStatement cs = conn.prepareCall("{call pro_3(?,?,?,?)}");

//4.给？号复制

cs.setInt(1,7788);

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

cs.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);

cs.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);

//5.执行

cs.execute();

//6.取出输出参数

String name = cs.getString(2);

double sal = cs.getDouble(3);

int deptno = cs.getInt(4);

System.out.println("姓名："+name+",工资:"+sal+",部门编号:"+deptno);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void main(String[] args){

new Test2();

}

}

 

 

▼ 案例三:qq登陆存储过程

第一步:创建表qq

create table qq(

username varchar2(20),

password varchar2(20)

)

 

insert into qq values('123456','123456abc');

insert into qq values('111111','888abc');

insert into qq values('123455','admin');

第二步:编写存储过程

输入参数  v_username varchar2,

  v_password varchar2,

输出参数   flag number   //0代表登陆失败，1代表登陆成功

 

create or replace procedure login_pro(v_username varchar2,v_password varchar2,flag out number) is

i number(2):=0;--i用来保留查询的记录条数，0代表没有

begin

select count(*) into i from qq where username=v_username and password=v_password;

if i>0 then

flag:=1;--1代表查找到该用户，登陆成功

else

flag:=0;--0代表没有找到该用户，登陆失败

end if;

end;

第三步:Java中调用存储过程

public void actionPerformed(ActionEvent e) {

if(e.getSource()==jb1){//登陆按钮

String qq = jtf.getText();

String mm = jpf.getText();

try {

//1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2.获得连接

Connection conn=DriverManager.getConnection ("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");

//3.调用存储过程

CallableStatement cs = conn.prepareCall("{call pro_login(?,?,?)}");

//4.给？号赋值

cs.setString(1, qq);

cs.setString(2, mm);

cs.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);

//5 执行存储过程

cs.execute();

//6.取出out值

int flag = cs.getInt(3);

if(flag>0){

System.out.println("成功");

}else{

System.out.println("失败");

}

} catch (Exception e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

}

}