oracle存储过程相关整理

--案例一:无参存储过程 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 THEN 16 ROLLBACK; 17 end firstPro; 18 19 --3.数据库调用存储过程:执行结果：output: Hello World 20 --(21) begin-end 21 begin 22 firstPro(); 23 end; 24 --(22)call 25 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 begin
12   secondPro(7);
13 end;
14 --(23)
15 declare
16   n number;
17 begin
18   n := 1;
19   secondPro(num=>n);
20 end;
21 --or
22 Begin
23   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_merchant
10       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();  

3.output结果：

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

 

  

oracle存储过程相关整理

标签：use   知识   oracl   gate   tco   ble   分享   打印   动态