Oracle-PL/SQL block structure exercises

Article Directory


1. Output text

set serveroutput on;
declare
begin
	dbms_output.put_line('输出文字!!!');
end;
/
Insert picture description here

2. Calculate the area of ​​the rectangle

declare
	v_length number:=&length;
	v_width number:=&width;
	v_area number;
begin
	dbms_output.put_line('长方形的面积为:'||v_length*v_width);
end;
/
Insert picture description here

3. Calculate the perimeter and area of ​​the rectangle with the block structure

declare
	v_length number:=&length;
	v_width number:=&width;
	v_area number;
begin
	declare
		v_cir number;
	begin
		v_cir := (v_width+v_length)*2;
		dbms_output.put_line('周长为:'||v_cir);
	end;
	v_area := v_length*v_width;
	dbms_output.put_line('面积为:'||v_area);
end;
/
Insert picture description here

4. Calculate the area of ​​the circle

declare
	v_r number:=&v_r;
	v_area number;
begin
	v_area := ACOS(-1)*v_r*v_r;
	dbms_output.put_line('圆面积为:'||v_area);
end;
/
Insert picture description here

5. Enter the student number to determine the student's scholarship level

-- 创建学生表
create table student(
sno number primary key,
grade number);

-- 插入数据
insert into student values(1, 70);
insert into student values(2, 90);

-- %TYPE 参考数据类型
-- select...into...赋值语句
declare 
v_sno student.sno %TYPE:=&sno;
v_grade student.grade %TYPE;
begin
	select avg(grade) into v_grade from student where sno = v_sno;
	case
		when v_grade > 85 then
			dbms_output.put_line('此同学平均成绩为:'||v_grade||',一等奖学金');
		when v_grade > 75 then
			dbms_output.put_line('此同学平均成绩为:'||v_grade||',二等奖学金');
		else
			dbms_output.put_line('此同学平均成绩为:'||v_grade||',没有奖学金');
	end case;
end;
Insert picture description here

6. Find the square from 1 to 10

Two ways to achieve:

Way 1:

declare 
	i number := 1;
begin	
	loop
		dbms_output.put_line(i||'的平方数为'||i*i);
		i:=i+1;
		exit when i > 10;
	end loop;	
end;

Way 2:

declare 
	i number := 1;
begin	
	loop
		dbms_output.put_line(i||'的平方数为'||i*i);
		i:=i+1;
		if i > 10 then
			exit;
		end if;
	end loop;	
end;
Insert picture description here

7. Find the factorial of 10

Three ways to achieve:

Way 1:

declare
	i number := 1;
	ans number := 1;
begin
	loop
		ans := ans * i;
		i := i + 1;
		exit when i >10;
	end loop;
	dbms_output.put_line('10的阶乘为'||ans);
end;

Way 2:

declare
	ans number := 1;
	i number := 1;
begin
	while i<=10 loop
		ans := ans*i;
		i := i + 1;
	end loop;
	dbms_output.put_line('10的阶乘为'||ans);
end;

Way 3:

declare
	ans number := 1;
begin 
	for i in 1..10 loop
		ans := ans*i;
	end loop;
	dbms_output.put_line('10的阶乘为'||ans);
end;
/
Insert picture description here

8. Calculate the absolute value of the expression (86+20×3-152)

declare
	v_abs number(8);	
begin 
	v_abs := abs(8+20*3-152);
	dbms_output.put_line('v_abs='||v_abs);
end;
Insert picture description here

9. Calculate the absolute value of (86+20×3-152), and then determine whether the value is greater than 80 (if else statement)

declare
	v_abs number(8);
begin
	v_abs := abs(86+20*3-152);
	if v_abs >= 80 then
		dbms_output.put_line('v_abs='||v_abs||'该值是大于等于80的');
	else
		dbms_output.put_line('v_abs='||v_abs||'该值是小于80的');
	end if;
end;
Insert picture description here

10. According to different academic achievements, output the corresponding grade category

