oracle存储过程相关整理

存储过程：

存储过程是 SQL, PL/SQL, Java 语句的组合， 它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。是存放在数据库服务器上的SQL语句块，其效率高于同等SQL语句6-10倍

下面通过例子让你了解存储过程对数据的增删查改（对Oracle中的emp操作）

一、Oracle存储过程语法：

Create [or replace] procedure 存储过程名称 (输入参数或输出参数)]as

变量

Begin

执行主体

End；

二、IN, OUT , IN OUT 用来修饰参数。

IN 表示这个变量必须被调用者赋值然后传入到 PROCEDURE 进行处理。

OUT 表示 PRCEDURE 通过这个变量将值传回给调用者。

IN OUT 则是这两种的组合。

三、执行存储过程方式：

1、Call 存储过程名称（参数）；

2、Execute 存储过程名称（参数）；

注意：在oracle 数据库中，call命令任何窗口都能使用，但是execute只能在命令窗口使用，否则会报无效的SQL语句的异常。

四、在存储过程中需要注意事项：

1、在oracle数据库存储过程中，表别名不能用as

2、在oracle数据库存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录则必须使用游标处理

3、在使用select....into....时必须保证数据库有该数据，否则报”no data found”异常

4、在存储过程中，别名不能和字段名相同，否则虽然编译能通过，但是运行结果会报错

五、存储过程基本语法

--案例一:无参存储过程
--1.创建结构
CREATE PROCEDURE procedureName--存储过程名字
  AS                          --as可替换成 is
    --声明变量
  BEGIN
    --执行主体
END;

--2.案例
create or replace procedure firstPro is
begin
  dbms_output.put_line('Hello World');--打印输出
exception   --存储过程异常
  WHEN OTHERS THEN
    ROLLBACK;
end firstPro;

--3.数据库调用存储过程:执行结果：output:  Hello World
--(21) begin-end
begin
  firstPro();
end;
--(22)call
call firstPro();

--4.删除存储过程
drop procedure firstPro;

--案例二:带参存储过程（in:入参）
--1.案例
create or replace procedure secondPro(num in number) as
begin
  dbms_output.put_line('The input num is :'||num);
end;
--2.调用
--(21)
call secondPro(4);
--(22)
begin
  secondPro(7);
end;
--(23)
declare
  n number;
begin
  n := 1;
  secondPro(num=>n);
end;
--or
Begin
  secondPro(num=>1);
end;
--备注：=> 是 Oracle 中调用 存储过程的时候, 指定 参数名进行调用
--一般如果是按顺序填写参数的时候,是不需要用=>符号的,
--但是Oracle可以让你在调用的时候,指定"参数名称=>参数值", 这样就可以不按参数的顺序进行调用.
-- => 前面的变量为存储过程的“形参”且必须于存储过程中定义的一致，而=>后的参数为“实际参数”。

--案例三:存储过程：声明变量
--1.案例
CREATE OR REPLACE procedure thirdPro
  is
    n_start   number;
    n_end   number;
    count_num number;
    use_time number;
  begin
    n_start:=dbms_utility.get_time;
    dbms_output.put_line('This   statement   start  time : '|| n_start );
    --查看oracle数据库版本
    SELECT count(*) into count_num FROM v$version;
    n_end:=dbms_utility.get_time;
    dbms_output.put_line('This   statement   end  time : '|| n_end );
    use_time:=   n_end   -   n_start;
    dbms_output.put_line('This   statement   cost   '|| use_time ||'   miliseconds');
end;
--备注：
--（1）dbms_utility.get_time 返回当前时间的1/100秒，毫秒
--它是用以前后两个取点做对s比的，单个是没有具体意义的，就是用来取差值的！

--2.执行存储过程
--(21)
call thirdPro();
--(22)
begin
   thirdPro();
end;

--案例四：动态sql语句执行
--1.案例
CREATE OR REPLACE PROCEDURE FourthProc(id varchar2 ,dicName VARCHAR2) AS
mysql VARCHAR2(500);
BEGIN
  mysql:='UPDATE sys_dictionary SET dic_name=:1 WHERE id=:2';
  EXECUTE IMMEDIATE mysql USING dicName,id;
  commit;
END;

--2.执行存储过程
CALL FourthProc('22ff8102-95cd-4862-a2ec-d011eca75ef1','男')

--案例五：返回结果集
--1.案例
create or replace procedure FifthPro(
       cur_OUT    OUT    SYS_REFCURSOR
) is

begin
   OPEN cur_OUT FOR
      select cname,merch_no,taxno zjid,'营业执照' zjname,BUSINESS_LICENSE_VALIDITY validity,trunc(BUSINESS_LICENSE_VALIDITY-sysdate) gqdate from quas.base_merchant
      where  BUSINESS_LICENSE_VALIDITY-sysdate < 60 ;

