Oracle 课程设计源码

 创建主表空间：

create tablespace test
datafile 'D:\OracleSpace\test' size 20m
extent management local;

创建用户，使用主表空间（用户名:haige  密码：123456）

create user haige
identified by 123456
default tablespace test
quota 20m on test
temporary tablespace temp;

给haige用户授予其create session权限

grant create session,create table to haige;

给用户创建存储过程的权限

grant create any procedure to haige;

给用户执行存储过程的权限

grant execute any procedure to haige;

给用户操作触发器的权限

grant create trigger to haige;

使用profile管理密码，失败三次，Oracle会自动锁定该账户

create profile lock_account limit
failed_login_attempts 3
password_lock_time 10;

alter user haige profile lock_account;

设置密码过期时间（控制用户的密码有效期为30天，密码宽限期为1天）

create profile password_life_time limit
password_life_time 30
password_grace_time 1;

创建4块表空间

SQL> create tablespace space01
  2  datafile 'D:\OracleSpace\space01' size 10m
  3  extent management local;

SQL> create tablespace space02
  2  datafile 'D:\OracleSpace\space02' size 10m
  3  extent management local;

SQL> create tablespace space03
  2  datafile 'D:\OracleSpace\space03' size 10m
  3  extent management local;

SQL> create tablespace space04
  2  datafile 'D:\OracleSpace\space04' size 10m
  3  extent management local;

使用haige用户创建数据表

create table user1(
user_id number(10) constraint user_pk primary key,
blog_id number(10) constraint blog_user_pk references blog(blog_id),
user_name varchar2(30) not null,
user_pwd varchar2(20) not null,
user_sex varchar2(6) not null,
user_email varchar2(50) not null,
user_picture blob,
user_basicInfo clob
);

create table blog(
 blog_id number(10) constraint blog_pk primary key,
 blog_title varchar2(50) not null,
 blog_logo varchar2(50) not null
 );

 create table category(
 category_id number(10) constraint category_pk primary key,
 blog_id number(10) constraint blog_category_pk references blog(blog_id),
 category_title varchar2(50) not null
 );

范围分区建表
create table article(
  article_id number(10) constraint article_pk primary key,
  article_random number(30) not null,
  category_id number(10) not null constraint article_category_pk references category(category_id),
  article_title varchar2(200) not null,
  article_date date default sysdate,
  article_content clob,
  article_summary clob,
  article_acessNum number(15)  default 0,
  artocle_reviewNum number(15) default 0
) partition by range(article_date)(
  partition part_01 values less than(to_date('2017-04-01','yyyy-mm-dd'))
 tablespace space01,
  partition part_02 values less than(to_date('2017-07-01','yyyy-mm-dd'))
 tablespace space02,
  partition part_03 values less than(to_date('2017-10-01','yyyy-mm-dd'))
  tablespace space03,
   partition part_04 values less than(maxvalue) tablespace space04
  );

散列分区建表
create table review(
  review_id number(10) constraint review_pk primary key,
  article_id number(10) not null constraint review_article_pk references article(article_id),
  review_content clob not null,
  review_datetime date default sysdate,
  user_id number(10) not null constraint review_user_pk references user1(user_id)
  ) partition by hash(review_id,review_datetime)
 (
   partition part_01 tablespace space01,
   partition part_02 tablespace space02,
   partition part_03 tablespace space03,
   partition part_04 tablespace space04
 );

create table album(
   album_id number(10) constraint album_pk primary key,
   user_id number(10) not null constraint album_user_pk references   user1(user_id),
   album_name varchar2(50) not null
 );

create table picture(
   picture_id number(10) not null constraint picture_pk primary key,
   picture_doc varchar2(50) not null,
   picture_file varchar2(60) not null,
   album_id number(10) not null constraint picture_album_pk references  album(album_id),
   picture_name varchar2(255) not null
 );

数据操作:

insert into blog(blog_id,blog_title,blog_logo) values(1,'海哥','图片1');

insert into category(category_id,blog_id,category_title) values(1,1,'烟台风光');

Insert into user1(user_id,blog_id,user_name,user_pwd,user_sex,user_email,user_basicInfo) values(1,1,'海哥','123456','男','1150979146@qq.com','一个积极向上的小伙');

insert into article(article_id,article_random,category_id,article_title,article_content,article_summary) values(1,23131543,1,'烟台的雪','三月的天，烟台还是下起了漫天大雪','三月的天');

insert into review(review_id,article_id,review_content,user_id) values(1,1,'写得很好！',1);

insert into album(album_id,user_id,album_name) values(1,1,'烟台大学');

insert into picture(picture_id,picture_doc,picture_file,album_id,picture_name) values(1,'烟大东门','D:\OracleSpace',1,'烟大');

创建存储过程

create or replace procedure add_blog(
 id_param in number,
 title_param in varchar2,
 logo_param in varchar2) is
 begin
 insert into haige.blog(blog_id,blog_title,blog_logo)
 values(id_param,title_param,logo_param);
 end add_blog;
/

传递参数调用存储过程add_blog
exec add_blog(2,'杨旭','图片2');

创建查询存储过程

create or replace procedure search_blog(
 id_param in number,
 title_param out blog.blog_title%type,
 logo_param out blog.blog_logo%type) is
 begin
 select blog_title,blog_logo
 into title_param,logo_param
 from haige.blog
 where blog_id=id_param;
 exception
 when no_data_found then
    title_param:='null';
    logo_param:='null';
    dbms_output.put_line('未找到指定编号的博客信息！');
 end search_blog;
 /

创建序列号（序列号+触发器实现主键自增长）

create sequence blog_id_seq
maxvalue 200
start with 2
increment by 1
cache 2;

创建触发器，实现插入数据时，blog_id字段自增
 create or replace trigger blog_trigger
 before insert on blog
 for each row
 declare
 begin
 select blog_id_seq.nextval into:New.blog_id from dual;
 end blog_trigger;
 /

创建增删改触发器(操作存储在日志中)

create table blog_log(
who varchar2(30),
when date,
action varchar2(50)
);

create or replace trigger blog_op
 before insert or update or delete
 on blog
 declare
   var_action varchar2(50);
 begin
 if inserting then
    var_action :='你执行了插入数据操作！';
 elsif updating then
    var_action :='你执行了修改操作！';
 elsif deleting then
    var_action :='你执行了删除操作！';
 end if;
 insert into blog_log(who,when,action)
 values(user,sysdate,var_action);
 end blog_log;
 /

创建blog_title列索引

create index blog_title on blog(blog_title);

select * from blog where blog_title='海哥';

增加article表的触发器

create or replace trigger article_raise
after insert or update on article
for each row
begin
  if inserting then
     dbms_output.put_line('已执行插入数据操作！');
  elsif updating then
     dbms_output.put_line('已执行修改数据操作！');
end if;
end;
/

创建一个视图，显示文章的基本信息和所在分类的名称

create view category_article_view
 as select article_title,article_content,article_summary,category_title from
 category,article
 where category.category_id=article.category_id;

在article的article_title列上建立位图索引

 create bitmap index article_title_bmp
 on article(article_title)
 tablespace test;

引用位图索引，对索引列进行查询

alter system set create_bitmap_area_size=8388608
scope=spfile;

set autotrace on explain
select * from article
where article_title='烟台的雪';

建立聚簇索引，通过blog_id字段对haige.user和haige.blog这两个表进行聚簇存储

create cluster user_blog_clu (blog_id number(10))
 pctfree 20 pctused 60
 size 500 tablespace test;

总结：只为运用知识解决问题，为了减少篇幅，相同的东西就不赘发了