Oracle语言,从入门到精通!____(不断更新中)

第一节：用户权限管理

 

CREATE    ANY    TABLE;             可以在任何方案中建表；                       CREATE    SESSION;          连接数据库；

CREATE    TABLE;                        创建表；                                              CREATE    VIEW;                  创建视图     

CREATE    PROCEDURE;              建过程，函数，包                                 CREATE     TRIGGER;         创建触发器     

2>显示系统权限              SELECT * FROM    system_privilege_map  ORDER    BY    NAME;

3>创建用户                     CREATE    USER    用户名    IDENTIFIED    BY    密码

4>授予用户权限              GRANT    权限1，权限2   TO    用户名

5>回收系统权限              REVOKE    权限    FROM    用户名；

 

思考问题？system --------->ken ---------->tom        假如收回了ken的权限，tom的登陆权限还有吗?

                    答案:有[系统权限不是级联回收]（但是如果tom执行的是SELECT的话，就执行不了了）

                     总结：如果回收了ken的权限，tom的增删改操作将无法执行，但是系统权限还是有的！

 

6>查询表记录                 GRANT    SELECT   ON   表名称    TO  用户名称;

7>修改表数据                 GRANT    UPDATE   ON   表名称    TO   用户名;

8>删除表数据                 GRANT    DELECT   ON   表名称     TO   用户名;

9>将上述权利全部赋予同一个用户    GRANT    ALL   ON   表名称   TO  用户名; 

10>精确权限：能否让某个用户的访问权限更加精确。在SELECT或者UPDATE后面加字段内容(用括号括起来)！

11>赋予EXECUTE（存储过程） 权限                         GRANT    EXECUTE    表名称    TO    用户名；

12>授予index（索引）权限                                        GRANT    INDEX   ON   表名称   TO    用户名；

13>WITH  GRANT  OPTION  意思是   加入   A将权限赋予B，同时B也可以授权与其他用户，但不能授予角色！

      GRANT    ALL  ON    表名称   TO   用户名   WITH   GRANT  OPTION;

 

                                                                                第二节：角色定义

角色定义：角色就是相关权限的命令集合，使用角色目的是为了简化用户权限的管理！

角色分为预定义角色和自定义角色：预定义角色指的是Oracle自身所有的角色！下面有详细介绍！

1. connect 角色 所拥有的权限

alter session/create cluster/create  database link/create session/create table/create view/create sequence

2.resource 角色 所拥有的权限

create cluster/create indextype/create table/create sequence/create type/create procedure/create trigger

3.DBA 角色 所拥有的权限

dba具有所有系统权限，包括with admin option，系统默认的dba为sys/system，它们可以将任何权限授予其他用户！但是它们不具有sysdba/sysoper权限（启动/关闭数据库）

4.创建角色：一般需要有dba创建，如果其他用具具有create role 权限 也可以进行角色创建

        4.1公众角色，不验证方式：create  role  not  identified；

        4.2创建角色，需要验证：create  role 角色名  identified  by 密码；

5.授予权限

        grant create session  to 角色名 with  admin  option

        grant select on 表明 to 角色名

        grant  update,delete,insert to 角色名

 

说明：如果删除了某个角色，被授予角色的对象的权限也随之消除。      

grant  select  on  表明 to  角色名；

grant   角色名  to  用户名；

假如角色名删除，那么用户名所拥有角色名的权限也随之没有！

注意，系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的

 

显示角色信息

6.select*from dba_roles;          查看所有角色信息

7.select*from role_sys_privs where role='角色名';         查看用户所拥有的系统权限；  

8.select*from role_tab_privs  where role='角色名';         查看用户所拥有的对象权限;

                                                               

                                                                     第三节：数据字典

 

1：数据字典记录了系统信息，他是只读表和视图的集合，归属为sys用户！

2：用户只能在数据字典上只能查询操作，修改和维护由系统自动完成！

3：数据字典组成，由数据字典基表和数据字典视图组成！基表存储数据库基本信息！

 

User_tables;

说明：用于显示当前用户所拥有的表，它只返回用户所对应方案的表

例子：SELECT*FROM USER_TABLES;

 

All_tables;

说明：用于显示当前用户可以访问的所有表，不仅返回用户对应方案的表，还返回可以访问的表！

例子：SELECT*FROM ALL_TABLES;

 

Dba_tables;

说明：他会显示所有方案的数据库表，但是用户必须拥有DBA角色和SELECT ANY TABLE 权限

例子：SELECT 表名称 FROM DBA_TABLES;

 

Dba_user;

说明：可以显示所有数据库用户的详细信息！

例子：SELECT*FROM DBA_USER;

 

Dba_sys_privs;

说明：可以查询用户所拥有的系统权限；

例子：SELECT*FROM DBA_SYS_PRIVS WHERE GRANTEE=值

 

Dba_tab_privs;

说明：查询用户所拥有的对象权限；

例子：SELECT*FROM DBA_TAB_PRIVS WHERE GRANTEE=值

 

Dba_col_privs;

说明：查询用户所拥有的列权限；

