oracle存储过程 游标 比对临时表和业务表 相同数据找出来 做标记 在插入临时表中 代码怎么写

oracle存储过程 游标 比对临时表和业务表 相同数据找出来 做标记 在插入临时表中 代码怎么写

游标效率比临时表效率高
临时表如下：

SELECt EmpCo FROM Employees

select identity(int,1,1) as id,* into #T from Employees --第一条搜索语句
declare @i int
declare @total int
declare @EmpCo varchar(10)
select @total = count(*) from #T
set @i = 1
while @i <= @total
begin
select @EmpCo=EmpCo from #T where id = @i --第二条搜索语句，而且是循环执行的。
print @EmpCo
set @i = @i + 1
end

drop table #T

-----------------------------------------------
游标如下：

Declare @EmpCo as varchar(50)
Declare @EmpCo1 as varchar(50)

DECLARE Employee_Cursor CURSOR FOR
SELECT EmpCo FROM Employees ---只有在这有一条搜索语句
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor INTO @EmpCo
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor INTO @EmpCo
print @EmpCo
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

临时表的，要用两条搜索语句，而且是循环执行的。 ；然而，游标的只用一条搜索语句，没有循环执行的循环语句

使用merge into就可以了。 示例 merge into bonuses d using (select employee_id, salary, department_id from employees where department_id = 80) s on (d.employee_id = s.employee_id) when matched then update set d.bonus = d.bonus + s.salary*.01 delete where (s.salary > 8000) when not matched then insert (d.employee_id, d.bonus) values (s.employee_id, s.salary*.01) where (s.salary <= 8000);