📄 queryfilter_b.sql
字号:
create or replace package body PKG_QueryFilter is
/*-------------------------------------------------------------------------
|| Description ：Filtering the result of query into the cursor
||
|| Parameter ：Notation Meaning I/O Type
|| -------------------------------------------------------------
|| p_Projection Shown Attributes Input VARCHAR2(100)
|| p_TableName Related Tables Input VARCHAR2(30)
|| p_Condition Conditions Input VARCHAR2(1000)
|| p_Cursor Filtering Result Output Result_CURSOR
|| p_AppCode Execution Code Output NUMBER
|| p_ErrorMsg Error Message Output VARCHAR2(100)
||
|| Author ：Dongyi Chen Date ：2009-4-22
||-------------------------------------------------------------------------*/
PROCEDURE Prc_QueryFilter ( p_Projection IN Varchar2,
p_TableName IN Varchar2,
p_Condition IN Varchar2,
p_Cursor Out Result_CURSOR,
p_AppCode OUT NUMBER,
p_ErrorMsg OUT Varchar2)
IS
-- local variable declarations
i_cursor INTEGER; -- cursor for dynamic sql
sqlStmt varchar2(300); -- sql statement
state INTEGER; -- execution state of the dynamic sql
v_userid Varchar2(2); -- user id who is running the current session
n_Max Number; -- current user's maximum access number of the specified table
n_Filter Number; -- current user's record number of the corresponding filter table to the specified table
i_col INTEGER; -- column/attribute number of a query
t_desc dbms_sql.desc_tab; -- collect column information
v_column varchar2(20); -- column value
v_colName varchar2(32); -- column name
v_col varchar2(100); -- selected column
v_cond varchar2(300); -- condition
i_sql INTEGER; -- anothter cursor for dynamic sql
n_row NUMBER; -- row number of identical records in two tables
n_outdate NUMBER; -- row number of 'outdate' records in the corresponding filter tables
BEGIN
-- initialization
p_AppCode := 1;
-- in case no condition
if p_Condition is null or (length(p_Condition)=0) then
v_cond := '1 = 1';
else
v_cond := p_Condition;
end if;
-- get userId
select client_info into v_userid from v$session b where b.audsid=to_number(USERENV('SESSIONID'));
--v_userid := 1;
i_cursor := dbms_sql.open_cursor; --open the dynamic sql cursor
-- create a temporary table to store the desired data
sqlStmt := 'create table ' || p_TableName || '_tmp' || v_userid || ' as select * from '|| p_TableName || ' where rownum = 0';
dbms_sql.parse(i_cursor,sqlStmt,dbms_sql.native); --parse the sql statement
state := dbms_sql.execute(i_cursor); --execute the statement
-- create a waiting table to store the undesired data after the first round loop for the second round process
sqlStmt := 'create table ' || p_TableName || '_wait' || v_userid || ' as select * from '|| p_TableName || ' where rownum = 0';
dbms_sql.parse(i_cursor,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_cursor);
-- get rowcount of the corresponding filter table
sqlStmt := 'select count(*) from ' || p_TableName || 'FILTER';
dbms_sql.parse(i_cursor, sqlStmt, dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN (i_cursor,1,n_Filter);
state := dbms_sql.execute(i_cursor);
state := DBMS_SQL.FETCH_ROWS (i_cursor);
DBMS_SQL.COLUMN_VALUE (i_cursor,1,n_Filter);
-- get the maximum access number of the current user to the specified table
sqlStmt := 'select AccessNumber from AccessNumber where UserID = ' || v_userid || ' and Table_Name = ''' || p_TableName || '''';
dbms_sql.parse(i_cursor, sqlStmt, dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN (i_cursor,1,n_Max);
state := dbms_sql.execute(i_cursor);
state := DBMS_SQL.FETCH_ROWS (i_cursor);
DBMS_SQL.COLUMN_VALUE (i_cursor,1,n_Max);
-- get origianl query result
sqlStmt := 'select * from '|| p_TableName || ' where ' || v_cond;
dbms_sql.parse(i_cursor,sqlStmt,dbms_sql.native);
dbms_sql.describe_columns(i_cursor, i_col, t_desc);
FOR j IN 1 .. i_col LOOP
dbms_sql.define_column(i_cursor, j, v_column,20);
-- record the selected column
if v_col is null or length(v_col)=0 then
v_col := t_desc(j).col_name;
else
v_col := v_col || ',' || t_desc(j).col_name;
end if;
END LOOP;
state := dbms_sql.execute(i_cursor);
WHILE (dbms_sql.fetch_rows(i_cursor) > 0) LOOP
v_cond := 'UserID = ' || v_userid;
FOR j IN 1 .. i_col LOOP
v_colname := t_desc(j).col_name;
dbms_sql.column_value(i_cursor, j, v_column);
v_cond := v_cond || ' and ' || v_colname || ' = ''' || v_column || '''';
END LOOP;
-- to see whether there is an identical record in the corresponding filter table
i_sql := dbms_sql.open_cursor;
sqlStmt := 'select count(*) from ' || p_TableName || 'FILTER WHERE ' || v_cond;
dbms_sql.parse(i_sql, sqlStmt, dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN (i_sql,1,n_row);
state := dbms_sql.execute(i_sql);
state := DBMS_SQL.FETCH_ROWS (i_sql);
DBMS_SQL.COLUMN_VALUE (i_sql,1,n_row);
if n_row>=1 then
-- update the last_access_date of the corresponding filter table
sqlStmt := 'update ' || p_TableName || 'filter set Last_Access_Date = sysdate where ' || v_cond;
dbms_sql.parse(i_sql,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_sql);
-- insert the record into the temporary table
sqlStmt := 'insert into ' || p_TableName || '_tmp' || v_userid || ' select * from ' || p_TableName || ' where ' || substr(v_cond,length('UserID = ' || v_userid || ' and ')+1);
dbms_sql.parse(i_sql,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_sql);
elsif n_Max > n_Filter then
n_Filter := n_Filter + 1;
-- insert the record into the temporary table
sqlStmt := 'insert into ' || p_TableName || '_tmp' || v_userid || ' select * from ' || p_TableName || ' where ' || substr(v_cond,length('UserID = ' || v_userid || ' and ')+1);
dbms_sql.parse(i_sql,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_sql);
-- insert the record into the corresponding filter table
sqlStmt := 'insert into ' || p_TableName || 'filter select ' || v_userid || ',' || v_col || ',sysdate from ' || p_TableName || ' where ' || substr(v_cond,length('UserID = ' || v_userid || ' and ')+1);
dbms_sql.parse(i_sql,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_sql);
else
-- insert the record into the waiting table
sqlStmt := 'insert into ' || p_TableName || '_wait' || v_userid || ' select * from ' || p_TableName || ' where ' || substr(v_cond,length('UserID = ' || v_userid || ' and ')+1);
dbms_sql.parse(i_sql,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_sql);
end if;
dbms_sql.close_cursor(i_sql);
END LOOP;
-- process the data in the waiting table
sqlStmt := 'select * from '|| p_TableName || '_wait' || v_userid;
dbms_sql.parse(i_cursor,sqlStmt,dbms_sql.native);
dbms_sql.describe_columns(i_cursor, i_col, t_desc);
v_col := '';
FOR j IN 1 .. i_col LOOP
dbms_sql.define_column(i_cursor, j, v_column,20);
-- record the selected column
if v_col is null or length(v_col)=0 then
v_col := t_desc(j).col_name;
else
v_col := v_col || ',' || t_desc(j).col_name;
end if;
END LOOP;
state := dbms_sql.execute(i_cursor);
WHILE (dbms_sql.fetch_rows(i_cursor) > 0) LOOP
v_cond := 'UserID = ' || v_userid;
FOR j IN 1 .. i_col LOOP
v_colname := t_desc(j).col_name;
dbms_sql.column_value(i_cursor, j, v_column);
v_cond := v_cond || ' and ' || v_colname || ' = ''' || v_column || '''';
END LOOP;
-- to see if there is an 'outdate' record in the corresponding filter table
i_sql := dbms_sql.open_cursor;
sqlStmt := 'select count(*) from '|| p_TableName || 'filter where userid = ' || v_userid || ' and sysdate - Last_Access_Date > 15 and rownum = 1';
dbms_sql.parse(i_sql, sqlStmt, dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN (i_sql,1,n_outdate);
state := dbms_sql.execute(i_sql);
state := DBMS_SQL.FETCH_ROWS (i_sql);
DBMS_SQL.COLUMN_VALUE (i_sql,1,n_outdate);
if n_outdate>0 then
-- delete one of the most 'outdate' record in the corresponding filter table
sqlStmt := 'delete from ' || p_TableName || 'filter where userid = ' || v_userid || ' and last_access_date = (select min(last_access_date) from empfilter where userid = ' || v_userid || ') and rownum = 1';
dbms_sql.parse(i_sql,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_sql);
-- insert the record into the temporary table
sqlStmt := 'insert into ' || p_TableName || '_tmp' || v_userid || ' select * from ' || p_TableName || ' where ' || substr(v_cond,length('UserID = ' || v_userid || ' and ')+1);
dbms_sql.parse(i_sql,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_sql);
-- insert the record into the corresponding filter table
sqlStmt := 'insert into ' || p_TableName || 'filter select ' || v_userid || ',' || v_col || ',sysdate from ' || p_TableName || ' where ' || substr(v_cond,length('UserID = ' || v_userid || ' and ')+1);
dbms_sql.parse(i_sql,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_sql);
end if;
dbms_sql.close_cursor(i_sql);
END LOOP;
-- return the data in temporary table
sqlStmt := 'select * from ' || p_TableName || '_tmp' || v_userid;
open p_Cursor for sqlStmt;
-- drop the temporary table
sqlStmt := 'drop table ' || p_TableName || '_tmp' || v_userid;
dbms_sql.parse(i_cursor,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_cursor);
-- drop the waiting table
sqlStmt := 'drop table ' || p_TableName || '_wait' || v_userid;
dbms_sql.parse(i_cursor,sqlStmt,dbms_sql.native);
state := dbms_sql.execute(i_cursor);
dbms_sql.close_cursor(i_cursor); --close the dynamic sql cursor
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(i_cursor) THEN
DBMS_SQL.CLOSE_CURSOR (i_cursor);
END IF;
IF DBMS_SQL.IS_OPEN(i_sql) THEN
DBMS_SQL.CLOSE_CURSOR (i_sql);
END IF;
p_AppCode := -1;
p_ErrorMsg := 'Prc_QueryFilter Error:'||SQLERRM;
END Prc_QueryFilter;
end PKG_QueryFilter;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -