oracle存储过程--在应用程序中访问存储过程程序完整举例

左手的ㄟ右手 2022-08-04 05:19 192阅读 0赞

**认识存储过程和函数**

存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序，我们通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比，存储过程有很多优点，具体归纳如下：  
\* 存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。  
\* 存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。  
\* 存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设计成存储函数。  
\* 像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。  
   存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。

首先得有表盒数据，那么第一步就是创建表盒插入数据：

SQL> create table emp
      2  (empno number,
      3  ename varchar2(10),
      4  sal number,
      5  job varchar2(100)
      6  );
     
    Table created
     
    SQL> insert into emp values(1,'王一',10000,'软件工程师');
     
    1 row inserted
     
    SQL> insert into emp values(2,'王二',8000,'摄影师');
     
    1 row inserted
     
    SQL> select * from emp;
     
         EMPNO ENAME             SAL JOB
    ---------- ---------- ---------- --------------------------------------------------------------------------------
             1 王一          10000 软件工程师
             2 王二           8000 摄影师
     
    SQL>

现在叫我们查询某个员工姓名，月薪和职位，那么我们创建一个存储过程如下：

--out参数：查询某个员工姓名，月薪和职位
    /*
      思考：
      1、查询某个员工的所有信息-->out参数太多？
      2、查询某个部门中所有员工的所有信息-->out中返回集合？
    */
    --删除存储过程
    
    create or replace procedure queryempincome1(eno in number,
                                               pename out varchar2,
                                               psal out number,
                                               pjob out varchar2)
    as                                          
    begin
       --得到该员工的姓名、月薪和职位 
       select ename,sal,job into pename,psal,pjob from emp where empno=eno;                                    
    end;
    /

然后我们在eclipse里面编写Java代码链接oracle数据库和访问存储过程，在控制台输出指定id的用户的基本信息：

package cn.edu.jdbc;
    
    import java.sql.CallableStatement;
    import java.sql.Connection;
    
    import oracle.jdbc.OracleTypes;
    
    import org.junit.Test;
    /**
     * create or replace procedure queryempinform(eno in number,
                                               pename out varchar2,
                                               psal out number,
                                               pjob out varchar2)
     * @author wf
     *
     */
    public class TestProcedure {
    	@Test
    	public void testProcedure(){
    		//{call <procedure-name[(<arg1>,<arg2>,<arh3>...)]>}
    		String sql = "{call queryempincome1(?,?,?,?)}";
    		
    		Connection connection = null;
    		CallableStatement call = null;
    		try {
    			//得到一個连接
    			connection = JDBCUtils.getConnection();
    			//通过连接创建输出statement
    			call = connection.prepareCall(sql);
    			
    			//对于in(输入)参数，要赋值
    			call.setInt(1, 2);//为第一个存储过程的参数赋值为1表示查询编号为1的员工信息
    			
    			//对于out(输出)，需要申明
    //			call.registerOutParameter(2, sqlType);
    			call.registerOutParameter(2, OracleTypes.VARCHAR);//指定oracle的输出参数类型
    			call.registerOutParameter(3, OracleTypes.NUMBER);
    			call.registerOutParameter(4, OracleTypes.VARCHAR);
    			
    			//执行调用
    			call.execute();
    			
    			//取出结果
    			String name = call.getString(2);
    			Double sal = call.getDouble(3);
    			String job = call.getString(4);
    			System.out.println("姓名:"+name+"\t"+"薪水:"+sal+"\t"+"工作:"+job);
    			
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    }