Oracle資料庫—— PL/SQL基礎編程

標籤：

一、涉及內容

　　1. 掌握PL/SQL程式塊的結構，理解並熟悉各種變數的應用。

二、具體操作

（一）使用system使用者登入SQL*PLUS，使用SQL語句建立使用者：u_你的姓名首字母（例如：u_zs），密碼為： t_你的學號後三位（例如：t_165）。並授予新使用者串連資料庫的許可權，然後以新使用者串連資料庫。

1. 建立使用者u_sxl

 

2. 授予新使用者u_sxl串連資料庫的許可權

 

3. 以新使用者u_sxl串連資料庫

 

（二）使用scott使用者串連資料庫，使用SQL語句建立表dep_你的學號後三位（例如：dep_165）

注意：建立表的同時要建立約束

列名    資料類型    列的說明

dno integer 系編號(主鍵約束)

dname   char(12)    系名(唯一約束)

1. 使用scott使用者串連資料庫(先修改一下密碼)

 

2. 建立表dep_120

 

（三）使用SQL語句建立表stu_你的學號後三位（例如：stu_165）

注意：建立表的同時要建立約束

列名    資料類型    列的說明

sno integer 學號(主鍵約束)

sname   Varchar2(20)    姓名(非空約束)

sage    integer 年齡(check約束：在12~99歲之間)

sdep    integer 系編號(外鍵約束)

建立的表格如下所示：

 

（四） 使用scott方案下的emp表和dept表完成下列查詢。（注意：每道題只能使用一條SQL語句實現）

1. 查詢20號部門的所有員工的姓名、部門名稱（要求：使用NATURAL JOIN）

 

2. 查詢部門地點（LOC）、員工姓名。如果某部門沒有員工，也要顯示部門地點。（要求：使用左外串連）

 

3. 查詢30號部門的員工獎金（COMM）的合計值。

  

4. 查詢每個員工的姓名和他頂頭上司（MGR）的姓名（要求：使用自身串連）

 

5. 查詢月薪低於2100元，並且所在部門編號是30的員工編號和月薪。（要求：使用INTERSECT）

 

6. 查詢平均工資最高的兩個部門的部門編號。（要求：使用rownum偽列）

 

7. 查詢與ALLEN的所在部門和僱用年份相同的所有員工的姓名。（要求：使用多列子查詢）

 

8. 按工作崗位（job）分組，查詢每種崗位的員工個數

 

9. 按工作崗位（job）分組，查詢每種崗位的薪水的最低值。

 

10. 將“SALES”部門的員工薪水增加15% （注意：需要使用兩張表）

 

11. 查詢平均月薪高於2800元的部門名稱、員工姓名、部門平均月薪。

 

12. 從emp表中刪除 所在部門位於“BOSTON”的員工的資訊。（注意：需要使用兩張表）

    

 

（五）編寫PL/SQL程式塊，每道題目使用一個程式塊實現。

1、建立並執行一個PL/SQL程式塊，通過替代變數由鍵盤輸入兩個數值x和y，用第一個數x乘以第二個數y再除以10取整（小數四捨五入），將結果存入變數：v_你的姓名首字母（例如：v_zs），然後輸出到螢幕上。

 

2、建立並執行一個PL/SQL程式塊，查詢scott方案的emp表中的最高薪水，將其存入變數：v_你的姓名首字母（例如：v_zs）中，然後在螢幕上顯示。

 

3、建立並執行一個PL/SQL程式塊，計算scott方案中emp表的某個僱員的年薪，僱員的編號通過替代變數由鍵盤輸入，年薪等於月薪sal乘以12個月再加上獎金comm，使用NVL函數處理空值。將計算出的年薪存入變數：v_你的姓名首字母（例如：v_zs）中，然後將結果輸出到螢幕上。

 

4、建立並執行一個PL/SQL程式塊，向dept表中添加一行資料，用替代變數輸入部門的編號和名稱，部門位置為NULL。

 

5、建立並執行一個PL/SQL程式塊，更新部門位置的值，用替代變數輸入部門的編號和新的部門位置。

 

6、建立並執行一個PL/SQL程式塊，刪除第4題添加的部門，用替代變數輸入部門的編號。

 

 

Oracle資料庫—— PL/SQL基礎編程