Oracle存储过程介绍

存储过程（Stored Procedure ）是一组为了完成特定功能的sql 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。存储过程是由流控制和sql 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。在Oracle 中，若干个有联系的过程可以组合在一起构成程序包。

存储过程的创建

使用CREATE OR REPLACEPROCEDURE命令语法如下：

CREATE [OR REPLACE] PROCEDURE PROCEDURE_name
    (argument1 [mode1] datatype1,argument2 [mode2] datatype2,…)
IS [AS]
PL/sqlBlock；

如上所示，procedure_name 用于指定过程名，argument 用于指定过程参数，IS 或 AS用于开始PL/sql块。注意，当定义参数时，只能指定数据类型，不能指定长度。

如上图，是员工信息表，现在使用存储过程来修改员工信息，job为CLERK的工资增加10%，job为SALESMAN的工资增加20%，job为ANALYST的工资增加30%，其他员工工资不变，失败则回滚。
使用PLsql程序设计语言来为它创建一个存储过程。
代码如下：

create or replace procedure update_sal
as
  --根据主键进行修改
  u_sal number(7,2) := 0;--修改后的工资
begin
       for dump in(select job,empno,sal from emp) loop
          case
                when dump.job = 'CLERK' then
                     u_sal := dump.sal*1.1;
                when dump.job = 'SALESMAN' then
                     u_sal := dump.sal*1.2;
                when dump.job = 'ANALYST' then
                     u_sal := dump.sal*1.3;
                else
                     u_sal := dump.sal;
          end case;
          update emp t set t.sal = u_sal where t.empno = dump.empno;
       end loop;
       commit;
       exception when others then
       rollback;
end;

新建一个测试窗口，运行上面的代码，成功之后，打开存储过程文件夹---->procedures。我们可以看到新建的存储过程update_sal，右击选择测试。

运行之后查看运行结果，如下图，工资的更新操作已经完成。

这里需要注意的是，定义数据长度时要与数据表的一致或大于数据表，如果存储过程中定义的长度小于表中数据长度时执行不会成功。
在开发中，也经常会编写一个带参数的过程，现在传入2个参数，向部门表dept中插入一笔数据。
首先先查询看一下部门表的信息，如下图：

写一个带参数的存储过程，如果执行成功，返回1，失败则返回0，代码如下：

create or replace procedure 
do_insert_dept(dept_name in varchar2,dept_loc in varchar2,is_success out number)
as
begin
         insert into dept(deptno,dname,loc)
         values(scott_squence.nextval,dept_name,dept_loc);
         commit ;
         is_success := 1;
         exception when others then
         rollback;
         is_success:=0;
end do_insert_dept;

按上面所述方法执行，右击存储过程名do_insert_dept，点击测试之后可以填写参数，新增部门dname为销售部，地址loc为山西太原，如下图：

点击运行，如下图，新增成功，返回数据1.

查询部门信息，我们可以看到，数据插入成功了

需要注意的是，由于部门编号是主键，不能出现重复的部门编号，所以我们这里要用到序列。
序列是一数据库对象，利用它可生成唯一的整数。一般使用序列自动地生成主键值。
关于序列sequence的介绍，可以看这里http://blog.csdn.net/weixin_36380516/article/details/68520098
过程中参数必须符合规范，在一个存储过程中可以传递也可以不传递参数，可以传递一个或者多个参数，和函数是一样，参数的类型有三种：
·in：表示执行过程传入的参数
·out：表示执行过程返回的参数
·in out：表示即可以作为传入的参数，也可以作为返回的参数（不建议使用的）
如果在参数中，定义一个out，一般的开发中，在没有异常发送的情况下，返回1，否则返回0。

在PLsql Developer中测试成功了，我们也可以使用JDBC来调用它：

public class JDBCTest {
	//定义数据库驱动程序类
	public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
	//定义数据库连接池
	public static final String URL = "jdbc:oracle:thin:@localhost:1521:ceshi";
	//数据库用户
	public static final String DBNAME = "scott";
	//用户密码
	public static final String PASSWORD = "1111";
	
	public static void main(String[] args) {
		Connection conn = null;
		CallableStatement calls = null;
		//是用来调用过程和函数的操作接口
		try {
			//使用反射机制加载驱动程序
			Class.forName(DRIVER);
			//取得连接
			conn = DriverManager.getConnection(URL,DBNAME,PASSWORD);
			String sql = "{call do_insert_dept(?,?,?)}";
			calls= conn.prepareCall(sql);//取得操作对象
			calls.setString(1,"技术部");
			calls.setString(2,"山西太原");
			calls.registerOutParameter(3,java.sql.Types.INTEGER);//注册返回类型
			calls.execute();//执行
			int returnNum = cstate.getInt(3);//取得返回结果
			System.out.println(returnNum);
		
		} catch (Exception e) {
			e.printStackTrace();//打印异常
		} finally{
			try {
				calls.close();
				conn.close();//连接关闭
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}
}

使用存储过程的优点：
(1)减少网络通信量。调用一个行数不多的存储过程与直接调用 sql语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行sql语句，那么其性能绝对比一条一条的调用 sql语句要高得多。

　　(2)执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

　　(3)更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

　　(4) 布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

使用存储过程的缺点： (1)可移植性差 (2)如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，这时候操作就比较繁琐了

Oracle存储过程介绍

猜你在找的Oracle相关文章