Oracle内部加入收藏?
发布时间:2020-12-12 13:03:08 所属栏目:百科 来源:网络整理
导读:我想在下面的例子中加入表类型与其他表的集合 – 我有一个函数F_GetPendingFeeds,它返回feed_log类型的表集合. 我想加入这个返回的集合与其中一个表 – CREATE OR REPLACE PACKAGE BODY L_DemoPkgIS TYPE t_feedLog IS TABLE OF feed_log%ROWTYPE INDEX BY P
我想在下面的例子中加入表类型与其他表的集合 –
我有一个函数F_GetPendingFeeds,它返回feed_log类型的表集合. CREATE OR REPLACE PACKAGE BODY L_DemoPkg IS TYPE t_feedLog IS TABLE OF feed_log%ROWTYPE INDEX BY PLS_INTEGER; FUNCTION F_GetPendingFeeds RETURN t_feedLog IS lo_feedLog t_feedLog; BEGIN SELECT feed_log_seq,processed_dt,processed_by,create_dt,created_by BULK COLLECT INTO lo_feedLog FROM feed_log WHERE status_cd = 0; RETURN lo_feedLog; EXCEPTION WHEN OTHERS THEN --TODO: Log Exception RAISE; END F_GetPendingFeeds; PROCEDURE P_ProcessFeed IS o_pendingFeed t_feedLog; ln_totalRecords t_feedLog; BEGIN -- Get the list of pending feed ids o_pendingFeed := F_GetPendingFeeds(); -- Check if new data is present for processing IF o_pendingFeed.COUNT = 0 THEN dbms_output.put_line('Feed processing failed. No data found.'); RETURN; END IF; SELECT COUNT(*) INTO ln_totalRecords FROM feed_details t1,table(o_pendingFeed) t2 --ERROR: ORA-22905: cannot access rows from a non-nested table item WHERE t1.feed_log_seq = t2.feed_log_seq; EXCEPTION WHEN OTHERS THEN --TODO: Log Exception RAISE; END P_ProcessFeed; END; 我收到错误 – PL/SQL: SQL Statement ignored PL/SQL: ORA-22905: cannot access rows from a non-nested table item 请注意我想加入表 – FROM feed_details t1,table(o_pendingFeed) t2 --ERROR: ORA-22905: cannot access rows from a non-nested table item WHERE t1.feed_log_seq = t2.feed_log_seq; 解决方法在Oracle 12C之前,您只能从使用CREATE TYPE在服务器上创建的集合中进行选择,例如SQL> CREATE TYPE r_feedLog IS OBJECT (foo NUMBER,bar VARCHAR2(20)); SQL> CREATE TYPE t_feedLog IS TABLE OF r_feedLog; 然后从包中删除t_feedLog的声明. 使用Oracle 12C,可以从包规范中定义的PL / SQL表中进行选择. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |