Oracle Day7

本节内容

  1.数据库安全性

  2.创建用户/删除用户/修改用户/解锁与锁定用户

  3.系统权限管理

  4.对象权限管理

  5.角色管理

  6.PL/SQL编程

一、数据库安全性

      数据库中存储的是数据，那么在这个互联网发达的时代，对于互联网公司来说什么最值钱：当然是数据。所以数据的安全性非常重要。

 Oracle中对数据的安全性的防护：

     1.数据库登录需要用户名密码。

     2.操作数据库需要相应的权限。

二、用户的操作

  创建用户：需要管理员来创建 system。或者超级管理员sys

   创建用户：

      

create user 用户名 IDENTIFIED BY 密码

修改用户密码

alter  user  用户名  IDENTIFIED BY 密码

删除用户：

  

drop user 用户名 Cascade  //删除用户下所有的数据库对象

锁定用户与解锁用户

alter user  用户名  account  lock //锁定

alter  user 用户名  account  unlock//解锁

　　

三、数据库权限

    系统权限：控制用户的登录，创建数据库对象的权限。

   管理员可以为用户授权

     

GRANT  权限  TO 用户名 [with admin option]

with admin option 选项表示该用户可以将这种系统权限授予其他用户

收回权限

REVOKE  权限  FROM   用户名

　　

　　

    对象权限：

 不同的对象具有不同的对象权限
对象的拥有者拥有所有权限
对象的拥有者可以向外分配权限   

给对象授权：

   

GRANT 权限
on  对象
to  用户 
[WITH GRANT OPTION];

WITH GRANT OPTION//被授权用户可将所获得的权限再次授予其它用户

给用户赋予表空间

给用户赋予表空间：
alter user 用户名  quota unlimited on users

收回对象权限

REVOKE 权限
on  对象
from 用户名
使用 WITH GRANT OPTION 子句所分配的权限同样被收回

　　

角色管理

   我们的权限是非常多的，那么如果我们要给多个用户授权那么就很麻烦，所以我们使用角色来管理权限。

  创建角色

    

CREATE ROLE  角色名

给角色授权

GRANT 权限  TO 角色名

这样以后我们要给用户授权只需要将角色授予这个用户就可以了。

将角色赋予用户：
GRANT   角色  TO  用户名

删除角色

删除角色
DROP ROLE  角色名

在oracle数据库中有2个预定义的角色，这2个角色包含了我们开发中所使用的有所权限

connect,resource

所以我们只要将这2个角色赋予用户就可以达到我们开发的要求。　　

四、PL/SQL编程

   　　我们使用PL/SQL工具类进行oracle数据库的开发

          PL/SQL程序的基本结构。

                 PL/SQL程序由3部分组成： 声明部分    执行部分（必须的）   异常处理部分

                 

DECLARE  
   /* 声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 */
BEGIN
   /*  执行部分:  过程及SQL 语句  , 即程序的主要部分 */
EXCEPTION
   /* 执行异常部分: 错误处理  */
END;

 PL/SQL程序块可以分为3类：

匿名块：动态构造，只能执行一次 （一般的sql语句）
子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们
触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序　

变量 ：

         声明变量：

                

变量名    [constant]  类型  [not null] [:=值]
变量赋值 
变量名：=值

声明常量：
变量名  constant 类型  ：=值 

注意：如果变量在声明时使用了NOT NULL选项则必须为变量指定初值。
      如果变量在声明时使用了CONSTANT选项则必须为变量指定初值，并且该初值不能被改变

使用：select ...into 语句可以把查询出的一条数据赋值给变量。

注意： 用select… into…语句给变量赋值必须要查询返回一条记录，不能多条记录，也不能没有记录

declare   
 v_ename varchar2(20);    
begin    
  select first_name into v_ename from employees     	where employee_id=&no;
  dbms_output.put_line(v_ename);//输出语句
end;

