complex sql search

最新推荐文章于 2023-03-08 17:20:14 发布

talentkep

最新推荐文章于 2023-03-08 17:20:14 发布

阅读量178

点赞数

分类专栏： self test 文章标签： SQL Server

本文链接：https://blog.csdn.net/talentkep/article/details/84513608

版权

self test 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

Declare

sqlStr_part1 varchar2(4000);

sqlStr_part2 varchar2(4000);

sqlStr_part3 varchar2(4000);

sqlStr_part4 varchar2(4000);

out_string_in_part1 varchar2(4000);

out_string_in_part2 varchar2(4000);

out_string_in_part3 varchar2(4000);

out_string_in_part4 varchar2(4000);

TYPE cur_type IS REF CURSOR;

d_cursor_tab_part1 cur_type;

d_cursor_tab_part2 cur_type;

d_cursor_tab_part3 cur_type;

d_cursor_tab_part4 cur_type;

idNum_part1 NUMBER;

idNum_part2 NUMBER;

idNum_part3 NUMBER;

idNum_part4 NUMBER;

v_rows NUMBER;

str_len_part1 NUMBER;

str_len_part2 NUMBER;

str_len_part3 NUMBER;

str_len_part4 NUMBER;

loop_count_part1 NUMBER;

loop_count_part2 NUMBER;

loop_count_part3 NUMBER;

loop_count_part4 NUMBER;

cursor searchSQL_Cursor_part1 is

select 'select count(*) from "'|| t.TABLE_NAME || '" t where

'|| 'upper("' ||COLUMN_NAME || '") like ''%PFINGOI%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%R538LA%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%R519LA%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%R532LA%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%4249B%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%4977B01A%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%3437A%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3437C%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R613A-LOOP-UTM%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R613A-LOOP-MIM%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R614A%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R613A%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R455A%''or '|| 'upper("' || COLUMN_NAME||'") like ''%DFDRLGDM%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R613-LOOP-TESTS%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%AAAA-LOOP-TESTS%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R143%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R123%''

or '|| 'upper("' || COLUMN_NAME||'") like ''%R162%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%1870%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3221%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3624%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3131%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3037%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3698%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R184%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%2900%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%4270%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%4035%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%4450%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%4675%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R473%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R342%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R358%'' '

as sqlStrr, TABLE_NAME from user_tab_columns t where data_type = 'VARCHAR2' or data_type = 'CHAR';

cursor searchSQL_Cursor_part2 is

select 'select count(*) from "'|| t.TABLE_NAME || '" t where

'|| 'upper("' || COLUMN_NAME||'") like ''%P65NLG-LOOP-GOI%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P65NLG57L1PBMAC1%''or '|| 'upper("' || COLUMN_NAME||'") like ''%P65NLG47L1P8M7C1%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P65NLG45L1P8M7C1%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P65NLG38L1P8M7C1%''

or '|| 'upper("' || COLUMN_NAME||'") like ''%P55NLG35L1P8M7C1%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P11ULG32L1P7M6C1%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P11ULG32L1P7M6C1%''or '|| 'upper("' || COLUMN_NAME||'") like ''%P09ULG-LOOP-UTM%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P09ULG-LOOP-MIM%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P09ULG54L1PAM9C3%''or '|| 'upper("' || COLUMN_NAME||'") like ''%P09ULG54L1PAM9C2%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P09ULG54L1PAM9C1%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P09UCI-LOOP-TESTS%''or '|| 'upper("' || COLUMN_NAME||'") like ''%PDDF_LGDMY%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P09UCI-LOOP-TESTS%'' '

as sqlStrr, TABLE_NAME from user_tab_columns t where data_type = 'VARCHAR2' or data_type = 'CHAR';

cursor searchSQL_Cursor_part3 is

select 'select count(*) from "'|| t.TABLE_NAME || '" t where

'|| 'upper("' ||COLUMN_NAME || '") like ''%0920POX0116%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%0760SIN1250%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%1000LOX0106%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%1100ANN120M%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%0650SIN0300%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%0800WGO0051%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%0850GOX0032-65LL%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%0620UPY1000-65LL%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%0410ALY030M-65LL%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%0800SOX0029%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%0800SOX0028%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%0800ANN060M-90G%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%0650SIN0206B%''or '|| 'upper("' || COLUMN_NAME||'") like ''%0650SIN0124%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%B1AL10N700-C124%''

or '|| 'upper("' || COLUMN_NAME||'") like ''%C090N200-12%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%C65%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%C090N200-13%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%C090N200-42%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%C090N200-43%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%C90%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%E30C65N200-D43%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P_16KAV2E1A1-13%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P_7KAV1E2A1-123%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P_7KAV1E2A1-2%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P_8KAV1E2A1-23%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%E35T05N50-C12%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%E35T05N50-C13%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%E35T05N50-C42%'' '

as sqlStrr, TABLE_NAME from user_tab_columns t where data_type = 'VARCHAR2' or data_type = 'CHAR';

cursor searchSQL_Cursor_part4 is

select 'select count(*) from "'|| t.TABLE_NAME || '" t where '|| 'upper("' ||COLUMN_NAME || '") like ''%LOGIC%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%LG%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%65LL%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%55LL%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%90RF%'' or '

