ORACLE存储过程和常用函数

注： 本篇笔记大部分示例来自网上资料及其他博客。 
目前很多互联网项目，都把复杂的业务操作写到了存储过程里，加快执行速度，提高效率。所以学习存储过程也是必要的。

简单入门例子

示例1

/*不带任何参数存储过程(输出系统日期)*/
create or replace procedure output_date is
begin
dbms_output.put_line(sysdate);
end output_date;
1
2
3
4
5
6

示例2

/*带参数in和out的存储过程*/
create or replace procedure get_username(userid in  varchar2, username out varchar2) is
begin
  select user_name into username  from sm_user where cuserid = userid;
  exception
  when no_data_found then 
   raise_application_error(-20001,'ID不存在!');
end get_username;
1
2
3
4
5
6
7
8
9

关于 游标 if,for 的例子

create or replace procedure peace_if is
cursor var_c is select * from sm_user;
begin
  for temp in var_c loop 
   if temp.user_name = '房莹莹' then dbms_output.put_line('用户名：'|| temp.user_name || '是坏人。');
   elsif temp.user_name = '臧金红' then dbms_output.put_line('用户名：'|| temp.user_name || '是良民。');
   else dbms_output.put_line('用户名：'|| temp.user_name || '不是人。');
   end if;
  end loop;

end peace_if;
1
2
3
4
5
6
7
8
9
10
11
12

关于游标 for,case 的例子

create or replace procedure  peace_case1(ptr  out varchar2,pintr in varchar2) is
cursor cur is select user_name from sm_user where cuserid= pintr ;

begin
  for temp in cur loop
    case temp.user_name 
    when '房莹莹' then ptr := temp.user_name ;
    when '臧金红' then ptr := temp.user_name ;
    else ptr := 'qita';
    end case;
  end loop;

end ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

关于for 循环的例子

create or replace procedure peace_for is
sum1 number := 0;
temp varchar2(500);

begin

for i in 1..9 loop 
   temp :='';
   for j in 1..9 loop 
     sum1 := j * j;
     temp := temp||to_char(i) || ' * ' ||to_char(j) ||' = ' ||to_char(sum1) ||' '; 
   end loop;
   dbms_output.put_line(temp );
end loop;
end peace_for;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

关于游标和loop循环的例子

create or replace procedure peace_loop is
sum1 number :=0;
temp number :=0;

begin
  loop
    exit when  temp >= 10;
    sum1 := sum1 + temp;
    temp := temp + 1;
  end loop;
  dbms_output.put_line(sum1);
end peace_loop;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

create or replace procedure loop_cur is
user_name varchar2(100);
cursor var_cur is select user_name from sm_user;
begin
  open var_cur;
       loop fetch var_cur into user_name;
       exit when var_cur%notfound;
       dbms_output.put_line(user_name);
       end loop;
  close var_cur;
end loop_cur;

1
2
3
4
5
6
7
8
9
10
11
12
13

关于异常处理的例子

示例1：

create or replace procedure peace_exp(in1 in varchar2) is
c_n varchar2(200);
begin
  select  user_name into c_n from sm_user where user_name= in1 ;
  dbms_output.put_line(c_n); 
  exception
    when no_data_found then dbms_output.put_line('try');
    when TOO_MANY_ROWS then dbms_output.put_line('more');

end peace_exp;

示例2：

create or replace procedure peace_insert(c_n in varchar2) is
error exception;
begin
  if c_n = 'ok' then insert into sm_user(cuserid,user_name) values(sys_guid(),c_n);
  elsif c_n = 'nk' then  insert into sm_user(cuserid,user_name) values(sys_guid(),c_n);
  raise error;
  else 
  Dbms_Output.put_line('c_n' || c_n); 
  end if;
  commit;
  exception
  when error then rollback;
  Dbms_Output.put_line('ERRO'); 

end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

关于包的例子 定义包体

create or replace package body peace_pkg 
as 
function test1(in1 in varchar2) 
return number 
as 
temp number; 
begin 
temp := 0; 
return temp; 
end; 
procedure test2 (in2 in varchar2) 
is 
begin 
dbms_output.put_line(in2); 
end; 


end peace_pkg; 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

oracle 常用函数

字符函数