PL/SQL编程中的复合类型：

         有时我们表中的列非常多的化，使用上面的变量方式不是很方便

        记录类型 ：记录类型是把逻辑相关的数据作为一个单元存储起　　

　　

义记录类型语法如下：
TYPE record_type IS RECORD(
   Field1 type1  [NOT NULL]  [:= exp1 ],
   Field2 type2  [NOT NULL]  [:= exp2 ],
   . . .   . . .
   Fieldn typen  [NOT NULL]  [:= expn ] ) ; 

%TYPE:定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同	，这时可以使用%TYPE

DECLARE
   -- 用 %TYPE 类型定义与表相配的字段
 type type_emp is record(
     empno employees.employee_id%type,
     ename employees.first_name%type,
     sal   employees.salary%type);   

-- 声明接收数据的变量
   v_emp type_emp ; 

declare
  type type_emp is record(
    empno employees.employee_id%type,
    ename employees.first_name%type,
    sal   employees.salary%type);
    v_emp type_emp;
begin
  	select employee_id, first_name, salary into 			v_emp 
   	from employees 
    	where employee_id = &no;
      	dbms_output.put_line(v_emp.empno || '/' || 	v_emp.ename || '/' ||v_emp.sal);
end;

%ROWTYPE:返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
例：DECLARE
    	v_empno employees.employee_id%TYPE :=&no;
   	rec employees%ROWTYPE;
        BEGIN
    	SELECT * INTO rec FROM employees 
              WHERE employee_id=v_empno;
    	DBMS_OUTPUT.PUT_LINE('姓名:'||rec.first_name||'工	资:'||rec.salary||'入职时间:'||to_char(rec.hire_date,'yyyy-mm-	dd')); 
        END; 

PL/SQL中的流程控制

    　　条件结构：

          

IF <布尔表达式> THEN
		PL/SQL 和 SQL语句
END IF;
或
IF <布尔表达式> THEN
		PL/SQL 和 SQL语句
ELSIF 布尔表达式 THEN
         PL/SQL 和 SQL语句

ELSE
		其它语句
END IF; 

declare
  v_sal employees.salary%type;
begin
  select salary into v_sal
  from employees
  where employee_id = &no;
  
  if v_sal >= 20000 then
     dbms_output.put_line('土豪');
  elsif  v_sal >=10000 then
     dbms_output.put_line('白领');
  elsif  v_sal >=8000 then      
     dbms_output.put_line('蓝领');
  else
     dbms_output.put_line('没领');
  end if;
end;

　　

　　

           选择结构：

CASE语句的基本形式为 ：
CASE selector
	WHEN expression1 THEN result1
	WHEN expression2 THEN result2
	WHEN expressionN THEN resultN
				[ ELSE resultN+1]
END [CASE];
其中：ELSE子句是可选的，但是当检测表达式与任何一个检测值都不匹配时，PL/SQL会产生预定义错误CASE_NOT_FOUND，其错	误号是ORA-6592

　

 -- 相当于switch语句
 
 
declare  
   
  a number:=&a;
  
begin
    
    case a
      when 60 then
        dbms_output.put_line('刚及格'); 
       
      when 80 then 
          dbms_output.put_line('良好'); 
      
     when 90 then
        dbms_output.put_line('优秀');
         
     else
         dbms_output.put_line('没有这个选项');
     end case;
     
end;

　　

　

           循环结构：

            简单循环：

            

LOOP
  要执行的语句;
  EXIT WHEN <条件语句> /*条件满足，退出循环语句*/
END LOOP;

FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限  LOOP
               要执行的语句;
END LOOP; 
其中：
每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1
跟在IN REVERSE 后面的数字必须是从小到大的顺序，但不一定是整数，可以是能够转换成整数的变量或表达式
可以使用EXIT WHEN子句退出循环 

　

--2.循环结构
--do...while...循环
declare
  v_no number(10) := 0;
  v_sum number(10) := 0;
