PL/SQL学习笔记（三）

-----创建一个序列，再创建一个表（主键是数字），通过序列生成该表的主键值。
create table mytb1(
       m_id number primary key,
       m_name varchar2(20) not null
)
create sequence myseq2
start with 1001
increment by 2
nomaxvalue
nocycle
cache 20;

declare 
      i integer;
begin
      i :=1;
      while i<=10 loop
      insert into mytb1 values(myseq2.nextval,'德玛西亚');
      i :=i+1;
      end loop;
  
end;
select * from mytb;
---创建表Student，其主键为数值类型：
drop table student;
create table student(
Stu_id number(6) primary key,
Stu_name varchar2(20) not null,
Stu_score number(3,1)
);
---编写一个pl/sql语句块将100条记录插入表中
select * from user_sequences;  --查询当前用户下的所有序列

begin
       for i in 1..100 loop
           insert into student values(myseq2.nextval,'德玛西亚',92.5);
       end loop;
end;
select * from student;

---编写一个pl/sql语句块计算表student的平均成绩，并打印
declare
     rs number;
begin
     select avg(Stu_score) into rs from student;
    dbms_output.put_line(rs);
end;

---编写一个pl/sql语句块，打印所有学生信息，如果成绩字段为null，显示为“无”
---方法一：
begin
    for stu in (select stu_id,stu_name, nvl(to_char(Stu_score),'无') stu_score from student) loop
            dbms_output.put_line(stu.stu_id||','||stu.stu_name||','||stu.stu_score);
    end loop;  
end;
---方法二
select stu_id, stu_name, 
    (
      case
                 when stu_score is null  then '无'
                 else to_char(stu_score)
      end
     ) stu_score  
from student;

---编写一个pl/sql语句块，打印成绩最高的20名学生信息
delete from student where stu_score not like 'null';            --删除有成绩的学生记录

begin
       for i in 1..50 loop
       insert into student values(myseq2.nextval,'Frank_Lei',trunc(DBMS_RANDOM.value(30,100),1));--插入30~100之间保留一位小数的随机成绩
       end loop;
end;
select * from student;

declare 
       cursor cur
       is
       select * from student where rownum<=20 order by stu_score desc;
begin
       for stu in cur loop
           dbms_output.put_line(stu.stu_id||'...'||stu.stu_name||'...'||stu.stu_score);
       end loop;
       
end;

---编写一个pl/sql语句块，打印所有学生信息，成绩显示为“合格”、“不合格”和“无”三种
declare
       cursor cur
       is
       select * from student;
begin
       for stu in cur loop
               case
                      when stu.stu_score<=0 then dbms_output.put_line(stu.stu_id||'...'||stu.stu_name||'...无');
                      when stu.stu_score>0 and stu.stu_score<60 then dbms_output.put_line(stu.stu_id||'...'||stu.stu_name||'...不合格');
                      else dbms_output.put_line(stu.stu_id||'...'||stu.stu_name||'...合格');
               end case;  
               
       end loop;
end;


---利用一条sql语句实现上题功能
select stu_id,stu_name,
       (
           case
                  when stu_score<=0 then '无'
                  when stu_score<60 and stu_score >0 then '不合格'
                  else '合格' 
           end
       ) stu_score
from student;

---编写一个pl/sql语句块，求阶乘
declare 
     temp number;
     rst number;
begin
     temp :=1;
     rst :=1;
     while temp<=4 loop
           rst := rst*temp;
           temp :=temp+1;
     end loop;
     dbms_output.put_line(rst);
end;