gpt4 book ai didi

oracle - 如何访问结构并获取列列表,refcursor 的数据类型?

转载 作者:行者123 更新时间:2023-12-02 07:22:16 26 4
gpt4 key购买 nike

我有一个程序可以让我得到带 refcursor 的输出,游标中的数据/结构将是动态的。每次根据输入数据类型和游标中的列数都会有所不同。那么我该如何访问这个结构并获取数据类型呢?

PROCEDURE  PROC_B ( name_     IN       VARCHAR2,
date_ IN DATE,
code_ IN VARCHAR2,
sp_name_ IN VARCHAR2,
wrapper_ OUT sys_refcursor,
datyapes_ OUT VARCHAR2,
TS2_ OUT VARCHAR2,
header_ OUT VARCHAR2)
AS
TS_ DATE;
BEGIN
PROC_A (name_, date_, code_, sp_name_, wrapper_, TS_, header_);
TS2_:= TO_CHAR(TS_, 'MM-DD-YYYY.HH24_MI');

-- Logic should come here for below requirement
-- Get the datatypes of variables from wrapper_ (ref cursor datatype) and send them back in datyapes_ .
-- Eg1 : If ref cursor returns 2 values with dataytpes VARCHAR2 & Num then o/p should be VARCHAR2|NUMBER ,
--Eg2 : If ref cursor returns 3 values with dataytpes DATE , TIMESTAMP , VARCHAR2 then o/p should be DATE|TS|VARCHAR2

END;**

最佳答案

您可以使用 DBMS_SQL.TO_CURSOR_NUMBER 函数将 ref 游标转换为 DBMS_SQL 游标。然后,有了游标号,您可以检查通过 DBMS_SQL 操作它。这包括能够描述它的列,如下例所示:

DECLARE
l_rc SYS_REFCURSOR;

l_cursor_number INTEGER;
l_col_cnt INTEGER;
l_desc_tab DBMS_SQL.desc_tab;
l_col_num INTEGER;
BEGIN
OPEN l_rc FOR 'SELECT object_name, object_type, last_ddl_time FROM dba_objects where rownum <= 10';

l_cursor_number := DBMS_SQL.to_cursor_number (l_rc);

DBMS_SQL.describe_columns (l_cursor_number, l_col_cnt, l_desc_tab);

l_col_num := l_desc_tab.FIRST;

IF (l_col_num IS NOT NULL) THEN
LOOP
DBMS_OUTPUT.put_line ('Column #' || l_col_num);
DBMS_OUTPUT.put_line ('...name: ' || l_desc_tab (l_col_num).col_name);
DBMS_OUTPUT.put_line ('...type: ' || l_desc_tab (l_col_num).col_type);
DBMS_OUTPUT.put_line ('...maxlen: ' || l_desc_tab (l_col_num).col_max_len);
-- ... other fields available in l_desc_tab(l_col_num) too.
l_col_num := l_desc_tab.NEXT (l_col_num);
EXIT WHEN (l_col_num IS NULL);
END LOOP;
END IF;

DBMS_SQL.close_cursor (l_cursor_number);
END;

输出

Column #1
...name: OBJECT_NAME
...type: 1
...maxlen: 128
Column #2
...name: OBJECT_TYPE
...type: 1
...maxlen: 23
Column #3
...name: LAST_DDL_TIME
...type: 12
...maxlen: 7

关于oracle - 如何访问结构并获取列列表,refcursor 的数据类型?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42563100/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com