begin
  loop
    v_sum := v_sum + v_no;
    v_no := v_no + 1;
    exit when v_no = 10;
  end loop;
  dbms_output.put_line('和为:' || v_sum);
end;

--while...循环
declare
  v_no number(10) := 0;
  v_sum number(10) := 0;
begin
  while v_no<10 loop
    v_sum := v_sum + v_no;
    v_no := v_no + 1;
  end loop;
  dbms_output.put_line('和为:' || v_sum);
end;

--for循环
declare
  v_no number(10) := 0;
begin
   for v_no in 0..10 loop
     dbms_output.put_line('值为:' || v_no);
   end loop;  
end;
  
declare
  v_no number(10) := 0;
begin
   for v_no in reverse 0..10 loop
     dbms_output.put_line('值为:' || v_no);
   end loop;  
end;

　　

　

　　

          GOTO语句：

          

GOTO   label;
 . . .  . . .
<<label>>　/*标号是用<<　>>括起来的标识符 */ 
其中：
GOTO语句是无条件跳转到指定的标号去的意思 

begin
  
for  a in reverse 10..20 loop
  
 dbms_output.put_line(a);
 --无条件跳转语句
 goto flag;

end loop;

 <<flag>>
     dbms_output.put_line('这是goto语句');  

end;

　　

PL/SQL中的异常：

PL/SQL利用异常处理机制捕获并响应错误。异常处理机制允许你将错误处理代码与可执行语句清楚的分开。任何类型的错误都被视为程序中不该发生的异常情形。异常可以是以下之一：

系统产生的错误(比如“内存用尽”或者“相同的  索引值”)
用户操作造成的错误
应用程序给用户提出的警告

 

异常的类型：

 异常一般都出现在PL/SQL程序块的后半部分

    

begin
  
declare






EXCEPTION
   WHEN first_exception THEN  <code to handle first exception >
   WHEN second_exception THEN  <code to handle second exception >
   WHEN OTHERS THEN  <code to handle others exception >
END; 

异常处理可以按任意次序排列,但 OTHERS 必须放在最后

　命名的系统异常：

    　

declare
 
   e_id employees.employee_id%type:=&e_id;
    sal employees.salary%type;
begin
    
   select salary into sal from employees where employee_id=e_id;
   
exception
   --异常处理
  -- when no_data_found  then
    -- dbms_output.put_line('没有这个员工');
    --可以处理所有异常
   when others then
     dbms_output.put_line('没有这个员工');
    
 end; 

　　

 未命名的系统异常：

  系统只定义了错误号而没有给启异常名

 declare
 
     notnull exception;
     
     pragma exception_init(notnull,1722);
    -- e_id employees.employee_id%type:=&no;
     emp employees%rowtype;
    begin
      
       select * into emp  from employees where employee_id='小明'; 
       
   exception
 
         
    when  notnull then
          dbms_output.put_line(sqlcode||'  '||sqlerrm);
 
 
   end;

　　命名的用户自定义异常

      用户定义的异常是通过显式使用 RAISE 语句来触发。当引发一个异常时，控制就转向到 EXCEPTION块异常部分，执行异常处理代码

      这类异常情况的处理，步骤如下 ：
  在PL/SQL 块的声明部分定义异常情况 ：
       <异常情况>  EXCEPTION;
   RAISE <异常情况>
   在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理

   

 declare
    a number:=10;
    notnull exception;
    
    begin
      
      if a<20 then
        RAISE notnull;
        
      end if;
      
      exception
        
        when notnull then
          
    dbms_output.put_line('这个值不符合要求');
    end;

　　

未命名的用户自定义异常：

  

--未命名的用户自定义异常
    
    
    declare 
       e_id employees.employee_id%type:=&no;
       e_name employees.last_name%type;
       
       begin
         
         select last_name into e_name from employees where employee_id=e_id;
           dbms_output.put_line(e_name);
      exception
         when others then
            raise_application_error(-20002,'不存在这个员工');
            
            
         end;