--分页查询
create or replace procedure AISITE_QUERY_NAVIGATION
(p_sql IN VARCHAR,
p_page_size IN INTEGER,
p_page_number IN INTEGER,
p_count OUT NUMBER,
p_cursor OUT RECORDSET.SELECT_CURSOR)
is
v_sql VARCHAR(4000);
v_page_lower_bound INTEGER;
v_page_upper_bount INTEGER ;
begin
v_page_lower_bound := (p_page_number - 1) * p_page_size + 1;
v_page_upper_bount := p_page_number * p_page_size;
create or replace procedure AISITE_QUERY_NAVIGATION
(p_sql IN VARCHAR,
p_page_size IN INTEGER,
p_page_number IN INTEGER,
p_count OUT NUMBER,
p_cursor OUT RECORDSET.SELECT_CURSOR)
is
v_sql VARCHAR(4000);
v_page_lower_bound INTEGER;
v_page_upper_bount INTEGER ;
begin
v_page_lower_bound := (p_page_number - 1) * p_page_size + 1;
v_page_upper_bount := p_page_number * p_page_size;
v_sql := 'select count(*) from (' || p_sql || ')' ;
EXECUTE IMMEDIATE v_sql INTO p_count ;
EXECUTE IMMEDIATE v_sql INTO p_count ;
v_sql := 'SELECT DISTINCT AISITE_NAVIGATION.*' ||
' FROM AISITE_NAVIGATION ' ||
' INNER JOIN ' ||
' (SELECT B.* '||
' FROM (SELECT A.* ,rownum rn' ||
' FROM (' || p_sql || ') A ' ||
' WHERE rownum <= ' || TO_CHAR(v_page_upper_bount) || ') B ' ||
' WHERE rn >= ' || TO_CHAR(v_page_lower_bound) || ') C' ||
' ON C.NAVIGATION_ID = AISITE_NAVIGATION.NAVIGATION_ID';
' FROM AISITE_NAVIGATION ' ||
' INNER JOIN ' ||
' (SELECT B.* '||
' FROM (SELECT A.* ,rownum rn' ||
' FROM (' || p_sql || ') A ' ||
' WHERE rownum <= ' || TO_CHAR(v_page_upper_bount) || ') B ' ||
' WHERE rn >= ' || TO_CHAR(v_page_lower_bound) || ') C' ||
' ON C.NAVIGATION_ID = AISITE_NAVIGATION.NAVIGATION_ID';
OPEN p_cursor FOR v_sql;
end AISITE_QUERY_NAVIGATION;
end AISITE_QUERY_NAVIGATION;
--导航移动
create or replace procedure AISITE_Move_Navigation
(
n_id in VARCHAR2 ,/*μ?o?id*/
p_type in NUMBER,/*ààDí*/
p_id in VARCHAR2,/*???úμ?id*/
p_seq in NUMBER, /*μ±?°μ?DòáD*/
IsMoveUp in NUMBER /*ê?·??òé?ò??ˉ*/
)
is
max_sequence number ;
begin
/*?òé?ò??ˉ*/
if IsMoveUp = 1 and p_seq != 1
then
if p_id != '00000000-0000-0000-0000-000000000000'
then
/*ò??ˉ????*/
update aisite_navigation
set aisite_navigation.sequence = p_seq
where
aisite_navigation.positiontype = p_type
and aisite_navigation.parentid = p_id
and aisite_navigation.sequence = p_seq - 1
and aisite_navigation.is_valid = 1 ;
else
/*ò??ˉ????*/
update aisite_navigation
set aisite_navigation.sequence = p_seq
where aisite_navigation.positiontype = p_type
and (aisite_navigation.parentid = '' or aisite_navigation.parentid is null )
and aisite_navigation.sequence = p_seq -1
and aisite_navigation.is_valid = 1 ;
end if ;
/*ò??ˉμ?o?*/
Update aisite_navigation
set aisite_navigation.sequence = p_seq -1
where aisite_navigation.navigation_id= n_id ;
end if ;
/*??μ?×?′óDòáD*/
if p_id != '00000000-0000-0000-0000-000000000000'
then
select max(sequence )
into max_sequence
from aisite_navigation
where aisite_navigation.parentid = p_id
and aisite_navigation.positiontype = p_type
and aisite_navigation.is_valid = 1 ;
else
select max(sequence )
into max_sequence
from aisite_navigation
where (aisite_navigation.parentid = '' or aisite_navigation.parentid is null )
and aisite_navigation.positiontype = p_type
and aisite_navigation.is_valid = 1 ;
end if ;
/*?ò??ò??ˉ*/
if IsMoveUp = 0 and p_seq != max_sequence
then
if p_id != '00000000-0000-0000-0000-000000000000'
then
/*ò??ˉ????*/
update aisite_navigation
set aisite_navigation.sequence = p_seq
where
aisite_navigation.positiontype = p_type
and aisite_navigation.parentid = p_id
and aisite_navigation.sequence = p_seq + 1
and aisite_navigation.is_valid = 1 ;
else
/*ò??ˉ????*/
update aisite_navigation
set aisite_navigation.sequence = p_seq
where aisite_navigation.positiontype = p_type
and (aisite_navigation.parentid = '' or aisite_navigation.parentid is null )
and aisite_navigation.sequence = p_seq + 1
and aisite_navigation.is_valid = 1 ;
end if ;
/*ò??ˉμ?o?*/
Update aisite_navigation
set aisite_navigation.sequence = p_seq + 1
where aisite_navigation.navigation_id= n_id ;
create or replace procedure AISITE_Move_Navigation
(
n_id in VARCHAR2 ,/*μ?o?id*/
p_type in NUMBER,/*ààDí*/
p_id in VARCHAR2,/*???úμ?id*/
p_seq in NUMBER, /*μ±?°μ?DòáD*/
IsMoveUp in NUMBER /*ê?·??òé?ò??ˉ*/
)
is
max_sequence number ;
begin
/*?òé?ò??ˉ*/
if IsMoveUp = 1 and p_seq != 1
then
if p_id != '00000000-0000-0000-0000-000000000000'
then
/*ò??ˉ????*/
update aisite_navigation
set aisite_navigation.sequence = p_seq
where
aisite_navigation.positiontype = p_type
and aisite_navigation.parentid = p_id
and aisite_navigation.sequence = p_seq - 1
and aisite_navigation.is_valid = 1 ;
else
/*ò??ˉ????*/
update aisite_navigation
set aisite_navigation.sequence = p_seq
where aisite_navigation.positiontype = p_type
and (aisite_navigation.parentid = '' or aisite_navigation.parentid is null )
and aisite_navigation.sequence = p_seq -1
and aisite_navigation.is_valid = 1 ;
end if ;
/*ò??ˉμ?o?*/
Update aisite_navigation
set aisite_navigation.sequence = p_seq -1
where aisite_navigation.navigation_id= n_id ;
end if ;
/*??μ?×?′óDòáD*/
if p_id != '00000000-0000-0000-0000-000000000000'
then
select max(sequence )
into max_sequence
from aisite_navigation
where aisite_navigation.parentid = p_id
and aisite_navigation.positiontype = p_type
and aisite_navigation.is_valid = 1 ;
else
select max(sequence )
into max_sequence
from aisite_navigation
where (aisite_navigation.parentid = '' or aisite_navigation.parentid is null )
and aisite_navigation.positiontype = p_type
and aisite_navigation.is_valid = 1 ;
end if ;
/*?ò??ò??ˉ*/
if IsMoveUp = 0 and p_seq != max_sequence
then
if p_id != '00000000-0000-0000-0000-000000000000'
then
/*ò??ˉ????*/
update aisite_navigation
set aisite_navigation.sequence = p_seq
where
aisite_navigation.positiontype = p_type
and aisite_navigation.parentid = p_id
and aisite_navigation.sequence = p_seq + 1
and aisite_navigation.is_valid = 1 ;
else
/*ò??ˉ????*/
update aisite_navigation
set aisite_navigation.sequence = p_seq
where aisite_navigation.positiontype = p_type
and (aisite_navigation.parentid = '' or aisite_navigation.parentid is null )
and aisite_navigation.sequence = p_seq + 1
and aisite_navigation.is_valid = 1 ;
end if ;
/*ò??ˉμ?o?*/
Update aisite_navigation
set aisite_navigation.sequence = p_seq + 1
where aisite_navigation.navigation_id= n_id ;
end if ;
end AISITE_Move_Navigation;
end AISITE_Move_Navigation;