什么是存储过程
存储过程是一组为了完成特定功能的SQL语句，经编译后存在数据库，存储过程是数据库中的一个重要对象。
ps:有人说：任何一个设计良好的数据库应用程序都应该用到存储过程，我觉得这个不一定，其实很多互联网公司的WEB应用程序都没有用到存储过程，难道就能说他们的应用程序设计的不好吗？现实中，银行、金融相关的公司使用存储过程多一些。
存储过程的优缺点
优点
效率高：创建的时候就会编译，调用时不需要重新编译，而直接写SQL每次执行都先编译再执行。
安全：与直接写SQL语句相比，存储过程不存在SQL注入的问题。可以向用户授予存储过程的访问权限，而不是直接授予表的访问权限，控制对特定数据的访问。
降低网络流量：存储过程直接存储在数据库中，所以不会产生大量的SQL代码流量。
缺点
可移植性很差：应用程序更换其他数据库，需要将原有的存储过程重新写一遍，并且如果你的存储过程中包含大量的业务逻辑，这样就会更加麻烦。
代码可复用性很差：面向对象的思维在这里完全没有用，两个很相近的功能，需要写两个存储过程，虽然可以相互调用，但是不能利用继承等面向对象的功能。
语法
create or replace procedure <存储过程名>
[(
参数列表：p_name1 in <输入参数数据类型>,p_name2 out <输出参数数据类型>
,p_name3 in out <参数数据类型>
)]
as或is
[定义变量：v_name1 <变量数据类型>]
begin
? <执行特定的操作或者任务，可以进行增删改查>
? end;
注意
如果没有参数（）要去掉，存储过程主体不能为空（begin和end之间不能什么都不做）
说明
in表示输入参数，out表示输出参数，可以同时使用in和out表示既是输入参数又是输出参数。
声明变量时可以使用 列名 %type 来声明类型
存储过程主体做select查询时，必须使用into将结果集保存到变量中
:=表示赋值操作
DEMO
登录Oracle数据库scott用户，初始密码tigger
1.定义一个存储过程，用来查询指定部门的员工的人数和平均工资，如果部门不存在，就引发一个自定义异常，显示“部门编号不存在！”(部门编号作为存储过程的输入参数，在调用存储过程前由用户输入，该部门员工的人数和平均工资需要返回)
创建
create or replace procedure proc1(
p_deptno in emp.deptno%type,
p_count_emp out number ,
p_avg_sal out emp.sal%type
)
is
v_row_num number:=0;
error1 exception;
begin
select count(*) into p_count_emp from emp e where e.deptno=p_deptno;
if p_count_emp!=0 then
select avg(e.sal) into p_avg_sal from emp e where e.deptno=p_deptno;
else raise error1;
end if;
exception when error1 then
dbms_output.put_line(‘部门编号不存在‘);
end;
语句块调用
declare
a emp.deptno%type;
b number:=0;
c emp.sal%type:=0;
begin
a:=&部门编号;
proc1(a,b,c);
dbms_output.put_line(‘部门编号为‘||a||
‘的员工人数为‘ ||b||‘，平均工资为：‘||
c);
end;
2.存储过程相互调用
创建
--根据员工编号删除员工
create or replace procedure proc3
(p_empno in emp.empno%type
)
as
begin
delete from emp e where e.empno=p_empno;
commit;
end;
--首先调用上面的那个存储过程，然后用输出参数返回剩下的员工人数
create or replace procedure proc4(
p_empno in emp.empno%type,
p_nums_afterdelete out number)
as
begin
proc3(p_empno);
select count(*) into p_nums_afterdelete from emp;
end;
语句块调用
declare
p_empno emp.empno%type:=&请输入员工编号;
p_nums_afterdelete number:=0;
begin
proc4(p_empno,p_nums_afterdelete);
dbms_output.put_line(‘已成功删除员工编号为‘||p_empno||‘的员工‘
||‘,当前还剩的员工总数为‘||p_nums_afterdelete);
end;
3.使用call关键字直接调用存储过程
create or replace procedure proc2(a in number,b in number)
as
c number;
begin
c :=a b;
dbms_output.put_line(c);
end;
call proc2(1,1);
JAVA调用存储过程
首先需要下载classes12.jar、ojdbc14.jar，并导入到自己的工程里。
插入数据
package java_procedure;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
//只需ojdbcjar包就行
/
--java调用存储过程
create table tb_student(
stu_id varchar2(20),
stu_name varchar2(20)
);
--①没有返回值的存储过程
create or replace procedure TESTA(
p_stu_id in tb_student.stu_id%type,
p_stu_name in tb_student.stu_name%type)
as
begin
insert into tb_student
(stu_id, stu_name)
values
(p_stu_id, p_stu_name);
end TESTA;
/
/
public class TestProcedureOne {
public static void main(String[] args) {
CallableStatement cs=null;
Connection conn=null;
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String userName="a3";
String pwd="a123";
try {
Class.forName(driver);
conn=DriverManager.getConnection(url,userName,pwd);
cs=conn.prepareCall("{call testa(?,?)}");
cs.setString(1,"001");
cs.setString(2, "zhangsan");
cs.execute();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if (cs!=null) {
cs.close();
}
if (conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}}
条件查询
package java_procedure;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
//只需ojdbcjar包就行
/
--②有返回值的存储过程
CREATE OR REPLACE PROCEDURE TESTB(
p_stu_id in tb_student.stu_id%type,
p_stu_name out tb_student.stu_name%type)
AS
BEGIN
SELECT stu_name INTO p_stu_name FROM tb_student
WHERE stu_id = p_stu_id;
END TESTB;
/
/
public class TestProcedureTwo {
public static void main(String[] args) {
CallableStatement cs=null;
Connection conn=null;
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String userName="a3";
String pwd="a123";
try {
Class.forName(driver);
conn=DriverManager.getConnection(url,userName,pwd);
cs=conn.prepareCall("{call testb(?,?)}");
cs.setString(1,"001");
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
//registerOutParameter()方法的第二个参数：如果是MySQL的驱动，使用Types.VARCHAR
cs.execute();
String stuName=cs.getString(2);
System.out.println(stuName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if (cs!=null) {
cs.close();
}
if (conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}}
查询所有
package java_procedure;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
//要用classes12.jar，ojdbcjar包有bug
/
--返回一个列表的存储过程
--建一个程序包
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
--建立存储过程
CREATE OR REPLACE PROCEDURE TESTC(
p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT FROM tb_student;
END TESTC;
*/
public class TestProcedureThree {
public static void main(String[] args) {
CallableStatement cs=null;
Connection conn=null;
ResultSet rs=null;
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String userName="a3";
String pwd="a123";
try {
Class.forName(driver);
conn=DriverManager.getConnection(url,userName,pwd);
cs=conn.prepareCall("{call testc(?)}");
cs.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
rs=(ResultSet)cs.getObject(1);
//上面一行代码可以这样写: rs=((OracleCallableStatement)cs).getCursor(1);
while(rs.next()){
System.out.println("ID:" rs.getString(1) "t" "姓名：" rs.getString(2));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
if (rs!=null) {
rs.close();
}
if (cs!=null) {
cs.close();
}
if (conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}}