ORACLE 新手学习笔记
1.分页查询
select * from (select rownum no, e.* from
(select * from emp order by sal desc) e where rownum<=5 ) where no>=3
select *
from
(select rownum no,e.* from (select * from emp order by sal desc) e)
where
no>=3 and no<=5
2.存储过程
a.范例一
create or replace procedure bp_sp_product_insert
(
v_product_no varchar2,
v_cost number
) as
l_count number;
l_product_title bp_system_products.product_title%type;
l_face bp_system_products.face%type;
l_cost bp_system_products.cost%type;
l_carrier_no bp_system_products.carrier_no%type;
l_business_type bp_system_products.business_type%type;
l_business_no bp_system_products.business_no%type;
begin
if (v_product_no is null) then
return;
end if;
select t.product_name,
t.product_value,
t.product_value * v_cost,
t.business_no
into l_product_title,
l_face,
l_cost,
l_business_no
from matchingrecharge.st_carrier_products t
where t.product_no = v_product_no
and t.status = enable_status.enable
and rownum <= 1;
bp_sp_product_bs_get(l_business_no, l_business_type, l_carrier_no);
select count(*)
into l_count
from bp_system_products t
where t.product_no = v_product_no;
--有数据更新
if (l_count > 0) then
update bp_system_products t
set t.product_title = l_product_title
,t.carrier_no = l_carrier_no
,t.face = l_face
,t.cost = l_cost
,t.update_time = sysdate
,t.business_type = l_business_type
,t.is_update = sysn_type.sys_complate
,t.business_no = l_business_no
where t.product_no = v_product_no;
else
insert into bp_system_products
(product_no,
product_title,
carrier_no,
face,
cost,
update_time,
business_type,
is_update,
business_no)
values
(v_product_no,
l_product_title,
l_carrier_no,
l_face,
l_cost,
sysdate,
l_business_type,
sysn_type.sys_complate,
l_business_no);
end if;
end;
b.范例二
create or replace procedure bp_sp_sys_product_get
(
v_product_no in varchar2, --产品编号
v_product_title in varchar2, --产品标题
v_business_type in varchar2, --业务类型(游戏,话费,Q币)
v_plat_id in varchar2, --平台编号
v_status in varchar2, --是否上架(0上架,1下架)
v_business_no in varchar2, --业务类型(移动河北)
v_page_index in number,
v_page_size in number,
v_count out number,
v_records out bp_pkg_cursor.pointer
) as
l_page_index number(10) := 1;
l_page_size number(10) := 10;
begin
if (v_page_index is not null and v_page_index > 0) then
l_page_index := v_page_index;
end if;
if (v_page_size is not null and v_page_size > 0) then
l_page_size := v_page_size;
end if;
select count(*)
into v_count
from bp_system_products t
left join bp_shop_products p on t.product_no = p.product_no
and p.platform_id = v_plat_id
where t.is_update = 0
and (v_product_no is null or t.product_no = v_product_no)
and
(v_business_type is null or t.business_type = v_business_type)
and (v_product_title is null or
t.product_title like '%' || to_char(v_product_title) || '%')
and (v_status is null or p.status = v_status or
(v_status = enable_status.disabled and p.status is null))
and (v_business_no is null or t.business_no = v_business_no);
open v_records for
select *
from (
select rd,
rownum as rn
from (select t.rowid as rd
from bp_system_products t
left join bp_shop_products p on t.product_no =
p.product_no
and p.platform_id =
v_plat_id
where t.is_update = 0
and (v_product_no is null or t.product_no = v_product_no)
and (v_business_type is null or
t.business_type = v_business_type)
and (v_product_title is null or
t.product_title like
'%' || to_char(v_product_title) || '%')
and (v_status is null or p.status = v_status or
(v_status = enable_status.disabled and
p.status is null))
and (v_business_no is null or
t.business_no = v_business_no)
order by t.product_title asc) tt0
where rownum <= l_page_index * l_page_size) tt1
inner join bp_system_products t0 on tt1.rd = t0.rowid
left join bp_shop_products t1 on t1.platform_id = v_plat_id
and
t1.product_no = t0.product_no
where tt1.rn > (l_page_index - 1) * l_page_size
order by t0.product_title;
end;
c.范例三
create or replace procedure bp_sp_system_get_section
(
v_section in varchar2,
v_errcode out varchar2,
v_carrier_no out varchar2,
v_province_id out varchar2,
v_province_name out varchar2,
v_city_id out varchar2,
v_city_name out varchar2,
v_mobile_type out varchar2
) is
l_exists number(1) := 0;
begin
--获取号码段信息
select count(0)
into l_exists
from bp_hf_section t
where t.section_id = v_section;
if (l_exists <= 0) then
v_errcode := error_code.failure;
return;
end if;
select a.carrier_no,
a.province_id,
c.province_name,
a.city_id,
d.city_name,
a.mobile_type
into v_carrier_no,
v_province_id,
v_province_name,
v_city_id,
v_city_name,
v_mobile_type
from bp_hf_section a
left join bp_hf_province c on a.province_id = c.province_id
left join bp_hf_city d on a.city_id = d.city_id
where a.section_id = v_section;
v_errcode := error_code.suc;
exception
when others then
v_errcode := error_code.failure;
return;
bp_sp_write_log('bp_sp_system_get_section',
0,
sqlerrm,
'v_section:' || v_section);
end bp_sp_system_get_section;
