实验4SQL_PL编程基础_V2013

1、实验4 PL/SQL编程基础姓名：计算机科学与技术学号：1107022126专业：11网络工程班级：同组人：苏靖实验日期：2013/10/31【实验目的与要求】n 掌握PL/SQL基本语法n 掌握PL/SQL简单存过程的编写【实验内容与步骤】4.0. 实验准备工作1. 测试用表的创建与数据添加(1)创建测试表Create Table TESTTABLE( RECORDNUMBER number(4) Not Null, CURRENTDATE Date Not Null ) Tablespace USER ;-这里的表空间其实可以不去管，这样它就会在当前用户的表空间中创建一个表 (2)使用fo

2、r语句在测试表中加入测试数据 Declare maxrecords Constant Int:=20;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(记录已经按照计划全部插入

3、，请查看！); End;/ 注：hqf为登录用户名，需根据情况改动，如：实验中一般用scott. (3)查询表中数据，给出查询结果截图： 4.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

4、,某某，某某从客户端得到*/declare v_name varchar2(20);begin -块开始 v_name:=& v_name ; -与“客户端”交互，类似C语言的scanf语句 DBMS_OUTPUT.put_line(测试结果为：Hello,|v_name); end; -块结束运行结果为：4.2. 简单变量的使用1变量的声明与引用set serveroutput on;- PL/SQL变量之 简单类型declare v_dept_id number(5) := 1111; v_age binary_integer := 12; v_dept_name varchar2(20)

5、 := 人事部; 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子句赋值的使用：declare v_deptno number(2); v_loc varchar2(15); begin select deptno,loc into v_deptno,v_loc from

6、 dept where dname=SALES; DBMS_OUTPUT.PUT_LINE (V_deptno | and |v_loc); end;运行结果为：实验练习：编写一PL/SQL程序，实现依次从客户端接收各字段的值，并放于变量中，输完一条记录的所有字段值后，将值写到数据库表Emp中。请给出程序源码：给出测试结果：3变量作用域：阅读以下程序，熟悉变量的斌值方法，理解变量的作用，给出运行结果。-嵌套块中的变量赋值和作用域DECLARE v_weight NUMBER(3) := 100; v_message VARCHAR2(255) := Outer Value;BEGIN DECL

7、ARE 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_

8、weight) | v_message);END;运行结果为：4.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变量之%TYP

9、E类型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）declare v_deptno dept.deptno%type; v_loc dept.loc%type; begin select deptno,loc into v_deptno,v_loc from dept where d

10、name=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 serveroutpu

11、t 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_

12、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;-记录间可以相互赋值

13、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

