oracle 笔记

scott/tiger

system/manager

--创建一个临时表空间

create temporary tablespace bbb_temp

tempfile 'e:/bbb_temp.dbf'

size 10m

autoextend on;

--创建一个用户默认的表空间

create tablespace bbb_data

logging

datafile 'e:/bbb_data.dbf'

size 10m

autoextend on;

--创建一个用户并指定对应的表空间

create user bbb

identified by bbb

default tablespace bbb_data

temporary tablespace bbb_temp;

--授权

grant 

create session, create any table, create any view ,create 

any index, create any procedure,

alter any table, alter any procedure,

drop any table, drop any view, drop any index, drop any 

procedure,

select any table, insert any table, update any table, 

delete any table

to bbb;

grant connect,resource to bbb;

--查询一个用户下面有哪些表

select * from user_tables;

--字符类型

char(10)固定长度的10个字符

varchar2(10)可变长度的10个字符

long长文本

--数字类型

number(4,2)

--日期类型

date,sysdate获取当前日期

--查询系统的当前时间,包含dual单行单列的表

select sysdate from dual;

17-11月-09

rowid

 select rowid,ename from emp;

 rownum

可以使用rownum来进行分页查询

select * from (select a.*, rownum rn from 

(select * from emp) a where rownum <= 10) 

where rn >= 5;

--建表建约束

create table student

(

stuid number(4) constraint pk_stu_id primary key not null,

stuname varchar2(10) not null,

stuage number(2) constraint ck_stu_age check(stuage between 16 and 

45),

stuemail varchar2(20),

stuaddress varchar2(50)

);

--查看表结构

desc student;

--给stuemail添加检查约束

alter table student

add constraint ck_stu_email check(stuemail like '%@%');

--给stuaddress添加默认值

alter table student modify(stuaddress default '荆州');

--创建课程表

create table course

(

courseid number(2) not null,

coursename varchar2(10) not null

);

--给course表加主键

alter table course add constraint pk_course_id primary key(courseid);

--创建成绩表

create table score

(

scoreid number(4) constraint pk_score_id primary key not null,

stuid number(4) not null,

courseid number(2) not null,

score number(4,2) not null constraint ck_score_score check(score between 0 and 100),

constraint fk_student_score foreign key(stuid) references student(stuid),

constraint fk_course_score foreign key(courseid) references course(courseid)

);

--创建三个序列

create sequence student_seq start with 1001 increment by 1;

create sequence course_seq start with 1 increment by 1;

create sequence score_seq start with 1 increment by 1;

--使用序列增加测试数据

insert into student values(student_seq.nextval,'刘德华',30,'[e-mail]','沙市');

insert into student values(student_seq.nextval,'张学友',28,'[e-mail]',null);

insert into student values(student_seq.nextval,'范冰冰',18,'[e-mail]','公安');

insert into student values(student_seq.nextval,'张国荣',31,'[e-mail]','荆州');

insert into course values(course_seq.nextval,'java');

insert into course values(course_seq.nextval,'sql');

insert into course values(course_seq.nextval,'c#');

commit;

insert into score values(score_seq.nextval,1001,1,80);

insert into score values(score_seq.nextval,1001,2,54);

insert into score values(score_seq.nextval,1001,3,90);

insert into score values(score_seq.nextval,1002,1,34);

insert into score values(score_seq.nextval,1002,2,56);

insert into score values(score_seq.nextval,1002,3,78);

insert into score values(score_seq.nextval,1003,1,32);

insert into score values(score_seq.nextval,1003,2,78);

insert into score values(score_seq.nextval,1003,3,99);

commit;

--查询java课程的总成绩

select avg(score) java平均成绩 from score where courseid=1;

--查询每个学员的三门课的平均成绩

select stuid 学员编号,avg(score) 平均成绩 from score group by stuid;

--按总分从高到低排列

select stuid 学号,sum(score) 总分 from score group by stuid order by 总分 desc;

--查询每个学员的总成绩

