如何将选择结果存储到Oracle过程中的变量中 [英] How to store selection result in to variable in Oracle procedure

问题描述

我写了一个简单的过程.我尝试将选择结果存储在变量中.我使用"SELECT INTO"查询，但无法执行此操作.

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

示例:

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

推荐答案

您有两种选择.您可以将该查询转换为光标:

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;

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

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;

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

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

这篇关于如何将选择结果存储到Oracle过程中的变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！