另一个有关的有用方法
declare
i integer
select nvl(sum(t.dwxhl),1) into i from tb_jhde t where zydm=-1这样就可以把获得的合计值存储到变量i中，如果查询的值为null就把它的值设置为默认的1

oracle的substr函数的用法
 取得字符串中指定起始位置和长度的字符串   substr( string, start_position, [ length ] )
 如:
     substr('This is a test', 6, 2)     would return 'is'
     substr('This is a test', 6)     would return 'is a test'
     substr('TechOnTheNet', -3, 3)     would return 'Net'
     substr('TechOnTheNet', -6, 3)     would return 'The'

select substr('Thisisatest', -4, 2) value from dual

decode(字段,'a','男','b','女','c','未知')from 表

decode(sign(字段-60),-1,'及格'，‘不及格’)from 表

select * from stu;
 
                                     ID NAME                                              CJ
--------------------------------------- ------------ ---------------------------------------
                                      1 JIM                                               88
                                      2 jom                                               75
                                      3 tom                                               55
                                      4 tt                                                59
                                      5 gg                                                49
                                      6 ff                                                64
                                      7 mm                                                78
                                      8 gf                                                98
                                      9 高金涛      
                                     10 高金涛      
 
10 rows selected

 select id,name ,decode(sign(cj-60),-1,'通过','未通过')as 考核 from stu;
 
                                     ID NAME         考核
--------------------------------------- ------------ ------
                                      1 JIM          未通过
                                      2 jom          未通过
                                      3 tom          通过
                                      4 tt           通过
                                      5 gg           通过
                                      6 ff           未通过
                                      7 mm           未通过
                                      8 gf           未通过
                                      9 高金涛       未通过
                                     10 高金涛       未通过

 select id,name,(case when cj<60 then '不及格' when cj<75 then '及格' when cj<85 then '良好' else '优秀' end) from stu;
 
                                     ID NAME         (CASEWHENCJ<60THEN'不及格'WHEN
--------------------------------------- ------------ ------------------------------
                                      1 JIM          优秀
                                      2 jom          良好
                                      3 tom          不及格
                                      4 tt           不及格
                                      5 gg           不及格
                                      6 ff           及格
                                      7 mm           良好
                                      8 gf           优秀
                                      9 高金涛       优秀
                                     10 高金涛       优秀

select * from work;
 
                                     ID NAME         SEX
--------------------------------------- ------------ ----------
                                      1 王五         男
                                      2 李四         男
                                      3 小芳         女
                                      4 东方不败     不男不女
 
SQL> select id,name,decode(sex,'男','man','女','woman','不男不女','woman and man')from work;
 
                                     ID NAME         DECODE(SEX,'男','MAN','女','WO
--------------------------------------- ------------ ------------------------------
                                      1 王五         man
                                      2 李四         man
                                      3 小芳         woman
                                      4 东方不败     woman and man