游标有什么问题？ [英] What is wrong with Cursors?

问题描述

SQL Server开发人员认为游标是一种不好的做法，除非在某些情况下。他们认为游标不会最佳地使用SQL引擎，因为它是一个过程结构，并且违反了基于集合的RDBMS的概念。

SQL Server developers consider Cursors a bad practise , except under some circumstances. They believe that Cursors do not use the SQL engine optimally since it is a procedural construct and defeats the Set based concept of RDBMS.

但是，Oracle开发人员似乎不推荐使用Cursors。 Oracle的DML语句本身是隐式游标。

However, Oracle developers do not seem to recommend against Cursors. Oracle's DML statements themselves are implicit cursors.

为什么这种方法有所不同？

Why this difference in approach ? Is it because of the way these 2 products are made , or does this advise apply to both products?

推荐答案

游标出了什么问题，是因为这两个产品的制作方式，是它们在 Oracle 和 MS SQL 中经常被滥用。

What's wrong with cursors is that they are often abused, both in Oracle and in MS SQL.

游标用于保持稳定的结果集，您可以逐行检索。

Cursor are for keeping a stable resultset which you can retrieve row-by-row. They are implicitly created when your query is run, and closed when it's finished.

当然保持这样的结果集需要一些资源： locks

Of course keeping such a resultset requires some resources: locks, latches, memory, even disk space.

这些资源释放得越快越好。

The faster these resources are freed, the better.

就像保持冰箱门打开

你不会在没有必要的情况下做几个小时，但这并不意味着你不应该打开你的冰箱。

这意味着：

<您可以调用 SQL 的 SUM 来逐行获取结果并求和：

• 您不执行整个查询并从游标获取第一个结果：您在查询中附加了 rownum <= 10 条件

• You don't get your results row-by-row and sum them: you call the SQL's SUM instead.
• You don't execute whole query and get the first results from the cursor: you append a rownum <= 10 condition to your query

等。

，在处理过程中的游标需要臭名昭着的 SQL / PLSQL上下文切换，每次你得到一个

As for Oracle, processing your cursors inside a procedure requires infamous SQL/PLSQL context switch which happens every time you get a result of an SQL query out of the cursor.

它涉及在线程之间传递大量数据并同步线程。

It involves passing large amounts of data between threads and synchronizing the threads.

这是 Oracle 中最刺激的事情之一。

This is one of the most irritating things in Oracle.

创建触发器并调用 DML 函数等于打开游标选择更新的行并为此游标的每一行调用触发器代码。

Creating a trigger and calling a DML function is equal to opening the cursor selecting the updated rows and calling the trigger code for each row of this cursor.

只是存在触发器（即使是空触发器）可能会减缓 DML 操作 10次或更多。

Mere existence of the trigger (even the empty trigger) may slow down a DML operation 10 times or more.

10g 上的测试脚本：

SQL> CREATE TABLE trigger_test (id INT NOT NULL)
  2  /

Table created

Executed in 0,031 seconds
SQL> INSERT
  2  INTO   trigger_test
  3  SELECT level
  4  FROM   dual
  5  CONNECT BY
  6     level <= 1000000
  7  /

1000000 rows inserted

Executed in 1,469 seconds
SQL> COMMIT
  2  /

Commit complete

Executed in 0 seconds
SQL> TRUNCATE TABLE trigger_test
  2  /

Table truncated

Executed in 3 seconds
SQL> CREATE TRIGGER trg_test_ai
  2  AFTER INSERT
  3  ON trigger_test
  4  FOR EACH ROW
  5  BEGIN
  6     NULL;
  7  END;
  8  /

Trigger created

Executed in 0,094 seconds
SQL> INSERT
  2  INTO   trigger_test
  3  SELECT level
  4  FROM   dual
  5  CONNECT BY
  6     level <= 1000000
  7  /

1000000 rows inserted

Executed in 17,578 seconds

1.47 秒没有触发器， 17.57 秒，空触发器不执行任何操作。

1.47 seconds without a trigger, 17.57 seconds with an empty trigger doing nothing.

这篇关于游标有什么问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！