表t1 字段 c1 c2 c3 测试数据 a a1 a2
b b1 b2
c c1 c2
d d1 d2
e e1 e2select ta1.c1,ta2.c2,ta2.c3 from
(select rownum as r1,c1,c2,c3 from (select c1,c2,c3 from t1 order by c1 ) ) ta1,
(select rownum as r2,c1,c2,c3 from (select c1,c2,c3 from t1 order by c1 desc) ) ta2
where ta1.r1=ta2.r2执行结果:
a e1 e2
b d1 d2
c c1 c2
d b1 b2
e a1 a2
可以根据情况,再对子查询当中的语句进行随机排序......
b b1 b2
c c1 c2
d d1 d2
e e1 e2select ta1.c1,ta2.c2,ta2.c3 from
(select rownum as r1,c1,c2,c3 from (select c1,c2,c3 from t1 order by c1 ) ) ta1,
(select rownum as r2,c1,c2,c3 from (select c1,c2,c3 from t1 order by c1 desc) ) ta2
where ta1.r1=ta2.r2执行结果:
a e1 e2
b d1 d2
c c1 c2
d b1 b2
e a1 a2
可以根据情况,再对子查询当中的语句进行随机排序......
---------- ---------- ---------- ----------
1 number: 1 4
2 number: 2 3
3 number: 3 2
4 number: 4 2
5 number: 5 2
6 number: 6 2
7 number: 7 2
8 number: 8 2
9 number: 9 2
10 number: 10 2
11 number: 11 2
12 number: 12 3
13 number: 13 513 rows selected.2) s为将记录"置乱"的过程,参数为置乱的密钥,约定密钥是多个数字组成的字符串,长度任意。
过程按一定算法对记录进行置乱。置乱后的结果为:SQL> exec s('391'); PL/SQL procedure successfully completed.SQL> select * from t1 order by c1; C1 C2 C3 C4
---------- ---------- ---------- ----------
1 number: 4 4
2 number: 7 3
3 number: 10 2
4 number: 1 2
5 number: 11 2
6 number: 2 2
7 number: 5 2
8 number: 8 2
9 number: 12 2
10 number: 3 2
11 number: 6 2
12 number: 9 3
13 number: 13 513 rows selected.
3) bs为置乱解析函数,参数为c1和密钥,与置乱处理的密钥一致时返回正解。如下:SQL> select c1,bs(rownum,'391') a,c3,c4 from t1 order by c1; C1 A C3 C4
---------- ---------- ---------- ----------
1 number: 1 4
2 number: 2 3
3 number: 3 2
4 number: 4 2
5 number: 5 2
6 number: 6 2
7 number: 7 2
8 number: 8 2
9 number: 9 2
10 number: 10 2
11 number: 11 2
12 number: 12 3
13 number: 13 513 rows selected.
s 与 bs 程序如下:CREATE or REPLACE PROCEDURE s (p VARCHAR2)
AS
TYPE t1_TabType IS TABLE OF t1.c2%TYPE INDEX BY BINARY_INTEGER;
t1_tab t1_TabType;
t2_tab t1_TabType;
lp number;
r0 number;
r1 number;
f1 number;
f2 number;
f3 number;
f4 number;
BEGIN
SELECT c2 bulk collect into t1_tab FROM t1 ORDER BY c1;
lp := length(p);
r1 := trunc( t1_tab.count / lp );
FOR i IN 1..lp * r1
LOOP
f1 := ceil(i/r1);
r0 := to_number(substr(p,f1,1));
f2 := mod(i - 1, r1) + 1;
f4 := mod(f2 - 1 + r0, r1);
t2_tab(((f1 - 1) * r1 ) + f4 + 1) := t1_tab((f2 * lp) - lp + f1);
END LOOP;
FOR i IN 1..lp * r1
LOOP
UPDATE t1 SET c2 = t2_tab(i)
WHERE c1 = i;
END LOOP;
END;
/CREATE OR REPLACE FUNCTION bs (n number,p VARCHAR2)
RETURN t1.c2%type
IS
TYPE t1_tab_Type IS TABLE OF t1.c2%type INDEX BY BINARY_INTEGER;
t1_tab t1_tab_Type;
lp number;
r0 number;
r1 number;
f1 number;
f2 number;
f3 number;
rr t1.c2%type;
BEGIN
SELECT c2 bulk collect into t1_tab FROM t1 ORDER BY c1;
lp := length(p);
r1 := trunc( t1_tab.count / lp );
if n <= lp * r1 THEN
f1 := ceil(n/lp);
f2 := mod(n - 1, lp) + 1;
r0 := to_number(substr(p,f2,1));
rr := (f2 * r1) - r1 + 1 + mod(f1 - 1 + r0, r1);
else
rr := n;
end if; RETURN t1_tab(rr);
END;
/
SQL> select * from t1 order by c1; C1 C2 C3 C4
---------- ---------- ---------- ----------
1 number: 1 4
2 number: 2 3
3 number: 3 2
4 number: 4 2
5 number: 5 2
6 number: 6 2
7 number: 7 2
8 number: 8 2
9 number: 9 2
10 number: 10 2
11 number: 11 2 C1 C2 C3 C4
---------- ---------- ---------- ----------
12 number: 12 3
13 number: 13 513 rows selected.SQL> exec s('43');PL/SQL procedure successfully completed.SQL> select c1,c2,bs(c1,'43') a,c3,c4 from t1 order by c1; C1 C2 A C3 C4
---------- ---------- ----------- ---------- ----------
1 number: 5 number: 1 4
2 number: 7 number: 2 3
3 number: 9 number: 3 2
4 number: 11 number: 4 2
5 number: 1 number: 5 2
6 number: 3 number: 6 2
7 number: 8 number: 7 2
8 number: 10 number: 8 2
9 number: 12 number: 9 2
10 number: 2 number: 10 2
11 number: 4 number: 11 2
12 number: 6 number: 12 3
13 number: 13 number: 13 513 rows selected.SQL>
CREATE or REPLACE PROCEDURE s (p VARCHAR2)
AS
TYPE t1_TabType IS TABLE OF t1.c2%TYPE INDEX BY BINARY_INTEGER;
t1_tab t1_TabType;
lp number;
r0 number;
r1 number;
f1 number;
f2 number;
f3 number;
f4 number;
BEGIN
SELECT c2 bulk collect into t1_tab FROM t1 ORDER BY c1;
lp := length(p);
r1 := trunc( t1_tab.count / lp );
FOR i IN 1..lp * r1
LOOP
f1 := ceil(i/r1);
r0 := to_number(substr(p,f1,1));
f2 := mod(i - 1, r1) + 1;
f4 := mod(f2 - 1 + r0, r1);
UPDATE t1 SET c2 = t1_tab((f2 * lp) - lp + f1)
WHERE c1 = ((f1 - 1) * r1 ) + f4 + 1;
END LOOP;
END;
/