大型数据库技术实验7.doc

实验7 PL/SQL编程基础姓名：林丹萍学号：0910012115专业：信息与计算科学班级：09信计1班同组人：无实验日期：2012-5-2【实验目的与要求】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)查询表中数据，给出查询结果截图： 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中。请给出程序源码：declare v_EMPNO NUMBER(4); v_ENAME VARCHAR2(10); v_JOB VARCHAR2(9); v_MGR NUMBER(4); v_HIREDATE DATE; v_SAL NUMBER(7,2); v_COMM NUMBER(7,2); v_DEPTNO NUMBER(2);begin v_EMPNO:=&EMPNO; v_ENAME:=&ENAME; v_JOB:=&JOB; v_MGR:=&MGR; v_HIREDATE:=&HIREDATE; v_SAL:=&SAL; v_COMM:=&COMM; v_DEPTNO:=&DEPTNO;insert into empvalues(v_EMPNO,v_ENAME,v_JOB,v_MGR,v_HIREDATE,v_SAL,v_COMM,v_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 employees.last_name%TYPE;- 用户hr v_hire_date employees.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. 条件判断语句的使用阅读并理解以下程序，调试并给出运行结果。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 TH