2,596
社区成员
发帖
与我相关
我的任务
分享关于oracle8i的case语法的问题！希望你指点一下！谢谢
这句话在sql 语法可以通过，但是在存储过程里面不行!为什么？
select sum(case when substr(account_code,1,3) in ('101','102') then nvl (market_value,0) else 0 end),
sum(case when account_code in ('111-01','111-02') then nvl(market_value,0) else 0 end)
from value_report
where (substr(account_code,1,3) in ('101','102') or account_code in ('111-01','111-02'))
and fund_code= p_fund_code
and value_date=v_last_date1;
存储过程里面
select sum(case when substr(account_code,1,3) in ('101','102') then nvl(market_value,0) else 0 end),
sum(case when account_code in ('111-01','111-02') then nvl(market_value,0) else 0 end)
into v_bank_money,v_deal_money
from value_report
where (substr(account_code,1,3) in ('101','102') or account_code in ('111-01','111-02'))
and fund_code= p_fund_code
and value_date=v_last_date1;
select sum(case when substr(account_code,1,3) in ('101','102') then nvl (market_value,0) else 0 end),
sum(case when account_code in ('111-01','111-02') then nvl(market_value,0) else 0 end)
from value_report
where (substr(account_code,1,3) in ('101','102') or account_code in ('111-01','111-02'))
and fund_code= p_fund_code
and value_date=v_last_date1;
存储过程里面
select sum(case when substr(account_code,1,3) in ('101','102') then nvl(market_value,0) else 0 end),
sum(case when account_code in ('111-01','111-02') then nvl(market_value,0) else 0 end)
into v_bank_money,v_deal_money
from value_report
where (substr(account_code,1,3) in ('101','102') or account_code in ('111-01','111-02'))
and fund_code= p_fund_code
and value_date=v_last_date1;
...全文
请发表友善的回复…
发表回复
wwl007 2002-04-21
- 打赏
- 举报
谢谢！我仅仅对事，不对人，这段代码我已经该了，我门也是在开发一个产品，我是在个产品开发不下去的时候参与近来的，说真的，这是我见的最差的东西了，我不知道中国有的软件开发人员为什么素质这吗差？竟然把那样一天要出3-5个bug的东西拿出去说这是我们的产品，一个产品安装一次要3-5天。
好了！不说了！谢谢大家！
好了！不说了！谢谢大家！
KingSunSha 2002-04-20
- 打赏
- 举报
wwl007(疑难杂症)：
首先向你道歉，我说的确实有点过分了，特别是在自己不了解你项目的情况下。
任何产品都不可能是十全十美的，这点想来大家也能理解。oracle确实在很多方面做的不好，比如用户界面、帮助系统等。pl/sql的语法相对于t/sql来说也严格得多，很多从sql server转过来的朋友一开始都不习惯。可能我从开始就用oracle，所以这种感觉比较少一点（事实上我还挺欣赏这种严格的语法规范。)
好了，下面是我改的语句，希望能对你有所帮助，也算是我道歉的一点心意吧：
SELECT sum(decode(repurchase_flag||buy_sale||substr(stock_code,1,2),
'12YH',nvl(repurchase_money,0),
'01YH',nvl(purchase_money,0),0)),
sum(decode(repurchase_flag||buy_sale||substr(stock_code,1,2),
'11YH',-nvl(repurchase_money,0),
'02YH',-nvl(purchase_money,0),0)),
sum(decode(repurchase_flag||buy_sale||substr(stock_code,1,2),
'12CJ',nvl(repurchase_money,0),
'01CJ',nvl(purchase_money,0),0)),
sum(decode(repurchase_flag||buy_sale||substr(stock_code,1,2),
'11CJ',-nvl(repurchase_money,0),
'02CJ',-nvl(purchase_money,0),0))
FROM repurchase,storage_kind_sub
WHERE repurchase_flag||buy_sale in ('12','01','11','02')
AND substr(stock_code,1,2) in ('YH','CJ')
AND storage_kind_sub.market_code ='1'
AND repurchase_date ='20020315'
AND fund_code='001';
首先向你道歉，我说的确实有点过分了，特别是在自己不了解你项目的情况下。
任何产品都不可能是十全十美的，这点想来大家也能理解。oracle确实在很多方面做的不好，比如用户界面、帮助系统等。pl/sql的语法相对于t/sql来说也严格得多，很多从sql server转过来的朋友一开始都不习惯。可能我从开始就用oracle，所以这种感觉比较少一点（事实上我还挺欣赏这种严格的语法规范。)
好了，下面是我改的语句，希望能对你有所帮助，也算是我道歉的一点心意吧：
SELECT sum(decode(repurchase_flag||buy_sale||substr(stock_code,1,2),
'12YH',nvl(repurchase_money,0),
'01YH',nvl(purchase_money,0),0)),
sum(decode(repurchase_flag||buy_sale||substr(stock_code,1,2),
'11YH',-nvl(repurchase_money,0),
'02YH',-nvl(purchase_money,0),0)),
sum(decode(repurchase_flag||buy_sale||substr(stock_code,1,2),
'12CJ',nvl(repurchase_money,0),
'01CJ',nvl(purchase_money,0),0)),
sum(decode(repurchase_flag||buy_sale||substr(stock_code,1,2),
'11CJ',-nvl(repurchase_money,0),
'02CJ',-nvl(purchase_money,0),0))
FROM repurchase,storage_kind_sub
WHERE repurchase_flag||buy_sale in ('12','01','11','02')
AND substr(stock_code,1,2) in ('YH','CJ')
AND storage_kind_sub.market_code ='1'
AND repurchase_date ='20020315'
AND fund_code='001';
IronPromises 2002-04-19
- 打赏
- 举报
幾位大佬就不要吵了.
wwl007 2002-04-19
- 打赏
- 举报
代码长的我也见过！不是没有见过！上千行的存储过程我也写过，（当然是分多个存储过程）问题是这个问题不是很难，oracle也不好好搞搞！作为最大的产品生产商，如果不能把自己的产品做好，我感到是他们的悲哀。
我知道你是一位高手，但是我不知道为什么问这样的问题。是的，有些问题是真的不容易解决，decode语法我最多也用过八个嵌套，但是说真的，感觉很不爽，case语法多不一定就是数据库设计有问题，还有就是业务真的很复杂。还有就是为了减少代码，提高速度、还有是灵活性。我不知道兄弟如何把这些结合在一块的。我自己认为这个项目最好的就是数据库设计，最差的就是开发人员。
我知道你是一位高手，但是我不知道为什么问这样的问题。是的，有些问题是真的不容易解决，decode语法我最多也用过八个嵌套，但是说真的，感觉很不爽，case语法多不一定就是数据库设计有问题，还有就是业务真的很复杂。还有就是为了减少代码，提高速度、还有是灵活性。我不知道兄弟如何把这些结合在一块的。我自己认为这个项目最好的就是数据库设计，最差的就是开发人员。
KingSunSha 2002-04-18
- 打赏
- 举报
1、你没见过骆驼说马背肿，这句sql根本就是很普通的，你没见过几百行甚至更长的sql吧？关于decode的，下面这段sql就是一句dba常用的监控语句，比你这句的decode多了很多层嵌套：
select Min_Cached,
Count(*),
Round(AVG(Executions),2)
from (
select DECODE(Min_Cached,
0, '1) 00-01min',
1, '2) 01-02min',
2, '2) 01-02min',
DECODE(SIGN(Min_Cached-6), -1, '3) 03-05min',
DECODE(SIGN(Min_Cached-16), -1, '4) 06-15min',
DECODE(SIGN(Min_Cached-31), -1, '5) 16-30min',
DECODE(SIGN(Min_Cached-61), -1, '6) 31-60min',
DECODE(SIGN(Min_Cached-121), -1, '7) 01-02hr',
'8) 2hr+ ')))))) Min_Cached,
Executions
from (
select ROUND((SysDate - TO_DATE(First_Load_Time, 'yyyy-mm/dd/hh24:mi:ss'))*24*60) Min_Cached,
Executions
from V$SQLAREA
where Parsing_User_ID != 0
)
)
group by Min_Cached;
2、 snowy_howe(天下有雪)已经给出答案了，你不去测试却跑来喊冤，你以为你是窦娥阿
3、这么多case when说明你的数据库设计有问题，用那么多的字段标志来hardcode说明你对业务和编程都不熟悉，将来有任何改动，都将是极其讨厌的事情
select Min_Cached,
Count(*),
Round(AVG(Executions),2)
from (
select DECODE(Min_Cached,
0, '1) 00-01min',
1, '2) 01-02min',
2, '2) 01-02min',
DECODE(SIGN(Min_Cached-6), -1, '3) 03-05min',
DECODE(SIGN(Min_Cached-16), -1, '4) 06-15min',
DECODE(SIGN(Min_Cached-31), -1, '5) 16-30min',
DECODE(SIGN(Min_Cached-61), -1, '6) 31-60min',
DECODE(SIGN(Min_Cached-121), -1, '7) 01-02hr',
'8) 2hr+ ')))))) Min_Cached,
Executions
from (
select ROUND((SysDate - TO_DATE(First_Load_Time, 'yyyy-mm/dd/hh24:mi:ss'))*24*60) Min_Cached,
Executions
from V$SQLAREA
where Parsing_User_ID != 0
)
)
group by Min_Cached;
2、 snowy_howe(天下有雪)已经给出答案了，你不去测试却跑来喊冤，你以为你是窦娥阿
3、这么多case when说明你的数据库设计有问题，用那么多的字段标志来hardcode说明你对业务和编程都不熟悉，将来有任何改动，都将是极其讨厌的事情
zhuzhichao 2002-04-18
- 打赏
- 举报
兄弟,你就死一次吧.
我已經死過N次了.
你可以看看我從SQL Server的存儲過程中某代碼改到Oracle的代碼.
你會頭大三圈嗎?
SQL Server:
update t3
set subject_02 = t2.subject,
subject01_02 = t2.subject01,
subjectname_02 = t2.subjectname,
subtotal_02 = t2.subtotal,
total_02 = t2.subtotal,
rate_02 = t2.rate,
type_02 = case
when (t2.leaf = 1 or (t2.leaf = 0 and len(t2.subject) = @subjectlength)) and t2.offset = 0 then 1
when (t2.leaf = 1 or (t2.leaf = 0 and len(t2.subject) = @subjectlength)) and t2.offset = 1 then 4
when (len(t2.subject) = 3 and substring(t2.subject,3,1)='|') then 3
when (t2.leaf = 0 and len(t2.subject) < @subjectlength) and len(t2.subject) >= 3 and t2.offset = 0 then 2
when (t2.leaf = 0 and len(t2.subject) < @subjectlength) and len(t2.subject) >= 3 and t2.offset = 1 then 4
when t2.subject = @b2 or t2.subject = @b3 then 5
end
from #temp02 t2,#temp03 t3
where t2.id = t3.id
Oracle:
update temp_balance03 t3
set (subject_02,subject01_02,subjectname_02,subtotal_02,total_02,rate_02,type_02)
= (select t2.subject,t2.subject01,t2.subjectname,t2.subtotal,t2.subtotal,t2.rate,
decode(t2.subject,v_b2,5,v_b3,5,
decode(sign(length(t2.subject)-3),0,decode(substr(t2.subject,3,1),'|',3),
decode(t2.leaf,1,decode(t2.offset,0,1,1,4)
,0
,decode(sign(length(t2.subject)-i_subjectlength),
0,decode(t2.offset,0,1,1,4),-1,
decode(t2.leaf,0,
decode(sign(length(t2.subject)-3),1,decode(t2.offset,0,2,1,4),0,decode(t2.offset,0,2,1,4)))))
from temp_balance02 t2 where t2.id = t3.id and t2.sessionid = t3.sessionid)
where t3.sessionid = i_sessionid;
我已經死過N次了.
你可以看看我從SQL Server的存儲過程中某代碼改到Oracle的代碼.
你會頭大三圈嗎?
SQL Server:
update t3
set subject_02 = t2.subject,
subject01_02 = t2.subject01,
subjectname_02 = t2.subjectname,
subtotal_02 = t2.subtotal,
total_02 = t2.subtotal,
rate_02 = t2.rate,
type_02 = case
when (t2.leaf = 1 or (t2.leaf = 0 and len(t2.subject) = @subjectlength)) and t2.offset = 0 then 1
when (t2.leaf = 1 or (t2.leaf = 0 and len(t2.subject) = @subjectlength)) and t2.offset = 1 then 4
when (len(t2.subject) = 3 and substring(t2.subject,3,1)='|') then 3
when (t2.leaf = 0 and len(t2.subject) < @subjectlength) and len(t2.subject) >= 3 and t2.offset = 0 then 2
when (t2.leaf = 0 and len(t2.subject) < @subjectlength) and len(t2.subject) >= 3 and t2.offset = 1 then 4
when t2.subject = @b2 or t2.subject = @b3 then 5
end
from #temp02 t2,#temp03 t3
where t2.id = t3.id
Oracle:
update temp_balance03 t3
set (subject_02,subject01_02,subjectname_02,subtotal_02,total_02,rate_02,type_02)
= (select t2.subject,t2.subject01,t2.subjectname,t2.subtotal,t2.subtotal,t2.rate,
decode(t2.subject,v_b2,5,v_b3,5,
decode(sign(length(t2.subject)-3),0,decode(substr(t2.subject,3,1),'|',3),
decode(t2.leaf,1,decode(t2.offset,0,1,1,4)
,0
,decode(sign(length(t2.subject)-i_subjectlength),
0,decode(t2.offset,0,1,1,4),-1,
decode(t2.leaf,0,
decode(sign(length(t2.subject)-3),1,decode(t2.offset,0,2,1,4),0,decode(t2.offset,0,2,1,4)))))
from temp_balance02 t2 where t2.id = t3.id and t2.sessionid = t3.sessionid)
where t3.sessionid = i_sessionid;
wwl007 2002-04-18
- 打赏
- 举报
SELECT SUM(case when (repurchase_flag = '1' and buy_sale = '2' and SUBSTR(stock_code,1,2)='YH') then nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='1' and SUBSTR(stock_code,1,2)='YH') then nvl(purchase_money,0) else 0 end), --回购投资流入
SUM(case when (repurchase_flag = '1' and buy_sale = '1' and SUBSTR(stock_code,1,2)='YH') then 0-nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='2' and SUBSTR(stock_code,1,2)='YH') then 0-nvl(purchase_money,0) else 0 end), --回购投资流出
SUM(case when (repurchase_flag = '1' and buy_sale = '2' and SUBSTR(stock_code,1,2)='CJ') then nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='1' and SUBSTR(stock_code,1,2)='CJ') then nvl(purchase_money,0) else 0 end), --投资流出
SUM(case when (repurchase_flag = '1' and buy_sale = '1' and SUBSTR(stock_code,1,2)='CJ') then 0-nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='2' and SUBSTR(stock_code,1,2)='CJ') then 0-nvl(purchase_money,0) else 0 end) --资金流入
FROM repurchase,storage_kind_sub
WHERE ((repurchase_flag='1' and buy_sale='2') or (repurchase_flag='0' and buy_sale='1')
or (repurchase_flag='1' and buy_sale='1') or (repurchase_flag='0' AND buy_sale = '2'))
AND storage_kind_sub.market_code ='1'
AND (SUBSTR(stock_code,1,2) in ('YH','CJ'))
AND repurchase_date ='20020315'
AND fund_code='001';
这个语法该成decode 我看我不死掉，你也差不多了！你说对吗?兄弟们！
SUM(case when (repurchase_flag = '1' and buy_sale = '1' and SUBSTR(stock_code,1,2)='YH') then 0-nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='2' and SUBSTR(stock_code,1,2)='YH') then 0-nvl(purchase_money,0) else 0 end), --回购投资流出
SUM(case when (repurchase_flag = '1' and buy_sale = '2' and SUBSTR(stock_code,1,2)='CJ') then nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='1' and SUBSTR(stock_code,1,2)='CJ') then nvl(purchase_money,0) else 0 end), --投资流出
SUM(case when (repurchase_flag = '1' and buy_sale = '1' and SUBSTR(stock_code,1,2)='CJ') then 0-nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='2' and SUBSTR(stock_code,1,2)='CJ') then 0-nvl(purchase_money,0) else 0 end) --资金流入
FROM repurchase,storage_kind_sub
WHERE ((repurchase_flag='1' and buy_sale='2') or (repurchase_flag='0' and buy_sale='1')
or (repurchase_flag='1' and buy_sale='1') or (repurchase_flag='0' AND buy_sale = '2'))
AND storage_kind_sub.market_code ='1'
AND (SUBSTR(stock_code,1,2) in ('YH','CJ'))
AND repurchase_date ='20020315'
AND fund_code='001';
这个语法该成decode 我看我不死掉，你也差不多了！你说对吗?兄弟们！
wwl007 2002-04-16
- 打赏
- 举报
我已经说了。我的语法是可以的，单独写sql语法可以，但是在存储过程里面就不行了，没有什么典型的错误提示！ora-06550
ykliu1 2002-04-16
- 打赏
- 举报
case 语句好象只在8.1.6以后才能用，能不能把错误代码贴出来
wwl007 2002-04-16
- 打赏
- 举报
SELECT SUM(case when (repurchase_flag = '1' and buy_sale = '2' and SUBSTR(stock_code,1,2)='YH') then nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='1' and SUBSTR(stock_code,1,2)='YH') then nvl(purchase_money,0) else 0 end), --回购投资流入
SUM(case when (repurchase_flag = '1' and buy_sale = '1' and SUBSTR(stock_code,1,2)='YH') then 0-nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='2' and SUBSTR(stock_code,1,2)='YH') then 0-nvl(purchase_money,0) else 0 end), --回购投资流出
SUM(case when (repurchase_flag = '1' and buy_sale = '2' and SUBSTR(stock_code,1,2)='CJ') then nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='1' and SUBSTR(stock_code,1,2)='CJ') then nvl(purchase_money,0) else 0 end), --投资流出
SUM(case when (repurchase_flag = '1' and buy_sale = '1' and SUBSTR(stock_code,1,2)='CJ') then 0-nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='2' and SUBSTR(stock_code,1,2)='CJ') then 0-nvl(purchase_money,0) else 0 end) --资金流入
FROM repurchase,storage_kind_sub
WHERE ((repurchase_flag='1' and buy_sale='2') or (repurchase_flag='0' and buy_sale='1')
or (repurchase_flag='1' and buy_sale='1') or (repurchase_flag='0' AND buy_sale = '2'))
AND storage_kind_sub.market_code ='1'
AND (SUBSTR(stock_code,1,2) in ('YH','CJ'))
AND repurchase_date ='20020315'
AND fund_code='001';
这个语法用decode完成！如何完成啊？
SUM(case when (repurchase_flag = '1' and buy_sale = '1' and SUBSTR(stock_code,1,2)='YH') then 0-nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='2' and SUBSTR(stock_code,1,2)='YH') then 0-nvl(purchase_money,0) else 0 end), --回购投资流出
SUM(case when (repurchase_flag = '1' and buy_sale = '2' and SUBSTR(stock_code,1,2)='CJ') then nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='1' and SUBSTR(stock_code,1,2)='CJ') then nvl(purchase_money,0) else 0 end), --投资流出
SUM(case when (repurchase_flag = '1' and buy_sale = '1' and SUBSTR(stock_code,1,2)='CJ') then 0-nvl(repurchase_money,0) when (repurchase_flag = '0' and buy_sale='2' and SUBSTR(stock_code,1,2)='CJ') then 0-nvl(purchase_money,0) else 0 end) --资金流入
FROM repurchase,storage_kind_sub
WHERE ((repurchase_flag='1' and buy_sale='2') or (repurchase_flag='0' and buy_sale='1')
or (repurchase_flag='1' and buy_sale='1') or (repurchase_flag='0' AND buy_sale = '2'))
AND storage_kind_sub.market_code ='1'
AND (SUBSTR(stock_code,1,2) in ('YH','CJ'))
AND repurchase_date ='20020315'
AND fund_code='001';
这个语法用decode完成！如何完成啊？
wwl007 2002-04-16
- 打赏
- 举报
我认为decode的时代应该退场了！你说那？decode能有 in这个功能吗？
没有，要用很多decode连接
没有，要用很多decode连接
wwl007 2002-04-16
- 打赏
- 举报
我认为decode的时代应该退场了！你说那？decode能有 in这个功能吗？
没有，要用很多decode连接
没有，要用很多decode连接
dgj 2002-04-16
- 打赏
- 举报
你可以试试decode
you_guess 2002-04-16
- 打赏
- 举报
改成视图
KingSunSha 2002-04-16
- 打赏
- 举报
select sum(decode(repurchase_flag||bu_sale||substr(stock_code,1,2),'12YH',nvl(repurchase_money,0),'01YH',nvl(purchase_money,0))
...
够了吗？
...
够了吗？
hanps 2002-04-16
- 打赏
- 举报
存儲過程中不能有case語句出現,換成decode
snowy_howe 2002-04-16
- 打赏
- 举报
SELECT SUM(decode(repurchase_flag||buy_sale||SUBSTR(stock_code,1,2),'12YH',nvl(repurchase_money,0),'01YH',nvl(purchase_money,0),0)), --回购投资流入
FROM repurchase,storage_kind_sub
WHERE ((repurchase_flag='1' and buy_sale='2') or (repurchase_flag='0' and buy_sale='1')
or (repurchase_flag='1' and buy_sale='1') or (repurchase_flag='0' AND buy_sale = '2'))
AND storage_kind_sub.market_code ='1'
AND (SUBSTR(stock_code,1,2) in ('YH','CJ'))
AND repurchase_date ='20020315'
AND fund_code='001';
这应该可以实现吧，我觉得！
讨论一下，呵呵
FROM repurchase,storage_kind_sub
WHERE ((repurchase_flag='1' and buy_sale='2') or (repurchase_flag='0' and buy_sale='1')
or (repurchase_flag='1' and buy_sale='1') or (repurchase_flag='0' AND buy_sale = '2'))
AND storage_kind_sub.market_code ='1'
AND (SUBSTR(stock_code,1,2) in ('YH','CJ'))
AND repurchase_date ='20020315'
AND fund_code='001';
这应该可以实现吧，我觉得！
讨论一下，呵呵
zhuzhichao 2002-04-16
- 打赏
- 举报
只有用decode吧,兄弟.
我已經試過了.
Oracle8.17
存儲過程中不能有case語句出現,換成decode就可以了.
Oracle的bug真多!
我已經試過了.
Oracle8.17
存儲過程中不能有case語句出現,換成decode就可以了.
Oracle的bug真多!
wwl007 2002-04-16
- 打赏
- 举报
关键是不是可以这样用！兄弟你实验一下！也写个oracel的case 语法在存储过程里面！
mycode 2002-04-16
- 打赏
- 举报
单看是没有什么问题.
能把表结构和创建的存储过程都全帖出来看一看吗?
也许有别的地方影响.
能把表结构和创建的存储过程都全帖出来看一看吗?
也许有别的地方影响.
加载更多回复（1）
