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

叁歲伎倆 2022-12-22 13:27 113阅读 0赞

**命名块(存储过程、函数、包、触发器)**，而**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 – 表明此参数为输入或输出参数

## 调用方式 ##

#### 方法一：块中调用 ####

格式：

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为占位符**  
输出格式化详细信息[点击这里][Link 1]

col object_name for a10;

## 删除 ##

drop function|procedure|package name;

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

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

[Link 1]: https://www.cnblogs.com/kingle-study/p/11064387.html