oracle存儲過程詳解「oracle從入門到精通視頻教程」

本次試驗依然在windows 2016下進行。

一、以默認普通用戶scott/tiger連接數據庫

安裝Oracle時，若沒有為下列用戶重設密碼，則其默認密碼如下：

以普通用戶scott連接,默認密碼為tiger

格式：conn 用戶名/密碼（conn scott/tiger）

口令更改為cat

二、檢查Oracle的服務

點擊開始—運行，輸入services.msc ，打開windows的服務選項，

回車

找到Oracle數據庫服務是否啟動的兩個服務，一個是Oracle服務，一個是Oracle監聽服務。

其中ORCL為數據庫的名稱

下面是oracle的監聽程序，專門用來負責數據庫用戶遠程訪問數據庫。

三、最簡單的增刪改查

3.1查詢(select)

3.1.1查詢用戶SCOTT狀態

SQL> select username,account_status from dba_users where username=’SCOTT’;

EXPIRED表示數據庫用戶處於到期狀態，不能正常連接上數據庫。

&是並且的意思

LOCKED表示用戶處於鎖住狀態，不能使用該數據庫用戶。

解鎖數據庫用戶語法

alter user 數據庫用戶名 account unlock;

解鎖SCOTT用戶

SQL> alter user scott account unlock;

驗證SCOTT用戶的狀態

SQL> select username,account_status from dba_users where username=’SCOTT’;

上圖顯示SCOTT用戶已經解鎖，但是目前處於到期狀態。在EXPIRED狀態下，Oracle數據庫默認SCOTT用戶需要使用原來的密碼重新登錄，並設置新密碼，即可解除到期狀態。

SQL> conn scott/tiger

conn是connect的縮寫，scott為oracle數據庫的用戶，tiger為scott用戶的初始密碼。

圖中顯示：”the password has expired”,說明scott用戶密碼到期，輸入新口令cat

驗證查詢soctt用戶是open狀態

SQL> select username,account_status from dba_users where username=’SCOTT’;

用scott/cat的用戶名和密碼連接數據庫

C:UsersAdministrator>sqlplus scott/cat

驗證連接用戶

SQL> show user

3.1.2查詢SCOTT用戶擁有那些表

SQL> select table_name from user_tables;

上圖中可以看到有4個表，

DEPT(Department 部門)

EMP（Employee 僱員）

BONUS(獎金)

SALGRADE（Salary Grade薪水等級）

上圖中SQL語句解釋

select 是SQL語句查詢表數據必須的關鍵字，並且在查詢SQL語句的開頭，後面跟的是表中的列，如果查詢多個列值，可以使用逗號隔開。

table_name 是user_tables表的一個用來存儲表名的字段；而（*）則表示表中的所有字段。

from 是SQL語句查詢表中必需的關鍵字，表示這些列數據”來自”那個表中，後面必須是表名。

user_tables，這是指表名，即把該表的列數據全部查詢出來，它是Oracle數據庫內部的表（也是）數據字典，專門用來查詢用戶自己擁有那些表。

分號（；）這是英文字符的符號，是表示這條查詢SQL語句結束的符號。

3.1.3查看有哪些部門

SQL> select * from dept;

上圖知，虛線上面有三個英文單詞，這表示dept表有三列，

DEPTNO(部門號)

DNAME（部門名稱）

LOC（工作地點）

可見該公司有4個部門，部門分別為10，20，30，40

可以看到部門為10的部門名稱為ACCOUNTING（財務部），工作地點在NEW YORK(紐約)。

*表示所有字段

3.1.4查詢dept表有哪些列

SQL> desc dept

由上圖可知該表有三列

DEPTNO(部門號)

DNAME（部門名）

LOG（部門工作地點）。

3.2增加（insert into）

3.2.1增加一個Development(開發)部門

SQL> insert into dept(deptno,dname,loc) values (50,’Development’,’Beijing’);

Insert into 插入SQL語句，而且必須是在開頭，在其後跟隨的是表名。

把剛才插入的數據提交到數據庫中，如果沒有commit,則插入的SQL語句在計算機的內存中。

SQL> commit;

3.2.2查看dept部門表中是否存在剛插入的數據，已經在dept部門表中增加了Development開發部門。

SQL> select * from dept;

3.3修改（update）

把開發部門的辦公地點更改為上海。

SQL> update dept set loc=’Shanghai’ where deptno=50;

查詢結果

SQL> select * from dept;

update 是SQL更新語句的關鍵字，並且在語句的開頭，後面跟隨的是需要更新的dept表。

