Oracle课后练习-6

最新推荐文章于 2023-04-07 16:09:12 发布

迷茫的21世纪的新轻年

最新推荐文章于 2023-04-07 16:09:12 发布

阅读量1.9k

点赞数 1

分类专栏： Oracle 文章标签： oracle

本文为博主：迷茫的21世纪的新轻年的原创文章，未经博主允许不得转载。

本文链接：https://blog.csdn.net/qq_36986510/article/details/106721518

版权

Oracle 专栏收录该内容

8 篇文章

订阅专栏

1、定义一个匿名块，查询雇佣日期比雇员FORD早的职工人数，将查询结果放入变量v_count，并在屏幕上显示出来。要求显示结果格式如下：雇佣日期比雇员FORD早的职工人数有人（或者“The number of employees is *”）

declare
	v_count number;
begin
	select count(*) into v_count
	from emp
	where hiredate<(select hiredate from emp where ename='FORD');
	dbms_output.put_line('雇佣日期比雇员FORD早的职工人数有'|| v_count ||'人');
end;

2、定义一个匿名块，查询雇员BLAKE所在的部门，在屏幕上显示该部门的部门名和地址。要求分别用两种方法完成：自定义的记录类型变量和%rowtype定义变量

declare
	v_deptname varchar2(50);
	v_address varchar2(50);
begin
	select dname,loc into v_deptname,v_address
	from dept 
	where deptno=(select deptno from emp where ename='BLAKE');
	dbms_output.put_line('部门名：'|| v_deptname ||'，地址：'|| v_address);
end;

declare
	v_dept dept%rowtype;
begin
	select * into v_dept
	from dept 
	where deptno=(select deptno from emp where ename='BLAKE');
	dbms_output.put_line('部门名：'|| v_dept.dname ||'，地址：'|| v_dept.loc);
end;

3、定义一个匿名块，向部门表dept插入一条新部门记录。具体要求如下：

（1）部门名用你自己的名字，其他部门信息自行定义

（2）使用替代变量输入新的部门号、部门名和部门地址。

（3）执行完该匿名块后，其他会话也能查看到插入的这条记录。需要在其他会话中查询该记录以验证是否插入成功。

declare
begin
 insert into dept values(&v_deptno,'&v_dname','&v_loc');
 commit;
end;

4、按如下步骤实现功能：

（1）定义一个绑定变量v_pt

（2）定义一个匿名块，在匿名块中给绑定变量赋值上你的名字拼音，并在屏幕上输出该绑定变量v_pt。

begin
 :v_pt:='yourname';
 dbms_output.put_line(:v_pt);
end;

5、定义一个匿名块，实现如下功能：

（1）根据下面的要求声明两个变量V_CHAR和V_NUM，变量名称、类型以及值如下：

V_CHAR ：可变字符串，长度50，值为原义字符串 ‘42 is the answer’
V_NUM ：数值型，值为变量V_CHAR的头两个字符（必须使用函数获得）

（2）把这两个PL/SQL变量分别赋值给SQL *Plus绑定变量G_CHAR和G_NUM，执行该块。

（3）用命令输出SQL *Plus绑定变量的结果。

要求输出结果：

G_CHAR

42 is the answer

G_NUM

variable g_char varchar2(50);
variable g_num number;

declare 
	v_char varchar2(50):='42 is the answer';
	v_num number:=to_number(substr(v_char,0,2));
begin
	:g_char:=v_char;
	:g_num:=v_num;
	dbms_output.put_line('赋值成功！');
end;

Print g_char;
Print g_num;

6、创建一个脚本文件lab10_6.sql，要求在脚本文件中实现如下功能：

（1）通过替代变量从SQL *Plus中接收两个数，要求输入数据前的提示信息必须如下范例：

Please enter the first number: 2

Please enter the second number: 4

（2）定义一个匿名块，实现功能：

第一个数除以第二个数，结果加上第二个数，并将结果四舍五入保留2位小数（如果小数位上是0也要显示出来）
最后将结果分别存入一个PL/SQL变量v_result和一个SQL *Plus变量g_result中，并在屏幕上输出两个变量的结果，以确认两个变量的结果是否一致。

variable g_result number;

accept v_num number prompt 'Please enter the first number:'
accept v_num2 number prompt 'Please enter the second number:'

declare 
	v_result number;
begin
	v_result:=to_char(round(&v_num/&v_num2+&v_num2,2),'FM9990.00');
	:g_result:=v_result;
	dbms_output.put_line('两个变量的结果：' || v_result );
end;

7、编写一个PL/SQL匿名块，查询雇员SMITH的奖金情况，如果有奖金，则在屏幕输出“雇员SMITH有获得奖金”，否则在屏幕输出“雇员SMITH没有获得奖金”。

