Background is two tables, one is called TG_CDR10, one is called TG_CDR10_GS, this is this package creation,
The create or replace package sp_package is
TYPE DATE_RECORD IS RECORD - custom types - line (including field and TYPE)
(
USER_ID number (16, 0),

Count_times1 NUMBER (10, 0),
Count_call_duration NUMBER (6, 0),
Count_data_dawn1 NUMBER (36, 0),
Count_data_up1 NUMBER (36, 0)
);

TYPE DATE_TABLE IS TABLE OF DATE_RECORD;
The function tests (par VARCHAR2, con VARCHAR2) return DATE_TABLE PIPELINED.
The function sum_data_up1 return SYS_REFCURSOR;


End sp_package;



This section is to create the package entity
The create or replace
Package body sp_package is
- function 1
The function sum_data_up1
Return SYS_REFCURSOR
Is
Annual_salary SYS_REFCURSOR;
The BEGIN
The OPEN annual_salary FOR
Select a TCG. User_id as user_id, (select sum (CALL_DURATION) from tg_cdr10 tc where tc user_id=TCG. User_id group by tc user_id) as CALL_DURATION from tg_cdr10_gs TCG.
RETURN annual_salary;
END;
2 - function
The function tests (par VARCHAR2, con VARCHAR2)
RETURN DATE_TABLE
PIPELINED
Is
L_RESULT DATE_RECORD;
Pp varchar2 (100);
The begin
Pp:='and' | | par | | '=' | | con;
If par='CALL_TYPE' then - parameter is the type of call
FOR REC IN (
With tempName as (
Select a TCG DATA_DOWN1, TCG DATA_UP1, TCG. User_id as user_id,
(select sum (CALL_DURATION) from tg_cdr10 tc where tc user_id=TCG. User_id and CALL_TYPE=con group by tc user_id) as CALL_DURATION,
(select sum (TIMES1) from tg_cdr10 tc where tc user_id=TCG. User_id and CALL_TYPE=congroup by tc user_id) as TIMES1
The from tg_cdr10_gs TCG)
The select t.u ser_id, sum (t.t imes1) as count_times1
, the sum (tc all_duration) as count_call_duration
, the sum (t.d ata_down1) as count_data_dawn1
, the sum (t.d ata_up1) as count_data_up1
The from tempName t group by t.u ser_id)
LOOP
L_RESULT. USER_ID:=REC. USER_ID;
L_RESULT. Count_call_duration:=REC. Count_call_duration;
L_RESULT. Count_times1:=REC. Count_times1;
L_RESULT. Count_data_dawn1:=REC. Count_data_dawn1;
L_RESULT. Count_data_up1:=REC. Count_data_up1;
PIPE ROW (L_RESULT); - return line in turn
END LOOP;
end if;

If par='LONG_TYPE1' then - parameter is long type
FOR REC IN (
With tempName as (
Select a TCG DATA_DOWN1, TCG DATA_UP1, TCG. User_id as user_id,
(select sum (CALL_DURATION) from tg_cdr10 tc where tc user_id=TCG. User_id and LONG_TYPE1=con group by tc user_id) as CALL_DURATION,
(select sum (TIMES1) from tg_cdr10 tc where tc user_id=TCG. User_idand LONG_TYPE1=con group by tc user_id) as TIMES1
The from tg_cdr10_gs TCG)
The select t.u ser_id, sum (t.t imes1) as count_times1
, the sum (tc all_duration) as count_call_duration
, the sum (t.d ata_down1) as count_data_dawn1
, the sum (t.d ata_up1) as count_data_up1
The from tempName t group by t.u ser_id)
LOOP
L_RESULT. USER_ID:=REC. USER_ID;
L_RESULT. Count_call_duration:=REC. Count_call_duration;
L_RESULT. Count_times1:=REC. Count_times1;
L_RESULT. Count_data_dawn1:=REC. Count_data_dawn1;
L_RESULT. Count_data_up1:=REC. Count_data_up1;
PIPE ROW (L_RESULT); - return line in turn
END LOOP;
end if;


If par='ROAM_TYPE' then - type parameter is roaming
FOR REC IN (
With tempName as (
Select a TCG DATA_DOWN1, TCG DATA_UP1, TCG. User_id as user_id,
(select sum (CALL_DURATION) from tg_cdr10 tc where tc user_id=TCG. User_id and ROAM_TYPE=con group by tc user_id) as CALL_DURATION,
(select sum (TIMES1) from tg_cdr10 tc where tc user_id=TCG. User_idandROAM_TYPE=con group by tc user_id) as TIMES1
The from tg_cdr10_gs TCG)
The select t.u ser_id, sum (t.t imes1) as count_times1
, the sum (tc all_duration) as count_call_duration
, the sum (t.d ata_down1) as count_data_dawn1
, the sum (t.d ata_up1) as count_data_up1
The from tempName t group by t.u ser_id)
LOOP
L_RESULT. USER_ID:=REC. USER_ID;
L_RESULT. Count_call_duration:=REC. Count_call_duration;
L_RESULT. Count_times1:=REC. Count_times1;
L_RESULT. Count_data_dawn1:=REC. Count_data_dawn1;
L_RESULT. Count_data_up1:=REC. Count_data_up1;
PIPE ROW (L_RESULT); - return line in turn
END LOOP;
end if;


END;




The end;




I want to ask, is FOR REC IN this statement what is specific meaning, and REC is refers to the cursor or? Then the TCG. DATA_DOWN1 like that, and the inside of the loop cycle what those things, the original table data is not, there is no creation process, why can direct call? Key I throw into PL/SQL developer is not an error, that is, is right, and this is why,,, ask god to help,

CodePudding user response:

FOR REC IN is cursor, TCG DATA_DOWN1 is tg_cdr10_gs table column, use SQL to query the cursor is inside the loop, there is no data inside the SQL is not an error

CodePudding user response:

1 () function will
Select a TCG. User_id as user_id, (select sum (CALL_DURATION) from tg_cdr10 tc where tc user_id=TCG. User_id group by tc user_id) as CALL_DURATION from tg_cdr10_gs TCG. The SQL query result set is assigned to annual_salary and return annual_salary (annual_salary SYS_REFCURSOR; Here stated annual_salary type is SYS_REFCURSOR)

Function 2
To determine parameters of par value choose to perform a different SQL
And will perform the SQL query result set assigned to cursor REC
Then put the cursor in the REC values were assigned to the corresponding column in the L_RESULT
nullnullnullnullnullnullnullnullnull