oracle練習(xí)題

1、oracle 上機(jī)練習(xí)(一)本練習(xí)主要熟悉oracel sql*plus的一些常用命令。1、連接SQL*Plus system/manager2、顯示當(dāng)前連接用戶SQL> show user3、查看系統(tǒng)擁有哪些用戶SQL> select * from all_users;4、新建用戶并授權(quán)SQL> create user a identified by a;（默認(rèn)建在SYSTEM表空間下）SQL> grant connect,resource to a;5、連接到新用戶SQL> conn a/a6、查詢當(dāng)前用戶下所有對(duì)象SQL> select * from

2、tab;7、建立第一個(gè)表SQL> create table a(a number);8、查詢表結(jié)構(gòu)SQL> desc a9、插入新記錄SQL> insert into a values(1);10、查詢記錄SQL> select * from a;11、更改記錄SQL> update a set a=2;12、刪除記錄SQL> delete from a;Oracle 上機(jī)練習(xí)（二）一、進(jìn)入自己創(chuàng)建的用戶下進(jìn)行以下操作。1、創(chuàng)建表STUDENT、COURSE、GRADE，各個(gè)表的結(jié)構(gòu)如下： (1) STUDENT學(xué)號(hào) NUMBER(6),姓名 VARCHAR

3、2(12)入學(xué)時(shí)間 DATE,專業(yè) VARCHAR2(20),性別 CHAR(2),年齡 INTEGER (2) COURSE課程號(hào) NUMBER(6),課程名稱 VARCHAR2(20),學(xué)時(shí) INTEGER,學(xué)分 INTEGER(3) GRADE 學(xué)號(hào) NUMBER(6), 課程號(hào) NUMBER(6), 成績 NUMBER(2) 2、向上面的三個(gè)表中分別插入5條紀(jì)錄。 示例：(1) INSERT INTO STUDENT (學(xué)號(hào)，姓名，入學(xué)時(shí)間，專業(yè)，性別，年齡)VALUES (100001,HUANGWEI,TO_DATE(1999-09-10,YYYY-MM-DD),COMPUTER,

4、男,23);(2) INSERT INTO COURSE (課程號(hào),課程名稱,學(xué)時(shí),學(xué)分) VALUES (000001,多媒體,32,4);(3) INSERT INTO GRADE (學(xué)號(hào)，課程號(hào),成績)VALUES (100001,000001,90) 二、練習(xí)使用SQL*PLUS的一些編輯和執(zhí)行命令 1、RUN 簡寫R 或者 /用來列出并執(zhí)行當(dāng)前存儲(chǔ)在緩沖區(qū)的SQL命令示例：SQL> select * from student; SQL> RUN 1* select * from student; 2、LIST 簡寫 L列出當(dāng)前緩沖區(qū)的內(nèi)容，主要用在當(dāng)我們寫錯(cuò)SQL語句的時(shí)

5、候，可以將錯(cuò)誤的SQL語句列出來，以便修改LIST n 列出SQL語句的第幾行。LIST m,n 列出SQL語句的第幾行到第幾行。3、CHANGE 簡寫 C 編輯LIST命令列出的行。 用法： CHANGE 舊文本/新文本4、DEL 刪除LIST n命令列出的某一行5、APPEND 簡寫 A 在一行的尾部添加文本6、CLEAR BUFFER 簡寫 CL BUFF 刪除全部的行 oracle 上機(jī)練習(xí)(三)本練習(xí)主要熟悉在oracel sql*plus中常用的匯總函數(shù)：匯總函數(shù)主要有：COUNT：返回滿足WHERE 條件子句中記錄的個(gè)數(shù)。SUM：它返回某一列的所有數(shù)值的和。AVG：可以返回某一列

6、的平均值。MAX：如果你想知道某一列中的最大值請(qǐng)使用MAX。MIN：MIN 與MAX 類似它返回一列中的最小數(shù)值。練習(xí)：建立TEAMSTATS表如下：NAME POS AB HITS WALKS SINGLES DOUBLES TRIPLES HR SOJONES 1B 145 45 34 31 8 1 5 10DONKNOW 3B 175 65 23 50 10 1 4 15WORLEY LF 157 49 15 5 8 3 3 16 DAVID OF 187 70 24 48 4 0 17 42HAMHOCKER 3B 50 12 10 10 2 0 0 13CASEY DH 1 0 0

