在论坛看到了一题目,写了一个过程,好久没写了,感觉很费劲啊... 贴出结果
题目如下
编写一个算命的存储过程,接收输入的一个生日(yyyymmdd),每2位相加取个位,最后得到一位数字,此数字为偶数(命好),为质数(一般),否则为(命运悲惨).算法如下:
19850324
0735356
708881
78669
5425
967
53
the result is: 8
8 -------------------偶数,命好
代码如下:
-- procedure
CREATE OR REPLACE PROCEDURE SM_PRC(inp_birthday IN VARCHAR2, v_res OUT VARCHAR2) IS
--cursor
CURSOR getTwoNumAdd(v_data VARCHAR2, v_length NUMBER) IS
SELECT to_char(MOD((num+SECOND),10)) a
FROM(
SELECT num,lead(num) over(ORDER BY i_level) SECOND
FROM(
SELECT substr(v_data,i_level,1) num, i_level
FROM (
SELECT LEVEL i_level FROM dual CONNECT BY LEVEL <= v_length
)
)
);
v_birthday VARCHAR2(8);
v_check CHAR;
v_temp VARCHAR2(8);
v_length NUMBER(2,0);
v_result VARCHAR2(8):=' ';
BEGIN
v_birthday := inp_birthday;
-- check the parameter
BEGIN
SELECT nvl(trim(translate(v_birthday,'1234567890'||v_birthday, ' ')),'Y') INTO v_check FROM dual;
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line('input parameter is invalid');
v_res:='null';
RETURN;
END;
IF v_check<>'Y' THEN
dbms_output.put_line('input parameter has non-numper arph,please check. ' || inp_birthday);
v_res:= NULL;
RETURN;
END IF;
dbms_output.put_line(v_birthday);
-- get the parameter's length
BEGIN
SELECT length(v_birthday) INTO v_length FROM dual;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('some error currence, please try again. ');
END;
FOR v_temp IN getTwoNumAdd(v_birthday,v_length)LOOP
v_result := trim(v_result||v_temp.a);
END LOOP;
v_res:=v_result;
END SM_PRC;
test script as below:
DECLARE
v_birthday VARCHAR2(8):='19851001';
v_res VARCHAR2(8):='';
v_length NUMBER(2):=0;
--v_length_res NUMBER(2):=0;
BEGIN
LOOP
SM_PRC(v_birthday,v_res);
v_birthday:=trim(v_res);
BEGIN
SELECT length(v_birthday) INTO v_length FROM dual;
END;
EXIT WHEN v_length=1;
END LOOP;
BEGIN
SELECT decode(v_birthday,
'0','good',
'2','good',
'4','good',
'6','good',
'8','good',
'1','ok',
'3','ok',
'5','ok',
'7','ok',
'7','bad'
) res
INTO v_res
FROM dual;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('error, failed');
v_res := NULL;
END;
dbms_output.put_line('the result is: '||v_res);
END;
the result as above show.