record类型和函数定义如下,想请大家给出一个访问该函数的java实例代码。create or replace type rectype as record
(
name varchar2(24),
num varchar2(10)
);create or replace function getUserInfo
(
return_code out integer
)
return rectype is
o_userinfo rectype;
begin
o_userinfo.name := 'abc';
o_userinfo.num := '111111';
return_code := 0;
return o_userinfo;
end getUserInfo;
(
name varchar2(24),
num varchar2(10)
);create or replace function getUserInfo
(
return_code out integer
)
return rectype is
o_userinfo rectype;
begin
o_userinfo.name := 'abc';
o_userinfo.num := '111111';
return_code := 0;
return o_userinfo;
end getUserInfo;
ResultSet rs=statement.excuteQuery(sql);反正跟普通的oracle内部函数decode、lag、trim等都差不多的调用方法吧。
Statement st = new Statement();
ResultSet rs = st.execudeQuery();
while(rs.next){}
有三种方式来返回,
1。 是单个的字段
2. 是多个的字段
3.是一个结果集,这时这个必须要用到 游标,和out参数http://zdf_zdf520.blog.163.com
定义行对象
create or replace type row_userlist as object(user_id VARCHAR2(32));
定义表对象
CREATE OR REPLACE TYPE table_userlist AS TABLE OF row_userlist;FUNCTION DEMO:
CREATE OR REPLACE FUNCTION SINOCPC.GetIdListOfUser(
user_ids VARCHAR2,
obj_type INT
)RETURN table_userlist PIPELINED AS
row_Rec row_userlist;
total INT;
BEGIN
IF obj_type=1 THEN
BEGIN
SELECT COUNT(*) INTO total FROM cpcroleuser a ,cpcuser b
WHERE a.sysuserid=b.sysuserid AND b.userid=user_ids
AND roleid IN('admins','crmshipadmins','crmplanadmins','crmsalesmanagers', 'crmfinanceadmins','crmaccountadmins'); EXCEPTION
WHEN no_data_found THEN
total:=0; END;
IF total<>0 OR user_ids='admin' THEN FOR row_s IN (SELECT custid FROM crmcust) LOOP
row_Rec:=row_userlist(row_s.custid);
PIPE ROW (row_Rec); END LOOP;
ELSE
FOR row_s IN
(select c.custid from crmcust c,(SELECT DISTINCT * FROM TABLE(GetManageUser(user_ids,2))) u
where INSTR((','||c.employee||','),(',' ||u.user_id||',') ) >0 ) LOOP
row_Rec:=row_userlist(row_s.custid);
PIPE ROW (row_Rec);
END LOOP; END IF;
ELSIF obj_type=2 THEN FOR row_s IN (
SELECT DISTINCT userid FROM (
SELECT userid FROM cpcuser WHERE user_ids='admin'
UNION ALL
SELECT c.userid FROM cpcuser c,cpcroleuser a
WHERE c.sysuserid=a.sysuserid AND c.userid=user_ids
AND roleid IN('admins','hradmins')
UNION ALL
SELECT userid FROM cpcuser a WHERE
EXISTS (SELECT * FROM TABLE(GetManageUser(user_ids,2)) c where c.user_id=a.userid))c
) LOOP
row_Rec:=row_userlist(row_s.userid);
PIPE ROW (row_Rec); END LOOP;
END IF;
RETURN;
END GetIdListOfUser;调用方法
select * from table(FUNCTIONNAME(parm1....))