oracle补充

create unique index  teacherhao on teacher(hao);
create unique index teacherhao2 on teacher(hao,hao2);

drop index teachername;

序列

序列是Oracle提供的用于产生一系列唯一数字的数据库对象。 在某些数据表的结构中，有些字段需要这种特征： 比如，对于某个学生数据表的学号关键字段，用户可以希望在录入数据时，能够自动在上一个记录的学号字段上自动加1等。

• 创建序列要有序列权限

• grant create sequence,select any sequence to 用户名
  --dba权限包括以上权限，所以不用重新赋予

• 序列的创建

• CREATE SEQUENCE sequence      --创建序列名称
  [INCREMENT BY n]     --递增的序列值是n  步长为n
  [START WITH n]      --开始的值,递增默认是minvalue 递减是maxvalue
  [{MAXVALUE n | NOMAXVALUE}]     --最大值
  [{MINVALUE n | NOMINVALUE}]    --最小值
  [{CYCLE | NOCYCLE}]     --循环/不循环
  [{CACHE n | NOCACHE}];   --分配并存入到内存中

  SQL例子：

• create sequence SEQ4 increment by 1 start with 1 minvalue 1 nomaxvalue nocycle nocache; 

• 序列的使用

• create table lvling6(
  id number,
  name varchar(20)
  );
  
  insert into lvling6 values(SEQ4.nextval,‘xiaomi2‘);
  select * from lvling6;

• 删除序列

• drop   sequence  SEQ4

视图

视图是查看数据表中数据的一种方， 当一些用户需要经常访问和查询数据表中某些字段构成的数据，但管理员从安全角度考虑又不希望他们直接接触数据表时，可以利用Oracle数据库提供的视图这一数据对象。 视图犹如数据表的窗户，用户只能查看他们可以看到的数据，视图不是数据表，它仅是一些SQL查询语句集合，作用是按照不同的要求从数据表中提取不同的数据。

视图优点

因为视图可以有选择性的选取数据库里的一部分，用户通过简单的查询可以从复杂查询中得到结果，维护数据的独立性，视图可从多个表检索数据，对于相同的数据可产生不同的视图

• 创建视图的SQL

• create [or replace] view  teacher_view(id,name,age,hao)  as  select   tid,tname,age,hao 
  from teacher;
  --or replace 代表如果视图存在则替换，不存在则创建一张新的视图
  
  create [or replace] view  teacher_student_view(id,name,age,hao,sname,sid)  as  select   t.tid,t.tname,t.age,t.hao,s.name,s.studentid
  from   teacher  t,student  s
  where t.tid=s.studentid;

• 用SQL 查看表的视图

• select    *   from    teacher_student_view ;
  --和查表一样，表名换成视图名即可

• 修改视图的SQL

• create or replace view  teacher_student_view(id,name,age,hao,studname,studid)    
  as  select  t.tid,t.tname,t.age,t.hao,s.name,s.studentid
  from   teacher  t,student  s
  where t.tid=s.studentid;
  
  --通过or replace 重新创建同名视图即可,如果视图存在则替换

• 修改视图的数据SQL

• create [or replace] view  teacher2_view(id,name,age,hao)   
  as  select  t.tid,t.tname,t.age,t.hao
  from   teacher  t;
  
  update   teacher2_view
  set  age=20
  where  id=1;--更新视图表中的数据
  --注：在实际开发中，很少对视图中数据进行修改

• 设置成只读视图
• with read only :设置为只读视图

• create [or replace] view  t_view(id,name,age,hao)   
  as  select  t.tid,t.tname,t.age,t.hao
  from   teacher  t
  where age>40
  with read only;
  --不能插入/更新数据

• 为视图加限制
• WITH CHECK OPTION:即该数据必须满足视图定义中的子查询中的WHERE条件，否则不允许插入或更新

• create [or replace] view tab_view(id,name,age,hao) 
  as  
  select  t.tid,t.tname,t.age,t.hao
  from  teacher  t
  where age>40
  WITH CHECK OPTION; 
  
  --当更新时：
  insert into  tab_view
  values(5,‘lao‘,10,10);
  --10<40 报错

• 删除视图的SQL

• drop   view    tab_view;

事务处理

为什么引入事务

