Oracle 课程设计源码

灰太狼 2022-06-14 09:13 202阅读 0赞

创建主表空间：

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;

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