King's Studio

Oracle存储过程语法及常用函数总结

Oracle数据库
字数统计: 2k阅读时长: 7 min
2020/04/23 Share

从去年底到现在4个月了，原本早就想对实习期间学习到的知识进行总结，一直拖到现在，中间又是工作的事，又是毕设系统的开发，自己的时间分配确实也有不合理的地方，不能很好地平衡工作和生活，毕竟也是自己第一次实习，还是有许多不足之处需要总结的。今天就对在项目组实习期间使用到的技术进行总结，写的不到位的地方还请大家批评指正😁。

实习期间主要接触到的是数据库相关的内容，涉及到少量的数据清洗，绝大部分还是属于数据抽取以及轻微的加工。我主要负责是的Oracle数据库存储过程的开发，SQL语句功底是做任何开发必不可少的，SQL语句写的好不好直接关系到系统性能的高低，还在学生时代的我就特别害怕编写SQL语句，这次的实习经历就帮我很好地克服了这个困难，也为我今后工作打下了坚实的基础。本次的总结主要分为两部分，一是基本语法，二是常用的函数。

基本语法

创建存储过程

首先是最基本的创建存储过程，我们通过下面的例子来看。

1
2
3
4
5
CREATE OR REPLACE PROCEDURE test1 --关键字用来创建或者覆盖一个已有的存储过程
AS
BEGIN 
	dbms_output.put_line('hello word, my name is stored procedure');--输出打印语句
END;

存储过程的调用

针对上面创建的存储过程我们进行调用，有多种调用的写法。

1
2
3
4
DECLARE
BEGIN
	test1; --在此处完成存储过程的调用
END;
1
2
3
BEGIN
	test1;
END;
1
CALL test1();

变量的申明和赋值

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PROCEDURE test2
AS
NAME VARCHAR2(100);--变量申明，注意VARCHAR需要指定长度
AGE INT;
BEGIN
	NAME:='ROSS';--变量赋值
	AGE:=18;
	dbms_output.put_line('name='||name||', age='||age);--通过||符号达到连接字符串的功能
END;

带参数的存储过程

1
2
3
4
5
CREATE OR REPLACE PROCEDURE test3(name in varchar2(100),age in int) --in表示入参
AS
BEGIN
	dbms_output.put_line('name='||name||', age='||age);
END;
1
2
3
BEGIN
	test3('ross',18);--调用存储过程时传入参数
END;

in，out参数问题

1
2
3
4
5
6
CREATE OR REPLACE PROCEDURE test4(name out varchar2(100),age in int)--in代表输入，out用于输出
AS
BEGIN
	dbms_output.put_line('age='||age);
	select 'ross' into name from dual;--将查询出的结果映射到出参name
END;
1
2
3
4
5
6
7
DECLARE
	name varchar2(100);
	age int;
BEGIN
	test4(name=>name,age=>10);
	dbms_output.put_line('name='||name);
END;

异常问题

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PROCEDURE test5
AS
age int;
BEGIN
	age:=10/0;--除数为0
	dbms_output.put_line(age);
	EXCEPTION WHEN OTHERS THEN--捕获异常并进行处理
		dbms_output.put_line('error');
END;
1
2
3
BEGIN
	test5();
END;

输出结果：

1
error

以上列出的是Oracle存储过程常见的语法，在学会基本语法后，我们就可以进行一些简单逻辑的书写，Oracle存储过程的意义就是将复杂的数据处理逻辑放在数据库，而其他需要和数据库做交互的开发人员只需要关注具体的业务逻辑，而不用纠结于数据的处理逻辑，开发人员直接调用数据库中已经开发好的存储过程就能获得自己需要的数据，如果获得的数据出现问题，只需要在数据库进行解决，而调用者的业务逻辑无需修改，这样各司其职也能很快的找出开发过程中存在的问题。

常用函数

在实习刚开始的时候发现很多函数都没有接触过，属于书到用时方恨少的情况，因此总结常用的函数很有必要，能在编写数据处理逻辑时大大提高效率。

row_number()函数

1
select row_number() over(partition by column1 order by column2) as result;

表示根据column1分组，在分组内根据column2排序，row_number()计算的值就是排序后的编号，如果出现计算的值相同会自动进行排序不会出现值相同的情况。

rank()函数

1
select rank() over(partition by column1 order by column2) as result;

根据column1分组，在分组内根据column2排名计算名次，如果column2相同，名次也相同。

union all和union

union all：对两个结果集进行并集操作，包括重复项，不排序；

union：对两个结果集进行并集操作，不包括重复项，进行默认规则排序。

case when判断

