create or replace PROCEDURE FZBH AS
BEGIN
  DECLARE
    strsql VARCHAR2(500); --执行语句
    FZCSBH VARCHAR2(100); --附证场所编号
    FZBH VARCHAR2(2000); --附证编号
    FZ_ID NUMBER; --附证ID
    CURSOR CUR_FZ IS
      SELECT ID FROM T_YJPT_GKWXHWZYFZBLHHZ WHERE RECORD_STATUS<>9; --所有的附证
    CURSOR CUR_FZCS(FZID IN NUMBER) IS
      SELECT DISTINCT FZCSBH FROM T_YJPT_GKWXHWZYFZBL_FZ WHERE RECORD_STATUS<>9 AND FZSQ=FZID AND FZCSBH IS NOT NULL ORDER BY FZCSBH; --所有的附证场所
    
  BEGIN
    
    --拼接附证场所编号
    OPEN CUR_FZ;
      LOOP
        FETCH CUR_FZ
          INTO FZ_ID;
        EXIT WHEN CUR_FZ%NOTFOUND;
          FZBH :='';
          OPEN CUR_FZCS(FZ_ID);
            LOOP
              FETCH CUR_FZCS
                INTO FZCSBH;
              EXIT WHEN CUR_FZCS%NOTFOUND;
                --附证编号是否存在
                
                FZBH := FZBH||chr(10)||FZCSBH;
            END LOOP;
          CLOSE CUR_FZCS;
          
          IF LENGTH(FZBH)>0 THEN
            FZBH := SUBSTR(FZBH,2,LENGTH(FZBH)-1);
          END IF;
          
          --更新附证编号
          strsql := 'UPDATE T_YJPT_GKWXHWZYFZBLHHZ SET BH = :1 WHERE ID = :2';
          --DBMS_OUTPUT.PUT_LINE(strsql);
          EXECUTE IMMEDIATE strsql USING FZBH,FZ_ID;
          COMMIT;
          
      END LOOP;
    CLOSE CUR_FZ;
    
  END;
END FZBH;