实验5+SQLPL编程基础.docx
《实验5+SQLPL编程基础.docx》由会员分享，可在线阅读，更多相关《实验5+SQLPL编程基础.docx（62页珍藏版）》请在冰点文库上搜索。
实验5+SQLPL编程基础
实验5 PL/SQL编程基础
姓名：
学号：
专业：
班级：
同组人：
无
实验日期：
2013/7/18
【实验目的与要求】
⏹掌握PL/SQL基本语法
⏹掌握PL/SQL简单存过程的编写
【实验内容与步骤】
5.1.实验准备工作
1.测试用表的创建与数据添加
（1）．创建测试表
CreateTableTESTTABLE（
RECORDNUMBERnumber（4）NotNull,
CURRENTDATEDateNotNull
）
Tablespace"USER";--这里的表空间其实可以不去管，这样它就会在当前用户的表空间中创建一个表
（2）．使用for语句在测试表中加入测试数据
Declare
maxrecordsConstantInt:
=20;
iInt:
=1;
Begin
ForiIn1..maxrecordsLoop
InsertIntohqf.testtable（recordnumber,currentdate）--hqf为模式名，应改动
Values（i,Sysdate）;
dbms_output.put_line（'现在输入的内容是：
'||i||' '||Sysdate）;
Commit; --这里切记要commit否则将不会将数据提交到表中
EndLoop;
dbms_output.put_line（'记录已经按照计划全部插入，请查看！
'）;
End;
//注：
hqf为登录用户名，需根据情况改动，如：
实验中一般用scott.
（3）．查询表中数据，给出查询结果截图：
5.2.最简单的PL/SQL程序
1.输出"Hello,World"
/***************************************
第一个例子:
输出"Hello,World"
***************************************/
setserverouton--设置SQL*Plus将服务器所返回的写出来
begin--块开始
DBMS_OUTPUT.put_line（'Hello,World'）;
--在控制台输出信息，类似C语言的Printf或者java语言中的System.out.print
end;--块结束
运行结果为：
1.接收数据并输出
/***************************************
在此基础上，完成Hello,某某，某某从客户端得到
***************************************/
declare
v_namevarchar2（20）;
begin--块开始
v_name:
='&v_name';--与“客户端”交互，类似C语言的scanf语句
DBMS_OUTPUT.put_line（'测试结果为：
Hello,'||v_name）;
end;--块结束
运行结果为：
5.3.简单变量的使用
1．变量的声明与引用
setserveroutputon;
--PL/SQL变量之简单类型
declare
v_dept_idnumber（5）:
=1111;
v_agebinary_integer:
=12;
v_dept_namevarchar2（20）:
='人事部';
v_rateconstantnumber（4,2）:
=22.12;
v_validbooleannotnull:
=TRUE;
v_hire_datedatenotnull:
=sysdate+7;
begin
v_dept_id:
=2222;
dbms_output.put_line（v_dept_id）;
end;
/
运行结果为：
2．Into子句赋值的使用：
declare
v_deptnonumber
（2）;
v_locvarchar2（15）;
begin
selectdeptno,loc
intov_deptno,v_loc
fromdept
wheredname='SALES';
DBMS_OUTPUT.PUT_LINE（V_deptno||'and'||v_loc）;
end;
运行结果为：
实验练习：
编写一PL/SQL程序，实现依次从客户端接收各字段的值，并放于变量中，输完一条记录的所有字段值后，将值写到数据库表Emp中。
请给出程序源码：
setserveroutputon
declare
d_empnonumber（4）;
d_enamevarchar2（10）
begin
d_empno:
='&编号';
d_ename:
='&姓名';
insertintoemp
（empno,ename）
values
（d_empno,d_ename）;
end;
/
给出测试结果：
3．变量作用域：
阅读以下程序，熟悉变量的斌值方法，理解变量的作用，给出运行结果。
--嵌套块中的变量赋值和作用域
DECLARE
v_weightNUMBER（3）:
=100;
v_messageVARCHAR2（255）:
='OuterValue';
BEGIN
DECLARE
v_weightNUMBER（3）:
=1;
v_messageVARCHAR2（255）:
='Innervalue';
BEGIN
v_weight:
=v_weight+1;
v_message:
='Put'||v_message;
DBMS_OUTPUT.PUT_LINE（'innerblock'||to_char（v_weight）||v_message）;
END;
v_weight:
=v_weight+1;
v_message:
='Put'||v_message;
DBMS_OUTPUT.PUT_LINE（'outterblock'||to_char（v_weight）||v_message）;
END;
运行结果为：
5.4.使用%type和%rowtype变量
1．使用%type变量
阅读以下程序，理解其功能，给出运行结果。
（1）DROPTABLEEMPL;
createtableEMPL
（
EMPLOYEE_IDNUMBER（5）notnull,
LAST_NAMEVARCHAR2（20）notnull,
HIRE_DATEDATE,
SALARYNUMBER（8,2）,
JOB_IDNUMBER（5）,
DEPARTMENT_IDNUMBER（5）
）;
setserveroutputon;
--PL/SQL变量之%TYPE类型
declare
v_last_nameemp.last_name%TYPE;
v_hire_dateemp.hire_date%TYPE;
v_balancenumber（6,2）;
v_min_balancev_balance%type;
begin
dbms_output.put_line（'定义%type类型的变量'）;
end;
/
运行结果为：
（2）declare
v_deptnodept.deptno%type;
v_locdept.loc%type;
begin
selectdeptno,loc
intov_deptno,v_loc
fromdept
wheredname='SALES';
DBMS_OUTPUT.PUT_LINE（V_deptno||'and'||v_loc）;
end;
/
运行结果为：
2．使用%Rowtype变量
阅读以下程序，理解其功能，给出运行结果。
（1） DROPTABLEEMPL;
createtableEMPL
（
EMPLOYEE_IDNUMBER（5）notnull,
LAST_NAMEVARCHAR2（20）notnull,
HIRE_DATEDATE,
SALARYNUMBER（8,2）,
JOB_IDNUMBER（5）,
DEPARTMENT_IDNUMBER（5）
）;
setserveroutputon;
--PL/SQL变量之%ROWTYPE类型
declare
v_emplemp%rowtype;
begin
dbms_output.put_line（'定义%rowtype类型的变量'）;
end;
/
运行结果为：
（2） droptablestock;
createtablestock
（
symbolvarchar2（50）,
pricenumber（6,2）
）;
insertintostockvalues（'IBM',188.88）;
insertintostockvalues（'ORCL',100.88）;
DECLARE
stock_info1stock%ROWTYPE;
stock_info2stock%ROWTYPE;
stock_info3stock%ROWTYPE;
BEGIN
--对应纪录直接取得相应字段的：
Populatethespecificfieldsinarecord
SELECTsymbol,price
INTOstock_info1.symbol,stock_info1.price
FROMstock
WHEREsymbol='ORCL';
--对应纪录直接放入纪录中
SELECT*INTOstock_info2FROMstock
WHEREsymbol='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;
运行结果为：
思考：
如下所示：
改变表结构。
droptablestock;
createtablestock
（
symbolvarchar2（50）,
pricenumber（6,2）,
ownervarchar2（10）
）;
对以上程序运行有影响吗？
为什么？
5.5.使用复合变量
1.记录类型
阅读以下程序，理解其功能，给出运行结果。
（1）setserveroutputon;
--PL/SQL变量之RECORD类型1
declare
typenamed_record_typeisrecord（
last_namevarchar2（20）,
first_namevarchar2（20）,
birthdaydate,
agenumber（3）
）;
v_record_1named_record_type;
v_record_2named_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：
setserveroutputon;
DECLARE
TYPErempISRECORD（
v_empnonumber（10）,
v_enameVARCHAR2（20）,
v_salaryNUMBER（10,2）
）;
rsm_empremp;
BEGIN
SELECTempno,ename,sal
INTOrsm_emp.v_empno,rsm_emp.v_ename,rsm_emp.v_salary
FROMemp
WHEREsal=（SELECTMAX（sal）FROMemp）;
--求出工资最高的雇员
DBMS_OUTPUT.PUT_LINE（'HighestPaidEmployeeis'||rsm_emp.v_ename）;
DBMS_OUTPUT.PUT_LINE（'Idis'||rsm_emp.v_empno||'Salary'||to_char（rsm_emp.v_salary,'999,999.99'））;
END;
运行结果为：
（3）--使用记录类型3：
setserveroutputon;
DECLARE
TYPErempISRECORD（
v_empnonumber（10）,
v_enameVARCHAR2（20）,
v_salaryNUMBER（10,2）
）;
rsm_empremp;
BEGIN
SELECTempno,ename,sal
INTOrsm_emp
FROMemp
WHEREsal=（SELECTMAX（sal）FROMemp）;
--求出工资最高的雇员
DBMS_OUTPUT.PUT_LINE（'HighestPaidEmployeeis'||rsm_emp.v_ename）;
DBMS_OUTPUT.PUT_LINE（'Idis'||rsm_emp.v_empno||'Salary'||to_char（rsm_emp.v_salary,'999,999.99'））;
END;
运行结果为：
2.记录表类型
阅读以下程序，理解其功能，给出运行结果。
--一维表类型（相当于基本数据类型元素组成的一维数组）
declare
typetabletype1istableofvarchar2（4）indexbybinary_integer;
--typetabletype2istableofemployees.last_name%typeindexbybinary_integer;
table1tabletype1;
--table2tabletype2;
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等属性进行操作，使用方法为“表变量名.属性”，返回的是数字。
*/
--使用表类型变量属性
setserveroutputon;
Declare
typetabletype1istableofvarchar2（9）indexbybinary_integer;
table1tabletype1;
ibinary_integer:
=11;
begin
table1
（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（'该条记录不存在！
'）;
endif;
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;
/
运行结果为：
5.6.复合变量的使用
1.完整定义一个记录类型的变量，了解其简单应用。
阅读以下程序，理解其功能，给出运行结果。
Declare
TypemyrecordIsRecord（
r_recordnumberNumber（4）,
r_currentdateDate
）;
v_myrecordmyrecord;
Begin
Select*
Intov_myrecord
Fromtesttable
Whererecordnumber=80;
dbms_output.put_line（'用记录类型的变量取出来的值为：
'||
v_myrecord.r_recordnumber||
v_myrecord.r_currentdate）;
End;
给出运行结果：
2.简单的%Rowtype定义变量的实例
阅读以下程序，理解其功能，给出运行结果。
Declare
v_myrowtesttable%Rowtype;
Begin
Select*
Intov_myrow
Fromtesttable
Whererecordnumber=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;
给出运行结果：
3．定义与使用一维表变量
阅读以下程序，理解其功能，给出运行结果。
--维表变量，这种变量看起来有些类似于C语言中的一维数组
Declare
Typemytbtype1IsTableOfVarchar2（4）IndexByBinary_Integer;
Typemytbtype2IsTableOftesttable.recordnumber%TypeIndexByBinary_Integer;
tb1mytbtype1;
tb2mytbtype2;
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
--这里区别于一维表变量的定义
TypemultbtypeIsTableOftesttable%RowtypeIndexByBinary_Integer;
multbmultbtype;
Begin
Select*Intomultb（12）
Fromtesttable
升级会员 