实验7 SQL_PL编程基础.doc

实验7 PL/SQL编程基础(I)姓名：学号：专业：班级：同组人：实验日期：【实验目的与要求】n 掌握PL/SQL基本语法n 掌握PL/SQL简单存过程的编写【实验内容与步骤】7.0. 实验准备工作1. 测试用表的创建与数据添加(1)创建测试表Create Table TESTTABLE( RECORDNUMBER number(4) Not Null, CURRENTDATE Date Not Null )Tablespace USER ;-这里的表空间其实可以不去管，这样它就会在当前用户的表空间中创建一个表(2)使用for语句在测试表中加入测试数据Declare maxrecords Constant Int:=100;i Int:=1; Begin For i In 1.maxrecords Loop Insert Into hqf.testtable(recordnumber,currentdate) -hqf为模式名，应改动 Values(i,Sysdate); dbms_output.put_line(现在输入的内容是：|i| |Sysdate); Commit;-这里切记要commit否则将不会将数据提交到表中 End Loop; dbms_output.put_line(记录已经按照计划全部插入，请查看！); End;/ 注：hqf为登录用户名，需根据情况改动，如：实验中一般用scott.(3)查询表中数据，给出查询结果截图：select * from testtable;2. 导入教材实验数据(1)创建用户school,并赋于相应的权限：CREATE USER venditionidentified by vendition;GRANT CREATE SESSION,RESOURCE,DBATO vendition;CREATE USER SCHOOLidentified by SCHOOL;GRANT CREATE SESSION,RESOURCE,DBATO SCHOOL;(2)将文件夹“TestTableBak”中的备份文件school.DMP和vendition.DMP复制到某一目录下(例如E盘根目)。(3)然后启动CMD命令提示窗口，运行如下的还原命令。imp userid=school/school ignore=y fromuser=school touser=school file=e:school.DMPimp userid=vendition/vendition ignore=y fromuser=vendition touser=vendition file=e:vendition.DMP(4)验证确认。以用户名School登录系统，查看相应的表，看是否有数据。7.1. 最简单的PL/SQL程序1. 输出Hello,World/* 第一个例子:输出Hello,World*/set serverout on -设置SQL*Plus将服务器所返回的写出来begin -块开始 DBMS_OUTPUT.put_line(Hello,World); -在控制台输出信息，类似C语言的Printf或者java语言中的System.out.printend; -块结束运行结果为：1. 接收数据并输出/* 在此基础上，完成Hello,某某，某某从客户端得到*/declare v_name varchar2(20);begin -块开始 v_name:=&姓名; -与“客户端”交互，类似C语言的scanf语句 DBMS_OUTPUT.put_line(Hello,|v_name); end; -块结束运行结果为：7.2. 简单变量的使用1变量的声明与引用set serveroutput on;- PL/SQL变量之 简单类型declare v_dept_id number(5) := 1111; v_age binary_integer := 12; v_dept_name varchar2(20) := 人事部; v_rate constant number(4,2) := 22.12; v_valid boolean not null := TRUE; v_hire_date date not null := sysdate + 7;begin v_dept_id := 2222; dbms_output.put_line(v_dept_id); end;/运行结果为：2Into子句的使用：declarev_deptno number(2);v_loc varchar2(15);begin select deptno,locinto v_deptno,v_locfrom deptwhere dname=SALES;DBMS_OUTPUT.PUT_LINE (V_deptno | and |v_loc);end;运行结果为：实验练习：编写一PL/SQL程序，实现依次从客户端接收各字段的值，并放于变量中，输完一条记录的所有字段值后，将值写到数据库表Emp中。请给出程序源码：declareE_EMPNO NUMBER(4); E_ENAME VARCHAR2(10) ; E_JOB VARCHAR2(9) ;E_MGR NUMBER(4) ; E_HIREDATE DATE ;E_SAL NUMBER(7,2) ;E_COMM NUMBER(7,2) ;E_DEPTNO NUMBER(2) ;beginE_EMPNO:=&号码; E_ENAME:=&姓名; E_JOB:=&工作; E_MGR:=&经理; E_HIREDATE:=&雇用时间; E_SAL:=&销售; E_COMM:=&COMM; E_DEPTNO:=&部门number; insert into empvalues (E_EMPNO,E_ENAME,E_JOB,E_MGR,E_HIREDATE,E_SAL,E_COMM,E_DEPTNO);end;给出测试结果：3变量作用域：阅读以下程序，熟悉变量的斌值方法，理解变量的作用，给出运行结果。-嵌套块中的变量赋值和作用域DECLARE v_weight NUMBER(3) := 100; v_message VARCHAR2(255) := Outer Value;BEGIN DECLARE v_weight NUMBER(3) := 1; v_message VARCHAR2(255) :=Inner value; BEGINv_weight := v_weight + 1;v_message := Put| v_message;DBMS_OUTPUT.PUT_LINE(inner block | to_char(v_weight) | v_message); END; v_weight := v_weight + 1; v_message := Put | v_message; DBMS_OUTPUT.PUT_LINE(outter block | to_char(v_weight) | v_message);END;运行结果为：7.3. 使用%type和%rowtype变量1使用%type变量阅读以下程序，理解其功能，给出运行结果。（1）DROP TABLE EMPL;create table EMPL( EMPLOYEE_ID NUMBER(5) not null, LAST_NAME VARCHAR2(20) not null, HIRE_DATE DATE, SALARY NUMBER(8,2), JOB_ID NUMBER(5), DEPARTMENT_ID NUMBER(5);set serveroutput on;- PL/SQL变量之%TYPE类型declare v_last_name emp.last_name%TYPE; v_hire_date emp.hire_date%TYPE; v_balance number(6,2) ; v_min_balance v_balance%type;begin dbms_output.put_line(定义%type类型的变量);end;/运行结果为：（2）declarev_deptno dept.deptno%type;v_loc dept.loc%type;beginselect deptno,locinto v_deptno,v_locfrom deptwhere dname=SALES;DBMS_OUTPUT.PUT_LINE (V_deptno | and |v_loc);end;/运行结果为：2使用%Rowtype变量阅读以下程序，理解其功能，给出运行结果。（1）DROP TABLE EMPL;create table EMPL( EMPLOYEE_ID NUMBER(5) not null, LAST_NAME VARCHAR2(20) not null, HIRE_DATE DATE, SALARY NUMBER(8,2), JOB_ID NUMBER(5), DEPARTMENT_ID NUMBER(5);set serveroutput on;- PL/SQL变量之%ROWTYPE类型declare v_empl emp%rowtype;begin dbms_output.put_line(定义%rowtype类型的变量);end;/运行结果为：（2）drop table stock;create table stock(symbol varchar2(50),price number(6,2);insert into stock values(IBM,188.88);insert into stock values(ORCL,100.88);DECLAREstock_info1 stock%ROWTYPE;stock_info2 stock%ROWTYPE;stock_info3 stock%ROWTYPE;BEGIN-对应纪录直接取得相应字段的：Populate the specific fields in a recordSELECT symbol,priceINTO stock_info1.symbol,stock_info1.priceFROM stockWHERE symbol =ORCL;-对应纪录直接 放入纪录中SELECT * INTO stock_info2 FROM stockWHERE symbol =ORCL;stock_info3 := stock_info2;-记录间可以相互赋值stock_info3.symbol:=ORACLE;-对纪录的各字段赋值。DBMS_OUTPUT.PUT_LINE(stock_info1 : | stock_info1.symbol | |stock_info1.price);DBMS_OUTPUT.PUT_LINE(stock_info2 : | stock_info2.symbol | |stock_info2.price);DBMS_OUTPUT.PUT_LINE(stock_info3 : | stock_info3.symbol | |stock_info3.price);END;运行结果为：思考：如下所示：改变表结构。drop table stock;create table stock(symbol varchar2(50),price number(6,2),owner varchar2(10);对以上程序运行有影响吗？为什么？7.4. 使用复合变量1.记录类型阅读以下程序，理解其功能，给出运行结果。（1）set serveroutput on;- PL/SQL变量之RECORD类型1declare type named_record_type is record ( last_name varchar2(20), first_name varchar2(20), birthday date, age number(3) ); v_record_1 named_record_type; v_record_2 named_record_type;begin v_record_1.last_name := 张; v_record_1.first_name := 三丰; v_record_1.birthday := 2-2月-1788; v_record_1.age := 432; dbms_output.put_line(定义record类型的变量);end;/运行结果为：（2）-使用记录类型2：set serveroutput on;DECLARE TYPE remp IS RECORD( v_empno number(10), v_ename VARCHAR2 (20), v_salary NUMBER (10,2); rsm_emp remp;BEGINSELECT empno,ename,salINTO rsm_emp.v_empno,rsm_emp.v_ename,rsm_emp.v_salaryFROM empWHERE sal =(SELECT MAX(sal) FROM emp);-求出工资最高的雇员 DBMS_OUTPUT.PUT_LINE (Highest Paid Employee is |rsm_emp.v_ename); DBMS_OUTPUT.PUT_LINE (Id is |rsm_emp.v_empno |Salary | to_char(rsm_emp.v_salary,999,999.99);END;运行结果为：（3）-使用记录类型3：set serveroutput on;DECLARE TYPE remp IS RECORD( v_empno number(10), v_ename VARCHAR2 (20), v_salary NUMBER (10,2); rsm_emp remp;BEGINSELECT empno,ename,salINTO rsm_empFROM empWHERE sal =(SELECT MAX(sal) FROM emp);-求出工资最高的雇员 DBMS_OUTPUT.PUT_LINE (Highest Paid Employee is |rsm_emp.v_ename); DBMS_OUTPUT.PUT_LINE (Id is |rsm_emp.v_empno |Salary | to_char(rsm_emp.v_salary,999,999.99);END;运行结果为：2.记录表类型阅读以下程序，理解其功能，给出运行结果。-一维表类型(相当于基本数据类型元素组成的一维数组)declare type tabletype1 is table of varchar2(4) index by binary_integer; -type tabletype2 is table of employees.last_name%type index by binary_integer; table1 tabletype1; -table2 tabletype2; begin table1(1):=大学; table1(2):=大专; -table2(1):=88; -table2(2):=55; dbms_output.put_line(table1(1)|table1(2); -dbms_output.put_line(table1(2)|table2(2);end;/运行结果为：/*在定义好的表类型变量里，可以使用count、delete、first、last、next、exists和prior等属性进行操作，使用方法为“表变量名.属性”，返回的是数字。*/-使用表类型变量属性set serveroutput on;Declaretype tabletype1 is table of varchar2(9) index by binary_integer;table1 tabletype1;i binary_integer :=11;begintable1(1):=成都市;table1(5):=北京市;table1(3):=青岛市; table1(11):=青岛市;dbms_output.put_line(总记录数：|to_char(table1.count);dbms_output.put_line(第一条记录：|table1.first);dbms_output.put_line(最后条记录：|table1.last);dbms_output.put_line(第3条的前一条记录：|table1.prior(3);dbms_output.put_line(第5条的后一条记录：|table1.next(5);if(table1.exists(i) then dbms_output.put_line(该条记录值为： | table1(i);else dbms_output.put_line(该条记录不存在！);end if; table1.delete(11);-table1.delete(12); dbms_output.put_line(总记录数：|to_char(table1.count);dbms_output.put_line(第一条记录：|table1.first);dbms_output.put_line(最后条记录：|table1.last);dbms_output.put_line(第3条的前一条记录：|table1.prior(3);dbms_output.put_line(第5条的后一条记录：|table1.next(5);end;/运行结果为：7.5. 复合变量的使用1. 完整定义一个记录类型的变量，了解其简单应用。阅读以下程序，理解其功能，给出运行结果。Declare Type myrecord Is Record( r_recordnumber Number(4), r_currentdate Date ); v_myrecord myrecord; Begin Select * Into v_myrecord From testtable Where recordnumber=40; dbms_output.put_line(用记录类型的变量取出来的值为：| v_myrecord.r_recordnumber| v_myrecord.r_currentdate); End;给出运行结果：2. 简单的%Rowtype定义变量的实例阅读以下程序，理解其功能，给出运行结果。Declare v_myrow testtable%Rowtype; Begin Select * Into v_myrow From testtable Where recordnumber=50; dbms_output.put_line(用rowtype查询的结果是：|v_myrow.recordnumber|v_myrow.currentdate); -dbms_output.put_line(用rowtype查询的结果是：|v_myrow.recordnumber|v_myrow.currentdate|myrow.Rowid); -上句操作说明了行类型的变量中不会把oracle表中的rowid的值带进来 End;给出运行结果：3定义与使用一维表变量阅读以下程序，理解其功能，给出运行结果。-维表变量，这种变量看起来有些类似于C语言中的一维数组Declare Type mytbtype1 Is Table Of Varchar2(4) Index By Binary_Integer; Type mytbtype2 Is Table Of testtable.recordnumber%Type Index By Binary_Integer; tb1 mytbtype1; tb2 mytbtype2; Begin tb1(1):=大学; tb1(2):=大专; tb2(1):=90; tb2(2):=70; dbms_output.put_line(tb1(1)|tb2(1); dbms_output.put_line(tb1(2)|tb2(2); End; 给出运行结果：4定义与使用多维表变量阅读以下程序，理解其功能，给出运行结果。定义一个多维表变量，这就像一个二维数组，当然这个二维的数组的下标就有些区别于在编程语言中熟悉的二维数组，可以理解为一维存储的列名，而另一维则是存储与一维列名相对应的数据。(1) 多维表变量简单应用Declare -这里区别于一维表变量的定义 Type multbtype Is Table Of testtable%Rowtype Index By Binary_Integer; multb multbtype; Begin Select * Into multb(12) From testtable Where recordnumber=88; dbms_output.put_line(multb(12).recordnumber=| multb(12).recordnumber| multb(12).currentedate| multb(12).currentdate ); End;给出运行结果：(2) oracle中的数组与其他编程语言中的数组的区别：Declare Type mytabletype Is Table Of Varchar2(9) Index By Binary_Integer; tb mytabletype; Begin tb(1):=成都市; tb(2):=太原市; tb(3):=北京市; dbms_output.put_line(记录总数：|to_char(tb.Count); dbms_output.put_line(第一条记录为：|tb.First|其值为：|tb(tb.First); dbms_output.put_line(最后条记录为：|tb.Last|其值为：|tb(tb.Last); dbms_output.put_line(第二条的前一条记录为：|tb.Prior(2)|其值为：|tb(tb.Prior(2); dbms_output.put_line(第二条的后一条记录为：|tb.Next(2)|其值为：|tb(tb.Next(2); dbms_output.put_line(第二条记录为：|tb(2); tb.Delete(2); dbms_output.put_line(删除第二条记录后的第二条记录为:|tb(3); End;给出运行结果：7.6. 条件判断语句的使用阅读并理解以下程序，调试并给出运行结果。1If语句：drop table employee;create table employee( empno number(4), name varchar2(30), salary number(8,2);insert into employee values(7900,黄蓉,3000);commit;set serveroutput on;-执行结果如何呢？DECLARE -注意employee.salary%TYPE这一类型定义，定义v_SALARY为emp表的sal字段的类型 -这是plsql一种方便的定义类型方法。 v_SALARY employee.salary%TYPE; v_Comment VARCHAR2(35);BEGIN /* 返回employee的salary */ SELECT salary INTO v_SALARY FROM employee WHERE name = 黄蓉; -WHERE empno = 7900; dbms_output.put_line(薪水是: | v_SALARY); - 条件判断语句 IF v_SALARY 1000 THEN v_Comment := 比较低; ELSIF v_SALARY 3000 THEN v_Comment := A little higher：凑和; ELSIF v_SALARY 50 - 跳出循环. IF v_Counter 10 THEN EXIT; END IF; END LOOP;END;给出运行结果：2FOR LOOP语句-FOR LOOP语句BEGIN FOR v_Counter IN 1.10 LOOP dbms_output.put_line(v_Counter is | v_Counter); END LOOP; -DBMS_OUTPUT.PUT_LINE(v_Counter); -在for循环外不可以访问循环变量END;给出运行结果：3while LOOP语句-while LOOP语句 DECLARE v_Counter NUMBER := 1;BEGIN -v_Counter = 10-循环. WHILE v_Counter = 50 LOOP dbms_output.put_line(v_Counter is | v_Counter); v_Counter := v_Counter + 1; END LOOP;END;给出运行结果：4while语句-WHILE语句DECLARE v_Counter NUMBER := 1;BEGIN WHILE v_Counter 50 - 跳出循环. END LOOP;END;给出运行结果：7.8. 综合练习以下实验中用到的Exam_EMP表结构与内容与Scott模式下的表Emp相同，请用Create As命令创建并导入数据。而后，参照前面实验，完成以下实验练习。1 计算Exam_EMP表中COMM最高与最低的差值，COMM值为空时按0计算。写出程序源码：Create table Exam_EMP AS SELECT * FROM EMP declare var1 number; var2 number; val_comm number;begin select max(nvl(comm,0) into var1 from Exam_EMP; select min(nvl(comm,0) into var2 from Exam_EMP; val_comm:=var1-var2; dbms_output.put_line(val_comm);end;给出运行测试结果：2 根据表Exam_EMP中deptno字段的值，为姓名为JONES的雇员修改工资；若部门号为10，则工资加100；部门号为20，加200；其他部门加400。写出程序源码：declarec1 number;c2 number;beginselect deptno into c1 from Exam_EMP where ename=JONES;if c1=10 then c2:=100;elsif c1=20 thenc2:=200;else c2:=400;end if; update Exam_EMP set sal=sal+c2 where ename=J