函数
1.1案例:
CREATE OR REPLACE FUNCTION "SCOTT"."GET_AVG_PAY"
(num_deptno number)
RETURN number is
num_avg_pay number;
BEGIN
select avg(sal) into num_avg_pay from emp where deptno=num_deptno;
return (round(num_avg_pay,2));
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('该编号不存在');
return(0);
END;
1.1.1使用variable声明变量a
sql*plus中调用函数的四种方法
--方法1:匿名快
variable a number
begin
:a:=GET_AVG_PAY(10);
end;
/
print a
/
--方法2:call
variable a number
call get_avg_pay(20) into:a;
print a
/
--方法3:execute
variable a number
execute :a:=get_avg_pay(30);
print a
/
--方法4:dual
select get_avg_pay(10) from dual;
1.1.2 使用declare声明变量(注意:末尾的 / 一定要写 否则没法执行!)
set serveroutput on
declare
avg_pay number;
begin
avg_pay:=scott.get_avg_pay(10);
dbms_output.put_line('平均工资是:'||avg_pay);
end;
/
1.2案例:身份证校验
CREATE OR REPLACE FUNCTION "SYSTEM"."FN_CHECKIDCARD" (p_idcard IN VARCHAR2) RETURN INT
IS
v_regstr VARCHAR2 (2000); --可用身份证号
v_sum NUMBER; --求和
v_mod NUMBER; --余数
v_checkcode CHAR (11) := '10X98765432'; --参考校验码
v_checkbit CHAR (1); --计算出的校验码
v_areacode VARCHAR2 (2000) := '11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,82,91,'; --行政代号
BEGIN
CASE LENGTHB (p_idcard)
WHEN 15 -- 15位 一代身份证号码
THEN
IF INSTRB (v_areacode, SUBSTR (p_idcard, 1, 2) || ',') = 0 THEN
RETURN 0;
END IF;
IF MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 400) = 0 -- 闰年
OR
(
MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 100) <> 0
AND
MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 4) = 0
)
THEN
v_regstr :=
'^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}$';
ELSE
v_regstr :=
'^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$';
END IF;
IF REGEXP_LIKE (p_idcard, v_regstr) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
WHEN 18
THEN -- 18位
IF INSTRB (v_areacode, SUBSTRB (p_idcard, 1, 2) || ',') = 0 THEN
RETURN 0;
END IF;
IF MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 400) = 0
OR
(
MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 100) <> 0
AND
MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 4) = 0
)
THEN -- 闰年
v_regstr :=
'^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9Xx]$';
ELSE
v_regstr :=
'^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$';
END IF;
IF REGEXP_LIKE (p_idcard, v_regstr) THEN
v_sum :=
( TO_NUMBER (SUBSTRB (p_idcard, 1, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 11, 1))
)
* 7
+ ( TO_NUMBER (SUBSTRB (p_idcard, 2, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 12, 1))
)
* 9
+ ( TO_NUMBER (SUBSTRB (p_idcard, 3, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 13, 1))
)
* 10
+ ( TO_NUMBER (SUBSTRB (p_idcard, 4, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 14, 1))
)
* 5
+ ( TO_NUMBER (SUBSTRB (p_idcard, 5, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 15, 1))
)
* 8
+ ( TO_NUMBER (SUBSTRB (p_idcard, 6, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 16, 1))
)
* 4
+ ( TO_NUMBER (SUBSTRB (p_idcard, 7, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 17, 1))
)
* 2
+ TO_NUMBER (SUBSTRB (p_idcard, 8, 1)) * 1
+ TO_NUMBER (SUBSTRB (p_idcard, 9, 1)) * 6
+ TO_NUMBER (SUBSTRB (p_idcard, 10, 1)) * 3;
v_mod := MOD (v_sum, 11);
v_checkbit := SUBSTRB (v_checkcode, v_mod + 1, 1);
IF v_checkbit = upper(substrb(p_idcard,18,1)) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
ELSE
RETURN 0;
END IF;
ELSE
RETURN 0; -- 身份证号码位数不对
END CASE;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END fn_checkidcard;
案例中涉及的oracle正则表达式
--一代身份证:15位
--闰年
'^ --匹配输入字符串的 开始 位置
[1-9][0-9]{5} --前6位 省级、地级、县区行政区代码各两位
[0-9]{2} --出生年份 取年的后两位
((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1]) --出生月日 1、3、5、7、8、10、12 即:包含31天的月份
|(04|06|09|11)(0[1-9]|[1-2][0-9]|30) --出生月日 4、6、9、11 即:包含30天的月份
|02(0[1-9]|[1-2][0-9])) --出生月日 闰年2月份有29天
[0-9]{3} --3位数字顺序码
$' --匹配输入字符串的 结束 位置
--平年
'^
[1-9][0-9]{5}
[0-9]{2}
((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])
|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)
|02(0[1-9]|1[0-9]|2[0-8]))
[0-9]{3}
$'
--二代身份证:18位
--闰年
'^
[1-9][0-9]{5}
(19|20)
[0-9]{2}
((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])
|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)
|02(0[1-9]|[1-2][0-9]))
[0-9]{3}
[0-9Xx]
$'
--平年
'^
[1-9][0-9]{5}
(19|20) --出生年份 前两位 19 或 20
[0-9]{2} --出生年份 后两位
((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])
|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)
|02(0[1-9]|1[0-9]|2[0-8])) --出生年月日 平年2月份有28天
[0-9]{3}
[0-9Xx] --校验码 如果是0-9则用0-9表示,如果是10则用X(罗马数字10)表示
$'
2.存储过程案例:
CREATE OR REPLACE PROCEDURE "SCOTT"."PRO_SQUARE"(
num in out number,
flag in boolean) is
i int := 2;
begin
if flag then
num := power(num,i);
else
num:=sqrt(num);
end if;
end;
调用存储过程:
set serveroutput on
declare
var_number number;
var_temp number;
boo_flag boolean;
begin
var_temp :=3;
var_number :=var_temp;
boo_flag :=false;
pro_square(var_number,boo_flag);
if boo_flag then
dbms_output.put_line(var_temp ||'的平方是:'||var_number);
else
dbms_output.put_line(var_temp ||'平方根是:'||var_number);
end if;
end;
/
执行结果:
3平方根是:1.73205080756887729352744634150587236694