字符函数是oracle中最常用的函数，我们来看看有哪些字符函数： 
lower(char)：将字符串转化为小写的格式。 
upper(char)：将字符串转化为大写的格式。 
length(char)：返回字符串的长度。 
substr(char, m, n)：截取字符串的子串，n代表取n个字符的意思，不是代表取到第n个 
replace(char1, search_string, replace_string) 
instr(C1,C2,I,J) –>判断某字符或字符串是否存在，存在返回出现的位置的索引，否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置; 
C1 被搜索的字符串 
C2 希望搜索的字符串 
I 搜索的开始位置,默认为1 
J 出现的位置,默认为1 
问题：将所有员工的名字按小写的方式显示 
SQL> select lower(ename) from emp; 
问题：将所有员工的名字按大写的方式显示。 
SQL> select upper(ename) from emp; 
问题：显示正好为5个字符的员工的姓名。 
SQL> select * from emp where length(ename)=5; 
问题：显示所有员工姓名的前三个字符。 
SQL> select substr(ename, 1, 3) from emp; 
问题：以首字母大写,后面小写的方式显示所有员工的姓名。 
SQL> select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp; 
问题：以首字母小写,后面大写的方式显示所有员工的姓名。 
SQL> select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp; 
问题：显示所有员工的姓名，用“我是老虎”替换所有“A” 
SQL> select replace(ename,’A’, ‘我是老虎’) from emp; 
问题：instr(char1,char2,[,n[,m]])用法 
SQL> select instr(‘azhangsanbcd’, ‘zhangsan’) from dual; –返回2 
SQL> select instr(‘oracle traning’, ‘ra’, 1, 1) instring from dual; –返回2 
SQL> select instr(‘oracle traning’, ‘ra’, 1, 2) instring from dual; –返回9 
SQL> select instr(‘oracle traning’, ‘ra’, 1, 3) instring from dual; –返回0，根据条件，由于ra只出现二次，第四个参数3，就是说第3次出现ra的位置，显然第3次是没有再出现了，所以结果返回0。注意空格也算一个字符 
SQL> select instr(‘abc’,’d’) from dual; –返回0

数学函数

数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round等 
我们讲最常用的： 
round(n,[m]) 该函数用于执行四舍五入， 
如果省掉m，则四舍五入到整数。 
如果m是正数，则四舍五入到小数点的m位后。 
如果m是负数，则四舍五入到小数点的m位前。 
eg、SELECT round(23.75123) FROM dual; –返回24 
SELECT round(23.75123, -1) FROM dual; –返回20 
SELECT round(27.75123, -1) FROM dual; –返回30 
SELECT round(23.75123, -3) FROM dual; –返回0 
SELECT round(23.75123, 1) FROM dual; –返回23.8 
SELECT round(23.75123, 2) FROM dual; –返回23.75 
SELECT round(23.75123, 3) FROM dual; –返回23.751 
trunc(n,[m]) 该函数用于截取数字。 
如果省掉m，就截去小数部分， 
如果m是正数就截取到小数点的m位后， 
如果m是负数，则截取到小数点的前m位。 
eg、SELECT trunc(23.75123) FROM dual; –返回23 
SELECT trunc(23.75123, -1) FROM dual; –返回20 
SELECT trunc(27.75123, -1) FROM dual; –返回20 
SELECT trunc(23.75123, -3) FROM dual; –返回0 
SELECT trunc(23.75123, 1) FROM dual; –返回23.7 
SELECT trunc(23.75123, 2) FROM dual; –返回23.75 
SELECT trunc(23.75123, 3) FROM dual; –返回23.751 
mod(m,n)取余函数 
eg、select mod(10,2) from dual; –返回0 
SELECT MOD(10,3) FROM dual; –返回1 
floor(n) 返回小于或是等于n的最大整数 
ceil(n) 返回大于或是等于n的最小整数 
eg、SELECT ceil(24.56) from dual; –返回25 
SELECT floor(24.56) from dual; –返回24 
abs(n) 返回数字n的绝对值 
对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报表有不同的结果

日期函数

