Oracle学习总结

Oracle

DDL::操控数据库对象的语句

1：新建用户：

  create user 用户名

  identified by 密码

  [default tablespace 空间名]；

2：修改用户:

  alter user 用户名

  identified by 密码；

3：创建表空间

  create tablespace 空间名

  datafile '路径' size nM

  autoextend on;

4：数据表

  创建数据表

create table 表名

(列名 数据类型 [约束],

列名 数据类型 [约束])

  添加（列，约束）

  alter table 表名

  add 列名 类型 [约束]

  add constraint 约束名 约束；

  修改（列，约束）

  alter table 表明

  modify 列名 数据类型 [约束]

  modify constraint

  删除（列，约束）

  alter table 表明

 

 

DML::操控数据库对象中数据的所有语句

1：索引

2：分区

3：锁

4：同义词：

同义词的概念：

从字面上理解就是别名的意思，和视图的功能类似，就是一种映射关系。它节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;同义词可以创建在不同一个数据库服务器上，通过网络实现连接。

同义词的好处：

1) 多用户协同开发中，可以屏蔽表对象的名字及其持有者

2) 为分布式数据库的远程对象提供位置透明性。

3) 为用户简化sql语句。

4) 方便连接远程数据库

创建同义词：

            create [public] [or replace] synonym 别名 for 模式名.表明[@DB_Link;];

删除同义词：

            drop [public] [or replace] 别名;

 

public：定义为公共的同义词

or replace：覆盖数据库中现有的同名同义词

@DB_Link：如果创建的是一个连接远程数据库中的表的同义词，需要首先创建一个连接对象，并且让当前登录用户获取对远程表的操作权限。

5：集合操作符：

  union : 返回两个查询的所有不同的行

  union all： 返回两个查询的所有行

  ntersect： 操作符只返回两个查询的公共行。

  minus : 返回第一个查询语句有的第二个没有的行（用作分页）

6：pl/sql块

pl/sql块包含的内容：

    数据操作语句

    事务控制语句

    游标

    函数，运算符，逻辑控制语句

    不可以出现DDL,DCL

 

pl/sql块的组成部分：

    声明部分

    可执行部分（必须部分）

    异常处理部分

   declare

      ——声明部分

   begin

     ——可执行部分（必须部分）

   exception

    —— 异常处理部分

   end

 

 

8：变量定义与赋值

变量名[constant] 数据类型[:=默认值];（constant 定义为常量）

 

数据类型：

   %TYPE: 定义一个变量的类型与表中指定列的类型一致

   %ROWTYPE:定义一个变量可以存储表中一行的值

 

 

9：条件控制语句：

 条件语句：

   if  then 

   [elsif  hen ]

  [else ]

   end if

 

 case语句

 case

   when  条件1 then 结果1

  when  条件2 then 结果2

 esle

 endcase

 

case 列名|变量名

  when 值1 then 结果1

  when  值2 then 结果2

 esle

 endcase

 

 

10：序列

序列：用于生成唯一、连续序号的对象

         创建序列：

            create sequence 序列名

            [start with N(序列初始值)]

            [increment by N(增值)]

            [maxvalue N(最大值)]

            [minvalue N(最小值)]

            [nocycle(不循环)]

            [cache(循环次数)];

         访问序列：通过序列的伪列来访问

            1：nextval 返回序列的下一个值（常用）。

            2：currval 返回序列的当前值。

         修改序列：{不能更改序列初始值（start with N）}

            alter sequence 序列名

            [increment by N(增值)]

            [maxvalue N(最大值)]

            [minvalue N(最小值)]

            [nocycle(不循环)]

            [cache(循环次数)];

         删除序列：

           drop sequence 序列名

 

11：游标

游标分类：

  隐式游标 ：执行DML操作时，自动创建隐式游标

             游标名：sql

             自动创建，自动打开，自动关闭

 

  显示游标: 查询返回多行记录时

  ref游标：sql语句在运行时确定，动态sql语句查询

 

游标的属性：

 %found：有一行或多行返回true

 %notfound：没有行，返回true

 %rowcount:受影响的行数

 %isopen：游标是否打开，隐式游标总是为false

 

显示游标的步骤：

  1）声明游标

 cursor 游标名[(参数名 类型,...)] is  select语句;

  2）打开游标

open 游标名[(参数值,...)];

  3）提取行

         fetch 游标名 into 变量列表,....

  4）关闭游标

