1 -----创建一个序列，再创建一个表（主键是数字），通过序列生成该表的主键值。
2 create table mytb1(
3 m_id number primary key,
4 m_name varchar2(20) not null
5 )
6 create sequence myseq2
7 start with 1001
8 increment by 2
9 nomaxvalue
10 nocycle
11 cache 20;
12
13 declare
14 i integer;
15 begin
16 i :=1;
17 while i<=10 loop
18 insert into mytb1 values(myseq2.nextval,'德玛西亚');
19 i :=i+1;
20 end loop;
21
22 end;
23 select * from mytb;
24 ---创建表Student，其主键为数值类型：
25 drop table student;
26 create table student(
27 Stu_id number(6) primary key,
28 Stu_name varchar2(20) not null,
29 Stu_score number(3,1)
30 );
31 ---编写一个pl/sql语句块将100条记录插入表中
32 select * from user_sequences; --查询当前用户下的所有序列
33
34 begin
35 for i in 1..100 loop
36 insert into student values(myseq2.nextval,'德玛西亚',92.5);
37 end loop;
38 end;
39 select * from student;
40
41 ---编写一个pl/sql语句块计算表student的平均成绩，并打印
42 declare
43 rs number;
44 begin
45 select avg(Stu_score) into rs from student;
46 dbms_output.put_line(rs);
47 end;
48
49 ---编写一个pl/sql语句块，打印所有学生信息，如果成绩字段为null，显示为“无”
50 ---方法一：
51 begin
52 for stu in (select stu_id,stu_name, nvl(to_char(Stu_score),'无') stu_score from student) loop
53 dbms_output.put_line(stu.stu_id||','||stu.stu_name||','||stu.stu_score);
54 end loop;
55 end;
56 ---方法二
57 select stu_id, stu_name,
58 (
59 case
60 when stu_score is null then '无'
61 else to_char(stu_score)
62 end
63 ) stu_score
64 from student;
65
66 ---编写一个pl/sql语句块，打印成绩最高的20名学生信息
67 delete from student where stu_score not like 'null'; --删除有成绩的学生记录
68
69 begin
70 for i in 1..50 loop
71 insert into student values(myseq2.nextval,'Frank_Lei',trunc(DBMS_RANDOM.value(30,100),1));--插入30~100之间保留一位小数的随机成绩
72 end loop;
73 end;
74 select * from student;
75
76 declare
77 cursor cur
78 is
79 select * from student where rownum<=20 order by stu_score desc;
80 begin
81 for stu in cur loop
82 dbms_output.put_line(stu.stu_id||'...'||stu.stu_name||'...'||stu.stu_score);
83 end loop;
84
85 end;
86
87 ---编写一个pl/sql语句块，打印所有学生信息，成绩显示为“合格”、“不合格”和“无”三种
88 declare
89 cursor cur
90 is
91 select * from student;
92 begin
93 for stu in cur loop
94 case
95 when stu.stu_score<=0 then dbms_output.put_line(stu.stu_id||'...'||stu.stu_name||'...无');
96 when stu.stu_score>0 and stu.stu_score<60 then dbms_output.put_line(stu.stu_id||'...'||stu.stu_name||'...不合格');
97 else dbms_output.put_line(stu.stu_id||'...'||stu.stu_name||'...合格');
98 end case;
99
100 end loop;
101 end;
102
103
104 ---利用一条sql语句实现上题功能
105 select stu_id,stu_name,
106 (
107 case
108 when stu_score<=0 then '无'
109 when stu_score<60 and stu_score >0 then '不合格'
110 else '合格'
111 end
112 ) stu_score
113 from student;
114
115 ---编写一个pl/sql语句块，求阶乘
116 declare
117 temp number;
118 rst number;
119 begin
120 temp :=1;
121 rst :=1;
122 while temp<=4 loop
123 rst := rst*temp;
124 temp :=temp+1;
125 end loop;
126 dbms_output.put_line(rst);
127 end;