ITPub博客

首页 > 数据库 > Oracle > 常用sql书写示例1

常用sql书写示例1

原创 Oracle 作者:wzy25 时间:2019-03-12 14:27:06 0 删除 编辑
常用sql书写示例:[@more@]

赋值语句

val := 1000;

DML语句

select * from dual;

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4

1. CASE WHENIFDECODE

DECODE仅适合于离散值的判断,且不能用于赋值语句。

val := case when fee_type in (41,43) then 1

when fee_type in (42) then –1

else 0 end;

等价与

if fee_type in (41,43) then val := 1

elsif fee_type in (42) then val := -1

else val := 0

endif;

等价与

select decode(fee_type,41,1,43,1,42,-1,0) into val from dual;

2. NVLNVL2

NVL2不用用于赋值语句

val := nvl(period_prem,0);

select nvl2(b.customer_id,b.addr,a.addr)

from t_policy a,t_customer b

where a.applicant=b.customer_id(+)

3. 日期与时间

1

day date := trunc(sysdate,XXXX);

其中XXXXYYYYYEAR时表示年初;MMMONTH时表示月初;DD时表示日初;…

2

select *

from t_policy a

where a.apply_date >= to_date(2005-01-01’,‘yyyy-mm-dd)

and a.apply_date < to_date(2005-12-31’,‘yyyy-mm-dd)+1

表示取申请日为2005年的全部保单;请注意最后一天的处理。

3

val number(3) := months_between(birthday,sysdate);

取出生日至今经过的月数。

4. CONNET BY

CONNET BY按照中序遍历生成森林,START WITH指定各子树的根。

select organ_id,
level,
sys_connect_by_path(organ_id,
'/') path
from t_company_organ
where organ_id like
'101%' or organ_id='1'
start with organ_id=
'1'
connect by parent_id=prior organ_id

结果为

ORGAN_ID LEVEL PATH

1 1 /1

101 2 /1/101

1010001 3 /1/101/1010001

1010002 3 /1/101/1010002

其中,LEVEL为节点在森林中的层级,SYS_CONNECT_BY_PATH为自根节点开始的完整路径。

5. GREATESTLEAST

GREATESTLEAST从多个数值中取得最大、最小值。请注意空值的处理。

1

select greatest(1,2,3,4) val from dual

结果为

VAL

4

2

select least(1,2,3,4) val from dual
结果为

VAL

1

3

select greatest(1,2,3,4,null) val from dual

结果为

VAL

--此处为空

6. GROUPING SETS

GROUPING SETS允许一次处理多种聚类。

select grouping_id(organ_id) all_organ,
decode(grouping_id(organ_id),
1,'合计',organ_id) organ_id,
count(*) DEPT_NUM
from t_dept
where dept_cate=
1
and organ_id like
'105%'
group by grouping sets ( (organ_id),
()
)
结果为

ALL_ORGAN ORGAN_ID DEPT_NUM

0 105 211

0 10501 85

0 10502 46

0 10503 72

0 10504 79

0 10505 30

1 合计 523

GROUPING_ID可以判断该字段是否为合计。

7. SELETE … FOR UPDATE | UPDATE … RETURNING

我们认为两语句均为原子语句,执行时不会被任何语句打断。用于解决对记录的锁控。UPDATE更新数为0时,返回的值为空。

1

select p_state_id into v_stat from t_policy where policy_id=1 for update

2

update t_policy set p_state_id=53
where policy_id=
1 and p_state=14
returning p_state_id ino v_state;

8. NOWAIT

NOWAIT在遇到记录被其它事务锁定时,不等待直接异常返回。

select p_state_id from t_policy where policy_id=1 for update nowait

9. 动态执行

通过动态执行可以更灵活地处理,但是代码行为也更难控制。动态执行代码的设计必须慎重考虑。

declare
i number(
3);
v number(
12,2);
begin
execute immediate
'select 3,12.2 from dual' into i,v;
dbms_output.put_line(
'i='||i||';v='||v);
end;

结果为

i=3;v=12.2

10. 中间表

中间表与普通表的区别在于,无论COMMIT还是ROLLBACK,中间表的记录都会在事务提交后清空表中记录。

create global temporary table TM_BANKINS_POL_BENE(
REQUEST_NO NUMBER(
10),
POLICY_ID NUMBER(
10),
BBR_NO NUMBER(
4),
BBR_ID NUMBER(
10),
NAME VARCHAR2(
40),
IDTYPE NUMBER(
4),
IDNO VARCHAR2(
40),
ORDER_ NUMBER(
4),
RATIO NUMBER(
6,2),
BBR_RELA NUMBER(
4)
)
on commit delete rows;

11. XML处理

XMLTYPEXML类型,EXTRACT取得子树,EXTRACTVALUE取得属性值,XMLSEQUENCEPIPELINED技术,实现森林到树记录集的转换。

XmlElement/XmlForest/XmlAgg实现从记录到XMLTYPE的转换。

1

select extractValue(value(a),'NODE') node
from table(xmlsequence(

extract( xmltype('12'),
'/L/NODE')
)
) a

结果为

NODE

1

2

2

select ''
||pile(
''||dept_id||'')
||
'' depts
from t_dept
where dept_id in (
'20101201','20101202')

结果为

DEPTS

2010120120101202

3

select to_char(
xmlelement(d,xmlforest(dept_id as id,abbr_name name))
.getClobVal()) xml
from t_dept
where dept_id in (
'20101201','20101202')

结果为

XML

20101201广州天河区十二部一组

20101202广州天河区十二部二组

4

select to_char(xmlelement("TOP",
xmlagg(xmlelement("L",xmlforest(dept_id as id))))
.getClobVal()) xml
from t_dept
where dept_id in (
'20101201','20101202')

结果为

XML

2010120120101202

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/3618/viewspace-1032900/,如需转载,请注明出处,否则将追究法律责任。

上一篇: pl/sql 编写规范四
下一篇: 常用sql书写示例2
请登录后发表评论 登录
全部评论

注册时间:2001-12-14

  • 博文量
    1011
  • 访问量
    764661