oracle 常见函数

原创
2016/11/22 16:47
阅读数 240

1    单行函数

    1.1    字符函数

        字符函数:输入是字符,输出是字符或者number

        upper:字符大写显示

SQL> select upper('abc') from dual;

UPP
---
ABC

SQL> select upper('aA,%c') from dual;

UPPER
-----
AA,%C

        lower:字符小写显示

SQL> select lower('ABC,a/') from dual;

LOWER(
------
abc,a/

        initcap:首字母大写

SQL> select initcap('abc,344%acc') from dual;

INITCAP('AB
-----------
Abc,344%Acc
注意:特殊字符后定也会被认为是首字母,也会大写

        concat:字符链接显示

SQL> select concat('aa','bb') from dual;

CONC
----
aabb
SQL> select concat('aa',234) from dual;

CONCA
-----
aa234

        length:统计字符长度

SQL> select length('slfjsdklfjd') from dual
  2  ;

LENGTH('SLFJSDKLFJD')
---------------------
		   11
# 按照字符统计

        lengthb:统计字符长度

SQL> select lengthb('adbc') from dual;

LENGTHB('ADBC')
---------------
	      4
#按照字节统计

        lengthc:统计字符长度。asic码统计

        GBK 字符编码中一个汉字字符两个字节,UTF-8 字符编码中一个汉字占3个字节

        在英文中length,lengthb,lengthc 统计定结果是一样定,在中文中就不一样的。

        这个在工作环境中只来比较length和lengthb是否一致,如果不一致,说明里面包含中文。

        substr:字符截取

SQL> select substr('abcdef',1,3) from dual;

SUB
---
abc
#1 表示从第一个开始截取,3表示截取3个字符。如果3不写表示截取全部
SQL> select substr('abcde',-3,2) from dual;

SU
--
cd
# 截取定开始位置可以从后往前定位,但是还是向后截取
SQL> select ename from emp where substr(ename,-1,1) = 'T';

ENAME
----------
SCOTT

# 在实际工作中使用截取能替代like 的部分功能,效率比like高很多

    instr:显示字符在字符串中定索引位置

SQL> select instr('abcde','c') from dual;

INSTR('ABCDE','C')
------------------
		 3
# c 在第三个。默认是从第一个开始找,第一次出现的。
SQL> select instr('abcdea','a',2) from dual;

INSTR('ABCDEA','A',2)
---------------------
		    6
# oracle11g 可以制定找第几次,这里就是找到列a第二次出现在6的位置
SQL> select instr('abcdea','a',2,2) from dual;

INSTR('ABCDEA','A',2,2)
-----------------------
		      0
# 从第二个字符开始找,找第二次出现的a

        trim():截断字符,截断两端

SQL> select trim('a' from 'aaaabddea') from dual;

TRIM
----
bdde
# 只要a字符中间的
SQL> select trim(trailing 'a' from 'aabcdefa') from dual;

TRIM(TR
-------
aabcdef
# 只截断右边
SQL> select trim(leading 'a' from 'aabcdefa') from dual;

TRIM(L
------
bcdefa
# 只截左边
#-------------------- 上面这些功能只有在oracle10G以后 有

        ltrim:oracle11g的高级截断,全部截断

SQL> select ltrim('abababaacbda','ab') from dual;

LTRI
----
cbda

    lpad:左填充函数

SQL> select lpad('abc',5,'*') from dual;

LPAD(
-----
**abc
# 默认是用空格填充

    rpad:右填充函数

SQL> select rpad('abcd',10,'-') from dual;

RPAD('ABCD
----------
abcd------

    replace:替换函数

SQL> select replace('abcd','c','*') from dual;

REPL
----
ab*d

 

    1.2    数值函数

        abs:绝对值

SQL> select abs(1.2) from dual;

  ABS(1.2)
----------
       1.2

SQL> select abs(-12) from dual;

  ABS(-12)
----------
	12

        round:保留几位小数

SQL> select round(1.2345,3) from dual;

ROUND(1.2345,3)
---------------
	  1.235
SQL> select round(123.4567,-2) from dual;

ROUND(123.4567,-2)
------------------
	       100
#小数点向前向后都可以
SQL> select trunc(234.567,2) from dual;

TRUNC(234.567,2)
----------------
	  234.56
# round 和trunc 的区别,round会四舍入,trunc不会
SQL> select ceil(12.13) from dual;

CEIL(12.13)
-----------
	 13
# ceil 直接向上去整数
SQL> select floor(12.57) from dual;

FLOOR(12.57)
------------
	  12
# floor 向下取整

    mod:取余

SQL> select mod(11,3) from dual;

 MOD(11,3)
----------
	 2

     1.3     日期函数

         sysdate:显示当前日期

SQL> select sysdate from dual;

SYSDATE
---------
22-NOV-16

          to_char():转换为字符函数

SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;

TO_CHAR(SY
----------
2016-11-22
  1* 	select to_char(11,'XXXX') from dual
SQL> /

TO_CH
-----
    B
# 十进制转换为16进制
  1* select to_number(10,'XXXX') from dual
SQL> /

TO_NUMBER(10,'XXXX')
--------------------
		  16
# 16进制准换为10进制

        to_date() :转换为日期

SQL> select to_date('2016-11-22','yyyy-mm-dd') +1 from dual;

TO_DATE('
---------
23-NOV-16

        add_month():加几个月

SQL> select add_months(sysdate,1) from dual;

ADD_MONTH
---------
22-DEC-16
  1* select add_months(sysdate,-1) from dual
SQL> /

ADD_MONTH
---------
22-OCT-16

        next_day():下一个星期几是那一天

SQL> select next_day(sysdate,1) from dual;

NEXT_DAY(
---------
27-NOV-16
  1* select next_day(sysdate,2) from dual
SQL> /

NEXT_DAY(
---------
28-NOV-16

SQL> select last_day(sysdate) from dual;

LAST_DAY(
---------
30-NOV-16
# 当年定最后一天

SQL> select trunc(sysdate,'yyyy') from dual;

TRUNC(SYS
---------
01-JAN-16
# 当年第一天

SQL> select trunc(sysdate,'mm') from dual;

TRUNC(SYS
---------
01-NOV-16
# 当月第一天

    

SQL> select current_date from dual;

CURRENT_D
---------
22-NOV-16
# 查看时区

    month_between():记录当前多少个月

SQL> select months_between(sysdate,to_date('2008-05-12','yyyy-mm-dd')) from dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('2008-05-12','YYYY-MM-DD'))
----------------------------------------------------------
						102.343878
# 512到现在过去列多少个月

    NVL2:

SQL> select nvl2('a',1,2) from dual;

NVL2('A',1,2)
-------------
	    1

SQL> select nvl2('',1,2) from dual;

NVL2('',1,2)
------------
	   2
# 如果空,返回第一个值,否则返回第二个值

    nullif:

SQL> select nullif('a','b') from dual;

N
-
a

SQL> select nullif('a','a') from dual;

N
-

# 如果两个值相等,则返回空,否则返回第一个值

    decpde:

SQL> select deptno,decode(deptno,10,1,20,2,3) cc from emp;

    DEPTNO	   CC
---------- ----------
	20	    2
	30	    3
	30	    3
	20	    2
	30	    3
	30	    3
	10	    1
	20	    2
	10	    1
	30	    3
	20	    2
	30	    3
	20	    2
	10	    1
# 如果deptno 是10则返回10,如果是20则返回2,否则返回3

    case:

SQL> l
  1  select ename,sal,deptno,
  2  case deptno
  3  when 10 then sal*1.1
  4  when 20 then sal*1.5
  5  else
  6  sal*2
  7* end from emp
ENAME		  SAL	  DEPTNO	TMP
---------- ---------- ---------- ----------
SMITH		  800	      20       1200
ALLEN		 1600	      30       3200
WARD		 1250	      30       2500
JONES		 2975	      20     4462.5
MARTIN		 1250	      30       2500
BLAKE		 2850	      30       5700
CLARK		 2450	      10       2695
SCOTT		 3000	      20       4500
KING		 5000	      10       5500
TURNER		 1500	      30       3000
ADAMS		 1100	      20       1650
JAMES		  950	      30       1900
FORD		 3000	      20       4500
MILLER		 1300	      10       1430
# 上面定是等值链接,下面定可以用case 不等值链接
SQL> select ename,sal,
  2  case  
  3  when sal < 2000 then sal*1.5
  4  when sal >=2000 and sal <3000 then sal*1.8
  5  else
  6  sal*2 
  7  end as tmp from emp;

ENAME		  SAL	     TMP
---------- ---------- ----------
SMITH		  800	    1200
ALLEN		 1600	    2400
WARD		 1250	    1875
JONES		 2975	    5355
MARTIN		 1250	    1875
BLAKE		 2850	    5130
CLARK		 2450	    4410
SCOTT		 3000	    6000
KING		 5000	   10000
TURNER		 1500	    2250
ADAMS		 1100	    1650
JAMES		  950	    1425
FORD		 3000	    6000
MILLER		 1300	    1950

    1.4 正则表达式

SQL> select ename from emp where regexp_like(ename,'^s','i');

ENAME
----------
SMITH
SCOTT
# 正则表达式里面i 忽略大小写,c 区分大小写
  1* select ename from emp where regexp_like(ename,'^s','c')
SQL> /

no rows selected

SQL> select ename from emp where regexp_like(ename,'?c','i');

ENAME
----------
CLARK
SCOTT
# 名字当中包含定有c字母的,且不区分大小写

  1* select ename,regexp_instr(ename,'S') from emp
SQL> /

ENAME	   REGEXP_INSTR(ENAME,'S')
---------- -----------------------
SMITH				 1
ALLEN				 0
WARD				 0
JONES				 5
# 搜索S 在字字符从中出现定位置

  1* select ename ,regexp_count(ename,'T') from emp
SQL> /

ENAME	   REGEXP_COUNT(ENAME,'T')
---------- -----------------------
SMITH				 1
ALLEN				 0
WARD				 0
JONES				 0
MARTIN				 1
BLAKE				 0
CLARK				 0
SCOTT				 2
# 统计字符出现定次数

2    多行函数

    2.1 count()   求数量

SQL> select count(*),count(1),count(comm) from emp;

  COUNT(*)   COUNT(1) COUNT(COMM)
---------- ---------- -----------
	14	   14		4
# count(*) 不处理空值,count(comm) 会不记空值

    2.2 max() 求最大

  1* select max(comm) from emp
SQL> /

 MAX(COMM)
----------
      1400

    2.3 min() 求最小

SQL> select min(sal) from emp;

  MIN(SAL)
----------
       800

    2.4 avg() 求平均

SQL> select avg(sal) from emp;

  AVG(SAL)
----------
2073.21429

    2.5 sum() 求和

SQL> select sum(comm) from emp;

 SUM(COMM)
----------
      2200

    2.6 group() 分组函数

SQL> select deptno,max(sal) from emp group by deptno;

    DEPTNO   MAX(SAL)
---------- ----------
	30	 2850
	20	 3000
	10	 5000

SQL> select job,max(sal) from emp group by job;

JOB	    MAX(SAL)
--------- ----------
CLERK		1300
SALESMAN	1600
PRESIDENT	5000
MANAGER 	2975
ANALYST 	3000

    2.7 having() 分组后筛选

SQL> select job,max(sal) from emp group by job having max(sal) >2000;

JOB	    MAX(SAL)
--------- ----------
PRESIDENT	5000
MANAGER 	2975
ANALYST 	3000

    2.8 rollup() 滚动分组

SQL> select deptno,max(sal) from emp group by rollup(deptno);

    DEPTNO   MAX(SAL)
---------- ----------
	10	 5000
	20	 3000
	30	 2850
		 5000
# 滚动分组就是对rollup() 里面定每一个条件都分组一次,上面是对部门分组求最高薪水,并对所有员工求了一次最大薪水。

  1* select deptno,job,max(sal) from emp group by rollup(deptno,job)
SQL> /

    DEPTNO JOB	       MAX(SAL)
---------- --------- ----------
	10 CLERK	   1300
	10 MANAGER	   2450
	10 PRESIDENT	   5000
	10		   5000
	20 CLERK	   1100
	20 ANALYST	   3000
	20 MANAGER	   2975
	20		   3000
	30 CLERK	    950
	30 MANAGER	   2850
	30 SALESMAN	   1600
	30		   2850
			   5000
# 这个有两个条件,先对部门里面每一个job 最大薪水进行分组统计,最后在全部统计一次最高薪水

    2.9    cube() :交叉分组

SQL> select deptno,job,max(sal) from emp group by cube(deptno,job);

    DEPTNO JOB	       MAX(SAL)
---------- --------- ----------
			   5000
	   CLERK	   1300
	   ANALYST	   3000
	   MANAGER	   2975
	   SALESMAN	   1600
	   PRESIDENT	   5000
	10		   5000
	10 CLERK	   1300
	10 MANAGER	   2450
	10 PRESIDENT	   5000
	20		   3000
	20 CLERK	   1100
	20 ANALYST	   3000
	20 MANAGER	   2975
	30		   2850
	30 CLERK	    950
	30 MANAGER	   2850
	30 SALESMAN	   1600

18 rows selected.
# 这个是先对所有求最大,在对deptno求最大,在对deptno 下的job求最大

    2.10 grouping sets 合并分组

  1* select deptno,job,max(sal) from emp group by grouping sets(deptno,job)
SQL> /

    DEPTNO JOB	       MAX(SAL)
---------- --------- ----------
	30		   2850
	20		   3000
	10		   5000
	   CLERK	   1300
	   SALESMAN	   1600
	   PRESIDENT	   5000
	   MANAGER	   2975
	   ANALYST	   3000
# 相当与对两个条件分别进行分组并把结果合并在一起

  1* select deptno,job,max(sal) ,grouping(deptno),grouping(job) from emp group by grouping sets(deptno,job)
SQL> /

    DEPTNO JOB	       MAX(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------- --------- ---------- ---------------- -------------
	30		   2850 	       0	     1
	20		   3000 	       0	     1
	10		   5000 	       0	     1
	   CLERK	   1300 	       1	     0
	   SALESMAN	   1600 	       1	     0
	   PRESIDENT	   5000 	       1	     0
	   MANAGER	   2975 	       1	     0
	   ANALYST	   3000 	       1	     0

# grouping 能检验字段是否参与分组

    2.11 分析函数和开窗

        row_number() over()

-- 组内排序
select a.*,row_number() over(partition by a.deptno order by a.sal desc ) num from emp a;

    rank() over():

-- 组内排序,处理并列

select a.*,rank() over(partition by a.deptno order by a.sal desc ) num from emp a;

    dense_rank() over():

-- 组内排序,处理并列,不跳过后面的排名
select a.*,dense_rank() over(partition by a.deptno order by a.sal desc) num from emp a;

-- 开窗
select a.*,avg(a.sal) over() from emp a;

    3多表查询

        3.1 自然连接natural join

-- 自然连接
-- 自然连接的两个表字段名称相同,属性相同的连个字段会自动连接
select * from emp natural join dept;

        

        3.2 join using 等值连接

-- 等值连接
-- join using 要求名称相同,属性可以不同
select * from emp join dept using (deptno);

    3.3 join on 

-- join on 字段名称可以不同,属性也可以不同.
select * from emp a join dept b on a.deptno = b.deptno;

    3.4 union和union all 求合集

-- union 和 union all

  -- union 剔除重复并排序
select 1 from dual
union
select 2 from dual
union
select 1 from dual;

   -- union all 不剔重,且不排序
   
select 1 from dual
union all
select 2 from dual
union all
select 1 from dual;

    3.5 intersect 求交集

-- intersect   求交集
select * from emp a where a.ename in ('SMITH','JONES')
intersect
select * from emp a where a.ename in ('SMITH')

    3.6 minus 求差集

-- minus 求差集
select * from emp a where a.ename in ('SMITH','JONES')
minus
select * from emp a where a.ename in ('SMITH');

    3.7 时间戳

-- 时间戳
select systimestamp from dual;

注意:表用delete 删除之后数据所在的子块还在,只是打上一个标记,还是可以通过日志找回来,如果想要彻底删除,则 alter table_name <> shrink space 删除表之后 空间的回收

融合语句:mergo into ,存在的做更新,不存在的做插入

        merge into table_name1 using table_name2  on 条件

        when matchd then update set 列名=列名

        when not matchd  then insert values (列值)

SQL> l
  1  merge into emp1
  2  using emp
  3  on (emp1.empno = emp.empno)
  4  when matched then update set sal=sal/2
  5* when note matched then insert values (emp.ename,emp.sal,emp.deptno,emp.empno)
SQL> select * from emp1;

ENAME		  SAL	  DEPTNO      EMPNO
---------- ---------- ---------- ----------
ALLEN		  800	      30       7499
WARD		  625	      30       7521
MARTIN		  625	      30       7654
BLAKE		 1425	      30       7698
TURNER		  750	      30       7844
JAMES		  475	      30       7900
KING		 5000	      10       7839
CLARK		 2450	      10       7782
SCOTT		 3000	      20       7788
JONES		 2975	      20       7566
MILLER		 1300	      10       7934
FORD		 3000	      20       7902
SMITH		  800	      20       7369
ADAMS		 1100	      20       7876
# emp1 表里面有的薪水减半,没有的插入

    4 事务控制语言TCL

        事务是如何产生的:DML,DDL,DCL

        事务结束:commit,ddl,exit,conn,rollback

        事务主要针对DML

        commit 事务完成

        rollback 只能在当前事务中回滚,会滚到上一次commit的位置。

        savepoint s1 设置保存点,以后可以回滚到当前位置,而不是上一次commit的位置

        rollback to s1

        用户做DML 的时候会申请锁死,有行级锁,有表级锁,update 是表锁 等事务完成之后才解锁。

 

        5    系统权限

            select * from system_privilege_map 查看系统权限

            查看当前用户有那些权限:

            select * from dba_sys_privs;

            select * from user_sys_privs;

SQL> select * from user_sys_privs;

USERNAME		       PRIVILEGE				ADM
------------------------------ ---------------------------------------- ---
SCOTT			       UNLIMITED TABLESPACE			NO
# 只有一个,使用表空间权限

          查看当前会话下的权限

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION           # 创建会话
UNLIMITED TABLESPACE     # 使用表空间
CREATE TABLE             # 建表
CREATE CLUSTER           # 创建游标
CREATE SEQUENCE          # 创建序列
CREATE PROCEDURE         # 创建
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.
# 当前会话下的所有系统权限

        schema ------ username  

        系统权限的授权

        grant 系统权限1,系统权限2,…… to 用户名

SQL> create user test identified by test;
SQL> conn test/test
ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied
SQL> grant create session to test;

Grant succeeded.

SQL> conn test/test
Connected.
# 授权以后已经可以链接了
create table tmp1 d as select 1 from dual
                  *
ERROR at line 1:
ORA-00922: missing or invalid option

        grant 系统权限,…… to 用户名 with admin option 授予系统权限级联

        注意:当有级联权限的用户的权限被收回,那他授权其他用户的权限是不会被回收的。

 

        权限回收:

        revoke 系统权限 from username

 

         6    对象权限

            用户要查看其他用户下的对象,则需要拥有对象权限

SQL> select * from table_privilege_map;

 PRIVILEGE NAME
---------- ----------------------------------------
	 0 ALTER # 修改
	 1 AUDIT # 审计
	 2 COMMENT # 注释
	 3 DELETE
	 4 GRANT
	 5 INDEX
	 6 INSERT
	 7 LOCK
	 8 RENAME
	 9 SELECT
	10 UPDATE

 PRIVILEGE NAME
---------- ----------------------------------------
	11 REFERENCES # 重定义
	12 EXECUTE #执行
	16 CREATE
	17 READ
	18 WRITE
	20 ENQUEUE
	21 DEQUEUE
	22 UNDER
	23 ON COMMIT REFRESH
	24 QUERY REWRITE
	26 DEBUG

 PRIVILEGE NAME
---------- ----------------------------------------
	27 FLASHBACK # 闪回
	28 MERGE VIEW
	29 USE
	30 FLASHBACK ARCHIVE

26 rows selected.

# 查看当前系统有那些对象权限
select * from dba_tab_privs; 表示dba 当前有那些对象权限

SQL> select * from user_tab_privs;

no rows selected
# 普通用户下没有任何对象权限

      授予对象权限:

        grant 对象权限 on 方案名.对象名 to 用户名

SQL> grant select on test.tmp1 to scott ;

Grant succeeded.
# 下面可以在scott 用户下面查看test 用户下的表tmp1
SQL> select * from test.tmp1;

	 D
----------
	 1

SQL> show user;
USER is "SCOTT"

# scott 用户已经可以访问test用户的表tmp1

SQL> insert into test.tmp1 select 2 from dual;
insert into test.tmp1 select 2 from dual
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

# 但是不能修改别的用户的表,需要进行授权

SQL> grant update(p) on test.tmp1 to scott;

Grant succeeded.
# 重新授权了对单个列定修改权限
SQL> update test.tmp1 a set a.p = 0 where a.d = 1;

1 row updated.

SQL> select * from test.tmp1;    

	 D	    P
---------- ----------
	 1	    0
# 授权列修改权限的列可以被修改,
SQL> update test.tmp a set a.d = 0 where a.d = 1;
update test.tmp a set a.d = 0 where a.d = 1
            *
ERROR at line 1:
ORA-00942: table or view does not exist
# 没有授权被修改的列,不可以被修改

 

  1* select * from user_tab_privs
SQL> /

GRANTEE 		       OWNER			      TABLE_NAME		     GRANTOR			    PRIVILEGE				     GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
SCOTT			       TEST			      TMP1			     TEST			    SELECT				     NO  NO
# scott 有用户test 用户下表tmp1 的 select 权限

SQL> select * from user_col_privs;

GRANTEE 		       OWNER			      TABLE_NAME		     COLUMN_NAME		    GRANTOR			   PRIVILEGE	    GRA
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ---
SCOTT			       TEST			      TMP1			     P				    TEST			   UPDATE	    NO

# scott 有用户test 的表tmp1 的p列的update 权限

        对象也有级联权限

        grant 对象权限 on 方案名.对象名 to 用户名 with grant option

SQL> grant select on test.tmp1 to scott with grant option;

Grant succeeded.

# 其他用户对用户test用户下表tmp1 的select 权限授予了用户scott,并级联
GRANTEE 		       OWNER			      TABLE_NAME		     GRANTOR			    PRIVILEGE				     GRA
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ---
HIE
---
SCOTT			       TEST			      TMP1			     TEST			    SELECT				     YES
NO

# 上面的yes 就是有权限级联

 

    7    角色权限

                是对象权限和系统权限的打包

        7.1 创建角色

            create role 角色的名字 identified by 密码。密码可以不用授权。

            查看当前系统有那些角色

select * from role_sys_privs

       

  1* select distinct role from role_sys_privs
SQL> /

ROLE
------------------------------
EXP_FULL_DATABASE
AQ_ADMINISTRATOR_ROLE
DBA
OEM_ADVISOR
RECOVERY_CATALOG_OWNER
SCHEDULER_ADMIN
OLAP_USER
RESOURCE
IMP_FULL_DATABASE # 导入数据库
OWB$CLIENT # 客户端操作
DATAPUMP_EXP_FULL_DATABASE

ROLE
------------------------------
CONNECT # 链接角色
OLAP_DBA # 数据仓促挂历角色
JAVADEBUGPRIV
DATAPUMP_IMP_FULL_DATABASE
OEM_MONITOR # OEM 监控角色
MGMT_USER
LOGSTDBY_ADMINISTRATOR

18 rows selected.

    查看角色下面有那些权限

SQL> select distinct GRANTED_ROLE from dba_role_privs where grantee = 'DBA';
# 查看dba 下有那些角色
SQL> select * from role_sys_privs where role = 'RESOURCE';

ROLE			       PRIVILEGE				ADM
------------------------------ ---------------------------------------- ---
RESOURCE		       CREATE SEQUENCE				NO
RESOURCE		       CREATE TRIGGER				NO
RESOURCE		       CREATE CLUSTER				NO
RESOURCE		       CREATE PROCEDURE 			NO
RESOURCE		       CREATE TYPE				NO
RESOURCE		       CREATE OPERATOR				NO
RESOURCE		       CREATE TABLE				NO
RESOURCE		       CREATE INDEXTYPE 			NO
# 看到resource 角色下面有8个系统权限

    查看当前用户有那些角色:

SQL> conn scott/tiger
Connected.
SQL> select * from user_role_privs;

USERNAME		       GRANTED_ROLE		      ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCOTT			       CONNECT			      NO  YES NO
SCOTT			       RESOURCE 		      NO  YES NO

SQL> select * from role_sys_privs;

ROLE			       PRIVILEGE				ADM
------------------------------ ---------------------------------------- ---
RESOURCE		       CREATE TRIGGER				NO
RESOURCE		       CREATE SEQUENCE				NO
RESOURCE		       CREATE TYPE				NO
RESOURCE		       CREATE PROCEDURE 			NO
RESOURCE		       CREATE CLUSTER				NO
CONNECT 		       CREATE SESSION				NO
RESOURCE		       CREATE OPERATOR				NO
RESOURCE		       CREATE INDEXTYPE 			NO
RESOURCE		       CREATE TABLE				NO
# 查看当前scott 用户下所有的角色系统权限

SQL> select * from role_tab_privs;

no rows selected
# scott用户下没有角色对象权限

SQL> select * from role_role_privs;

no rows selected

# scott 用户没有角色下的角色

    系统权限授予角色

    grant 系统权限  to 角色名称

    grant 对象权限 on 方案名.对象名 to 叫

SQL> create role test_role;

Role created.
# 创建角色

SQL> grant create session,create table to test_role;

Grant succeeded.
# 角色授予系统权限

SQL> grant select,insert,update on test.tmp1 to test_role;

Grant succeeded.

# 角色授权对象权限

SQL> select role,privilege from role_sys_privs where role = 'TEST_ROLE';

ROLE			       PRIVILEGE
------------------------------ ----------------------------------------
TEST_ROLE		       CREATE SESSION
TEST_ROLE		       CREATE TABLE

# 查看角色下的系统权限

SQL> select role,owner,table_name,column_name,privilege from role_tab_privs where role = 'TEST_ROLE';

ROLE			       OWNER			      TABLE_NAME		     COLUMN_NAME		    PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------
TEST_ROLE		       TEST			      TMP1							    SELECT
TEST_ROLE		       TEST			      TMP1							    UPDATE
TEST_ROLE		       TEST			      TMP1							    INSERT

# 查看角色下的对象权限

     对象权限授予角色

    grant 对象权限 on 方案名.对象名 to 角色

 

展开阅读全文
打赏
0
2 收藏
分享
加载中
更多评论
打赏
0 评论
2 收藏
0
分享
返回顶部
顶部