PL/SQL程序基礎(chǔ)1

2023-12-09 21:28| 來源: 網(wǎng)絡(luò)整理| 查看: 265

PL/SQL程序基礎(chǔ)1 一．實驗?zāi)繕?biāo)1、針對數(shù)據(jù)庫應(yīng)用領(lǐng)域的數(shù)據(jù)需求，設(shè)計出基于Oracle數(shù)據(jù)庫的解決方案的能力；2、承擔(dān)Oracle數(shù)據(jù)庫系統(tǒng)的實施、運(yùn)行與維護(hù)等基本工作的能力。二．實驗項目三．實驗錯誤解決方案問題一：創(chuàng)建的過程帶有編譯錯誤問題

一．實驗?zāi)繕?biāo)

掌握PL/SQL程序的基本結(jié)構(gòu)編程，具備如下能力：

1、針對數(shù)據(jù)庫應(yīng)用領(lǐng)域的數(shù)據(jù)需求，設(shè)計出基于Oracle數(shù)據(jù)庫的解決方案的能力； 2、承擔(dān)Oracle數(shù)據(jù)庫系統(tǒng)的實施、運(yùn)行與維護(hù)等基本工作的能力。二．實驗項目

編寫存儲過程或存儲函數(shù)完成以下各題，存儲過程或函數(shù)依次命名為obj2_1、obj2_2、… 以下1-7題與EMP、DEPT表有關(guān)。如果沒有上述基本表，可以用下述SQL語句創(chuàng)建：

create table COUNTRIES as select * from univ.COUNTRIES; create table DEPT as select * from univ.DEPT; create table EMP as select * from univ.EMP;

1、輸出名為SMITH的雇員的工資和職位。(存儲過程)

create or replace procedure obj2_1 as vsal emp.sal%type; vjob emp.job%type; begin select sal,job into vsal, vjob from emp where ename='SMITH'; dbms_output.put_line(vsal ||','|| vjob); end; . / set serveroutput on; exec obj2_1;

2、傳送參數(shù)部門編號，輸出該部門名和地理位置。(存儲過程)

create or replace procedure obj2_2(z_deptno dept.deptno%type) as z_dname dept.dname%type; z_loc dept.loc%type; begin select dname,loc into z_dname,z_loc from dept where deptno=z_deptno; dbms_output.put_line(z_dname||' '||z_loc); end; . / exec obj2_2(30)

3、傳送參數(shù)雇員號，輸出該雇員的工資和提成，沒有提成的用0替代。（用%type實現(xiàn)）。(存儲過程)

create or replace procedure obj2_3(v_empno emp.empno%type) as v_sal emp.empno%type; v_comm emp.comm%type; begin select sal,nvl(comm,0) into v_sal,v_comm from emp where empno=v_empno; dbms_output.put_line(v_sal||' '||v_comm); end; . / set serveroutput on; exec obj2_3(7369)

4、傳送參數(shù)雇員號，輸出該雇員的所有信息，沒有提成的用0替代。（用%rowtype實現(xiàn)）。(存儲過程)

create or replace procedure obj2_4(vempno emp.empno%type) as row_emp emp%rowtype; begin select empno, ename,job,mgr, hiredate,nvl(sal,0),nvl(comm,0),deptno into row_emp from emp where empno= vempno; dbms_output.put(row_emp.empno||','||row_emp.ename||','); dbms_output.put(row_emp.job||','||row_emp.mgr||','); dbms_output.put(row_emp.hiredate||','||row_emp.sal||','); dbms_output.put_line(row_emp.comm||','||row_emp.deptno); end; . / set serveroutput on; exec obj2_4(7369)

5、傳送參數(shù)雇員號，返回該雇員的工資。(存儲函數(shù))

create or replace function obj2_5(v_empno emp.empno%type) return integer as v_sal emp.sal%type; begin select sal into v_sal from emp where empno=v_empno; return v_sal; end; . / select obj2_5(7369)from dual;

6、傳送參數(shù)雇員名或雇員編號，判斷他的job，根據(jù)job不同，為他增加相應(yīng)的sal（用if-elsif實現(xiàn)，不要改動到基本表emp，創(chuàng)建一個與emp表一模一樣的表emp1）。(存儲過程) ?Job?? ??raise ?clerk? ??+500 ?salesman?+1000 ?analyst??+1500 ?otherwise?+3000

