pl/sql小例子

-----------------------------------------------------------

---                                                    ----

---Program:                                            ----

---This program is use to post order to all store.     ----

---History:                                            ----

---By MT.sh 20110710 first version!                    ----

---                                                    ----

-----------------------------------------------------------

conn ops$edpman/panmanedp;

-set serveroutput on

-----------------------------------------------------------

---START TO POST ST_ORDER_YH TO PS_ORDER_YH@STORE      ----

-----------------------------------------------------------

DECLARE

 CURSOR st_order IS

 SELECT distinct

       o.area_no,

       o.storege_no,

       o.ord_no,

       o.suppl_no,

       o.recode_date,

       o.st_flag,

       o.mmail_no,

       trunc(sysdate) trans_date,

       to_char(sysdate,'HH24MI') trans_time

 FROM  st_order_yh o

 WHERE

 o.recode_date>=to_date(20110710,'yyyymmdd')

 AND   o.mmail_no is not null

 AND   o.storege_no=91087;

 TYPE strecord IS RECORD

   (

   area_no       st_order_yh.area_no%TYPE,

   storege_no    st_order_yh.storege_no%TYPE,

   ord_no        st_order_yh.ord_no%TYPE,

   suppl_no      st_order_yh.suppl_no%TYPE,

   recode_date   st_order_yh.recode_date%TYPE,

   st_flag       st_order_yh.st_flag%TYPE,

   mmail_no      st_order_yh.mmail_no%TYPE,

   trans_date date,

   trans_time number(4)

   );

   real_record strecord;

 BEGIN

   OPEN st_order;

   LOOP

   FETCH st_order INTO real_record;

   EXIT WHEN  st_order%NOTFOUND;

   insert into peisong.ps_order_yh@&store_no

   (area_no,storege_no,ord_no,suppl_no,recode_date,deldate,

    del_time,cashier_no,ord_qty,gor_qty,flag,st_