Oracle經(jīng)典練習(xí)題(很全面)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上精選優(yōu)質(zhì)文檔-傾情為你奉上專心-專注-專業(yè)專心-專注-專業(yè)精選優(yōu)質(zhì)文檔-傾情為你奉上專心-專注-專業(yè)Oracle 經(jīng)典練習(xí)題一.創(chuàng)建一個簡單的PL/SQL程序塊 1.編寫一個程序塊,從emp表中顯示名為“SMITH”的雇員的薪水和職位。declare v_emp emp%rowtype; begin select * into v_emp from emp where ename=SMITH; dbms_output.put_line(員工的工作是：|v_emp.job| ； 他的薪水是：|v_emp.sal); end; 2.編寫一個程序塊，接受用戶輸入一個部門號，

2、從dept表中顯示該部門的名稱與所在位置。方法一：（傳統(tǒng)方法） declare pname dept.dname%type; ploc dept.loc%type; pdeptno dept.deptno%type;begin pdeptno:=&請輸入部門編號; select dname,loc into pname,ploc from dept where deptno=pdeptno; dbms_output.put_line(部門名稱: |pname|所在位置:|ploc); exception 異常處理 when no_data_found then dbms_output.put_

3、line(你輸入的部門編號有誤!); when others then dbms_output.put_line(其他異常);end;方法二：（使用%rowtype） declare erow dept%rowtype; begin select * into erow from dept where deptno=&請輸入部門編號; dbms_output.put_line(erow.dname|-|erow.loc); exception when no_data_found then dbms_output.put_line(你輸入的部門號有誤!); when others then d

4、bms_output.put_line(其他異常); end; 3.編寫一個程序塊，利用%type屬性，接受一個雇員號，從emp表中顯示該雇員的整體薪水(即，薪水加傭金)。declare pempno emp.empno%type; totalSal emp.sal%type;begin pempno:=&請輸入員工編號; select sal+nvl(comm,0) into totalSal from emp where empno=pempno; dbms_output.put_line(該員工總共薪水|totalSal); exception when no_data_found th

