oracle的学习Ⅵ（存储过程、函数、包、触发器、简单的用户管理和权限管理）

命名块(存储过程、函数、包、触发器)，而PL/SQL是未命名的不利于重复利用；而且命名块可以运行在服务器端，而PL/SQL块只能运行在客服端

一、存储过程

创建格式

要注意下面存储过程的定义里面数据类型不能有长度

create or replace procedure procedure_name[(参数1 参数类型1 数据类型1, ...)]
is|as			# 不可省略
	变量声明;
begin 
    过程主要功能的实现;
    exception
end;

如：

create or replace procedure proc1
is 
    cursor cur1 is select ename, sal from emp order by empno;
    rec_cur cur1%rowtype;
    v_avgsal emp.sal%type;
begin 
    select avg(sal) into v_avgsal from emp;
    open cur1;
    loop
        fetch cur1 into rec_cur;
        exit when cur1%notfound;
        dbms_output.put_line(rec_cur.ename || '  ' || rec_cur.sal || '  ' || (rec_cur.sal-v_avgsal));
    end loop;
    close cur1;
end;

参数类型

in – 表明此参数为输入参数，默认为in
out – 表明此参数为输出参数
in out – 表明此参数为输入或输出参数

create or replace procedure proc3(v_empno in emp.empno%type, v_sal out emp.sal%type)
is 
begin 
    select sal into v_sal from emp where empno = v_empno;
    dbms_output.put_line(v_empno || '的工资是：' || v_sal);
exception
    when no_data_found then
        dbms_output.put_line(v_empno || '不存在');
end;

调用方式

方法一：块中调用

格式：

begin
    procedure_name[(参数列表)];			# 当没有参数时括号可以不用写
end;

例如：

declare 
    v_sal1 emp.sal%type;
begin 
    proc3(7788, v_sal1);
    dbms_output.put_line('传出的工资是：' || v_sal1);
end;

方法二：execute与结合变量调用

结合变量类似于全局变量，不过它的生命周期为当前的sqlplus
格式：

variable 变量名 变量类型;					# 结合变量的定义
execute procedure_name[(参数列表)];		# execute执行存储过程
print :变量名;			# 注意这里的print只能打印变量，不能打印由变量组成的字符串

例如：

variable v_sal number;
execute proc3(7788, :v_sal1);
print :v_sal1;

二、函数

函数与存储过程类似，只是函数有返回值而存储过程没有，并且函数与存储过程的调用方式有稍微不同

创建格式

要注意下面函数的定义里面数据类型不能有长度

create or replace function function_name[(参数1 参数类型1 数据类型1, ...)]
return 数据类型				# 这里的数据类型不能有长度
is|as			# 不可省略
	变量声明;
begin 
    过程主要功能的实现;
    return 表达式;
exception
	when 异常 then
		异常处理;
		return 表达式;		
end;

如：

create or replace function fun1(v_empno in emp.empno%type)
return number			# 不能有长度
is 
    v_sal emp.sal%type;
begin 
    select sal into v_sal from emp where empno = v_empno;
    return (v_sal);
exception
    when no_data_found then 
        return null;
end;

参数类型

in – 表明此参数为输入参数，默认为in
out – 表明此参数为输出参数
in out – 表明此参数为输入或输出参数

create or replace procedure proc3(v_empno in emp.empno%type, v_sal out emp.sal%type)
is 
begin 
    select sal into v_sal from emp where empno = v_empno;
    dbms_output.put_line(v_empno || '的工资是：' || v_sal);
exception
    when no_data_found then
        dbms_output.put_line(v_empno || '不存在');
end;

调用方式

方法一：块中调用

格式：

begin
    function_name[(参数列表)];			# 当没有参数时括号可以不用写
end;

例1：

declare 
	v_sal1 emp.sal%type;
begin 
    v_sal1:=fun1(7788);
    dbms_output.put_line('7788的工资是：'||v_sal1);
end;

或

begin 
    dbms_output.put_line(fun1(7788));
end;

方法二：execute与结合变量调用

格式：

variable 变量名 变量类型;					# 结合变量的定义
execute :变量名 := function_name[(参数列表)];		# execute执行存储过程
print :变量名;			# 注意这里的print只能打印变量，不能打印由变量组成的字符串

例如：

variable v_sal1 number;
execute :v_sal1 := fun1(7788);
print :v_sal1;

方法三：利用select语句和dual虚拟表来调用

select func1(7788) from dual;

三、包

