Oracle学习笔记

最新推荐文章于 2018-03-28 16:00:58 发布
黑猴子csd
最新推荐文章于 2018-03-28 16:00:58 发布
阅读量998
点赞数
分类专栏：学习笔记文章标签： select
本文链接：https://blog.csdn.net/u012579604/article/details/46992007
版权
学习笔记专栏收录该内容
7 篇文章 2 订阅
订阅专栏
--"oracle.jdbc.OracleDriver";             
--"jdbc:oracle:thin:@localhost:1521:orcl";



select * from Dba_Tablespaces;                                              --查看所有表空间
select table_name from all_all_tables where tablespace_name='SPAC1';        --查找某个表空间下的表：注意，条件名称一定要大写
select tablespace_name from all_all_tables where table_name='ORCL_BOOK'     --查看某表所在表空间
select * from dba_users;                                                    --查找所有账户
--
select * from all_views;
select * from dba_views;
select * from user_views;
--
select * from all_synonyms;
select * from dba_synonyms;
select * from user_synonyms;
--
select * from all_indexes;
select * from dba_indexes;
select * from user_indexes;
--
select * from all_sequences;
select * from dba_sequences;
select * from user_sequences;
--
--创建表空间：
create tablespace spac1
       datafile 'c:\spac1.dbf'
       size 2m 
       autoextend on              --autoextend off  开启或关闭自动增长
       next 1m                    --每次增长1m，最大增长到20m
       maxsize 20m;

--创建用户
create user my_user1 identified by myuser1 default tablespace spac1;
grant connect,resource to my_user1; --revoke移除权限，Oracle中具体权限表************
--资源文件：创建资源文件的作用是为XXX用户创建限制，如登录时输入3次密码错误，将锁定该用户等
create profile myuser1_profile limit
  sessions_per_user unlimited
  cpu_per_session unlimited
  cpu_per_call unlimited
  connect_time unlimited
  idle_time unlimited
  logical_reads_per_session unlimited
  logical_reads_per_call unlimited
  composite_limit unlimited
  private_sga unlimited
  failed_login_attempts 10
  password_life_time 180
  password_reuse_time unlimited
  password_reuse_max unlimited
  password_lock_time 1
  password_grace_time 7
  password_verify_function Null;
--
alter user my_user1 profile myuser1_profile --为用户配置所使用的资源文件
--

--创建表
--伪列：这是一个重要的对象，在Oracle数据库的每一张表中存在着两列隐藏列，列名分别是：rowid、rownum分别指示物理id和行id。通过select * from 表名；是查询不到这两列的内容的。
--伪列存在的作用是增大查询效率和辅助完成特定需求，如分页等
--select rowid,rownum,title from Orcl_Book;

