declare m0 int :=&m;--输入第一个数 n0 int :=&n;--输入第二个数 m int :=&m;--输入第一个数 n int :=&n;--输入第二个数 v_tem int;--最大公约数 beginwhile(m!=n) loop
select sign(m-n) into v_tem from dual;
if (v_tem>0) then m:=m-n; else n:=n-m; end if;
end loop; dbms_output.put_line(to_char(m0)||':'||to_char(n0)||'='||to_char(m0/n)||':'||to_char(n0/n)); end ;
SQL> select * from t1;
ID1 ID2 ---------- ---------- 4 2 7 3 11 11 SQL> CREATE OR REPLACE FUNCTION func_1(p_1 IN NUMBER, p_2 IN NUMBER) 2 RETURN NUMBER IS 3 v_result NUMBER; 4 BEGIN 5 WITH t AS( 6 SELECT p_1 AS DATA 7 FROM DUAL 8 UNION ALL 9 SELECT p_2 10 FROM DUAL), 11 t_min AS (SELECT MIN(data) AS min_data FROM t), 12 list AS (SELECT ROWNUM RN 13 FROM t_min 14 CONNECT BY ROWNUM <= min_data) 15 SELECT MAX(rn) 16 INTO v_result 17 FROM list 18 WHERE NOT EXISTS (SELECT 1 FROM t WHERE MOD(data, rn) > 0); 19 RETURN v_result; 20 END; 21 /
放各位 5L是高手,8L的也比较经典,但通用性不强 我参照5L写了个返回两个正整数的最大公约数的Function CREATE OR REPLACE FUNCTION Greatest_COMM_DEN( V_A in POSITIVE, V_B in POSITIVE ) RETURN POSITIVE IS NUM_A POSITIVE := V_A; NUM_B POSITIVE := V_B; /****************************************************************************** NAME: Greatest_COMM_DEN PURPOSE: 返回兩個正整數的最大公約數 REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 2010/6/21 1. Created this function. NOTES: Automatically available Auto Replace Keywords: Object Name: Greatest_COMM_DEN Sysdate: 2010/6/21 Date and Time: 2010/6/21, 上午 10:17:12, and 2010/6/21 上午 10:17:12 Username: (set in TOAD Options, Procedure Editor) Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/ BEGIN while not (NUM_A = NUM_B) loop if(NUM_A>NUM_B) then NUM_A := NUM_A - NUM_B; else NUM_B := NUM_B - NUM_A; end if; end loop; RETURN NUM_A; END Greatest_COMM_DEN;/
m0 int :=&m;--输入第一个数
n0 int :=&n;--输入第二个数
m int :=&m;--输入第一个数
n int :=&n;--输入第二个数
v_tem int;--最大公约数
beginwhile(m!=n)
loop
select sign(m-n) into v_tem from dual;
if (v_tem>0) then
m:=m-n;
else
n:=n-m;
end if;
end loop;
dbms_output.put_line(to_char(m0)||':'||to_char(n0)||'='||to_char(m0/n)||':'||to_char(n0/n));
end ;
ID1 ID2
---------- ----------
4 2
7 3
11 11
SQL> CREATE OR REPLACE FUNCTION func_1(p_1 IN NUMBER, p_2 IN NUMBER)
2 RETURN NUMBER IS
3 v_result NUMBER;
4 BEGIN
5 WITH t AS(
6 SELECT p_1 AS DATA
7 FROM DUAL
8 UNION ALL
9 SELECT p_2
10 FROM DUAL),
11 t_min AS (SELECT MIN(data) AS min_data FROM t),
12 list AS (SELECT ROWNUM RN
13 FROM t_min
14 CONNECT BY ROWNUM <= min_data)
15 SELECT MAX(rn)
16 INTO v_result
17 FROM list
18 WHERE NOT EXISTS (SELECT 1 FROM t WHERE MOD(data, rn) > 0);
19 RETURN v_result;
20 END;
21 /
Function created
SQL> select id1,id2,func_1(id1,id2) from t1;
ID1 ID2 FUNC_1(ID1,ID2)
---------- ---------- ---------------
4 2 2
7 3 1
11 11 11
SQL> select id1,id2,id1/func_1(id1,id2)||':'||id2/func_1(id1,id2) from t1;
ID1 ID2 ID1/FUNC_1(ID1,ID2)||':'||ID2/
---------- ---------- --------------------------------------------------------------------------------
4 2 2:1
7 3 7:3
11 11 1:1
5L是高手,8L的也比较经典,但通用性不强
我参照5L写了个返回两个正整数的最大公约数的Function
CREATE OR REPLACE FUNCTION Greatest_COMM_DEN(
V_A in POSITIVE,
V_B in POSITIVE
) RETURN POSITIVE
IS
NUM_A POSITIVE := V_A;
NUM_B POSITIVE := V_B;
/******************************************************************************
NAME: Greatest_COMM_DEN
PURPOSE: 返回兩個正整數的最大公約數 REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2010/6/21 1. Created this function. NOTES: Automatically available Auto Replace Keywords:
Object Name: Greatest_COMM_DEN
Sysdate: 2010/6/21
Date and Time: 2010/6/21, 上午 10:17:12, and 2010/6/21 上午 10:17:12
Username: (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/
BEGIN
while not (NUM_A = NUM_B)
loop
if(NUM_A>NUM_B) then
NUM_A := NUM_A - NUM_B;
else
NUM_B := NUM_B - NUM_A;
end if;
end loop;
RETURN NUM_A;
END Greatest_COMM_DEN;/