PL/SQL 游标
在处理SQL语句时,Oracle将创建一个称为上下文区域的内存区域。光标是指向此上下文区域的指针。它包含处理语句所需的所有信息。在PL/SQL中,上下文区域由Cursor控制。游标包含有关select语句和它所访问的数据行的信息。
游标用于引用程序,一次读取和处理SQL语句返回的行。游标有两种类型:
隐式游标
显式光标
1)PL/SQL隐式游标
如果您不使用显式游标,则Oracle在执行SQL语句时会自动生成隐式游标
默认情况下创建这些语句,以便在执行INSERT,UPDATE,DELETE等DML语句时处理语句。
Orcale提供了一些称为隐式游标属性的属性检查DML操作的状态。其中一些是: %FOUND,%NOTFOUND,%ROWCOUNT和%ISOPEN。
例如: 当您执行INSERT,UPDATE,DELETE之类的SQL语句时,游标属性会告知是否影响了任何行以及影响了多少行。如果在PL/SQL块中运行SELECT INTO语句,则隐式游标属性可用于查找SELECT语句是否返回了任何行。如果没有选择任何数据,它将返回错误。
下表通过其每个属性来区分光标的状态。
属性 |
说明 |
%FOUND |
如果INSERT,DELETE和UPDATE之类的DML语句影响至少一行或更多行,或者SELECT INTO语句返回一行或更多行,则其返回值为TRUE。否则返回FALSE。 |
%NOTFOUND |
如果INSERT,DELETE和UPDATE之类的DML语句不影响任何行,或者SELECT INTO语句不返回任何行,则其返回值为TRUE。否则返回FALSE。它与%FOUND正好相反。 |
%ISOPEN |
对于隐式游标,它始终返回FALSE,因为在执行其关联的SQL语句后,SQL游标会自动关闭。 |
%ROWCOUNT |
它返回受DML语句(如INSERT,DELETE和UPDATE)影响或由SELECT INTO语句返回的行数。 |
PL/SQL隐式游标示例
创建客户表并具有记录:
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
23 |
Allahabad |
20000 |
2 |
Suresh |
22 |
Kanpur |
22000 |
3 |
Mahesh |
24 |
Ghaziabad |
24000 |
4 |
Chandan |
25 |
Noida |
26000 |
5 |
Alex |
21 |
Paris |
28000 |
6 |
Sunita |
20 |
Delhi |
30000 |
让我们执行以下程序来更新表并将每个客户的薪水提高5000。在这里,SQL%ROWCOUNT属性用于确定受影响的行数:
创建过程:
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 5000;
if sql%notfound THEN
dbms_output.put_line('no customers updated');
ELSif sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers updated ');
END IF;
END;
/
输出:
6 customers updated
PL/SQL procedure successfully completed.
现在,如果检查客户表中的记录,您会发现行已更新。
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
23 |
Allahabad |
25000 |
2 |
Suresh |
22 |
Kanpur |
27000 |
3 |
Mahesh |
24 |
Ghaziabad |
29000 |
4 |
Chandan |
25 |
Noida |
31000 |
5 |
Alex |
21 |
Paris |
33000 |
6 |
Sunita |
20 |
Delhi |
35000 |
2)PL/SQL显式游标
显式游标由程序员定义,以获取对上下文区域的更多控制。这些游标应在PL/SQL块的声明部分中定义。它是在SELECT语句上创建的,该语句返回多个行。
以下是创建显式游标的语法:
显式游标的语法
以下是创建显式游标的语法:
CURSOR cursor_name IS select_statement;;
步骤:
在使用显式游标时,您必须遵循以下步骤。
声明光标以在内存中初始化。
打开游标以分配内存。
获取光标以检索数据。
关闭光标以释放分配的内存。
1)声明游标:
它使用名称和关联的SELECT语句定义游标。
显式游标清理的语法
CURSOR name IS
SELECT statement;
2)打开游标:
它用于为游标分配内存,并使将SQL语句返回的行轻松提取到其中。
打开光标的语法:
3)获取游标:
它用于一次访问一行。您可以按如下所示从上面打开的游标中获取行:
获取游标的语法:
FETCH cursor_name INTO variable_list;
4)关闭游标:
它用于释放分配的内存。以下语法用于关闭上面打开的游标。
游标关闭的语法:
PL/SQL显式游标示例
程序员定义了显式游标,以获取对上下文区域的更多控制。它在PL/SQL块的声明部分中定义。它是在SELECT语句上创建的,该语句返回多个行。
让我们以一个示例来演示显式游标的用法。在此示例中,我们使用已经创建的CUSTOMERS表。
创建客户表并具有记录:
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
23 |
Allahabad |
20000 |
2 |
Suresh |
22 |
Kanpur |
22000 |
3 |
Mahesh |
24 |
Ghaziabad |
24000 |
4 |
Chandan |
25 |
Noida |
26000 |
5 |
Alex |
21 |
Paris |
28000 |
6 |
Sunita |
20 |
Delhi |
30000 |
创建过程:
执行以下程序以检索客户名称和地址。
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/
输出:
1 Ramesh Allahabad
2 Suresh Kanpur
3 Mahesh Ghaziabad
4 Chandan Noida
5 Alex Paris
6 Sunita Delhi
PL/SQL procedure successfully completed.