create table Orcl_Book(
       BookClassID     number primary key,
       Title           varchar2(50) not null unique,
       Author          varchar2(50),
       ISBN            varchar2(50),
       CBFXX           varchar2(50),
       ZTXX            varchar2(50),
       DTZRZ           varchar2(50),
       ZRZ             varchar2(50),
       ZGTSFLFH        varchar2(50),
       KTTSFLFH        varchar2(50),
       LTZT            varchar2(50),
       FZX             varchar2(200),
       CBSJ            date,
       DJ              number(10,2),
       PCJE            number(10,2)
);
insert into Orcl_Book(Title) values('三国演义');
insert into Orcl_Book(Title) values('三国演义2');
select * from Orcl_Book;
--
create table Orcl_StorePlace(
       StoreID         number primary key,
       MC              varchar2(50),
       WZ              varchar2(200),
       FZR             varchar2(50),
       SM              varchar2(200)
);
--
create table Orcl_Store(
       BookID          number primary key,
       BookClassID     number,
       StoreID         number,
       SQH             varchar2(50) not null unique,
       TMH             varchar2(50),
       NJH             varchar2(50),
       ZT              varchar2(50) not null unique,
       RGSJ            date not null unique
);
alter table Orcl_Store add(
      Constraint Store_Book_fk foreign key(BookClassID) references Orcl_Book(BookClassID),
      Constraint Store_StorePlace_fk foreign key(StoreID) references Orcl_StorePlace(StoreID)
);
--
create table Orcl_Reader(
       ReaderId         number primary key,
       ZJH              varchar2(50),
       GH               varchar2(50),
       BZRQ             date,
       SXRQ             date,
       GQRQ             date,
       YJ               number(10,2),
       SXF              number(10,2),
       LJJS             number,
       RuleID           number,
       ZT               varchar2(50)
);
alter table Orcl_Reader add(
      Constraint Reader_Rule_fk foreign key(RuleID) references Orcl_Rule(RuleID)
);
--
create table Orcl_Rule(
       RuleID            number primary key,
       GZMC              varchar2(50),
       KJYTS             number,
       KJYGCDID          varchar2(50),
       KXJTS             number,
       KXJCS             number,
       KYYSJ             number       
);
--
create table Orcl_JYB(
       JYID              number primary key,
       ReaderID          number,
       BookID            number,
       JYSJ              date,
       DQSJ              date,
       GHRQ              date,
       ZT                varchar2(50),
       XJCS              number
);
alter table Orcl_JYB add(
      Constraint JYB_Reader_fk foreign key（ReaderID) references Orcl_Reader(ReaderID),
      Constraint JYB_Store_fk foreign key(BookID) references Orcl_Store(BookID)

);
--
create table Orcl_CFB(
       CFID              number primary key,
       ReaderID          number,
       BookID            number,
       CFYY              varchar2(50),
       CFJG              varchar2(50),
       FKJE              number(10,2),
       ZT                varchar2(50)
);
alter table Orcl_CFB add(
      Constraint CFB_Reader_fk foreign key(ReaderId) references Orcl_Reader(ReaderID),
      constraint CFB_Store_fk foreign key(BookID) references Orcl_Store(BookID)
);
--
--序列
--create sequence [方案] 序列名称
--[increment by 整数]                   ：指定序列增量
--[start with 整数]                     ：起始值
--[maxvalue 整数 | nomaxvalue ]         ：是否有最大值约束
--[minvalue 整数 | nominvalue ]         ：是否有最小值约束
--[cycle 整数 | nocycle ]               ：数字到达最大/最小值时序列是否重新开始
--cache                                 ：缓存中保存的序列整数的数量

--使用序列：
--select my_sequence.currval from dual;查询当前值     
--my_sequence.nextval  序列下一个值；
--eg:
select XL_BOOKCLASSID.currval from dual;
--
create sequence XL_BookClassID
increment by 1
start with 1
nomaxvalue
--
create sequence XL_StoreID
increment by 1
start with 1
--
create sequence XL_BookID
increment by 1
start with 1
--
create sequence XL_ReaderID
increment by 1
start with 1
--
create sequence XL_RuleID
increment by 1
start with 1
--
create sequence XL_JYID
increment by 1
start with 1
--
create sequence XL_CFID
increment by 1
start with 1
--


--存储过程
--create or replace procedure 过程名称(
--       sno in number default 20,      ：输入参数，可以通过default设置参数默认值
--       res out number                 ：输出参数
--                                      ：（in out）还可以拥有双向参数
--)
--is
--       声明语句段；
--       [异常名1 exception；]
--begin
--       执行语句段；
--       ......
--       [raise 异常名1；]             ：抛出异常
--exception
--       
--       when 异常名1 then
--            异常处理语句段1；
--end [过程名]；

--说明：
--or replace：是可选项，如果同名的过程已经存在则覆盖原有过程。
--过程是命名的PL/SQL代码块，用is（as）关键字替代了declare作为声明语句段的开始。

--使用：
--要执行创建的过程，可以通过PL/SQL主程序或其他过程来调用；也可以在命令行工具中通过“execute 过程名”来调用执行。
--1.
--   begin
--     myproc；
--   end；
--2.
--   SQL>execute myproc;

