函数

知识加法器
76 阅读6分钟

[TOC]

函数

  • 单行函数：每条记录都会执行，如字将母转为大写

  • 多行函数（聚合函数，组函数）：多条记录求结果，如最大值

  • 系统函数

  • 自定义函数

常用函数

字符函数

函数说明
lower(x)将字符串x转换成小写
upper(x)将字符串x转换成大写
initcap(x)将字符串x首字母成大写，其余小写
函数 说明 示例 值
concat(str1,str2) 连接两个值，等同于|| CONCAT('Good', 'String') GoodString
substr(str,n1[,n2]) 截取str，第n1位开始长度为n2，n2可选。 SUBSTR('String',1,3) Str
length(str) 取字符长度 LENGTH('String') 6
instr(str,str2,[,n1],[n2]) 查找str中，str2从n1开始，第n2次出现的位置。n1,n2默认值为1 INSTR('String', 'r') 3
lpad(str,n1,s2) str以s2从左填充到n1长度后的字符串 LPAD(sal,10,'*') ******5000
rpad(str,n1,s2) str以s2从右填充到n1长度后的字符串 RPAD(sal,10,'*') 5000******
trim(st from str) 去除字符串头尾的字符st TRIM('S' FROM 'SSMITH') MITH
replace(str,x,y) 把str中的x用y替换 REPLACE（'abc','b','d') adc

日期函数

Oracle默认的日期格式是：DD-MON-RR

SELECT sysdate  FROM dual;
# 2023-01-16 20:33:07

SELECT sysdate+21  FROM dual;
# 2023-02-06 20:33:38


SELECT months_between(
    to_date('2023-01-16','yyyy-mm-dd'),
    to_date('2022-01-16','yyyy-mm-dd') 
)  FROM dual;
# 12
函数 说明
sysdate 返回系统日期
months_between(日期1,日期2) 返回两个日期类型数据之间间隔的自然月数
add_months(指定日期,月数) 返回指定日期加上相应的月数后的日期
next_day(某一日期,下一个指定日期) 返回某一日期的下一个指定日期
last_day(指定日期) 返回指定日期当月最后一天的日期
round(date[,'fmt']) 将date按照fmt指定的格式进行四舍五入，fmt为可选项，如果没有指定fmt，则默认为DD，将date四舍五入为最近的天。
格式码：世纪CC，年YY，月MM，日DD，小时HH24，分MI，秒SS
trunc(date[,'fmt']) 将date按照fmt指定的格式进行截断，fmt为可选项，如果没有指定fmt，则默认为'DD'，将date截取为最近的天。
extract([year] [month][day] FROM [日期类型表达式]) 返回日期类型数据中的年份、月份或者日。

数值函数

函数 说明 示例 值
round(number, n) 将number四舍五入到小数点后的第n位 round(45.926, 2) 45.93
trunc(number, n) 将number截取到小数点后的第n位 TRUNC(45.926, 2) 45.92
mod(m,n) 取m除以n后得到的余数 mod(1600, 300) 100

转换函数

  • to_char() 转为字符型数据
  • to_date 转为日期型数据
  • to_number 转换为数值型数据
# to_char
SELECT sysdate  FROM dual;
# 2023-01-16 20:33:07

SELECT to_char(sysdate,"yyyy/mm/dd")  FROM dual;
# 2023/01/16

# 中文需要引号引起来
SELECT to_char(sysdate,'yyyy"年"mm"月"dd"日"')  FROM dual;
# 2023年01月16日


# to_date
SELECT to_date('2023-01-16','yyyy-mm-dd')+4  FROM dual;
# 2023-01-20

# to_number
SELECT to_number(11,'xx')  FROM dual;
# 17

TO_CHAR 用于日期型

格式码说明
YYYY完整的年份数字表示
YEAR年份的英文表示
MM用两位数字来表示月份
MONTH月份的全名
DAY星期几
DY用3个英文字符缩写来表示星期几

TO_CHAR 用于数值型

格式码说明
9一位数字
0显示前导零
$显示美元符号
L显示本地货币符号，羊角符
.显示小数点
,显示千位符

通用函数

函数 说名
avg([distinct] x]) 求平均值
count([distinct] x) 统计满足条件的非空的行记录数。
如果是count(*)的话则不会忽略非空的行记录。
max([distinct] x]) 求最大值
min([distinct] x ) 求最小值
sum([distinct] x) 求和

自定义函数

case函数

# 语法
CASE expr
	WHEN comparison_expr1 THEN return_expr1
	[WHEN comparisonexpr2 THEN return_expr2 
    WHEN comparison_exprn THEN return_exprn
	ELSE else_expr]
END

# 示例：
# 在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000， 税率为10％，如果工资大于等于2000并小于3000，税率为 15％，如果工资大于等于3000，税率为20％。
select sal,
 (case
	when sal<1000 then 0
	when sal>=1000 and sal<2000 then sal*0.1
	when sal>=2000 and sal <3000  then sal*0.15
	when sal>=3000 then sal*0.2
	else 0 
 end) 税款 
 from emp;

decode函数

decode(字段 | 表达式 , 条件 1 , 结果 1[, 条件 2 , 结果 2 … ,][,缺省值])

# 示例：
SELECT ename, deptno, 
decode(deptno,
       10,'销售部',
       20,'技术部',
       30, '管理部',
       '无') deptname
FROM emp;

创建函数

create [or replace] function 函数名 ([p1,p2...pn])
return datatype
is | as  
# 声明部分 
begin
 # PL/SQL程序块
end
# function 是创建函数的关键字。
# p1,p2...pn是函数的入参，Oracle创建的函数也可以不需要入参。
# return datatype:是函数的返回值的类型

# 创建函数 输入学生学号输出姓名
create or replace function f1(p in varchar2)   # 函数参数p
return varchar2   # 函数返回类型
is stname varchar2(50) ; # 返回的变量
# 函数结构体
begin
  select s.stuname into stname from stuinfo s where s.stuid=p;
  return stname;
end ;

使用函数

select f1('00001') from dual;

删除函数

DROP FUNCTION f1

存储过程

所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。

创建

create [or replace] procedure 存储过程名
as 返回值 返回类型
begin
  ----------------------------
end;
# 注：
#   在存储过程(PROCEDURE)和函数(FUNCTION)中没有区别；
#   在视图(VIEW)中只能用AS不能用IS；
#   在游标(CURSOR)中只能用IS不能用AS。

# 无参数
create or replace procedure myDemo01
as
begin
  dbms_output.put_line('hello word, my name is stored procedure');
end;

# 有参数
create [or replace] procedure myDemo02(name in varchar,age in int)
as 
begin
  dbms_output.put_line('name='||name||', age='||age);
end;
# in代表输入，out用于输出，参数默认类型是in类型。

create [or replace] procedure myDemo04(name out varchar,age in int)
as
begin
    dbms_output.put_line('age='||age);
    select 'ex_sunqi' into name from dual;
end;

declare
   name varchar(10);
   age int;
begin
   myDemo04(name=>name,age=>25);
   dbms_output.put_line('name='||name);
end;

调用

声明declare关键字

declare
begin
  myDemo01;　#　在此处也可使用myDemo01();完成存储过程的调用
end;

不声明declare关键字

begin
  myDemo01;　#　在此处也可使用myDemo01();完成存储过程的调用
end;

call关键字

call myDemo01(); # call 存储过程名可完成调用，注意括号不能少

删除

DROP procedure myDemo04
avatar
文章
阅读
粉丝