Oracle之函数学习以及事务

Oracle之函数学习

1、字符函数是oracle中最常用的函数

lower(char)：将字符串转化为小写的格式

upper(char)：将字符串转化为大写的格式

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

substr(char,m,n)：取字符串的子串

将所有的员工的名字按小写的方式显示？

SQL> select lower(ename),sal from emp;

LOWER(ENAM        SAL

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

smith             800

allen            1600

按照大写的方式显示？

SQL> select upper(ename),sal from emp;

UPPER(ENAM        SAL

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

SMITH             800

显示正好有5个字符的员工？

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

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

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

    DEPTNO

----------

      7369 SMITH      CLERK           7902 17-DEC-80        800

        20

显示所有员工姓名的前三个字符？

SQL> select substr(ename,1,3) from emp;   从第一个字符开始，向后偏移三个字符

SUBSTR(ENAME

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

SMI

ALL

SQL> select substr(ename,2,3) from emp;   从第二个字符开始，向后偏移三个字符

SUBSTR(ENAME

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

MIT

LLE

取出首字母为大写的行？

SQL> select upper(substr(ename,1,1)) from emp; 第一步取出首字母，然后将首字母进行替换成大写

UPPE

----

S

A

将字符串首字母除外的字符都变成小写？

第一步使用substr函数先将第二个字符到最后一个字符摘出来，由length函数计算出长度

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

LOWER(SUBSTR(ENAME,2,LENGTH(ENAME)-1

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

mith

llen

将首字符为大写其他字符为小写的条件查询出来

将两者结合起来就行

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

UPPER(SUBSTR(ENAME,1,1))||LOWER(SUBSTR(E

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

Smith

Allen

Ward            ||：Oracle使用双竖线表示字符串连接函数

replace(char1,search_string,replace_string)

显示所有员工的姓名，用“B”替换“A”？

SQL> select replace(ename,'A','B') from emp前者为旧字符，后者为新字符（也就是替换后的字符）

REPLACE(EN

----------

SMITH

BLLEN

2、数学函数

数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos、cosh、exp、in、

log、sinh、sqrt、tan、tanh、acos、asin、atan、round

round(n,[m])

该函数用于执行四舍五入，如果省掉m则四舍五入到整数，m为正数，则四舍五入到小数点的m位，m为负数，则四舍五入到小数点的m位前

SQL> select round(sal),sal from student where xm='xiaocai';

ROUND(SAL)       SAL

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

      1234   1234.34      四舍五入

SQL> select round(sal,1),sal from student where xm='xiaocai';

ROUND(SAL,1)       SAL

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

      1234.3   1234.34 

trunc(n,[m])

该函数用于截取数字，如果省掉m，就截取整数部分，如果m是正数就截取到小数点的m位后。如果m是负数，则截取到小数点的前m位

SQL> select trunc(sal),sal from student where xm='xiaocai';

TRUNC(SAL)       SAL

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

      1234   1234.34

SQL> select trunc(sal,1),sal from student where xm='xiaocai';

TRUNC(SAL,1)       SAL

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

      1234.3   1234.34

mod(m,n)

取模，取得就是余数

SQL> select mod(10,2) from dual;        在做oracle测试时，可以使用dual表（虚拟表）

 MOD(10,2)

----------

         0

SQL> select mod(1,3) from dual;         余数为1

  MOD(1,3)

----------

         1

floor(n)

返回小于或者是等于n的最大整数

SQL> select floor(sal),sal from student where xm='xiaocai';

FLOOR(SAL)       SAL

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

      1234   1234.34      小于或者等于1234.34的最大整数为1234，也就是比原值稍小的整数（或者相等）

ceil(n)

返回大于或者是等于n的最小整数

SQL> select ceil(sal),sal from student where xm='xiaocai';

 CEIL(SAL)       SAL

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

      1235   1234.34          比原值稍大的整数（或者相等）

3、日期函数

日期函数用于处理date类型的数据

默认情况下日期格式是dd-7月-yy

a、sysdate：该函数返回当前系统时间

SQL> select sysdate from dual;

SYSDATE

---------

26-SEP-16

b、add_months(d,n)：往d上加n个月时间

查询出入职时间到现在已经过了8个月的员工？

SQL> select * from emp where sysdate>add_months(hiredate,8);        入职时间是hiredate，往此基础上增加8个月，此时的系统时间比这个大就是表面已经入职8个月了

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

显示出员工入职时间到现在有多少天？

SQL> select sysdate-hiredate workday,ename from emp;

   WORKDAY ENAME

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

13057.8384 SMITH

12992.8384 ALLEN

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

   WORKDAY ENAME

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

     13057 SMITH

     12992 ALLEN

c、last_day(d)：返回指定日期所在月份的最后一天

找出个月倒数第三天受雇佣的所有员工和时间？

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

HIREDATE    LAST_DAY(HIREDATE)

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

1980/12/17  1980/12/31     入职时间是1980/12/17，当年的那一月的最后一天是1980/12/31

1981/2/20   1981/2/28

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

HIREDATE  ENAME

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

28-SEP-81 MARTIN

4、转换函数

转换函数用于将数据类型从一种转为另外一种，在某些情况下，oracle server允许值的数据和实际的不一样，这时

oracle server会隐含的转化为数据类型

create table t1(id int);

insert into t1 values ('10'):这样oracle会自动的将'10'转化为10

create table t2(id vachar2(10));

inset into t2 values (1);这样oracle就会自动将1转化为'1'

尽管oracle可以进行隐含的数据类型转化，但是它并不适应所有的情况。为了提高程序的可靠性

应该使用转换函数进行转换.

函数to_char

使用函数to_char来替换，将表的某一个字段的进行替换

SQL> select to_char(hiredate,'yyyy-mm-dd') from emp;

TO_CHAR(HI

----------

1980-12-17

SQL> select to_char(sysdate,'day') from dual;     以day字符串的格式替换一下查询结果

TO_CHAR(SYSDATE,'DAY')

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

thursday

日期可以显示时/分/秒

yy：两位数字的年份

yyyy：四位数的年份

mm：2位数的月数

dd：2位数的天数

hh34:24小时的小时数，比如晚上八点就是：20点

hh22:8点就是08点

mi、ss显示分钟、秒

示例：

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

ENAME      TO_CHAR(HIREDATE,'YYYY-MM-DDHH

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

SMITH      1980-12-17 00:00:00

ALLEN      1981-02-20 00:00:00

WARD       1981-02-22 00:00:00

JONES      1981-04-02 00:00:00

MARTIN     1981-09-28 00:00:00

SQL> select to_char(sysdate,'yyyy-mm-dd hh34:mi:ss') from emp;

TO_CHAR(SYSDATE,'YY

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

2016-09-26 10:39:51

2016-09-26 10:39:51

薪水yoga货币单位显示：

9：显示数字，并忽略前面0，表示薪水这个字段的类型一共有多少有效数和支持的小数位数

2：显示数字，如位数不足，用0补充

.：在指定位置显示小数点

，：在指定位置显示逗号

$：在数字前加美元

L：在数字前加本地货币（oracle安装时会判断该用户使用的字符集来判断是哪个国家）

C:在数字前加国际货币单位符号

G：在指定位置显示组分符号

D：在指定位置显示小数点符号

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

ENAME      TO_CHAR(SAL,'L99999.99')

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

SMITH                ￥800.00

ALLEN               ￥1600.00

WARD                ￥1250.00

JONES               ￥2975.00

MARTIN              ￥1250.00

SQL> desc emp;

Name     Type         Nullable Default Comments 

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

EMPNO    NUMBER(4)                              

ENAME    VARCHAR2(10) Y                         

JOB      VARCHAR2(9)  Y                         

MGR      NUMBER(4)    Y                         

HIREDATE DATE         Y                         

SAL      NUMBER(7,2)  Y         由于这个表emp的字段sal是number(7,2)，所以上面用的是一共七位有效数，两位小数                      

COMM     NUMBER(7,2)  Y                         

DEPTNO   NUMBER(2)    Y     

to_char()用来取出hiredate字段的年份

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

ENAME      TO_CHAR(HIREDATE,'YEAR')

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

SMITH      nineteen eighty

ALLEN      nineteen eighty-one

WARD       nineteen eighty-one

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

ENAME      TO_CHAR(HIREDATE,'YYYY')

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

SMITH      1980

ALLEN      1981

WARD       1981

SQL> select to_char(sysdate,'day') from dual;

TO_CHAR(SYSDATE,'DAY')

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

星期五

SQL> select to_char(sysdate,'dd') from dual;

TO_CHAR(SYSDATE,'DD')

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

23         只显示的就是这一天的日子

查询入职时间是1980年的员工？

SQL> select * from emp where to_char(hiredate,'yyyy')=1980;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

查询入职月份是12月的员工？

SQL> select * from emp where to_char(hiredate,'mm')=12;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

 7900 JAMES      CLERK      7698 1981/12/3      950.00               30

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

函数sys_context：系统函数，下面是该函数的一些替换值

1、terminal：当前会话客户所对应的中断的标识符

2、language:语言

3、db_name：当前数据库名称

4、nls_date_format：当前会话客户所对应的日期格式

5、session_user：当前会话客户所对应的数据库用户名

6、current_schema:当前会话客户所对应的方案名

7、host：返回数据库所在主机的名称

通过该函数，可以查询到一些重要信息，比如你使用的是哪个数据库？

SQL> select sys_context('userenv','db_name') from dual;

SYS_CONTEXT('USERENV','DB_NAME')

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

orcl11g

查看当前数据库支持的语言

SQL> select sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAG

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

SIMPLIFIED CHINESE_CHINA.AL32UTF8

查看nls_date_format

SQL> select sys_context('userenv','nls_date_format') from dual;

SYS_CONTEXT('USERENV','NLS_DAT

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

DD-MON-RR

SQL> select sys_context('userenv','terminal') from dual;                                       

SYS_CONTEXT('USERENV','TERMINAL')

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

pts/0

查看session_user，相当于show user；查看当前用户

SQL> select sys_context('userenv','session_user') from dual;

SYS_CONTEXT('USERENV','SESSION

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

SCOTT

查看current_schema:当前会话客户所对应的方案名    一个用户对应一个方案，一个方案有很多数据对象

SQL> select sys_context('userenv','current_schema') from dual;

SYS_CONTEXT('USERENV','CURRENT

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

SCOTT

SQL> select sys_context('userenv','current_schema') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

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

SCOTT

查看当前数据库的host主机

SQL> select sys_context('userenv','host') from dual;

SYS_CONTEXT('USERENV','HOST')

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

aliyun_test

schema：

oracle以方案的名称来管理数据对象

当数据库创建了一个用户时，oracle就会给用户分配一个schema方案

方案里面存放着什么呢？

很多的数据对象

表、视图、触发器、存储过程等等（以方案的方式管理数据对象）

http://blog.csdn.net/kimsoft/article/details/4627520

A schema is a collection of database objects (used by a user.). 

Schema objects are the logical structures that directly refer to the database’s data.

A user is a name defined in the database that can connect to and access objects.

Schemas and users help database administrators manage database security.

从定义中我们可以看出schema为数据库对象的集合，为了区分各个集合，我们需要给这个集合起个名字

这些名字就是我们在企业管理器的方案下看到的许多类似用户名的节点，这些类似用户名的节点其实就是一个schema

schema里面包含了各种对象如tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links。

一个用户一般对应一个schema,该用户的schema名等于用户名，并作为该用户缺省schema。

这也就是我们在企业管理器的方案下看到schema名都为数据库用户名的原因。Oracle数据库中不能新创建一个schema

要想创建一个schema，只能通过创建一个用户的方法解决(Oracle中虽然有create schema语句，但是它并不是用来创建一个schema的)

在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema。即schema的个数同user的个数相同

而且schema名字同user名字一一 对应并且相同，所有我们可以称schema为user的别名，虽然这样说并不准确，但是更容易理解一些。

一个用户有一个缺省的schema，其schema名就等于用户名，当然一个用户还可以使用其他的schema。

如果我们访问一个表时，没有指明该表属于哪一个schema中的，系统就会自动给我们在表上加上缺省的sheman名。

比如我们在访问数据库时，访问scott用户下的emp表，通过select * from emp; 其实，这sql语句的完整写法为select * from scott.emp。

在数据库中一个对象的完整名称为schema.object，而不属user.object。类似如果我们在创建对象时不指定该对象的schema

在该对象的schema为用户的缺省schema。这就像一个用户有一个缺省的表空间，但是该用户还可以使用其他的表空间

如果我们在创建对象时不指定表空间，则对象存储在缺省表空间中，要想让对象存储在其他表空间中，我们需要在创建对象时指定该对象的表空间。

补充：

SQL> select trunc(sysdate+7)+(1/24) from dual;    对于当前日期和时间，在日期上加7天，时间中的小时加1，其它分、秒都不变，1/24：一天除以24小时，等于1小时

SQL> select trunc((sysdate+7)+(1/24)) from dual;  上述sql和这类似，结果都一样

SQL> select trunc((sysdate+7)+(1/24)) from dual;

TRUNC((SY

---------

03-OCT-16

=>:

创建函数中参数的赋值:=>（=>这个符号的含义）

oracle实参与形参有二种对应方式

1.一种是位置方式，和面向对象语言参数传递类似;

2.另外一种是=> 作为形参对应，因为位置对应方法有缺限，比如一个函数有四个参数，但第三个是可以不传（有默认值)

这里就没办法位置对应方法，oralce内部一般用此种方法作参数传递（一般是， 某些参数有默认值的时候，你需要跳过某些参数来进行调用）

在Oracle中各符号含义

　　%（百分号）： 用来表示任意数量的字符，或者可能根本没有字符。

　　_（下划线）： 表示确切的未知字符。

　　？（问号）： 用来表示确切的未知字符。

　　#（井号）： 用来表示确切的阿拉伯数字，0到9.

　　[a-d]（方括号）：用来表示字符范围，在这里是从a到d.

　　单引号（'）：在Oracle中，应该只使用单引号将文本和字符和日期括起来，不能使用引号（包括单双引号）将数字括起来。

　　双引号（"）：在Oracle中，单双引号含义不同。双引号被用来将包含特定字符或者空格的列别名括起来。双引号还被用来将文本放入日期格式。

　　撇号（'）：在Oracle中，撇号也可以写成彼此相邻的两个单引号。为了在供应商名字中间查找所有带撇号的供应商名字，可以这样编写代码：select * from l_suppliers where supplier_name like '%''%'

　　&符号：在Oracle中，&符号常用来指出一个变量。例如，&fox是一个变量，稍微有点不同的一种&& fox.每当&fox出现在Oracle脚本中时，都会要求您为它提供一个值。而使用&&fox，您只需要在& &fox第一次出现时为它提供变量值。如果想将&符号作为普通的符号使用，则应该关闭这个特性。要想关闭这个特性，可以运行以下的命令： set define off ，这是一个SQLplus命令，不是一个SQL命令。SQLplus设置了SQL在Oracle中运行的环境。

　　双竖线（||）：Oracle使用双竖线表示字符串连接函数。

　　星号（*）：select *意味着选择所有的列，count（*）意味着计算所有的行，表示通配符时，表示0个或任意多个字符。

　　正斜杠（/）：在Oracle中，用来终止SQL语句。更准确的说，是表示了“运行现在位于缓冲区的SQL代码”。正斜杠也用作分隔项。

　　多行注释：/*……*/.

　　不等于：有多种表达方式：！=、^=、<>、not xxx=yyy、not（xxx=yyy）

insert插入数据  

当使用values子句时，一次插入的数据有限，当使用子查询插入数据时，一条insert语句可以插入大量的数据

当处理行迁移或者装载外部表的数据到数据库时，可以使用子查询来插入数据

1、先创建一个单表

SQL> create table mytable(myid number(4),myname varchar2(20),mydept number(3));

Table created

SQL> desc mytable;

Name   Type         Nullable Default Comments 

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

MYID   NUMBER(4)    Y                         

MYNAME VARCHAR2(20) Y                         

MYDEPT NUMBER(3)    Y

2、利用子查询将查询到的某一行数据插入到新表中

SQL> insert into mytable select empno,ename,deptno from emp where deptno=20;

5 rows inserted

3、查看插入的数据

SQL> select * from mytable;

 MYID MYNAME               MYDEPT

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

 7369 SMITH                    20

 7566 JONES                    20

 7788 SCOTT                    20

 7876 ADAMS                    20

 7902 FORD                     20

利用子查询迁移行，迁移库数据，效率提升

用update结合子查询更新

希望scott员工的岗位、工资、补助和smith员工一样多？

SQL> update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';

1 row updated

SQL> select job,sal,comm from emp where ename='SMITH';

JOB             SAL      COMM

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

CLERK        800.00 

SQL> select job,sal,comm from emp where ename='SCOTT';

JOB             SAL      COMM

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

CLERK        800.00 

Oracle事务处理

事务用于保证数据的一致性，它由一组相关的dml（增删改）语句组成，该组的dml语句要么全部成功，要么全部失败

如：网上转账就是典型的要用事务来处理，用来保证数据的一致性

事务和锁

当执行事务操作时，也就是dml语句操作时，oracle会在被作用的表上加锁，防止其他用户在此表上

修改此表的表的结构，这里对用户来说就是非常重要的

提交事务

当执行使用commit语句可以提交事务，当执行了commit语句之后，会确认事务的变化，结束事务，删除保存点，释放锁

当使用commit语句结束事务之后，其他会话将可以查看到事务变化后的数据,当退出plus命令行时，会默认提交事务

回退事务

回退事务需要用到保存点，能够将误操作的数据进行回滚

savepoint a;

rollback to a;

a1:8点

a2:12:00 

a3:17:00

可以从a3回滚到a2，然后从a2继续回滚到a1

但是使用commit一旦提交，保存点都将失去

SQL> savepoint a1;   创建保存点

Savepoint created

SQL> delete from mytable where myname='SMITH'; 模拟删除数据

1 row deleted

SQL> commit;   提交事物

Commit complete

SQL> rollback to a1;    进行保存点回滚，但是失败，说明提交事物后，保存点将失效

rollback to a1

ORA-01086: 从未在此会话中创建保存点 'A1' 或者该保存点无效

只读事务

只读事务是指只允许执行查询的操作，而不允许执行任何其他dml操作的事务，使用只读事务可以确保用户只能取得

某时间点的数据。假定机票代售点每天18点开始统计今天的销售状况，这是可以使用只读事务，在设置只读事务

之后，尽管其他会话可能会提交新的事务，但是只读事务将不会取得最新数据的变化，从而可以保证取得

特定时间点的数据信息