oracle 一些常用的函数

select *  from emp where empno=7839;


select  sal  from emp where empno=7839;

字符函数

select  length(sal) from emp;

select lower(ename) from emp;

select upper(ename) from emp;

select  substr(sal,1,3)  from emp;

select replace(sal,'a','dddddddddddd')  from emp;

select  round(sal,1) from emp where empno=7839;

select comm from emp where empno=7839;


select trunc(comm) from emp where empno=7839;


select trunc(comm,1) from emp where empno=7839;


select ceil(comm) from emp wher+e empno=7839; ---向上取整


select floor(comm) from emp where empno=7839; --向下取整


select mod(10,3) from dual;


select floor(sal)  from emp;


select  sysdate from dual;


select  * from emp  where sysdate> add_months(hiredate,8*12);  --- 工龄超过8年的 


select trunc(sysdate-hiredate),ename from emp;


select hiredate,last_day(hiredate) from emp;


select hiredate ,ename from emp where last_day(hiredate)-2=hiredate;  ---某个月的倒数第三天




select to_char(hiredate,'yyyy-MM-dd hh:mi:ss') from emp;


select to_char(hiredate,'yyyy-MM-dd hh24:mi:ss') from emp;




insert into emp values(8881,'testUser','manager',7782,sysdate,100,1000,10);


insert into emp values(8882,'testUser','manager',7782,to_date('2012-12-20 23:12:12','yyyy-MM-dd hh24:mi:ss'),100,1000,10);


commit;


select *  from emp where empno=8882;


select to_char(sal,'L9,999.99')  from emp;


select to_char(sal,'$9,999.99')  from emp;  


 
select ename,hiredate from emp  where  to_char(hiredate,'MM')=12;  --查询12月入职的员工




select ename,hiredate from emp  where  to_char(hiredate,'yyyy')=1980; --查询1980 入职的员工


系统函数

select  sys_context('USERENV','db_name')  from dual;


select  sys_context('USERENV','current_schema')  from dual;


select  sys_context('USERENV','language')  from dual;


select  sys_context('USERENV','session_user') from dual;

--只读存储过程是用来获得，某个时间的数据
set transaction  read only;

用户和方案的关系
     一个用户对应一个方案，用户以方案的方式管理 数据库对象 
   一个方案包括 表 视图 触发器  存储过程