在Oracle SQL Developer 环境中,一下的"declare"声明文字不必写出,直接写入"declare"一下的不分即可。
一.创建一个简单的PL/SQL程序块
使用不同的程序块组件工作
使用编程结构编写PL/SQL程序块
处理PL/SQL程序块中的错误
1.编写一个程序块,从emp表中显示名为“SMITH”的雇员的薪水和职位。
declare
v_empemp%rowtype;
begin
select * into v_emp from emp where ename='SMITH';
dbms_output.put_line('员工的工作是:'||v_emp.job||'; 他的薪水是:'||v_emp.sal);
end;
2.编写一个程序块,接受用户输入一个部门号,从dept表中显示该部门的名称与所在位置。
方法一:(传统方法)
declare
v_loc deptcp.dname%type;
v_dname deptcp.dname%type;
v_deptno deptcp.deptno%type;
begin
v_deptno :=&部门编号;
select loc,dnameinto v_loc,v_dname fromdeptcp where deptno=v_deptno;
dbms_output.put_line('员工所在地是:'||v_loc||';部门名称是:'||v_dname);
exception
when no_data_found
thendbms_output.put_line('您输入的部门编号不存在,请从新输入,谢谢');
end;
方法二:(使用%rowtype)
declare
v_dept dept%rowtype;
begin
select * into v_dept from deptwhere deptno=&部门号;
dbms_output.put_line(v_dept.dname||'--'||v_dept.loc);
end;
3.编写一个程序块,利用%type属性,接受一个雇员号,从emp表中显示该雇员的整体薪水(即,薪水加佣金)。(*期末考试试题*)
declare
v_salemp.sal%type;
begin
selectsal+commintov_salfromempwhereempno=&雇员号;
dbms_output.put_line(v_sal);
end;
4.编写一个程序块,利用%rowtype属性,接受一个雇员号,从emp表中显示该雇员的整体薪水(即,薪水加佣金)。
方式一:(错误程序)(让学生思考错在哪里?)
declare
v_emp empcp%rowtype;
begin
select*into v_empfromempcp where empno = &雇员编号;
dbms_output.put_line('整体薪水是:'||v_emp.sal+v_emp.comm);
end;
declare
v_empemp%rowtype;
begin
select *intov_empfromempwhereempno=&雇员号;
dbms_output.put_line(v_emp.sal+v_emp.comm);
end;
5.某公司要根据雇员的职位来加薪,公司决定按下列加薪结构处理:
Designation Raise
-----------------------
Clerk 500
Salesman 1000
Analyst 1500
Otherwise 2000
编写一个程序块,接受一个雇员名,从emp表中实现上述加薪处理。(*期末考试试题*)
declare
v_empemp%rowtype;
begin
select *intov_empfromempwhereename='&name';
ifv_emp.job='CLERK'then
updateempsetsal=sal+500whereempno=v_emp.empno;
elsifv_emp.job='SALESMAN'then
updateempsetsal=sal+1000whereempno=v_emp.empno;
elsifv_emp.job='ANALYST'then
updateempsetsal=sal+1500whereempno=v_emp.empno;
else
updateempsetsal=sal+2000whereempno=v_emp.empno;
endif;
commit;
end;
6.编写一个程序块,将emp表中雇员名全部显示出来。
declare
cursorv_cursorisselect *fromemp;
begin
forv_empinv_cursor
loop
dbms_output.put_line(v_emp.ename);
endloop;
end;
7.编写一个程序块,将emp表中前5人的名字显示出来。
declare
cursorv_cursorisselect *fromemp;
v_countnumber :=1;
begin
forv_empinv_cursor
loop
dbms_output.put_line(v_emp.ename);
v_count :=v_count+1;
exitwhenv_count>5;
endloop;
end;
8.编写一个程序块,接受一个雇员名,从emp表中显示该雇员的工作岗位与薪水,若输入的雇员名不存在,显示“该雇员不存在”信息。(*期末考试试题*)
declare
v_empemp%rowtype;
my_exceptionException;
begin
select *intov_empfromempwhereename='&name';
raisemy_exception;
exception
whenno_data_foundthen
dbms_output.put_line('该雇员不存在!');
whenothersthen
dbms_output.put_line(v_emp.job||'---'||v_emp.sal);
end;
9.接受两个数相除并且显示结果,如果第二个数为0,则显示消息“除数不能为0”(课堂未讲)。
declare
v_dividendfloat;
v_divisorfloat;
v_resultfloat;
my_exceptionException;
begin
v_dividend:=&被除数;
v_divisor:=&除数;
v_result:=v_dividend/v_divisor;
raisemy_exception;
exception
whenmy_exceptionthen
dbms_output.put_line(v_result);
whenothersthen
dbms_output.put_line('除数不能为0');
end;
二.声明和使用游标
使用游标属性
使用游标For循环工作
声明带参数的游标
(使用FOR UPDATEOF和CURRENT OF子句工作)
1. 通过使用游标来显示dept表中的部门名称。
declare
cursorv_cursorisselect *fromdept;
begin
forv_deptinv_cursor
loop
dbms_output.put_line(v_dept.dname);
endloop;
end;
2. 使用For循环,接受一个部门号,从emp表中显示该部门的所有雇员的姓名,工作和薪水。
declare
cursorv_cursorisselect *fromempwheredeptno=&部门号;
begin
forv_empinv_cursor
loop dbms_output.put_line(v_emp.ename||'--'||v_emp.job||'--'
||v_emp.sal);
endloop;
end;
3. 使用带参数的游标,实现第2题。
declare
cursorv_cursor(p_deptnonumber)isselect *fromempwheredeptno=p_deptno;
v_deptnonumber(2);
begin
v_deptno:=&部门号;
forv_empinv_cursor(v_deptno)
loop
dbms_output.put_line(v_emp.ename||'--'||v_emp.job||'--'||v_emp.sal);
endloop;
end;
4.编写一个PL/SQL程序块,从emp表中对名字以“A”或“S”开始的所有雇员按他们基本薪水的10%给他们加薪。
declare
cursorv_cursorisselect *fromemp;
begin
forv_empinv_cursor
loop
ifv_emp.enamelike'A%'then
updateempsetsal=sal+sal*0.1whereempno=v_emp.empno;
elsifv_emp.enamelike'S%'then
updateempsetsal=sal+sal*0.1whereempno=v_emp.empno;
endif;
commit;
endloop;
end;
5. emp表中对所有雇员按他们基本薪水的10%给他们加薪,如果所增加后的薪水大于5000卢布,则取消加薪。
declare
cursorv_cursoris
select *fromemp;
begin
forv_empinv_cursorloop
ifv_emp.sal *1.1 < 5000then
updateempsetsal =sal * 1.1whereempno =v_emp.empno;
endif;
commit;
endloop;
end;
三,创建PL/SQL记录和PL/SQL表
创建过程
创建函数
3.创建一个过程,能向dept表中添加一个新记录.(in参数)
createorreplaceprocedure
insert_dept(dept_noinnumber,dept_nameinvarchar2,dept_locinvarchar2)
is
begin
insertintodeptvalues(dept_no,dept_name,dept_loc);
end;
调用该存储过程:
begin
insert_dept(50,'技术部','武汉');
end;
4.创建一个过程,从emp表中带入雇员的姓名,返回该雇员的薪水值。(out参数)
然后调用过程。
createorreplaceprocedure
find_emp3(emp_nameinvarchar2,emp_saloutnumber)
is
v_salnumber(5);
begin
selectsalintov_salfromempwhereename = emp_name;
emp_sal:=v_sal;
exception
whenno_data_foundthen
emp_sal :=0;
end;
调用:
declare
v_salnumber(5);
begin
find_emp3('ALLEN',v_sal);
dbms_output.put_line(v_sal);
end;
5.编写一个程序块,接受一个雇员号与一个百分数,从emp表中将该雇员的薪水增加输入的百分比(*课堂没讲)。
(利用过程,in out 参数)
createorreplaceprocedure
update_sal(emp_noinnumber,parsentinfloat)
is
begin
updateempsetsal=sal+sal*parsentwhereempno=emp_no;
end;
调用:
begin
update_sal(7499,0.5);
end;
6.创建一个函数,它以部门号作为参数且返回那个部门的所有的所有雇员的整体薪水。
然后调用此函数。
7.创建一个函数,它以部门号作为参数传递并且使用函数显示那个部门名称与位置。
然后调用此函数。
createorreplacefunction
find_dept(dept_nonumber)
returndept%rowtype
is
v_deptdept%rowtype;
begin
select *intov_deptfromdeptwheredeptno=dept_no;
returnv_dept;
end;
调用函数:
declare
v_deptdept%rowtype;
begin
v_dept:=find_dept(30);
dbms_output.put_line(v_dept.dname||'---'||v_dept.loc);
end;
四,创建程序包
创建程序件
创建触发器
1.创建在dept表中插入和删除一个记录的数据包,它且有一个函数(返回插入或删除的部门名称)和两个过程。
然后调用包。
createorreplacepackagepack_1
is
procedurefind_emp(emp_noinnumber,emp_nameoutvarchar2);
procedurefind_emp1(emp_nameinvarchar2,emp_nooutnumber);
functionfind_dname(dept_nonumber)
returnvarchar2;
endpack_1;
createorreplacepackagebodypack_1
is
functionfind_dname(dept_nonumber)
returnvarchar2
is
v_dnamevarchar2(20);
begin
selectdnameintov_dnamefromdeptwheredeptno=dept_no;
retrunv_dname;
end;
endpack_1;
调用包:
declare
v_dnamevarchar2(20);
begin
v_dname:=pack_1.find_dname(50);
dbms_output.put_line(v_dname);
end;
3.使用单独过程打开游标变量,将dept表中的记录显示出来。只创建程序包,无需主体。
4.创建一个行级别触发器,将从emp表中删除的记录输入到ret_emp表中。
createorreplacetriggerdelete_emp
afterdeleteonemp
foreachrow
begin
insertintoret_empvalues(:old.empno,:old.ename,:old.job,
:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end;
5.创建一个行级别触发器,停止用户删除雇员名为"SMITH"的记录。
createorreplacetriggerdelete_smith
beforedeleteonemp
foreachrow
when (old.ename='SMITH')
begin
raise_application_error(-20001,'不能删除该条信息!');
end;
6. 创建一个语句级别触发器,不允许用户在"Sundays"使用emp表。
createorreplacetriggert_control_emp
beforeinsertorupdateordeleteonemp
begin
ifto_char(sysdate,'DY','nls_date_language=AMERICAN')
in('SUN')then
raise_application_error(-20001,'不允许在星期天操作emp表');
endif;
end;