Oracle實(shí)驗(yàn)5：PL_SQL復(fù)合數(shù)據(jù)類型：記錄、表和可變數(shù)組

1、數(shù)據(jù)庫開發(fā)技術(shù)實(shí)驗(yàn)5 報(bào)告實(shí)驗(yàn)題目：PL/SQL復(fù)合數(shù)據(jù)類型：記錄、表和可變數(shù)組日期2015 11 - 28班級(jí)計(jì)算機(jī)1301姓名實(shí)驗(yàn)環(huán)境：Win10 +Oracle11g一、實(shí)驗(yàn)內(nèi)容與完成情況(記錄所有的實(shí)驗(yàn)過程)：1、根據(jù)表emp的全部字段定義記錄變量emp_record。用SELECT語句將編號(hào)為7788的雇員的全部字段對(duì)應(yīng)地存入該記錄變量，最后輸出記錄變量的雇員名稱字段emp_record.ename和雇員工資字段的內(nèi)容。執(zhí)行結(jié)果如下：SCOTT的工資為：3000PL/SQL 過程已成功完成。PL/SQL 過程已成功完成。SQL語句set SERVEROUTPUT ONDECLARET

2、YPE emp_record is RECORD( empno EMP.EMPNO%TYPE, ename EMP.ENAME%TYPE, Job EMP.JOB%TYPE, mgr EMP.MGR%TYPE, hiredate EMP.HIREDATE%TYPE, sal EMP.SAL%TYPE, comm number(10,2):=0, deptno number(3):=10);e_record emp_record;beginselect * into e_recordfrom emp where empno=7788;dbms_output.put_line(e_record.e

3、name|'的工資為：'|to_char(e_record.sal); end;/實(shí)驗(yàn)結(jié)果截圖2、聲明一張Index_By（關(guān)聯(lián)數(shù)組）表，用來接收并存儲(chǔ)DEPT表的信息，把部門號(hào)作為鍵，不要忘記部門號(hào)是10的倍數(shù)。使用循環(huán)從DEPT表中將所有部門信息檢索到PL/SQL記錄表中，然后用另一個(gè)循環(huán)來顯示表中的這些信息。執(zhí)行結(jié)果為：10, ACCOUNTING, NEW YORK20, RESEARCH, DALLAS30, SALES, CHICAGO40, OPERATIONS, BOSTONPL/SQL 過程已成功完成。SQL語句SET SERVEROUTPUT ONDECLA

4、REtype dept_type is table OF dept%rowtypeindex by binary_integer;v_count number;dept_list dept_type;begin select COUNT(*) into v_count FROM dept; for i IN 1.v_count LOOP select * into dept_list(i*10) from dept where deptno=i*10; END LOOP; FOR i IN 1.v_count LOOP DBMS_OUTPUT.PUT_LINE(dept_list(i*10).

5、deptno|','|dept_list(i*10).dname|','|dept_list(i*10).loc); END LOOP;end;/實(shí)驗(yàn)結(jié)果截圖 3、*閱讀以下程序，找出出錯(cuò)之處，說明出錯(cuò)原因，預(yù)測(cè)運(yùn)行輸出結(jié)果是什么。請(qǐng)刪改錯(cuò)誤，加上適當(dāng)注釋后，運(yùn)行該程序，驗(yàn)證自己的預(yù)測(cè)是否正確。SET SERVEROUTPUT ONDECLARE TYPE dept_list IS TABLE OF dept.dname%TYPE; -定義嵌套表 TYPE top5_list IS VARRAY(5) OF dept.loc%TYPE; -定義可變數(shù)組 dis_

6、dept dept_list; -嵌套表的聲明 num_5 top5_list; -可變數(shù)組的聲明BEGIN dis_dept(1):='AMGN' IF dis_dept IS NULL THEN dis_dept :=dept_list('AMGN','BGEN'); DBMS_OUTPUT.PUT_LINE('dis_dept表當(dāng)前元素個(gè)數(shù)為：'|to_char(dis_dept.count); END IF; num_5:=top5_list('ORCL','CSCO',NULL,NULL)

7、; IF num_5(3) IS NULL THEN num_5(3):='CPQ' END IF; num_5(4):='DELL' FOR COUNTER IN 1.4 LOOP DBMS_OUTPUT.PUT_LINE(NUM_5(COUNTER); END LOOP;END;/執(zhí)行結(jié)果為：SQL語句SET SERVEROUTPUT ONDECLARE TYPE dept_list IS TABLE OF dept.dname%TYPE; -定義嵌套表 TYPE top5_list IS VARRAY(5) OF dept.loc%TYPE; -定義可變數(shù)

8、組 dis_dept dept_list; -嵌套表的聲明 num_5 top5_list; -可變數(shù)組的聲明BEGIN /*修改：在此處給dis_dept初始化，否則無法使用*/ IF dis_dept IS NULL THEN dis_dept :=dept_list('AMGN','BGEN'); DBMS_OUTPUT.PUT_LINE('dis_dept表當(dāng)前元素個(gè)數(shù)為：'|to_char(dis_dept.count); END IF; num_5:=top5_list('ORCL','CSCO',NU

9、LL,NULL); IF num_5(3) IS NULL THEN num_5(3):='CPQ' END IF; num_5(4):='DELL' FOR COUNTER IN 1.4 LOOP DBMS_OUTPUT.PUT_LINE(NUM_5(COUNTER); END LOOP;END;實(shí)驗(yàn)結(jié)果截圖4、將上題改為使用INDEX BY BINARY_INTEGER的索引表，則實(shí)現(xiàn)同一執(zhí)行結(jié)果，程序要做哪些相應(yīng)的改變。以下是與上題對(duì)應(yīng)的另一個(gè)相關(guān)的例子：SET SERVEROUTPUT ONDECLARE TYPE dept_list IS TABLE

10、OF dept.dname%TYPE index by binary_integer; -定義索引組織表 TYPE top5_list IS VARRAY(5) OF dept.loc%TYPE; -定義可變數(shù)組 dis_dept dept_list; -索引表的聲明 num_5 top5_list; -可變數(shù)組的聲明 v_count number(3); BEGIN dis_dept(1):='AMGN' -為表元素賦值 IF not dis_dept.exists(2) THEN dis_dept(2) :='BGEN' DBMS_OUTPUT.PUT_LI

11、NE('dis_dept表當(dāng)前元素個(gè)數(shù)為：'|to_char(dis_dept.count); - dis_dept.extend(2); -索引表無須使用extend(n)增加元素，使用了反而出錯(cuò) dis_dept(3):='EXTEND_ELEMENT' v_count:=dis_dept.count; DBMS_OUTPUT.PUT_LINE('dis_dept表增添元素后的個(gè)數(shù)為：'|to_char(dis_dept.count); FOR i IN 1. v_count loop DBMS_OUTPUT.PUT_LINE(dis_dep

12、t(i); END LOOP; END IF; num_5:=top5_list('ORCL','CSCO',NULL,NULL); IF num_5(3) IS NULL THEN num_5(3):='CPQ' END IF; num_5(4):='DELL' FOR COUNTER IN 1.4 LOOP DBMS_OUTPUT.PUT_LINE(NUM_5(COUNTER); END LOOP;END;實(shí)驗(yàn)結(jié)果截圖5、使用%ROWTYPE屬性,基于游標(biāo)student_cur定義記錄。顯示'CS'系學(xué)生的序號(hào)

13、、姓名和年齡等信息。(參見教材P255例11.3)執(zhí)行結(jié)果為：序號(hào) 學(xué)生姓名 年齡1 李明勇 202 張立 19SQL語句SET SERVEROUTPUT ONdeclareCURSOR student_cur IS SELECT sno, sname,sage FROM student WHERE sdept= 'CS'stu_record student_cur%rowtype;beginDBMS_OUTPUT.PUT_LINE('序號(hào) 姓名 年齡 '); FOR stu_record IN student_cur LOOP DBMS_OUTPUT.PUT_

14、LINE(stu_record.sno|' '| stu_record.sname|' '|stu_record.sage); END LOOP;end;實(shí)驗(yàn)結(jié)果截圖6.閱讀以下程序，并寫出程序執(zhí)行結(jié)果。CREATE TYPE sname_type IS TABLE OF VARCHAR2(10);CREATE TABLE Mentors ( mentor_id NUMBER(5) CONSTRAINT mentor_pk PRIMARY KEY, mentor_name VARCHAR2(10) NOT NULL, student_name sname_typ

15、e)NESTED TABLE student_name STORE AS sname_table;INSERT INTO mentors VALUES(10101,'王彤',sname_type('王曉芳','張純玉','劉春蘋','王曉芳');INSERT INTO mentors VALUES(10104,'孔世杰',sname_type('王天儀','韓劉','劉春蘋');SET SERVEROUT ON DECLARE sname_table1

16、 sname_type; sname_table2 sname_type; sname_table sname_type; BEGIN SELECT student_name INTO sname_table1 FROM mentors WHERE mentor_name = '王彤' DBMS_OUTPUT.PUT_LINE ('集合sname_table1中的元素-'); FOR i IN 1.sname_table1.COUNT LOOP DBMS_OUTPUT.PUT_LINE ('學(xué)生姓名：'|sname_table1(i); END

17、LOOP; SELECT student_name INTO sname_table2 FROM mentors WHERE mentor_name = '孔世杰' DBMS_OUTPUT.PUT_LINE ('集合sname_table2中的元素-'); FOR i IN 1.sname_table2.COUNT LOOP DBMS_OUTPUT.PUT_LINE ('學(xué)生姓名：'|sname_table2(i); END LOOP; sname_table := sname_table1 MULTISET EXCEPT sname_table

18、2; sname_table := SET(sname_table); DBMS_OUTPUT.PUT_LINE ('集合sname_table中的元素-'); FOR i IN 1.sname_table.COUNT LOOP DBMS_OUTPUT.PUT_LINE ('學(xué)生姓名：'|sname_table(i); END LOOP;END;SQL語句CREATE TYPE sname_type IS TABLE OF VARCHAR2(10);CREATE TABLE Mentors ( mentor_id NUMBER(5) CONSTRAINT men

19、tor_pk PRIMARY KEY, mentor_name VARCHAR2(10) NOT NULL, student_name sname_type)NESTED TABLE student_name STORE AS sname_table;INSERT INTO mentors VALUES(10101,'王彤',sname_type('王曉芳','張純玉','劉春蘋','王曉芳');INSERT INTO mentors VALUES(10104,'孔世杰',sname_type(&#

20、39;王天儀','韓劉','劉春蘋');SET SERVEROUT ON DECLARE sname_table1 sname_type; sname_table2 sname_type; sname_table sname_type; BEGIN SELECT student_name INTO sname_table1 FROM mentors WHERE mentor_name = '王彤' DBMS_OUTPUT.PUT_LINE ('集合sname_table1中的元素-'); FOR i IN 1.sname_

21、table1.COUNT LOOP DBMS_OUTPUT.PUT_LINE ('學(xué)生姓名：'|sname_table1(i); END LOOP; SELECT student_name INTO sname_table2 FROM mentors WHERE mentor_name = '孔世杰' DBMS_OUTPUT.PUT_LINE ('集合sname_table2中的元素-'); FOR i IN 1.sname_table2.COUNT LOOP DBMS_OUTPUT.PUT_LINE ('學(xué)生姓名：'|sname

22、_table2(i); END LOOP; sname_table := sname_table1 MULTISET EXCEPT sname_table2; sname_table := SET(sname_table); DBMS_OUTPUT.PUT_LINE ('集合sname_table中的元素-'); FOR i IN 1.sname_table.COUNT LOOP DBMS_OUTPUT.PUT_LINE ('學(xué)生姓名：'|sname_table(i); END LOOP;END;-預(yù)測(cè)結(jié)果-集合sname_table1中的元素-學(xué)生姓名：王曉芳

23、-學(xué)生姓名：張純玉-學(xué)生姓名：劉春蘋-學(xué)生姓名：王曉芳-集合sname_table2中的元素-學(xué)生姓名：王天儀-學(xué)生姓名：韓劉-學(xué)生姓名：劉春蘋-集合sname_table中的元素-學(xué)生姓名：王曉芳-學(xué)生姓名：張純玉-學(xué)生姓名：王曉芳-1.首先要先創(chuàng)建數(shù)組create or replace type t_ret_table is table of varchar2(50);-2.利用自定義函數(shù)實(shí)現(xiàn)一，利用函數(shù)返回?cái)?shù)組create or replace function f_split_string(var_str in string,var_split In String) return t_

24、ret_table isvar_out t_ret_table;var_tmp varchar2(4000);var_element varchar2(4000);beginvar_tmp := var_str;var_out := t_ret_table();-如果存在匹配的分割符while instr(var_tmp,var_split)>0 loopvar_element := substr(var_tmp,1,instr(var_tmp,var_split)-1);var_tmp := substr(var_tmp,instr(var_tmp,var_split)+length(

25、var_split),length(var_tmp);var_out.extend(1);var_out(var_out.count) := var_element;end loop;var_out.extend(1);var_out(var_out.count) := var_tmp;return var_out;end f_split_string;-select * from table(f_split_string('湖南|湖北|北京','|');create TYPE loc_type IS TABLE OF VARCHAR2(50);SET SERV

26、EROUTPUT ON DECLARE loc loc_type; str VARCHAR2(4000); BEGIN str:='&str' select * from table(f_split_string(str,'|') t; FOR i IN 1.t.count LOOP DBMS_OUTPUT.PUT_LINE('省份：'|loc(i); END LOOP;END;/實(shí)驗(yàn)結(jié)果截圖 *附加題：*分割省份信息放入嵌套表結(jié)構(gòu)中。用戶輸入：湖南|河南|江西，按”|”分割省份。1) 接受用戶輸入2) 分割字符串，把省份分割出放入嵌套表結(jié)構(gòu)中3) 遍歷輸出嵌套表提示：str := '&STR' - str:= '湖南|河南|江西'/運(yùn)行結(jié)果形如下： SQL語句create TYPE loc_type IS TABLE OF VARCHAR2(50);SET SE