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程序块的后半部分
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;
