oracle 可以将 TYPE 声明为引用游标行类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17989977/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
can TYPE be declared of ref cursor rowtype
提问by kushalvm
TYPE ref_cur IS REF CURSOR;
ref_cur_name ref_cur;
TYPE tmptbl IS TABLE OF ref_cur_name%ROWTYPE;
n_tmptbl tmptbl;
I tried this code but can't get it thru compiler . Is there a way to store the results of ref cursor into a table ?
我试过这段代码，但无法通过编译器得到它。有没有办法将引用游标的结果存储到表中？
NOTE-I need a table because i need to access the column of ref cursor . Using dbms_sqlto access records of ref cursor is a bit tough for me .
注意 - 我需要一个表，因为我需要访问 ref cursor 的列。使用dbms_sql来访问引用游标的记录对我来说有点困难。
UPDATE :
更新 ：
/* Formatted on 8/1/2013 4:09:08 PM (QP5 v5.115.810.9015) */
CREATE OR REPLACE PROCEDURE proc_deduplicate (p_tblname IN VARCHAR2,
p_cname IN VARCHAR2,
p_cvalue IN VARCHAR2)
IS
v_cnt NUMBER;
TYPE ref_cur IS REF CURSOR;
ref_cur_name ref_cur;
v_str1 VARCHAR2 (4000);
v_str2 VARCHAR2 (4000);
v_str3 VARCHAR2 (4000);
BEGIN
v_str1 :=
'SELECT ROWID v_rowid FROM '
|| p_tblname
|| ' WHERE '
|| p_cname
|| '='''
|| p_cvalue
|| '''';
BEGIN
v_str2 :=
'SELECT COUNT ( * )
FROM '
|| p_tblname
|| ' WHERE '
|| p_cname
|| ' = '''
|| p_cvalue
|| '''';
logerrors ('proc_deduplicate',
'count exception',
SQLCODE,
v_str2 || SQLERRM,
'e');
EXECUTE IMMEDIATE v_str2 INTO v_cnt;
EXCEPTION
WHEN OTHERS
THEN
logerrors ('proc_deduplicate',
'count exception',
SQLCODE,
SQLERRM,
'e');
END;
IF v_cnt IS NOT NULL
THEN
OPEN ref_cur_name FOR v_str1;
LOOP
IF v_cnt = 1
THEN
EXIT;
ELSE
BEGIN
v_str3 :=
'DELETE FROM '
|| p_tblname
|| ' WHERE ROWID = v_rowid ';
-- THIS IS THE PROBLEM . i just created an alias above for rowid keyword but i guess, DBMS sql will have to be used after all .
EXECUTE IMMEDIATE v_str3;
EXCEPTION
WHEN OTHERS
THEN
logerrors (
' proc_deduplicate
',
' delete exception
',
SQLCODE,
SQLERRM,
' e
'
);
END;
END IF;
v_cnt := v_cnt - 1;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
logerrors (
' proc_deduplicate',
' final exception
',
SQLCODE,
SQLERRM,
' e'
);
END;
/
回答by Nick Krasnov
By issuing TYPE ref_cur IS REF CURSORyou are declaring a weak cursor. Weak cursors return no specified types. It means that you cannot declare a variable that is of weak_cursor%rowtype, simply because a weak cursor does not return any type.
通过发出TYPE ref_cur IS REF CURSOR您声明弱游标。弱游标不返回指定类型。这意味着您不能声明属于 的变量weak_cursor%rowtype，仅仅因为弱游标不返回任何类型。
declare
type t_rf is ref cursor;
l_rf t_rf;
type t_trf is table of l_rf%rowtype;
l_trf t_trf;
begin
null;
end;
ORA-06550: line 4, column 27:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 3:
PL/SQL: Item ignored
If you specify return typefor your ref cursor, making it strong, your PL/SQL block will compile successfully:
如果你为你的 ref 游标指定返回类型，使它强大，你的 PL/SQL 块将成功编译：
SQL> declare -- strong cursor
2 type t_rf is ref cursor return [table_name%rowtype][structure];
3 l_rf t_rf;
4 type t_trf is table of l_rf%rowtype;
5 l_trf t_trf;
6 begin
7 null;
8 end;
9 /
PL/SQL procedure successfully completed
回答by Alex Poole
As far as I understand what you're doing, you just need to parameterise the delete:
据我了解你在做什么，你只需要参数化删除：
...
v_str3 VARCHAR2 (4000);
v_rowid ROWID;
BEGIN
...
OPEN ref_cur_name FOR v_str1;
LOOP
FETCH ref_cur_name INTO v_rowid;
EXIT WHEN ref_cur_name%NOTFOUND;
IF v_cnt = 1
THEN
EXIT;
ELSE
BEGIN
v_str3 :=
'DELETE FROM '
|| p_tblname
|| ' WHERE ROWID = :v_rowid ';
EXECUTE IMMEDIATE v_str3 USING v_rowid;
...
You need to fetch the ref_cur_nameinto a variable, which needs to be declared obviously, and then use that as a bind variable value in the delete.
您需要将其提取ref_cur_name到一个变量中，该变量需要明确声明，然后将其用作删除中的绑定变量值。
You should do the same thing with the p_cvaluereferences in the other dynamic SQL too. You could probably make this much simpler, with a single delete and no explicit count, in a single dynamic statement:
您也应该p_cvalue对其他动态 SQL 中的引用执行相同的操作。您可以在单个动态语句中使用单个删除和不显式计数使这更简单：
CREATE OR REPLACE PROCEDURE proc_deduplicate (p_tblname IN VARCHAR2,
p_cname IN VARCHAR2,
p_cvalue IN VARCHAR2)
IS
BEGIN
execute immediate 'delete from ' || p_tblname
|| ' where ' || p_cname || ' = :cvalue'
|| ' and rowid != (select min(rowid) from ' || p_tblname
|| ' where ' || p_cname || ' = :cvalue)'
using p_cvalue, p_cvalue;
END proc_deduplicate;
/
If you wanted to know or report how many rows were deleted, you could refer to SQL%ROWCOUNTafter the execute immediate.
如果你想知道或报告有多少行被删除，你可以参考SQL%ROWCOUNT后execute immediate。
回答by Gunjan Sharma
Strong ref cursor returns defined values, but weak is free to anything, totally dynamic.
Strong ref cursor 返回定义的值，但weak 对任何东西都是自由的，完全是动态的。
But we can't define rowtypevariable on weak ref cur
e.g.
但是我们不能rowtype在弱引用中定义变量，例如
declare
refcur sys_refcursor;
emprec refcur%rowtype; --it'll yield error
while
尽管
declare
type empref is ref cursor returns employees%rowtype;
empcur empref;
emprec empcur%rowtype; --it'll work fine.
This is very useful, now we can define collection on them and many other advantage if you talk about practically.
这非常有用，现在我们可以在它们上定义集合以及许多其他优点，如果您实际谈论的话。
回答by Alex Poole
No. You're trying to declare the type against an instance of the cursor anyway, so you'd be closer with:
不。无论如何，您都试图针对游标实例声明类型，因此您会更接近：
TYPE tmptbl IS TABLE OF ref_cur%ROWTYPE;
but you still can't do that, you'd get PLS-00310: with %ROWTYPE attribute, 'REF_CUR' must name a table, cursor or cursor-variable.
但你仍然不能那样做，你会得到PLS-00310: with %ROWTYPE attribute, 'REF_CUR' must name a table, cursor or cursor-variable.
A ref cursor is weakly-typed, so the compiler doesn't know what a record would look like. You could open the ref cursor for different results depending on the logic in the block, or a dynamic query, and the compiler would have no way to know what to expect in advance.
ref 游标是弱类型的，因此编译器不知道记录的样子。您可以根据块中的逻辑或动态查询为不同的结果打开引用游标，编译器无法提前知道会发生什么。
The PL/SQL documentationstate that %rowtypecan apply to an explicit cursor, a strongcursor variable, or a table or view. And thiscompares strong and weak cursor variables.
可以应用于显式游标、强游标变量或表或视图的PL/SQL 文档状态。而这种比较强与弱的游标变量。%rowtype
If you know what your query will be you can declare a recordtype with those fields, or against a table %rowtypeif you're be querying a single table. Since you're using dbms_sqlI guess you won't know that though. Maybe if you updated your question with more information about what you're actually trying to do there would be some other approaches you could try.
如果您知道您的查询将是什么，您可以record使用这些字段声明一个类型，或者%rowtype如果您要查询单个表，则针对一个表。既然你在使用，dbms_sql我想你不会知道的。也许如果您使用有关您实际尝试做的事情的更多信息更新了您的问题，那么您可以尝试其他一些方法。
