英文:
Question: i've found a strange function that use an externally declared variable inside a cursor where clause
问题
The first code snippet you provided appears to be a PL/SQL function where a cursor is used with variables in the WHERE clause. However, there are some syntax errors in your code, like missing semicolons and assignment statements. Here's a corrected version:
create FUNCTION VERY_STRANGE_FUNCTION(
input_field IN VARCHAR2,
input_field_value IN VARCHAR2
)
RETURN VARCHAR2
IS
out_RESULT VARCHAR2(4000);
BEGIN
DECLARE
var_ID_ORDER VARCHAR2(100);
var_ID_ORDER_2 VARCHAR2(100);
OUTPUT_VAR VARCHAR2(4000);
BEGIN
var_ID_ORDER := input_field;
var_ID_ORDER_2 := input_field_value;
FOR rec IN (SELECT ID, NAME, COMPANY
FROM ORDERS
WHERE ORDER_ID1 = var_ID_ORDER
AND ORDER_ID2 = var_ID_ORDER_2)
LOOP
OUTPUT_VAR := rec.COMPANY;
END LOOP;
RETURN (OUTPUT_VAR);
END;
END;
Regarding your question about cursors and variables, the second code snippet is a correct way to use cursors with variables in PL/SQL. Cursors in PL/SQL can be used with variables in the WHERE clause to filter the result set based on those variables.
Using variables in a cursor allows you to dynamically filter the data based on runtime values, which can be very useful in certain situations. However, you're correct that using variables in a cursor may prevent query caching and reuse, potentially leading to performance issues, especially when dealing with complex functions and multiple cursors.
In such cases, you should consider performance optimization techniques like ensuring proper indexing, minimizing cursor usage, and analyzing the query execution plan to identify areas for improvement.
英文:
today i was looking to some old code and i found this function where a cursor have inside a variable in the where clause but this variabile was not assigned in the cursor statement itself but later in the code, just before opening the cursor.
I've sanitazed the function from company data:
create FUNCTION VERY_STRANGE_FUNCTION(
input_field IN VARCHAR2,
input_field_value IN VARCHAR2
)
RETURN VARCHAR2
IS
out_RESULT VARCHAR2(4000);
BEGIN
DECLARE
var_ID_ORDER VARCHAR2(100);
var_ID_ORDER_2 VARCHAR2(100);
OUTPUT_VAR VARCHAR2(4000);
CURSOR cur_ORDERS IS
SELECT ID, NAME, COMPANY
FROM ORDERS
WHERE ORDER_ID1 = var_ID_ORDER
AND ORDER_ID2 = var_ID_ORDER_2;
BEGIN
var_ID_ORDER := input_field
var_ID_ORDER2 := input_field_value
FOR rec IN cur_ORDERS
LOOP
OUTPUT_VAR := rec.COMPANY;
END LOOP;
RETURN (OUTPUT_VAR);
end;end;
Im new to PLSQL, but in all the courses i had take i see that if i need to create a cursor that need a variable i should do something like that:
CURSOR cur_dummy (var1 VARCHAR2, var2 NUMBER)
IS
select
ID,NAME,NUMBER
from ORDERS
where ID = var1
AND NUMBER=var2;
This is a correct way to mix cursors and variables or not? For me, the first thing i could think of, is that the cursor could not be cached or reused becouse every time is different.
This function is a lot more complex, about 3000 rows, add 10 cursors like that with joins from 10 tables each and get called about 100 times during some queries.
答案1
得分: 1
不必使用 CURSOR 参数。您的游标 SQL 可以引用其命名范围内的任何变量，包括直接存储过程，以及包级别的变量（如果已打包）。
当您选择使用 CURSOR 参数时，您正在添加一个新的、更狭窄的命名范围，在其中可以拥有仅在游标内部而不在其他任何地方具有值的变量。这可以提供更清晰的代码，减少了像在循环中打开游标而不重置游标依赖的存储过程级别变量等错误的可能性。经典情况下会发生这种情况，例如当您有嵌套的游标，并且外部游标的结果驱动内部游标的参数时。被迫传递它所需的内容有助于防止在这种情况下的变量设置错误。
但是，如果您不是在游标打开本身上进行循环（只是在提取数据），那么真的很少有选择一个而不选择另一个的理由。遵循您的编程风格或公司的风格标准来做出这种决策。
英文:
You do not have to use CURSOR parameters. Your cursor SQL is able to reference any variable within its naming scope, which would include the immediate procedure, plus any package level variables if packaged.
When you do choose to use CURSOR parameters, you are adding a new, narrower naming scope in which you can have variables that have value only within the cursor and nowhere else. This can give cleaner code that is less liable to bugs like looping and opening a cursor in that loop without resetting procedure-level variables that the cursor depends on. A classic situation where this happens is when you have nested cursors and the results of the outer cursor and driving the inner cursor's parameters. Being forced to pass in what it needs helps prevent variable-setting bugs in situations like this.
But if you're not looping on the cursor open itself (just the fetching), there is really little reason to choose one over the other. Follow your programming style or your company's standards for style decisions like that.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论