7、0 0 0 0 1在TEAMSTATS表上做如下操作：(1)查看WALKS小于20的球員的個(gè)數(shù)。SQL> SELECT COUNT(*) FROM TEAMSTATS WHERE WALKS<20;(2)查看表中所有記錄的個(gè)數(shù)。（自己寫）(3)查看隊(duì)員總的HITS數(shù)。SQL>SELECT SUM(HITS) TOTAL_HITS FROM TEAMSTATS;(4)查看3B位置上的隊(duì)員總的DOUBLES及總的SO。(5)查看漏球（SO）的平均數(shù)。SQL>SELECT AVG(SO) AVE_STRIKE_OUTS FROM TEAMSTATS;(6)查看TRIPLES為

8、0的隊(duì)員的HITS的平均數(shù)。(7)查看所有隊(duì)員的最高SIGGLES是多少。SQL>SELECT MAX(SIGGLES) FROM TEAMSTATS; oracle 上機(jī)練習(xí)(四) 題目：已知公司的員工表EMP(EID, ENAME, BDATE, SEX, CITY)，部門表DEPT（DID, DNAME, DCITY），工作表WORK（EID,DID,STARTDATE,SALARY）。各個(gè)字段說明如下：EID員工編號(hào)，最多6個(gè)字符。例如A00001（主鍵）ENAME員工姓名，最多10個(gè)字符。例如SMITHBDATE出生日期，日期型SEX員工性別，單個(gè)字符。F或者M(jìn)CITY員工居住

9、的城市，最多20個(gè)字符。例如：上海DID部門編號(hào)，最多3個(gè)字符。例如 A01 （主鍵）DNAME部門名稱，最多20個(gè)字符。例如：研發(fā)部門DCITY部門所在的城市，最多20個(gè)字符。例如：上海STARTDATE員工到部門上班的日期，日期型SALARY員工的工資。整型。請(qǐng)使用ORACLE的sql*plus 完成下列的操作1、 創(chuàng)建表EMP，DEPT，WORK，并定義表的主鍵和外鍵。2、 向每個(gè)表中插入適當(dāng)?shù)臄?shù)據(jù)。例如：插入三條部門的數(shù)據(jù)，分別為每個(gè)部門插入兩條員工數(shù)據(jù)3、 查詢“研發(fā)”部門的所有員工的基本信息4、 查詢擁有最多的員工的部門的基本信息（要求只取出一個(gè)部門的信息），如果有多個(gè)部門人數(shù)一樣

10、，那么取出部門編號(hào)最小的那個(gè)部門的基本信息。5、 顯示部門人數(shù)大于5的每個(gè)部門的編號(hào)，名稱，人數(shù)6、 顯示部門人數(shù)大于5的每個(gè)部門的最高工資，最低工資7、 列出員工編號(hào)以字母P至S開頭的所有員工的基本信息8、 刪除年齡超過60歲的員工9、 為工齡超過10年的職工增加10%的工資 說明：1、環(huán)境：sql*plus 2、將練習(xí)（四）所有的SQL語句整理成文本文件保存下來，以姓名和學(xué)號(hào)命名。下課之前作為附件發(fā)到郵箱：slioracle上機(jī)練習(xí)（五）一.PL/SQL快速學(xué)習(xí)：1.PL/SQL 是一項(xiàng)ORACLE 的技術(shù),是ORACLE 的過程型語言，它由標(biāo)準(zhǔn)的SQL 語句和一系列可以讓你在不同的情況下