包可以把功能相近的对象（变量、函数、存储对象等）集合在一起再将包授权给某一用户，不同包可以有相同的对象名。包的声明与实现缺一不可

包的声明

包中包含的公有变量、游标、函数、存储过程等的声明，这里声明的东西意味着用户可从外界调用

create or replace package package_name
is
 公有变量、游标、函数、存储过程等的声明;
end;

包的实现

包含包的私有成员的实现（包含有私有变量、游标、函数、存储过程）以及包的公有成员的实现（在包的声明里面）。

create or replace package body package_name
is
 公有变量、游标、函数、存储过程的实现（外界可调用）;
 私有变量、游标、函数、存储过程的实现（外界不可调用）;
end;

例如：

# 写一个包，可以查看emp表员工信息，获得emp的员工人数， 修改员工工资、修改员工部门、将修改保存到表中。要求员工人数用函数实现。

create or replace package pack_emp		# 包的声明
is
	procedure emp_information(v_empno emp.empno%type);
	function emp_number return number;
	rec_emp emp%rowtype;
	procedure emp_modify_sal(v_empno emp.empno%type, v_sal emp.sal%type);
	procedure emp_modify_deptno(v_empno emp.empno%type, v_deptno emp.deptno%type);
end;
/

create or replace package body pack_emp		# 包的实现
is
	procedure emp_update(rec_emp emp%rowtype)
	is
	begin
		update emp set sal = rec_emp.sal, deptno = rec_emp.deptno where empno = rec_emp.empno;
	exception
		when others then
			dbms_output.put_line('EXCEPTIONS CODE:' || sqlcode() || '  ' || 'EXCEPTION INFORMATION:' || sqlerrm());
	end;

	procedure emp_information(v_empno emp.empno%type)
	is
		rec_emp emp%rowtype;
	begin
		select * into rec_emp from emp where empno = v_empno;
		dbms_output.put_line('Employee Information:' || rec_emp.empno || '  ' || rec_emp.ename || '  ' || rec_emp.sal);
	exception
		when no_data_found then
			dbms_output.put_line('NO_DATA_FOUND');
		when others then
			dbms_output.put_line('EXCEPTIONS CODE:' || sqlcode() || '  ' || 'EXCEPTION INFORMATION:' || sqlerrm());
	end;	

	function emp_number return number
	is
		v_number number;
	begin
		select count(*) into v_number from emp;
		return v_number;
	end;

	procedure emp_modify_sal(v_empno emp.empno%type, v_sal emp.sal%type)
	is
	begin
		select * into rec_emp from emp where empno = v_empno;
		rec_emp.sal := v_sal;
		emp_update(rec_emp);
	exception
		when no_data_found then
			dbms_output.put_line('NO_DATA_FOUND');
		when others then
			dbms_output.put_line('EXCEPTIONS CODE:' || sqlcode() || '  ' || 'EXCEPTION INFORMATION:' || sqlerrm());
	end;
		 
	procedure emp_modify_deptno(v_empno emp.empno%type, v_deptno emp.deptno%type)
	is
	begin
		select * into rec_emp from emp where empno = v_empno;
		rec_emp.deptno := v_deptno;
		emp_update(rec_emp);
	exception
		when no_data_found then
			dbms_output.put_line('NO_DATA_FOUND');
		when others then
			dbms_output.put_line('EXCEPTIONS CODE:' || sqlcode() || '  ' || 'EXCEPTION INFORMATION:' || sqlerrm());
	end;
end;

四、触发器

在满足特定事件时自动执行，它可实现更加复杂的数据完整性和动态备份

类型

• DML触发器（有DML事件时触发，作用在表上）
• 替代触发器（instead of 触发器，对视图进行操作时触发，作用在视图上）
• 用户触发器（当有用户事件时触发，作用在user上）
• 数据库系统触发器（当有系统事件时触发，作用在系统上）

语法

create or replace trigger trigger_name		# 没有参数，不能被调用
{before|after}					# 触发时间，注意先后
trigger_event					# 触发事件（insert，update，delete）
on table_name					# 触发对象
[for each row]					# 触发级别，指定则为行级，没有则为语句级
[when trigger_condition]		# 添加的触发条件，一定要加括号且里面的列要加前缀且没有冒号，且要注意一定要加括号（如：old.sal）
trigger_body					# 触发体，标准的PL/SQL块

注意：