日期函数用于处理date类型的数据。默认情况下日期格式是dd-mon-yy 即“12-7 月-12” 
(1)sysdate 返回系统时间 
eg、SQL> select sysdate from dual; 
(2)oracle add_months函数 
oracle add_months(time,months)函数可以得到某一时间之前或之后n个月的时间 
eg、select add_months(sysdate,-6) from dual; –该查询的结果是当前时间半年前的时间 
select add_months(sysdate,6) from dual; –该查询的结果是当前时间半年后的时间 
(3)last_day(d)：返回指定日期所在月份的最后一天 
问题：查找已经入职8个月多的员工 
SQL> select * from emp where sysdate>=add_months(hiredate,8); 
问题：显示满10年服务年限的员工的姓名和受雇日期。 
SQL> select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10); 
问题：对于每个员工，显示其加入公司的天数。 
SQL> select floor(sysdate-hiredate) “入职天数”,ename from emp; 
或者 
SQL> select trunc(sysdate-hiredate) “入职天数”,ename from emp; 
问题：找出各月倒数第3天受雇的所有员工。 
SQL> select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

转换函数

转换函数用于将数据类型从一种转为另外一种。在某些情况下，oracle server允许值的数据类型和实际的不一样，这时oracle server会隐含的转化数据类型 
比如： 
create table t1(id int); 
insert into t1 values(‘10’);–这样oracle会自动的将’10’ –>10 
create table t2 (id varchar2(10)); 
insert into t2 values(1); –这样oracle就会自动的将1 –>’1’； 
我们要说的是尽管oracle可以进行隐含的数据类型的转换，但是它并不适应所有的情况，为了提高程序的可靠性，我们应该使用转换函数进行转换。 
to_char()函数 
你可以使用select ename, hiredate, sal from emp where deptno = 10;显示信息，可是，在某些情况下，这个并不能满足你的需求。 
问题：日期是否可以显示 时/分/秒 
SQL> select ename, to_char(hiredate, ‘yyyy-mm-dd hh24:mi:ss’) from emp; 
问题：薪水是否可以显示指定的货币符号 
SQL> 
yy：两位数字的年份 2004–>04 
yyyy：四位数字的年份 2004年 
mm：两位数字的月份 8 月–>08 
dd：两位数字的天 30 号–>30 
hh24： 8点–>20 
hh12：8点–>08 
mi、ss–>显示分钟\秒 
9：显示数字，并忽略前面0 
0：显示数字，如位数不足，则用0补齐 
.：在指定位置显示小数点 
,：在指定位置显示逗号 
$：在数字前加美元 
L：在数字前面加本地货币符号 
C：在数字前面加国际货币符号 
G：在指定位置显示组分隔符、 
D：在指定位置显示小数点符号(.) 
问题：显示薪水的时候，把本地货币单位加在前面 
SQL> select ename, to_char(hiredate, ‘yyyy-mm-dd hh24:mi:ss’), to_char(sal,’L99999.99’) from emp; 
问题：显示1980年入职的所有员工 
SQL> select * from emp where to_char(hiredate, ‘yyyy’)=1980; 
问题：显示所有12月份入职的员工 
SQL> select * from emp where to_char(hiredate, ‘mm’)=12;

to_date()函数 
函数to_date用于将字符串转换成date类型的数据。 
问题：能否按照中国人习惯的方式年—月—日添加日期。 
eg、SELECT to_date(‘2012-02-18 09:25:30’,’yyyy-mm-dd hh24:mi:ss’) FROM dual;

sys_context()系统函数

1)terminal：当前会话客户所对应的终端的标示符，如计算机名 
2)language: 语言 
3)db_name： 当前数据库名称 
4)nls_date_format： 当前会话客户所对应的日期格式 
5)session_user： 当前会话客户所对应的数据库用户名 
6)current_schema： 当前会话客户所对应的默认方案名 
7)host： 返回数据库所在主机的名称 
通过该函数，可以查询一些重要信息，比如你正在使用哪个数据库？ 
select sys_context(‘USERENV’,’db_name’) from dual; 
注意：USERENV是固定的，不能改的，db_name可以换成其它, 
eg、select sys_context(‘USERENV’,’language’) from dual; 
select sys_context(‘USERENV’,’current_schema’) from dual;

查看oracle版本： SELECT * from v$version

trunc函数

1、TRUNC(for dates) TRUNC函数为指定元素而截去的日期值。 
　　其具体的语法格式如下：　TRUNC（date[,fmt]） 
　　其中：date一个日期值，fmt日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去 
下面是该函数的使用情况： 
select trunc(to_date(‘2012-03-23 23:59:59’,’yyyy-mm-dd hh24:mi:ss’)) from dual – return date : 2012-3-23 
　　trunc(sysdate,’yyyy’) –返回当年第一天. 
　　trunc(sysdate,’mm’) –返回当月第一天. 
trunc(sysdate,’d’) –返回当前星期的第一天.

