Oracle 高级查询、事物、存储过程及函数

一、Sql函数

　　　　　　1、数值函数（输入参数和返回值都是数值型，多数函数精确到38位）

--多少次方
select power(3,2) from dual;
--开方
select sqrt(3) from dual;
--绝对值
select ABS(-12) from dual;
--向上取值
select ceil(5.3)from dual;
--向下取值
select floor (5.3)from dual;
--四舍五入
select round (1.235,2) from dual;
--截取
select trunc (152.3365) from dual;
--判断正负
select sign (-12) from dual;

　　　　　　2、字符函数（输入参数是字符型，返回值是数值型和字符型）

　　　　　　　　　　lower(char)  将字符串转换为小写格式

　　　　　　　　　　upper(char)  将字符串转换为大写格式

　　　　　　　　　　length(char)返回字符串的长度

　　　　　　　　　　ltrim(char [,set]) 去掉set左端的字符串

--截取字符
select substr('hehe',3,2) from dual;
--合并
select concat('h','e') from dual;
--查找位置
select instr('he','h') from dual;
--替换
select replace('he','e','h') from dual;
--去空格
select ltrim('this','th') from dual

　　　　　　3、转换函数（将数值从一种数据类型装换为另一种数据类型）

--to_number()转换为数字
select to_number('2000.02','999999D99') from dual;
--to_char()将日期型转变为字符串
select to_char(sysdate,'yyyy-mm-dd') from dual;
--to_date()转换为date类型
select to_date('2013-04-05','yyyy-mm-dd') from dual;　　　

　　　　nvl(expr1,expr2) 将null转换为实际值

　　　　nvl2(expr1,expr2,expr3) 如果expr1不为null 这返回expr2,否则返回expr3

二、查询

　　　　1、多表查询

　　　　　　union :返回不重复行（补）

　　　　　　union all:返回所有行，包括重复行（并）

　　　　　　intersect :两个查询都检索到的行（交）

　　　　　　minus:返回第一个查询检索到的行减去第二个查询检索到的行所剩余的行（差）

　　　2、连接查询

--内连接
select dept.deptno,dname,enamefrom scott.dept,scott.emp where dept.deptno=emp.deptno;
--自连接
select manager.ename from scott.emp manager,scott.emp worker where manager.empno=work.mgr and worker.ename='SMITH';

三、事物处理

　　　　　　commit:提交事务

update scptt.emp set sal=2000 where ename='MARY';
commit;
select sal from scott.emp where ename='MARY';

　　　　　　rollback:回滚事务

update scptt.emp set sal=3000 where ename='MARY';
rollback;
select sal from scott.emp where ename='MARY';

　　　　　　savepoint a:设置保存点 整个事务部回滚

　　　　　　rollack to a :取消部分事务

　　　　　　rollack :取消全部事务

　　　　1、事物的属性

　　　　　　　　原子性（A）、一致性（C）、隔离性（I）、持久性（D）

四、过程和函数

　　　　　　1、存储过程（procedure）

create or replace procedure proc_name(v_empno number)
as
--内部变量
v_name varchar2(30);
begin
       select scott.emp.ename into v_name from scott.emp where empno=v_empno;
       dbms_output.put_line('员工信息'|| v_name);
end;
--调用存储过程
begin
  proc_name(7369);
end;

--删除存储过程
drop procedure proc_name

　　　　　　2、函数

--函数
create or replace function func_name
return varchar2
as
begin
    return 'xxx';  
end;
--调用函数
select func_name() from dual;
--删除函数
 drop function func_name