例子：SELECT*FROM DBA_COL_PRIVS WHERE GRANTEE=值

 

Dba_role_privs;

说明:可以查看用户所拥有的角色！

例子：SELECT*FROM DBA_ROLE_PRIVS WHERE GRANTEE=所查询的名字

 

比较role

select*from role_sys_privs where role='角色名';         查看用户所拥有的系统权限；  

select*from role_tab_privs  where role='角色名';         查看用户所拥有的对象权限;

 

 

查询数据库所有角色,一般是DBA：SELECT*FROM DBA_ROLES;

查询数据表空间：SELECT 表名称 FROM DBA_TABLESPACES;

 

查询orale中所有的系统权限，一般是dba:

SELECT *　FROM system_privilege_map ORDER BY name;

查询数据库中的所有角色：SELECT*FROM DBA_ROLES;(如果查询单个，查询的表是DBA_ROLE_PRIVS;)

显示当前数据库的名称：SELECT*FROM GLOBAL_NAME;

 

 

-----未实现（后续补充）

1.对象定义情况

2.对象占用空间大小

3.列信息

4.约束信息

 

                 

  ★★★★后记★★★★：这都是我自己整理的，并非复制粘贴，因为Oracle不是我发明的所以我不能说是我原创的！如果有错误了，欢迎批评，让我们共同进步！     2010.09.14 日凌晨一点

 

 

第四节：数据库的逻辑结构 _表空间_段_区_块

 

什么是表空间？

        答：表空间用于从逻辑上组织数据库的数据，数据库通常由一个表空间和多个表空间组合而成！

表空间的作用？

       1.控制数据库占用磁盘空间量

       2.DBA可以将不同的数据类型部署在不同的位置，有利于I/O操作，同时又有利于备份和操作！

创建表空间

       CREATE  TABLESPACE   表空间名称

       DATAFILE 'd:\data01.dbf'                                                       //文件路径

       SIZE  大小M                                                                         //文件大小

       AUTOEXTEND  ON  NEXT  50M MAXSIZE   1024M                 //设置文件自动增长路径

       UNIFORM  SIZE 128K                                                           //区的大小为128K，如果不指定,默认为64K！

修改表空间的状态

       1.让表空间Online          ALTER  TABLESPACE  表空间名字  ONLINE;

       2.使表空间离线Offline    ALTER  TABLESAPCE  表空间名字 OFFLINE;

       3.让表空间只读             ALTER  TABLESAOCE  表空间名字  READ  ONLY;         //注意：系统表空间无法修改此项属性

       4.让表空间只读             ALTER  TABLESAOCE  表空间名字  READ  WRITE;

 知道表空间名，显示该表空间包括的所有表

        SELECT * FROM ALL_TABLES  WHERE TALESPACE_NAME = '表空间名称';

        SELECT * FROM TABLESPACE_NAME,TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME="表名称";

删除表空间

        DROP  TABLESPACE  表空间名字  INCLUDING  CONTENTS  AND  DATAFILES;

扩展表空间

        案例说明：

        1. 建立一个表空间 sp01
        2. 在该表空间上建立一个普通表 mydment 其结构和dept一样
        3. 向该表中加入数据 insert into mydment select * from dept; 
        4. 当一定时候就会出现无法扩展的问题，怎么办？
        5. 就扩展该表空间，为其增加更多的存储空间。

       

        CREATE  TABLESPACE  sp01

        DATAFILE 'D:\123.mdf'

        SIZE  20M

        UNIFORM  128K;

 

        CREATE  TABLE  mydment  AS SELECT*FROM SCOTT.DEPT;

        INSERT  INTO  myment SELECT*FROM SCOTT.DEPT;

        INSERT  INTO  myment SELECT*FROM myment;

        对上面案例的操作 

               增加数据文件 

               ALTER  TABLESPACE sp01  DATAFILE '路径'  SIZE 5M;

               增加数据文件的大小 有问题

               ALTER  TABLESPACE sp01 DATAFILE '路径'  RISIZE 10M;

               设置文件的自动增长

               ALTER TABLE sp01 DATAFILE "路径"  AUTOEXTEND  ON  NEXT  10M MAXSIZE  1024M;

 

移动数据文件步骤

      有时候因为数据文件损坏，就需要来进行文件移动操作!

       1.确定表空间所在地址

       SELECT  TABLESPACE_NAME  FROM  DBA_DATA_FILES  WHERE  FILE_NAME = ' 路径 ';

       2.使表空间离线

       ALTER  TABLESPACE sp01 OFFLINE;

       3.移动数据文件

       HOST  MOVE  '原地址','目标地址';

        4.对数据库进行逻辑修改

       ALTER  TABLESPACE  sp01  RENAME DATAFILE  '源地址'TO'目标地址';

        5.使表空间联机

        ALTER  TABLESPACE  online；

        6.查询表空间信息

        SELECT  TABLESPACE_NAME  FROM  dba_tablespaces;

        7.显示表空间所包含的数据文件

        SELECT FILE_NAME,BYTE FROM dba_data_files WHERE TABLESPACE_NAME = 'SP01';   

 

