oracle存储过程

什么是：存储过程（Stored Procedure ）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。

怎么用：用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

优点啥的？：存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。 存储过程是由流控制和SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。在Oracle 中，若干个有联系的过程可以组合在一起构成程序包。

存储过程的创建

使用命令语法如下：

 DROP PROCEDURE IF EXISTS add_gslb();//如果存在就删除

 CREATE [OR REPLACE] 
 DEFINER=`root`@`localhost` 
 PROCEDURE add_gslb(INOUT p_inout int)// 为某个用户和密码 创建存储过程。

下图是员工表： 我们用存储过程来修改员工信息，

job为CLERK的工资增加10%，

job为SALESMAN的工资增加20%，

job为ANALYST的工资增加30%，其他员工工资不变，失败则回滚。

    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;  

写一个带参数的存储过程，如果执行成功，返回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;  

过程中参数必须符合规范，在一个存储过程中可以传递也可以不传递参数，可以传递一个或者多个参数，和函数是一样，参数的类型有三种：
·in：表示执行过程传入的参数
·out：表示执行过程返回的参数
·in out：表示即可以作为传入的参数，也可以作为返回的参数（不建议使用的）
如果在参数中，定义一个out，一般的开发中，在没有异常发送的情况下，返回1，否则返回0。

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

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

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

(4)分布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。 
使用存储过程的缺点：
(1)可移植性差

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

附带java中调用存储过程代码：使用

CallableStatement //进行调用 

    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(?,?,?)}";  //调用的sql
                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();  
                }  
            }  
        }  
    }  

原文地址：http://blog.csdn.net/weixin_36380516/article/details/68576444