Oracle事务及函数

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 

Connected as scott

SQL> UPDATE EMP SET (JOB,SAL,COMM)=(SELECT JOB,SAL,COMM FROM EMP WHERE ENAME='SMITH')

  2  WHERE ENAME='SCOTT';

 

SQL> SELECT * FROM EMP;

SQL> insert into emp values(8888,'OK','MANAGER',7788,to_date('1988-12-12','yyyy-mm-dd'),34.34

  2  ,240.00,20);

SQL> SELECT * FROM EMP;

 

SQL> COMMIT;

 

SQL> --设置只读事务

 

SQL> set transaction read only;

 

SQL> select * from emp;

 

SQL> delete emp where ename='OK';

SQL> commit;

 

SQL> select * from scott.emp;

 

SQL> insert into emp values(8888,'OKK','CLERK',9999,to_date('1988-11-11','yyyy-mm-dd'),

  2  1333,300.00,20);

 

SQL> --把字段改为小写显示

 

SQL> select lower(ename) from emp;

 

SQL> --将ename字段改为大写显示

SQL> select upper(ename) from emp;

 

SQL> --显示正好为5个字符的员工的姓名

SQL> select * from emp where length(ename)=5;

 

SQL> --显示所有员工姓名的前3个字符

SQL> select substr(ename,1,3) from emp;

SQL> 

SQL> --以首字母大写显示所有员工的姓名

SQL> select (upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1))) from emp;

SQL> 

SQL> --替换

SQL> select replace(ename,'A','我是A') from emp;

SQL> select replace(ename,'我是A','A') from emp;

SQL> 

SQL> --round()函数四舍五入

SQL> select round(sal) from emp where ename='SMITH';

SQL> SELECT ROUND(COMM,1),COMM FROM EMP WHERE ENAME='MARTIN';

 

SQL> SELECT TRUNC(COMM,-1),COMM FROM EMP WHERE ENAME='MARTIN';

 

SQL> SELECT FLOOR(COMM),COMM FROM EMP WHERE ENAME='MARTIN';--向下取整

SQL> SELECT CEIL(COMM),COMM FROM EMP WHERE ENAME='MARTIN';--向上取整

SQL> select mod(10,3) from dual;--在做Oracle测试时可以用dual这个表

SQL> --显示在一个月为30天的情况所有员工的日薪金，忽略余数

SQL> select trunc(sal/30),ename from emp;

SQL> --返回数字n的绝对值

SQL> select abs(-13) from dual;

SQL> 

SQL> select acos(0.5) from dual;

SQL> select asin(0.5) from dual;

SQL> 

SQL> select sin(60) from dual;

 

   SIN(60)

----------

-0.3048106

 

SQL> select sin(30) from dual;

 

   SIN(30)

----------

-0.9880316

 

SQL> select sin(-30) from dual;

 

  SIN(-30)

----------

0.98803162

 

SQL> select cos(30) from dual;

 

   COS(30)

----------

0.15425144

 

SQL> select exp(3) from dual;--返回e的3次幂

 

    EXP(3)

----------

20.0855369

 

SQL> select log(100,10) from dual;

 

LOG(100,10)

-----------

        0.5

 

SQL> select log(10,100) from dual;

 

LOG(10,100)

-----------

          2

 

SQL> select power(2,3) from dual;

 

POWER(2,3)

----------

         8

 

SQL> --日期函数

SQL> select sysdate from dual;

 

SYSDATE

-----------

2011-3-19 1

 

SQL> --查找8个月以前入职的员工

SQL> select * from emp where sysdate>add_months(hiredate,8);

SQL> 

SQL> select * from emp where sysdate>add_months(hiredate,300);

SQL> 

SQL> --显示满了10年的员工雇佣日期

SQL> select * from emp where sysdate>=add_months(hiredate,12*10);

SQL> 

SQL> --显示每个员工的雇佣的天数

SQL> select sysdate-hiredate "入职天数",ename from emp;

SQL> select trunc(sysdate-hiredate) "入职天数",ename from emp;

SQL> select floor(sysdate-hiredate) "入职天数",ename from emp;

SQL> 

SQL> --找出各月倒数第3天雇的所有员工

SQL> select hiredate,last_day(hiredate) from emp;

SQL> 

SQL> select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

 

HIREDATE    ENAME

----------- ----------

1981-9-28   MARTIN

 

SQL> --转换函数

SQL> select * from emp;

SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

SQL> insert into emp values(8808,'test用户','MANAGER',7788,sysdate,23,23,10);

SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

SQL> 

SQL> --显示货币符号

SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),sal from emp;

SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99999.99') from emp;

SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'$99999.99') from emp;

SQL> 

SQL> --显示1980年入职的员工

SQL> select ename,to_char(hiredate,'yyyy') from emp;

SQL> select ename,to_char(hiredate,'yyyy')from emp;

SQL> select ename,to_char(hiredate,'yyyy'),to_char(sal,'L99999.99') from emp;

SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=1980;

SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'MM')=12;

SQL> SELECT SYS_CONTEXT('USERENV','DB_NAME') FROM DUAL;

SQL> SELECT SYS_CONTEXT('USERENV','DB_NAME') FROM DUAL;

 

SYS_CONTEXT('USERENV','DB_NAME

--------------------------------------------------------------------------------

orcl

 

SQL> SELECT SYS_CONTEXT('USERENV','LANGUAGE') FROM DUAL;

 

SYS_CONTEXT('USERENV','LANGUAG

--------------------------------------------------------------------------------

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

 

SQL> SELECT SYS_CONTEXT('USERENV','SESSION_USER') FROM DUAL;

 

SYS_CONTEXT('USERENV','SESSION

--------------------------------------------------------------------------------

SCOTT

 

SQL> SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;

 

SYS_CONTEXT('USERENV','CURRENT

--------------------------------------------------------------------------------

SCOTT

 

SQL> SELECT SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') FROM DUAL;

 

SYS_CONTEXT('USERENV','NLS_DAT

--------------------------------------------------------------------------------

DD-MON-RR

 

SQL>