Oracle PL/SQL-迭代本地定义表的列名 [英] Oracle PL/SQL - Iterate on column names of locally defined table
本文介绍了Oracle PL/SQL-迭代本地定义表的列名的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想遍历本地定义的表的名称,但是它不能按预期工作:
I would like to iterate on the names of a locally defined table, but it doesn't work as expected:
declare
type books is record
(title varchar(50) := 'First Book'
,author varchar(50) := 'Me'
,subject varchar(100) := 'Simple ones'
,book_id number := 94321
);
type table_of_books is
table of books;
list_of_books table_of_books;
CURSOR c IS
SELECT COLUMN_NAME FROM ALL_COL_COMMENTS WHERE table_name='list_of_books';
begin
dbms_output.enable;
list_of_books := table_of_books();
list_of_books.extend(1);
list_of_books(1) := books();
FOR current_field IN c LOOP -- Is not iterating
dbms_output.put_line(current_field.column_name); -- I would expect output here
END LOOP;
end;
/
这是Oracle 19c.
This is Oracle 19c.
推荐答案
如果使用的是在SQL范围内创建的对象,而不是在PL/SQL范围内创建的记录,则可以使用 ANYDATA
找出属性及其值.
If you use an object created in the SQL scope rather than a record created in the PL/SQL scope then you can use ANYDATA
to find out the attributes and their values.
例如,如果您的类型是:
For example, if your types are:
CREATE TYPE book is OBJECT(
title varchar(50),
author varchar(50),
subject varchar(100),
book_id number,
first_published DATE
);
CREATE TYPE table_of_books IS TABLE OF book;
然后您可以创建包:
CREATE PACKAGE reflection IS
TYPE type_info IS RECORD(
prec PLS_INTEGER,
scale PLS_INTEGER,
len PLS_INTEGER,
csid PLS_INTEGER,
csfrm PLS_INTEGER,
schema_name VARCHAR2(30),
type_name VARCHAR2(30),
version VARCHAR2(100),
count PLS_INTEGER
);
TYPE attr_info IS RECORD(
prec PLS_INTEGER,
scale PLS_INTEGER,
len PLS_INTEGER,
csid PLS_INTEGER,
csfrm PLS_INTEGER,
attr_elt_type ANYTYPE,
aname VARCHAR2(30)
);
FUNCTION get_size(
p_anydata IN ANYDATA
) RETURN PLS_INTEGER;
FUNCTION get_attr_name_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2;
FUNCTION get_attr_value_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2;
END;
/
带有包体:
CREATE PACKAGE BODY reflection IS
DEBUG BOOLEAN := FALSE;
FUNCTION get_type(
p_anydata IN ANYDATA
) RETURN ANYTYPE
IS
v_typeid PLS_INTEGER;
v_anytype ANYTYPE;
v_type_info REFLECTION.TYPE_INFO;
BEGIN
v_typeid := p_anydata.GetType( typ => v_anytype );
RETURN v_anytype;
END;
FUNCTION get_info(
p_anytype IN ANYTYPE
) RETURN type_info
IS
v_typeid PLS_INTEGER;
v_type_info REFLECTION.TYPE_INFO;
BEGIN
v_typeid := p_anytype.GetInfo (
v_type_info.prec,
v_type_info.scale,
v_type_info.len,
v_type_info.csid,
v_type_info.csfrm,
v_type_info.schema_name,
v_type_info.type_name,
v_type_info.version,
v_type_info.count
);
IF v_typeid <> DBMS_TYPES.TYPECODE_OBJECT THEN
RAISE_APPLICATION_ERROR( -20000, 'Not an object.' );
END IF;
RETURN v_type_info;
END;
FUNCTION get_size(
p_anydata IN ANYDATA
) RETURN PLS_INTEGER
IS
BEGIN
RETURN Get_Info( Get_Type( p_anydata ) ).COUNT;
END;
FUNCTION get_attr_name_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2
IS
v_anydata ANYDATA := p_anydata;
v_anytype ANYTYPE;
v_type_info REFLECTION.TYPE_INFO;
v_output VARCHAR2(4000);
v_attr_typeid PLS_INTEGER;
v_attr_info REFLECTION.ATTR_INFO;
BEGIN
v_anytype := Get_Type( v_anydata );
v_type_info := Get_Info( v_anytype );
IF p_index < 1 OR p_index > v_type_info.COUNT THEN
RETURN NULL;
END IF;
v_anydata.PIECEWISE;
v_attr_typeid := v_anytype.getAttrElemInfo(
pos => p_index,
prec => v_attr_info.prec,
scale => v_attr_info.scale,
len => v_attr_info.len,
csid => v_attr_info.csid,
csfrm => v_attr_info.csfrm,
attr_elt_type => v_attr_info.attr_elt_type,
aname => v_attr_info.aname
);
RETURN v_attr_info.aname;
END;
FUNCTION get_attr_value_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2
IS
v_anydata ANYDATA := p_anydata;
v_anytype ANYTYPE;
v_type_info REFLECTION.TYPE_INFO;
v_output VARCHAR2(4000);
BEGIN
v_anytype := Get_Type( v_anydata );
v_type_info := Get_Info( v_anytype );
IF p_index < 1 OR p_index > v_type_info.COUNT THEN
RETURN NULL;
END IF;
v_anydata.PIECEWISE;
FOR i IN 1 .. p_index LOOP
DECLARE
v_attr_typeid PLS_INTEGER;
v_attr_info REFLECTION.ATTR_INFO;
v_result_code PLS_INTEGER;
BEGIN
v_attr_typeid := v_anytype.getAttrElemInfo(
pos => i,
prec => v_attr_info.prec,
scale => v_attr_info.scale,
len => v_attr_info.len,
csid => v_attr_info.csid,
csfrm => v_attr_info.csfrm,
attr_elt_type => v_attr_info.attr_elt_type,
aname => v_attr_info.aname
);
IF DEBUG THEN
DBMS_OUTPUT.PUT_LINE(
'Attribute ' || i || ': '
|| v_attr_info.aname
|| ' (type ' || v_attr_typeid || ')'
);
END IF;
CASE v_attr_typeid
WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
DECLARE
v_value NUMBER;
BEGIN
v_result_code := v_anydata.GetNumber( v_value );
IF i = p_index THEN
RETURN TO_CHAR( v_value );
END IF;
END;
WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
DECLARE
v_value VARCHAR2(4000);
BEGIN
v_result_code := v_anydata.GetVarchar2( v_value );
IF i = p_index THEN
RETURN v_value;
END IF;
END;
WHEN DBMS_TYPES.TYPECODE_DATE THEN
DECLARE
v_value DATE;
BEGIN
v_result_code := v_anydata.GetDate( v_value );
IF i = p_index THEN
RETURN TO_CHAR( v_value, 'YYYY-MM-DD HH24:MI:SS' );
END IF;
END;
ELSE
NULL;
END CASE;
END;
END LOOP;
RETURN NULL;
END;
END;
/
然后您获取代码的代码可能是:
Then your code to get the values could be:
DECLARE
list_of_books table_of_books;
idx PLS_INTEGER := 1;
p_anydata ANYDATA;
p_attr_name VARCHAR2(30);
p_attr_value VARCHAR2(4000);
BEGIN
dbms_output.enable;
list_of_books := table_of_books(
book(
'First book',
'Me',
'Simple Ones',
94321,
DATE '1970-01-01'
),
book(
'Second book',
'You',
'Intermediate Ones',
55555,
DATE '2020-01-01'
),
book(
'Third book',
NULL,
'Advanced Ones',
77777,
DATE '2099-12-31' + INTERVAL '0 23:59:59' DAY TO SECOND
)
);
FOR book_no IN 1 .. list_of_books.COUNT LOOP
p_anydata := ANYDATA.ConvertObject( list_of_books(book_no) );
DBMS_OUTPUT.PUT_LINE( 'Book ' || book_no || ':' );
FOR attr_no IN 1 .. REFLECTION.get_size( p_anydata ) LOOP
p_attr_name := REFLECTION.get_attr_name_at( p_anydata, attr_no );
p_attr_value := REFLECTION.get_attr_value_at( p_anydata, attr_no );
DBMS_OUTPUT.PUT_LINE( ' ' || p_attr_name || ': ' || p_attr_value );
END LOOP;
END LOOP;
END;
/
哪个输出:
Book 1:
TITLE: First book
AUTHOR: Me
SUBJECT: Simple Ones
BOOK_ID: 94321
FIRST_PUBLISHED: 1970-01-01 00:00:00
Book 2:
TITLE: Second book
AUTHOR: You
SUBJECT: Intermediate Ones
BOOK_ID: 55555
FIRST_PUBLISHED: 2020-01-01 00:00:00
Book 3:
TITLE: Third book
AUTHOR:
SUBJECT: Advanced Ones
BOOK_ID: 77777
FIRST_PUBLISHED: 2099-12-31 23:59:59
db<>小提琴此处
这篇关于Oracle PL/SQL-迭代本地定义表的列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文