久久久久久亚洲精品无码,亚洲乱码日产精品a级毛片久久,亚洲一区二区三区久久

2010年10月11日 #

oracle 創建帶參視圖

create or replace package CARD_SERVICE_DAY_VIEW_PARAM is

function set_param(num varchar) return varchar;

function get_param return varchar;

end CARD_SERVICE_DAY_VIEW_PARAM;

commit;

create or replace package body CARD_SERVICE_DAY_VIEW_PARAM is

paramValue varchar(10);

function set_param(num varchar) return varchar is

begin

paramValue:=num;

return num;

end;

function get_param return varchar is

begin

return paramValue;

end;

end CARD_SERVICE_DAY_VIEW_PARAM;

commit;

create or replace view CARD_SERVICE_DAY_VIEW as

select

cs.id,

cs.card_id,

cs.lock_money,

to_char(cs.check_time,'yyyy-MM-dd hh24:mi') as check_time,

cs.name,cs.phone,( months_between(to_date(CARD_SERVICE_DAY_VIEW_PARAM.get_param(),'yyyyMMdd'),

to_date(to_char(cs.CHECK_TIME,'yyyyMMdd'),'yyyyMMdd')) ) as curr_batch,

(select count(1) from transaction where card_id=cs.card_id and tran_time between cs.check_time and to_date(CARD_SERVICE_DAY_VIEW_PARAM.get_param(),'yyyyMMdd') and (curr_bal-money)<cs.lock_money) as condition,

(select count(1) from service_transaction where batch=(months_between(to_date(CARD_SERVICE_DAY_VIEW_PARAM.get_param(),'yyyyMMdd'),to_date(to_char(cs.CHECK_TIME,'yyyyMMdd'),'yyyyMMdd'))) and s_id=cs.id) as hasAudit

from card_service cs

where cs.state='T'

and

months_between(to_date(CARD_SERVICE_DAY_VIEW_PARAM.get_param(),'yyyyMMdd'),to_date(to_char(cs.CHECK_TIME,'yyyyMMdd'),'yyyyMMdd')) in (1,2,3,4,5,6)

order by cs.card_id

commit;

posted @ 2010-10-11 15:26 賀挺閱讀(540) | 評論 (0) | 編輯收藏

heting

常用鏈接

留言簿(3)

隨筆分類

隨筆檔案

文章檔案

搜索

最新評論

閱讀排行榜

評論排行榜