1
2
3
4
5
6
7
8
select case when column1 like '%c%' then
		result1
	when column1 like '%b%' then
		result2
	else
 		result3
 	end as result
from table1;

case when是存储过程中常用的判断语句，表示满足某一个条件，即用then后的值进行展示，case when的用法很灵活，具体操作可以到数据库中进行测试。

substr()函数

1
select substr(string,a,b) from table1;

string表示需要截取的字符串；a表示截取字符串的开始位置，当为0或者1时从第一位开始；b表示要截取的字符串的长度。

instr()函数

1
select instr(string1,string2,start_position,with_number) from dual;

string1表示源字符串；string2表示目标字符串；start_position表示起始位置；with_number表示匹配序号，instr()函数通常用来返回某个字符在指定字符串中的位置。

replace()函数

1
select replace(column1,string1,string2) from table1;

column1表示需要替换的字段名；string1表示原字段内容；string2表示需要喜欢的内容。

trunc()函数

针对日期：

1
2
3
4
select trunc(sysdate,'dd') from dual;--返回当前日期，并且去除时分秒
select trunc(sysdate,'yy') from dual;--返回当年第一天
select trunc(sysdate,'mm') from dual;--返回当月第一天
select trunc(sysdate,'d') from dual;--返回当前周第一天

针对数字：

1
2
3
select trunc(55.5,-1) from dual;--负1表示从小数点左边第一位截取后面全为0，结果为50
select trunc(55.55,1) from dual;--1表示小数点后的保留位数，结果为55.5
select trunc(55.55) from dual;--截取整数部分，结果为55

add_months()函数

1
2
select add_months(sysdate,2) from dual;--表示两个月以后的时间
select add_months(sysdate,-2) from dual;--表示两个月以前的时间

coalesce()函数

1
select coalesce(expression1,espression2,expression3) from dual;

返回参数中第一个不为空的值，如果所有表达式均为空值，最终返回一个空值。

decode()函数

1
select decode(column1,case1,value1,case2,value2,value3) from dual;

decode()函数是常用的对某一个字段的值进行判断的函数，表示判断字段column1，如果满足条件case1，则结果为值value1，如果满足条件case2，则结果为值value2，如果前面的条件都不满足则为值value3。

总结

常用的函数部分，我只列举出了常用的一些，实际上还有很多函数没有列举，具体在存储过程的开发过程中都会碰到，掌握上面的这些基本语法就能够写出带有复杂逻辑的代码了，真正提高SQL能力的还是需要通过多练，多写，不要畏惧SQL语句，具备一定的SQL编写能力之后，还需要具有能够优化的能力，在实际的开发中，写的好的SQL语句和不规范的SQL语句性能差距是相当大的，就拿最常用的left join来说，一个存储过程中如果有大量的left join首先在编译阶段就会消耗大量的时间，这个时候就需要考虑SQL优化的问题，也许有人会说能写出来就很不错了，但一旦遇到数据量特别大的情况，一条性能很差的SQL可能会导致整个系统的性能出现严重的问题，因此SQL优化是一个很重要的能力，Oracle存储过程总结就暂时到这里，后面遇到常用的或者不常用的函数我会再加入进来。

原文作者：金奇

原文链接：https://www.rossontheway.com/2020/04/23/Oracle存储过程语法及常用函数总结/

发表日期：April 23rd 2020, 12:00:00 am

更新日期：April 23rd 2020, 4:34:25 pm

版权声明：本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可，除特别声明外，转载请注明出处！

CATALOG
  1. 1. 基本语法
    1. 1.1. 创建存储过程
    2. 1.2. 存储过程的调用
    3. 1.3. 变量的申明和赋值
    4. 1.4. 带参数的存储过程
    5. 1.5. in，out参数问题
    6. 1.6. 异常问题
  2. 2. 常用函数
    1. 2.1. row_number()函数
    2. 2.2. rank()函数
    3. 2.3. union all和union
    4. 2.4. case when判断
    5. 2.5. substr()函数
    6. 2.6. instr()函数
    7. 2.7. replace()函数
    8. 2.8. trunc()函数
    9. 2.9. add_months()函数
    10. 2.10. coalesce()函数
    11. 2.11. decode()函数
  3. 3. 总结
Total : 52
2023
2021
2020
2019
年度总结 Elasticsearch Java Jenkins自动化部署 Oracle数据库 消息中间件 分享 MyBatis SSM 分布式架构 Spring Boot开发 Spring Boot Vue 大数据之HBase 大数据之MapReduce SpringMVC 大数据技术生态体系 SpringCloud组件 运维 励志 面试