select stuid 学号,sum(score) 总成绩 from score group by stuid;

--查询java成绩的前三名

select score 成绩 from score where courseid=1 and rownum<=3;

--查询地址为空的学员

select * from student where stuaddress is null;

--查询没有参加c#(3)考试的学员id

select stuid from student where stuid not in(select stuid from score where courseid=3);

--查询学员的sql成绩并显示学号、sql成绩、以及学员姓名

select stu.stuid 学号,sco.score 成绩,stu.stuname 姓名

from student stu inner join score sco

on(stu.stuid=sco.stuid);

--查询哪些学生没有参加考试

select stu.stuid 学号,sco.score 成绩,stu.stuname 姓名

from student stu left join score sco

on(stu.stuid=sco.stuid) where sco.score is null;

--等价于

select stu.stuid 学号,sco.score 成绩,stu.stuname 姓名

from student stu,score sco where stu.stuid=sco.stuid(+) and sco.score is null;

(+)在左边是右连接

--查询显示学员的java成绩，如果成绩<60分      显示’不及格’

   60<=成绩<80    显示’三等’

   80<=成绩<90    显示’二等’

   90<=成绩       显示’一等’

set serveroutput on;

declare

num number(4);

begin

num:=&请输入一个数;

case(num)

when 0 then dbms_output.put_line('不及格');

when 1  then dbms_output.put_line('三等');

when 2 then dbms_output.put_line('二等');

when 3  then dbms_output.put_line('一等');

else  dbms_output.put_line('成绩不正确');

end case;

end;

/

set serveroutput on;

declare

javascore score.score%type;

begin

--假设返回单条记录就可以接收，多条记录就报异常

select score into javascore from score where courseid=100;

if javascore<60 then 

dbms_output.put_line('不及格');

elsif javascore between 60 and 79  then

dbms_output.put_line('三等');

elsif javascore between 80 and 89 then

dbms_output.put_line('二等');

elsif javascore between 90 and 100 then

dbms_output.put_line('一等');

else

dbms_output.put_line('错误');

end if;

exception

when too_many_rows then

dbms_output.put_line('返回行数不只一行');

when no_data_found then

dbms_output.put_line('找不到需要的数据');

end;

/

set serveroutput on;

declare

javascore score.score%type;

--1、定义游标

cursor score_cur is select score from score where courseid=1;

begin

--2、打开游标

open score_cur;

--3、从游标里面取数据

loop

fetch score_cur into javascore;

if javascore<60 then 

dbms_output.put_line('不及格');

elsif javascore between 60 and 79  then

dbms_output.put_line('三等');

elsif javascore between 80 and 89 then

dbms_output.put_line('二等');

elsif javascore between 90 and 100 then

dbms_output.put_line('一等');

else

dbms_output.put_line('错误');

end if;

--游标结束

exit when score_cur%notfound;

end loop;

--4、关闭游标

close score_cur;

exception

when too_many_rows then

dbms_output.put_line('返回行数不只一行');

when no_data_found then

dbms_output.put_line('找不到需要的数据');

end;

/

循环

set serveroutput on;

declare 

i number(4):=0;

begin

loop

i:=i+1;

dbms_output.put_line('accp');

exit when i>10;

end loop;

end;

/

set serveroutput on;

declare 

i number(4):=0;

begin

while i<10 loop

i:=i+1;

dbms_output.put_line('accp');

end loop;

end;

/

set serveroutput on;

declare 

i number(4):=0;

begin

for i in 1..10 loop

dbms_output.put_line('accp');

end loop;

end;

/

--编写程序，显示从2到100之间的

set serveroutput on;

declare 

i number(4):=1;

j number(4):=1;

begin

loop

i:=i+1;

j:=1;

loop

j:=j+1;

exit when mod(i,j)=0;

end loop;

if i=j then

dbms_output.put_line(i);

end if;

exit when i>100;

end loop;

end;

/

