当前位置: 动力学知识库 > 问答 > 编程问答 >

oracle - need to update records with crystal report

问题描述:

i have a crystal report that returns the records i need based on criteria i added in the report. I also want this report to update records in the data base when it is run. to do this i created the procedure (in oracle) below. How do i get this to run? I really only need the part that updates records to run in the procedure. Is this possible? meaning can i run the procedure from the same report that i would build as i normally do (using select expert)? I kind of sandwich the update statment between a statment that returns records as my research indicates that i can't just run the update portion from crystal. when i try using the procedure below crystal throws an invalid argument provided error.

 create or replace Procedure "P_UPDATE_REPORT_DATE_2" (

N_BEGIN IN date,

N_END IN date)

is

CUS_ID NUMBER;

cursor c1 is select customer_id FROM customer;

begin

--real begining

update dbo.customer set REPORT_DATE= sysdate

where customer.customer_id in

(SELECT customer.custoemr_id

FROM dbo.CUS_ADDRESS CUS_ADDRESS INNER JOIN (((dbo.CUS_ASSOC_V

CUS_ASSOC_V INNER JOIN dbo.customer customer ON

CUS_ASSOC_V.ASSOC_ID=customer.ASSOC_ID) INNER JOIN dbo.ITEM ITEM ON

CUSTOMER.ITEM_NUMBER=ITEM.ITEM_NUMBER) LEFT OUTER JOIN

dbo.PAY_TYPE_CODE PAY_TYPE_CODE ON

CUSTOMER.PRIM_PAY=PAY_TYPE_CODE.CODE) ON

ITEM_ADDRESS.ADDRESS_ID=ITEM.ITEM_ADDRESS_ID

WHERE CUSTOMER.FACILITY='CHI'

AND CUSTOMER.START_DATE<= N_END

AND CUSTOMER.END_DATE>= N_BEGIN

AND CUSTOMER.CUSTOMER_TYPE_CODE='O');

commit;

--fake to create sandwich

OPEN c1;

LOOP

FETCH c1 INTO CUS_ID;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(CUS_ID);

END LOOP;

end "P_UPDATE_REPORT_DATE_2";

网友答案:

Create a function with an autonomous transaction:

create or replace function my_function return number is
    pragma autonomous_transaction;
begin
    --Perform DML here
    insert into my_table values(1);

    --You must commit or rollback within an autonomous transaction
    commit;

    --Return any dummy value
    return 1;
end;
/

Then call it with SQL like this:

select my_function from dual;

Performing DML as part of a SELECT statement is generally a horrible thing you should try to avoid. SQL does not make any guarantees about how things will be executed. And Crystal Reports does not always execute every query in the report. Be very careful and test thoroughly. Or even better, find some other way to accomplish this.

分享给朋友:
您可能感兴趣的文章:
随机阅读: