Oracle beginner Study Notes

1. Paging Query

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. Stored Procedure

A. Example 1

Create or replace procedure bp_sp_product_insert
(
V_product_no varchar2,
V_cost number
)
Rochelle count number;
Rochelle product_title bp_system_products.product_title % type;
Rochelle face bp_system_products.face % type;
Rochelle cost bp_system_products.cost % type;
L_carrier_no bp_system_products.carrier_no % type;
Rochelle business_type bp_system_products.business_type % type;
Rochelle 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,
Rochelle face,
Rochelle cost,
Rochelle 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;
-- Data update available
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,
Rochelle product_title,
Rochelle rier_no,
Rochelle face,
Rochelle cost,
Sysdate,
Rochelle business_type,
Sysn_type.sys_complate,
Rochelle business_no );
End if;
End;

B. Example 2

Create or replace procedure bp_sp_sys_product_get
(
V_product_no in varchar2, -- product no.
V_product_title in varchar2, -- product title
V_business_type in varchar2, -- business type (game, phone, qcoin)
V_plat_id in varchar2, -- platform ID
V_status in varchar2, -- whether to shelve (0 shelving, 1 dismounting)
V_business_no in varchar2, -- business type (mobile Hebei)
V_page_index in number,
V_page_size in number,
V_count out number,
V_records out bp_pkg_cursor.pointer
)
Rochelle page_index number (10): = 1;
Rochelle page_size number (10): = 10;
Begin
If (v_page_index is not null and v_page_index> 0) then
Rochelle page_index: = v_page_index;
End if;

If (v_page_size is not null and v_page_size> 0) then
Rochelle 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
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. Example 3

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
-- Get the number segment information
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
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;