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;