set 是SQL更新語句的關鍵字，該關鍵字跟隨在表名的後面，是”設置”的意思。在後面跟隨所要更新的列值。比如這裡loc為dept表的字段，把該值更改為”Shanghai”。

where 是SQL語句中的條件限制關鍵字，即操作的數據需要滿足的條件，這個關鍵字可以在增刪改查SQL語句中使用，一般用在SQL語句的後面來表示條件。條件可由列名、字符串、算數表達式等組成。

deptno=50 是SQL條件語句的一部分，表示在dept表中把部門號等於50的數據查詢出來。

3.4刪除(delete)

刪除Development開發部門

SQL> delete from dept where deptno=50;

delete from 是SQL語句中刪除數據必不可少的關鍵字，並且位於SQL語句的開始位置。後面跟隨的是要刪除的dept表，表名後面則是where條件限制語句。

四、綜合運用

4.1 查詢出公司每個員工的號碼、名字、薪水。

SQL> select empno ,ename,sal, from emp;

select關鍵字後可以選擇查詢任意列，列與列之間用逗號隔開。後面跟隨的empno、ename、sal均為emp表的列。

4.2調整查詢結果表的列次序，可以把最關心的重點列放到最前面。

SQL> select ename,sal,empno from emp;

4.3在oracle的sqlplus中，英文字符默認是左對齊，而數字則默認是右對齊。

查看員工的姓名和入職時間，其中hiredate的時間格式與我國習慣不符，畢竟Oracle數據不是中國人開發的。

SQL> select ename,hiredate from emp;

把時間設置為我們習慣的時間格式，僅僅是設置為當前會話場景。

SQL> alter session set nls_date_format=’YYYY-MM-DD’;

alter 更改數據庫參數的SQL語句關鍵字。

session 只更改當前會話的時間格式

nls_date_format 數據庫的日期參數，日期雖然一樣，但可以有不一樣的時間日期格式

4.4查詢當前數據庫時間

SQL> select sysdate from dual;

dual是oracle數據庫的一個虛表，即不是真實存在的表，在查詢用到計算、常量、表達式等時可以使用的dual虛表。

4.5查詢在公司服務時間的員工信息，使用當前時間減去員工的入職時間，就可以得到在公司服務時間，這裡需要引入round函數。

SQL> select ename,round((sysdate-hiredate)/365,0) from emp;

round(x,y) 是Oracle數據庫的一個四捨五入的函數

x 表示這個數字需要進行四捨五入

y 則表示在哪位數進行四捨五入，如果y=0,表示在個位進行四捨五入，y=2表示在小數點後兩位進行四捨五入。

4.6根據入職時間求出工作年限並且按照年限（第二列）升序排列

SQL> select ename,round((sysdate – hiredate)/365,0) from emp order by 2;

Order by 2中，order by 是排序的關鍵字，2表示按照第二列排序，默認排序是升序。

4.7按照工作年限倒序查詢出每個員工的年數

SQL> select ename,round((sysdate-hiredate)/365,0) from emp order by round((sysdate-hiredate)/365,0) desc;

desc 表示排序使用降序來輸出數據，即從大到小進行排序。

4.8 Oracle數據庫查詢入職時間。

SQL> select ename,round((sysdate-hiredate)/365,0) from emp order by hiredate;

把查詢出來的表列名變成中文，方便顯示。

SQL> select ename as “姓名” ,round((sysdate-hiredate)/365,0) as “工作（年）” from emp order by “工作（年）” desc;

as 是列別名的關鍵字，可以用英文雙引號””裡面的字符來表示該列的別名。如”姓名”是ename列的別名，查詢輸出數據時以”姓名”代替了ename列名。

工作年數的列名則變成了”工作（年）”，在Oracle數據庫中，同時允許用列的別名進行排序。

4.9查詢工資

SQL> select ename,sal from emp order by sal;

4.10 每個人增加1500元進行顯示

SQL> select ename,sal+1500 from emp order by sal;

4.11對查詢出來的員工薪水進行文字說明

SQL> select ename||’員工本月工資為：￥’|| (sal+1500) as “公司員工本月工資表” from emp order by sal;

|| 兩個豎杠是oracle的連接符，可以把查詢出來的數據和其他字符串連接起來，可以對多個字符串、多個表的列值相連接。

‘ ‘ 兩個單引號”裡面的字符串，可以把裡面的字符串輸出。即表的列數據和字符串連接在一起輸出結果。

4.12查詢到部門表中部門有重複的

SQL> select ename,deptno from emp;