According to different academic achievements, output the corresponding grade category: less than 60 means failing, 60 to 70 (exclusive) means passing, 70 to 85 (exclusive) means good, and greater than 85 means excellent. What level is 88 divided into?

if elsif statement mode:

declare
	grade number(5) := &grade;
begin
	if grade<60 then
		dbms_output.put_line('不及格'||grade);
	elsif 60<=grade and grade<70 then
		dbms_output.put_line('及格'||grade);
	elsif 70<=grade and grade<85 then
		dbms_output.put_line('良好'||grade);
	else
		dbms_output.put_line('优秀'||grade);
	end if;
end;
Insert picture description here

case when method:

declare
	v_grade number(5) := &grade;
begin
	case
		when v_grade<60 then dbms_output.put_line('不及格'||v_grade);
		when 60<=v_grade and v_grade<70 then dbms_output.put_line('及格'||v_grade);
		when 70<=v_grade and v_grade<85 then dbms_output.put_line('良好'||v_grade);
		else dbms_output.put_line('优秀'||v_grade);
	end case;
end;
/
Insert picture description here

11. LOOP loop, starting from 10, each loop will decrement the variable by 2 until the value of the variable is less than 1, and then terminate the loop

Two ways to achieve:

Simple cycle method:

declare
	i number(5) := 10;
	j number(5) := 1;
begin
	loop
		dbms_output.put_line('第'||j||'次循环,i = '|| i);
		i := i-2;
		j := j+1;
		exit when i<1;
	end loop;
end;

while loop mode:

declare
	i number(5) := 10;
	j number(5) := 1;
begin
	while i>1 loop
		dbms_output.put_line('第'||j||'次循环,i = '|| i);
		i := i-2;
		j := j+1;
	end loop;
end;
Insert picture description here

12. Enter the name of the fruit to query the corresponding number

Query the product code corresponding to the product name of apple from the fruit table FRUITS, and store the code in the variable v_ss_id, the final output screen, the running result is as follows: the code corresponding to apple is: 101, if the exception handling part is not found: apple There is no corresponding code, if there are multiple: Apple does not have a corresponding code (including the structure of the declaration part, the execution part and the exception handling part)

-- 创建水果表
CREATE TABLE fruits(
f_id    varchar2(10)     	NOT NULL,
s_id    number(6)        	NOT NULL,
f_name  varchar(255)  	NOT NULL,
f_price number (8,2)  	NOT NULL
);

-- 插入数据
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('a1', 101,'apple',5.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('b1',101,'blackberry', 10.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('bs1',102,'orange', 11.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('bs2',105,'melon',8.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('t1',102,'banana', 10.3);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('t2',102,'grape', 5.3);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('o2',103,'coconut', 9.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('c0',101,'cherry', 3.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('a2',103, 'apricot',2.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('l2',104,'lemon', 6.4);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('b2',104,'berry', 7.6);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('m1',106,'mango', 15.6);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('m2',105,'xbabay', 2.6);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('t4',107,'xbababa', 3.6);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('m3',105,'xxtt', 11.6);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('b5',107,'xxxx', 3.6);
declare
	v_ss_id fruits.s_id %TYPE ;
begin
	select s_id into v_ss_id from fruits where f_name = 'apple';
	dbms_output.put_line('apple对应的编号是:'|| v_ss_id);
	exception 
		when no_data_found then
			dbms_output.put_line('apple没有对应的编码');
		when too_many_rows then
			dbms_output.put_line('apple没有对应的编码');
end;
Insert picture description here

13. Use the search type CASE statement to find the fruit name corresponding to the fruit number

declare
	v_id fruits.f_id %TYPE := &f_id;
	v_name fruits.f_name %TYPE;
begin	
	select f_name into v_name from fruits where f_id = v_id;
	dbms_output.put_line('编号为:'||v_id);
	case v_name
		when 'banana' then
			dbms_output.put_line('香蕉'||v_name);
		when 'apple' then
			dbms_output.put_line('苹果'||v_name);
		else
			dbms_output.put_line('没有要找的水果');
	end case;
end;
Insert picture description here