create table emp1 as select * from emp; create or replace procedure obj2_6(vempno emp1.empno%type,vname emp1.ename%type) as vsal emp1.sal%type; vjob emp1.job%type; begin select sal,job into vsal,vjob from emp1 where empno=vempno or ename=vname; if vjob='CLERK' then vsal:=vsal+500; elsif vjob='SALESMAN' then vsal:=vsal+1000; elsif vjob='ANALYST' then vsal:=vsal+1500; else vsal:=vsal+3000; end if; update emp1 set sal=vsal where (vempno=empno) or (ename=vname); dbms_output.put_line(vname||'的工資已經(jīng)更改為:'||vsal); end; . / exec obj2_6(7369,''); exec obj2_6('','SMITH');

7、傳送參數(shù)部門編號，按照下列加工資比例執(zhí)行給該部門的雇員加工資(用CASE實現(xiàn)，修改emp1表的數(shù)據(jù)) (存儲過程) ?deptno?raise(%) ?10???8% ?20???10% ?30???20% ?40???20% 加薪比例以現(xiàn)有的sal為標(biāo)準(zhǔn)。

create or replace procedure obj2_7(vdeptno emp1.deptno%type) as begin case(vdeptno) when 10 then update emp1 set sal=sal*1.08 where deptno=vdeptno; when 20 then update emp1 set sal=sal*1.18 where deptno=vdeptno; when 30 then update emp1 set sal=sal*1.20 where deptno=vdeptno; when 40 then update emp1 set sal=sal*1.20 where deptno=vdeptno; end case; dbms_output.put_line(vdeptno||'部門的加薪已完成'); end; . / set serveroutput on; exec obj2_7(20);

以下8-12題與以下表有關(guān)：學(xué)生、學(xué)費標(biāo)準(zhǔn)表、收費表、收費明細(xì)表。先從a_db模式中將這些表等復(fù)制到自己的模式中。

學(xué)生每學(xué)年開學(xué)前必須注冊。開始注冊前要初始化學(xué)生表，所有狀態(tài)為“注冊”的學(xué)生的狀態(tài)設(shè)置為空值，注冊后設(shè)置為"注冊"，還有“畢業(yè)”、“開除”、“休學(xué)”等狀態(tài)。注冊時在收費表生成相應(yīng)記錄。學(xué)生每學(xué)年按學(xué)生所屬專業(yè)收取學(xué)費，交學(xué)費時產(chǎn)生收費明細(xì)記錄，并修改收費表中相應(yīng)記錄。

create table 學(xué)生 as select * from a_db.學(xué)生; create table 學(xué)費標(biāo)準(zhǔn)表 as select * from a_db.學(xué)費標(biāo)準(zhǔn)表; create table 收費表 as select * from a_db.收費表; create table 收費明細(xì)表 as select * from a_db.收費明細(xì)表;

8、在學(xué)生表中增加一列，用來記錄學(xué)生的密碼，寫一個PL/SQL程序，模擬登錄的過程。輸入學(xué)號和密碼，判斷是否正確，對于登錄成功和失敗分別給出提示信息。(存儲過程) 增加密碼列 alter table 學(xué)生 add 密碼 varchar2(12); 創(chuàng)建了學(xué)生表的密碼列，設(shè)置密碼為666 update 學(xué)生 set 密碼 = ‘666’,

create or replace procedure obj2_8(v_學(xué)號學(xué)生.學(xué)號%type,v_密碼學(xué)生.密碼%type) as vv_學(xué)號學(xué)生.學(xué)號%type; vv_密碼學(xué)生.密碼%type; begin select 學(xué)號,密碼 into vv_學(xué)號,vv_密碼 from 學(xué)生 where v_學(xué)號=學(xué)號 and v_密碼=密碼; dbms_output.put_line('登錄成功!'); exception when no_data_found then dbms_output.put_line('登陸失敗'); end;

9、編寫一個向?qū)W費標(biāo)準(zhǔn)表添加記錄的過程。

create or replace procedure obj2_9 ( v_專業(yè) 學(xué)費標(biāo)準(zhǔn)表.專業(yè)%type, v_學(xué)年學(xué)費標(biāo)準(zhǔn)表.學(xué)年%type, v_學(xué)費學(xué)費標(biāo)準(zhǔn)表.學(xué)費%type) as begin insert into 學(xué)費標(biāo)準(zhǔn)表(專業(yè), 學(xué)年, 學(xué)費) values (v_專業(yè), v_學(xué)年, v_學(xué)費); dbms_output.put_line(v_專業(yè)||','||v_學(xué)年||','||v_學(xué)費||'添加完成'); end; . / exec obj2_9('計算機(jī)科學(xué)與技術(shù)','2017','13000');

