PLSQL Day2

--8.编写一个程序，键盘输入学号，查询出对应学生姓名和所学科目数量：

declare
no scott.student.sno%type := '&学号';
name scott.student.sname%type;
cnt number;
begin
select s.sname,count(sc.cno)
into name,cnt
from student s left join sc
on s.sno = sc.sno
where s.sno = no
group by s.sname;
dbms_output.put_line('姓名 '||name||': 选课数 '||cnt);
end;
--9.编写一个 PL/SQL 程序块，键盘输入员工编号，
--以计算某个雇员的年薪（假设每个月的提成固定）：
declare
no number := &员工编号;
name char(20);
year_sal number;
begin
select empno,sum(sal+nvl(comm,0))*12
into no,year_sal
from scott.emp
where empno = no
group by empno;
dbms_output.put_line('员工编号'||no||': 年薪'||year_sal);
end;

select * from emp;

------------------------------------if - end if------------------------------------------
declare
--声名一个变量用来存放从键盘输入的值
v_str varchar2(20);
begin
--从键盘输入一个颜色字符串存入变量v_str
v_str := '&请输入一个颜色';
--判断输入的颜色是否为red，如果是red输出它是红色，
--如果不是输入其它颜色
if v_str = 'red' then
dbms_output.put_line('它是红色');
end if;
if v_str != 'red' then
dbms_output.put_line('其它颜色');
end if;
end;

------------------------------loop 先循环再判断------------------------------------------------

declare
--声名一个变量，用来存放一个数字
v_n number(10); --可以在此处直接赋值
begin
--给循环变量赋值为1
v_n := 1;
--循环语句
loop
--循环体执行的plsql语句
dbms_output.put_line(v_n);
--退出循环条件
exit when v_n = 5;
--改变循环变量的值，保证循环不会死循环（循环控制语句）
v_n := v_n+1;
end loop;
end;

------------------------------while 先循环再判断------------------------------------------------

declare
--声名一个变量，来控制循环
v_n number(10);
begin
--给循环变量赋值
v_n := 1;
--while循环语句
while v_n < 6 loop
--循环体语句（打印数字）
dbms_output.put_line(v_n);
--循环控制语句
v_n := v_n+1;
end loop;
end

------------------------------GOTO语句------------------------------------------------

declare
begin
for i in 1..9 loop
dbms_output.put_line(i);
if i=5 then
goto here;
end if;
end loop;
<<here>>
dbms_output.put_line('来玩啊');
end;
------------------------------
begin
dbms_output.put_line('hello1');
goto mylabel;
dbms_output.put_line('hello2');
dbms_output.put_line('hello3');
<<mylabel>>
dbms_output.put_line('hello4');
dbms_output.put_line('hello5');
end;

------------------------------------------------------------------------------

--1.键盘接受两个值，打印比较大的值：
declare
v1 number := &v1;
v2 number := &v2;
begin
if v1 > v2
then dbms_output.put_line(v1);
elsif v1 < v2
then dbms_output.put_line(v2);
else
dbms_output.put_line(v1||'='||v2);
end if;
end;

--2.键盘接受三个值，并按照从小到大依次从控制台输出：

declare
v1 number := &v1;
v2 number := &v2;
v3 number := &v3;
begin
if v1 > v2 and v2 > v3
then dbms_output.put_line(v3 ||'<'||v2 ||'<'||v1);
elsif v1 > v2 and v2 < v3 and v1 < v3
then dbms_output.put_line(v2 ||'<'||v1 ||'<'||v3);
elsif v1 > v2 and v2 < v3 and v1 > v3
then dbms_output.put_line(v2 ||'<'||v3 ||'<'||v1);
elsif v1 < v2 and v2 < v3
then dbms_output.put_line(v1 ||'<'||v2 ||'<'||v3);
elsif v1 < v2 and v2 > v3 and v1 > v3
then dbms_output.put_line(v3 ||'<'||v1 ||'<'||v2);
elsif v1 < v2 and v3 < v2 and v1 < v3
then dbms_output.put_line(v1 ||'<'||v3 ||'<'||v2);
end if;
end;

