oracle对随机抽样的实现方案 -- 乱数排列

zhgc · 发表于 2019-7-12 16:40:04

CREATE OR REPLACE PROCEDURE SFIS1.sp_oba_control (
p_line IN VARCHAR2,
DATA IN VARCHAR2,
mygroup IN VARCHAR2,
res OUT VARCHAR2
)
AS
c_wip VARCHAR2 (25);
c_model VARCHAR2 (25);
c_mo VARCHAR2 (25);
c_rate NUMBER (10, 2);
c_hh NUMBER;
c_cycle NUMBER;
c_count NUMBER;
c_defect NUMBER;
c_serial_number_no NUMBER;
c_shift VARCHAR2 (25);
c_model_type VARCHAR2 (25);
c_sn VARCHAR2 (25);
c_group VARCHAR2 (25);
c_groupname VARCHAR2 (25);
c_wipgroup VARCHAR2 (25);
c_linename VARCHAR2 (25);
line VARCHAR2 (25);
c_test_value2 NUMBER;
scrap VARCHAR2 (1);
c_found BOOLEAN;
c_begin NUMBER;
c_end NUMBER;
c_qty NUMBER;
c_debug varchar2(35);
c_tmp varchar2(10);
CURSOR getobaroute
IS
SELECT data3, data4, data2, qa_rate
FROM sfis1.c_model_config_t
WHERE model_name = c_model
AND data3 = line
AND data4 = mygroup
AND CLASS = c_shift
AND qa_rate <> '0';
CURSOR debugstr
IS
SELECT substr(lpad(kp_order,3,'0'),-2,2)
FROM sfis1.c_rand_order_t a
WHERE a.model_name = c_model
AND a.line_name = line
AND a.group_name = mygroup
AND TO_NUMBER (a.serial_number) BETWEEN 1 AND 10;
BEGIN
c_sn := DATA;
res := 'OK, NO SN';
SELECT mo_number, model_name, scrap_flag, group_name, wip_group,line_name
INTO c_mo, c_model, scrap, c_group, c_wip,line
FROM sfism4.r_wip_tracking_t
WHERE serial_number = DATA AND ROWNUM = 1;
c_shift := 'N';
c_hh := TO_CHAR (SYSDATE, 'HH24');
IF (c_hh >= 8 AND c_hh < 20)
THEN
c_shift := 'D';
END IF;
/*
SELECT model_type
INTO c_model_type
FROM sfis1.c_model_desc_t
WHERE model_name = c_model;
*/-- IF (c_model IN ('52-001523')) AND (mygroup = 'OBA1')
--THEN
-- select data3 line,
-- data4 group,
-- data2 wip
-- from SFIS1.C_MODEL_CONFIG_T
res := '';
SELECT COUNT (1)
INTO c_count
FROM sfis1.c_model_config_t
WHERE model_name = c_model
AND data3 = line
AND data4 = mygroup
AND CLASS = c_shift
AND qa_rate <> '0';
IF (c_count = 0)
THEN
GOTO endproc;
END IF;
-- res := 'OK, NO CONFIG11';
c_found := TRUE;
c_begin := 0;
c_end := 0;
-- SELECT data3, data4, data2, qa_rate
-- FROM sfis1.c_model_config_t
-- WHERE model_name = c_model and data3=line AND data4 = mygroup and class=c_shift AND qa_rate <> '0';
OPEN getobaroute;
LOOP
FETCH getobaroute
INTO c_linename, c_groupname, c_wipgroup, c_rate;
DBMS_OUTPUT.put_line ( c_linename
|| ' '
|| c_groupname
|| ' '
|| c_wipgroup
|| ' '
|| c_rate
);
EXIT WHEN getobaroute%NOTFOUND;
-- EXIT WHEN c_found = TRUE;
--c_rate := FLOOR (1 / c_rate);
c_rate := FLOOR (c_rate);
IF (c_rate > 100)
THEN
c_rate := 100;
END IF;
c_begin := c_end + 1;
c_end := c_end + c_rate;
SELECT COUNT (1)
INTO c_count
FROM sfism4.r_product_order_t
WHERE serial_number = c_sn AND group_name = mygroup AND ROWNUM = 1;
IF (c_count = 0) OR (NOT c_found)
THEN
c_found := FALSE;
SELECT COUNT (1)
INTO c_count
FROM sfism4.r_product_order_t
WHERE serial_number = c_sn AND group_name = mygroup AND ROWNUM = 1;
IF (c_count = 0)
THEN
--SELECT SFIS1.QA_SEQUENCE.NEXTVAL into c_count FROM DUAL;
SELECT COUNT (1)
INTO c_count
FROM sfism4.r_product_order_t
WHERE model_name = c_model
AND line_name = line
AND data1 = c_shift
AND group_name = mygroup AND ROWNUM=1;
-- MO_NUMBER=C_MO ; -- NOT LINE ..
IF (c_count = 0)
THEN
c_count := 101; -- set to more than 100
c_cycle := 1;
ELSE
SELECT MAX (kp_order) + 1, MAX (kp_cycle)
INTO c_count, c_cycle
FROM sfism4.r_product_order_t
WHERE model_name = c_model
AND line_name = line
AND data1 = c_shift
AND group_name = mygroup ;
--MO_NUMBER=C_MO ;
END IF;
/*INSERT INTO sfism4.r_product_order_t
(serial_number, model_name, mo_number, line_name,
group_name, work_time, kp_order, kp_cycle,
wip_group, data1, data2
)
VALUES (c_sn, c_model, c_mo, line,
mygroup, SYSDATE, c_count, c_cycle,
c_wip, c_shift, 'SYANT'
); */
res := 'OK, NO CONFIG11';
IF ((c_count MOD 100) = 1)
THEN
INSERT INTO sfism4.r_product_order_t_bak
SELECT *
FROM sfism4.r_product_order_t
WHERE model_name = c_model
AND line_name = line
AND data1 = c_shift
AND group_name = mygroup and kp_cycle=c_cycle;
/*delete
FROM sfism4.r_product_order_t
WHERE model_name = c_model
AND line_name = line
AND data1 = c_shift
AND group_name = mygroup and kp_cycle=c_cycle ; */
c_cycle:=c_cycle+1;
-- get new random data now!
DELETE FROM sfis1.c_rand_order_t
WHERE model_name = c_model
AND line_name = line
AND group_name = mygroup;
INSERT INTO sfis1.c_rand_order_t
(serial_number, model_name, line_name, group_name,
kp_order, kp_cycle)
SELECT ROWNUM, c_model, line, mygroup, DATA, c_cycle
FROM (SELECT DATA
FROM sfis1.c_order_t
ORDER BY DBMS_RANDOM.VALUE);
END IF;
INSERT INTO sfism4.r_product_order_t
(serial_number, model_name, mo_number, line_name,
group_name, work_time, kp_order, kp_cycle,
wip_group, data1, data2
)
VALUES (c_sn, c_model, c_mo, line,
mygroup, SYSDATE, c_count, c_cycle,
c_wip, c_shift, 'SYANT'
);
/* INSERT INTO sfism4.r_product_order_t
(serial_number, model_name, mo_number, line_name,
group_name, work_time, kp_order, kp_cycle,
wip_group, data1, data2
)
VALUES (c_sn, c_model, c_mo, line,
mygroup, SYSDATE, c_count, c_cycle,
c_wip, c_shift, 'SYANT'
); */
END IF;
SELECT kp_order
INTO c_count
FROM sfism4.r_product_order_t
WHERE serial_number = c_sn
AND line_name = line
AND group_name = mygroup
AND ROWNUM = 1;
-----------------------
c_debug:='';
OPEN debugstr;
LOOP
FETCH debugstr INTO c_tmp;
EXIT WHEN debugstr%NOTFOUND;
c_debug:=c_debug||c_tmp||'-';
END LOOP;
CLOSE debugstr;
uPDATE sfism4.r_product_order_t
SET data2 = c_debug
WHERE serial_number = c_sn
AND line_name = line
AND group_name = mygroup
AND ROWNUM = 1;
-------------------
c_count := c_count MOD 100;
if(c_count=0)then
c_count:=100;
end if;
SELECT COUNT (1)
INTO c_qty
FROM sfis1.c_rand_order_t a
WHERE a.model_name = c_model
AND a.line_name = line
AND a.group_name = mygroup
AND a.kp_order = c_count
AND TO_NUMBER (a.serial_number) BETWEEN c_begin AND c_end;
-- UPDATE sfism4.r_product_order_t
-- SET data2 = c_count || ' ' || c_begin || ' ' || c_end
-- WHERE serial_number = c_sn; --------------
--IF ((c_count MOD c_rate) = 0)
IF (c_qty > 0)
THEN
-- UPDATE sfism4.r_product_order_t
-- SET data2 = '55'
-- WHERE serial_number = c_sn; --------------
-- c_found := TRUE;
UPDATE sfism4.r_product_order_t
SET kp_flag = 1,
kp_cycle = c_cycle,
wip_group = c_wipgroup
WHERE serial_number = c_sn AND group_name = mygroup
AND ROWNUM = 1;
UPDATE sfism4.r107
SET wip_group = c_wipgroup,
next_station = c_wipgroup,
rework_no = 'OBA'
WHERE serial_number = c_sn
AND group_name = c_groupname
AND error_flag = 0;
INSERT INTO sfism4.r117
SELECT *
FROM sfism4.r107
WHERE serial_number = c_sn;
END IF;
END IF;
EXIT WHEN getobaroute%NOTFOUND;
END LOOP;
CLOSE getobaroute;
-- res:='OK';
<<endproc>>
c_count := '';
END;
/

