在TESTW表中title字段中有21个值,我需要随机取出这些值并插入到GOODS表中的TITLE字段,
我写了SQL,但是卡在这里
以下是我的语句:
declare
v_ID INTEGER ;
v_id1 integer:=1;
v_GOODS_ID INTEGER ;
v_goods_id1 integer:=1;
v_PRIVILEGE_ID INTEGER ;
v_DEVELOPER_USER_ID INTEGER ;
v_TITLE VARCHAR2(100);
(省略无关字段)
v_f1 number;
v_f2 number;
begin
v_f1 := 0;
loop
v_id := v_id1;
v_goods_id :=trunc(dbms_random.value(0,4));
v_privilege_id :=trunc(dbms_random.value(0,4));
v_developer_user_id :=trunc(dbms_random.value(1,100));
v_title := (需要在这里随机循环取出TESTW中TITLE的值并插入GOODS中的TITLE字段,在这里卡住了)
v_content := dbms_random.string(opt => 'u', len => 20);
v_confirm_status :=trunc(dbms_random.value(0,3));
v_goods_number :=trunc(dbms_random.value(0,9));
v_goods_language_id :=trunc(dbms_random.value(0,4));
v_ispay :=trunc(dbms_random.value(0,2));
v_et_price :=trunc(dbms_random.value(9000,10000));
v_sales_price :=trunc(dbms_random.value(5000,9000));
v_ksrun_id :=trunc(dbms_random.value(0,10));
v_sales_uint_id :=trunc(dbms_random.value(0,2));
v_software_size :=trunc(dbms_random.value(1,1000));
v_size_unit_id :=trunc(dbms_random.value(0,2));
v_goods_type_id :=trunc(dbms_random.value(0,12));
v_logo_path :=dbms_random.string(opt =>'u',len => 20);
v_iscanconfirm :=trunc(dbms_random.value(0,1));
v_undercarriage_time :=to_date(trunc(dbms_random.value(2455928,2455928+364)),'J');
v_confirm_time :=to_date(trunc(dbms_random.value(2455563,2455563+364)),'J');
v_grounding_time :=to_date(trunc(dbms_random.value(2452641,2452641+364)),'J');
v_software_grade :=trunc(dbms_random.value(0,5));
v_grade :=trunc(dbms_random.value(10,1000));
v_weighting_id :=trunc(dbms_random.value(1,10));
v_merchant_type :=trunc(dbms_random.value(0,1));
select count(*) into v_f2 from goods where id = v_id or goods_id = v_goods_id or privilege_id = v_privilege_id or developer_user_id = v_developer_user_id or title = v_title
or content = v_content or confirm_status = v_confirm_status or goods_number = v_goods_number or goods_language_id = v_goods_language_id or ispay = v_ispay or et_price= v_et_price
or sales_price = v_sales_price or ksrun_id = v_ksrun_id or sales_uint_id = v_sales_uint_id or software_size = v_software_size or size_unit_id = v_size_unit_id
or goods_type_id = v_goods_type_id or logo_path = v_logo_path or iscanconfirm = v_iscanconfirm or undercarriage_time = v_undercarriage_time or confirm_time = v_confirm_time
or grounding_time = v_grounding_time or software_grade = v_software_grade or grade=v_grade /*or weighting_id = v_weighting_id*/
or merchant_type=v_merchant_type ;
if v_f2 > 0 then
insert into goods values(getusersequence(), v_goods_id, v_privilege_id,v_developer_user_id,v_title, v_content, v_confirm_status,v_goods_number, v_goods_language_id, v_ispay, v_et_price,
v_sales_price, v_ksrun_id, v_sales_uint_id, v_software_size , v_size_unit_id, v_goods_type_id, v_logo_path, v_iscanconfirm,v_undercarriage_time, v_confirm_time,
v_grounding_time, v_software_grade,v_grade,v_weighting_id,v_merchant_type);
v_f1 := v_f1 + 1;
v_id1 := v_id1 +1;
v_goods_id :=v_goods_id +1;
end if; exit when v_f1 >= 100;
end loop;
commit;
end;
/
我写了SQL,但是卡在这里
以下是我的语句:
declare
v_ID INTEGER ;
v_id1 integer:=1;
v_GOODS_ID INTEGER ;
v_goods_id1 integer:=1;
v_PRIVILEGE_ID INTEGER ;
v_DEVELOPER_USER_ID INTEGER ;
v_TITLE VARCHAR2(100);
(省略无关字段)
v_f1 number;
v_f2 number;
begin
v_f1 := 0;
loop
v_id := v_id1;
v_goods_id :=trunc(dbms_random.value(0,4));
v_privilege_id :=trunc(dbms_random.value(0,4));
v_developer_user_id :=trunc(dbms_random.value(1,100));
v_title := (需要在这里随机循环取出TESTW中TITLE的值并插入GOODS中的TITLE字段,在这里卡住了)
v_content := dbms_random.string(opt => 'u', len => 20);
v_confirm_status :=trunc(dbms_random.value(0,3));
v_goods_number :=trunc(dbms_random.value(0,9));
v_goods_language_id :=trunc(dbms_random.value(0,4));
v_ispay :=trunc(dbms_random.value(0,2));
v_et_price :=trunc(dbms_random.value(9000,10000));
v_sales_price :=trunc(dbms_random.value(5000,9000));
v_ksrun_id :=trunc(dbms_random.value(0,10));
v_sales_uint_id :=trunc(dbms_random.value(0,2));
v_software_size :=trunc(dbms_random.value(1,1000));
v_size_unit_id :=trunc(dbms_random.value(0,2));
v_goods_type_id :=trunc(dbms_random.value(0,12));
v_logo_path :=dbms_random.string(opt =>'u',len => 20);
v_iscanconfirm :=trunc(dbms_random.value(0,1));
v_undercarriage_time :=to_date(trunc(dbms_random.value(2455928,2455928+364)),'J');
v_confirm_time :=to_date(trunc(dbms_random.value(2455563,2455563+364)),'J');
v_grounding_time :=to_date(trunc(dbms_random.value(2452641,2452641+364)),'J');
v_software_grade :=trunc(dbms_random.value(0,5));
v_grade :=trunc(dbms_random.value(10,1000));
v_weighting_id :=trunc(dbms_random.value(1,10));
v_merchant_type :=trunc(dbms_random.value(0,1));
select count(*) into v_f2 from goods where id = v_id or goods_id = v_goods_id or privilege_id = v_privilege_id or developer_user_id = v_developer_user_id or title = v_title
or content = v_content or confirm_status = v_confirm_status or goods_number = v_goods_number or goods_language_id = v_goods_language_id or ispay = v_ispay or et_price= v_et_price
or sales_price = v_sales_price or ksrun_id = v_ksrun_id or sales_uint_id = v_sales_uint_id or software_size = v_software_size or size_unit_id = v_size_unit_id
or goods_type_id = v_goods_type_id or logo_path = v_logo_path or iscanconfirm = v_iscanconfirm or undercarriage_time = v_undercarriage_time or confirm_time = v_confirm_time
or grounding_time = v_grounding_time or software_grade = v_software_grade or grade=v_grade /*or weighting_id = v_weighting_id*/
or merchant_type=v_merchant_type ;
if v_f2 > 0 then
insert into goods values(getusersequence(), v_goods_id, v_privilege_id,v_developer_user_id,v_title, v_content, v_confirm_status,v_goods_number, v_goods_language_id, v_ispay, v_et_price,
v_sales_price, v_ksrun_id, v_sales_uint_id, v_software_size , v_size_unit_id, v_goods_type_id, v_logo_path, v_iscanconfirm,v_undercarriage_time, v_confirm_time,
v_grounding_time, v_software_grade,v_grade,v_weighting_id,v_merchant_type);
v_f1 := v_f1 + 1;
v_id1 := v_id1 +1;
v_goods_id :=v_goods_id +1;
end if; exit when v_f1 >= 100;
end loop;
commit;
end;
/
begin
for c1 in (select * from wl_selected_course_tab) loop
insert into wl_score_tab
values(c1.student_id,c1.course_id,trunc(dbms_random.value(40,101)));
end loop;
end;
select title INTO v_title from testw where rownum = TRUNC(dbms_random.value(1,21));