SET SERVEROUTPUT ON
ACCEPT ROW_ID PROMPT '请输入要查询的字符串 :'
DECLARE
SQL_STMT VARCHAR2(200);
V_TBLNAME VARCHAR2(30);
V_COLNAME VARCHAR2(30);
REC_COUNT NUMBER(7):=0;
CURSOR WT_TABLES IS
SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS
WHERE DATA_TYPE='VARCHAR2'
ORDER BY TABLE_NAME,COLUMN_NAME;
BEGIN
FOR COLREC IN WT_TABLES LOOP
V_TBLNAME:= COLREC.TABLE_NAME;
V_COLNAME:= COLREC.COLUMN_NAME;
-- DBMS_OUTPUT.PUT_LINE ('TABLE: '||
-- V_TBLNAME||' ,COLUMN: '||V_COLNAME);
-- DBMS_OUTPUT.PUT_LINE (V_TBLNAME);
SQL_STMT:='SELECT COUNT(*) FROM '||COLREC.TABLE_NAME||' WHERE '||COLREC.COLUMN_NAME||' LIKE :1';
EXECUTE IMMEDIATE SQL_STMT INTO REC_COUNT USING '&ROW_ID';
-- DBMS_OUTPUT.PUT_LINE (V_TBLNAME||' '||REC_COUNT);
IF REC_COUNT>=1 THEN
DBMS_OUTPUT.PUT_LINE ('TABLE: '||
V_TBLNAME||' ,COLUMN: '||V_COLNAME);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('以上表中的字段包含该数据');
END;
/
-----******************-----------
执行示例:
SQL> @querystring.sql
请输入要查询的字符串 :TestString
原值 18: EXECUTE IMMEDIATE SQL_STMT INTO REC_COUNT USING '&ROW_ID';
新值 18: EXECUTE IMMEDIATE SQL_STMT INTO REC_COUNT USING 'qq';
TABLE: CABINET ,COLUMN: NAME
TABLE: CSC_GENERATEDNUMBER ,COLUMN: GN_REQUESTOR
TABLE: ELECTRONICSIGNATURE ,COLUMN: NAME
TABLE: LIFECYCLEHISTORY ,COLUMN: ACTORNAME
TABLE: LOCATIONCHANGERECORD ,COLUMN: USERID
TABLE: PERSONRESOURCE ,COLUMN: NAME
TABLE: PUBLISHINGSUMMARY ,COLUMN: USERNAME
TABLE: RECENTUPDATE ,COLUMN: NAME
TABLE: WORKITEM ,COLUMN: COMPLETEDBY
TABLE: WTUSER ,COLUMN: NAME
以上表中的字段包含该数据
PL/SQL 过程已成功完成。
SQL>
查找Oracle数据库中包含指定值的字段
最新推荐文章于 2023-06-05 13:51:07 发布