SQL 遍历所有表

本文介绍了一个使用SQL Server存储过程实现的全文搜索功能。通过两个游标遍历所有表及字符型字段,查找包含指定字符串的数据,并返回相关表名及记录数。此方法适用于早期SQL Server版本中未内置全文搜索功能的情况。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

ALTER proc Full_Search(@string varchar(50))

as

begin

 

declare @tbname varchar(50)

declare tbroy cursor for select name from sysobjects where xtype= 'u ' --第一个游标遍历所有的表

 

open tbroy

fetch next from tbroy into @tbname while @@fetch_status=0

begin

declare @colname varchar(50)

declare colroy cursor for select name from syscolumns

where id=object_id(@tbname) and xtype in (

select xtype from systypes where name in ( 'varchar ', 'nvarchar ', 'char ', 'nchar ') --数据类型为字符型的字段

) --第二个游标是第一个游标的嵌套游标,遍历某个表的所有字段

 

open colroy

fetch next from colroy into @colname while @@fetch_status=0

begin

 

declare @sql nvarchar(1000),@j int

select @sql= 'select @i=count(1) from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%'''

exec sp_executesql @sql,N'@i int output',@i=@j output --输出满足条件表的记录数

if @j> 0

BEGIN

select 包含字串的表名=@tbname

--exec( 'select distinct '+@colname+' from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%''')

END

fetch next from colroy into @colname

end

close colroy

 

deallocate colroy

 

fetch next from tbroy into @tbname

end

close tbroy

deallocate tbroy

end

go

 

exec Full_Search '123'

转载于:https://www.cnblogs.com/nickdeng/archive/2010/11/23/1885763.html

