时间戳

首页 > 解决方案 > 如何在另一个游标内声明游标

sql - 如何在另一个游标内声明游标

问题描述

如何在游标内声明另一个游标c_employees？

注意：游标c_employees返回employees_id和这个id传递给where子句中的其他游标。

例如

cursor c_employees
        is
            Select employees_id from employees;
begin
    for e in c_employees loop
        begin
           --How here define other cursor from cursor_employees I get employees_id and put as parameter.

           cursor c_leaves is Select hours from my_table where employees_id = e.employees_id;
           for j in c_leaves loop
                begin
                   Insert into table2(......
                end;
           end loop;

标签： sqloracleplsql

解决方案

游标因此可以带参数

cursor c_employees    is
            Select employees_id from employees;
cursor c_leaves(e_id int)
  is Select hours from my_table where employess_id = e_id;
            
begin
    for e in c_employees loop
        begin
           for j in c_leaves(e.employees_id) loop
                begin
                   Insert into table2(......
                end;
           end loop;

但一般来说，当您开始看到带有游标等的游标时......也是时候看看查询是否可以用 JOIN 替换。例如，上面的代码可能只是

insert into table2
select ..
from employees e, my_table m
where e.employess_id = m.employess_id;

推荐阅读