close 游标名;

 

     for游标：

         cursor 游标名[(参数名 类型,...)] is  select语句;

 for l_emp in c_emp

  loop  dbms_output.put_line(l_emp.ename||':'||l_emp.sal); 

  end loop;

--------------------带参数游标---------------

declare

 l_emp emp%rowtype;

 l_deptno number(6):=&部门;

 --声明游标

 cursor c_emp(l_no number)is 

  select * from emp wheredeptno=l_no;

begin

--打开游标

open c_emp(l_deptno);

loop

  --提取行

  fetch c_emp into l_emp;

  exit when c_emp%notfound;

 dbms_output.put_line(l_emp.ename||':'||l_emp.sal);

 

end loop;

 

--关闭游标

close c_emp;

 

end;

 

12：存储过程：

语法：

 create or replace procedure 过程名

  (

    参数名   [in|out]  类型,

  .....

   )is

   --声明部分

　begin

   --可执行部分

 exception

   --异常处理

 end;

 

调用过程：

 1)在sql命令窗口：

    execute 过程名(值,....);

 2)在pl/sql块中调用

     过程名(值,....);

 3)在Hibernate  createSQLQuery中调用存储过程

     SQLQuery query =session.createSQLQuery("{Call proc(?,?)}"); 

 query.setInteger(0,2);

 query.setString(1,"12312");

 query.executeUpdate();

 4)在jdbc中调用存储过程

  //conn为数据库连接对象

  CallableStatementc=conn.prepareCall("{call getsum(?)}");

  //给存储过程的参数设置值

 c.setInt(1,100);  

  //执行存储过程

  c.execute();

  conn.close();

13：函数：

函数：是可以返回值的命名的 PL/SQL 子程序

 

create or replace function 函数名

  (

    参数名   in|out  类型,

  .....

  )return 返回值类型

  is

   声明部分

　begin

    可执行部分

 exception

    异常处理

 end;

 

调用函数：

  1）作为pl/sql块中表达式的一部分

  2）与sql语句一起使用

 

函数与过程的区别？？？

 1）定义上：

     函数不能有输出参数，函数至少有一条return语句

     过程中可以有输入，输出参数，没有return语句

 

 2）调用上

    过程调用时，是pl/sql中一条语句，单独使用

函数调用时，作为pl/sql语句中表达式的一部分，还可以与sql语句一起使用

 

14：视图

语法：

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEWview_name  

[(alias[, alias]...)]  

AS subquery 

[WITH CHECK OPTION [CONSTRAINTconstraint]]  

[WITH READ ONLY]

 

OR REPLACE    ：若所创建的试图已经存在，ORACLE自动重建该视图；

FORCE ：不管基表是否存在ORACLE都会自动创建该视图；

NOFORCE  ：只有基表都存在ORACLE才会创建该视图：

alias ：为视图产生的列定义的别名；

subquery    ：一条完整的SELECT语句，可以在该语句中定义别名；

WITH CHECK  OPTION ：插入或修改的数据行必须满足视图定义的约束；

WITH READ  ONLY ：该视图上不能进行任何DML操作（增删改查）。

 

视图的优点有：

           1：提供了另外一种级别的表安全性。

           2：隐藏数据的复杂性。

           3：简化用户的SQL命令。

           4：隔离基表结构的改变。

           5：通过重命名列，从另一个角度提供数据

15：触发器

触发器定义：

触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

触发器的类型：

DML触发器：Oracle在进行DML操作是触发，可以选择在操作前或者操作后触发。

替代触发器：由Oracle不能直接对由两张表以上的视图进行操作而给出的触发器。

系统触发器：由Oracle的系统事件而制定的触发器，比如Oracle数据库的启动与关闭或者是用户的退出与登录。

语法：

DMl触发器：

 

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name 
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;

OR REPLACE：覆盖掉之前定义的同名触发器

BEFORE | AFTER ：选择触发器的触发方式是在触发事件前还是触发事件后

INSERT | DELETE| UPDATE ：触发事件

[OF column [,column …]]：指定某行触发

REFERENCING ：子句说明、相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

(例：WHEN (old.region_id = 80)

BEGIN

DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id
                                               ||'、新的region_id值是'||:new.region_id);

END;)

FOR EACH ROW ：定义触发器为行触发

WHEN：字句说明触发条件，condition为一个逻辑表达式时，其中必须包含相关名称(REFERENCING)，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

PL/SQL_BLOCK |CALL procedure_name：触发器触发时要执行的部分

 

替代触发器：

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;

Instead of：表明此触发器为替代触发器

