第四章 oracle高级查询,事物,过程及函数
(一).当行函数:输入一位,输出一位
当行函数包含(日期函数,数值函数,字符函数,转换函数,其它函数)
1.日期函数:
add_months
months_between
last_day
next_day
2.数值函数(abs,ceil,floor,power,sqrt,mod,round,trunc,sign)
(1)abs:绝对值
(2)ceil:向上取整
(3)floor:向下取整
(4)round(n,[m]):
若m=0,则四舍五入到整数位
若m<0,则四舍五入到小数点前m位
若m>0,则四舍五入到小数点后m位
(5)trunc(n,[m])
若m=0,则将数字n的小数部分截取掉
若m<0,则将n截取到小数点前的m位
若m>0,则将n截取到小数点后的m位
(6)power(n):n次方
(7)sqrt:开平方根
(8)mod:取余
(9)sign:标记数值正负(整数为1,0为0,负数为-1)
3.字符函数(lower,upper,ltrim,rtrim,substr,concat,replace,instr)
(1)lower:将字符串转换为小写
(2)upper:将字符串转换为大写
(3)length:返回字符串长度
(4)ltrim:去左边的空格
(5)rtrim:去右边的空格
(6)substr:截取字符串
例如:select substr('cto',3,2) from dual;
结果为:o.
3:从什么位置开始,2是截取的多少位
(7)replace:替换
例如:select replace('cto','t','e') from dual;
结果为:ceo
(8)instr:取得索引,从1开始
4.转换函数(to_number,to_char,to_date,nvl(expr1,expr2),nvl2(expr1,expr2,expr3))
(1)to_number(将符合数字格式的字符串转变成数字值)
select to_number('2000.22','999999D99') from dual;
结果为:2000.22
(2)to_char()
select to_char(sysdate,'yyyy-mm-dd') from dual;
结果为:2013-12-27
(3)nvl(expr1,expr2)
expr1,expr2可以是任何数据类型,但是两者数据必须要匹配
如果expr1是null，则返回expr2;如果expr1不为null,则返回expr1;
例如:select nvl(1,2) from dual;
结果为:1
select nvl(null,2) from dual;
结果为:2
(4)nvl2(expr1,expr2,expr3)
如果expr1不是null,则返回expr2;如果expr1是null,则返回expr3;
例如:select nvl2(1,2,3) from dual;
结果为:2
select nvl2(null,2,3) from dual;
结果为:3
(5)to_date()
例如:select to_date('13-12-27','yyyy-mm-dd') from dual;
结果为:0013-12-27
(二)多行函数:max,min,avg,sum,count ->group by having
多行函数包含:分组函数,分析函数
(1)分析函数:
分页:row_number() over(order by 条件)
排行:rank,和row_number用法一样
(三)多表查询
1.集合操作符:
(1)union:检索所有不重复的行(补集)
(2)union all:所有行(并集)
(3)intersect:检索查询共有的行(交集)
(4)minus:检索查询之外的行(差集)
2.链接查询:
(1)自连接
例如:emp表包含empno员工号,mgr管理者号,检索员工smith的上级领导
select manager.ename from scott.emp manager,scott.emp worker
where manager.empno=worker.mgr and worker.ename='SMITH';
结果为:FORD
(2)内连接 :inner join .... on
(3)外连接:
左(外)链接:left[outer] join ... on
右(外)连接:right[outer] join ...on
完全外链接:full join ... on
(四)事务处理:事务是用于确保数据库数据的一致性,它由一组相关的DML语句组成.
(1)提交事务(commit)
(2)回退事务(rollback)
[1]设置保存点:是使用SQL命名savepoint来完成,
或者使用包dbms_transaction的过程savepoint来设这保存点.
例如:
savepoint a;
或者
exec dbms_transaction.savepoint('a');
[2]取消部分事务
为了取消部分事务,用户可以退回保存点,使用savepoint来完成,
或者使用包dbms_transaction的过程savepoint.
例如:
rollback to a ;
或者
exec dbms_transaction.rollback_savepoint('a');
[3]取消全部事务(会取消所有事务变化,结束事务,删除所有保存点并释放锁)
例如
rollback;
或者
exec dbms_transaction.rollback('a');
(3)事务的ACID属性:
原子性
一致性
隔离性
持久性
(五)过程和函数
1.创建存储过程
create [or replace] procedure proc_name(argument1 [model1] datatype1,
argument2 [model2] datatype2...)
is [as]
PL/SQL BLOCK;
argument1,argument2为指定过程的参数,当指定参数数据类型时,不能指定其长度
modl是参数模式,包括输入参数(in),输出参数(out),输入输出参数(in out),默认为in.
PL/SQL或者as用于开始执行一个PL/SQL块;
例题:
(1)无参数的存储过程
create or replace procedure proc_name
as
begin
dbms_output.put_line(systimestamp);
end;
declare
begin
proc_name;
end;
结果为:27-12月-13 05.34.38.156000000 下午 +08:00
(2)建立一个带out参数的存储过程
create or replace procedure testout(value1 number,value2 out number)
is
begin
value2:=value1+50;
end;
调用存储过程
declare
result number;
begin
testout(10,result);
dbms_output.put_line(result);
end;
输出结果为:60
2.删除存储过程
drop procedure 存储过程名;
3.创建函数
create [or replace] function function_name(argument1 [model1] datatype1,
argument2 [model2] datatype2...)
is [as]
PL/SQL BLOCK;
argument1,argument2为指定过程的参数,当指定参数数据类型时,不能指定其长度
modl是参数模式,包括输入参数(in),输出参数(out),输入输出参数(in out),默认为in.
is或者as用于开始执行一个PL/SQL块;
(1)例如:
create or replace function get_user
return varchar2
is
v_user varchar2(100);
begin
select ename into v_user from scott.emp where empno='7369';
return v_user;
end;
使用变量接收函数返回值
declare
username varchar2(50);
begin
username:=get_user;
dbms_output.put_line(username);
end;
结果为:SMITH
在SQL语句中直接调用函数
select get_user from dual;
结果为:SMITH
4.注意:函数必须要返回数据,所以只能作为表达式的一部分调用,可以在SQL中调用函数,
带out和in out参数的函数不能在SQL中被调用,且必须只能使用SQL所支持的标准数据类.
在包中如dbms_output调用函数:
dbms_output.put_line('用户名:'||函数名);
例如:
declare
username varchar2(50);
begin
username:=get_user;
dbms_output.put_line('用户名:'||get_user);
end;
结果为:用户名:SMITH
