存储过程

CREATE OR REPLACE PROCEDURE P_BIG_TYPE_COMPONENT(BIG_CATEGORY_ID IN varchar2) 
as 
--DECLARE
cols varchar2(256);-- 只声明
BIG_TABLE_NAME_PREFIX VARCHAR2(4000);
CREATESQL VARCHAR2(4000);
INSERTSQL VARCHAR2(4000);
LEVEL_TABLE_COLS VARCHAR2(4000);
TABLE_NUM NUMBER;

  BEGIN
  BIG_TABLE_NAME_PREFIX := 'CC_BIG_';
  --获取大类下所有字段 （TODO 后续用，隔开数据  wm_concat ORACLE11要替换）
  select cols into cols from (
    select   
    distinct wm_concat(
    nvl2(
       (select soa.name from sm_object_attribute soa where soa.attr_id = cca.attr_id), 
       ((select soa.name from sm_object_attribute soa where soa.attr_id = cca.attr_id) || ' varchar2(256),'),
       ''
    ))
    || 'ID varchar2(256) ' cols  
    From 
      ci_category_attr cca where cca.category_id in
      (select bc.category_id from br_category bc 
      start with bc.category_id = BIG_CATEGORY_ID connect by prior bc.category_id = PARENT_ID)
  );
  --删除表
  SELECT COUNT(1) INTO TABLE_NUM FROM USER_TABLES WHERE TABLE_NAME = UPPER(BIG_TABLE_NAME_PREFIX||BIG_CATEGORY_ID);
  IF TABLE_NUM > 0 THEN
    EXECUTE IMMEDIATE ('drop table ' || BIG_TABLE_NAME_PREFIX||BIG_CATEGORY_ID ) ;
  END IF;
  --创建表
  CREATESQL:='create table ' || BIG_TABLE_NAME_PREFIX||BIG_CATEGORY_ID || '(' || cols ||')';
  dbms_output.put_line(CREATESQL);
  EXECUTE IMMEDIATE CREATESQL ;

--2.将大类下所有叶子节点特有属性表数据查出来 ，获取所有子类， 取出表名， 再查询所有表名， 放入大表中保存； 
     for bc in (select bc.table_name from br_category bc 
           where level = 3
           start with bc.category_id = BIG_CATEGORY_ID connect by prior bc.category_id = PARENT_ID) LOOP
     --小类列，作为大表的属性 (TODO 更改wm_concat方式)
     select COLUMN_NAME into LEVEL_TABLE_COLS from (select wm_concat(COLUMN_NAME) COLUMN_NAME from user_tab_columns where table_name =bc.table_name);    
     dbms_output.put_line('小类（子表）属性:'||LEVEL_TABLE_COLS);
     --如果小类属性（LEVEL_TABLE_COLS）不为空，则将小类数据插入大类表中
     if LEVEL_TABLE_COLS <>' ' then
       INSERTSQL:= 'insert into ' || BIG_TABLE_NAME_PREFIX||BIG_CATEGORY_ID||'('|| LEVEL_TABLE_COLS ||')' || ' select '||LEVEL_TABLE_COLS||' from '|| bc.table_name || '';  
       dbms_output.put_line(INSERTSQL);  
       EXECUTE IMMEDIATE INSERTSQL ;
       COMMIT;
     END if;
     END LOOP;
END;

drop table CC_BIG_CC00000154
call P_BIG_TYPE_COMPONENT('CC00000154');


--遍历大类， 按大类创建带特有属性的大类表：
DECLARE

begin
  for bc in (select bc.category_id from br_category bc where parent_id = '1' ) LOOP
     P_BIG_TYPE_COMPONENT(bc.category_id||'');
  end loop;
end;

---循环-----------------------------------------------

CREATE OR REPLACE PROCEDURE P_CREATE_BIG_TYPE_TABLE as 
begin
  for bc in (select bc.category_id from br_category bc where parent_id = '1' ) LOOP
     dbms_output.put_line(bc.category_id);  
     P_BIG_TYPE_COMPONENT(bc.category_id||'');
  end loop;
end;

CALL P_CREATE_BIG_TYPE_TABLE()

---SQL执行后返回值存储-----------------------------------------------

         EXECUTE IMMEDIATE 'select count(a.com_model) count_pp from jt_material a where a.com_model like ' || '''%' ||bc.com_model|| '%''' into count_pp;

------单引号转义----------------------------------------------
查看编译错误：
打开cmd窗口，执行时候后面加/ , 然后show error ，即可看到错误详情