如何将选择结果存储到Oracle过程中的变量中

发布于 2024-08-03 04:23:33 字数 232 浏览 5 评论 0原文

我写了一个简单的程序。我尝试将选择结果存储在变量中。我使用“SELECT INTO”查询，但我不能这样做。

例子：

DECLARE
     v_employeeRecord  employee%ROWTYPE;
BEGIN
 SELECT * INTO v_employeeRecord
      FROM Employee WHERE Salary > 10;
END;

原文

I write a simple procedure. I try to store selection result in variable. I use "SELECT INTO" query but I can not doing this.

Example:

DECLARE
     v_employeeRecord  employee%ROWTYPE;
BEGIN
 SELECT * INTO v_employeeRecord
      FROM Employee WHERE Salary > 10;
END;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

流云如水 2024-08-10 04:23:33

你有几个选择。您可以将该查询转换为游标：

DECLARE
     CURSOR v_employeeRecords IS
          SELECT * FROM Employee WHERE Salary > 10;
     v_employeeRecord  employee%ROWTYPE;
BEGIN
     FOR v_employeeRecord IN v_employeeRecords LOOP
          /* Do something with v_employeeRecord */
     END LOOP;
END;

或者，您可以创建一个 TABLE 变量：

DECLARE
     v_employeeRecord  employee%ROWTYPE;
     v_employeeRecords IS TABLE OF employee%ROWTYPE;
     i BINARY_INTEGER;
BEGIN
 SELECT * BULK COLLECT INTO v_employeeRecords
      FROM Employee WHERE Salary > 10;

 i := v_employeeRecords.FIRST;
 WHILE v_employeeRecords.EXISTS(i) LOOP
     v_employeeRecord := v_employeeRecords(i);
     /* Do something with v_employeeRecord */
     i := v_employeeRecords.NEXT(i);
 END;
END;

我还没有在 Oracle 中尝试过这些示例，因此您可能会遇到编译器错误...

You have a couple options. You could turn that query into a cursor:

DECLARE
     CURSOR v_employeeRecords IS
          SELECT * FROM Employee WHERE Salary > 10;
     v_employeeRecord  employee%ROWTYPE;
BEGIN
     FOR v_employeeRecord IN v_employeeRecords LOOP
          /* Do something with v_employeeRecord */
     END LOOP;
END;

Or, you can create a TABLE variable:

DECLARE
     v_employeeRecord  employee%ROWTYPE;
     v_employeeRecords IS TABLE OF employee%ROWTYPE;
     i BINARY_INTEGER;
BEGIN
 SELECT * BULK COLLECT INTO v_employeeRecords
      FROM Employee WHERE Salary > 10;

 i := v_employeeRecords.FIRST;
 WHILE v_employeeRecords.EXISTS(i) LOOP
     v_employeeRecord := v_employeeRecords(i);
     /* Do something with v_employeeRecord */
     i := v_employeeRecords.NEXT(i);
 END;
END;

I haven't tried these samples in Oracle, so you may get compiler errors...

回复收藏 0 原文

Spring初心 2024-08-10 04:23:33

如果您的 SELECT 返回多于一行，您将无法使用 SELECT INTO 合成。

您需要构建一个循环来浏览结果集：

Adam 演示了如何使用显式游标和批量收集循环。我将展示如何构建最简单的循环（隐式游标，不需要 DECLARE 部分）：

BEGIN
   FOR c_emp IN (SELECT * 
                   FROM Employee 
                  WHERE Salary > 10) LOOP
      /* do something with each row, for example:*/
      UPDATE foo SET bar = bar + c_emp.salary WHERE id = c_emp.id;
   END LOOP;
END;

IF your SELECT returns more than one row, you won't be able to use the SELECT INTO synthax.

You will need to build a loop to navigate through the resulte set:

Adam demonstrated how you would use an explicit cursor and a bulk collect loop. I will show how you can build the simplest loop possible (implicit cursor, doesn't need a DECLARE section):

BEGIN
   FOR c_emp IN (SELECT * 
                   FROM Employee 
                  WHERE Salary > 10) LOOP
      /* do something with each row, for example:*/
      UPDATE foo SET bar = bar + c_emp.salary WHERE id = c_emp.id;
   END LOOP;
END;

回复收藏 0 原文

~没有更多了~