常用sql书写示例1

最新推荐文章于 2022-09-09 16:26:29 发布

congdi7904

最新推荐文章于 2022-09-09 16:26:29 发布

阅读量127

点赞数

文章标签：数据结构与算法

常用sql书写示例：[@more@]

赋值语句

val := 1000;

DML语句

select * from dual;

Normal07.8 磅02falsefalsefalseMicrosoftInternetExplorer4

1. CASE WHEN、IF、DECODE

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. NVL、NVL2

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’);

其中XXXX为YYYY或YEAR时表示年初;为MM或MONTH时表示月初;为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);

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

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. GREATEST、LEAST

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

例1、

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

结果为

VAL

例2、

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

VAL

例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处理

XMLTYPE为XML类型，EXTRACT取得子树，EXTRACTVALUE取得属性值，XMLSEQUENCE为PIPELINED技术，实现森林到树记录集的转换。

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

例1、

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

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

结果为

NODE

例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')