背景是有兩個表，一個叫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里面是不報錯也就是是對的。。這是為什么。。求大神來幫幫忙。。
uj5u.com熱心網友回復：
FOR REC IN 是游標, tcg.DATA_DOWN1 是tg_cdr10_gs 表的欄位,loop里面是查詢sql給游標使用，這個sql里面沒有數據也不會報錯的uj5u.com熱心網友回復：
函式1是將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中值就是空 只要查詢陳述句中的表是存在的有沒有資料是不影響的 也就不會報錯
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/80587.html
標籤：開發