1.编写一个PL/SQL 程序块以显示所给出雇员编号的雇员的详细信息。
declare
v1 emp%rowtype; ;/*声明变量v1 %rowtype:使该变量的类型与emp表中的整行相同*/
begin
select * into v1 from emp where empno=&员工编号; /*将emp表中查询出的结果保存到v1中*/
DBMS_OUTPUT.put_line(v1.empno);
DBMS_OUTPUT.put_line(v1.ename);
DBMS_OUTPUT.put_line(v1.job);
DBMS_OUTPUT.put_line(v1.mgr);
DBMS_OUTPUT.put_line(v1.hiredate);
DBMS_OUTPUT.put_line(v1.sal);
DBMS_OUTPUT.put_line(v1.comm);
DBMS_OUTPUT.put_line(v1.deptno);
end;
/*要想能输出结果，先要执行SQL>set serveroutput on; */
/*执行方法，在pl/sql中，用command window，然后将程序块复制到editor中，*/
/*点一下执行按钮，或者按F8，执行，再到dialog中输入左斜杠/*/
/*如下所示：*/
/* SQL> */
/* 16 / */
/* */
/* PL/SQL procedure successfully completed */
2.编写一个PL/SQL 程序块以计算某个雇员的年度薪水总额
declare
sumsal emp.sal%type;/*声明变量sumsal emp.sal%type:使该变量的类型与emp表sal字段类型相同*/
begin
select (sal+nvl(comm,0))*12 into sumsal from emp where empno=&员工编号;/*nvl函数表示如果第一个参数为空值，就返回第二个参数值，否则返回第一个参数*/
DBMS_OUTPUT.put_line(sumsal);
end;
declare
sumsal emp.sal%type;
begin
select (sal+nvl(comm,0))*12 into sumsal from emp where empno=&员工编号;
DBMS_OUTPUT.put_line('该员工的年度总薪水为：'||sumsal);--(注意输出字符串的格式)
end;
3.按照下列加薪比例执行
deptno raise(%age)
10 5%
20 10%
30 15%
40 20%
加薪的百分比是按他们现有的薪水为根据的。编写一个PL/SQL程序块以对指定的雇员加薪
declare
ndeptno emp.deptno%type;
begin
select deptno into ndeptno from emp where deptno=&部门号
case ndeptno
when 10 then update emp set sal=sal+sal*0.05 where deptno=10;
when 20 then update emp set sal=sal*1.1 where deptno=20;
when 30 then update emp set sal=sal*1.15 where deptno=30;
when 40 then update emp set sal=sal*1.2 where deptno=40;
end;
end;
declare
ndeptno emp.deptno%type;
begin
select deptno into ndeptno from emp where empno=&员工编号;
case ndeptno
when 10 then update emp set sal=sal+sal*0.05 where deptno=10;
when 20 then update emp set sal=sal*1.1 where deptno=20;
when 30 then update emp set sal=sal*1.15 where deptno=30;
when 40 then update emp set sal=sal*1.2 where deptno=40;
end case;
end;
declare
ndeptno emp.deptno%type;
nempno emp.empno%type;
begin
nempno=&员工编号;
select deptno into ndeptno from emp where empno=nempno;
case ndeptno
when 10 then update emp set sal=sal+sal*0.05 where deptno=10;
when 20 then update emp set sal=sal*1.1 where deptno=20;
when 30 then update emp set sal=sal*1.15 where deptno=30;
when 40 then update emp set sal=sal*1.2 where deptno=40;
end case;
end;
4.编写一个PL/SQL程序块以向"emp"表添加10个新雇员编号
declare
begin
for i in 1..10
loop
insert into emp(empno) values(i);
end loop;
end;
5.只使用一个变量来解决试验作业5.
6.接受两个数相除并且显示结果。如果第二个数为0,
则显示消息"didn't your mother tell you not to dibide by zero';
declare
num1 number;
num2 number;
res number;
begin
num1:=&第一个数;
num2:=&第二个数;
if num2=0 then
DBMS_OUTPUT.put_line('didn''t your mother tell you not to dibide by zero');
else
res:=num1/num2;
DBMS_Output.put_line(res);
end if;
end;