ireport oracle,用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());