為了清晰顯示有哪些部門，可以過濾掉重複的部門值

SQL> select distinct deptno from emp;

distinct是去掉重複數據的SQL關鍵字，這個去掉重複數據的關鍵字經常用到。

4.13查詢工資少於2000元的員工，並按照薪水排序。

SQL> select ename,sal from emp where sal<=2000 order by sal;

Order by需要在where的後面，常用運算符如下

“>”大於

“>=”大於等於

“<“小於

“<=”小於等於

“<>”和”！=”都是不等於

“=”等於

4.14查看薪水在1500到2500元之間的員工信息並且按照薪水排序

SQL> select ename,sal from emp where sal between 1500 and 2500 order by sal;

between…and…用於條件where之中，表示數值介於兩個數值之間，這裡表示是工資在1500-2500之間。

4.15查詢拿保底工資的銷售人員

SQL> select empno,ename,job,sal from emp where job=’SALESMAN’ and sal=1250;

where條件中的and表示”而且”的意思，即條件既要是銷售人員，而且工資也是1250元的員工。

4.16查看沒有獎金及工資少於1500元的員工

SQL> select empno,ename,job,sal,comm from emp where comm is null or sal<=1500;

or 是或者的意思，這裡表示沒有獎金或者工資少於等於1500元的員工；

null 在oracle數據庫中是一個很特殊的值，它即不表示0，也不表示空，是一個不能確定的未知數。

4.17員工的工資加上提成，那個員工的工資是最少的

SQL> select empno,ename,job,sal,comm,sal+nvl(comm,0) from emp where comm is null or sal<=1500 order by sal+nvl(comm,0);

Nvl(X,Y)是數據庫的一個內部函數，表示如果X有值，則返回X的值，如果X的值為null,則默認為Y。

這裡則是用comm（提成）替代X，”0″代表Y，如果有提成，則工資加上提成；如果沒有提成，則表示提成是”0″，只有工資。把員工的薪水加上提成作為排序，可以明顯看出來員工的收入多少。

4.20查看姓名中”M”開頭的員工

SQL> select ename,job,sal from emp where ename like ‘M%’;

Like 是條件where中模糊查詢的關鍵字，後面的字符串需用雙單引號括起來，%在SQL語句中表示字符後面的所有字符，其中M%表示以M開頭的所有字符。

4.21查詢哪些員工屬於銷售人員、分析師、管理人員。

SQL> select ename,job from emp where job in (‘SALESMAN’,’ANALYST’,’MANAGER’);

in 表示在某個列中存在多個值均符合，或者使用or代替也可。

下面用or來代替上面的SQL語句，同時看看有哪些不一樣。在oracle數據庫的命令窗口輸入下面的SQL語句：

SQL> select ename,job from emp where job=’SALESMAN’ or job=’ANALYST’ or job=’MANAGER’;

4.22統計公司每個崗位都有多少個員工

SQL> select job,count(*) from emp group by job;

group by 是oracle數據庫中的分組函數，可以這樣理解，按照工作職位進行分組，然後統計每個職位的人數。

Count(*)是統計數量的函數，這裡統計公司每個崗位都有多少人。

4.23統計公司每個崗位都有多少個員工，並按數量進行排序。

SQL> select job,count(*) from emp group by job order by count(*);

4.24統計公司每個部門有哪些員工，並按照部門號進行排序。

SQL> select deptno,count(*) from emp group by deptno order by deptno;

從圖中可以看到，10號部門有3人，20號部門有5人，而30號部門有6人。

4.25求公司總共支付員工的薪水

SQL> select sum(sal)+sum(nvl(comm,0)) from emp;

sum 是Oracle數據庫內部的一個函數，即所有數值之和。

4.26求公司員工的平均工資

SQL> select round(avg(sal),2) from emp;

avg是oracle數據庫的求平均值函數。

4.27統計公司所有員工中最高、最低、工資及相差多少

SQL> select max(sal),min(sal),max(sal)-min(sal) from emp;

max 是Oracle數據庫內部的函數，專門統計最大的數值

min 是Oracle數據庫的內部函數，專門統計最小的數值

4.28列出平均工資大於2500的崗位

SQL> select job,avg(sal) from emp having avg(sal)>2500 group by job;

having 在使用group by分組時，如有條件限制需要使用having，而不能使用where.即在group by中所對應的條件限制為having,上面的例子中數據庫首先使用group by進行崗位分組，再使用avg(sal)求出每個崗位的平均工資，最後平均工資大於2500元由having進行限制。