背景是有两个表，一个叫TG_CDR10,一个叫TG_CDR10_GS,这是创建包
create or replace package sp_package is
TYPE DATE_RECORD IS RECORD --自定义类型-行（含字段及类型）
(
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;
function tests(par VARCHAR2,con VARCHAR2) return DATE_TABLE PIPELINED;
function sum_data_up1 return SYS_REFCURSOR;
end sp_package;
这是创建包实体
create or replace
package body sp_package is
--函数1
function sum_data_up1
return SYS_REFCURSOR
is
annual_salary SYS_REFCURSOR;
BEGIN
OPEN annual_salary FOR
select 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 tests(par VARCHAR2,con VARCHAR2)
RETURN DATE_TABLE
PIPELINED
is
L_RESULT DATE_RECORD;
pp varchar2(100);
begin
pp:= ' and '|| par||'='||con;
if par='CALL_TYPE' then --参数是呼叫类型
FOR REC IN (
with tempName as (
select 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=con　group by tc.user_id) as TIMES1
from tg_cdr10_gs tcg)
select t.user_id,sum(t.times1)as count_times1
,sum(t.call_duration) as count_call_duration
,sum(t.data_down1) as count_data_dawn1
,sum(t.data_up1) as count_data_up1
from tempName t group by t.user_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); --依次返回行
END LOOP;
end if;
if par='LONG_TYPE1' then --参数是长途类型
FOR REC IN (
with tempName as (
select 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_id　and LONG_TYPE1=con group by tc.user_id) as TIMES1
from tg_cdr10_gs tcg)
select t.user_id,sum(t.times1)as count_times1
,sum(t.call_duration) as count_call_duration
,sum(t.data_down1) as count_data_dawn1
,sum(t.data_up1) as count_data_up1
from tempName t group by t.user_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); --依次返回行
END LOOP;
end if;
if par='ROAM_TYPE' then --参数是漫游类型
FOR REC IN (
with tempName as (
select 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_id　and　ROAM_TYPE=con group by tc.user_id) as TIMES1
from tg_cdr10_gs tcg)
select t.user_id,sum(t.times1)as count_times1
,sum(t.call_duration) as count_call_duration
,sum(t.data_down1) as count_data_dawn1
,sum(t.data_up1) as count_data_up1
from tempName t group by t.user_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); --依次返回行
END LOOP;
end if;
END;
end;
我想问的是，FOR REC IN这个语句具体含义是什么。。rec是指游标还是？然后 tcg.DATA_DOWN1这样的，还有loop里面的循环那些事什么东西，原表数据没有，里面也没有创建的过程，为什么能直接调用？关键我扔到PL/SQL developer里面是不报错也就是是对的。。这是为什么。。求大神来帮帮忙。。