我应该在这里使用 SQL 游标吗？
我最近读到了有关如何避免游标的内容。 好吧，我想知道我对它们的使用是否合适。
我正在创建一个可以在线工作的脚本引擎（嵌入在页面中，服务器端） 该脚本引擎将由该产品的“高级”最终用户使用。 然而，该产品与数据库的配合非常紧密，脚本语言类似于 C，但也被简化为类似于 PHP。 对于数据库，我基本上想要这样的语法，因为它是语言中可创建的最一致的语法，而最终用户不必手动编写 SQL 代码（如果我们要让他们这样做，为什么他们不能跳过脚本引擎是为了让生活更简单）。 语法是这样的：
declare DataSet $data("tablename","OtherID="+$oid);
//Dataset(string tablename,string where_clause_addon)
$data["field1"]="the data of field... ";
$data.Next();
$data["field1"]="The data of the next row";
$data[10]["field1"]="The data of the 10th row";
我通过为每个 DataSet 创建一个全局游标（我在应用程序中只使用 1 个连接）在内部控制它，然后让全局游标跟踪当前行位置（它也是一个 SCROLL 和 UPDATE 游标） ）。 这让我的生活变得更加简单，否则我将被迫编写自己的 SQL 控件来对抗 .Net 糟糕的 DataReader。
游标的这种用法可以吗？ 请注意，包含这些脚本的页面将无法在全球范围内访问，它仅适用于客户端（因此可能一次只能访问 3-10 个用户）。
有谁看到更好的方法来跟踪当前变量位置？ （因为它们能够处理未知模式的表）
另外，使用这样的游标的并发性会有任何问题吗？ （我的文档说游标对于连接来说是全局的，每个页面请求都会当场建立一个新连接，因此用户不会共享连接）
I have recently read about how cursors should be avoided. Well, I would like to know if my usage of them is appropriate.
I am creating a scripting engine that will work online(embedded in a page, server-side) This scripting engine will be used by "advanced" end users of this product. The product works very heavily with the database however, and the scripting language is C-like, but simplified to where it also resembles PHP. For databases I basically want a syntax like this, as it is the most consistent syntax creatable within the language without the end user having to hand write SQL code(if we are going to make them do that, why can't they just skip the scripting engine as its there to make life simpler). The syntax is something like this:
declare DataSet $data("tablename","OtherID="+$oid);
//Dataset(string tablename,string where_clause_addon)
$data["field1"]="the data of field... ";
$data.Next();
$data["field1"]="The data of the next row";
$data[10]["field1"]="The data of the 10th row";
I control this internally by creating a global cursor for each DataSet (I only use 1 connection in the application) and then letting the global cursor keep track of the current row position(its a SCROLL and UPDATE cursor also). This makes my life much simpler as otherwise I would be forced to write my own SQL controls to combat .Net's sucky DataReader.
Is this usage of cursors an OK one? Note that the page with these scripts will not be world wide accessible, its only for clients(so probably only like 3-10 users at once).
Does anyone see a better method of keeping track of the current variable location? (as these are able to address tables of unknown schema)
Also, would I have any problems with concurrency using cursors like this? (My docs say cursors are global to the connection, and each page request makes a new connection on the spot, so users aren't sharing connections)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
“我最近读到了有关如何避免使用光标的内容。”
它们是无法避免的。
“我读到的更多内容是，不一定要避免使用游标，而是游标速度很慢，应该只在某些情况下使用。”
正确——在某些情况下。
尽可能使用SQL语句来做。 不要通过打开游标并执行一堆 if 语句来发明自己的 SELECT 处理逻辑：使用 WHERE 子句。 不要通过打开游标并读取每一行来发明自己的 GROUP-BY 处理：使用 GROUP BY 子句。 不要使用嵌套游标循环发明自己的连接算法：使用正确的 JOIN 子句。
不要使用游标发明自己的 SQL。 使用 SQL。
一般来说，“不要使用游标”的人说的是“不要使用游标重新发明 SQL 算法”。 这不是一个宽泛、模糊的“不要使用光标”。 这是非常具体的建议：“学习 SQL，不要通过编写游标循环来弥补知识空白。”
只要您不重新发明现有的 SQL 算法，就有很多事情必须使用游标。
不要迷恋“避免光标”。 不要迷恋尽可能多地使用“纯 SQL”进行合理的操作。
当您在光标/无光标编程中绞尽脑汁时，不要进行过早的优化。 只需编写尽可能最好的 SQL 并尽早并经常进行性能基准测试即可。
如果您无法找出最好的 SQL，请在这里询问最好的 SQL——不要迷恋游标。
"I have recently read about how cursors should be avoided."
They can't be avoided.
"I've read more that cursors shouldn't necessarily be avoided, but rather that cursors are slow and should only be used in certain circumstances."
Correct -- in certain situations.
Use SQL statements to do as much as possible. Don't invent your own SELECT processing logic by opening a cursor and doing a bunch of if-statements: use a WHERE clause. Don't invent your own GROUP-BY processing by opening a cursor and reading each row: use a GROUP BY clause. Don't invent your own join algorithm by using nested cursor loops: use a proper JOIN clause.
Don't invent your own SQL using cursors. Use SQL.
Generally, the "don't use cursors" folks are saying "Don't reinvent SQL algorithms by using a cursor". This is not a broad, vague "don't use cursors". It's very specific advice: "Learn SQL, don't work around gaps in your knowledge by writing cursor loops."
So long as you're not reinventing an existing SQL algorithm, there are many things for which you must use cursors.
Don't fetishize over "avoiding cursors". Do fetishize over doing as much in "pure SQL" as is reasonable.
Don't engage in premature optimization as you wring your hands over cursor/no-cursor programming. Simply write the best SQL you can and benchmark performance early and often.
If you can't figure out the best SQL, ask here for the best SQL -- without fetishizing over cursors.