复制代码

zhgc · 发表于 2019-7-12 16:45:57

核心代码：

SELECT ROWNUM,  DATA
                  FROM (SELECT DATA
                           FROM sfis1.c_order_t
                        ORDER BY DBMS_RANDOM.VALUE)

得到的结果如下：

ROWNUM|DATA
1|54
2|52
3|25
4|75
5|82
6|17
7|53
8|61
9|34
10|20
11|12
12|18
13|37
14|32
15|48
16|26
17|92
18|78
19|43
20|22
21|86
22|19
23|66
24|1
25|7
26|56
27|3
28|16
29|64
30|38
31|27
32|76
33|5
34|88
35|91
36|4
37|87
38|11
39|23
40|40
41|8
42|94
43|74
44|14
45|30
46|85
47|31
48|21
49|42
50|29
51|63
52|36
53|84
54|24
55|13
56|58
57|73
58|67
59|6
60|33
61|100
62|28
63|83
64|89
65|2
66|99
67|96
68|79
69|68
70|71
71|95
72|9
73|41
74|62
75|72
76|51
77|70
78|69
79|97
80|46
81|55
82|57
83|49
84|80
85|98
86|50
87|47
88|15
89|60
90|90
91|45
92|35
93|39
94|81
95|10
96|77
97|59
98|65
99|44
100|93

那mysql中如何实现呢？

zhgc · 发表于 2019-7-12 16:46:32

mysql 参考

SELECT * FROM sfc.c_line_desc_t ORDER BY RAND()

		自动登录	找回密码
密码			立即注册

oracle对随机抽样的实现方案 -- 乱数排列

浏览过的版块