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;

文章插圖

【oracle存儲過程詳解 oracle從入門到精通視頻教程】having 在使用group by分組時，如有條件限制需要使用having，而不能使用where.即在group by中所對應的條件限制為having,上面的例子中數據庫首先使用group by進行崗位分組，再使用avg(sal)求出每個崗位的平均工資，最后平均工資大于2500元由having進行限制。