实验3PLSQL编程.docx
《实验3PLSQL编程.docx》由会员分享，可在线阅读，更多相关《实验3PLSQL编程.docx（16页珍藏版）》请在冰点文库上搜索。
实验三PL/SQL编程
1．PL/SQL块处理
定义一个包含声明、执行和异常处理的块
查询EMP表中职工号7788的工资，输出工资的值并且如果工资小于3000那么把工资更改为3000，异常部分对NO_DATA_FOUND异常进行处理，输出没有该员工。
如果想运行缓冲区的内容，那么可以用RUN命令或者/命令；serveroutput需要设置为on
2．记录类型的使用
创建一个记录类型v_record，类型包含name，salary，job，deptno等分量，要求记录类型的分量的数据类型和emp表中列的数据类型一致（%type实现）。
创建一个变量，变量类型为v_record，读取EMP表中职工号为7788的ename，sal，job，deptno为该变量赋值，输出变量的分量。
3．条件语句的使用
分别用IF语句和CASE语句实现以下要求：
输入一个员工号，修改该员工的工资，如果该员工职位是CLERK，工资增加100；若为SALESMAN，工资增加160；若为ANALYST，工资增加200；否则增加300。
4.循环和显示游标的使用
分别用简单循环、WHILE循环、FOR循环以及显示游标统计并输出各个部门的人数以及平均工资
5．用隐式游标实现以下要求：
修改部门号为50的部门地址为‘BEIJING’。
如果该部门不存在，则向dept表中插入一个部门号为50，地址为‘BEIJING’的记录。
6．创建一个显示雇员总人数的存储过程emp_count,并执行该存储过程
7.编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程
8.创建函数，实现功能为：
在scott.emp表和scott.dept表中查询出任意给定职工号的职工姓名及职工所在部门的名称。
9.创建触发器，实现更新dept表中的deptno值，级联更新emp表中相应值。
10.对存储过程、函数及触发器实现查看、修改、删除等基本操作。
主要算法和程序清单：
1.
DECLARE
v_empnoemp.empno%TYPE:
=7788;
v_salemp.sal%TYPE;
v_addemp.sal%TYPE;
BEGIN
SELECTsalINTOv_salFROMempWHEREempno=v_empno;
IFv_sal<3000
THENv_add:
=3000;
ELSE
dbms_output.put_line（'sal>3000'）;
ENDIF;
UPDATEempSETsal=v_addWHEREempno=v_empno;
END;
/
2．
declare
typev_recordisrecord（
nameemp.ename%type,
salaryemp.sal%type,
jobemp.job%type,
deptnoemp.deptno%type
）;
empinfov_record;--定义变量
begin
selectename,sal,job,deptno
intoempinfo
fromemp
whereempno=7788;
dbms_output.put_line（'雇员'||empinfo.name||'的职务是：
'||empinfo.job||'工资是：
'||empinfo.salary||'部门号是：
'||empinfo.deptno）;
end;
/
3.
declare
cursorc_empisselect*fromscott.empforupdate;
v_incrementnumber;
begin
forv_empinc_emploop
casev_emp.deptno
when10thenv_increment:
=100;
when20thenv_increment:
=160;
when30thenv_increment:
=200;
elsev_increment:
=300;
endcase;
updatescott.empsetsal=sal+v_incrementwhere
currentofc_emp;
endloop;
end;
4.
5.
begin
updatescott.dept
setloc='BEIJING'wheredeptno=50;
ifsql%notfoundthen
insertintoscott.dept（deptno,loc）values（50,'BEIJING'）;
dbms_output.put_line（'插入成功!
'）;
ELSE
dbms_output.put_line（'更新成共'）;
endif;
end;
6.
createorreplaceprocedureemp_count
asv_totalnumber;
begin
selectcount（*）intov_totalfromscott.emp;
dbms_output.put_line（'雇员总数：
'||v_total）;
end;
/
SQL>executeemp_count;
雇员总数：
15
PL/SQL过程已成功完成。
SQL>begin
2emp_count;
3end;
4/
雇员总数：
15
PL/SQL过程已成功完成。
7.
1CREATEORREPLACEPROCEDUREEMP_LIST
2AS
3CURSORemp_cursorIS
4SELECTempno,enameFROMscott.emp;
5BEGIN
6FOREmp_recordINemp_cursorLOOP
7DBMS_OUTPUT.PUT_LINE（Emp_record.empno||Emp_record.ename）;
8ENDLOOP;
9EMP_COUNT;
10*END;
SQL>/
过程已创建。
8.
SQL>CREATEORREPLACEPROCEDUREselect_emp
2（v_emp_noINemp.empno%type）
3IS
4v_emp_nameemp.ename%type;
5v_dept_namedept.dname%type;
6BEGIN
7SELECTEMP.ENAME,DEPT.DNAME
8INTOv_emp_name,v_dept_name
9FROMEMP,DEPT
10WHEREEMP.DEPTNO=DEPT.DEPTNOANDEMPNO=v_emp_no;
11DBMS_OUTPUT.PUT_LINE（v_emp_name||''||v_dept_name）;
12ENDselect_emp;
13/
过程已创建。
SQL>EXECUTEselect_emp（7844）;
TURNERSALES
PL/SQL过程已成功完成。
SQL>CREATEORREPLACETRIGGERupdate_dept_to_emp
2AFTERUPDATEONDEPTFOREACHROW
3BEGIN
4IFUPDATINGTHEN
5UPDATEEMPSETDEPTNO=:
new.DEPTNO
6WHEREDEPTNO=:
old.DEPTNO;
7ENDIF;
8ENDupdate_dept_to_emp;
9/
触发器已创建
9.
CREATEORREPLACETRIGGERtr_reg_dep
AFTERupdateOFdeptno
ONdept
FOREACHROW
BEGIN
DBMS_OUTPUT.PUT_LINE（'旧的deptno值是'||:
old.deptno
||'、新的deptno值是'||:
new.deptno）;
UPDATEempSETdeptno=:
new.deptno
WHEREdeptno=:
old.deptno;
END;
10.
selectobject_name,statusfromuser_objectswhereobject_type='FUNCTION';
selectobject_name,statusfromuser_objectswhereobject_type='PROCEDURE';
SELECTOBJECT_NAMEFROMDBA_OBJECTSWHEREOBJECT_TYPE='TRIGGER';
dropprocedureselect_emp;
DROPFUNCTIONGET_AVG_PAY;
DROPTRIGGERTR_REG_DEP;
五、拓展题
--创建一个包mypackage，声明该包有一个过程update_sal和一个函数get_YearSal
createorreplacepackagemyPackageis
procedureupdate_sal（namevarchar2,newsalnumber）;
functionget_YearSal（namevarchar2）returnnumber;
end;
createorreplacepackagebodymyPackageis
procedureupdate_sal（namevarchar2,newsalnumber）is
begin
updateempsetsal=newSalwhereename=name;
end;
functionget_YearSal（namevarchar2）returnnumberis
v_salnumber（7,2）;
begin
selectsal*12+nvl（comm,0）intov_salfromempwhereename=name;
returnv_sal;
end;
end;
调用执行包中的存储过程或函数
我们现在有这样一张用户表表结构如下，希望向表中增加数据时，表中id列的数字自动生成。
第一步创建序列，要求开始的数字为1，每次递增1，按顺序产生序列值；第二步创建一个触发器，向用户表中插入数据的时候触发触发器，在触发器内部调用序列并生成一个序列值赋值给表的id列。
表结构如下:
UserInfo（id,username,userPass）
--用户表（用户编号number类型，用户名，用户密码）
createsequenceseq_user_id
startwith1--从1开始
incrementby1;--每次增量为1
第二步创建一个触发器给id列赋值
createtriggertr_user_id
beforeinsertonuser
foreachrow
begin
selectseq_user_id.nextvalinto:
new.idfromdual;
end;
主要算法:
1.
DECLARE
v_empnoemp.empno%TYPE:
=7788;
v_salemp.sal%TYPE;
v_addemp.sal%TYPE;
BEGIN
SELECTsalINTOv_salFROMempWHEREempno=v_empno;
IFv_sal<3000
THENv_add:
=3000;
ELSE
dbms_output.put_line（'sal>3000'）;
ENDIF;
UPDATEempSETsal=v_addWHEREempno=v_empno;
END;
/
2．
declare
typev_recordisrecord（
nameemp.ename%type,
salaryemp.sal%type,
jobemp.job%type,
deptnoemp.deptno%type
）;
empinfov_record;--定义变量
begin
selectename,sal,job,deptno
intoempinfo
fromemp
whereempno=7788;
dbms_output.put_line（'雇员'||empinfo.name||'的职务是：
'||empinfo.job||'工资是：
'||empinfo.salary||'部门号是：
'||empinfo.deptno）;
end;
/
3.
declare
cursorc_empisselect*fromscott.empforupdate;
v_incrementnumber;
begin
forv_empinc_emploop
casev_emp.deptno
when10thenv_increment:
=100;
when20thenv_increment:
=160;
when30thenv_increment:
=200;
elsev_increment:
=300;
endcase;
updatescott.empsetsal=sal+v_incrementwhere
currentofc_emp;
endloop;
end;
4.
5.
begin
updatescott.dept
setloc='BEIJING'wheredeptno=50;
ifsql%notfoundthen
insertintoscott.dept（deptno,loc）values（50,'BEIJING'）;
dbms_output.put_line（'插入成功!
'）;
ELSE
dbms_output.put_line（'更新成共'）;
endif;
end;
6.
createorreplaceprocedureemp_count
asv_totalnumber;
begin
selectcount（*）intov_totalfromscott.emp;
dbms_output.put_line（'雇员总数：
'||v_total）;
end;
/
SQL>executeemp_count;
雇员总数：
15
PL/SQL过程已成功完成。
SQL>begin
2emp_count;
3end;
4/
雇员总数：
15
PL/SQL过程已成功完成。
7.
1CREATEORREPLACEPROCEDUREEMP_LIST
2AS
3CURSORemp_cursorIS
4SELECTempno,enameFROMscott.emp;
5BEGIN
6FOREmp_recordINemp_cursorLOOP
7DBMS_OUTPUT.PUT_LINE（Emp_record.empno||Emp_record.ename）;
8ENDLOOP;
9EMP_COUNT;
10*END;
SQL>/
过程已创建。
8.
SQL>CREATEORREPLACEPROCEDUREselect_emp
2（v_emp_noINemp.empno%type）
3IS
4v_emp_nameemp.ename%type;
5v_dept_namedept.dname%type;
6BEGIN
7SELECTEMP.ENAME,DEPT.DNAME
8INTOv_emp_name,v_dept_name
9FROMEMP,DEPT
10WHEREEMP.DEPTNO=DEPT.DEPTNOANDEMPNO=v_emp_no;
11DBMS_OUTPUT.PUT_LINE（v_emp_name||''||v_dept_name）;
12ENDselect_emp;
13/
过程已创建。
SQL>EXECUTEselect_emp（7844）;
TURNERSALES
PL/SQL过程已成功完成。
SQL>CREATEORREPLACETRIGGERupdate_dept_to_emp
2AFTERUPDATEONDEPTFOREACHROW
3BEGIN
4IFUPDATINGTHEN
5UPDATEEMPSETDEPTNO=:
new.DEPTNO
6WHEREDEPTNO=:
old.DEPTNO;
7ENDIF;
8ENDupdate_dept_to_emp;
9/
触发器已创建
9.
CREATEORREPLACETRIGGERtr_reg_dep
AFTERupdateOFdeptno
ONdept
FOREACHROW
BEGIN
DBMS_OUTPUT.PUT_LINE（'旧的deptno值是'||:
old.deptno
||'、新的deptno值是'||:
new.deptno）;
UPDATEempSETdeptno=:
new.deptno
WHEREdeptno=:
old.deptno;
END;
10.
selectobject_name,statusfromuser_objectswhereobject_type='FUNCTION';
selectobject_name,statusfromuser_objectswhereobject_type='PROCEDURE';
SELECTOBJECT_NAMEFROMDBA_OBJECTSWHEREOBJECT_TYPE='TRIGGER';
dropprocedureselect_emp;
DROPFUNCTIONGET_AVG_PAY;
DROPTRIGGERTR_REG_DEP;
五、拓展题
--创建一个包mypackage，声明该包有一个过程update_sal和一个函数get_YearSal
createorreplacepackagemyPackageis
procedureupdate_sal（namevarchar2,newsalnumber）;
functionget_YearSal（namevarchar2）returnnumber;
end;
createorreplacepackagebodymyPackageis
procedureupdate_sal（namevarchar2,newsalnumber）is
begin
updateempsetsal=newSalwhereename=name;
end;
functionget_YearSal（namevarchar2）returnnumberis
v_salnumber（7,2）;
begin
selectsal*12+nvl（comm,0）intov_salfromempwhereename=name;
returnv_sal;
end;
end;
疑难小结：
在创建过程时，还是对基本知识掌握不牢固，在使用循环时有时会迷惑循环过程，追根揭底是对循环过程的不熟悉，还要勤加锻炼，对游标的声明及使用还是不熟悉，还有就是练习过程中会敲错代码，今后的学习过程中一点要结合课本多加练习吃透基本知识
升级会员 