oracle存储过程相关整理 -云博客

oracle存储过程相关整理

# 数据库 2024-05-05 06:46 0 21 来源：云博客

存储过程：

存储过程是 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 --案例一:无参存储过程 2 --1.创建结构 3 CREATE PROCEDURE procedureName--存储过程名字 4 AS --as可替换成 is  5 --声明变量 6 BEGIN 7 --执行主体 8 END; 9 10 --2.案例11 create or replace procedure firstPro is 12 begin 13 dbms_output.put_line(‘Hello World‘);--打印输出14 exception --存储过程异常15 WHEN OTHERS THEN16 ROLLBACK;17 end firstPro;18 19 --3.数据库调用存储过程:执行结果：output: Hello World20 --(21) begin-end21 begin22  firstPro();23 end;24 --(22)call25 call firstPro();26 27 --4.删除存储过程28 drop procedure firstPro;

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

 1 --案例三:存储过程：声明变量 2 --1.案例 3 CREATE OR REPLACE procedure thirdPro  4 is  5 n_start number;  6 n_end number;  7 count_num number; 8 use_time number; 9 begin 10 n_start:=dbms_utility.get_time;11 dbms_output.put_line(‘This statement start time : ‘|| n_start );12 --查看oracle数据库版本13 SELECT count(*) into count_num FROM v$version;14 n_end:=dbms_utility.get_time; 15 dbms_output.put_line(‘This statement end time : ‘|| n_end ); 16 use_time:= n_end - n_start; 17 dbms_output.put_line(‘This statement cost ‘|| use_time ||‘ miliseconds‘); 18 end; 19 --备注：20 --（1）dbms_utility.get_time 返回当前时间的1/100秒，毫秒21 --它是用以前后两个取点做对s比的，单个是没有具体意义的，就是用来取差值的！22 23 --2.执行存储过程24 --(21)25 call thirdPro();26 --(22)27 begin 28  thirdPro();29 end;

 1 --案例四：动态sql语句执行 2 --1.案例 3 CREATE OR REPLACE PROCEDURE FourthProc(id varchar2 ,dicName VARCHAR2) AS  4 mysql VARCHAR2(500);  5 BEGIN  6 mysql:=‘UPDATE sys_dictionary SET dic_name=:1 WHERE id=:2‘;  7 EXECUTE IMMEDIATE mysql USING dicName,id;  8 commit; 9 END;10 11 --2.执行存储过程12 CALL FourthProc(‘22ff8102-95cd-4862-a2ec-d011eca75ef1‘,‘男‘)

 1 --案例五：返回结果集 2 --1.案例 3 create or replace procedure FifthPro( 4  cur_OUT OUT SYS_REFCURSOR 5 ) is 6  7 begin 8 OPEN cur_OUT FOR 9 select cname,merch_no,taxno zjid,‘营业执照‘ zjname,BUSINESS_LICENSE_VALIDITY validity,trunc(BUSINESS_LICENSE_VALIDITY-sysdate) gqdate from quas.base_merchant10 where BUSINESS_LICENSE_VALIDITY-sysdate < 60 ;11 12 end FifthPro;

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

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

 1 /** 2  *  3 */ 4 package kklazy.reportquery.service; 5  6 import java.sql.CallableStatement; 7 import java.sql.Connection; 8 import java.sql.DriverManager; 9 import java.sql.ResultSet; 10 import java.sql.SQLException; 11 import java.util.ArrayList; 12 import java.util.List; 13 import javax.transaction.Transactional; 14 import org.hibernate.Session; 15 import org.hibernate.internal.SessionFactoryImpl; 16 import org.springframework.beans.factory.annotation.Value; 17 import org.springframework.stereotype.Service; 18 import kklazy.ctps.service.DefaultCtpsService; 19 import kklazy.reportquery.model.ReportQueryEntity; 20 import oracle.jdbc.internal.OracleTypes; 21  22 /** 23  * @author Administrator 24  *  25 */ 26 @Service("fifthProService") 27 @Transactional(rollbackOn=Exception.class) 28 public class FifthProService extends DefaultCtpsService<ReportQueryEntity, String>{ 29 //注入jdbc连接参数 30 @Value("${ctps.database.driver}") 31 private String driverClass; 32 @Value("${ctps.database.url}") 33 private String url; 34 @Value("${ctps.database.username}") 35 private String username; 36 @Value("${ctps.database.password}") 37 private String password; 38  39  40 /** 41  * 执行存储过程 42 */ 43 public List<ReportQueryEntity> execute() { 44 Session session = (Session) this.getJpa().getManager().getDelegate(); 45 SessionFactoryImpl sessionFactory = (SessionFactoryImpl) session.getSessionFactory(); 46 Connection conn = null; 47 ResultSet result = null; 48 List<ReportQueryEntity> allList = new ArrayList<ReportQueryEntity>(); 49 try { 50 conn = sessionFactory.getConnectionProvider().getConnection(); 51 if (conn == null || conn.isClosed()) { 52 try { 53  Class.forName(driverClass); 54 conn = DriverManager.getConnection(url, username, password); 55 } catch (ClassNotFoundException e) { 56  e.printStackTrace(); 57  } 58  } 59 } catch (SQLException e) { 60  e.printStackTrace(); 61  } 62 try { 63 CallableStatement call = conn.prepareCall("{call FifthPro(?,?)}"); 64  System.out.println(); 65 call.setString(1, "00"); 66 call.registerOutParameter(2, OracleTypes.CURSOR); 67  call.execute(); 68 result = (ResultSet)call.getObject(2); 69 if(result == null) { 70 System.out.println("查询失败！"); 71 }else { 72 allList = getFindList(result); 73 System.out.println("查询成功"); 74  } 75 System.out.println("执行存储过程的结果是：" + result); 76 } catch (SQLException e) { 77  e.printStackTrace(); 78  System.out.println(e); 79 }finally { 80 if (conn != null) { 81 try { 82  conn.close(); 83 } catch (SQLException e) { 84  e.printStackTrace(); 85  } 86  } 87  } 88 return allList; 89  } 90  91 public List<ReportQueryEntity> getFindList(ResultSet result) throws SQLException{ 92  93 List<ReportQueryEntity> allList = new ArrayList<ReportQueryEntity>(); 94 if(null !=result ) { 95 while (result.next()) { 96 ReportQueryEntity reportEntity = new ReportQueryEntity(); 97 reportEntity.setData1(result.getString(1)); 98 reportEntity.setData2(result.getString(2)); 99 reportEntity.setData3(result.getString(3));100 reportEntity.setData4(result.getString(4));101 reportEntity.setData5(result.getString(5));102 reportEntity.setData6(result.getString(6));103 104  allList.add(reportEntity);105  }106  }107 return allList;108  }109 110 }

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

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

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

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