注意事项：

基本要点：

触发时机：指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。

触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。

条件谓词：

INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。

UPDATING [（column_1,column_2,…,column_x）]：当触发事件是UPDATE时，如果         修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x是可选的。

DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。

解发对象：指定触发器是创建在哪个表、视图上。

DML触发器：

CREATE TRIGGER语句文本的字符长度不能超过32KB；

触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的             SELECT 语句。

触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；

由触发器所调用的过程或函数也不能使用数据库事务控制语句；

触发器中不能使用LONG,LONG RAW 类型；

触发器内可以参照LOB 类型列的列值，但不能通过 :NEW 修改LOB列中的数据；

替代触发器：

只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。

不能指定BEFORE 或 AFTER选项。

FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。

没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。

 

 参考网址：http://blog.csdn.net/indexman/article/details/8023740/

16：数据库连接对象

Oracle中自带了DBLink功能，它的作用是将多个oracle数据库逻辑上看成一个数据库,也就是说在一个数据库中可以操作另一个数据库中的对象，例如我们新建了一个数据database1，我们需要操作数据库database2中的表，或者我们需要操作远程机器上数据库database3中的表，我们就可以使用dblink这个强大的功能！

数据库授权：

我们如果要创建全局的DBLink，就是说无论什么角色都可以使用，那么我们需要先                   确定用户是否有DBLink权限，如果没有则需要使用sysdba角色给用户授权：

查看用户是有有DBLink权限：

select * from user_sys_privs where privilege like upper('%DATABASELINK%'); 

没有，则使用sysdba授权：

grant create public database link to dbusername;

创建DBLink：

create database link 要创建的dblink名称

connect to 要连接数据库的用户名 identifiedby 要连接数据的密码

using '(DESCRIPTION =

             (ADDRESS_LIST=

      (ADDRESS = (PROTOCOL = TCP)(HOST = 连接数据库主机IP地址)(PORT = 端口号))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = 连接数据库服务名)

    )

)';

 

如果创建全局 dblink，必须使用 systm或 sys用户，在 database前加 public：

create public database....

使用DBLink：

查询另一个数据库中的数据，其他修改、删除是一样的使用，都是另一个数据库表名@本数据库创建dblink名称：

select xxx FROM 表名@dblink名称;

 

DCL::操控数据库对象权限的语句

1：授权：

授权：

               grant dba to 用户名；

               grant 权限名 [on 对应表名]  to 用户名;

撤销权限：

revoke权限名 [on 对应表名]  to 用户名;

查看权限：

select * from user_sys_privs;-- 查看当前用户所有权限

select * from user_tab_privs;-- 查看所有用户对表的权限

权限传递：

grant alert table on tablename to TEST with admin option;--关键字 with admin option

grant alert table on tablename to TEST with grant option;--关键字 with grant option

注：权限传递：即在给用户A授予权限S后，可以再用用户A登录将权限S授予B  用户

关键字 with admin option：当用户A授权S给用户B后，用户A被撤销权限S，用户B仍然有权限S

关键字 with grant option：当用户A授权S给用户B后，用户A被撤销权限S，用户B也随着用户A被撤销权限S

2：角色

　　角色即权限的集合，可以把一个角色授予给用户

　　create role myrole;--创建角色

　　grant create session to myrole;--将创建session的权限授予myrole

　　grant myrole to TEST;--授予TEST用户myrole的角色

　　drop role myrole;删除角色

/*但是有些权限是不能授予给角色的，比如unlimited tablespace和any关键字*/

常用角色有：

                   connect：一般是授予最终用户的典型权利，最基本的（）

                   resource：一般是授予开发人员的（dml操作）

                   dba：具有所有的系统权限，及with admin option选项

 

-------------------------------------------------------------------------

锁

   行级锁：行级锁是一种排他锁，防止其他事务修改此行

 执行insert， delete， update，

    select ..from ....for update [of column]

    会自动对记录加行级锁

 

   表级锁：锁定表

    lock table 表名 in 表级锁 mode

     具体内容参考课件第三章13页

分区：

    范围分区（range）、散列分区（hash）、符合分区（）

-------------------------------4：-----------------------------------------------

同义词、视图、序列、索引

同义词：

         创建同义词：

            create [public] [or replace] synonym 别名 for 模式名.表明;

         删除同义词：

            drop [public] [or replace] 别名;

 

干货：

