plsql 存储过程创建和java调用存储过程

Love The Way You Lie 2022-05-14 05:04 478阅读 0赞

**一.存储过程**

相对于直接使用SQL 语句，在应用程序中直接调用存储过程有以下好处：   
   (1)减少网络通信量。   
   (2)执行速度更快。创建时候编译,解析,安全检查和优化,后来不需要重复编译,存在内存之中,直接调用效率高   
   (3)更强的适应性。    
   (4) 分布式工作。

**二.简单示例**

1.简单的helloword存储过程,可以直接在客户端直接进行调用

打印Hello World
    /*
    调用存储过程：
    1. exec sayHelloWorld();
    2. begin
         sayHelloWorld();
         sayHelloWorld();
       end;
       /
    
    */
    create or replace procedure sayHelloWorld
    as
      --说明部分
    begin
       dbms_output.put_line('Hello World');
    
    end;
    /

2.查询某个员工的信息

--in 输入参数
    --out 输出参数
    create or replace procedure queryempinfo(eno in number,
                                                pename out varchar2,
                                                psal   out number,
                                                pjob   out varchar2)
    as
    begin
      select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
    end;
    /

3.复杂的存储过程

-- ----------------------------
    -- procedure for addonprice_query
    -- ----------------------------
    -- Create procedure 
    create or replace procedure queryAddOnPrice(out_ow_price out number, voy in varchar2,  airportlines in varchar2, transfer in varchar2,
    			 leavePortTravelStartDate in varchar2,  leavePortTravelEndDate in varchar2, cab in varchar2, fltNum_addOn_In in varchar2, fltNum_addOn_Out in varchar2 )
    as
      
       POW_PRICE INTERNATIONAL_ADDON_XXX.OW_PRICE%type;
    begin
         SELECT   n.OW_PRICE into POW_PRICE   from(
    --   此处对查询出来的结果集进行分组,排序  partition 是分组
         select t.*,row_number() over(partition by INTERNATIONAL_LIMITED_FLIGHTS ,INLAND_LIMITED_FLIGHTS, 
            LEAVEPORT_TRAVEL_START_DATE, LEAVEPORT_TRAVEL_END_DATE,PUTPORT_TRAVEL_END_DATE,PUTPORT_TRAVEL_START_DATE order by UPDATE_TIME DESC,OW_PRICE asc) row_number  from (
          SELECT
             OW_PRICE,
             PUTPORT_TRAVEL_START_DATE,
             PUTPORT_TRAVEL_END_DATE,
             LEAVEPORT_TRAVEL_START_DATE,
             LEAVEPORT_TRAVEL_END_DATE,
             INTERNATIONAL_LIMITED_FLIGHTS,
             INLAND_LIMITED_FLIGHTS,
             UPDATE_TIME
            FROM
                INTERNATIONAL_ADDON_PRICE  addonprice
            WHERE
                1 = 1
            AND ADDON_AIRLINE_ID IN (
                SELECT
                    VOYAGEID
                FROM
                    INTERNATIONAL_ADDON_XXX
                WHERE
                    voyage = voy
            )
            AND AIRLINES LIKE '%'||airportlines||'%'
            AND TRANSFERS LIKE '%'||transfer||'%'
            AND STATUS = 1
            AND CABIN = cab
            AND ((LEAVEPORT_TRAVEL_START_DATE <= leavePortTravelStartDate AND LEAVEPORT_TRAVEL_END_DATE >= leavePortTravelStartDate)  OR (LEAVEPORT_TRAVEL_START_DATE is null and LEAVEPORT_TRAVEL_END_DATE is null ))
        --    AND LEAVEPORT_TRAVEL_END_DATE >= leavePortTravelStartDate
            AND ((PUTPORT_TRAVEL_END_DATE >= leavePortTravelEndDate AND PUTPORT_TRAVEL_START_DATE <= leavePortTravelEndDate) OR (PUTPORT_TRAVEL_END_DATE is null and PUTPORT_TRAVEL_START_DATE is null))
        --    AND PUTPORT_TRAVEL_START_DATE <= leavePortTravelEndDate
            AND leavePortTravelStartDate >= TRAVEL_START_DATE
            AND leavePortTravelEndDate <= TRAVEL_END_DATE
            AND (INLAND_LIMITED_FLIGHTS LIKE '%'||fltNum_addOn_In||'%'  or INLAND_LIMITED_FLIGHTS is null)
            AND (INTERNATIONAL_LIMITED_FLIGHTS LIKE '%'||fltNum_addOn_Out||'%' or INTERNATIONAL_LIMITED_FLIGHTS is null)
         --   AND ( INLAND_LIMITED_FLIGHTS is not null or INTERNATIONAL_LIMITED_FLIGHTS is not null)
          GROUP BY 
             OW_PRICE,
             PUTPORT_TRAVEL_START_DATE,
             PUTPORT_TRAVEL_END_DATE,
             LEAVEPORT_TRAVEL_START_DATE,
             LEAVEPORT_TRAVEL_END_DATE,
             INTERNATIONAL_LIMITED_FLIGHTS,
             INLAND_LIMITED_FLIGHTS,
             UPDATE_TIME
            ORDER BY
              UPDATE_TIME DESC,
              INTERNATIONAL_LIMITED_FLIGHTS nulls last,
              INLAND_LIMITED_FLIGHTS nulls last,
              OW_PRICE asc,
              LEAVEPORT_TRAVEL_START_DATE nulls last,
              LEAVEPORT_TRAVEL_END_DATE nulls last,
              PUTPORT_TRAVEL_START_DATE nulls last,
              PUTPORT_TRAVEL_END_DATE nulls last
              
            ) t
           ) 
            n where ROWNUM = 1;
       out_ow_price := POW_PRICE;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN
      out_ow_price := 0;
      dbms_output.put_line('没有XXXX的数据');
    
    
    end;

4.java代码调用存储过程

Session openSession = this.getHibernateTemplate().getSessionFactory().openSession();
    		Connection conn = null;
    		CallableStatement call = null;
    		Double addonPrice = 0d;
    		//String sql = "{?=call queryAddOnPrice(?,?,?,?,?,?,?,?)}";
    		String hql =  "{call queryAddOnPrice(?,?,?,?,?,?,?,?,?)}";
    		try {
    			conn = openSession.connection();
    			call = conn.prepareCall(hql);
    			// out 参数 声明
    			call.registerOutParameter(1, OracleTypes.NUMBER);
    			call.setString(2, voyage);   // in  参数 赋值    
    			call.setString(3, airportlines);
    			call.setString(4, transfers);
    			call.setString(5, leavePortTravelStartDate);
    			call.setString(6, leavePortTravelEndDate);
    			call.setString(7, cabin);
    			call.setString(8, fltNum_addOn_In);
    			call.setString(9, fltNum_addOn_Out);
    			
    			call.execute(); // 执行
    			
    			 addonPrice = call.getDouble(1); // 取出结果
    			 log.info("通过存储过程   国内ADDON价格费用为:" + addonPrice );
    		} catch (Exception e) {
    			log.error("通过存储过程   国内ADDON价格费用出错!");
    			e.printStackTrace();
    		}finally{
    			WebUtil.release(call, conn, openSession);
    		}