Oracle数据库创建Packge

--规则练习取任务

--类型
create or replace type RuleTask as Object (
   taskId varchar2(20),
   ruleTaskTip varchar2(200),
   ruleTaskName varchar2(200),
   ruleId varchar2(20),
   standardValue  varchar2(500),
   imageName   varchar2(200),
   ruleName varchar2(100)
);

create or replace type RuleTaskTable is table of RuleTask;

--包头
create or replace package Training_RULE_TASK_PACKAGE
is
      type  cur_rowA is ref cursor;

      function training_get_taskteam(v_user_id training_users_rule_info.user_id%TYPE)
                                     return training_users_rule_info.task_team%TYPE;

      -- '根据规则组合查询任务'
      function query_rule_task_userid(user_id in varchar2) return RuleTaskTable;

end Training_RULE_TASK_PACKAGE;

--包体
create or replace package body Training_RULE_TASK_PACKAGE
is
      --‘根据用户查询规则组合’
      FUNCTION training_get_taskteam(v_user_id training_users_rule_info.user_id%TYPE)
      RETURN training_users_rule_info.task_team%TYPE
      IS
             v_task_team  training_users_rule_info.task_team%TYPE;
           
      BEGIN

        
             SELECT task_team INTO v_task_team FROM training_users_rule_info ruleinfo
                                     WHERE ruleinfo.user_id =  v_user_id
                                           and
                                           ruleinfo.is_finished='N';
        
         RETURN v_task_team;

      END training_get_taskteam;

      -- '根据规则组合查询任务'
      FUNCTION query_rule_task_userid(user_id in varchar2) return RuleTaskTable
      is
                v_ruleTaskTable RuleTaskTable;---定义返回变量
             groupIds varchar2(500);
             groupId varchar2(20):='';
             num number :=0;
             total number := 0;
             list_index number := 1;
             v_row training_rule_task%rowtype;
             cur_row cur_rowA;
             ruleName varchar2(100);
            
      begin
              v_ruleTaskTable :=RuleTaskTable();--初始化嵌套表

           select Training_RULE_TASK_PACKAGE.training_get_taskteam(user_id) into groupIds from dual;
             total := length(groupIds)/8;
             while num<total loop
                 if num=0 then
                      select substr(groupIds,1,instr(groupIds,',',1,1)-1) into groupId from dual;
                     
                      open cur_row for select * from training_rule_task where rule_id =groupId order by task_id;
                      loop
                           fetch cur_row into v_row;
                           exit when cur_row%NOTFOUND;
                          
                           --根据任务的规则编号查询所属规则名称
                           select rule_name into ruleName from training_rule_table where rule_id = v_row.rule_id;
                          
                           --添加数据到集合
                           v_ruleTaskTable.extend;
                           v_ruleTaskTable(list_index) := RuleTask(v_row.task_id,v_row.rule_task_tip,v_row.rule_task_name,v_row.rule_id,v_row.standard_value,v_row.image_name,ruleName);
                           list_index := list_index+1;
                      end loop;
                      close cur_row;
                 elsif num>0 then
                        select substr(groupIds,instr(groupIds,',',1,num)+1,instr(groupIds,',',1,1)-1) into groupId from dual;
                        dbms_output.put_line(groupId);

                        open cur_row for select * from training_rule_task where rule_id =groupId order by task_id;
                        loop
                             fetch cur_row into v_row;
                             exit when cur_row%NOTFOUND;
                            
                             --根据任务的规则编号查询所属规则名称
                             select rule_name into ruleName from training_rule_table where rule_id = v_row.rule_id;
                             --添加数据到集合
                             v_ruleTaskTable.extend;
                             v_ruleTaskTable(list_index) := RuleTask(v_row.task_id,v_row.rule_task_tip,v_row.rule_task_name,v_row.rule_id,v_row.standard_value,v_row.image_name,ruleName);
                             list_index := list_index+1;
                        end loop;
                        close cur_row;
                   end if;
                     num:=num+1;
             end loop;
             return v_ruleTaskTable;
      end query_rule_task_userid;

end Training_RULE_TASK_PACKAGE;


--模拟作业取任务

