甲骨文,如何打开游标并选择一列到一个变量中 [英] Oracle, how to open cursor and select one column of many into a variable
本文介绍了甲骨文,如何打开游标并选择一列到一个变量中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个Oracle存储过程返回一个参考游标。我想先打开游标,然后再返回它来检查计数并在需要时抛出异常,但是我在语法以及如何执行此操作方面遇到了麻烦。
I have a Oracle stored procedure returning a reference cursor. I want to open the cursor before i return it to check a count and throw an exception if need be, but im having trouble with syntax and how im supposed to do this.
V_ASN_COUNT NUMBER;
OPEN O_CURSOR FOR
SELECT column1, -- a bunch of columns
column2,
COUNT(DISTINCT SI.ASN_NO) OVER (PARTITION BY SI.ASN_NO) AS ASN_COUNT
FROM AN_ORDER_INFO OI, AN_SHIPMENT_INFO SI
WHERE -- a bunch of criteria
OPEN O_CURSOR;
LOOP
FETCH ASN_COUNT INTO V_ASN_COUNT;
END LOOP;
CLOSE O_CURSOR;
IF(V_ASN_COUNT > 1) THEN
RAISE MULTIPLE_ASNS;
END IF;
推荐答案
我认为您可以做到这一点:
I think you can do this one:
curid NUMBER;
desctab DBMS_SQL.DESC_TAB;
colcnt NUMBER; -- total number of columns
res NUMBER;
V_ASN_COUNT NUMBER;
BEGIN
OPEN O_CURSOR FOR
SELECT
column1, -- a bunch of columns
column2,
...
COUNT(DISTINCT SI.ASN_NO) OVER (PARTITION BY SI.ASN_NO) AS ASN_COUNT
FROM AN_ORDER_INFO OI, AN_SHIPMENT_INFO SI
WHERE -- a bunch of criteria
curid := DBMS_SQL.TO_CURSOR_NUMBER (O_CURSOR);
DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
-- "ASN_COUNT" is the last column, i. e. "colcnt" refers to column number of "ASN_COUNT"
-- or set colcnt directly, e.g. colcnt := 12;
FOR i IN 1..colcnt LOOP
IF desctab(i).col_type = 2 THEN
DBMS_SQL.DEFINE_COLUMN(curid, i, V_ASN_COUNT);
ELSIF desctab(i).col_type = 12 THEN
DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
.......
ELSE
DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 25);
END IF;
END LOOP;
-- I do not know if this loop is needed, perhaps you can simply do
-- DBMS_SQL.DEFINE_COLUMN(curid, colcnt, V_ASN_COUNT);
-- for a single column
res := DBMS_SQL.FETCH_ROWS(curid); -- Fetch only the first row, no loop required
DBMS_SQL.COLUMN_VALUE(curid, colcnt, V_ASN_COUNT); -- Loop over all column not required, you just like to get the last column
IF V_ASN_COUNT > 1 THEN
RAISE MULTIPLE_ASNS;
END IF;
DBMS_SQL.CLOSE_CURSOR(curid);
有关更多详细信息,请查看Oracle文档: DBMS_SQL.TO_CURSOR_NUMBER 函数。
For further details, check Oracle documentation: DBMS_SQL.TO_CURSOR_NUMBER Function.
但是,打开/后退光标仍然存在问题!
However, the problem with open/rewinded cursor remains!
这篇关于甲骨文,如何打开游标并选择一列到一个变量中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文