在单用户的数据库系统中，无需对数据的一致性和完整性做过多的考虑。而在多用户并发系统中，多个用户对同一数据的并发访问要求以某种机制来实现数据的一致性和完整性

什么是事务

事务（Transaction）用于保证数据的一致性，它由一组相关的dml语句(增删改语句)组成，该组的dml语句要么全部成功，要么全部失败。 如：网上转帐就是用事务来处理，用以保证数据的一致性。

3种事务的处理命令

commit命令:commit是事务的提交命令，数据库为了保证事务的唯一性，一致性，在内存中将为每一个客户建立工作区，客户机对数据库进行操作处理的事务都在工作区内完成，只有在输入commit命令后，工作区内修改内容才写到数据库上，称为物理写入，这样可以保证在任意的客户机没有物理提交个修改以前，别的客户机读取的后台数据库中的数据是完整的，一致的。

set  auto  on;   --在运行中输入sqlplus 后进入dos下，打开自动提交功能
set  auto  off;   --取消自动提交功能

rollback命令:rollback是事务的回滚命令，在尚未提交commit命令之前，如果发现delete、insert、update等操作需要恢复的话，可以使用rollback命令回滚到上次commit时的状态

savepoint命令:savepoint 是保存点命令，事务通常由数条命令组成，可以将每个事务划分成若干个部分进行保存，这样每次可以回滚每个保存点，而不必回滚整个事务

创建保存点：savepoint 保存点名; 回滚保存点： rollback to 保存点名;

Oracle 数据备份与还原

exp命令用于把数据从远程数据库服务器导出至本地,生成dmp文件

imp命令用于把本地的数据库dmp文件从本地导入到远程的Oracle数据库中

将数据库中的表导出,生成dmp文件

exp   hh/hh@orcl   file=E:/lvling.dmp  tables=(lvling)

exp   xiaomi7/xiaomi7@192.168.2.57:1521/orcl   file=E:/meetingroom.dmp  tables=(meetingroom) --远程库

exp   hh/hh@orcl   file=E:/lvling.dmp  tables=(lvling,lvling1)

将生成dmp文件,导入数据库中

imp hh/hh@orcl file=E:/lvling.dmp tables=(lvling)
--注意:导入导出时，用同一用户，同一表空间

将数据库中yy用户表导出,生成dmp文件

exp yy/yy@orcl file=E:/lvling.dmp  owner=(yy)

将生成dmp文件,导入数据库中

imp yy/yy@orcl file=E:/lvling.dmp fromuser=yy

将数据库中yy，tt  用户表导出,生成dmp文件

exp system/orcl@XE file=E:/two.dmp  owner=(yy,tt)             --  sys得给权限，sysdba权限，否则导不出来

将生成dmp文件,导入数据库中

imp system/orcl@XE file=E:/two.dmp  fromuser=tt touser=tt
imp system/orcl@XE file=E:/two.dmp  fromuser=yy touser=yy

将orcl整个数据库导出,生成dmp文件

exp yy/yy@orcl  file=E:/lvling.dmp  full=y

将生成dmp文件,导入orcl整个数据库中

imp  yy/yy@orcl  file=E:/lvling.dmp   full=y  ignore=y         --ignore=y忽略创建错误 因为有的表已经存在，然后它就报错，对该表就不进行导入

 PL/SQL程序(过程化SQL语言)

需求：创建一个简单的PL/SQL程序向数据库中插入数据

create  table    lv(sname   varchar2(10),   spassword varchar2(20))

--创建过程：
create [or replace] procedure  lv_one as
begin  --执行部分
for i in 1..10 loop
    insert into lv  values(‘a‘,‘bb‘);
    end loop;
end;

--调用过程：
exec  lv_one;

需求：创建一个简单的PL/SQL程序删除数据库中数据

--创建过程：
create [or replace] procedure  lv_two as
begin--执行部分
delete from lv where sname=‘lv1‘;
end;

--调用过程：
exec  lv_two;

命名规范

当定义变量时，建议用v_作为前缀 v_sal

当定义常量时，建议用c_作为前缀 c_month

当定义游标时，建议用_cursor作为后缀 emp_cursor

当定义异常时，建议用e_作为前缀 e_error

什么是PL/SQL块

块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是在编写块，要完成简单的功能，可能需要一个块，复杂的功能，要一个块中嵌套另一个块

