用ireport调用oracle存储过程

ireport不能直接调用oracle存储过程的结果集，因为存储过程不能返回标准的结果集。在oracle中作为解决方案，你可以使用存储函数来得到存储过程的结果集。

CREATE GLOBAL TEMPORARY TABLE TEMP_DATA(
    create_date DATE,
    case_id VARCHAR2(30),
    division_a NVARCHAR2(50),
    division_name NVARCHAR2(50),
    case_type NVARCHAR2(255),
    case_stage_2 VARCHAR2(20),
    case_stage_3 VARCHAR2(20),
    case_stage_4 VARCHAR2(20),
    transaction_remark NVARCHAR2(255)
) ON COMMIT PRESERVE ROWS

CREATE OR REPLACE PROCEDURE LOAD_TEMP_DATA
as
    TYPE cur is REF CURSOR;
    myCursor cur;   
    create_date DATE;
    case_id VARCHAR2(30);
    division_a NVARCHAR2(50);
    division_name NVARCHAR2(50);
    case_type NVARCHAR2(255);
    case_stage VARCHAR2(2);
    case_assignee VARCHAR2(255);
    transaction_remark NVARCHAR2(255);
begin
    EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_DATA';
    COMMIT;
    
    OPEN myCursor FOR 
      select 
          table1.create_date, table1.case_id,
          table2.division_name, 
          table1.division_name,
          table3.description c_type,
          table1.case_stage, table1.case_assignee,
          table1.transaction_remark
      from
      (select 
          cm.create_date, cm.case_id, dt.division_name, dt.division_parent_id, cm.case_type,
          ct.transaction_id, ct.case_stage, ct.case_action, ct.transaction_remark, ct.case_assignee
      from
          com.division_type dt, com.user_master um, com.case_master cm, com.case_transaction ct
      where
          dt.division_id= um.division_id and cm.create_by= um.user_id and ct.case_id= cm.case_id
          and ct.case_action='A5'
      order by
          cm.case_id, ct.transaction_id
      )table1 
      join
      (select 
          dt.division_id, dt.division_name
      from 
          com.division_type dt 
      where 
          dt.division_level = 'A'
      )table2 on table1.division_parent_id=table2.division_id
      join
      (select
          p.field_value fvalue, 
          p.field_description description
      from
          com.parameter p
      where
          p.field_name='Case_Type'
      )table3 on table1.case_type = table3.fvalue;
    
    LOOP
    FETCH myCursor INTO
    create_date, case_id, division_a, division_name, 
    case_type, case_stage, case_assignee, transaction_remark;
    EXIT WHEN myCursor%NOTFOUND;
    dbms_output.put_line(case_id);
    IF case_stage='S2' THEN
        INSERT INTO TEMP_DATA VALUES
        (
            create_date, case_id, division_a, division_name,
            case_type, case_assignee, '---', '---', transaction_remark
        );
    ELSIF case_stage='S3' THEN
        INSERT INTO TEMP_DATA VALUES
        (
            create_date, case_id, division_a, division_name,
            case_type, '---', case_assignee, '---', transaction_remark
        );
    ELSIF case_stage='S4' THEN
        INSERT INTO TEMP_DATA VALUES
        (
            create_date, case_id, division_a, division_name,
            case_type, '---', '---', case_assignee, transaction_remark
        );
    ELSE
        INSERT INTO TEMP_DATA VALUES
        (
            create_date, case_id, division_a, division_name,
            case_type, '---', '---', '---', transaction_remark
        );
    END IF;
    COMMIT;
    END LOOP;    
    CLOSE myCursor; 
end;

--call LOAD_TEMP_DATA();

--select * from TEMP_DATA;

CREATE OR REPLACE TYPE DATA_TYPE AS OBJECT(
    create_date DATE,
    case_id VARCHAR2(30),
    division_a NVARCHAR2(50),
    division_name NVARCHAR2(50),
    case_type NVARCHAR2(255),
    case_stage_2 VARCHAR2(20),
    case_stage_3 VARCHAR2(20),
    case_stage_4 VARCHAR2(20),
    transaction_remark NVARCHAR2(255)
)

CREATE OR REPLACE TYPE DATA_TYPE_TABLE AS TABLE OF DATA_TYPE

CREATE OR REPLACE FUNCTION DATAS_FUNCTION
    return DATA_TYPE_TABLE pipelined
is
    PRAGMA AUTONOMOUS_TRANSACTION;

    TYPE cur1 is REF CURSOR;
    myCursor cur1;
    out_rec DATA_TYPE := DATA_TYPE(null, null, null, null, null, null, null, null, null);
BEGIN
    LOAD_TEMP_DATA();    
    
    OPEN myCursor FOR
      select 
          create_date, case_id, division_a, division_name, case_type,
          case_stage_2, case_stage_3, case_stage_4, transaction_remark
      from TEMP_DATA;
    LOOP
      FETCH myCursor INTO
        out_rec.create_date, out_rec.case_id, out_rec.division_a,
        out_rec.division_name, out_rec.case_type, out_rec.case_stage_2,
        out_rec.case_stage_3, out_rec.case_stage_4, out_rec.transaction_remark;
      EXIT WHEN myCursor%NOTFOUND;
      PIPE ROW(out_rec);
    END LOOP;
    CLOSE myCursor;
    return;  
END;


select * from table(DATAS_FUNCTION());

评论

2 楼 w156445045 2012-06-18  

请问下博主，现在最新的iReport也不能调用存储过程嘛？
iReport 4.5.1

1 楼 how 2011-10-27  

function传入参数可以吗？