Sql游标的缺点
我正在研究光标，我在某处读到过。 每次从游标中获取一行时，都会导致一次网络往返，而无论结果集有多大，普通的选择查询都只会进行一次往返。
任何人都可以解释一下吗？这意味着什么？并通过一些例子详细说明了网络往返和单次往返的含义。当我们使用游标和当我们使用 while 循环时？
I was studying cursor and I read somewhere that. Each time you fetch a row from the cursor, it results in a network round trip whereas normal select query makes only one round trip however large the resultset is.
Can anyone explain what does that means? And what does network round trip and one round trip means in detail with some example. And when we use cursor and when we use while loop?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
几乎在所有数据库系统中，使用数据的应用程序；以及负责存储和搜索数据的DBMS；住在不同的机器上。他们通过网络相互交谈。即使它们位于同一台计算机上，仍然有效地存在网络连接。
这很重要，因为在应用程序决定它已准备好读取数据、该请求通过网络到达数据库服务器、数据库服务器实际收到该响应以及响应最终通过网络到达之间存在一段时间。应用端的网络。
当你查询一整套数据时，你只需要支付一次这个费用；尽管这看起来很浪费；事实上，将保留所有数据的负担放在应用程序上要高效得多，因为向应用程序提供更多资源通常比在数据库服务器上执行相同操作更容易。
当您的应用程序一次只获取一行数据时，那么应用程序和数据库之间的往返成本将为每行支付一次；如果您想要显示 100 篇博客文章的标题，那么您需要为该报告支付 100 次数据库往返的费用。更糟糕的是，数据库服务器必须以某种方式跟踪部分完成的结果集。这通常意味着可用于查询另一个请求的数据的资源将被一个应用程序保留，而该应用程序并没有请求它最初请求的所有数据。
基本规则是仅在绝对必要时才与数据库对话，并使交互尽可能短；这意味着您只请求真正需要的数据（让数据库尽可能多地进行过滤，而不是在应用程序中进行）并尽快接受所有数据，以便数据库可以转移到另一个数据库任务。
In virtually all database systems, the application that uses the data; and the DBMS that is responsible for storing and searching the data; live on separate machines. They talk to each other over a network. Even when they are on the same machine, there is still effectively a network connection.
This matters because there is some time between when an application decides that it's ready to read data, when that request arrives over the network at the database server, when the database server actually gets the response for that, and when the response finally arrives over the network on the application side.
When you do a query for a whole set of data, you only pay this cost once; Although it may seem wasteful; in fact it's much more efficient to put the burden of holding on to all of the data on the application, because it's usually easier to give more resources to an application than to do the same on a database server.
When your application only fetches data one row at a time, then the cost of the round trip between application and database is paid once per row; If you want to show the titles of 100 blog posts, then you're paying the cost of 100 round trips to the database for that one report. Whats worse is that the database server has to some how keep track of the partially completed result set. That usually means that the resources that could be used for querying data for another request are instead being retained by an application that hasn't happened to ask for all of the data it originally asked for.
The basic rule is to talk to the database only when you absolutely have to, and to make the interaction as short as possible; This means you only ask for the data you really need (have the database do as much filtering as possible, instead of doing it in the application) and accept all of the data as quickly as possible, so that the database can move on to another task.
不幸的是，该引用是不正确的。
“普通 SELECT”创建客户端从中获取的游标。该机制与打开并返回 SYS_REFCURSOR （或用于打开游标的任何其他机制）完全相同。在这两种情况下，客户端每次从数据库请求数据时都会通过网络获取许多行。客户端可以控制每次获取的行数——客户端在一次网络往返中获取 1 行或从游标中获取所有行的情况非常罕见。
当客户端应用程序从游标获取数据时（无论游标如何打开），客户端应用程序通过网络发送 N 行请求（同样，客户端控制 N 的值），实际会发生什么。数据库将接下来的 N 行发送回客户端（通常，Oracle 必须继续执行查询才能确定接下来的 N 行，因为 Oracle 通常不会具体化整个结果集）。客户端应用程序对这 N 行执行某些操作，然后通过网络发送另一个请求以获取接下来的 N 行，并且重复该模式。
Unfortunately, that reference is incorrect.
A "normal SELECT" creates a cursor that the client fetch from. The mechanics are exactly the same as if you open and return a
SYS_REFCURSOR(or any other mechanism for opening a cursor). In both cases, the client will fetch a number of rows over the network every time it requests data from the database. The client can control the number of rows that are fetched each time-- it would be exceptionally rare for the client to fetch 1 row or to fetch all the rows from a cursor in a single network round-trip.What actually happens when a client application fetches from a cursor (no matter how the cursor is opened), the client application sends a request over the network for N rows (again, the client controls the value of N). The database sends the next N rows back to the client (generally, Oracle has to continue executing the query in order to determine the next N rows because Oracle does not generally materialize an entire result set). The client application does something with those N rows and then sends another request over the network for the next N rows and the pattern repeats.