背景是有两个表,一个叫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里面是不报错也就是是对的。。这是为什么。。求大神来帮帮忙。。
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里面是不报错也就是是对的。。这是为什么。。求大神来帮帮忙。。
解决方案 »
- 关于Ibatis中Iterator的问题
- 请教一个rollup的问题
- Oracle
- 异常处理问题
- 小问题!SQL的执行慢,应该算是执行计划中的CPU时间,还是经过时间?
- 查询一个表的一条记录
- 大家来看看我这个存储过程合理吗?
- ASP中的调用“$exp.....”?
- 在Oracle中用varchar2定义的字符串在输出中文时为什么是乱码?
- 在ORACLE里,我要比较两个时间之间的差怎么做?????????????????????????
- 关于数据逻辑删除方式的讨论
- 未找到 Oracle 客户端和网络组件。这些组件是由 Oracle 公司提供的,是 Oracle 7.3.3 版(或更高)客户软件安装的一部分。
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; 这条SQL的查询结果集赋给annual_salary 并返回annual_salary (annual_salary SYS_REFCURSOR; 这里申明annual_salary 的类型是SYS_REFCURSOR )函数2
先判断参数par 的值选择执行不同的SQL
并将执行SQL的查询结果集赋值给游标REC
然后将游标REC中的值分别赋值给L_RESULT 中对应的列
最后返回DATE_TABLE
( L_RESULT DATE_RECORD; 表示L_RESULT 是DATE_RECORD类型
而TYPE DATE_TABLE IS TABLE OF DATE_RECORD; 表示DATE_TABLE 与DATE_RECORD类型相同 )loop中 结合RETURN DATE_TABLE 与 L_RESULT DATE_RECORD; 以及TYPE DATE_TABLE IS TABLE OF DATE_RECORD;
这个地方即可知道 返回值类型是DATE_TABLE 而 DATE_TABLE 与DATE_RECORD类型相同
L_RESULT.USER_ID := REC.USER_ID ; 是将游标REC中的值赋值给 L_RESULT 就将游标REC中的值转为DATE_TABLE的值返回 这个是函数 表中没有数据 返回的DATE_TABLE中值就是空 只要查询语句中的表是存在的有没有数据是不影响的 也就不会报错