--循环加分，java成绩普遍很低，现都给与加分，但是总分不能超过100分，直到所有人都及格，停止加分。

set serveroutput on;

declare 

num number(4):=1;

begin

while num>0 loop

update score set score=100 where score>=95 and courseid=1;

update score set score=score+5 where score<95 and courseid=1;

select count(*) into num from score where score<60 and courseid=1;

end loop;

commit;

end;

/

--使用游标查询并显示学员信息

set serveroutput on;

declare

stu_name student.stuname%type;

stu_age student.stuage%type;

--1、定义游标

cursor score_cur is select stuname,stuage from student;

begin

--2、打开游标

open score_cur;

--3、从游标里面取数据

loop

fetch score_cur into stu_name,stu_age;

dbms_output.put_line('姓名'||stu_name||',年龄'||stu_age);

--游标结束

exit when score_cur%notfound;

end loop;

--4、关闭游标

close score_cur;

exception

when too_many_rows then

dbms_output.put_line('返回行数不只一行');

when no_data_found then

dbms_output.put_line('找不到需要的数据');

end;

/

set serveroutput on;

declare

stu_row student%rowtype;

--1、定义游标

cursor score_cur is select * from student;

begin

--2、打开游标

open score_cur;

--3、从游标里面取数据

loop

fetch score_cur into stu_row;

dbms_output.put_line('姓名'||stu_row.stuname||',年龄'||stu_row.stuage);

--游标结束

exit when score_cur%notfound;

end loop;

--4、关闭游标

close score_cur;

end;

/

--使用参数游标传递学员学号，查询与之相关的信息

set serveroutput on;

declare

stu_row student%rowtype;

id student.stuid%type;

--1、定义游标

cursor score_cur(stu_id student.stuid%type) is select * from student where stuid>stu_id;

begin

--2、打开游标

id:=&输入学号;

open score_cur(id);

--3、从游标里面取数据

loop

fetch score_cur into stu_row;

dbms_output.put_line('姓名'||stu_row.stuname||',年龄'||stu_row.stuage);

--游标结束

exit when score_cur%notfound;

end loop;

--4、关闭游标

close score_cur;

end;

/

--编写过程，接收学员的学号，求出此学员的三门课的平均成绩打印输出。

create or replace procedure

findstudent 

(

stu_id number

)

as

stu_name student.stuname%type;

begin

select stuname into stu_name from student where stuid=stu_id;

dbms_output.put_line(stu_name);

end;

/

create or replace procedure findscore

(

stu_id number

)

as

cursor avg_score is select avg(score) from score where stuid=stu_id group by courseid;

score_avg score.score%type;

begin

open avg_score;

loop

fetch avg_score into score_avg;

dbms_output.put_line(score_avg);

exit when avg_score%notfound;

end loop;

close avg_score;

end;

/

--编写过程，带输入参数学号和课程编号，输出参数为这个学生这门课的成绩，如果缺考就为0

create or replace procedure findscore

(

stu_id in number,

course_id in number,

outscore out number

)

as

score1 number(4);

begin

select score into score1 from score where stuid=stu_id and courseid=course_id;

outscore:=score1;

exception

when no_data_found then

outscore:=0;

end;

/

调用

set serveroutput on;

declare

  stu_id score.stuid%type;

course_id score.courseid%type;

outscore score.score%type:=0;

begin

stu_id:=&输入学号;

course_id:=&输入课程编号;

findscore(stu_id,course_id,outscore);

dbms_output.put_line(outscore);

end;

/

--编写函数，接收课程的编号，求出这门课的总成绩并返回，调用此函数。

create or replace function 

findtotalscore(course_id number)

return number

as

stotal number(4);

begin

select sum(score) into stotal from score where courseid=course_id;

return stotal; 

end;

/

 select findtotalscore(1) from dual;

set serveroutput on;

declare

totalscore number(4);

begin

totalscore:=findtotalscore(&请输入课程号);

dbms_output.put_line(totalscore);

end;

/