误删表空间，不需要恢复数据的方式：

  1）以sys登录

  2）SQL> shutdown immediate;

  3）SQL> startup mount;

  4）SQL> alter database open;

     如果成功，则不需要下一步

     如果失败，哪个文件被删除了

 5）alter database datafile 'c:\study01.dbf' offline drop;

    执行第4步，如果还有错，反复执行5步，直到第4不没有错误位置

数据字典：

ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生, 随着数据库的变化而变化
         体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。
         数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。
         我们不能手工修改数据字典里的信息，很多时候，一般的ORACLE用户不知道如何有效地利用它。
         dictionary　　　全部数据字典表的名称和解释，它有一个同义词dict
         dict_column　　 全部数据字典表里字段名称和解释
         如果我们想查询跟索引有关的数据字典时，可以用下面这条SQL语句:
                   SQL>select * from dictionarywhere instr(comments,'index')>0;
         如果我们想知道user_indexes表各字段名称的详细含义，可以用下面这条SQL语句:
                   SQL>selectcolumn_name,comments from dict_columns where

table_name='USER_INDEXES';

依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看ORACLE的其它文档资料了。
         下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。

1：用户

        查看所有用户：

                SQL>select * from dba_users；
         查看当前用户的缺省表空间
                   SQL>selectusername,default_tablespace from user_users;

查看当前用户的角色
                   SQL>select * fromuser_role_privs;
         查看当前用户的系统权限和表级权限
                   SQL>select * fromuser_sys_privs;
                   SQL>select * fromuser_tab_privs;

2：表

查看用户下所有的表
                   SQL>select * fromuser_tables;
         查看名称包含log字符的表
                   SQL>selectobject_name,object_id from user_objects
                            whereinstr(object_name,'LOG')>0;
         查看某表的创建时间
                   SQL>selectobject_name,created from user_objects where                                                                          object_name=upper('&table_name');
         查看某表的大小
                   SQL>selectsum(bytes)/(1024*1024) as "size(M)" from user_segments
                            wheresegment_name=upper('&table_name');
         查看放在ORACLE的内存区里的表
                   SQL>selecttable_name,cache from user_tables where instr(cache,'Y')>0;

3：索引

查看索引个数和类别
                   SQL>selectindex_name,index_type,table_name from user_indexes order by                                        table_name;
         查看索引被索引的字段
                   SQL>select * fromuser_ind_columns where index_name=upper('&index_name');
         查看索引的大小
                   SQL>selectsum(bytes)/(1024*1024) as "size(M)" from user_segments
                            wheresegment_name=upper('&index_name');

4：序列号

查看序列号，last_number是当前值
                   SQL>select * fromuser_sequences;

5：视图

查看视图的名称
                   SQL>select view_namefrom user_views;
         查看创建视图的select语句
                   SQL>setview_name,text_length from user_views;
                   SQL>set long 2000; 说明：可以根据视图的text_length值设定set long 的大小
                   SQL>select text fromuser_views where view_name=upper('&view_name');

6：同义词

查看同义词的名称
                   SQL>select * fromuser_synonyms;

7：约束条件

查看某表的约束条件
                   SQL>selectconstraint_name, constraint_type,search_condition, r_constraint_name
                            fromuser_constraints where table_name = upper('&table_name');
                   SQL>selectc.constraint_name,c.constraint_type,cc.column_name
                            fromuser_constraints c,user_cons_columns cc where c.owner =

upper('&table_owner') and c.table_name =upper('&table_name')
         and c.owner = cc.owner and c.constraint_name= cc.constraint_name
         order by cc.position;

8：存储函数和过程

查看函数和过程的状态
                   SQL>selectobject_name,status from user_objects where object_type='FUNCTION';
                   SQL>selectobject_name,status from user_objects where object_type='PROCEDURE';
         查看函数和过程的源代码

SQL>select text from all_source whereowner=user and name=upper('&plsql_name');

SQL>select text from user_source where owner=user andname=upper('&plsql_name');

 

小技巧：

打开oracle的输出：set serveroutput on

Oracle误提交后commit数据恢复（闪回）

1.查询最近更新数据之前的数据（以便确定是不是Commit之前的数据）

select * from  tablenameas of timestamp to_timestamp('2014-04-22 08:00:00', 'yyyy-mm-ddhh24:mi:ss');

2.闪回操作前启用行移动功能（不启用不可以闪回）

alter table tablename enable row movement;

3.执行闪回语句：

flashback table tablename to timestamp TO_TIMESTAMP('20140422 15:10:00','YYYYMMDD HH24:MI:SS');