Oracle常用语句

一、建立一个用户xiaoming ，密码a1234，授予xiaoming 创建视图的权限。
 create user xiaoming identified by a1234;
 grant create view to xiaoming;


二、建立student表，该表主要描述学生的学号，姓名，出生日期，性别，院系编号。
create table student
 (学号 number primary key,
  姓名 varchar2(20),
  出生日期 date,
  性别 varchar2(5) check(性别='男' or 性别='女'),
  院系编号 number;
 )


三、建立course表，该表主要描述课程号，课程名，先修课号，学分。
create table course
(课程号 number primary key,
 课程名 varchar2(20),
 先修课号 number,
 学分 number
);


四、建立score表，该表主要描述学生的学号，课程号，成绩
create table score
(学号 number,
 课程号 number,
 成绩 number,
constraint fk_a foreign key(学号) references student(学号),
constraint fk_b foreign key(课程号) references course(课程号)
)


五、通过insert命令向每个表插入至少3条记录。（要求学生性别为'男'或'女'。注意定义表的主键外键。）
insert into student values(1,'张一','01-5月-2006','男',11);
 insert into student values(2,'张二','01-4月-2006','女',22);
 insert into student values(3,'张三','01-3月-2006','女',33);


insert into course values(111,'课程一',111,10);
insert into course values(222,'课程二',111,10);
insert into course values(333,'课程三',111,10);


insert into score values(1,111,20);
insert into score values(2,222,20);
insert into score values(3,333,30);


六、建立一个序列从1000开始，每次递增1。利用序列给student表插入3条记录（序列作为学号）。
 create sequence seq_stu start with 1000 increment by 1;
 insert into student values(seq_stu.nextval,'张四','01-5月-2006','男',44);
 insert into student values(seq_stu.nextval,'张五','01-5月-2006','男',55);
 insert into student values(seq_stu.nextval,'张六','01-5月-2006','男',66);


七、创建视图，视图中有学生的学号，姓名，课程名，成绩，学分
 create view view1
 as select student.学号,student.姓名,course.课程号,score.成绩,course.学分
 from student,course,score
 where student.学号=score.学号 and course.课程号=score.课程号


八、创建视图，求每个学生的平均学分（包含姓名，平均学分）
 create view view2
 as select student.姓名,avg(学分) as 平均学分
 from view1
 group by 姓名;


九、利用循环实现1-100能被5整除的数的和。
 set serveroutput on
 declare 
 s integer :=0;
 i integer :=1;
 begin
 while i<=100 loop
 if mod(i,5)=0 then
 s:=s+i;
 end if;
 i:=i+1;
 end loop;
 dbms_output.put_line('1到100能被5整除的数和为: '||s);
 end;


十、求斐波纳序列前20项。
declare
f1 integer :=1;
f2 integer :=1;
f integer;
cnt integer :=3;
begin
dbms_output.put_line(1);
dbms_output.put_line(1);
while cnt<=20 loop
f:=f2+f1;
f1:=f2;
f2:=f;
dbms_output.put_line(f);
cnt:=cnt+1;
end loop;
end;


一 、建立3个表，分别是商品表，会员表，订单表，商品表的属性有商品号，商品名，
单价，数量，厂名，产地。会员表属性有会员号，姓名，性别，年龄，电话。订单
表的属性有会员号，商品号，数量，单价，日期（注意定义主键与外键），性别字段只
能取’男’或’女’，通过insert语句在每个表插入5条数据。
create table 商品表
(商品号 number primary key,
 商品名 varchar2(20),
 单价 number,
 数量 number,
 厂名 varchar2(20),
 产地 varchar2(20)
);


create table 会员表
(会员号 number primary key,
 姓名 varchar2(20),
 性别 varchar2(10) check(性别='男' or 性别='女'),
 年龄 number,
 电话 number
);


 create table 订单表
 (会员号 number,
  商品号 number,
  数量 number,
  单价 number,
  日期 date);