14、 table stock;create table stock(symbol varchar2(50),price number(6,2),owner varchar2(10);对以上程序运行有影响吗？为什么？4.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 numb

15、er(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_emp

16、no 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 (

17、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

18、 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;运行结果为：4.5. 复合变量的使用1. 完整定义一个记录类型的变量，了解其简单应用。阅读以下程序，理解其功能，给出运行结果。 Declare Type myrecord Is Record( r_recordnumber Number(4)

19、, r_currentdate Date ); v_myrecord myrecord; Begin Select * Into v_myrecord From testtable Where recordnumber=8; dbms_output.put_line(用记录类型的变量取出来的值为：| v_myrecord.r_recordnumber| v_myrecord.r_currentdate); End;给出运行结果：2. 简单的%Rowtype定义变量的实例 阅读以下程序，理解其功能，给出运行结果。Declare v_myrow testtable%Rowtype; Begin S

20、elect * Into v_myrow From testtable Where recordnumber=90; 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; 给出运行结果：4.6. 条件判断语句的使用阅读并理解

21、以下程序，调试并给出运行结果。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.

22、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_

23、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

24、:= 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;给出运行结果：4.8. 游标的使用:1隐式游标：（0）创建测试表和加入测试数据drop table emp_E

25、xamloyees;create table emp_Examloyees(EMP_EXAMLOYEE_ID NUMBER(6) primary key,FIRST_NAME VARCHAR2(20),LAST_NAME VARCHAR2(25),EMAIL VARCHAR2(25),PHONE_NUMBER VARCHAR2(20),HIRE_DATE DATE,JOB_ID VARCHAR2(10),SALARY NUMBER(8,2),COMMISSION_PCT NUMBER(2,2),DEPARTMENT_ID NUMBER(4) );insert into emp_Examloye

26、es(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(1,JIA,JENNY,3000,1);insert into emp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(2,guan,rose,4000,3);insert into emp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(3,

27、xu,mike,2000,2);insert into emp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(4,zhang,billy,1000,2);insert into emp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(5,jin1,dede,5000,1);insert into emp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME

28、,LAST_NAME,SALARY,DEPARTMENT_ID) values(6,jin2,dede,5000,2);insert into emp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(7,jin3,dede,5000,1);insert into emp_Examloyees(EMP_EXAMLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(8,jin4,dede,5000,3);COMMIT;（1） 使

29、用隐式游标：以下程序段以sql%rowcount为例展示的是隐式游标的使用方法，请阅读并理解程序，给出测试结果。set serveroutput on;declare v_delete_count number(3);begin delete from emp_Examloyees e where e.DEPARTMENT_ID = 1; v_delete_count := sql%rowcount; commit; dbms_output.put_line(总共删除数据: | v_delete_count | 条 );end;/给出运行结果：2显式游标：（1）用显式游标：-此处没有用循环，只

30、能取出由标指针指向的第一条纪录drop table emp_Exam;create table emp_Exam( id number(3), name varchar2(20), sal number(8,2);insert into emp_Exam values(1,a,3000);insert into emp_Exam values(2,b,3000);insert into emp_Exam values(3,c,3000);insert into emp_Exam values(4,d,3000);commit;set serveroutput on;declare cursor

31、 c is select * from emp_Exam; v_all_emp_Examl emp_Exam%rowtype;begin open c; fetch c into v_all_emp_Examl; dbms_output.put_line(v_all_emp_Examl.id | - | v_all_emp_E |- | v_all_emp_Examl.sal); close c;end; /（2）用显式游标循环取值：-显示游标循环取值：declare cursor c is select * from emp_Exam; v_all_emp_Examl em

32、p_Exam%rowtype;begin open c; loop fetch c into v_all_emp_Examl; exit when c%notfound; dbms_output.put_line(v_all_emp_Examl.id | - | v_all_emp_E |- | v_all_emp_Examl.sal); end loop; close c;end;/给出运行结果：3游标功能使用综合：-游标的功能展示，这里展示了%isopen,%found,%Declare temp_Examsal scott.emp_Exam.sal%Type; Curs

33、or mycursor Is Select * From scott.emp_Exam e Where e.sal=temp_Examsal; currentrecord mycursor%Rowtype; Begin temp_Examsal:=3000; temp_Examsal:=800; -打开游标 If mycursor%Isopen Then dbms_output.put_line(该游标已经打开了，正在关闭！); Close mycursor; Open mycursor; Else dbms_output.put_line(游标关闭，正在打开！); Open mycursor

34、; End If; -读取数据 Fetch mycursor Into currentrecord; If mycursor%Found Then dbms_output.put_line(游标已经取到数据,查询结果是：); dbms_output.put_line(to_char(currentrecord.ename); Else dbms_output.put_line(没有要查询的结果！); End If; -读取记录总条数 Loop Fetch mycursor Into currentrecord; Exit When mycursor%Notfound; End Loop; db

35、ms_output.put_line(查结果总共有：|mycursor%Rowcount); -关闭游标 If mycursor%Isopen Then dbms_output.put_line(正在关闭游标，程序正常结束！); Close mycursor; End If; End;给出运行结果：4.9. 异常处理:阅读并理解以下程序，掌握异常处理的一般形式，给出运行结果。0准备测试表和数据：drop table sm_emp_Exam;CREATE TABLE sm_emp_Exam( no char(4), name char(10), salary number(6,2), phone

36、 char(8);-insert TOMINSERT INTO sm_emp_Exam VALUES (001,TOM,999.99,62543678);commit;-insert TOM againINSERT INTO sm_emp_Exam VALUES (002,TOM,999.99,62543678);commit;DELETE FROM sm_emp_Exam WHERE name=TOM;commit;1异常处理示例：SET SERVEROUTPUT ON-named exception testDECLARE v_name VARCHAR2(10); n_sal NUMBER

37、(8,2);BEGIN SELECT name , salary INTO v_name , n_sal FROM sm_emp_Exam WHERE name=TOM; DBMS_OUTPUT.PUT_LINE(TOM : SALARY : | n_sal);EXCEPTION -注意：当多个exception在exception段中出现，只执行一个，之后不直接跳到end。 WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(NO EMP_EXAMLOYEE NAMED TOM); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.P

38、UT_LINE(TOO MANY TOM); WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(ERROR!);END;给出运行结果：第 23 页 共 32 页4.10. 综合练习以下实验中用到的Exam_EMP表结构与内容与Scott模式下的表Emp相同，请用Create As命令创建并导入数据。而后，参照前面实验，完成以下实验练习。1 计算Exam_EMP表中COMM最高与最低的差值，COMM值为空时按0计算。写出程序源码：set serveroutput on; declare max_comm number(7,2); min_comm number(7,2);

39、 d_comm number(7,2); begin select max(comm),min(comm) into max_comm,min_comm from EMP;d_comm:=max_comm-min_comm; dbms_output.put_line(comm的差值为：|d_comm); end; /给出运行测试结果：2 根据表Exam_EMP中deptno字段的值，为姓名为JONES的雇员修改工资；若部门号为10，则工资加100；部门号为20，加200；其他部门加400。写出程序源码：set serveroutput on; declare v_deptno number(2

40、); v_sal number(7,2); begin select deptno,sal into v_deptno,v_sal from emp where ename=JONES; if v_deptno=10 then update emp set sal=v_sal+100 where ename=JONES; elsif v_deptno=20 then update emp set sal=v_sal+200 where ename=JONES; else update emp set sal=v_sal+400 where ename=JONES; end if; commit; end; /给出运行测试结果