10、編寫一個學(xué)生注冊的過程，注冊日期默認(rèn)為當(dāng)天，以學(xué)號為參數(shù)。

create or replace procedure obj2_10(v學(xué)號學(xué)生.學(xué)號%type) as begin insert into 學(xué)生(學(xué)號,注冊日期) values (v學(xué)號,sysdate); update 學(xué)生 set 狀態(tài)='注冊' where 學(xué)號=v學(xué)號 and 狀態(tài)='null'; dbms_output.put_line(v學(xué)號||'學(xué)生已在'|| to_char (sysdate,'yyyy-mm-dd')||'注冊成功'); end; . /

11、編寫一個收學(xué)費的過程，收費日期默認(rèn)為當(dāng)天，以學(xué)年、學(xué)號、學(xué)費為參數(shù)。

create or replace procedure obj2_11( v_學(xué)年收費明細(xì)表.學(xué)年%type, v_學(xué)號收費明細(xì)表.學(xué)號%type, v_學(xué)費收費明細(xì)表.學(xué)費%type) as begin insert into 收費明細(xì)表(學(xué)年,學(xué)號,學(xué)費) values (v_學(xué)年, v_學(xué)號, v_學(xué)費); dbms_output.put_line('成功添加學(xué)生'||v_學(xué)號||'在'|| v_學(xué)年||'學(xué)年的付費記錄,共付款:'|| v_學(xué)費||'元'); end; . / exec obj2_11('2019','S101',1300);

12、編寫一個過程，輸出指定學(xué)年的欠費情況（含欠費人數(shù)、欠費總金額）。

create or replace procedure obj2_12( v_學(xué)年收費表.學(xué)年%type) as total number(5); money number(10); begin select count(學(xué)號),sum(應(yīng)交學(xué)費-已交學(xué)費) a into total,money from 收費表 where 學(xué)年=v_學(xué)年 and 應(yīng)交學(xué)費>已交學(xué)費; dbms_output.put_line('欠費人數(shù)為:'||total||' 欠費金額為:'||money); end; . / exec obj2_12('2016'); select 學(xué)號，應(yīng)交學(xué)費-已交學(xué)費 from 收費表 where 學(xué)年=2016 and 已交學(xué)費< 應(yīng)交學(xué)費;

13、輸出如下九九乘法表。(存儲過程)

create or replace procedure obj2_13 as i int; j int; begin for i in 1..9 loop for j in 1..i loop dbms_output.put(i||'*'||j||'='||(i*j)||' '); end loop; dbms_output.put_line(' '); end loop; end; . / exec obj2_13 三．實驗錯誤解決方案問題一：創(chuàng)建的過程帶有編譯錯誤問題

1、問題的出現(xiàn) 在實驗項目2中創(chuàng)建“傳送參數(shù)部門編號，輸出該部門名和地理位置。(存儲過程)”，SQL語句如下：

create or replace procedure obj2_2 (vdeptno in DEPT.dept%type) as vdname dept.dname%type; vloc dept.loc%type; begin select dname,loc into vdname,vloc from dept where deptno= vdeptno; dbms_output.put_line(dname ||','|| loc); end;

出現(xiàn)錯誤：警告：創(chuàng)建的過程帶有編譯錯誤

2、問題分析

錯誤出現(xiàn)在第3行，表明“%type”定義數(shù)據(jù)類型有問題。使用由%TYPE定義的變量時要用“.”運(yùn)算符指定表名限定詞，而且不用加“in”。但在第3行中由于C語言的語法干擾加了“in”，所以出錯。

3、解決方案解決方案如下：

create or replace procedure obj2_2(z_deptno dept.deptno%type) as z_dname dept.dname%type; z_loc dept.loc%type; begin select dname,loc into z_dname,z_loc from dept where deptno=z_deptno; dbms_output.put_line(z_dname||' '||z_loc); end; . / exec obj2_2(30)

執(zhí)行該語句后，表已創(chuàng)建，問題解決。

【本文地址】

公司簡介

聯(lián)系我們

今日新聞

推薦新聞

專題文章