实验3PLSQL编程.docx
- 文档编号:30209538
- 上传时间:2023-08-07
- 格式:DOCX
- 页数:20
- 大小:146.71KB
实验3PLSQL编程.docx
《实验3PLSQL编程.docx》由会员分享,可在线阅读,更多相关《实验3PLSQL编程.docx(20页珍藏版)》请在冰豆网上搜索。
实验3PLSQL编程
实验三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;
疑难小结:
在创建过程时,还是对基本知识掌握不牢固,在使用循环时有时会迷惑循环过程,追根揭底是对循环过程的不熟悉,还要勤加锻炼,对游标的声明及使用还是不熟悉,还有就是练习过程中会敲错代码,今后的学习过程中一点要结合课本多加练习吃透基本知识
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验 PLSQL 编程