-- 方法二优解
declare
a number;
b number;
c number;
x number;
begin
a := &输入第一个值;
b := &输入第二个值;
c := &输入第三个值;
if a > b then
x := a;
a := b;
b := x;
end if;
if a > c then
x := a;
a := c;
c := x;
end if;
if b > c then
x := b;
b := c;
c := x;
end if;
dbms_output.put_line(a||','||b||','||c);
end;

--3.判断一个年份是不是闰年：

--公历年份是4的倍数，且不是100的倍数的，为闰年

--公历年份是整百数的，必须是400的倍数，才是闰年

declare
year number := &输入一个年份;
begin
if mod(year,4) = 0 and mod(year,100)<>0
then dbms_output.put_line(year||'是闰年');
elsif mod(year,400) = 0
then dbms_output.put_line(year||'是闰年');
else
dbms_output.put_line(year||'不是闰年');
end if;
end;

--4.体质指数（BMI）=体重（kg）÷身高^2（m）

偏瘦 <= 18.4

正常 18.5 ~ 23.9

过重 24.0 ~ 27.9

肥胖 >= 28.0

现要求输入体重和身高，求出体质指数所在范围：

declare
height number:=&"身高(m)";
weight number:=&"体重(kg)";
BMI number := weight / power(height,2);
begin
case when BMI<= 18.4
then dbms_output.put_line('偏瘦');
when BMI between 18.5 and 23.9
then dbms_output.put_line('正常');
when BMI between 24.0 and 27.9
then dbms_output.put_line('过重');
else dbms_output.put_line('肥胖');
end case;
end;

--5.输入一个数，判断是奇数还是偶数：
declare
n number := &输入一个数字;
begin
if mod(n,2) = 0
then dbms_output.put_line('偶数');
else
dbms_output.put_line('奇数');
end if;
end;

--6.输入一个数，判断这个数的平方根是否大于这个数除以2的值：
declare
n number := &输入一个数字;
begin
if sqrt(n) > n/2
then dbms_output.put_line('这个数的平方根大于这个数除以2的值');
else
dbms_output.put_line('这个数的平方根不大于这个数除以2的值');
end if;
end;

--7.读入一个整数，表示一个人的年龄。

如果小于6 岁，则输出“儿童”；

6 岁到13 岁，输出“少儿”；

14 岁到18 岁，输出“青少年”；

19 岁到35 岁，输出“青年”；

36 岁到50 岁，输出“中年”；

50 岁以上输出“中老年”。

declare
age number := &age;
begin
case when age<6
then dbms_output.put_line('儿童');
when age between 6 and 13
then dbms_output.put_line('少儿');
when age between 14 and 18
then dbms_output.put_line('青少年');
when age between 19 and 35
then dbms_output.put_line('青年');
when age between 36 and 50
then dbms_output.put_line('中年');
else dbms_output.put_line('中老年');
end case;
end;

--8.输入一个员工号，修改该员工的工资，如果该员工为10号部门，则工资加100元；
--若为20号部门，则工资加200元；若为30号部门，则工资加300元，否则增加500元：

select * from emp;
select * from emp_bak;
declare
no number:=&员工号;
dno number;
e_sal number;
begin
select deptno
into dno
from emp
where empno = no;
if dno = 10
then e_sal := 100 ;
elsif dno = 20
then e_sal := 200;
elsif dno = 30
then e_sal := 300;
else e_sal := 500;
end if;
update emp_bak set sal = sal+e_sal where empno = no;
end;

--9.根据输入的员工号，修改该员工工资，如果该员工工资低于1000元，则工资加200元，
--如果工资在1000~2000元之间，则工资加150元，如果工资在2000~3000元之间，则工资加100元，否则增加50元：
select * from emp;
select * from emp_bak;
declare
no number:=&员工号;
e_sal number;
money number;
begin
select sal
into e_sal
from emp
where empno = no;
if e_sal < 1000
then money := 200;
elsif e_sal between 1000 and 2000
then money := 150;
elsif e_sal between 2000 and 3000
then money := 100;
else money := 50;
end if;
update emp_bak set sal = sal + money where empno = no;
end;

