Passing a list of dates to an oracle db function via java

问题
create or replace 
FUNCTION getWorkingDays (DATE_ONE DATE, DATE_TWO DATE) RETURN NUMBER
IS
DAY_COUNT NUMBER := 0;
START_DATE DATE;
END_DATE DATE;
BEGIN -- loop through and update
IF(DATE_ONE is not null and DATE_TWO is not null)
THEN
IF DATE_ONE < DATE_TWO THEN
  START_DATE := DATE_ONE;
  END_DATE := DATE_TWO;
ELSE
  START_DATE := DATE_TWO;
  END_DATE := DATE_ONE;
END IF;

WHILE START_DATE < END_DATE
LOOP
  IF TO_CHAR(START_DATE,'DY') NOT IN ('SAT','SUN') THEN
    DAY_COUNT := DAY_COUNT + 1;
END IF;
  START_DATE := START_DATE + 1;
END LOOP;
END IF;
RETURN DAY_COUNT;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END getWorkingDays;
回答1

将数组传递给数据库函数有很多方法。 一个简单的如下:

首先,您应该在 DB 模式中创建一个TABLE类型:

CREATE TYPE DATE_ARRAY AS TABLE OF DATE;

之后,您应该使用这种新型输入编写一个FUNCTION

-- a dummy function just for presenting the usage of input array
CREATE FUNCTION Date_Array_Test_Function(p_data IN DATE_ARRAY) 
RETURN INTEGER  
IS
    TYPE Cur IS REF CURSOR;
    MyCur cur;

    single_date DATE;
BEGIN
    /* Inside this function you can do anything you wish 
        with the input parameter: p_data */

    OPEN MyCur FOR SELECT * FROM table(p_data);

    LOOP
      FETCH MyCur INTO single_date;
      EXIT WHEN MyCur%NOTFOUND;

      dbms_output.put_line(to_char(single_date));
    END LOOP;

    RETURN 0;

END Date_Array_Test_Function;

现在在java代码中,您可以使用以下代码调用具有数组输入类型的此类函数:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class Main 
{           
    public static void main(String[] args) throws SQLException 
    {
        Connection c = DriverManager.getConnection(url, user, pass);

        String query = "begin ? := date_array_test_function( ? ); end;";

        // note the uppercase "DATE_ARRAY"
        ArrayDescriptor arrDescriptor = ArrayDescriptor.createDescriptor("DATE_ARRAY", c);

        // Test dates
        Date[] inputs = new Date[] {new Date(System.currentTimeMillis()),
                                    new Date(System.currentTimeMillis()),
                                    new Date(System.currentTimeMillis())};

        ARRAY array = new ARRAY(arrDescriptor, c, inputs);

        CallableStatement cs = c.prepareCall(query);
        cs.registerOutParameter(1, Types.INTEGER); // the return value
        cs.setObject(2, array); // the input of the function
        cs.executeUpdate();

        System.out.println(cs.getInt(1));
    }
}

希望这会有所帮助。

祝你好运

回答2
CREATE TYPE DateList IS TABLE OF DATE;
/

CREATE FUNCTION getWorkingDays (
  in_start_date IN  DATE,
  in_end_date   IN  DATE,
  in_date_list  IN  DateList
) RETURN NUMBER
IS
  p_start_date   DATE;
  p_end_date     DATE;
  p_working_days NUMBER;
BEGIN
  IF in_start_date IS NULL OR in_end_date IS NULL THEN
    RETURN NUll;
  END IF;

  p_start_date := TRUNC( LEAST( in_start_date, in_end_date ) );
  p_end_date   := TRUNC( GREATEST( in_start_date, in_end_date ) );

  -- 5/7 * ( Number of days between monday of the week containing the start date
  --         and monday of the week containing the end date )
  -- + LEAST( day of week for end date, 5 )
  -- - LEAST( day of week for start date, 5 )
  p_working_days := ( TRUNC( p_end_date, 'IW' ) - TRUNC( p_start_date, 'IW' ) ) * 5 / 7
                    + LEAST( p_end_date - TRUNC( p_end_date, 'IW' ) + 1, 5 )
                    - LEAST( p_start_date - TRUNC( p_start_date, 'IW' ) + 1, 5 );

  IF in_date_list IS NOT NULL THEN
    FOR i IN 1 .. in_date_list.COUNT LOOP
      IF in_date_list(i) >= p_start_date AND in_date_list(i) < p_end_date THEN
        p_working_days := p_working_days - 1;
      END IF;
    END LOOP;
  END IF;

  RETURN p_working_days;
END;
/
标签

更多相关内容:请点击查看