oracle中的存储过程的写法(在线)

Question

FOR rs in (SELECT SD.BOOKID,B.NAME BOOKNAME,BS.NAME,BS.ID
   B.PURBLISHINGHOUSE,B.PRICE,B.PURCASEQUANTITY,B.STOCK,SD.PURCASEQUANTITY 
   FROM SUBSCRIBEDETAIL SD,SUBSCRIBE S,BOOK B,BOOKSELLER BS
   WHERE S.STATE=purchasestate
   AND SD.STATE=purchasedetailstate
   AND S.DSID=SD.DSID
   AND SD.BOOKID=B.ID
   AND B.BOOKSELLER=BS.ID
   AND B.TYPE=1       
   GROUP BY SD.BOOKID)

Answer 1

SELECT SD.BOOKID,B.NAME BOOKNAME,BS.NAME,BS.ID
   B.PURBLISHINGHOUSE,B.PRICE,B.PURCASEQUANTITY,B.STOCK,SD.PURCASEQUANTITY 
   FROM SUBSCRIBEDETAIL SD,SUBSCRIBE S,BOOK B,BOOKSELLER BS
   WHERE S.STATE=purchasestate
   AND SD.STATE=purchasedetailstate
   AND S.DSID=SD.DSID
   AND SD.BOOKID=B.ID
   AND B.BOOKSELLER=BS.ID
   AND B.TYPE=1       
   GROUP BY SD.BOOKID; ) 不用into rs或者可以写成
create procedure purchasebooktable(
purchasestate in number,
purchasedetailstate in number,
vResult  OUT SYS_REFCURSOR
)
is
type number;
bookname varchar2(100);
bookid number;
publishinghouse varchar2(100);
booksellername varchar2(100);
booksellerid number;
price number;
spotpurchasequantity number;
stock number;
purchasequantity number;
rst publicpackage.resultset;
cursor rs_a is
SELECT SD.BOOKID,B.NAME BOOKNAME,BS.NAME,BS.ID
   B.PURBLISHINGHOUSE,B.PRICE,B.PURCASEQUANTITY,B.STOCK,SD.PURCASEQUANTITY 
   INTO rs
   FROM SUBSCRIBEDETAIL SD,SUBSCRIBE S,BOOK B,BOOKSELLER BS
   WHERE S.STATE=purchasestate
   AND SD.STATE=purchasedetailstate
   AND S.DSID=SD.DSID
   AND SD.BOOKID=B.ID
   AND B.BOOKSELLER=BS.ID
   AND B.TYPE=1       
   GROUP BY SD.BOOKID;
begin   
   FOR rs in rs_a 
   LOOP
   INSERT INTO L_PURCHASE(BOOKID,BOOKNAME,BOOKSELLERID,BOOKSELLERNAME,
                          PURBLISHINGHOUSE,PRICE,STOCK,SPOTPURCASEQUANTITY)
   VALUES(RS.BOOKID,RS.BOOKNAME,RS.ID,RS.NAME,RS.PURBLISHINGHOUSE,RS.PRICE,RS.STOCK
          ,RS.SPOTPURCASEQUANTITY);
   OPEN vResult FOR 'SELECT * FROM L_PURCHASE';
   END LOOP;
end;

Answer 2

cursor rs_a is 这是什么意思

Answer 3

"cursor rs_a is 这是什么意思"
----------------------------
这是定义一个游标，得到一个记录集.

Answer 4

为什么他把返回写在循环里呀,这个记录集下有多条记录呀
   FOR rs in rs_a 
   LOOP
   INSERT INTO L_PURCHASE(BOOKID,BOOKNAME,BOOKSELLERID,BOOKSELLERNAME,
                          PURBLISHINGHOUSE,PRICE,STOCK,SPOTPURCASEQUANTITY)
   VALUES(RS.BOOKID,RS.BOOKNAME,RS.ID,RS.NAME,RS.PURBLISHINGHOUSE,RS.PRICE,RS.STOCK
          ,RS.SPOTPURCASEQUANTITY);
   OPEN vResult FOR 'SELECT * FROM L_PURCHASE';
   END LOOP;