declare
	v_comm number ;
begin
	select comm into v_comm
	from emp
	where ename='SMITH';
	if v_comm >=0 then
	 dbms_output.put_line('雇员SMITH有获得奖金');
	else
	 dbms_output.put_line('雇员SMITH没有获得奖金');
	end if;
end;

8、编写一个PL/SQL匿名块，实现计算1+2+3+4+5+……+99+100的值，并在屏幕上显示求和结果（分别用三种循环方式实现（LOOP、WHILE、FOR））。

declare
	v_num number:=0;
	v_i number:=1;
begin
	loop
		v_num:=v_num+v_i;
		v_i:=v_i+1;
		if v_i=101 then
		 exit;
		end if;
	end loop;
	 dbms_output.put_line('求和结果：'||v_num);
end;

declare
	v_num number:=0;
	v_i number:=1;
begin
	while v_i<=100 loop
		v_num:=v_num+v_i;
		v_i:=v_i+1;
	end loop;
	 dbms_output.put_line('求和结果：'||v_num);
end;

declare
	v_num number:=0;
begin
	for v_i in 1..100
	loop
		v_num:=v_num+v_i;
	end loop;
	 dbms_output.put_line('求和结果：'||v_num);
end;

9、编写一个PL/SQL匿名块，向MESSAGES表中插入数字。按下面的步骤完成：

（1）先用如下语句创建一个表MESSAGES。

create table messages

(result varchar2(60));

（2）编写匿名块，要求在匿名块中实现如下功能：

在MESSAGE表中插入数字1到10，除了6和8不要插入。
在块结束前要求提交事务。（建议测试阶段不要提交事务，确定没有错误再提交）

（3）完成匿名块后，测试时查询下MESSAGES表，显示块的运行效果。

declare
	v_num number:=0;
begin
	for v_i in 1..10
	loop
		if v_i=6 then 
		 dbms_output.put_line('6不插入');
		elsif v_i=8 then
		 dbms_output.put_line('8不插入');
		else
		 insert into messages values (v_i);
		end if;
	end loop;

    commit;
end;

10、创建一个PL/SQL块，根据某个员工的工资，计算给该员工的奖金。按下面的步骤完成：

（1）先执行下列语句向EMP表插入一新记录，雇员名用你的姓名。

Insert into emp

Values(8000, ‘你的名字’, ‘CLERK’, 7698, SYSDATE, NULL, NULL, 10);

注意：该雇员的工资为NULL。

（2）在匿名块中要求实现如下功能（要求分别用if语句和case语句完成）：

通过SQL *Plus替代变量读取雇员编号。
通过雇员编号获得其工资，根据工资等级不同，计算该员工的奖金，并设置该员工的奖金。
如果该雇员工资小于1000美元，则该雇员的奖金为工资的10%。
如果该雇员工资在1000~1500美元之间，则该雇员的奖金为工资的15%。
如果该雇员工资大于1500美元，则该雇员的奖金为工资的20%。
如果该雇员工资为NULL，则该雇员的奖金为0。
最后要求提交事务。（建议测试阶段不要提交事务，确定没有错误再提交）

（3）完成该PL/SQL块后，用以下四个雇员编号（7369、7521、7698、8000）的雇员信息分别进行测试，最后通过查询雇员表的雇员号、雇员名、工资、奖金以验证更新后的奖金数额是否正确。

declare 
	v_empno emp.empno%type;
	v_sal emp.sal%type;
begin
	v_empno:=&v_empno;
	select sal into v_sal from emp where empno=v_empno;
	
	 if v_sal<1000 then
	  update emp set comm=v_sal*0.1 where empno=v_empno;
	 elsif v_sal<=1500 then
	  update emp set comm=v_sal*0.15 where empno=v_empno;
	 elsif v_sal>1500 then
	  update emp set comm=v_sal*0.20 where empno=v_empno;
	 else
	  update emp set comm=0 where empno=v_empno;
	 end if;

    commit;
end;

--这里sal用聚合函数，防止出现异常

declare 
	v_empno emp.empno%type;
	v_sal emp.sal%type;
begin
	v_empno:=&v_empno;
	select max(sal) into v_sal from emp where empno=v_empno;
	case 
	 when v_sal<1000 then update emp set comm=v_sal*0.1 where empno=v_empno;
	 when v_sal<=1500 then  update emp set comm=v_sal*0.15 where empno=v_empno;
	 when v_sal>1500 then  update emp set comm=v_sal*0.20 where empno=v_empno;
	 else update emp set comm=0 where empno=v_empno;
	end case;

    commit;
end;