5、en dbms_output.put_line(你輸入的員工編號有誤!); when others then dbms_output.put_line(其他異常);end; 4.編寫一個程序塊，利用%rowtype屬性，接受一個雇員號，從emp表中顯示該雇員的整體薪水(即，薪水加傭金)。declare erow emp%rowtype;begin select * into erow from emp where empno=&請輸入員工編號; dbms_output.put_line(erow.sal+nvl(m,0); exception when no_data_found then d

6、bms_output.put_line(你輸入的員工編號有誤!); when others then dbms_output.put_line(其他異常);end; 5.某公司要根據(jù)雇員的職位來加薪，公司決定按下列加薪結(jié)構(gòu)處理： Designation Raise - Clerk 500 Salesman 1000 Analyst 1500 Otherwise 2000編寫一個程序塊,接受一個雇員名，從emp表中實(shí)現(xiàn)上述加薪處理。declareerow emp%rowtype;begin select * into erow from emp where ename=&name; if ero

7、w.job=Clerk then update emp set sal=sal+500 where empno=erow.empno; elsif erow.job=Salesman then update emp set sal=sal+1000 where empno=erow.empno; elsif erow.job=Analyst then update emp set sal=sal-1500 where empno=erow.empno; else update emp set sal=sal+2000 where empno=erow.empno; end if; commit

8、;exception when no_data_found then dbms_output.put_line(你輸入的員工編號有誤!); when others then dbms_output.put_line(其他異常);end;6.編寫一個程序塊，將emp表中雇員名全部顯示出來。declare cursor cs is select ename from emp;begin for erow in cs loop dbms_output.put_line(erow.ename); end loop;end; 7.編寫一個程序塊，將emp表中前5人的名字顯示出來。方式一:declare

9、cursor cs is select t.* from (select e.ename,rownum rm from emp e)t where t.rm between 1 and 6;begin for erow in cs loop dbms_output.put_line(erow.ename); end loop;end;方式二:-方式二declare cursor cs is select ename from emp; i number :=1;begin for erow in cs loop dbms_output.put_line(erow.ename); i:=i+1;

10、 -迭代 exit when i5; -退出條件 end loop;end; 8.編寫一個程序塊，接受一個雇員名，從emp表中顯示該雇員的工作崗位與薪水，若輸入的雇員名不存在，顯示“該雇員不存在”信息。declare pjob emp.job%type; totalsal emp.sal%type;begin select job,sal into pjob,totalsal from emp where ename=&請輸入員工姓名; dbms_output.put_line(pjob |- |totalsal); exception when no_data_found then dbms

11、_output.put_line(你輸入的員工姓名有誤!);end; 9.接受兩個數(shù)相除并且顯示結(jié)果，如果第二個數(shù)為0，則顯示消息“除數(shù)不能為0”。declare num1 float; num2 float; res float; my_exception Exception; begin num1:=&被除數(shù); num2:=&除數(shù); res:=num1/num2; raise my_exception; exception when my_exception then dbms_output.put_line(res); when others then dbms_output.put_l

12、ine(除數(shù)不能為0); end;二.聲明和使用游標(biāo)- 游標(biāo):(集合) ，處理返回多行記錄的問題 - 聲明游標(biāo) -語法： cursor 游標(biāo)名 is DQL;- 遍歷游標(biāo) /* 1.打開游標(biāo), open 游標(biāo)名; 2.從游標(biāo)中提取一行的記錄：fetch 游標(biāo)名 into 變量名,.; 3.使用循環(huán)， exit when 游標(biāo)名%notfound; 4.關(guān)閉游標(biāo)， close 游標(biāo)名;通過使用游標(biāo)來顯示dept表中的部門名稱。 declare cursor co is select dname from dept; begin for vname in co loop dbms_output.p

13、ut_line(vname.dname); end loop; end;使用For循環(huán)，接受一個部門號，從emp表中顯示該部門的所有雇員的姓名，工作和薪水。declare cursor c_emp is select * from emp where deptno=&請輸入部門號;begin for erow in c_emp loop dbms_output.put_line(erow.ename | |erow.job | |erow.sal); end loop;exception when no_data_found then dbms_output.put_line(輸入的部門編號有

14、誤);end;使用帶參數(shù)的游標(biāo)，實(shí)現(xiàn)第2題。declare cursor c_cs(c_deptno number) is select * from emp where deptno=c_deptno; v_deptno number;begin v_deptno:=&請輸入部門編號; for erow in c_cs(v_deptno) loop dbms_output.put_line(erow.ename | |erow.job | |erow.sal); end loop;exception when no_data_found then dbms_output.put_line(輸

15、入的部門編號有誤);end; 4.編寫一個PL/SQL程序塊，從emp表中對名字以“A”或“S”開始的所有雇員按他們基本薪水的10%給他們加薪。declare cursor c_emp is select ename from emp;begin for erow in c_emp loop if erow.ename like A% then update emp set sal=sal+sal*0.1 where ename=erow.ename; elsif erow.ename like S% then update emp set sal=sal+sal*0.1 where enam

16、e=erow.ename; end if; commit; end loop;end;emp表中對所有雇員按他們基本薪水的10%給他們加薪，如果所增加后的薪水大于5000盧布，則取消加薪。 declare cursor c_emp is select * from emp;begin for erow in c_emp loop if erow.sal*1.15000 then update emp set sal=sal+sal*0.1 where ename=erow.ename; end if; commit; end loop;end;三 存儲過程- 存儲過程(dba聲明,得授予dba

17、權(quán)限): 封裝了一組sql語句，提前編譯好，效率較高 ,存儲在服務(wù)端 - 場景：網(wǎng)購：數(shù)據(jù)庫發(fā)生什么改變 - 庫存量-1(update) ,訂單增加(insert)，錢（update）,物流(insert) , 日志(insert)- 語法 /* create or replace procedure 存儲過程名稱(參數(shù)名 in|out 類型,.) as | is - 聲明變量 begin - 過程化語言 end; */1.創(chuàng)建一個過程，能向dept表中添加一個新記錄.（in參數(shù)）create or replace procedure insert_dept(p_deptno in numbe

18、r,p_dname in varchar2,p_loc in varchar2)isbegin insert into dept values(p_deptno,p_dname,p_loc);end;-調(diào)用該存儲過程declarebegin insert_dept(50,DEVELOP,SHENGZ);end; 2.創(chuàng)建一個過程，從emp表中輸入雇員的姓名，返回該雇員的薪水值。（out參數(shù)）然后調(diào)用過程。create or replace procedure find_emp(p_name in varchar2,p_sal out number)ise_sal emp.sal%type;be

19、gin select sal into e_sal from emp where ename=p_name; p_sal:=e_sal;exception when no_data_found then p_sal:=0;end;-調(diào)用存儲過程declare msal number(5);begin find_emp(SCOTT,msal); dbms_output.put_line(msal);end; 3.編寫一個程序塊，接受一個雇員號與一個百分?jǐn)?shù)，從emp表中將該雇員的薪水增加輸入的百分比。 （利用過程，in out 參數(shù)） create or replace procedure add

20、Sal(p_empno in number,p_num in float)isbegin update emp set sal=sal+sal*p_num where empno=p_empno;exception when no_data_found then dbms_output.put_line(輸入的員工編號有誤);end;-訪問存儲過程declarebegin addSal(7788,0.5); commit;end;存儲函數(shù) - 存儲函數(shù):封裝了一組sql語句，提前編譯好，效率較高 ,存儲在服務(wù)端 - 存儲函數(shù)必須有一個返回值，存儲函數(shù)可以用select語句中 /* create

21、 or replace function 函數(shù)名(參數(shù)名 in|out 類型,.) return type as | is begin return 值; end; */ 4.創(chuàng)建一個函數(shù)，它以部門號作為參數(shù)且返回那個部門的所有的雇員的整體薪水(其實(shí)就是該部門的平均工資)。 然后調(diào)用此函數(shù)。create or replace function getAllSal(f_deptno in number)return numberise_sal emp.sal%type;begin select avg(sal) into e_sal from emp where deptno=f_deptno;

22、 return e_sal;exception when no_data_found then return 0;end;-調(diào)用存儲函數(shù)select getAllSal(20) from dual; 5.創(chuàng)建一個函數(shù)，它以部門號作為參數(shù)傳遞并且使用函數(shù)顯示那個部門名稱與位置。然后調(diào)用此函數(shù)。 create or replace function showDnameAndLoc(f_deptno in number)return dept%rowtypease_row dept%rowtype;begin select * into e_row from dept where deptno=f_deptno; return e_row;end;-訪問存儲函數(shù)declare erow dept%rowtype;begin erow:=showDnameAndLoc(20); dbms_output.put_line(erow.dname | | erow.loc);end;四 觸發(fā)器練習(xí) - 觸發(fā)器(監(jiān)聽器):監(jiān)聽表中的數(shù)據(jù)是否發(fā)生了改變 - 增刪改 操作/*create or replace trigger 觸發(fā)器名after | before 在改變之前還是之后執(zhí)行觸發(fā)器insert | dele