--
create or replace procedure Ins_Orcl_Book (
       Title      in     varchar2,
       Author     in     varchar2,
       ISBN       in     varchar2,
       CBFXX      in     varchar2,
       ZTXX       in     varchar2,
       DTZRZ      in     varchar2,
       ZRZ        in     varchar2,
       ZGTSFLFH   in     varchar2,
       KTTSFLFH   in     varchar2,
       LTZT       in     varchar2,
       FZX        in     varchar2,
       CBSJ       in     date,
       DJ         in     number,
       PCJE       in     number
       -- 14
)
is 
begin
  insert into Orcl_Book(Title, Author, ISBN, CBFXX, ZTXX, DTZRZ, ZRZ, ZGTSFLFH, KTTSFLFH, LTZT, FZX, CBSJ, DJ,PCJE) values(Title, Author, ISBN, CBFXX, ZTXX, DTZRZ, ZRZ, ZGTSFLFH, KTTSFLFH, LTZT, FZX, CBSJ, DJ,PCJE);
end;
--


--函数
--create or replace function 函数名(参数名 参数类型，......)
--return 返回值类型
--is
--begin
--end [函数名]；
--
create or replace function Sel_Orcl_Book_1(a number)      --：需要注意的是，函数必须有至少一个参数，即使不需要
return sys_refcursor                                      --：需要注意的是，函数必须有一个返回值
is 
   cur sys_refcursor;
begin
   open cur for select * from Orcl_Book;                  --此种写法是固定的@************** 查询时使用游标 ******************@
   return cur;
end;


--执行函数
declare 
  cur sys_refcursor;
  rowone Orcl_Book%rowtype;
begin
  cur:=Sel_Orcl_Book_1(1);
  loop
    fetch cur into rowone;
    exit when cur%notfound;
    dbms_output.put_line(rowone.title);
  end loop;
  close cur;
end;
--
--Oracle中提供的常用函数：
--1.单行SQL字符函数
--：ABS(n)     返回n的绝对值
--：ACOS（）      



--游标
--：游标换句话说就是查询出的结果集，通过游标我们更方便函数、存储过程等的编写，应用更方便。
--基本用法分4步
--1.定义游标               CURSOR   cursor_name    IS   select语句；
--2.打开游标               OPEN cursor_name；
--3.取值                   FETCH  cursor_name   INTO  variable[,variable]......；
--                              取值一般是个循环的过程，所以应用是一般需要结合循环语句一起使用。
--4.关闭游标               CLOSE cursor_name；
--游标的4个重要属性：
--%FOUND       %NOTFOUND     %ROWCOUNT        %ISOPEN

--:游标的2中应用
--：1 见函数 Sel_Orcl_Book_1（0）；【上】：open cur_name for select语句：--
--：2
declare
     cursor cur is select * from Orcl_Book;
     rowone Orcl_Book%rowtype;
begin
  open cur;
  loop
    fetch cur into rowone;
    exit when cur%notfound;
    dbms_output.put_line(rowone.title);
  end loop;
  close cur;
end;  
--
--
--2种数据类型：
--1：Oracle提供的数据类型
        --字符数据类型：char（n）、varchar2（n）、long
        --数字数据类型：number（m，n）
        --日期数据类型：date
        --大对象数据类型：blob、clob、nclob
        --Bfile数据类型
        --其他：raw（n）、long row
        --返回某列的数据类型（xxx表示列名）：xxx%type
        --返回某条记录对象类型（即表的一行记录为一个对象）（xxx表示表名）：xxx%rowtype
--2：自定义数据类型
        --type ty_name is record（
        --     stuID     students.stu_id%type,
        --     stuName   varchar2
        --）；
        --使用：myStu ty_name;


--#-包：略！



--触发器
--分三类：：：before、after、instead of
--before    ：表示在数据库动作之前触发器执行
--after     ：表示在数据库动作之后触发器执行
--instead of：触发器能将数据库动作替换为其他操作
--
--创建触发器：
--create or replace trigger 触发器名
--after|before|instead of
--[insert] [[or] update [of column_list]] | [[or] delete] on 表名/视图名
--[for each row]                                                              ：对表的每一行触发器执行一次，如果没有这一行，表示对整个表执行一次
--[when （条件）]
--PL/SQL语句

--触发器示例，为[XXX]表实现自增长列
create or replace trigger TRI_XL_BookClassID
before insert on Orcl_Book
for each row
  when ( NEW.BookClassID is null )
    begin
      select XL_BookClassID.Nextval into :NEW.BookClassID from dual;
