1:查询 “+” 的作用,关联表可以为空。
select
bp.pur_id,
sum(nvl(bpd.pur_price, 0) * nvl(bpd.order_num, 0)) coin
from
tb_purchasing bp,
tb_purchasing_detail bpd
where
bp.pur_id = bpd.pur_id(+)
group by bp.pur_id
2: decode判断是否为空,若为空boollog为0,否则为1
1)
select decode(param.param_name,null,0,1) boollog
from bas_param param
where param.param_type='0003'
2)
select decode(count(0),0,0,1) from BAS_RESOURCE where RES_PID = 1 有没有孩子的判断方法
3: to_number(“字符串”)转换为整型数字
select to_number(param.param_no)
from bas_param param
where param.param_type='0003'
4: to_char(整型)转换为字符串
select to_char(param.param_id)
from bas_param param
where param.param_type='0003'
5: nvl ("字符串", 0) 计算字符串的整型数值
sum(nvl(bpd.pur_price, 0) * nvl(bpd.order_num, 0)) coin
6: 利用序列生产分拣单号
select seq_pack_id.nextval from dual
7: 查询用户的角色
1)
用户1已有的角色
select
r.role_id , r.role_name
from
bas_role r,
bas_user_roles t
where
r.role_id = t.role_id and
t.user_id = 1
2)
用户1未有的角色
select r.role_id , r.role_name
from bas_role r,
(
select ur.role_id
from bas_user_roles ur
where ur.user_id = 1
) t
where
r.role_id = t.role_id(+) and
t.role_id is null
select
bp.pur_id,
sum(nvl(bpd.pur_price, 0) * nvl(bpd.order_num, 0)) coin
from
tb_purchasing bp,
tb_purchasing_detail bpd
where
bp.pur_id = bpd.pur_id(+)
group by bp.pur_id
2: decode判断是否为空,若为空boollog为0,否则为1
1)
select decode(param.param_name,null,0,1) boollog
from bas_param param
where param.param_type='0003'
2)
select decode(count(0),0,0,1) from BAS_RESOURCE where RES_PID = 1 有没有孩子的判断方法
3: to_number(“字符串”)转换为整型数字
select to_number(param.param_no)
from bas_param param
where param.param_type='0003'
4: to_char(整型)转换为字符串
select to_char(param.param_id)
from bas_param param
where param.param_type='0003'
5: nvl ("字符串", 0) 计算字符串的整型数值
sum(nvl(bpd.pur_price, 0) * nvl(bpd.order_num, 0)) coin
6: 利用序列生产分拣单号
select seq_pack_id.nextval from dual
7: 查询用户的角色
1)
用户1已有的角色
select
r.role_id , r.role_name
from
bas_role r,
bas_user_roles t
where
r.role_id = t.role_id and
t.user_id = 1
2)
用户1未有的角色
select r.role_id , r.role_name
from bas_role r,
(
select ur.role_id
from bas_user_roles ur
where ur.user_id = 1
) t
where
r.role_id = t.role_id(+) and
t.role_id is null