EXEC SQL BEGIN DECLARE SECTION;
unsigned int nUserID, friendID;
int gid, nStartPos, nReqCount;
VARCHAR db_link_name[32];
SQL_CURSOR cur_friendlist;
int retcode;
EXEC SQL END DECLARE SECTION;
1.
EXEC SQL ALLOCATE :cur_friendlist;
EXEC SQL AT :db_link_name EXECUTE
begin
Mini_User_Pkg.GetFriendsList(:nUserID,:nStartPos,:nReqCount,:cur_friendlist,:retcode);
end;
END-EXEC;
EXEC SQL FETCH :cur_friendlist INTO :nFriendsid;
EXEC SQL CLOSE :cur_friendlist;
EXEC SQL FREE :cur_friendlist;
对应的存储过程
procedure GetFriendsList
(
t_userid in Blog_Userfriends.userid%type,
t_startpos in integer,
t_reqcount in integer,
curfriends out T_CURSOR,
returncode out integer
)
is
t_endpos integer;
begin
returncode := 0;
t_endpos := t_startpos + t_reqcount;
OPEN curfriends FOR
SELECT BLOGGERID,GROUPID
FROM
( select rownum as RdNo, BLOGGERID,GROUPID FROM BLOG_USERFRIENDS WHERE USERID=t_userid and rownum <= t_endpos)
WHERE RdNo > t_startpos and RdNo <= t_endpos;
EXCEPTION
when OTHERS then
begin
returncode := 1;
raise;
return;
end;
end GetFriendsList;
2.
EXEC SQL AT :db_link_name
CALL Mini_User_Pkg.GetUserPwd(:userid,:var_emailaddress,:returncode);
对应的存储过程 :
procedure GetUserPwd
(
t_username in Blog_Userinfo.Username%type, --用户名
t_userId out Blog_Userinfo.userId%type, --用户ID
t_userpwd out Blog_Userinfo.Servicepasswd%type, -- 密码
ReturnCode out integer --返回参数,错误代码 0=正常
)
is
begin
ReturnCode := 0;
select userid, servicepasswd into t_userId, t_userpwd
from blog_userinfo
where username = t_username;
EXCEPTION
when NO_DATA_FOUND THEN
begin
returncode := 1403;
return;
end;
when OTHERS then
begin
ReturnCode := 2;
return;
end;
end GetUserPwd;
Pro*C操作存储过程
最新推荐文章于 2021-05-14 02:10:17 发布