|| 'upper("' || COLUMN_NAME||'") like ''%CIS%''or '|| 'upper("' || COLUMN_NAME||'") like ''%CCD%''or '|| 'upper("' || COLUMN_NAME||'") like ''%FSI%''or '|| 'upper("' || COLUMN_NAME||'") like ''%BSI%''or '|| 'upper("' || COLUMN_NAME||'") like ''%APS%''or '|| 'upper("' || COLUMN_NAME||'") like ''%R614%''or '|| 'upper("' || COLUMN_NAME||'") like ''%R613%''or ' || 'upper("' || COLUMN_NAME||'") like ''%IMAGE SENSOR%'' '

as sqlStrr, TABLE_NAME from user_tab_columns t where data_type = 'VARCHAR2' or data_type = 'CHAR';

begin

--part1 loop

for searchSQL_Cursor_Point_part1 in searchSQL_Cursor_part1 loop

DBMS_OUTPUT.enable(1000000);

sqlStr_part1 := searchSQL_Cursor_Point_part1.sqlStrr;

out_string_in_part1:=sqlStr_part1;

str_len_part1 := length(sqlStr_part1);

loop_count_part1 := 0;

execute immediate sqlStr_part1;

OPEN d_cursor_tab_part1 FOR sqlStr_part1;

LOOP

FETCH d_cursor_tab_part1 INTO idNum_part1;

EXIT WHEN d_cursor_tab_part1%NOTFOUND;

if (idNum_part1>0)

then

WHILE loop_count_part1<str_len_part1 loop

dbms_output.put_line( substr( out_string_in_part1, loop_count_part1 +1, 255 ) );

loop_count_part1 := loop_count_part1 +255;

end loop;

DBMS_OUTPUT.PUT_LINE('--------------------------------------Search for prodID find table:'||searchSQL_Cursor_Point_part1.TABLE_NAME||'Found:='||idNum_part1||'=exists!');

end if;

-- process record

END LOOP;

end loop;

--part2 loop

for searchSQL_Cursor_Point_part2 in searchSQL_Cursor_part2 loop

DBMS_OUTPUT.enable(1000000);

sqlStr_part2 := searchSQL_Cursor_Point_part2.sqlStrr;

out_string_in_part2:=sqlStr_part2;

str_len_part2 := length(sqlStr_part2);

loop_count_part2 := 0;

execute immediate sqlStr_part2;

OPEN d_cursor_tab_part2 FOR sqlStr_part2;

LOOP

FETCH d_cursor_tab_part2 INTO idNum_part2;

EXIT WHEN d_cursor_tab_part2%NOTFOUND;

if (idNum_part2>0)

then

WHILE loop_count_part2<str_len_part2 loop

dbms_output.put_line( substr( out_string_in_part2, loop_count_part2 +1, 255 ) );

loop_count_part2 := loop_count_part2 +255;

end loop;

DBMS_OUTPUT.PUT_LINE('--------------------------------------Search for planID find table:'||searchSQL_Cursor_Point_part2.TABLE_NAME||'Found:='||idNum_part2||'=exists!');

end if;

-- process record

END LOOP;

end loop;

--part3 loop

for searchSQL_Cursor_Point_part3 in searchSQL_Cursor_part3 loop

DBMS_OUTPUT.enable(1000000);

sqlStr_part3 := searchSQL_Cursor_Point_part3.sqlStrr;

out_string_in_part3:=sqlStr_part3;

str_len_part3 := length(sqlStr_part3);

loop_count_part3 := 0;

execute immediate sqlStr_part3;

OPEN d_cursor_tab_part3 FOR sqlStr_part3;

LOOP

FETCH d_cursor_tab_part3 INTO idNum_part3;

EXIT WHEN d_cursor_tab_part3%NOTFOUND;

if (idNum_part3>0)

then

WHILE loop_count_part3<str_len_part3 loop

dbms_output.put_line( substr( out_string_in_part3, loop_count_part3 +1, 255 ) );

loop_count_part3 := loop_count_part3 +255;

end loop;

DBMS_OUTPUT.PUT_LINE('--------------------------------------Search for recipeID find table:'||searchSQL_Cursor_Point_part3.TABLE_NAME||'Found:='||idNum_part3||'=exists!');

end if;

-- process record

END LOOP;

end loop;

--part4 loop

for searchSQL_Cursor_Point_part4 in searchSQL_Cursor_part4 loop

DBMS_OUTPUT.enable(1000000);

sqlStr_part4 := searchSQL_Cursor_Point_part4.sqlStrr;

out_string_in_part4:=sqlStr_part4;

str_len_part4 := length(sqlStr_part4);

loop_count_part4 := 0;

execute immediate sqlStr_part4;

OPEN d_cursor_tab_part4 FOR sqlStr_part4;

LOOP

FETCH d_cursor_tab_part4 INTO idNum_part4;

EXIT WHEN d_cursor_tab_part4%NOTFOUND;

if (idNum_part4>0)

then

WHILE loop_count_part4<str_len_part4 loop

dbms_output.put_line( substr( out_string_in_part4, loop_count_part4 +1, 255 ) );

loop_count_part4 := loop_count_part4 +255;

end loop;

DBMS_OUTPUT.PUT_LINE('--------------------------------------Search for Logic or CIS keyword find table:'||searchSQL_Cursor_Point_part4.TABLE_NAME||'Found:='||idNum_part4||'=exists!');

end if;

-- process record

END LOOP;

end loop;

end;