11、對(duì)SQL 語句的執(zhí)行進(jìn)行控制的命令組成。2.PL/SQL 塊的結(jié)構(gòu): PL/SQL 是一種塊結(jié)構(gòu)語言,也就是說PL/SQL 的程序可以分成邏輯塊來寫。塊是PL/SQL 代碼的邏輯單元，包括至少一個(gè)BEGIN 部分和可以選擇的DECLARE 以及EXCEPTION 部分。 PL/SQL 塊的基本結(jié)構(gòu)：SYNTAX:BEGIN - optional, denotes beginning of block DECLARE - optional, variable definitions BEGIN - mandatory, denotes beginning of procedure section

12、 EXCEPTION - optional, denotes beginning of exception section END - mandatory, denotes ending of procedure sectionEND - optional, denotes ending of block (1)在PL/SQL 的DECLARE 部分包括了變量、常量、指針和特殊數(shù)據(jù)類型的定義。 (2) BEGIN部分是PL/SQL 語句塊中的必須部分，在這一部分將會(huì)使用變量和用戶指針來操作數(shù)據(jù)庫中的數(shù)據(jù)。 (3)EXCEPTION 部分在PL/SQL 語句塊中是可以選擇的，如果在這一部分被省略

13、而遇到異常的時(shí)候該塊就會(huì)終止了。3.由基本的PL/SQL塊所組成的PL/SQL程序，可組成不同的程序形式，它們的用途和適用性各不相同。程序形式大致有以下幾種： (1)無名塊：也就是沒有命名的PL/SQL塊，它可以是嵌入某一個(gè)應(yīng)用之中的一個(gè)PL/SQL塊。無名塊在所有PL/SQL環(huán)境中都適用。 (2)存儲(chǔ)過程/函數(shù)（Procedure/Function）：命名的PL/SQL塊，它可以接受參數(shù)，并且可 以重復(fù)的被調(diào)用。 (3)包（Package）：命名的PL/SQL塊，由一組相關(guān)的過程、函數(shù)和標(biāo)識(shí)符組成。 (4)數(shù)據(jù)庫觸發(fā)器（Triggers）：是與一個(gè)具體數(shù)據(jù)庫表相關(guān)聯(lián)的PL/SQL存儲(chǔ)程序。每

14、當(dāng)一個(gè)SQL操作影響到該數(shù)據(jù)庫表時(shí)，系統(tǒng)就自動(dòng)執(zhí)行相應(yīng)的數(shù)據(jù)庫觸發(fā)器。4.PL/SQL中的控制語句: (1)條件語句：IF.THEN 語法： IF condition1 THEN statement1； ELSIF condition2 THEN（可選） statement2； ELSE（可選） statement3； END IF (2)循環(huán)語句： i.簡單循環(huán)： LOOP statement1； END LOOP ii.FOR循環(huán)： iii.WHILE循環(huán)：二.實(shí)例： 1.使用的表為：PAY_TABLE， 結(jié)構(gòu)： Name Null? Type NAME NOT NULL VARCHAR2

15、(20) PAY_TYPE NOT NULL VARCHAR2(8) PAY_RATE NOT NULL NUMBER(8,2) EFF_DATE NOT NULL DATE PREV_PAY NUMBER(8,2) 數(shù)據(jù)： NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY SANDRA SAMUELS HOURLY 12.50 01-JAN-04 ROBERT BOBAY HOURLY 11.50 15-MAY-03 KEITH JONES HOURLY 10.00 31-OCT-04 SUSAN WILLIAMS HOURLY 9.75 01-MAY-04

16、CHRISSY ZOES SALARY 50000.00 01-JAN-04 CLODE EVANS SALARY 42150.00 01-MAR-04 JOHN SMITH SALARY 35000.00 15-JUN-03 KEVIN TROLLBERG SALARY 27500.00 15-JUN-03 2.操作要求：要給為你工作的時(shí)間超過了六個(gè)月的個(gè)人增加薪金。符合條件的鐘點(diǎn)工的薪金增加4% 而符合條件的雇員的薪金需要增加5% 。PL/SQL 的腳本： set serveroutput on BEGIN DECLARE UnknownPayType exception; cursor

