数据库函数、存储过程

 

 

 

 

 

 

 

-- mysql 数字函数

-- ABS(x):返回x的绝对值

select ABS(-2) from dual;

 

-- ceil(x):返回不小于x的最小整数值 大于等于

select ceil(-2.1) from dual;

select ceil(2.3) from dual;

 

-- floor(x):返回不大于x的最大整数值  小于等于

select floor(2.1) from dual;

 

-- mod(x,y) :返回x/y的模 (取余)

select mod (5,3) from dual;

 

-- rand():返回0~1内的随机值

select rand() from dual;

 

-- round(x,y):返回参数x的四舍五入的有y位小数的值

select round(3.1415926,2) from dual;

 

-- truncate(x,y):返回数字x截断位y位小数的结果

select truncate(3.1415926,5) from dual;

 

-- mysql 聚合函数

-- avg():返回某列的平均值

select round(avg(prod_price),2) from products ;

 

-- count():返回某列的行数

select count(*) from products ;

-- max():返回某列的最大值

select max(prod_price) from products ;

-- min(): 返回某列的最小值

select min(prod_price) from products ;

 

-- sum(): 返回某列之和

select sum(prod_price) from  products ;

-- mysql字符串函数

-- concat(s1,s2,....):字符串连接

select concat('A','B','C') from dual;

 

-- left(str,x):返回字符串str最左边的x个字符

select left('123456',3) from dual;

 

-- lpad(str,n,pad):在str最左边填充n个pad

select lpad('123',6,'1') from dual;

 

-- replace(str,s1,s2):用字符串s2替换字符str中所有出现的字符串s1

select replace ('123333452','3','8') from dual;

-- substring(str,x,y):返回从字符串str的x位置起y个字符长度的字符

select substring('123456','2',5) from dual;

 

-- mysql 日期函数

-- now(): 返回当前的日期和时间

select now() from dual;

 

-- year(data): 返回日期data的年份

select year ('2022-03-15') from dual;

 

-- date_format(date,fmt):返回按字符串fmt格式日期date值(MySQL下)

select date_format(now(),'%Y-%m-%d') from dual; 

-- to_char(date,fmt): 返回按字符串fmt格式日期date值（Oracle下）

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM dual; 

 

-- date_add(date,interval expr type):返回一个日期或时间值加上一个时间间隔的时间值

select date_add('2022-03-10',interval 2 day) from dual;

select date_add('2022-03-10',interval -1 day) from dual;

 

-- datediff(expr,expr2):返回起始时间expr和结束时间expr2 之间的天数

select datediff('2022-03-01','2022-03-11') from dual;

 

-- ifnull(value1,value2):如果value不为空，返回value1，否则返回value2

select ifnull(prod_id,2) from products ;

 

-- case when [value1] then[result1].....else[default] end:如果value1是真，返回result1,否则返回result

select prod_id ,prod_name ,prod_price ,

case 

when prod_price >=10 then 'A'

when prod_price >=8 then 'B'

when prod_price >=6 then 'C'

else 'D'

end as '等级'

from products;

运行结果：

 

1. 函数示例一、二、三，手动敲一遍。

-- 函数示例一

-- 创建一个函数hello(name),返回hello+name;

-- mysql

drop function if exists hello;

create function hello(name varchar(10))

returns varchar(30)

begin

# 定义变量

declare result varchar(30);

# 赋值运算，使用字符串连接函数把两个字符串连接起来

set result =concat('hello,',name);

return result;

end;

-- oracle

CREATE OR REPLACE FUNCTION hello(name varchar2)

RETURN varchar2

AS 

--定义变量

RESULT varchar2(30);

BEGIN

-- 赋值运算，使用字符串连接函数把两个字符串连接起来

RESULT :='hellp,'||NAME;

RETURN RESULT;

END;

-- 测试

select hello('word') from dual;

运行结果：

 

 

-- 函数示例二

-- 创建一个函数max_number(var1,var2),返回其中较大的值

-- mysql

create function max_number(var1 int,var2 int)

returns int 

begin

if var1>var2 then

return var1;

else

return var2;

end if;

end;

-- oracle

CREATE OR REPLACE FUNCTION max_number(var1 NUMBER,var2 number)

RETURN NUMBER 

AS 

BEGIN

IF var1>var2 then

RETURN var1;

ELSE

RETURN var2;

END IF;

END;

-- 测试

select max_number(100,200) from dual;

运行结果：

 

-- 函数示例三

-- 根据需求判断输入时间属于前一天或当天，例如小于10点为前一天，超过10点即为当天

-- mysql

create function get_date(v_datetime datetime,v_time varchar(50))

returns varchar(50)

begin

declare v_date varchar(50);

declare cDate varchar(50);

declare beginDate varchar(50);

set cDate=date_format(v_datetime,'%Y-%m-%d %H:%i:%s');

set beginDate=concat(date_format(v_datetime,'%Y-%m-%d'),v_time); 

if cDate < beginDate then

  set v_date=date_format(date_add(v_datetime,interval -1 day),'%Y-%m-%d');

else

  set v_date=date_format(v_datetime,'%Y-%m-%d');

 end if;

return v_date;

end;

-- oracle

CREATE  OR REPLACE FUNCTION  get_date(v_datetime date,v_time varchar2)

RETURN  varchar2 AS 

v_date varchar2(50);

cDate varchar2(50);

beginDate varchar2(50);

BEGIN 

cDate := to_char(v_datetime,'YYYY-MM-DD hh24:mi:ss');

beginDate := concat(to_char(v_datetime,'YYYY-MM-DD'),v_time);

IF  cDate < beginDate then

   v_date := to_char(v_datetime-1,'YYYY-MM-DD');

ELSE 

    v_date := to_char(v_datetime,'YYYY-MM-DD');

END  IF ;

RETURN  v_date;

END;

1. 针对函数示例二，在MySQL下创建一个函数get_max_number，用于获取3个整数的最大值。

create function get_max_number(var1 int,var2 int,var3 int)

returns int 

begin

-- declare max_number int;

if var1>var2 and var1>var3 then

return var1;

elseif var2>var1 and var2>var3 then

return var2;

else 

return var3;

end if;

end;

select get_max_number(1,2,3) from dual;

1. 分别在Oracle与MySQL下创建存储过程pro_delete_all_order，用游标的方式从orders表中获取所有订单号，然后将每个订单号的记录删除，包括orders和orderitems的相关记录。 

-- MySql

create procedure pro_delete_all_order()

begin

declare c int(11);

declare total int default 0;

declare done int default false;

declare all_order cursor for select o.order_num from orders o;

declare continue handler for not found set done=true;

set total=0;

open all_order;

fetch all_order into c;

while(not done) do

delete from orderitems where order_num=c;

delete from orders where order_num =c;

set total=total+1;

fetch all_order into c;

end while;

close all_order;

select total;

end;

 call pro_delete_all_order();

--oracle

CREATE OR REPLACE PROCEDURE pro_delete_all_order

AS 

CURSOR all_order IS SELECT * FROM ORDERS o;

BEGIN

FOR temp IN all_order LOOP 

DELETE FROM ORDERITEMS o WHERE o.ORDER_NUM =temp.ORDER_NUM;

DELETE FROM ORDERS o WHERE o.ORDER_NUM =temp.ORDER_NUM;

COMMIT;

END LOOP ;

END;

BEGIN 

pro_delete_all_order;

END;