Oracle第五章PL/SQL基础

Homework - Chapter 5
5.7.1 实训 Practical training
1：PL/SQL 基础 Basics
〖实训目的 Training purpose〗
（1）学会 PL/SQL基本语法； Learn basic PL/SQL syntax
（2）理解 PLSQL 语句块的构成。 Understand the composition of PL/SQL statement blocks
〖实训内容 Training content〗

（1）使用PL/SQL 语句块在第一行输出自己的姓名、学号，在第二行输出自己想说的一句话，如 “我喜欢PL/SQL 编程！".
Use the PL/SQL statement block to output your name and student number on the first line, and output a sentence you want to say on the second line, such as “I like PL/SQL programming!”
set serveroutput on
declare
myname varchar2(10):=‘Zeng’;
sentence varchar2(50):=‘I LIKE PL/SQL PROGRAMMING!’;
begin
dbms_output.put_line(‘name=’||myname);
dbms_output.put_line(‘like=’||sentence);
end;

（2）使用%TYPE 定义两个变量，分别对应员工表中的员工姓名和月薪，然后用这两个变量读出员工表中员工编号为7876 的员工的姓名和月薪并输出。
Use %TYPE to define two variables corresponding to the employee name and monthly salary in the employee table, and then use these two variables to read out the name and monthly salary of the employee with employee number 7876 in the employee table and output them.
declare
v_name scott.emp.ename%type;
v_sal scott.emp.sal%type;
begin
select ename,sal into v_name,v_sal from scott.emp where empno = 7876;
dbms_output.put_line(‘ename=’||v_name);
dbms_output.put_line(‘sal=’||v_sal);
end;
select * from scott.emp where empno=7876

（3）要求用户输入一个员工编号，之后根据输入的内容进行查询，查询员工的姓名，并将查询到的姓名予以输出。注：用户的输入信息使用 “&" 完成。
The user is required to input an employee number, then query according to the input content, query the employee’s name, and output the queried name. Note: the user’s input information is completed with “&”.
declare
v_ename scott.emp.ename%type;
v_empno scott.emp.empno%type;
begin
v_empno:=&a;
select ename into v_ename from scott.emp where empno = v_empno;
dbms_output.put_line(‘ename=’||v_ename);
end;
select * from scott.emp

5.7.2 实训2： 分支结构 Training 2: branch structure
〖实训目的 Training purpose〗
（l）学会PL/SQL基本语法； Learn the basic syntax of PL/SQL;
（2）理解PL/SQL 语句块的构成： Understand the composition of PL/SQL statement blocks:
（3）掌握PL/SQL 的程序流程控制（分支结构）： Master PL/SQL program flow control (branch structure):
（4）学会使用PL/SQL 注释。Learn to use PL/SQL annotations.
〖实训内容 Training content〗