2、TRUNC(for number) 
　　TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。 
　　其具体的语法格式如下TRUNC（number[,decimals]） 
　　其中：number待做截取处理的数值，decimals指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分 
　　下面是该函数的使用情况： 
　　TRUNC（89.985，2）=89.98 
　　TRUNC（89.985）=89 
　　TRUNC（89.985，-1）=80 
　　注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。与取整类似，比如参数为1即取整到十分位，如果是-1，则是取整到十位，以此类推；如果所设置的参数为负数，且负数的位数大于整数的字节数的话，则返回为0。如：TRUNC(89.985,-3)=0.

to_char() ,to_date (),TO_NUMBER()

to char是把日期或数字转换为字符串;to date是把字符串转换为数据库中得日期类型 
TO_CHAR 
使用TO_CHAR函数处理数字：TO_CHAR(number, ‘格式’)；TO_CHAR(salary,’$99,999.99’); 
使用TO_CHAR函数处理日期： TO_CHAR(date,’格式’); 
select to_number(to_char(sysdate,’yyyy’)) from dual –取年 
select to_number(to_char(sysdate,’mm’)) from dual –取月 
select to_number(to_char(sysdate,’dd’)) from dual –取日 
TO_NUMBER 
使用TO_NUMBER函数将字符转换为数字：TO_NUMBER(char[, ‘格式’])

TO_DATE 
使用TO_DATE函数将字符转换为日期：TO_DATE(char[, ‘格式’]) 
select to_date(‘2011-11-5 4:39:57’,’yyyy-mm-dd hh24:mi ss’) as col from dual 
各种格式：

INSTR,SUBSTR函数

INSTR方法的格式为：INSTR(源字符串,目标字符串,起始位置,匹配序号) 
例如：INSTR(‘CORPORATE FLOOR’,’OR’, 3, 2)中，源字符串为’CORPORATE FLOOR’,目标字符串为’OR’，起始位置为3，取第2个匹配项的位置。 
默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。 
所以SELECT INSTR(‘CORPORATE FLOOR’, ‘OR’, -1, 1) “Instring” FROM DUAL的显示结果是：14

substr函数的用法 
取得字符串中指定起始位置和长度的字符串substr( string, start_position, [ length ] ) 
如: 
substr(‘This is a test’, 6, 2) return ‘is’ 
substr(‘This is a test’, 6) return ‘is a test’ 
substr(‘TechOnTheNet’, -3, 3) return ‘Net’ 
substr(‘TechOnTheNet’, -6, 3) return ‘The’ 
select substr(‘Thisisatest’, -4, 2) value from dual 
举个例子更容易区分这两个函数： 
select substr(‘张三:一班:男’,instr(‘张三:一班:男’,’:’,1,1)+1,2) as 班级, instr(‘张三:一班:男’,’:’,1,1)+1 开始位置 from dual 
结果是：班级 开始位置 
一班 4

translate与replace