alter table 订单表 add constraint fk_add1 foreign key(会员号) references 会员表(会员号);
alter table 订单表 add constraint fk_add2 foreign key(商品号) references 商品表(商品号);


insert into 商品表 values(1,'1商品',1,1,'1厂','1产地');
insert into 商品表 values(2,'2商品',2,2,'2厂','2产地');
insert into 商品表 values(3,'3商品',3,3,'3厂','3产地');
insert into 商品表 values(4,'4商品',4,3,'4厂','4产地');
insert into 商品表 values(5,'5商品',5,5,'5厂','5产地');


insert into 会员表 values(11,'1会员','男',11,111);
insert into 会员表 values(22,'2会员','男',22,222);
insert into 会员表 values(33,'3会员','男',33,333);
insert into 会员表 values(44,'4会员','男',44,444);
insert into 会员表 values(55,'5会员','男',55,555);


insert into 订单表 values(11,1,1,1,'01-1月-2000');
insert into 订单表 values(22,2,1,1,'01-1月-2000');
insert into 订单表 values(33,3,1,1,'01-1月-2000');
insert into 订单表 values(44,4,1,1,'01-1月-2000');
insert into 订单表 values(55,5,1,1,'01-1月-2000');


二、 对会员表表建立以姓名为索引关键字的降序索引。
  create index in_dex1 on 会员表(姓名 desc);


三、 建立一个序列从1000开始，每次递增1。利用序列给会员表插入3条记录（序列作为会员号）。
create sequence seq_hyh  start with 1000 increment by 1; 
insert into 会员表 values(seq_hyh.nextval,'6会员','男',66,666);

insert into 会员表 values(seq_hyh.nextval,'7会员','男',77,777);
insert into 会员表 values(seq_hyh.nextval,'8会员','男',88,888);


四、 建立会员表的同义词customer。
 create synonym customer for 会员表;


五、 基于订单表建立视图，该视图可以统计各商品的的数量（以商品和数量作为结果列）。
  create view view1
as select 商品号,sum(数量) as 总数量 from 订单表 group by 商品号;


六、 基于订单表建立存储过程，通过商品的种类来统计商品的平均价格(要求
传入商品号，输出平均价格)。
create or replace procedure pjjg(sph in number,v_jg out number)
is
begin
select avg(单价) into v_jg from 订单表 where 商品号=sph;
end;


七、 在商品表上创建一个触发器，当更新时候，商品的价格大于1000元的时候，价格下降
到90%，不大于1000的时候下降到95%。(scott下)
create or replace trigger spb_update
before update on 商品表
for each row
when (new.单价>old.单价)
begin
if :old.单价>1000 then
:new.单价:=0.9*:new.单价;
else
:new.单价:=0.95*:new.单价;
end if;
end;


八、 在商品表上建立触发器，当对数据进行更新时，
记录更新的时间，动作（提示：建立一张表updatetime，包括更新时间和行为两个字段，
最后请更新一条记录验证触发器是否建立成功）。（10分）
create table updatetime
(opertime date,iperation varchar2(20));


create or replace trigger time_update
after update on 商品表
begin
if updating then
insert into updatetime values(sysdate,'update');
end if;
end;
/


九、 定义一个游标访问商品表中的价格最高的前3条的信息（输出商品名，价格）。
 declare
 v_name varchar2(20);
 v_price number;
 cursor spb_cursor is select 商品名,单价 from 商品表 order by 单价 desc;
 begin
 open spb_cursor;
 for i in 1..3 loop
 fetch spb_cursor into v_name,v_price;
 dbms_output.put_line(v_name||','||v_price);
 end loop;
 close spb_cursor;
 end;
 /


十、 定义一个函数如果商品的价格大于等于1500元,则显示“价格很贵”，否则 显示
“价格适中”(传入参数是商品名) 。
 create or replace function pri_ce
 (spm varchar2) return varchar2
 is 
 price number;
 begin
 select 单价 into price from 商品表 where 商品名=spm;
 if price>=1500 then
 return '价格很贵';
 else
 return '价格适中';
 end if;
 end;
 /