（1）编写一个PL/SQL 程序，实现接收部门编号的功能，根据此编号显示部门名称和地理位置。
Write a PL/SQL program to realize the function of receiving department number, and display the Department name and geographical location according to this number.
declare
v_dname scott.dept.dname%type;
v_loc scott.dept.loc%type;
v_deptno scott.dept.deptno%type;
begin
v_deptno := &a;
select dname,loc into v_dname,v_loc from scott.dept where deptno=v_deptno;
dbms_output.put_line(‘dname=’||v_dname||’ '||‘loc=’||v_loc);
end;
select * from scott.dept

（2）修改第(1) 题，使用%ROWTYPE实现第(l) 题的功能。
Modify question (1) and use %ROWTYPE to realize the function of question (1).
declare
v_dept scott.dept%rowtype;
begin
select * into v_dept from scott.dept where deptno = &a;
dbms_output.put_line(‘dname=’||v_dept.dname||’ '||‘loc=’||v_dept.loc);
end;
select * from scott.dept where deptno = 10

（3）使用PL/SQL 程序块，给定部门为销售部，显示销售部员工的人数。
Using PL/SQL program block, the given department is the sales department, and the number of employees in the sales department is displayed.
declare
emp_deptname constant varchar2(10):=‘SALES’;
emp_count number(4);
begin
select count(*) into emp_count
from scott.emp a,scott.dept b
where a.deptno = b.deptno and dname = emp_deptname;
dbms_output.put_line(‘部门为：’ || emp_deptname || ’ 的员工数量：’ || Emp_count );
end;

（4）两个数相除，并显示结果，如果除数为0，则显示错误提示。
Divide the two numbers and display the result. If the divisor is 0, an error message will be displayed.
declare
v_num1 int:=&a;
v_num2 int:=&b;
result int;
begin
result := v_num1/v_num2;
if result=0 then
dbms_output.put_line(‘错误’);
else
dbms_output.put_line(result);
end if;
end;

（5）基于第（3）题，修改并使用IF 语句实现：如果人员数为0 则输出“销售部门无人"，如果人员数、10 则输出“销售部门人丁兴旺：" 及其人数，否则仅输出该部门的具体人数。
If the number of sales personnel in the 10th department is modified as “number of sales personnel in the 10th Department”, then the output is only based on the number of sales personnel in the 10th Department (if the number of sales personnel in the 10th department is modified), and the output is only based on the number of sales personnel in the 10th department.
declare
emp_deptname constant varchar2(10):=‘SALES’;
emp_count number(4);
begin
select count(*) into emp_count
from scott.emp a,scott.dept b
where a.deptno = b.deptno and dname = emp_deptname;
if emp_count = 0 then
dbms_output.put_line(‘销售部门无人’);
elsif emp_count > 10 then
dbms_output.put_line(‘销售部门人丁兴旺’);
else
dbms_output.put_line(‘部门为：’ || emp_deptname || ’ 的员工数量：’ || Emp_count );
end if;
end;
select * from scott.dept

（6）用CASE WHEN实现：输入一个员工的编号，如果其工资高于3500 ，则显示“高工资；工资大于2000 ，则显示“中等工资"；工资小于2000 ，则认为是“低等工资"，并把其工资显示出来。
Use CASE WHEN to realize: enter the number of an employee. If the salary is higher than 3500, it will display “high salary”; if the salary is greater than 2000, it will display “medium salary”; if the salary is less than 2000, it will be considered as “low salary”, and its salary will be displayed.
declare
v_empno number(4):=&empno;
v_sal number(10);
begin
select sal into v_sal from scott.emp where empno = v_empno;
case
when (v_sal>3500) THEN
DBMS_OUTPUT.put_line(v_sal||‘高工资’);
when (v_sal>2000 and v_sal <3500) THEN
DBMS_OUTPUT.put_line(v_sal||‘中等工资’) ;
when (v_sal<2000) THEN
DBMS_OUTPUT.put_line(v_sal||‘低等工资’);
End case;
end;

（7）用CASE WHEN 实现：接收两个数相除，如果除数为0，则显示错误提示；如果除数为1 ，则显示被除数：否则显示相除的结果。
Use CASE WHEN to realize: receive the division of two numbers. If the divisor is 0, an error prompt will be displayed; If the divisor is 1, the dividend is displayed; otherwise, the result of division is displayed.
declare
v_num1 int:=&a;
v_num2 int:=&b;
result int;
begin
result := v_num1/v_num2;
case
when (result=0) THEN
DBMS_OUTPUT.put_line(‘错误’);
when (result=1) THEN
DBMS_OUTPUT.put_line(‘被除数=’||v_num1);
else
DBMS_OUTPUT.put_line(result);
End case;
end;

5.7.3 实训3： 循环结构 Training 3: loop structure
〖实训目的 Training purpose〗
（1）进一步掌握PL/SQL的语法； Further master the syntax of PL/SQL;
（2）掌握PL/SQL 的程序流程控制：分支结构、循环结构。Master the program flow control of PL/SQL: branch structure and loop structure.
〖实训内容 Training content〗

（1）查询并输出工资最高的员工工号、姓名、工资，如果员工工资与平均工资差距> 500 ，则显示“工资最高的人在***部门 “，否则输出“各个部门工资相对比较平均”。
Query and output the job, name and salary of the employee with the highest salary. If the gap between the employee’s salary and the average salary is > 500, “the person with the highest salary is in *** Department” will be displayed, otherwise, “the salary of each department is relatively average” will be output.
declare
v_maxSal scott.emp.sal%type;
v_avgSal scott.emp.sal%type;
v_maxSalEmp scott.emp%rowtype;
v_maxSalEmpDept scott.dept.dname%type;
begin
select * into v_maxSalEmp from scott.emp where sal = (select max(sal) from scott.emp);
select avg(sal) into v_avgSal from scott.emp;
select dname into v_maxSalEmpDept from scott.dept where deptno = v_maxSalEmp.deptno;
dbms_output.put_line(‘empno=’||v_maxSalEmp.empno||’ ‘||‘ename=’||v_maxSalEmp.ename||’ '||‘sal=’||v_maxSalEmp.sal);
if (v_maxSalEmp.sal > v_avgSal) then
dbms_output.put_line(‘工资最高的人在’||v_maxSalEmpDept||‘部门’);
else dbms_output.put_line(‘各个部门工资相对比较平均’);
end if;
end;

（2）使用LOOP 循环在员工表中增加 10个新的员工编号 （8001 、8010 ）。
Use LOOP to add 10 new employee numbers (8001, 8010) to the employee table.
declare
x number(20):=0;
empno number(20):=8000;
begin
loop
x:=x+1;
insert into scott.emp(empno) values(empno+x);
exit when x = 10;
end loop;
end;
select * from scott.emp

（3）使用WHILE 循环在员工表中增加 10个新的员工编号 （8011 、8020 ）。
Use the WHILE loop to add 10 new employee numbers (8011, 8020) to the employee table.
declare
x number(20):=0;
empno number(20):=8011;
begin
while x<10
loop
insert into scott.emp(empno) values(empno+x);
x:=x+1;
end loop;
end;

（4）使用FOR 循环在员工表中增加 10个新的员工编号 （8021 、8030）。
Use the FOR loop to add 10 new employee numbers (8021, 8030) to the employee table.
declare
x number(4);
begin
for x in 8021…8030
loop
insert into scott.emp(empno) values(x);
end loop;
end;
select * from scott.emp

5·7.4 实训4：游标的使用 Training 4: use of cursors
〖实训目的 Training purpose〗
（1）进一步掌握PL/SQL 的语法： To master the syntax of PL/SQL:
（2）了解游标的使用。Understand the use of cursors.
〖实训内容 Training content〗

（1）用分支CASE WHEN 实现：接收两个数相除，如果除数为0，则显示错误提示；如果除数为1，则显示被除数；否则显示相除的结果。
Branch CASE WHEN implementation: receive the division of two numbers. If the divisor is 0, an error prompt will be displayed; If the divisor is 1, the dividend is displayed; Otherwise, the result of division is displayed.
declare
v_num1 int:=&a;
v_num2 int:=&b;
result int;
begin
result := v_num1/v_num2;
case
when (result=0) THEN
DBMS_OUTPUT.put_line(‘错误’);
when (result=1) THEN
DBMS_OUTPUT.put_line(‘被除数=’||v_num1);
else
DBMS_OUTPUT.put_line(result);
End case;
end;

（2）用LOOP 循环实现：循环输出 “****年月"，从2014 年1 月至2014 年12 月。
Realized by LOOP cycle: cycle output "****yearmonth", from January 2014 to December 2014.
declare
year number(4):=2014;
month int(4):=1;
begin
loop
DBMS_OUTPUT.put_line(year||‘年’||month||‘月’);
month:=month+1;
exit when month>12;
end loop;
end;

（3）查询输出前10个员工及其姓名，输出格式为“员工编号： ,姓名："，要求使用循环。
Query and output the first 10 employees and their names. The output format is “employee No.: ***, name: ***”. It is required to use cycle.
declare
cursor c1 is select ename, sal from scott.emp where rownum<11;
v1 varchar2(10);
v2 number(7,2);
begin
open c1;
fetch c1 into v1, v2;
while c1%found
loop
dbms_output.put_line(v1||‘员工的工资：’||v2 );
fetch c1 into v1, v2;
end loop;
close c1;
end;

（4）使用游标完成加薪任务：一般职工加薪10 ％（不得超过4000），项目经理加薪15％（不得超过8000），其他人员加薪12％（不得超过6000）。
Use the cursor to complete the salary increase task: the salary increase of general employees is 10% (no more than 4000), the salary increase of project managers is 15% (no more than 8000), and the salary increase of other personnel is 12% (no more than 6000).
set serverout on
declare
cursor c1 is select empno,job,sal from scott.emp;
v_empno scott.emp.empno%type;
v_job scott.emp.job%type;
v_sal scott.emp.sal%type;
begin
open c1;
fetch c1 into v_empno,v_job,v_sal;
while c1 % found
loop
if(v_job=‘SALESMAN’) then
if(v_sal+(v_sal0.1)<4000) then
update scott.emp set sal=sal+(v_sal0.1) where empno = v_empno;
else
update scott.emp set sal=4000;
end if;
elsif(v_job=‘MANAGER’) then
if(v_sal+(v_sal0.15)<8000) then
update scott.emp set sal=sal+(v_sal0.15) where empno = v_empno;
else
update scott.emp set sal=8000;
end if;
else
if(v_sal+(v_sal0.12)<6000) then
update scott.emp set sal=sal+(v_sal0.12) where empno = v_empno;
else
update scott.emp set sal=6000;
end if;
end if;
fetch c1 into v_empno,v_job,v_sal;
end loop;
close c1;
end;
select * from scott.emp
select * from scott.dept
5.7.5 实训5： 常处理 Practical training 5: regular processing
〖实训目的 Training purpose〗
（1）进一步掌握PL/SQL的语法： To further master the syntax of PL/SQL:
（2）学会异常处理。Learn exception handling.
〖实训内容 Training content〗

（1）查询所有部门信息，并以“部门在地方" 的格式输出所有记录。
Query all department information and output all records in the format of "* Department in *** location".
select * from scott.dept
declare
cursor c1 is select dname, loc from scott.dept;
v1 varchar2(30);
v2 varchar2(30);
begin
open c1;
fetch c1 into v1, v2;
while c1%found
loop
dbms_output.put_line(v1||‘部门在’||v2);
fetch c1 into v1, v2;
end loop;
close c1;
end;

（2）己知每个部门有一个经理，编写程序，统计输出部门名称、部门总人数、总工资和部门经理（输出格式为“部门总共有人，总工资为**，部门经理为***" ）。
It is known that each department has a manager, who writes a program to output the Department name, total number of people in the Department, total salary and Department Manager (the output format is "* * * Department has * * people in total, total salary is * * * and department manager is * * ").
declare
v_deptno number;
v_dept_name varchar2(20);
v_count number;
v_count_sal number;
v_dept_manager varchar2(20);
cursor c_dept is select deptno,count(),sum(sal) from scott.emp group by deptno;
begin
open c_dept;
dbms_output.put_line(‘--------------部门统计---------------’);
dbms_output.put_line('部门名称 '||'人员总数 '||'薪资总和 '||'部门经理 ');
loop
fetch c_dept into v_deptno,v_count,v_count_sal;
exit when c_dept%notfound;
select dname into v_dept_name from scott.dept where deptno = v_deptno;
select ename into v_dept_manager from scott.emp where deptno = v_deptno and job = ‘MANAGER’;
dbms_output.put_line(rpad(v_dept_name,15)||rpad(v_count,6)||rpad(v_count_sal,10)||rpad(v_dept_manager,6));
end loop;
close c_dept;
end;
select * from scott.dept
select * from scott.emp

（3）要求用户输入一个员工编号，之后根据输入的内容进行查询，查询员工的姓名，如果找不到该用户信息，则返回“没有此员工"。
It is known that each department has a manager, who writes a program to output the Department name, total number of people in the Department, total salary and Department Manager (the output format is “*** Department has ** people in total, total salary is *** and department manager is ***”).
declare
eno number;
en varchar2(30);
begin
eno:=&no;
select ename into en from scott.emp where empno=eno;
dbms_output.put_line(‘雇员的姓名为：’||en);
exception
when no_data_found then
dbms_output.put_line(‘没有此雇员：’);
end;

（4）实现接收部门编号的功能，根据编号显示部门名称和地理位置，若没有该部门编号则用异常来显示错误提示“无此部门！".
Realize the function of receiving department number, and display the Department name and geographical location according to the number. If there is no department number, an exception will be used to display the error prompt “no such department!”
declare
v_dname scott.dept.dname%type;
v_loc scott.dept.loc%type;
begin
select dname,loc into v_dname,v_loc from scott.dept where deptno = &deptno;
dbms_output.put_line(‘dname=’||v_dname||’ '||‘loc=’||v_loc);
exception
when NO_DATA_FOUND then
dbms_output.put_line(‘无此部门’);
end;

（5）参照以上过程，完成以下功能：接收一个部门编号，显示该部门的所有员工信息（用%ROWTYPE 实现），当这个部门没有员工时(no_data_found），用异常来显示错误提示“该部门不存在或无员工"；如果这个部门有多个员工，则用异常来显示提示“该部门员工超过1人".
Refer to the above process to complete the following functions: receive a department number and display all employee information of the Department (implemented with %ROWTYPE). When there are no employees in the Department (no_data_found), use exceptions to display the error prompt “the Department does not exist or has no employees”; if there are multiple employees in the Department, use exceptions to display the prompt “the Department has more than one employee”
declare
cursor mycur(p_deptno number) is select * from scott.emp where deptno = p_deptno;
dno scott.emp.deptno%type;
empinfo scott.emp%rowtype;
begin
dno:=&dno;
for empinfo in mycur(dno) loop
dbms_output.put_line(empinfo.empno||’ ‘||empinfo.ename||’ ‘||empinfo.job||’ ‘||empinfo.mgr||’ ‘||empinfo.hiredate||’ ‘||empinfo.sal||’ ‘||empinfo.comm||’ '||empinfo.deptno);
end loop;
end;
select * from scott.emp where deptno = 10;

5·8 习题 exercises

1. 判断题 (True/False)
   （1） PL/SQL 代码块中异常处理部分可有可无。（ False ） Exception handling part in PL/SQL code block is optional.
   （2） --为单行注释符，/…/伪多行注释符。（ True ） – Is a single line comment, /…/ Pseudo multiline annotator.
   （3）%FOUND 表示，当最近一次读记录时成功返回，则值为TRUE （ True ） %FOUND indicates that it is returned successfully when the record is last read, and the value is true
   （4）%ISOPEN 为布尔型属性，表示当最近一次读记录时成功返回，则值为TRUE （ False ） %ISOPEN is a Boolean property, which indicates that it is returned successfully when the record is last read, and the value is TRUE
   （5）打开游标的语句是 FETCH CURSOR （ False ） The statement that opens the cursor is FETCH CURSOR

2· 选择题 choice question
（1） PL/SQL 块由哪几个部分组成？（ D ） What are the parts of PL/SQL block?
A. DECLARE BEGIN END
B· BEGIN END
C· EXCEPTION BEGIN END
D· DECLARE BEGIN EXCEPTION END
（2） PL/SQL 代码段中的注释符号是（ C ）。 The comment symbol in the PL/SQL snippet is ( )
A. //
B. \
C. –
D. ’
（3）在PL/SQL 语句块中，跳出循环体所用的语句是（ C ） In the PL/SQL statement block, the statement used to jump out of the loop body is ()
A· BREAK
B. CONTINUE
C. EXIT
D. NEXT
（4）下列关于游标的说法，不正确的是（D）。The following statements about cursors are incorrect ()
A. 游标是从数据表中提取出来的数据 Cursors are data extracted from a data table
B. 游标以临时表的形式存放在内存中 Cursors are stored in memory as temporary tables
C. 在游标中有一个数据指针 There is a data pointer in the cursor
D. 游标在初始状态下指向的是首记录 The cursor points to the first record in the initial state
（5）在PL/SQL 代码段的异常处理块中，捕获所有异常的关键字是（ C ）。In the exception handling block of PL/SQL code segment, the keyword that catches all exceptions is ()
A. OTHERS
B. ALL
C. EXCEPTION
D. ERRORS
（6）当在一个PL/SQL 块中通过查询得到一个值并存储至某一变量，但是没有任何值返回时，会 （ B ）.- When a value is obtained through query in a PL/SQL block and stored in a variable, but no value is returned, it will ()
A. 不会有异常，只不过没有结果而己 There will be no exceptions, but there will be no results
B. 有异常，异常为NO_DATA_FOUND - There is an exception. The exception is No_DATA_FOUND
c. 有异常，异常为VALUE_ERROR - There is an exception. The exception is value_ERROR
D. 编译不能通过 - Compilation failed
（7）不属于游标的属性是（ C ）。 The property that does not belong to the cursor is ()
A. %NOTFOUND
B. %FOUND
C. %ROWTYPE
D· %ROWCOUNT

3. 综合题 Comprehensive questions

（1）编写PL/SQL 程序，计算1、100之间所有奇数之和。
set serverout on
declare
i number(10):=0;
sumresult number(10):=0;
begin
while i < 100
loop
i:=i+1;
if (i mod 2!=0) then
sumresult:=sumresult+i;
end if;
end loop;
dbms_output.put_line(‘result=’||sumresult);
end;
Write PL/SQL program to calculate the sum of all odd numbers between 1 and 100.

（2）编写一个程序块，利用％TYPE 属性，接收一个员工号，从 emp 表中显示该员工的整体薪水（薪水+ 佣金）。
Write a program block, use the %TYPE attribute to receive an employee number and display the employee’s overall salary (salary + commission) from the EMP table.
set serverout on
declare
v_empno scott.emp.empno%type:=&empno;
v_sal scott.emp.sal%type;
v_comm scott.emp.comm%type;
v_sum number(10);
begin
select sal,comm into v_sal,v_comm from scott.emp where empno = v_empno;
v_sum:=v_sal+nvl(v_comm,0);
dbms_output.put_line(v_sum);
end;
select * from scott.emp

（3）编写一个程序块，将emp表中的员工姓名全部显示出来。
Write a program block to display all the employee names in the EMP table.
declare
cursor c_ename is select * from scott.emp;
begin
dbms_output.put_line(‘姓名：’);
for x in c_ename
loop
dbms_output.put_line(x.ename);
end loop;
end;

（4）编写一个程序块，将emp 表中前5 人的名字显示出来。
Write a program block to display the names of the first 5 people in the EMP table
declare
cursor c_ename is select * from scott.emp where rownum <= 5;
begin
dbms_output.put_line(‘姓名：’);
for x in c_ename
loop
dbms_output.put_line(x.ename);
end loop;
end;

（5）编写一个程序块，接收一个员工名，从emp 表中显示该员工的工作岗位与薪水，若输入的员工名不存在，则显示“该员工不存在" .
Write a program block, receive an employee name, and display the position and salary of the employee from the EMP table. If the entered employee name does not exist, it will display “the employee does not exist” for approval
declare
v_job scott.emp.job%type;
v_sal scott.emp.sal%type;
v_ename scott.emp.ename%type := upper(‘&ename’);
begin
select job,sal into v_job,v_sal from scott.emp where ename=v_ename;
dbms_output.put_line(‘工作岗位：’||v_job||‘薪水:’||v_sal);
exception
when no_data_found then
dbms_output.put_line(‘该员工不存在’);
end;

（6）创建一个PL/SQL程序块，实现对表employees 给定一个employee_id, 可以把该员工的工资信息返回的功能。注意：要求有NO_DATA_FOUND异常处理，在异常处理部分输出“没有找到该employee_id 的员工"。
Create a PL/SQL program block to give an employee to the table employees_id, which can return the salary information of the employee. Note: no is required_DATA_Found exception handling: the exception handling section outputs “employee_id not found”.
declare
v_sal scott.emp.sal%type;
begin
select sal into v_sal from scott.emp where empno = &empno;
dbms_output.put_line(‘工资=’||v_sal);
exception
when no_data_found then
dbms_output.put_line(‘没有找到该employee_id 的员工’);
end;