• 行级触发器就是该SQL影响的行数等于执行触发器的次数
• 语句级触发器不管该SQL影响多少条数据，触发器只执行一次
• 三个谓词
  • inserting（当insert语句执行时为true，否则为false）
  • updating（当update 语句执行时为true，否则为false）
  • deleting（当delete语句执行时为true，否则为false）
• 两个属性：
  • :old（旧值），:new（新值）
  • insert只有:new，delete只有:old，update有:new和:old
  • 当 :new和:old 出现在when里面时要 去掉冒号
• 在DML触发器中，不能直接或间接使用commit，rollback等事务处理语句
• 触发事件有insert，update，delete，update of col_name或他们的组合，如insert or update of sal or delete，这里没有insert/delete of col_name的方式

例一（级联更新）：

# 创建一个触发器，当修改dept表的部门号时，emp表中的部门号随之修改

create or replace trigger tri2
after					# 在修改dept表后在修改emp表
update of deptno 
on dept
for each row
begin 
	update emp set deptno =: new.deptno where deptno =: old.deptno;
end;

例二（级联删除）：

# 写一个级联删除触发器。

create or replace trigger tri3
before				# 在删除dept表前在删除emp表
delete
on dept
for each row
begin 
	delete from emp where deptno=:old.deptno;
end;

例三（动态备份）：

# 实现对dept表的动态更新，新表为new_dept

create table new_dept as select * from dept;	# 只复制表结构，没有复制约束
create or replace trigger tri4
before 
delete or update or insert
on dept
for each row
begin 
	if inserting then
		insert into new_dept values (:new.deptno, :new.dname, :new.loc);
	elsif deleting then
		delete from new_dept where deptno = :old.deptno and dname = :old.dname and loc = :old.loc;
	else
		update new_dept set deptno = :new.deptno, dname = :new.dname, loc = :new.loc where deptno = :old.deptno and dname = :old.dname and loc = :old.loc;
	end if;
end;

五、用户管理

必须由管理员完成对用户的管理

新建

create user username identified by password;

create user test identified by oracle;

修改

alter user test identified by oracle11;

删除

drop user test;

六、权限管理

权限又分为系统权限和对象权限

系统权限

它允许用户执行特定的数据库动作，如创表，创建会话等，权限有：create session, create table , create view, create index , create any table, drop any table等

系统权限的授予

grant privilege[, privilege…] to username [with admin option];

with admin option使被授予用户具有相同权限的分配权力，可将此权限授予他人

grant create session to test;  sys--test		test用户不可以将此权限授予其他用户
grant create session to test with admin option; sys--test--test1  test用户可以将此权限授予其他用户（test1）

系统权限的回收

要注意的是系统权限的回收不具有级联性

revoke privilege[, privilege…] from username;

revoke create session from test;  sys  test--test1  系统只收回了sys对test的授权，没有收回test对test1的授权

对象权限

它允许用户对特定的对象进行操作，权限有select, insert, update, delete, execute等，简单来说系统权限与对象权限作用的对象不同，且系统权限有多个单词，对象权限只有一个对象

对象权限的授予

grant object_priv | all [(columns)] on object to username [with grant option];

columns是把特定列的权限授予他人
with grant option使被授予用户具有相同权限的分配权力，可将此权限授予他人

grant select on dept,update on emp to test; scott--test   scott把权限授予test
grant select on dept to test with grant option; scott--test--test1  scott把权限授予test后，test可以打权限授予test1

对象权限的回收

要注意的是对象权限的回收具有级联性

revoke object_priv | all on object from username;

revoke select on dept  from test;  scott  test  test1  对象权限收回具有级联型

七、其他

命名块查错

使用show errors命令与edit命令可以快速的查错和改错

查看定义的函数与存储过程等等信息

user_objects表里面包含了函数、存储过程、索引、触发器、表、视图等对象
user_source是对资源的描述，其中text为资源的内容，而name为资源的名称

describle user_objects;	
col object_name for a10;
select object_name, object_type from user_objects;		# 查看名字与类型
describle user_source;
select text from user_source where name = 'FUN1';	# 查看具体的代码

输出格式化

这里的a10表示一行显示10个长度的字符串，若字符串过长则换行；99999来表示日期类型，其中9为占位符
输出格式化详细信息点击这里

col object_name for a10;

删除

drop function|procedure|package name;

如何使新建用户能够创建并添加数据

①在sys下创建用户
②授权：grant create session, create table, unlimited tablespace to test; 注意一定要设置unlimited tablespace否则无法添加数据，因为表没有分配大小
③切换到新用户。