<think>我们正在讨论如何在PL/SQL遍历数据。根据用户的问题,他们希望了解在PL/SQL遍历数据的方法。根据之前的引用,我们有几种方法可以实现。 在PL/SQL中,遍历数据通常有以下几种方式: 1. 使用显式游标(Explicit Cursor) 2. 使用隐式游标(Implicit Cursor),例如FOR循环 3. 使用BULK COLLECT和FORALL(用于批量处理,但遍历时通常结合循环) 4. 使用集合(例如嵌套、索引)的遍历方法(如引用[1]中所示,但那是遍历一个集合,而不是直接遍历) 用户的问题中提到了引用[1]是关于PL/SQL中TABLE类型的遍历,引用[2]是关于PL/SQL的基本语法(包括游标),引用[3]是PostgreSQL的数组遍历,与当前问题关系不大。 因此,我将重点介绍在PL/SQL中如何遍历数据(即从数据库中读取数据并逐行处理)的常用方法。 方法一:使用显式游标 步骤: a. 声明游标 b. 打开游标 c. 循环获取数据 d. 处理数据 e. 关闭游标 方法二:使用隐式游标(FOR循环) 这种方法更简洁,Oracle会自动管理游标的打开、获取和关闭。 方法三:使用BULK COLLECT(批量收集)结合FOR循环 当数据量较大时,可以使用批量收集来减少上下文切换,提高性能。然后使用普通的FOR循环或FORALL(但FORALL用于DML,不能用于遍历处理,所以遍历处理时用FOR循环)。 下面我将分别给出示例: ### 方法一:显式游标 ```sql DECLARE CURSOR c_emp IS SELECT employee_id, last_name, salary FROM employees; v_emp_id employees.employee_id%TYPE; v_name employees.last_name%TYPE; v_salary employees.salary%TYPE; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO v_emp_id, v_name, v_salary; EXIT WHEN c_emp%NOTFOUND; -- 在这里处理每一行数据,例如输出 DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_name || ', Salary: ' || v_salary); END LOOP; CLOSE c_emp; END; / ``` ### 方法二:隐式游标(FOR循环) ```sql DECLARE BEGIN FOR emp_rec IN (SELECT employee_id, last_name, salary FROM employees) LOOP -- 直接使用emp_rec.列名访问 DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ', Name: ' || emp_rec.last_name || ', Salary: ' || emp_rec.salary); END LOOP; END; / ``` ### 方法三:使用BULK COLLECT(批量获取)和FOR循环 这种方法适合处理大量数据,减少与SQL引擎的交互次数。 ```sql DECLARE -- 定义记录类型 TYPE emp_tab IS TABLE OF employees%ROWTYPE; v_emps emp_tab; BEGIN -- 一次性获取所有数据(也可以使用LIMIT分批) SELECT * BULK COLLECT INTO v_emps FROM employees; -- 然后遍历集合 FOR i IN 1 .. v_emps.COUNT LOOP DBMS_OUTPUT.PUT_LINE('ID: ' || v_emps(i).employee_id || ', Name: ' || v_emps(i).last_name || ', Salary: ' || v_emps(i).salary); END LOOP; END; / ``` 注意:第三种方法如果数据量非常大,一次性获取所有数据可能会消耗大量PGA内存,因此可以使用LIMIT子句分批获取,示例如下: ### 方法三(改进):使用BULK COLLECT with LIMIT ```sql DECLARE CURSOR c_emp IS SELECT * FROM employees; -- 定义集合类型和变量 TYPE emp_tab IS TABLE OF employees%ROWTYPE; v_emps emp_tab; -- 每次获取的行数 v_limit NUMBER := 100; BEGIN OPEN c_emp; LOOP FETCH c_emp BULK COLLECT INTO v_emps LIMIT v_limit; EXIT WHEN v_emps.COUNT = 0; -- 没有数据时退出 -- 处理当前批次的数据 FOR i IN 1 .. v_emps.COUNT LOOP DBMS_OUTPUT.PUT_LINE('ID: ' || v_emps(i).employee_id || ', Name: ' || v_emps(i).last_name || ', Salary: ' || v_emps(i).salary); END LOOP; END LOOP; CLOSE c_emp; END; / ``` 总结: - 对于小数据量,推荐使用方法二(隐式游标FOR循环),因为它简洁且不易出错。 - 对于大数据量,推荐使用方法三(带LIMIT的BULK COLLECT),以提高性能并控制内存使用。 另外,引用[1]中展示的是遍历一个已经存在的集合(TABLE类型),而不是直接从中读取。但我们可以通过将数据先批量收集到集合中,然后遍历集合,这就是方法三。 请根据实际需求选择合适的方法。 §§相关问题§§ 1. 如何在PL/SQL中处理游标异常? 2. PL/SQL中BULK COLLECT与FORALL的区别是什么? 3. 如何优化PL/SQL中大数据量的处理? 4. 在PL/SQL中,游标变量和静态游标的区别是什么?</think>在 PL/SQL遍历数据主要有以下几种方法,根据性能和需求场景选择合适的方式: ### 方法一:使用 FOR 循环(隐式游标) ```sql BEGIN FOR rec IN (SELECT employee_id, last_name, salary FROM employees) LOOP DBMS_OUTPUT.PUT_LINE( 'ID: ' || rec.employee_id || ', Name: ' || rec.last_name || ', Salary: ' || rec.salary ); END LOOP; END; ``` **特点**: - 最简洁的写法,Oracle 自动管理游标开闭 - 适用于中小型数据集 - 无需显式声明游标变量[^2] --- ### 方法二:显式游标遍历 ```sql DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name, salary FROM employees; emp_rec emp_cursor%ROWTYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_rec; EXIT WHEN emp_cursor%NOTFOUND; -- 遍历结束条件 DBMS_OUTPUT.PUT_LINE( 'ID: ' || emp_rec.employee_id || ', Name: ' || emp_rec.last_name ); END LOOP; CLOSE emp_cursor; END; ``` **特点**: - 完整控制游标生命周期 - 适合需要异常处理的场景 - 可配合 `%ROWCOUNT` 获取行计数 --- ### 方法三:BULK COLLECT + FORALL(高性能批处理) ```sql DECLARE TYPE emp_tab IS TABLE OF employees%ROWTYPE; v_emps emp_tab; BEGIN SELECT * BULK COLLECT INTO v_emps FROM employees; FOR i IN 1..v_emps.COUNT LOOP DBMS_OUTPUT.PUT_LINE( 'ID: ' || v_emps(i).employee_id || ', Salary: ' || v_emps(i).salary ); END LOOP; END; ``` **特点**: - 批量获取数据,减少上下文切换 - 适合处理大型数据集(10,000+行) - 可添加 `LIMIT` 子句分批次处理[^1] --- ### 方法四:集合遍历(内存) ```sql DECLARE TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER; emp_data emp_table; i PLS_INTEGER; BEGIN -- 填充数据到集合 SELECT * INTO emp_data(1) FROM employees WHERE employee_id = 100; SELECT * INTO emp_data(2) FROM employees WHERE employee_id = 101; -- 遍历集合 i := emp_data.FIRST; WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE(emp_data(i).last_name); i := emp_data.NEXT(i); -- 移动到下一个元素[^1] END LOOP; END; ``` --- ### ⚠️ 关键注意事项 1. **性能选择**: - 小数据集:推荐 FOR 循环 - 大数据集:使用 BULK COLLECT + LIMIT 分页 ```sql FETCH emp_cursor BULK COLLECT INTO v_emps LIMIT 1000; -- 每次取1000行 ``` 2. **异常处理**: ```sql EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No records found'); WHEN OTHERS THEN RAISE; ``` 3. **动态SQL遍历**: ```sql EXECUTE IMMEDIATE 'SELECT * FROM employees' BULK COLLECT INTO v_emps; ``` > **提示**:使用 `DBMS_OUTPUT.ENABLE(buffer_size => NULL)` 可输出无限制长度的调试信息。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值