存储过程根据业务场景自己摸索的写法

c

自己摸索的最low写法

begin
  DECLARE v_htbh VARCHAR(255);  #上游合同编号
	DECLARE v_id integer;         #上游合同id    
  DECLARE v_kbsj date;
  DECLARE  no_more_products INT DEFAULT 0;
  #DECLARE myCursor CURSOR FOR select user_id from sys_user where dept_id=153;
  #DECLARE  v_sxid select id from yqt_sxyhtdyb where xmid=(select xmmc from ms_fbht where syhtbh=v_id limit 1) and  xyhtbh=(select htbh from ms_htxx_info where id=v_htbh) and htbh=(select htbh from ms_fbht where syhtbh=v_id limit 1);
  DECLARE myCursor CURSOR FOR select htbh,id from ms_htxx_info where  request_id is  null;   #循环游标   上游合同的编号

  --  发展经营部合同台账中的累计开票金额等于发票管理 ms_fpgl 中对应上游合同编号的 kpjey(开票金额元)累加
  --                        累计收款金额等于合同收款确认单 ms_htskjs 对应的合同编号的收款金额(shje)合计 shje
  --                        初期应收款等于合同金额
  --                        末期应收账款等于和合同金额-累计已收款金额
  
  DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_products = 1;  
  OPEN myCursor;  #打开游标
  
  REPEAT   #进入循环
    #循环的游标赋值 
    FETCH myCursor INTO v_htbh,v_id;    #赋值给变量
    #-------------------------------  
    #插入上游合同
    IF NOT no_more_products THEN   #当no_more_products!=1继续循环

    INSERT INTO yqt_fzjybhttz(htbh,htlb,htsx,sftzl,qdrq,htqsrq,htjzrq,htzj,yssj,yssx,sl,ljkpje,ljskje,qcyszk,qmysk)
    VALUES(
        (select htbh from ms_htxx_info where id=v_id),    #合同编号
        (select lbmc from ms_helb where id=(select htlb from ms_htxx_info where id=v_id)),  #和类别
        (select option_text from spms_option where table_name='ms_htxx_info' and field_name='htsxnhw' and option_val=(select htsxnhw from ms_htxx_info where id=v_id )),  #合同属性
        (select option_text from spms_option  where table_name='ms_htxx_info' and field_name='htlb' and option_val=(select sftzl from ms_htxx_info where id=v_id)),       #是否投资类
        (select htqdrq from ms_htxx_info where id=v_id),   #合同签订日期
        (select htksrq from ms_htxx_info where id=v_id),   #合同起始日期
        (select htjzrq from ms_htxx_info where id=v_id),   #合同截至日期
        (select htje from ms_htxx_info where id=v_id),     #合同总价
         null,null,   #验收时间，验收手续
        (select sl from ms_htxx_info where id=v_id),    #税率
        (select sum(ifnull(kpjey,0)) from ms_fpgl where htbh=v_id and request_id is null),     #累计开票金额
        (select sum(ifnull(shje,0)) from ms_htskjs where htbh=v_id and request_id is null),   #累计收款金额
        (select htje from ms_htxx_info where id=v_id),   #初期应收款
        ((select htje from ms_htxx_info where id=v_id)-ifnull((select sum(ifnull(shje,0)) from ms_htskjs where htbh=v_id and request_id is null),0))   #末期应收款   合同金额-已收款金额
     );

-- (select id from yqt_sxyhtdyb where xmid=(select xmmc from ms_fbht where syhtbh=v_id limit 1) and 
    --       xyhtbh=(select htbh from ms_htxx_info where id=v_htbh) and htbh=(select htbh from ms_fbht where syhtbh=v_id limit 1));

    END IF;                               
    UNTIL  no_more_products = 1  #结束循环
    #--------------------------------
  
END REPEAT;
  CLOSE myCursor ;
end

只是为了记载本人写存储过程的印记，很low。欢迎指正