第四章 oracle高级查询,事物,过程及函数
(一).当行函数:输入一位,输出一位 当行函数包含(日期函数,数值函数,字符函数,转换函数,其它函数)1.日期函数: add_months months_between last_day next_day2.数值函数(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; 输出结果为:602.删除存储过程 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; 结果为:SMITH4.注意:函数必须要返回数据,所以只能作为表达式的一部分调用,可以在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