文章目录
一、PL/SQL的程序
procedure language/SQL:是 Oracle 对 sql 语言的过程化扩展,在 sql 命令语言中增加了过程处理语句(如分支、循环等),使得 sql 语句具有过程处理能力。
1.基本语法结构
declare
说明部分(变量说明、光标申明、例外说明)
begin
语句序列(DML语句)
exception
例外处理语句
end;
(1)说明部分
① 定义基本变量
类型:char, varchar2, date, number, boolean, long
如:
var1 char(15);
married boolean := true;
psal number(7,2);
演示:
--使用基本变量类型
declare
--定义基本变量类型
--数据类型
pnumber number(7,2);
--字符串变量
pname varchar2(20);
--日期变量
pdate date;
begin
pnumber := 1;
dbms_output.put_line(pnumber);
pname := 'zhangsan';
dbms_output.put_line(pname);
pdate := sysdate;
dbms_output.put_line(pdate);
dbms_output.put_line(pdate + 1);
end;
② 定义引用型变量
如: my_name emp.ename%type; (定义变量my_name的类型为emp表字段的ename类型)
演示:
--使用引用型变量
declare
--定义引用型变量;查询并打印7839的姓名和薪水
--pename varchar2(20);
--psal number;
pename emp.ename%type;
psal emp.sal%type;
begin
--得到78391的姓名和薪水
select ename,sal into pename,psal from emp where empno = 7839;
--打印
dbms_output.put_line(pename || '的薪水是:' || psal);
end;
③ 定义记录型变量
如:emp_rec emp%rowtype;
可以理解成对象,每一个属性代表每一列,emp_rec.ename
演示:
--使用记录型变量
declare
--定义记录型变量,代表的是一行
emp_rec emp%rowtype;
begin
--得到7839一行的信息
select * into emp_rec from emp where empno = 7839;
--打印
dbms_output.put_line(emp_rec.ename || '员工的薪水是:' || emp_rec.sal);
end;
(2)主体部分
① IF条件语句
IF 条件
THEN 语句1; 语句2;
END IF;
IF 条件 THEN 语句1;
ELSE 语句2;
END IF;
注意elsif的写法
IF 条件1 THEN 语句1;
ELSIF 条件2 THEN 语句2;
ELSE 语句3;
END IF;
演示:
declare
--定义变量报错用户从键盘输入的数字
pnum number := 3;
begin
--执行if语句进行条件判断
if pnum = 0 then dbms_output.put_line('输入的数字是0');
elsif pnum =1 then dbms_output.put_line('输入的数字是1');
elsif pnum = 2 then dbms_output.put_line('输入的数字是2');
else dbms_output.put_line('其他');
end if;
end;
plsql developer的sql窗口不支持: accept num prompt ‘请输入一个数字’;
② 循环语句
a.while循环
WHILE total <= 100 LOOP
…
total := total + salary;
END LOOP;
演示:
--循环打印1-10
declare
--定义标量
pnum number := 1;
begin
while pnum <= 10 loop
--循环体
dbms_output.put_line(pnum);
--变量+1
pnum := pnum + 1;
end loop;
end;
b.while循环
LOOP
EXIT WHEN [WHEN 条件]
…
END LOOP
演示:
declare
pnum number := 1;
begin
loop
--退出条件:循环变量大于10
exit when pnum > 10;
--打印该变量的值
dbms_output.put_line(pnum);
--循环变量
pnum := pnum + 1;
end loop;
end;
c.for循环
FOR i IN 1…10 LOOP
语句序列
END LOOP;
演示:
begin
for pnum in 1..10 loop
dbms_output.put_line(pnum);
end loop;
end;
2.光标
就是一个结果集,在pl/sql中使用光标来代替一个集合。
通过上面的学习我们知道了定义变量,假如查询的是个结果集,该如何记录,如:select ename into enames from emp; 无论使用基本类型,引用型还是记录型,都不能接收该结果集。
① 语法
CURSOR 光标名 [(参数名 数据类型)] IS SELECT 语句
如: cursor c1 is select ename from emp;
使用:
- 打开光标:
open c1;
(打开光标执行查询) - 取一行光标的值:
fetch c1 into pename;
(取一行到变量中) - 关闭光标:
close c1;
(关闭光标释放资源)
fetch的作用:1、把当前指针指向的记录返回;2、将指针指向下一条记录。
演示:(打印emp表中所有的员工名称及薪水)
/**
光标的属性
%found %notfound
*/
--打印员工的薪水
declare
--定义一个光标
cursor cemp is select ename,sal from emp;
--为光标定义对应得变量
pename emp.ename%type;
psal emp.sal%type;
begin
--打开光标
open cemp;
--循环取记录
loop
--取一条记录
fetch cemp into pename,psal;
--没有取到记录是退出
exit when cemp%notfound;
--取到的打印出来
dbms_output.put_line(pename||'薪水是:'||psal);
end loop;
--关闭光标
close cemp;
end;
② 实例演示
案例:按员工的职位涨工资,总裁+1000,经理+800,其他员工+400;
为了不改变emp表,create emp_01 as select * from emp;
declare
--定义光标代表给哪些员工涨工资
cursor cemp is select empno,ejob from emp_01;
pempno emp_01.empno%type;
pjob emp_01.ejob%type;
begin
--打开光标
open cemp;
loop
--取出一个员工
fetch cemp into pempno,pjob;
--定义循环退出条件
exit when cemp%notfound;
--判断员工的职位
if pjob='PRESIDENT' then update emp_01 set sal = sal + 1000 where empno = pempno;
elsif pjob='MANAGER'then update emp_01 set sal = sal + 800 where empno = pempno;
else update emp_01 set sal = sal + 400 where empno = pempno;
end if;
end loop;
--关闭光标
close cemp;
--对于oracle,默认的事务隔离级别是read committed
--事务的ACID
commit;
dbms_output.put_line('涨工资完成');
end;
③ 光标的属性
属性:
%found :fetch从光标中取到了值,%found为true,否则为false;
%notfound :和%found相反;
%isopen :判断光标是否打开;
%rowcount :影响的行数
演示:
--%isopen 演示光标是否打开
declare
cursor cemp is select ename,sal from emp;
begin
--打开光标
open cemp;
if cemp%isopen then
dbms_output.put_line('光标打开');
else
dbms_output.put_line('光标没有打开');
end if;
--关闭光标
close cemp;
end;
--%rowcount 演示影响的行数=====================================================
declare
cursor cemp is select ename,sal from emp;
pename emp.ename%type;
psal emp.sal%type;
begin
--打开光标
open cemp;
loop
fetch cemp into pename,psal;
exit when cemp%notfound;
--打印rowcount的值
dbms_output.put_line('rowcount is '||cemp%rowcount);
end loop;
--关闭光标
close cemp;
end;
④ 光标的限制
默认情况下,oracle数据库只允许在同一个会话中,打开300个光标。
SQL> show parameter cursor;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
---可以更改光标的限制
SQL> alter system set open_cursors=400 scope=both;
System altered
SQL> show parameter cursor;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 400
session_cached_cursors integer 50
SQL>
修改光标的限制:
alter system set open_cursors=400 scope=both;
scope的取值:both,memory(只更改当前实例,不更改参数文件),spfile(只更改参数文件,不更改当前实例,数据库需要重启)
⑤ 带参数的光标
CURSOR 光标名 [(参数名 数据类型)] IS SELECT 语句
--查询某个部门中员工的姓名
declare
--定义带参数的光标
cursor cemp(dno number) is select ename from emp where deptno = dno;
pename emp.ename%type;
begin
--打开光标 传递参数
open cemp(10);
loop
--取出每个员工的姓名
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
3.例外
① 系统例外
pl/sql中程序中的例外(相当于java中的异常)
- No_data_found (没有找到数据)
- Too_many_rows (select …into语句匹配多行)
- Zero_divide (零作为除数)
- Value_error (算术或转换错误)
- Timeout_on_resorce (等待资源超时)
演示:
--系统例外:no_data_found
declare
pename emp.ename%type;
begin
--查询员工号是12345的员工(不存在)
select ename into pename from emp where empno = '12345';
dbms_output.put_line('员工:' || pename);
exception
when no_data_found then dbms_output.put_line('没有找到该员工');
when others then dbms_output.put_line('其他例外');
end;
--系统例外:too_many_rows
declare
pename emp.ename%type;
begin
--所有部门号为10的员工(多行数据)
select ename into pename from emp where deptno = '10';
exception
when too_many_rows then dbms_output.put_line('select into匹配了多行');
when others then dbms_output.put_line('其他例外');
end;
--系统例外:zero_divide
declare
pnum number;
begin
pnum := 1 / 0;
exception
when zero_divide
then dbms_output.put_line('0不能作除数');
pnum := 0;
dbms_output.put_line('pnum:'|| pnum);
when others
then dbms_output.put_line('其他例外');
end;
--系统例外:value_error
declare
pnum number;
begin
pnum := 'aaa';
exception
when value_error then dbms_output.put_line('算术或转换异常');
when others then dbms_output.put_line('其他');
end;
② 自定义例外
定义变量,类型是 exception;
使用 raise 抛出自定义例外。
演示:自定义例外查询部门为50的员工姓名
--自定义例外:查询部门为50的员工姓名(不存在50部门)
declare
--定义光标,代表50号部门员工姓名
cursor cemp is select ename from emp where deptno = 50;
pename emp.ename%type;
--自定义例外
no_emp_found exception;
begin
--打开光标
open cemp;
--直接取一个员工的姓名
fetch cemp into pename;
--没有找到抛出例外
if cemp%notfound then
raise no_emp_found;
end if;
dbms_output.put_line(pename);
--关闭光标
--oracle区别于其他编程语言,当程序抛出异常断了时,会自动启动pmon(process monitor)进程来关闭资源
close cemp;
exception
when no_emp_found then dbms_output.put_line('没有找到该部门的员工');
when others then dbms_output.put_line('其他例外');
end;
二、程序设计方法
1.瀑布模型
- SQL语句
- 变量 :1、初始值多少
2、最终值如何得到
2.案例分析
① 案例一
统计在1980、1981、1982、1987年份入职的员工人数:
--统计每年入职的员工人数
/**
分析过程:
SQL语句:
select to_char(hiredate,'yyyy') from emp;
->结果集是集合-->光标-->循环-->eixt
变量 :1、初始值多少,2、最终值如何得到
每年入职的员工人数:
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
*/
declare
cursor cyears is select to_char(hiredate,'yyyy') from emp;
phiredate varchar2(4);
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
begin
--打开光标
open cyears;
loop
--取出一个员工的入职年份
fetch cyears into phiredate;
exit when cyears%notfound;
if phiredate = '1980' then count80 := count80 + 1;
elsif phiredate = '1981' then count81 := count81 + 1;
elsif phiredate = '1982' then count82 := count82 + 1;
elsif phiredate = '1987' then count87 := count87 + 1;
end if;
end loop;
--关闭光标
close cyears;
--输出结果
dbms_output.put_line('total:'||(count80 + count81 + count82 + count87));
dbms_output.put_line('1980:'|| count80);
dbms_output.put_line('1981:'|| count81);
dbms_output.put_line('1982:'|| count82);
dbms_output.put_line('1987:'|| count87);
end;
② 案例二
为员工涨工资,从最低工资涨起,每人涨10%,但工资总额不能超过5万元,请计算涨工资的人数和涨工资后的工资总额并输出。
/**
SQL语句
select empno,sal from emp order by sal;
-->光标 -->循环 -->退出条件:1.工资总额>5w 2.所有员工涨完
变量:1.初始值 2.如何得到
涨工资的人数:
countEmp number := 0;
涨后的工资总额:
salTotal number;
1.select sum(sal) into salTotal from emp;
2.涨后工资总额=涨前工资总额 + sal*0.1
*/
declare
cursor cemp is select empno,sal from emp_01 order by sal;
cempno emp_01.empno%type;
csal emp_01.sal%type;
--定义 涨工资的人数
countEmp number := 0;
--定义 涨后的工资总额
salTotal number;
begin
--当前工资总和赋值
select sum(sal) into salTotal from emp_01;
--打开光标
open cemp;
loop
--exit when salTotal > 50000;
--取出一个员工的工资
fetch cemp into cempno,csal;
exit when cemp%notfound;
--先计算总的,防止超出
if salTotal + (0.1 * csal) > 50000 then exit;
end if;
--人数+1
countEmp := countEmp + 1;
--修改该员工工资 涨工资总额 = 涨前的工资总额 + sal * 0.1
update emp_01 set sal = sal * (1 + 0.1) where empno = cempno;
salTotal := salTotal + (0.1 * csal);
end loop;
--关闭光标
close cemp;
commit;
--打印结果
dbms_output.put_line('人数:'|| countEmp || ' 涨后的工资总额:'|| salTotal);
end;
③ 案例三
实现按部门分段(<=3000,(3000,6000],>6000)统计各工资段的职工人数,以及各部门的工资总额,如下图:
/**
SQL语句
1,部门
select deptno from dept; 光标-->循环 --> 退出条件:notfound
2,部门中员工的薪水
select sal from emp where deptno=?; 带参光标 --> 循环 -->退出条件:notfound
变量 1,初始值 2,如何得到
每个分段的员工人数
count1 number;
count2 number;
count3 number;
每个部门的工资总额
saltotal number;
1 select sum(sal) into saltotal from emp where deptno = ?;
2 累加
*/
declare
--部门光标
cursor cdept is select deptno from dept;
pdeptno dept.deptno%type;
--部门员工薪水
cursor cemp(dept_no dept.deptno%type) is select sal from emp where deptno = dept_no;
psal emp.sal%type;
--记录各个分段员工人数
count1 number;
count2 number;
count3 number;
--总工资
saltotal number;
begin
--打开部门光标
open cdept;
loop
--取一个部门
fetch cdept into pdeptno;
exit when cdept%notfound;
count1 := 0;
count2 := 0;
count3 := 0;
saltotal:=0;
--取对应部门的员工薪水
open cemp(pdeptno);
loop
--取一个员工的薪水
fetch cemp into psal;
exit when cemp%notfound;
--判断薪水范围
if psal <= 3000 then count1:=count1+1;
elsif psal > 3000 and psal <= 6000 then count2:=count2+1;
else count3:=count3+1;
end if;
--薪水总和
saltotal := saltotal + psal;
end loop;
close cemp;
insert into msg values(pdeptno,count1,count2,count3,saltotal);
saltotal := 0;
end loop;
close cdept;
commit;
end;
三、其他案例
用plsql写一个程序,按系(系名)分段统计(成绩小于60分,60-85分,85分以上)“大学物理”课程各分数段的学生人数,以及各系学生的平均成绩。如下图:
所用到的表:T_STUDENT(学生表); T_DEPT(系别表);T_COURSE(课表);T_SC(成绩表)
/**
SQL语句
1.得到有哪些系
select dno,dname from t_dept; -->光标-->循环 -->退出条件:notfound
2.得到系中,选修了“大学物理”课程学生的成绩
select grace from t_sc where cno = (select cno from t_course where cname = ??)
and sno =any (select sno from t_student where dno = ??)
-->带参光标('大学物理',‘部门号’)-->循环-->退出条件:notfound
变量:1.初始值 2.如何得到
每个分段的人数
count1 number; count2 number; count3 number;
每个系选修了“大学物理”学生的平均成绩
avggrade number;
1,累加
2,select avg(grace) from t_sc where cno = (select cno from t_course where cname = ??)
and sno =any (select sno from t_student where dno = ??)
*/
declare
--系别光标
cursor cdept is select dno,dname from t_dept;
pdno t_dept.dno%type;
pdname t_dept.dname%type;
--课程成绩光标
cursor csc(p_cname t_course.cname%type,p_dno t_student.dno%type)
is select grade from t_sc where cno = (select cno from t_course where cname = p_cname)
and sno =any (select sno from t_student where dno = p_dno);
pgrade t_sc.grade%type;
--定义变量 各分段人数计数 和 平均分数
count1 number;
count2 number;
count3 number;
avggrade number;
--课程名称
pcourseName varchar2(20) := '大学物理';
begin
--打开系别光标
open cdept;
loop
--取一个系的信息
fetch cdept into pdno,pdname;
exit when cdept%notfound;
count1 := 0;
count2 := 0;
count3 := 0;
avggrade := 0;
--系中选修了大学物理成绩光标
open csc(pcourseName, pdno);
loop
--取一个学生成绩
fetch csc into pgrade;
exit when csc%notfound;
--判断成绩范围
if pgrade < 60 then count1:=count1 + 1;
elsif pgrade >= 60 and pgrade < 85 then count2:=count2+1;
else count3:=count3+1;
end if;
avggrade := avggrade + pgrade;
end loop;
close csc;
--保存当前结果
insert into msg1 values(pcourseName,pdname,count1,count2,count3,decode((count1 + count2+count3),0,0,round(avggrade/(count1 + count2+count3),3)));
commit;
end loop;
close cdept;
end;
提供上面例子用到的数据表脚本:
drop table T_SC;
drop table T_COURSE;
drop table T_STUDENT;
drop table T_TEACHER;
drop table T_DEPT;
create table T_DEPT
(
dno number(2),
dname varchar2(30),
director number(4),
tel varchar2(8)
);
create table T_TEACHER
(
tno number(4),
tname varchar2(10),
title varchar2(20),
hiredate date,
sal number(7,2),
bonus number(7,3),
mgr number(4),
T_DEPTtno number(2)
);
create table T_STUDENT
(
sno number(6),
sname varchar2(8),
sex varchar2(2),
birth date,
passwd varchar2(8),
dno number(2)
);
create table T_COURSE(
cno varchar2(8),
cname varchar2(20),
credit number(1),
ctime number(2),
quota number(3)
);
create table T_SC
(sno number(6),
cno varchar2(8),
grade number(3));
alter table T_DEPT add (constraint pk_T_DEPTtno primary key(dno));
alter table T_DEPT add (constraint dno_number_check check(dno>=10 and dno<=50));
alter table T_DEPT modify(tel default 62795032);
alter table T_STUDENT add (constraint pk_sno primary key(sno ));
alter table T_STUDENT add (constraint sex_check check(sex='男' or sex='女'));
alter table T_STUDENT modify(birth default sysdate);
alter table T_COURSE add (constraint pk_con primary key(cno));
alter table T_SC add (constraint pk_key primary key(cno,sno));
alter table T_TEACHER add (constraint pk_tno primary key(tno));
alter table T_SC add (foreign key(cno) references T_COURSE(cno));
alter table T_SC add (foreign key(sno) references T_STUDENT(sno));
alter table T_STUDENT add (foreign key(dno) references T_DEPT(dno));
alter table T_TEACHER add (foreign key(T_DEPTtno) references T_DEPT(dno));
insert into T_DEPT values(10,'计算机系',9469,'12345678');
insert into T_DEPT values(20,'自动化系',9581,'12345678');
insert into T_DEPT values(30,'无线电系',9791,'12345678');
insert into T_DEPT values(40,'信息管理系',9611,'12345678');
insert into T_DEPT values(50,'微纳电子系',2031,'12345678');
insert into T_TEACHER values(9468,'charles','professor',to_date('2004-12-17','yyyy-mm-dd'),8000,1000,null,10);
insert into T_TEACHER values(9469,'smith','professor',to_date('2004-12-17','yyyy-mm-dd'),5000,1000,9468,10);
insert into T_TEACHER values(9470,'allen','associate professor',to_date('2003-02-04','yyyy-mm-dd'),4200,500,9469,10);
insert into T_TEACHER values(9471,'word','professor',to_date('2004-12-17','yyyy-mm-dd'),8000,1000,9469,20);
insert into T_TEACHER values(9581,'jones','professor',to_date('2003-02-04','yyyy-mm-dd'),6500,1000,9468,20);
insert into T_TEACHER values(9582,'martin','associate professor',to_date('2005-09-28','yyyy-mm-dd'),4000,800,9581,20);
insert into T_TEACHER values(9583,'blake','lecture',to_date('2006-05-01','yyyy-mm-dd'),3000,300,9581,30);
insert into T_TEACHER values(9791,'clark','professo',to_date('2003-09-06','yyyy-mm-dd'),5500,null,9468,30);
insert into T_TEACHER values(9792,'T_SCott','asT_SCiate professor',to_date('2004-09-12','yyyy-mm-dd'),4500,null,9791,30);
insert into T_TEACHER values(9793,'baggy','lecturer',to_date('2004-11-17','yyyy-mm-dd'),3000,null,9791,30);
insert into T_TEACHER values(9611,'turner','professor',to_date('2005-08-09','yyyy-mm-dd'),6000,1000,9468,40);
insert into T_TEACHER values(9612,'adams','asT_SCiate professor',to_date('2004-12-01','yyyy-mm-dd'),4800,80,9611,40);
insert into T_TEACHER values(9613,'james','lecturer',to_date('2006-12-03','yyyy-mm-dd'),2800,200,9611,40);
insert into T_TEACHER values(2031,'ford','rofessor',to_date('2005-03-12','yyyy-mm-dd'),5500,null,9468,50);
insert into T_TEACHER values(2032,'milier','asT_SCiate professor',to_date('2005-12-01','yyyy-mm-dd'),4300,null,2031,40);
insert into T_TEACHER values(2033,'migeal','lecturer',to_date('2006-01-23','yyyy-mm-dd'),2900,null,2031,50);
insert into T_TEACHER values(2034,'peggy','lecturer',to_date('2007-01-23','yyyy-mm-dd'),2500,null,2031,50);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-01','yyyy-mm-dd'),1,'John','男','123456',10);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-02','yyyy-mm-dd'),2,'Jacob','男','123456',10);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-03','yyyy-mm-dd'),3,'Michael','男','123456',10);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-04','yyyy-mm-dd'),4,'Joshua','男','123456',10);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-05','yyyy-mm-dd'),5,'Ethan','男','123456',10);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-06','yyyy-mm-dd'),6,'Matthew','男','123456',20);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-07','yyyy-mm-dd'),7,'Daniel','男','123456',20);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-08','yyyy-mm-dd'),8,'Chris','男','123456',20);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-09','yyyy-mm-dd'),9,'Andrew','男','123456',30);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-10','yyyy-mm-dd'),10,'Anthony','男','123456',30);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-11','yyyy-mm-dd'),11,'William','男','123456',30);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-12','yyyy-mm-dd'),12,'Joseph','男','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-13','yyyy-mm-dd'),13,'Alex','男','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-14','yyyy-mm-dd'),14,'David','男','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-15','yyyy-mm-dd'),15,'Ryan','男','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-16','yyyy-mm-dd'),16,'Noah','男','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-17','yyyy-mm-dd'),17,'James','男','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-18','yyyy-mm-dd'),18,'Nicholas','男','123456',50);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-19','yyyy-mm-dd'),19,'Tyler','男','123456',50);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-20','yyyy-mm-dd'),20,'Logan','男','123456',50);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-21','yyyy-mm-dd'),21,'Emily','女','123456',10);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-22','yyyy-mm-dd'),22,'Emma','女','123456',10);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-23','yyyy-mm-dd'),23,'Madis','女','123456',10);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-24','yyyy-mm-dd'),24,'Isabe','女','123456',10);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-25','yyyy-mm-dd'),25,'Ava','女','123456',10);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-26','yyyy-mm-dd'),26,'Abigail','女','123456',20);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-27','yyyy-mm-dd'),27,'Olivia','女','123456',20);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-28','yyyy-mm-dd'),28,'Hannah','女','123456',20);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-29','yyyy-mm-dd'),29,'Sophia','女','123456',30);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-30','yyyy-mm-dd'),30,'Samant','女','123456',30);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-08-31','yyyy-mm-dd'),31,'Elizab','女','123456',30);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-07-01','yyyy-mm-dd'),32,'Ashley','女','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-07-02','yyyy-mm-dd'),33,'Mia','女','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-07-03','yyyy-mm-dd'),34,'Alexis','女','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-07-04','yyyy-mm-dd'),35,'Sarah','女','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-07-05','yyyy-mm-dd'),36,'Natalie','女','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-07-06','yyyy-mm-dd'),37,'Grace','女','123456',40);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-07-07','yyyy-mm-dd'),38,'Chloe','女','123456',50);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-07-08','yyyy-mm-dd'),39,'Alyssa','女','123456',50);
insert into T_STUDENT(birth,sno,sname,sex,passwd,dno) values(to_date('2010-07-09','yyyy-mm-dd'),40,'Brianna','女','123456',50);
insert into T_COURSE values('c001','数据结构',3,10,100);
insert into T_COURSE values('c002','Java语言',2,20,100);
insert into T_COURSE values('c003','数字电路',3,30,100);
insert into T_COURSE values('c004','模拟电路',3,40,100);
insert into T_COURSE values('c005','信号与系统',4,50,100);
insert into T_COURSE values('c006','C语言',3,60,100);
insert into T_COURSE values('c007','高等数学',5,70,100);
insert into T_COURSE values('c008','自动原理',1,80,100);
insert into T_COURSE values('c009','数理方程',3,90,100);
insert into T_COURSE values('c010','大学语文',2,61,100);
insert into T_COURSE values('c011','机械制图',3,52,100);
insert into T_COURSE values('c012','微机原理',3,43,100);
insert into T_COURSE values('c013','通信基础',3,74,100);
insert into T_COURSE values('c014','计算机原理',5,35,100);
insert into T_COURSE values('c015','数据库',3,86,100);
insert into T_COURSE values('c016','编译原理',2,97,100);
insert into T_COURSE values('c017','大学物理',2,38,100);
insert into T_COURSE values('c018','统计基础',4,50,100);
insert into T_COURSE values('c019','线性代数',4,70,100);
insert into T_COURSE values('c020','Linux基础',3,60,100);
insert into T_SC values(6,'c002',60);
insert into T_SC values(6,'c015',60);
insert into T_SC values(6,'c010',61);
insert into T_SC values(27,'c010',65);
insert into T_SC values(6,'c001',60);
insert into T_SC values(6,'c011',61);
insert into T_SC values(6,'c018',70);
insert into T_SC values(8,'c007',65);
insert into T_SC values(27,'c020',65);
insert into T_SC values(27,'c015',65);
insert into T_SC values(26,'c015',55);
insert into T_SC values(25,'c015',59);
insert into T_SC values(1,'c017',65);
insert into T_SC values(2,'c017',66);
insert into T_SC values(3,'c017',67);
insert into T_SC values(4,'c017',68);
insert into T_SC values(5,'c017',69);
insert into T_SC values(6,'c017',70);
insert into T_SC values(7,'c017',71);
insert into T_SC values(8,'c017',72);
insert into T_SC values(9,'c017',73);
insert into T_SC values(10,'c017',74);
insert into T_SC values(11,'c017',75);
insert into T_SC values(12,'c017',76);
insert into T_SC values(13,'c017',77);
insert into T_SC values(14,'c017',78);
insert into T_SC values(15,'c017',79);
insert into T_SC values(16,'c017',80);
insert into T_SC values(17,'c017',81);
insert into T_SC values(18,'c017',82);
insert into T_SC values(19,'c017',83);
insert into T_SC values(20,'c017',84);
insert into T_SC values(21,'c017',85);
insert into T_SC values(22,'c017',86);
insert into T_SC values(23,'c017',87);
insert into T_SC values(24,'c017',88);
insert into T_SC values(25,'c017',89);
insert into T_SC values(26,'c017',90);
insert into T_SC values(27,'c017',89);
insert into T_SC values(28,'c017',88);
insert into T_SC values(29,'c017',87);
insert into T_SC values(30,'c017',86);
insert into T_SC values(31,'c017',85);
insert into T_SC values(32,'c017',84);
insert into T_SC values(33,'c017',83);
insert into T_SC values(34,'c017',82);
insert into T_SC values(35,'c017',81);
insert into T_SC values(36,'c017',80);
insert into T_SC values(37,'c017',79);
insert into T_SC values(38,'c017',78);
insert into T_SC values(39,'c017',77);
insert into T_SC values(40,'c017',76);
commit;
来源:根据慕课网《Oracle数据库开发必备利器之PL/SQL基础》课程整理的学习笔记。