--类型
create or replace type SimulantTask as Object (
    SIMULANT_TASK_ID  VARCHAR2(20),
    IMAGE_TEMPLATE_ID NUMBER(20),
    FIELD_CODE        VARCHAR2(100),
    STANDARD_VALUE    VARCHAR2(1000),
    IMAGE_SOURCE      VARCHAR2(200),
    BUSINESS_TYPE     VARCHAR2(20),
    FIELD_TYPE        VARCHAR2(2),
    FIELD_LEVEL       VARCHAR2(20),
    SIMULATE_TASK_TIP VARCHAR2(200),
    FIELD_ID          NUMBER(20),
    TYPE_RATE         NUMBER(10,2),
    LEVEL_RATE       NUMBER(10,2)
);

create or replace type SimulantTaskTable is table of SimulantTask;

--包头
create or replace package query_training_simulant_task
is

    function query_simulant_task(v_user_id varchar2)return SimulantTaskTable;

end query_training_simulant_task;


--包体
create or replace package body query_training_simulant_task
is

    function query_simulant_task(v_user_id varchar2)return SimulantTaskTable
    is
      v_business_type varchar2(300) := '';
      v_field_type varchar2(300) := '';
      v_field_level varchar2(300) := '';
      str varchar2(200) :='';
      num number :=0;
      total number := 0;
      tasknum number := 0;
      strsql varchar2(1000) := 'select * from (select * from training_simulant_task where (';
      type ref_cursor is ref cursor;
      v_cur ref_cursor;
      v_simulantTaskTable SimulantTaskTable;---定义返回变量
      v_row training_simulant_task%rowtype;
      list_index number := 1;
      type_rate number := 0;
      level_rate number := 0;
    begin
      select business_type into v_business_type from training_users_purview where user_id = v_user_id;
      select field_type into v_field_type from training_users_purview where user_id = v_user_id;
      select field_level into v_field_level from training_users_purview where user_id = v_user_id;
     
      v_simulantTaskTable := SimulantTaskTable();--初始化嵌套表
     
      if (v_business_type is not null) and (v_field_type is not null) and (v_field_level is not null) then
           
        if v_business_type!='-1' then

          total := length(v_business_type)/3;
          while num<total loop
                 if num=0 then
                    select substr(v_business_type,1,instr(v_business_type,',',1,1)-1) into str from dual;
                    strsql := strsql||' business_type = '||str;

                 elsif num>0 then
                    select substr(v_business_type,instr(v_business_type,',',1,num)+1,instr(v_business_type,',',1,1)-1) into str from dual;
                    strsql := strsql||' or business_type = '||str;
                 end if;

                 num:=num+1;
          end loop;

        else
         
          return v_simulantTaskTable;
        end if;

        if v_field_type!='-1' then
          num := 0;
          total := length(v_field_type)/3;
          while num<total loop
                 if num=0 then
                    select substr(v_field_type,1,instr(v_field_type,',',1,1)-1) into str from dual;
                    strsql := strsql||') and ( field_type = '||str;

                 elsif num>0 then
                    select substr(v_field_type,instr(v_field_type,',',1,num)+1,instr(v_field_type,',',1,1)-1) into str from dual;
                    strsql := strsql||' or field_type = '||str;
                 end if;

                 num:=num+1;
          end loop;

        else
         
          return v_simulantTaskTable;
        end if;

        if v_field_level!='-1' then
          str := v_field_level;
          select param_value into tasknum from training_task_param where training_task_param.param_description='任务数';
          strsql := strsql||') and field_level >= '||str||' order by dbms_random.value) task where rownum <= '||tasknum;
        else
          return v_simulantTaskTable;
        end if;
               
        strsql := strsql||' and field_code in (select field_code from training_field_template where field_code = task.field_code)
               and image_template_id in (select image_template_id from training_image_template where image_template_id = task.image_template_id)';
       
        open v_cur for strsql;

        loop
            fetch v_cur into v_row;
            exit when v_cur%NOTFOUND;
            --查询转换率
            select CHAR_CONVERT_RATE into type_rate from TRAINING_FIELD_TYPE where id = v_row.field_type;
            select CHAR_CONVERT_RATE into level_rate from TRAINING_FIELD_LEVEL where id = v_row.field_level;
           
            --添加数据
            v_simulantTaskTable.extend;
           
            v_simulantTaskTable(list_index) := SimulantTask(v_row.simulant_task_id,v_row.image_template_id,v_row.field_code,v_row.standard_value,v_row.image_source,v_row.business_type,v_row.field_type,v_row.field_level,v_row.simulate_task_tip,v_row.field_id,type_rate,level_rate);
            list_index := list_index+1;

        end loop;

        close v_cur;
       
      end if;

      return v_simulantTaskTable;

    end query_simulant_task;

end query_training_simulant_task;