Store Procedure Crystal Report

Hari ini ngetes store procedure yang dipakai crystal report.

Ada tambahan temp table nya sih....




/* temporary table */

CREATE GLOBAL TEMPORARY TABLE "TMP_AREA_TABLE"

( "LOCGRP_CODE" VARCHAR2(6 BYTE),

"LOCGRP_NAME" VARCHAR2(30 BYTE),

"LOC_CODE" VARCHAR2(6 BYTE),

"LOC_NAME" VARCHAR2(30 BYTE),

"DEPO" VARCHAR2(1 BYTE)

) ON COMMIT PRESERVE ROWS ;










/* report package */

create or replace PACKAGE Report_Package

AS TYPE Report_Type IS REF CURSOR;

TYPE Report_Type2 IS REF CURSOR RETURN TMP_AREA_TABLE%ROWTYPE;

END Report_Package;











/* Store Procedure Report */

create or replace PROCEDURE P_RPT_AREAL1_TMP

(Report_Cursor IN OUT Report_Package.Report_Type2,

pUserType VARCHAR2, pLocCode VARCHAR2, pLocGrpCode VARCHAR2) AS

TEMP_ROW TMP_AREA_TABLE%ROWTYPE;




CURSOR AREA_CURSOR IS

SELECT LcGrps.LOCGRP_CODE, LcGrps.LOCGRP_NAME, Locs.LOC_CODE, Locs.LOC_NAME, Locs.DEPO

FROM LOCATIONS Locs INNER JOIN LOCATION_GROUPS LcGrps ON Locs.LOCGRP_CODE=LcGrps.LOCGRP_CODE;

BEGIN

set_vpd_context.set_akses_user(pUserType, pLocCode, pLocGrpCode);

delete from TMP_AREA_TABLE;

OPEN AREA_CURSOR();

LOOP

FETCH AREA_CURSOR INTO TEMP_ROW;

EXIT WHEN AREA_CURSOR%NOTFOUND;

INSERT INTO TMP_AREA_TABLE(LOCGRP_CODE, LOCGRP_NAME, LOC_CODE, LOC_NAME, DEPO)

VALUES(TEMP_ROW.LOCGRP_CODE, TEMP_ROW.LOCGRP_NAME, TEMP_ROW.LOC_CODE,

TEMP_ROW.LOC_NAME, TEMP_ROW.DEPO);

END LOOP;

OPEN Report_Cursor FOR

SELECT LOCGRP_CODE, LOCGRP_NAME, LOC_CODE, LOC_NAME, DEPO FROM TMP_AREA_TABLE;




END;




Sebenernya ini bukan cara yg bener, cm lumayan lah drpd menggunakan table fisik.

Untuk kedepannya ngilangin cursor nya.

maklum newbie