现在的位置: 首页 > 综合 > 正文

Pro*C操作存储过程

2013年10月22日 ⁄ 综合 ⁄ 共 1915字 ⁄ 字号 评论关闭

 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;

抱歉!评论已关闭.