PL/SQL常见例题

在开始接触PL/SQL时，遇到的一些经典的例题，包括循环，函数，游标等几个方面。

码字不易，点个关注给个赞吧

1.       编写PL/SQL程序块，输出100到110之间的所有素数。

代码：

declare

i int:=2;

j int:=100;

--GOTO label;

begin

while j<110 loop

j:=j+1;

i:=1;

while i<=j/2 loop

i:=i+1;

if mod(j,i)=0

then exit;

end if;

end loop;

if i>j/2

thendbms_output.put_line(j||'是素数');

end if;

end loop;

end;

 

 

 

2.       编写PL/SQL块，输入一个雇员编号，而后取得指定的雇员姓名、编号及所在部门名。

代码：

declare

type emp_dept_type is record(

var_ename emp.ename%type,

var_empno emp.empno%type,

var_dname dept.dname%type

);

emp_dept emp_dept_type;

begin

select ename,empno,dname intoemp_dept from emp,dept

where emp.deptno=dept.deptnoand emp.empno=&a;

dbms_output.put_line('ename'||emp_dept.var_ename||'empno'||emp_dept.var_empno||'dname'||emp_dept.var_dname);

end;

 

3.       编写一个PL/SQL块，将EMP表中名为SMITH的雇员的工资进行修改，若原工资大于$2000，则加$500，否则加$1000 。

代码：

declare

var_sal emp.sal%type;

begin

select sal into var_sal

from emp where empno=7566;

if var_sal>2000

then update emp set sal=sal+500where empno=7566;

elsif var_sal<2000

then update emp setsal=sal+1000 where empno=7566;

end if;

end;

 

 

4.       用户输入一个雇员编号，根据它所在的部门给上涨工资，规则：

n  10部门上涨10%，20上涨20%，30上涨30%；

n  但是要求最高不能超过5000，超过5000就停留在5000。

要求：利用用户自定义异常处理工资高于5000情况。

代码：

declare

esal emp.sal%type;

eno emp.empno%type:=&a;

dept1 emp.deptno%type;

begin

select deptno into dept1 fromemp where empno=eno;

if dept1=10

then update emp setsal=sal*1.1 where deptno=10;

elsif dept1=20

then update emp setsal=sal*1.2 where deptno=20;

elsif dept1=30

then update emp setsal=sal*1.3 where deptno=30;

end if;

select deptno into dept1 fromemp where empno=eno;

select max(sal) into esalfrom emp where deptno=dept1;

loop

if esal>5000

then update emp set sal=5000where sal=esal and deptno=dept1;

end if;

select max(sal) into esalfrom emp where deptno=dept1;

exit when esal<=5000;

end loop;

end；

 

 

5.       从控制台输入一个员工编号，声明一个记录类型（RECORD类型）emp_type，然后使用该类型的变量来存储emp表中的一条记录信息（只包括姓名、职务、工资及入职日期），并输出这条记录信息。

代码：

declare

type emp_type is record(

var_ename emp.ename%type,

var_job emp.job%type,

var_sal emp.sal%type,

var_hiredateemp.hiredate%type

);

empin emp_type;

begin

select ename,job,sal,hiredateinto empin from emp where empno=&a;

dbms_output.put_line('ename  '||empin.var_ename||'job  '||empin.var_job||'sal   '||empin.var_sal||'hiredate   '||empin.var_hiredate);

end;

 

6.       编写一个函数，根据部门号，对员工工资进行修改并返回该部门修改后的最高工资。修改原则为：如果是10部门，每个员工增加10%的工资，如果是20号部门，每个员工增加5%的工资，如果是30部门，每个员工增加3%的工资，其他部门的雇员工资增加1%的工资。

函数创建代码：

create or replace functionmyfunction1(eno emp.empno%type)

return number

is

null_exception exception;

esal emp.sal%type;

--eno emp.empno%type:=&a;

dept1 emp.deptno%type;

begin

--update emp set sal=sal*1.5where empno=eno;

select deptno into dept1 fromemp where empno=eno;

if dept1=10

then update emp setsal=sal*1.1 where deptno=10;

elsif dept1=20

then update emp setsal=sal*1.05 where deptno=20;

elsif dept1=30

then update emp setsal=sal*1.03 where deptno=30;

else update emp setsal=sal*1.01 where deptno=40;

end if;

select max(sal) into esalfrom emp where deptno=dept1;

dbms_output.put_line('the maxsal is:'||esal||'提升工资已完成');

return 1;

exception

when no_data_found then

dbms_output.put_line('查无此值');

end;

declare

a number;

begin

a:=myfunction1(7566);

end；

 

调用该函数

declare

a number;

begin

a:=myfunction1(7566);

end;

7.       声明一个游标（emp_info）用来读取部门号是20职工姓名、编号、所在部门名及职务。

代码：

declare

cursor empinfo(var_deptno innumber:=10)

is selectename,empno,dname,job from emp,dept where emp.deptno=dept.deptno

and emp.deptno=var_deptno;

type record_emp_dept isrecord(

var_ename emp.ename%type,

var_empno emp.empno%type,

var_dname dept.dname%type,

var_job emp.job%type);

emp_row record_emp_dept;

begin

open empinfo(10);

fetch empinfo into emp_row;

while empinfo%found loop

dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||'所在部门名是'||emp_row.var_dname||'职务是'||emp_row.var_job);

fetch empinfo into emp_row;

end loop;

close empinfo;

end;

 

 

 

 

8.      利用游标逐行输出员工姓名及工资级别。

代码：

declare

cursor empgrade

is select ename ,grade

from emp,salgrade

where emp.sal between losaland hisal;

type record_emp is record(

var_ename emp.ename%type,

var_gradesalgrade.grade%type);

emp_row record_emp;

begin

open empgrade;

fetch empgrade into emp_row;

while empgrade%found loop

dbms_output.put_line(emp_row.var_ename||'的工资等级是'||emp_row.var_grade);

fetch empgrade into emp_row;

end loop;

close empgrade;

end;

 

 

9.       使用函数统计SCOTT.EMP表每个部门的信息，包括部门名，员工总数及平均工资。

函数创建代码：

create or replace function myfunction

return number
is 
i int:=10;
var_dname dept.dname%type;
num number;
avg_sal emp.sal%type;
begin
for i in (select distinct deptno from emp) loop
select dname into var_dname from dept where deptno=i.deptno;
select count(empno) into num from emp where deptno=i.deptno;
select avg(sal) into avg_sal from emp where deptno=i.deptno;
dbms_output.put_line('部门名为'||var_dname||'的员工人数为'||num||'平均工资为'||avg_sal);
end loop;
return null;
end;
set serveroutput on
declare 
a number;
begin
a:=myfunction;
end;

 

 

调用该函数

set serveroutputon

declare

a number;

begin

a:=myfunction;

end;