Oracle_PL_SQL經(jīng)典練習(xí)題.doc

Oracle 作業(yè)題一.創(chuàng)建一個(gè)簡(jiǎn)單的PL/SQL程序塊 使用不同的程序塊組件工作 使用編程結(jié)構(gòu)編寫PL/SQL程序塊 處理PL/SQL程序塊中的錯(cuò)誤 1.編寫一個(gè)程序塊,從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.編寫一個(gè)程序塊，接受用戶輸入一個(gè)部門號(hào)，從dept表中顯示該部門的名稱與所在位置。方法一：（傳統(tǒng)方法）declarev_loc deptcp.dname%type;v_dname deptcp.dname%type;v_deptno deptcp.deptno%type;beginv_deptno :=&部門編號(hào);select loc,dname into v_loc,v_dname from deptcp where deptno=v_deptno;dbms_output.put_line(員工所在地是：|v_loc|；部門名稱是：|v_dname);exceptionwhen no_data_found then dbms_output.put_line(您輸入的部門編號(hào)不存在，請(qǐng)從新輸入，謝謝);end;方法二：（使用%rowtype） declare v_dept dept%rowtype; begin select * into v_dept from dept where deptno=&部門號(hào); dbms_output.put_line(v_dept.dname|-|v_dept.loc); end; 3.編寫一個(gè)程序塊，利用%type屬性，接受一個(gè)雇員號(hào)，從emp表中顯示該雇員的整體薪水(即，薪水加傭金)。（*期末考試試題*）declare v_sal emp.sal%type; begin select sal+comm into v_sal from emp where empno=&雇員號(hào); dbms_output.put_line(v_sal); end; 4.編寫一個(gè)程序塊，利用%rowtype屬性，接受一個(gè)雇員號(hào)，從emp表中顯示該雇員的整體薪水(即，薪水加傭金)。方式一：（錯(cuò)誤程序）（讓學(xué)生思考錯(cuò)在哪里？） declare v_emp empcp%rowtype; begin select * into v_emp from empcp where empno = &雇員編號(hào); dbms_output.put_line(整體薪水是：|v_emp.sal+v_m); end; declare v_emp emp%rowtype; begin select * into v_emp from emp where empno=&雇員號(hào); dbms_output.put_line(v_emp.sal+v_m); end; 5.某公司要根據(jù)雇員的職位來加薪，公司決定按下列加薪結(jié)構(gòu)處理： Designation Raise - Clerk 500 Salesman 1000 Analyst 1500 Otherwise 2000編寫一個(gè)程序塊,接受一個(gè)雇員名，從emp表中實(shí)現(xiàn)上述加薪處理。（*期末考試試題*）declare v_emp emp%rowtype; begin select * into v_emp from emp where ename=&name; if v_emp.job=CLERK then update emp set sal=sal+500 where empno=v_emp.empno; elsif v_emp.job=SALESMAN then update emp set sal=sal+1000 where empno=v_emp.empno; elsif v_emp.job=ANALYST then update emp set sal=sal+1500 where empno=v_emp.empno; else update emp set sal=sal+2000 where empno=v_emp.empno; end if; commit;end; 6.編寫一個(gè)程序塊，將emp表中雇員名全部顯示出來。 declare cursor v_cursor is select * from emp; begin for v_emp in v_cursor loop dbms_output.put_line(v_emp.ename); end loop; end; 7.編寫一個(gè)程序塊，將emp表中前5人的名字顯示出來。declare cursor v_cursor is select * from emp; v_count number :=1; begin for v_emp in v_cursor loop dbms_output.put_line(v_emp.ename); v_count := v_count+1; exit when v_count5; end loop; end; 8.編寫一個(gè)程序塊，接受一個(gè)雇員名，從emp表中顯示該雇員的工作崗位與薪水，若輸入的雇員名不存在，顯示“該雇員不存在”信息。（*期末考試試題*）declare v_emp emp%rowtype; my_exception Exception; begin select * into v_emp from emp where ename=&name; raise my_exception; exception when no_data_found then dbms_output.put_line(該雇員不存在!); when others then dbms_output.put_line(v_emp.job|-|v_emp.sal); end; 9.接受兩個(gè)數(shù)相除并且顯示結(jié)果，如果第二個(gè)數(shù)為0，則顯示消息“除數(shù)不能為0”(課堂未講)。declare v_dividend float; v_divisor float; v_result float; my_exception Exception; begin v_dividend:=&被除數(shù); v_divisor:=&除數(shù); v_result:=v_dividend/v_divisor; raise my_exception; exception when my_exception then dbms_output.put_line(v_result); when others then dbms_output.put_line(除數(shù)不能為0); end;二.聲明和使用游標(biāo) 使用游標(biāo)屬性 使用游標(biāo)For循環(huán)工作 聲明帶參數(shù)的游標(biāo) （使用FOR UPDATE OF和CURRENT OF子句工作）1. 通過使用游標(biāo)來顯示dept表中的部門名稱。declare cursor v_cursor is select * from dept; begin for v_dept in v_cursor loop dbms_output.put_line(v_dept.dname); end loop; end;2. 使用For循環(huán)，接受一個(gè)部門號(hào)，從emp表中顯示該部門的所有雇員的姓名，工作和薪水。declare cursor v_cursor is select * from emp where deptno=&部門號(hào); begin for v_emp in v_cursorloop dbms_output.put_line(v_emp.ename|-|v_emp.job|-|v_emp.sal); end loop; end;3. 使用帶參數(shù)的游標(biāo)，實(shí)現(xiàn)第2題。declare cursor v_cursor(p_deptno number) is select * from emp where deptno=p_deptno; v_deptno number(2); begin v_deptno:=&部門號(hào); for v_emp in v_cursor(v_deptno) loop dbms_output.put_line(v_emp.ename|-|v_emp.job|-|v_emp.sal); end loop; end; 4.編寫一個(gè)PL/SQL程序塊，從emp表中對(duì)名字以“A”或“S”開始的所有雇員按他們基本薪水的10%給他們加薪。declare cursor v_cursor is select * from emp; begin for v_emp in v_cursor loop if v_emp.ename like A% then update emp set sal=sal+sal*0.1 where empno=v_emp.empno; elsif v_emp.ename like S% then update emp set sal=sal+sal*0.1 where empno=v_emp.empno; end if; commit; end loop; end;5. emp表中對(duì)所有雇員按他們基本薪水的10%給他們加薪，如果所增加后的薪水大于5000盧布，則取消加薪。declare cursor v_cursor is select * from emp;begin for v_emp in v_cursor loop if v_emp.sal * 1.1 5000 then update emp set sal = sal * 1.1 where empno = v_emp.empno; end if; commit; end loop;end;三，創(chuàng)建PL/SQL記錄和PL/SQL表 創(chuàng)建過程 創(chuàng)建函數(shù) 3.創(chuàng)建一個(gè)過程，能向dept表中添加一個(gè)新記錄.（in參數(shù)）create or replace procedureinsert_dept(dept_no in number,dept_name in varchar2,dept_loc in varchar2) is begin insert into dept values(dept_no,dept_name,dept_loc); end;調(diào)用該存儲(chǔ)過程： begin insert_dept(50,技術(shù)部,武漢); end; 4.創(chuàng)建一個(gè)過程，從emp表中帶入雇員的姓名，返回該雇員的薪水值。（out參數(shù)）然后調(diào)用過程。create or replace procedure find_emp3(emp_name in varchar2,emp_sal out number) is v_sal number(5); begin select sal into v_sal from emp where ename = emp_name; emp_sal:=v_sal; exception when no_data_found then emp_sal :=0; end;調(diào)用：declare v_sal number(5); begin find_emp3(ALLEN,v_sal); dbms_output.put_line(v_sal); end; 5.編寫一個(gè)程序塊，接受一個(gè)雇員號(hào)與一個(gè)百分?jǐn)?shù)，從emp表中將該雇員的薪水增加輸入的百分比（*課堂沒講）。 （利用過程，in out 參數(shù)）create or replace procedure update_sal(emp_no in number,parsent in float) is begin update emp set sal=sal+sal*parsent where empno=emp_no; end;調(diào)用： begin update_sal(7499,0.5); end; 6.創(chuàng)建一個(gè)函數(shù)，它以部門號(hào)作為參數(shù)且返回那個(gè)部門的所有的所有雇員的整體薪水。 然后調(diào)用此函數(shù)。 7.創(chuàng)建一個(gè)函數(shù)，它以部門號(hào)作為參數(shù)傳遞并且使用函數(shù)顯示那個(gè)部門名稱與位置。然后調(diào)用此函數(shù)。create or replace function find_dept(dept_no number) return dept%rowtype is v_dept dept%rowtype; begin select * into v_dept from dept where deptno=dept_no; return v_dept; end;調(diào)用函數(shù)：declare v_dept dept%rowtype; begin v_dept:=find_dept(30); dbms_output.put_line(v_dept.dname|-|v_dept.loc); end;四，創(chuàng)建程序包 創(chuàng)建程序件 創(chuàng)建觸發(fā)器 1.創(chuàng)建在dept表中插入和刪除一個(gè)記錄的數(shù)據(jù)包，它且有一個(gè)函數(shù)(返回插入或刪除的部門名稱)和兩個(gè)過程。然后調(diào)用包。create or replace package pack_1 is procedure find_emp(emp_no in number,emp_name out varchar2); procedure find_emp1(emp_name in varchar2,emp_no out number); function find_dname(dept_no number) return varchar2; end pack_1; create or replace package body pack_1is function find_dname(dept_no number) return varchar2is v_dname varchar2(20); begin select dname into v_dname from dept where deptno=dept_no; retrun v_dname; end;end pack_1;調(diào)用包：declare v_dname varchar2(20); begin v_dname:=pack_1.find_dname(50); dbms_output.put_line(v_dname); end; 3.使用單獨(dú)過程打開游標(biāo)變量，將dept表中的記錄顯