PL/SQL块由三个部分组成:定义部分、执行部分、异常处理部分

declare   
/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/  
begin   
/*执行部分——要执行pl/sql语句和sql语句*/  
exception   
/*异常处理部分——处理运行的各种错误*/  
end;

实例1：只包括执行部分的pl/sql块

/*只要涉及输入就需要打开输出选项*/
set  serveroutput  on --打开输出选项 
--set  serveroutput  off  关闭输出选项

begin   
dbms_output.put_line(‘hello‘);
end;
/--在命令窗口上执行最后得加/

/*相关说明： dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。*/

实例2:PL/SQL包含定义部分，执行部分和异常处理部分

declare
    v_ename varchar2(5); --定义字符串变量
    v_sal number(7,2);
begin
    select ename,sal into v_ename,v_sal from scott.emp where empno=&aa;  --& 表示要接收从控制台输入的变量,赋值给aa;
    dbms_output.put_line(‘雇员名：‘ ||v_ename||v_sal); 
exception
    when no_data_found 
     /*no_data_found：找不到数据的异常，是Oracle定义好的异常*/
    then dbms_output.put_line(‘编号不对‘);
end;

实例3:需求：输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)

declare  
   c_shuilv number(3,2):=0.03;--税率常量用c
   v_sname varchar2(3);  --姓名变量用v
   v_sal number(10);      --工资
   v_shui number(10);     --个人所得税
begin
    --执行
    select   sname,sal  into  v_sname,v_sal  from lv where id=&id;
    v_shui:=c_shuilv*v_sal; --个人所得税
    dbms_output.put_line(‘姓名是‘||v_sname||‘工资‘||v_sal||‘税‘||v_shui);
end;

-- ‘:=‘就是赋值的意思,相当于java中的‘=‘

%type

实例3会产生漏洞，当要查的人员姓名大于v_sname varchar2(3)时会报错

可以使用%type属性定义变量,这样它会按照数据库列来确定你定义的变量的类型和长度

标识符名 表名.列名%type;

v_sname varchar2(3); 可以换成v_sname lv.sname%type;

%rowtyp

使用%rowtype,可以使变量获得整个记录的数据类型

declare  
    lvtable  lv  %rowtype; 
begin
    --执行
    select *  into  lvtable  from lv  where id=1;
    dbms_output.put_line(lvtable.sname);
    dbms_output.put_line(lvtable.sal);
end;

if..then..else..end if:相当于if else语句,then后的是如果条件成立执行的语句,语句结束使用end if,同样支持嵌套

loop..exit..end loop:相当于while,配合if..then exit;退出循环,语句结束使用end loop;

for..in..loop..end loop:相当于for语句,for 循环变量 in 循环上界..循环下界 loop 循环处理语句段; end loop;

goto labelOffLOOP(标签名):跳到标签处,标签定义语句：<<labelOffLOOP>>

Oracle PL/SQL过程

需求：创建一个PL/SQL过程修改员工的工资

create [or replace] procedure lv_three (lvName in varchar2,newSal number) as --默认不写时是in
begin
    update lv set sal=newSal where sname=lvName;
end;

--调用过程：
exec  lv_three(‘jerry‘,4000);
--或
call  lv_three(‘jerry‘,4000);

--注：当遇到error时，用show error;查看错误
--类似于函数调用，过程可以被另一个过程调用

需求：创建一个PL/SQL过程查询员工的姓名

create [or replace] procedure lv_four (sid in number,stname out varchar2) 
as  --或is 
begin
    select  sname  into stname  from  lv  where id=sid;
end;

--调用过程：
declare
    ming varchar(10);
    sid number(10);
begin
    sid:=1;
    lv_four (sid,ming);
    dbms_output.put_line(ming);
end;

需求：创建一个PL/SQL过程查询员工的年工资

create or replace procedure  getsal(saly in out number) as
begin
    select  sal *12 into saly from lv where sal=saly;
exception
    when  no_data_found 
    then dbms_output.put_line(‘没有找相关数据‘);
end;

--调用过程：
declare
    ssal number(10);
begin
    ssal:=1;
    getsal(ssal);
    dbms_output.put_line(ssal);
end;

删除过程

drop  procedure  过程名;

oracle补充

标签：type属性   sql   独立   作用   index   uniq   cep   while   角度