1.translate 
语法：TRANSLATE(char, from, to) 
用法：返回将出现在from中的每个字符替换为to中的相应字符以后的字符串。 
若from比to字符串长，那么在from中比to中多出的字符将会被删除。 
三个参数中有一个是空，返回值也将是空值。 
举例：SQL> select translate(‘abcdefga’,’abc’,’wo’)返回值from dual; 
返回值——- wodefgw 
分析：该语句要将’abcdefga’中的’abc’转换为’wo’, 
由于’abc’中’a’对应’wo’中的’w’,故将’abcdefga’中的’a’全部转换成’w’; 
而’abc’中’b’对应’wo’中的’o’,故将’abcdefga’中的’b’全部转换成’o’; 
而’abc’中的’c’在’wo’中没有与之对应的字符,故将’abcdefga’中的’c’全部删除; 
简单说来，就是将from中的字符转换为to中与之位置对应的字符，若to中找不到与之对应的字符，返回值中的该字符将会被删除。 
在实际的业务中，可以用来删除一些异常数据，比如表a中的一个字段t_no表示电话号码，而电话号码本身应该是一个由数字组成的字符串， 
为了删除那些含有非数字的异常数据，就用到了translate函数： 
SQL> delete from a， where length(translate(trim(a.t_no), ‘0123456789’ || a.t_no, ‘0123456789’)) <> length(trim(a.t_no)); 
2.replace 
语法：REPLACE(char, search_string,replacement_string) 
用法：将char中的字符串search_string全部转换为字符串replacement_string，没有匹配的字符串就都不变。 
举例： 
SQL> select REPLACE(‘fgsgswsgs’, ‘fk’ ,’j’) from dual;返回值from dual; 
结果是fgsgswsgs 
SQL> select REPLACE(‘fgsgswsgs’, ‘sg’ ,’eeerrrttt’)返回值from dual; 
结果是fgeeerrrtttsweeerrrttts 
分析：第一个例子中由于’fgsgswsgs’中没有与’fk’匹配的字符串，故返回值仍然是’fgsgswsgs’； 
第二个例子中将’fgsgswsgs’中的字符串’sg’全部转换为’eeerrrttt’。 
总结：综上所述，replace与translate都是替代函数，只不过replace针对的是字符串，而translate针对的是单个字符。

Decode()函数

DECODE函数，它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。 
区别于SQL的其它函数，DECODE函数还能识别和操作空值。 
语法：DECODE(control_value,value1,result1[,value2,result2…][,default_result]); 
试图处理的数值。DECODE函数将该数值与后面的一系列的偶序相比较，以决定返回值。 
value1 是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应。 
result1 是一组成序偶的结果值。 
default_result 未能与任何一个值匹配时，函数返回的默认值。 
例如： 
Select decode( x , 1 , ‘x is 1 ’, 2 , ‘x is 2 ’, ‘others’) from dual 
当x等于1时，则返回‘x is 1’。 
当x等于2时，则返回‘x is 2’。 
否则，返回others’。 
需要，比较2个值的时候，可以配合SIGN()函数一起使用。 
SELECT DECODE( SIGN(5 -6), 1 ‘Is Positive’, -1, ‘Is Nagative’, ‘Is Zero’)from dual； 
同样，也可以用CASE实现： 
SELECT CASE SIGN(5 - 6) 
WHEN 1 THEN ‘Is Positive’ 
WHEN　-1 THEN ‘Is Nagative’ 
ELSE　‘Is Zero’ END 
FROM DUAL 
此外，还可以在Order by中使用Decode。 
例如：表subject，有subject_name列。要求按照：语、数、外的顺序进行排序。这时，就可以非常轻松的使用Decode完成要求了。 
select * from subject order by decode(subject_name, ‘语文’,1, ‘数学’,2, ‘外语’,3)

SQL SERVER : 
SELECT a, 
CASE WHEN a=1THEN ‘one’ 
WHEN a=2THEN ‘two’ 
ELSE ‘other’ 
END AS COL 
FROM test;

a | COL 
—+——- 
1 | one 
2 | two 
3 | other 
1 SELECT TOP 3 id, CASE WHEN id=1 THEN ‘one’ WHEN id=2 THEN ‘two’ELSE ‘other’ END as idName FROM users; 
2 SELECT TOP 3 id, CASE ID WHEN 1 THEN ‘one’ WHEN 2 THEN ‘two’ELSE ‘other’ END as idName FROM users; 
1,2 结果一样 
(此处实用oracle与SQL Server一样)

NVL函数

nvl( ) 函数(类似于SQLSERVER的isnull) 
语法: 1. NVL(eExpression1, eExpression2) 
参数: 1. eExpression1, eExpression2 
如果eExpression1的计算结果为null值，则NVL( )返回eExpression2。 
如果eExpression1的计算结果不是null值，则返回eExpression1。 
eExpression1和eExpression2可以是任意一种数据类型。 
如果eExpression1与eExpression2的结果皆为null值，则NVL( )返回.NULL.。 
1. select nvl(a.name,’空得’) as name from student a join school b on a.ID=b.ID 
注意：两个参数得类型要匹配

wm_concat函数

wmsys.wm_concat(column)将多条记录组成一个用逗号隔开的字符串。 
select wmsys.wm_concat(id) aa from tbl 
类似于SQL server的语句：SELECT @temp=@temp + ‘,’ + id from tbl order by id