亚洲AV日韩精品一区二区三区,亚洲国产精品无码第一区二区三区,无码专区一va亚洲v专区在线

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 on 2010-10-11 15:26 賀挺閱讀(538) 評論(0) 編輯收藏所屬分類: 數據庫

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: oracle 創建帶參視圖 oracle多列轉一行 sql2005中datetime 時間類型的格式化

heting

常用鏈接

留言簿(3)

隨筆分類

隨筆檔案

文章檔案

搜索

最新評論

閱讀排行榜

評論排行榜