★过程(存储过程)
存储过程其实就是一种命名的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();
}
}
}