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點開始統計今天的銷售狀況，這是可以使用只讀事務，在設置只讀事務

之后，盡管其他會話可能會提交新的事務，但是只讀事務將不會取得最新數據的變化，從而可以保證取得

特定時間點的數據信息