end FifthPro;

2.案例五：Test测试存储过程步骤：Test-》点击Start Debugger 按钮：开始执行存储过程-》点击run按钮存储过程直接执行到结束，返回如图2的结果Cursor->点击右上角的按钮，查看结果集：如图3所示。（若点击step  into 按钮，则进入存储过程详细代码，按步执行）

3.java代码执行存储过程：

/**
 *
 */
package kklazy.reportquery.service;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.transaction.Transactional;
import org.hibernate.Session;
import org.hibernate.internal.SessionFactoryImpl;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import kklazy.ctps.service.DefaultCtpsService;
import kklazy.reportquery.model.ReportQueryEntity;
import oracle.jdbc.internal.OracleTypes;

/**
 * @author Administrator
 *
 */
@Service("fifthProService")
@Transactional(rollbackOn=Exception.class)
public class FifthProService extends DefaultCtpsService<ReportQueryEntity, String>{
    //注入jdbc连接参数
    @Value("${ctps.database.driver}")
    private String driverClass;
    @Value("${ctps.database.url}")
    private String url;
    @Value("${ctps.database.username}")
    private String username;
    @Value("${ctps.database.password}")
    private String password;


    /**
     * 执行存储过程
     */
    public List<ReportQueryEntity> execute() {
        Session session = (Session) this.getJpa().getManager().getDelegate();
        SessionFactoryImpl sessionFactory = (SessionFactoryImpl) session.getSessionFactory();
        Connection conn = null;
        ResultSet  result = null;
        List<ReportQueryEntity> allList = new ArrayList<ReportQueryEntity>();
        try {
            conn = sessionFactory.getConnectionProvider().getConnection();
            if (conn == null || conn.isClosed()) {
                try {
                    Class.forName(driverClass);
                    conn = DriverManager.getConnection(url, username, password);
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            CallableStatement call = conn.prepareCall("{call  FifthPro(?,?)}");
            System.out.println();
            call.setString(1, "00");
            call.registerOutParameter(2, OracleTypes.CURSOR);
            call.execute();
            result = (ResultSet)call.getObject(2);
            if(result == null) {
                System.out.println("查询失败！");
            }else {
                allList = getFindList(result);
                System.out.println("查询成功");
            }
            System.out.println("执行存储过程的结果是：" + result);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println(e);
        }finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return allList;
    }

    public List<ReportQueryEntity> getFindList(ResultSet result) throws SQLException{

        List<ReportQueryEntity> allList = new ArrayList<ReportQueryEntity>();
        if(null !=result ) {
            while (result.next()) {
                ReportQueryEntity reportEntity = new ReportQueryEntity();
                reportEntity.setData1(result.getString(1));
                reportEntity.setData2(result.getString(2));
                reportEntity.setData3(result.getString(3));
                reportEntity.setData4(result.getString(4));
                reportEntity.setData5(result.getString(5));
                reportEntity.setData6(result.getString(6));

                allList.add(reportEntity);
            }
        }
        return allList;
    }

}

案例六、查询所有数据（游标的具体使用详见：https://www.cnblogs.com/xiaoliu66007/p/7495753.html）

PL/SQL 中 SELECT 语句只返回一行数据。如果超过一行数据，那么就要使用显式游标，INTO 子句中要有 SELECT子句中相同列数量的变量。

INTO 子句中也可以是记录变量。

--案例六：显式游标
--------1.用游标显示查询所有的结果
CREATE OR REPLACE procedure sys_dictionary_proc
AS
CURSOR dictionary_emp IS SELECT * FROM sys_dictionary where dic_group ='OPERATE_TYPE';--定义游标,该游标指向查询结果
rowresult sys_dictionary%ROWTYPE;
BEGIN
  OPEN dictionary_emp;--打开游标
  LOOP FETCH dictionary_emp INTO rowresult;--将游标中的值赋给rowresult
    EXIT WHEN dictionary_emp%NOTFOUND;--判断：游标不存在时跳出循环
    dbms_output.put_line('分组：'||rowresult.dic_group||'值：'||rowresult.DIC_VALUE||'显示名：'||rowresult.dic_name);
  END LOOP;
  CLOSE dictionary_emp;--关闭游标
END;
drop procedure sys_dictionary_proc;

--2.调用
CALL sys_dictionary_proc();

3.output结果：

分组：OPERATE_TYPE值：1显示名：新增
分组：OPERATE_TYPE值：2显示名：修改
分组：OPERATE_TYPE值：3显示名：删除