Oracle中的“'Member of”
Oracle中的“'Member of”
提问于 2017-09-21 01:11:57
我正在尝试在Oracle中使用member of。当表类型是number或任何其他数据类型时，我可以使用它。下面是这个的代码：
declare
type t is table of number;
lt t;
begin
select channel_key
bulk collect into lt
from dim_channels;
if 22 member of lt then
dbms_output.put_line('ss');
end if;
end;当表基于如下代码中的记录时，如何使用member of。
declare
type rt is record
(
channel_key number(10),
channel_code varchar2(100)
);
type t is table of rt;
lt t;
lrt rt;
begin
select channel_key, channel_code
bulk collect into lt
from dim_channels;
end;回答 2
Stack Overflow用户
发布于 2017-09-21 02:08:00
这不适用于普通的本地PL/SQL记录类型。要包含更多属性，您需要具有MAP或ORDER函数的对象类型：
create or replace type demo_ot as object
( channel_key integer
, channel_code varchar2(30)
, map member function demo_map return varchar2 )
/
create or replace type body demo_ot as
map member function demo_map return varchar2
is
begin
return self.channel_key || '<#>' || self.channel_code;
end demo_map;
end;
/
declare
type demo_t is table of demo_ot; -- You would normally create this globally in SQL
my_set demo_t;
my_object demo_ot;
begin
select demo_ot(ckey, ccode)
bulk collect into my_set
from ( select 1 as ckey, 'One' as ccode from dual
union all
select 2 as ckey, 'Two' as ccode from dual );
my_object := demo_ot(2, 'Two');
if my_object member of my_set then
dbms_output.put_line('Member found');
else
dbms_output.put_line('Member not found');
end if;
end;
/Stack Overflow用户
发布于 2017-09-21 23:21:16
我创建了以下代码来测试它
create or replace PROCEDURE P_MEMBER_OF_TEST(p_fname IN VARCHAR2,
p_lname in varchar2)
AS
type type_rec is record
(
first_name employees.first_name%type,
last_name employees.last_name%type
);
TYPE T_TAB_TYPE IS TABLE OF type_rec;
T_TAB T_TAB_TYPE;
t_rec type_rec;
i int;
BEGIN
t_rec.first_name := p_fname;
t_rec.last_name := p_lname;
SELECT FIRST_NAME,last_name bulk collect INTO T_TAB FROM
EMPLOYEES;
dbms_output.put_line(t_rec.first_name || ',' || t_rec.last_name);
IF t_rec MEMBER OF T_TAB THEN
DBMS_OUTPUT.PUT_LINE ('YES');
ELSE
DBMS_OUTPUT.PUT_LINE('NO');
END IF;
END;它编译时没有问题，但是当我执行它时，当我注释if-else-end if代码块时，我得到了连接已被重置的错误。它会被执行。您还可以建议代码@William Robertson的问题是什么吗
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/46328053
复制相关文章
相似问题
腾讯云开发者
