oracle plsql 根据值查询在某个表,某个字段和 sql server 中根据值查询在某个表某个字段

淡淡的烟草味﹌ 2023-10-18 11:12 45阅读 0赞

[oracle plsql 根据 值 查询 在某个表,某个字段 和 sql server 中根据 值 查询 在某个表某个字段][oracle plsql _ _ _ _ _ sql server _ _ _]

Oracle 方式 :

declare
    CURSOR cur_query IS
      select table_name, column_name, data_type from user_tab_columns;
      a number;
      sql_hard varchar2(2000);
      vv number;
    begin
      for rec1 in cur_query loop
      a:=0;
      if rec1.data_type ='VARCHAR2' or rec1.data_type='CHAR' THEN
      a := 1;
      end if;
      if a>0 then
      sql_hard := '';
      sql_hard := 'SELECT count(*) FROM  '|| rec1.table_name ||' where '
      ||rec1.column_name|| ' like''00110000%'''; -- 改成我们要查询的值  如果是确定某个值的话,就直接用 = 
      --dbms_output.put_line(sql_hard); 
      execute immediate sql_hard INTO vv;
      IF vv > 0 THEN
       dbms_output.put_line(rec1.table_name||'--'||rec1.column_name);
      end if; 
      END IF;
      end loop;
    end;

SQL Server 的方式 :

declare @key varchar(30)
    set @key = 'ICG'    --替换为要查找的字符串
    DECLARE @tabName VARCHAR(40),@colName VARCHAR(40)
    
    declare @sql VARCHAR(2000)
    declare @tsql varchar(8000)
    
    DECLARE tabCursor CURSOR FOR
    SELECT name From sysobjects WHERE xtype = 'u' AND name <> 'dtproperties'
    
    OPEN tabCursor
    
    FETCH NEXT FROM tabCursor INTO @tabName
    WHILE @@fetch_status = 0
    BEGIN
     set @tsql = ''
     DECLARE colCursor CURSOR FOR Select Name FROM SysColumns Where id=Object_Id(@tabName) and xtype=167
     OPEN colCursor
     FETCH NEXT FROM colCursor INTO @colName
     WHILE @@fetch_status = 0
     BEGIN
      SET @sql = 'if(exists(select * from ' + @tabName + ' where '
      SET @sql = @sql + @colName + ' like ''%' + @key + '%'')) begin  select * from '
      set @sql = @sql + @tabName + ' where ' + @colName + ' like ''%' + @key + '%'';select '''
       + @tabName + ''' as TableName  end'
      set @tsql = @tsql + @sql + ';'
      FETCH NEXT FROM colCursor INTO @colName
     END
     exec(@tsql)
     CLOSE colCursor
     DEALLOCATE colCursor
     FETCH NEXT FROM tabCursor INTO @tabName
    END
    
    CLOSE tabCursor
    
    DEALLOCATE tabCurso

转自:https://www.cnblogs.com/wuyifu/archive/2013/06/06/3120307.html  侵删

[oracle plsql _ _ _ _ _ sql server _ _ _]: https://www.cnblogs.com/wuyifu/archive/2013/06/06/3120307.html