end;
--
create or replace trigger TRI_XL_StoreID
before insert on Orcl_StorePlace
for each row
  when ( NEW.StoreID is null )
    begin
      select XL_StoreID.Nextval into :NEW.StoreID from dual;
end;
--
create or replace trigger TRI_XL_BookID
before insert on Orcl_Store
for each row
  when ( NEW.BookID is null ） 
    begin 
      select XL_BookID.Nextval into :NEW.BookID from dual;
end;
--
create or replace trigger TRI_XL_ReaderID
before insert on Orcl_Reader
for each row 
  when ( NEW.ReaderID is null ) 
    begin 
      select XL_ReaderID.Nextval into :NEW.ReaderID from dual;
end;
--
create or replace trigger TRI_XL_RuleID
before insert on Orcl_Rule
for each row
  when ( NEW.RuleID is null )
    begin 
      select XL_RuleID.Nextval into :NEW.RuleID from dual;
end;
--
create or replace trigger TRI_XL_JYID
before insert on Orcl_JYB
for each row 
  when ( NEW.JYID is null )
    begin 
      select XL_JYID.Nextval into :NEW.JYID from dual;
end;
--
create or replace trigger TRI_XL_CFID
before insert on Orcl_Cfb
for each row 
  when （ NEW.CFID is null )
    begin 
      select XL_CFID.Nextval into :NEW.CFID from dual;
end;
--










---其他存储过程
create or replace procedure Ins_Orcl_StorePlace (
       MC         in  varchar2,
       WZ         in  varchar2,
       FZR        in  varchar2,
       SM         in  varchar2
--:4
)
is 
begin
  insert into Orcl_StorePlace(MC, WZ, FZR, SM) values(MC, WZ, FZR, SM);
end;
--
create or replace procedure Ins_Orcl_Store (
       BookClassID  in   number,
       StoreID      in   number,
       SQH          in   varchar2,
       TMH          in   varchar2,
       NJH          in   varchar2,
       ZT           in   varchar2,
       RGSJ         in   date
--:7
)
is 
begin
  insert into Orcl_Store(BookClassID, StoreID, SQH, TMH, NJH, ZT, RGSJ) values(BookClassID, StoreID, SQH, TMH, NJH, ZT, RGSJ);
end;
--
create or replace procedure Ins_Orcl_Reader(
       ZJH         in varchar2,
       GH          in varchar2,
       BZRQ        in date,
       SXRQ        in date,
       GQRQ        in date,
       YJ          in number,
       SXF         in number,
       LJJS        in number,
       RuleID      in number,
       ZT          in varchar2
)
is 
begin
  insert into Orcl_Reader(ZJH, GH, BZRQ, SXRQ, GQRQ, YJ, SXF, LJJS, RuleID, ZT) values(ZJH, GH, BZRQ, SXRQ, GQRQ, YJ, SXF, LJJS, RuleID, ZT);
end;
--
create or replace procedure Ins_Orcl_Rule(
       GZMC         in   varchar2,
       KJYTS        in   number,
       KJYGCDID     in   varchar2,
       KXJTS        in   number,
       KXJCS        in   number,
       KYYSJ        in   number       
)
is 
begin
  insert into Orcl_Rule(GZMC, KJYTS, Kjygcdid, Kxjts, Kxjcs, Kyysj) values(GZMC, KJYTS, Kjygcdid, Kxjts, Kxjcs, Kyysj);
end;
--
create or replace procedure Ins_Orcl_JYB(
       ReaderID          number,
       BookID            number,
       JYSJ              date,
       DQSJ              date,
       GHRQ              date,
       ZT                varchar2,
       XJCS              number
)
is 
begin
  insert into Orcl_Jyb(ReaderID, Bookid, Jysj, Dqsj, Ghrq, Zt, Xjcs) values(ReaderID, Bookid, Jysj, Dqsj, Ghrq, Zt, Xjcs);
end;

--
create or replace procedure Ins_Orcl_CFB(
       ReaderID          number,
       BookID            number,
       CFYY              varchar2,
       CFJG              varchar2,
       FKJE              number,
       ZT                varchar2
)
is 
begin
  insert into orcl_cfb(readerid, bookid, cfyy, cfjg, fkje, zt) values(readerid, bookid, cfyy, cfjg, fkje, zt);
end;

--