/*（网易数据分析实习卷）好评率是会员对平台评价的重要指标。
现在需要统计2018年1月1日到2018年1月31日，用户'小明'提交的母婴类目"花王"品牌的好评率
（好评率=“好评”评价量/总评价量）:

用户评价详情表：a
字段：id（评价id，主键），create_time（评价创建时间，格式'2017-01-01'）， user_name(用户名称)，goods_id(商品id，外键) ，
sub_time（评价提交时间，格式'2017-01-01 23:10:32'），sat_name（好评率类型，包含：“好评”、“中评”、“差评”）

商品详情表：b
字段：good_id（商品id，主键），bu_name（商品类目）, brand_name(品牌名称)。
--没有数据，纯写逻辑
*/
select sum(decode(a.sat_name,'好评',1,0))/count(*)
from a join b on a.goods_id = b.good_id
where b.bu_name = '母婴' and brand_name = '花王' and user_name = '小明'
and a.sub_time between to_date('2018-01-01','yyyy-MM-dd') and to_date('2018-01-31','yyyy-MM-dd');

--10.编写一个 PL/SQL 程序块，根据员工scott入职的时间修改发放奖金列，大于等于6年则奖金为2000，小于6年的奖金是1500：
select * from emp;
select * from emp_bak;
declare
entrydate date;
begin
select hiredate
into entrydate
from emp_bak
where ename = 'SCOTT';
if months_between(sysdate,entrydate)>=6
then update emp_bak set comm = 2000 where ename = 'SCOTT';
else
update emp_bak set comm = 1500 where ename = 'SCOTT';
end if;
end;
--11.编写一个 PL/SQL 程序块，查询scott相应的工资级别并显示所在部门名称，薪水，和所在的工资级别：
declare
e_sal number;
e_grade number;
e_dname char(10);
begin
select d.dname,e.sal,s.grade
into e_dname,e_sal,e_grade
from emp e join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal
where ename = 'SCOTT';
dbms_output.put_line(e_dname||' '||e_sal||' '||e_grade);
end;

--1.编写一个程序块，将emp表中雇员名全部显示出来：
begin
for x in (select * from emp) loop
dbms_output.put_line(x.ename);
end loop;
end;

--2.编写一个程序块，将emp表中工资前5人的名字显示出来：
begin
for x in ( select * from ( select e.*,row_number()over(order by e.sal desc) r from emp e) where r < 6) loop
dbms_output.put_line(x.ename);
end loop;
end;

--3.编写一个PL/SQL程序块，从emp表中对名字以‘A’或‘S’开始的所有雇员按他们基本薪水的10%给他们加薪：
begin
for e in (select * from emp where ename like 'A%' or ename like 'S%') loop
update emp_bak set sal = sal + sal*0.1 where ename = e.ename;
end loop;
end;

--4.显示1-100之间的所有奇数（三种循环方式）：
-- loop
declare
i number := 1;
begin
loop
if mod(i,2) = 1
then dbms_output.put_line(i);
end if;
i := i+1;
exit when i > 100;
end loop;
end;

-- while
declare
i number := 1;
begin
while i <= 100 loop
if mod(i,2) = 1
then dbms_output.put_line(i);
end if;
i := i+1;
end loop;
end;
-- for
begin
for i in 1..100 loop
if mod(i,2) = 1
then dbms_output.put_line(i);
end if;
end loop;
end;

--5.将雇员表中的所有小于3000的工资增加400，并统计出增加工资的人数及增加的工资数量：
select * from emp;
select* from emp_bak;
declare
n number := 0; -- 人数
i_a number := 0; -- 工资
begin
for e in (select * from emp where sal < 3000) loop
n := n+1;
i_a := i_a + 400;
update emp_bak set sal = sal + 400 where empno = e.empno;
end loop;
dbms_output.put_line('增加工资的人数: '||n||',增加的工资数量为: '||i_a);
end;

--6.利用三种循环求1到100的偶数和：
-- loop
declare
i number := 1;
s number := 0;
begin
loop
if mod(i,2) = 0
then s := s + i;
end if;
i := i + 1;
exit when i > 100;
end loop;
dbms_output.put_line(s);
end;

-- while
declare
i number := 1;
s number := 0;
begin
while i <= 100 loop
if mod(i,2) = 0
then s := s + i;
end if;
i := i + 1;
end loop;
dbms_output.put_line(s);
end;

-- for
declare
s number := 0;
begin
for i in 1..100 loop
if mod(i,2) = 0
then s := s + i;
end if;
end loop;
dbms_output.put_line(s);
end;