使用动态表名称的oracle函数和游标 [英] oracle function and cursor using dynamic table name
问题描述
在我的oracle数据库中,我想使用游标创建一个函数或过程,该函数或过程将使用动态表名.这是我的代码.
IN my oracle database i want to create a function or procedure with cursor which will use dynamic table name.here is my code.
CREATE OR REPLACE Function Findposition ( model_in IN varchar2,model_id IN number) RETURN number IS cnumber number;
TYPE c1 IS REF CURSOR;
c2 c1;
BEGIN
open c2 FOR 'SELECT id,ROW_NUMBER() OVER ( ORDER BY id) AS rownumber FROM '||model_in;
FOR employee_rec in c2
LOOP
IF employee_rec.id=model_id then
cnumber :=employee_rec.rownumber;
end if;
END LOOP;
close c2;
RETURN cnumber;
END;
帮助我解决此问题.IN
help me to solve this problem.IN
推荐答案
- 对于弱类型的引用游标,无需声明
c1
类型.您可以只使用SYS_REFCURSOR
类型. - 您不能像这样混合使用隐式和显式游标调用.如果要转到游标
OPEN
,则必须循环地从游标中移出FETCH
,并且必须将其移至CLOSE
.您不能OPEN
和CLOSE
它,但是在隐式游标循环中从中获取它. - 您必须声明一个或多个变量才能将数据提取到其中.我声明了一个记录类型和该记录的一个实例,但是您可以轻松地在两个变量中声明两个局部变量和
FETCH
. -
ROWID
是保留字,所以我改用ROWPOS
. - There is no need to declare a
c1
type for a weakly typed ref cursor. You can just use theSYS_REFCURSOR
type. - You can't mix implicit and explicit cursor calls like this. If you are going to
OPEN
a cursor, you have toFETCH
from it in a loop and you have toCLOSE
it. You can'tOPEN
andCLOSE
it but then fetch from it in an implicit cursor loop. - You'll have to declare a variable (or variables) to fetch the data into. I declared a record type and an instance of that record but you could just as easily declare two local variables and
FETCH
into those variables. ROWID
is a reserved word so I usedROWPOS
instead.
将它们放在一起,您可以编写类似的内容
Putting that together, you can write something like
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE Function Findposition (
2 model_in IN varchar2,
3 model_id IN number)
4 RETURN number
5 IS
6 cnumber number;
7 c2 sys_refcursor;
8 type result_rec is record (
9 id number,
10 rowpos number
11 );
12 l_result_rec result_rec;
13 BEGIN
14 open c2 FOR 'SELECT id,ROW_NUMBER() OVER ( ORDER BY id) AS rowpos FROM '||model_in;
15 loop
16 fetch c2 into l_result_rec;
17 exit when c2%notfound;
18 IF l_result_rec.id=model_id
19 then
20 cnumber :=l_result_rec.rowpos;
21 end if;
22 END LOOP;
23 close c2;
24 RETURN cnumber;
25* END;
SQL> /
Function created.
我相信这会返回您期望的结果
I believe this returns the result you expect
SQL> create table foo( id number );
Table created.
SQL> insert into foo
2 select level * 2
3 from dual
4 connect by level <= 10;
10 rows created.
SQL> select findposition( 'FOO', 8 )
2 from dual;
FINDPOSITION('FOO',8)
---------------------
4
请注意,从效率的角度来看,最好将其编写为单个SQL语句,而不是每次打开一个游标并从表中获取每一行.如果确定要使用游标,则希望在找到感兴趣的行时退出游标,而不是继续从表中获取每一行.
Note that from an efficiency standpoint, you'd be much better off writing this as a single SQL statement rather than opening a cursor and fetching every row from the table every time. If you are determined to use a cursor, you'd want to exit the cursor when you've found the row you're interested in rather than continuing to fetch every row from the table.
从代码清晰的角度来看,许多变量名称和数据类型似乎很奇怪.您的参数名称似乎选择不当-例如,我不希望model_in
是输入表的名称.声明名为c2
的游标也是有问题的,因为它是非描述性的.
From a code clarity standpoint, many of your variable names and data types seem rather odd. Your parameter names seem poorly chosen-- I would not expect model_in
to be the name of the input table, for example. Declaring a cursor named c2
is also problematic since it is very non-descriptive.
这篇关于使用动态表名称的oracle函数和游标的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!