第五章 维护数据库的完整性

数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则，在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。

        约束：约束用于确保数据库满足特定的商业规则，在Oracle中，常见的约束有not null，unique，primary key，foreign key 和check五种。

                  not null （非空）            也就是说插入数据的时候不可以为空！

                  unique（唯一）              数据的唯一性，但是可以为null。例如身份证号码。学号！

                  primary key（主键）       当定义主键后，该列不能重复，且不能为空值！一个表只能有一个主键，但是可以有多可UNIQUE

                  foreign key（引用）        用于主表和从表之间的关系！外间约束要定义在从表上！

                  chec（检查约束）          可以为检查约束

使用举例：在一个表中

                  REFERENCES 表名称（表的字段）

说明：在建表的时候，如果忘记添加约束，可以进行后序添加！

                  添加为空的约束：ALTER  TABLE  表名称 MODIFY  字段名 NOT NULL;            //只有为空约束才是MODIFY；

                  增加唯一约束：ALTER  TABLE 表名称 ADD CONSTRAINT  '约束名称' UNIQUE(身份证字段 );

                  增加检查约束：ALTER  TABLE 表名称 ADD CONSTRAINT  '约束名称'  check(beijing in ('beijing','shanghai','guangzhou'));

删除约束：

                  删除一般约束：

                  ALTER  TABLE  表名称

                  DROP  CONSTRAINT  约束名称； 

                  删除主键约束（如果连接有其他表必须加上cascade）：

                  ALTER  TABLE  表名称

                  DROP  CONSTRAINT  PRIMARY KEY CASCADE;

例子：主键约束和外键约束：

                  CREATE  TABLE  stu_01(

                  stu_class NUMBER(6),

                  stu_name VARCAHR(2),

                  CONSTRAINT  约束名称 FOREIGN KEY(stu_class) REFERENCES  stu_class(class_id )

                  )

                  CREATE  TABLE  stu_class(

                  class_id NUMBER(6)

                 class_name  VARCHAR(2)

                 ) 

                 

 ---------------------------------->2010.10.03日

第六节  pl/sql  编程

创建一个表

    CREATE TABLE student(

          stu_id NUMBER(3),

          stu_name VARCHAR(8) 

          )

 

创建存储过程

      CREATE PROCEDURE sp_add

           IS

                   BEGIN

                    INSERT  INTO student VALUES(123,‘我星永恒');

                END;

 

EXEC  sp_add;

CALL  sp_add;

 

编写规范

1.注释

      单行注释 - -

      多行注释 /* */

2.标志符号的命名规范
      1).当定义变量时，建议用v_作为前缀v_sal
      2).当定义常量时，建议用c_作为前缀c_rate
      3).当定义游标时，建议用_cursor作为后缀emp_cursor
      4).当定义例外时，建议用e_作为前缀e_error

pl/sql块介绍：块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。

         pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。

定义部分：declare

执行部分：begin

异常处理：exception

end；

实例：

      --打开输出选项
               SET SERVEROUTPUT ON;
                       BEGIN
                            DBMS_OUTPUT.put_line('Hello,World');
                       END;

dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。

 

declare
v_name VARCHAR2(30);
BEGIN
     SELECT ENAME INTO v_name FROM emp_hsj WHERE empno=&empno;
     dbms_output.put_line('姓名：' || v_name);
END;

 

第七节： P/L SQL  编程具体介绍

存储过程：过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)， 通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。

 CREATE  PROCEDURE  update_emp(v_name VARCAHR2,v_sal NUMBER)

         IS

     BEGIN

     UPDATE emp SET SAL=v_sal WHERE ename=stu_name;

END;

 

EXEC update_stu('SMITH',800);

 

函数：

CREATE  FUNCTION  yearsal(name VARCHAR2) RETURN  NUMBER；

IS year_sal NUMBER(7,2);

BEGIN

SELECT sal*12+NVL(comm,0) INTO yearSal FROM Emp WHERE ename=name;

END

 

--调用函数
SELECT  fn_QueryYearSalByName('SMITH') FROM DUAL;

 

包：包用于把过程跟函数存储起来！

CREATE  PACKAGE  sp_package

IS

    PROCEDURE  update_emp(v_name VARCAHR2,v_sal NUMBER);

    CREATE  FUNCTION  yearsal(name VARCHAR2) RETURN  NUMBER；

END

 

包体:

   CREATE  PACKAGE BODY  sp_package

   IS

         PROCEDURE  update_emp(v_name VARCAHR2,v_sal NUMBER);

              IS   

                 BEGIN

                        UPDATE emp SET SAL=v_sal WHERE ename=stu_name;

                 END;

        

           FUNCTION  yearsal(name VARCHAR2) RETURN  NUMBER；

               IS

 

 

                   

                      year_sal NUMBER(7,2);

                            BEGIN

                               SELECT sal*12+NVL(comm,0) INTO yearSal FROM Emp WHERE ename=name;

                             END;

     END;

 

SELECT sp_package.fn_annual_income('SMITH') FROM dual;

评论

1 楼 wangen2009 2012-08-06