17、pay_cursor is select name, pay_type, pay_rate, eff_date, sysdate, rowid from pay_table; IndRec pay_cursor%ROWTYPE; cOldDate date; fNewPay number(8,2); BEGIN open pay_cursor; loop fetch pay_cursor into IndRec; exit when pay_cursor%NOTFOUND; cOldDate := sysdate - 180; if (IndRec.pay_type = 'SALARY

18、') then fNewPay := IndRec.pay_rate * 1.05; elsif (IndRec.pay_type = 'HOURLY') then fNewPay := IndRec.pay_rate * 1.04; else raise UnknownPayType; end if; if (IndRec.eff_date < cOldDate) then update pay_table set pay_rate = fNewPay, prev_pay = IndRec.pay_rate, eff_date = IndRec.sysdate

19、where rowid = IndRec.rowid; commit; end if; end loop; close pay_cursor; EXCEPTION when UnknownPayType then dbms_output.put_line('='); dbms_output.put_line('ERROR: Aborting program.'); dbms_output.put_line('Unknown Pay Type for Name'); when others then dbms_output.put_line(

20、9;ERROR During Processing. See the DBA.'); END; END;/三.練習(xí)： 1.對(duì)PAY_TABLE表寫一個(gè)查詢所有記錄的過程。 2.對(duì)PAY_TABLE表寫一個(gè)插入一條記錄的過程，要求輸入?yún)?shù)為一條記錄。 3.寫一個(gè)過程實(shí)現(xiàn)如下要求：對(duì)工作時(shí)間超過8個(gè)月的職員，如果PAY_TYPE 是HOURLY,則改為SALARY,并將PAY_RATE改為按每天7小時(shí)工作的年薪. oracle上機(jī)練習(xí)（六）一 案例某數(shù)據(jù)庫有兩張表：emp表和dept表，兩張表的結(jié)構(gòu)如下：emp (emp_id number(5), emp_name varchar2(20

21、), emp_salary number(4)；dept (dept_id number(3), dept_name varchar2(20), emp_id number(5);要求如下：1、按照上表結(jié)構(gòu)建立相應(yīng)的表，為每張表寫入5組合法數(shù)據(jù)。2、操縱相關(guān)表，使得“技術(shù)部”的員工的薪水上漲20%。3、建立日志，追蹤薪水變動(dòng)情況。4、建立測試包。二 案例的分析與實(shí)現(xiàn)要求1考察點(diǎn)為基本SQL語句；要求2主要考察復(fù)合查詢；要求3是考察觸發(fā)器的應(yīng)用；要求4不僅考察了包的創(chuàng)建，而且也考察了在PL/SQL中的測試方法。要求1：首先根據(jù)前面表的結(jié)構(gòu)可以創(chuàng)建兩張表：創(chuàng)建員工表create table emp

22、 (emp_id number(5), emp_name varchar2(20), emp_salary number(4);部門表create table dept (dept_id number(3), dept_name varchar2(20), emp_id number(5);建立了表之后就可以往表里面寫數(shù)據(jù)了，這里把添加表記錄的代碼寫入到相應(yīng)的存儲(chǔ)過程。/*給emp表添加記錄的存儲(chǔ)過程*/create or replace procedure ins_table_emp(p_emp_id number,p_emp_name varchar2,p_emp_salary numbe

23、r) asv_emp_id number:=p_emp_id;v_emp_name varchar2(20):=p_emp_name;v_emp_salary number:=p_emp_salary;begininsert into emp values (v_emp_id,v_emp_name,v_emp_salary);end ins_table_emp;/*給dept表添加記錄的存儲(chǔ)過程*/create or replace procedure ins_table_dept(p_dept_id number,p_dept_name varchar2,p_emp_id number) a

24、sv_dept_id number:=p_dept_id;v_dept_name varchar2(20):=p_dept_name;v_emp_id number:=p_emp_id;begininsert into dept values (v_dept_id,v_dept_name,v_emp_id);end ins_table_emp;/*調(diào)用相應(yīng)的存儲(chǔ)過程實(shí)現(xiàn)記錄添加*/beginins_table_emp(10000,'',4000);ins_table_emp(10001,'?èy',2300);ins_table_emp(10002,&

25、#39;3?t',3500);ins_table_emp(10003,'à?',3500);ins_table_emp(10004,'á?ò?',3500);ins_table_dept(111,'DD?t2?',10000);ins_table_dept(111,'DD?t2?',10001);ins_table_dept(111,'DD?t2?',10002);ins_table_dept(112,'?ê?2?',10003);ins_table_

26、dept(113,'êD3?2?',10004);end;要求2：給指定部門的員工加薪，這實(shí)際上是一個(gè)復(fù)合查詢，首先需要把所有該部門的員工選出來，然后對(duì)這些員工的薪水進(jìn)行相應(yīng)的改動(dòng)。代碼如下：（需要注意的是：將要加薪的部門作為參數(shù)，這樣的存儲(chǔ)過程更有靈活性。）create or replace procedure add_salary(p_dept_name varchar2) asv_dept_name varchar2(20):=p_dept_name;beginupdate emp set emp.EMP_SALARY=emp.EMP_SALARY*1.2 wh

27、ere emp.EMP_ID in (select emp.EMP_ID from emp,dept where emp.EMP_ID=dept.EMP_ID and dept.DEPT_ID='?ê?2?');end add_salary;要求3：建立日志對(duì)薪水的變動(dòng)情況形成一個(gè)追蹤，也就是說，如果對(duì)某個(gè)職員的薪水進(jìn)行變更就應(yīng)該將其相應(yīng)的變更記錄全部記下來。如果對(duì)emp表的salary字段創(chuàng)建一個(gè)觸發(fā)器，來監(jiān)視對(duì)salary的更改，把每次更改進(jìn)行記錄，這樣就達(dá)到了要求3的目的了。create or replace trigger print_salary_chang

28、ebefore delete or insert or update on emp-觸發(fā)事件for each row- 每修改一行都需要調(diào)用此過程declare -只有觸發(fā)器的聲明需要declare，過程和函數(shù)都不需要salary_balance number;begin-:new 與:old分別代表該行在修改前和修改后的記錄salary_balance=:new.salary=:old.salary;dbms_output.PUT_LINE('old salary is: '| :old.salary);dbms_output.PUT_LINE('old salary

29、 is: '| :new.salary);dbms_output.PUT_LINE('old salary is: '| to_char(salary_balance);end print_salary_change;要求4：與其他語言（c/c+等）相比，PL/SQL的測試有其不同之處，歸納下來有三種方法：1、使用DBMS_OUTPUT包的PUT_LINE方法來顯示中間變量，以此來觀察程序是否存在邏輯錯(cuò)誤。2、插入測試表的方法。即創(chuàng)建一個(gè)臨時(shí)的中間表，然后把所有涉及到的中間變量的結(jié)果都作為記錄插入到中間表中，這樣可以查詢表中的結(jié)果來觀察程序的執(zhí)行情況。3、使用異常處理手

30、段，對(duì)可疑的程序段使用begin end ，然后可以在exception里進(jìn)行異常捕獲處理。這里使用第二種方法來建立一個(gè)測試包，PL/SQL里包的概念類似于面向?qū)ο罄锏念惖母拍?，包將一組操作和屬性封裝在一起，不僅增強(qiáng)了程序的模塊化，而且由于封裝了更多的操作和屬性而提高了執(zhí)行效能。建立一個(gè)PL/SQL需要兩個(gè)步驟：首先要建立包頭，類似于建立一個(gè)類的頭文件，里面主要對(duì)包中的過程，函數(shù)和變量的聲明；第二部分主要是包體部分，實(shí)現(xiàn)前面聲明的過程和函數(shù)，另外還需要對(duì)包進(jìn)行初始化等工作。根據(jù)這一思路，建立測試包如下：/*包頭部分*/create or replace package debug asproc

31、edure debug(v_description varchar2,v_valueOfvariable varchar2)procedure reset;v_numberOfLine number;end debug;/*包體部分*/create or replace package body debug asprocedure debug(v_description varchar2,v_valueOfvariable varchar2) isbegininsert into debugtablevalues(v_numberOfLine,v_description,v_valueOfvariable);v_numberOfLine:=v_numberOfLine+1;end debug;