Oracle PL/SQL语言初级教程之过程和函数

快来打我* 2022-09-20 08:44 168阅读 0赞

**　过程和函数**  
  
　　过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。过程有零个或多个参数，没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的主要区别不在于返回值，而在于他们的调用方式。过程是作为一个独立执行语句调用的：  
  
pay\_involume(invoice\_nbr,30,due\_date);  
  
　　函数以合法的表达式的方式调用：  
  
order\_volumn:=open\_orders(SYSDATE,30);  
  
　　创建过程的语法如下：

<table> 
 <tbody> 
  <tr> 
   <td>CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name<br>[parameter_lister]<br>{AS|IS}<br>declaration_section<br>BEGIN<br>executable_section<br>[EXCEPTION<br>exception_section]<br>END [procedure_name] </td> 
  </tr> 
 </tbody> 
</table>

每个参数的语法如下：  
  
paramter\_name mode datatype \[(:=|DEFAULT) value\]  
  
　　mode有三种形式：IN、OUT、INOUT。  
  
　　IN表示在调用过程的时候，实际参数的取值被传递给该过程，形式参数被认为是只读的，当过程结束时，控制会返回控制环境，实际参数的值不会改变。  
  
　　OUT在调用过程时实际参数的取值都将被忽略，在过程内部形式参数只能是被赋值，而不能从中读取数据，在过程结束后形式参数的内容将被赋予实际参数。  
  
　　INOUT这种模式是IN和OUT的组合；在过程内部实际参数的值会传递给形式参数，形势参数的值可读也可写，过程结束后，形势参数的值将赋予实际参数。  
  
　　创建函数的语法和过程的语法基本相同，唯一的区别在于函数有RETUREN子句

<table> 
 <tbody> 
  <tr> 
   <td>CREATE [ OR REPLACE] FINCTION [schema.]function_name<br>[parameter_list]<br>RETURN returning_datatype<br>{AS|IS}<br>declaration_section<br>BEGIN<br>executable_section<br>[EXCEPTION]<br>exception_section<br>END [procedure_name] </td> 
  </tr> 
 </tbody> 
</table>

在执行部分函数必须有哟个或多个return语句。  
  
　　在创建函数中可以调用单行函数和组函数，例如：

<table> 
 <tbody> 
  <tr> 
   <td>CREATE OR REPLACE FUNCTION my_sin(DegreesIn IN NUMBER)<br>RETURN NUMBER<br>IS <br>pi NUMBER=ACOS(-1);<br>RadiansPerDegree NUMBER;<br><br>BEGIN<br>RadiansPerDegree=pi/180;<br>RETURN(SIN(DegreesIn*RadiansPerDegree));<br>END</td> 
  </tr> 
 </tbody> 
</table>

**包**  
  
　　包是一种将过程、函数和数据结构捆绑在一起的容器；包由两个部分组成：外部可视包规范，包括函数头，过程头，和外部可视数据结构；另一部分是包主体(package body),包主体包含了所有被捆绑的过程和函数的声明、执行、异常处理部分。  
  
　　打包的PL/SQL程序和没有打包的有很大的差异，包数据在用户的整个会话期间都一直存在，当用户获得包的执行授权时，就等于获得包规范中的所有程序和数据结构的权限。但不能只对包中的某一个函数或过程进行授权。包可以重载过程和函数，在包内可以用同一个名字声明多个程序，在运行时根据参数的数目和数据类型调用正确的程序。  
  
　　创建包必须首先创建包规范，创建包规范的语法如下：

<table> 
 <tbody> 
  <tr> 
   <td>CREATE [OR REPLACE] PACKAGE package_name<br>{AS|IS}<br>public_variable_declarations |<br>public_type_declarations |<br>public_exception_declarations |<br>public_cursor_declarations |<br>function_declarations |<br>procedure_specifications<br>END [package_name]</td> 
  </tr> 
 </tbody> 
</table>

创建包主体使用CREATE PACKAGE BODY语句：

<table> 
 <tbody> 
  <tr> 
   <td>CREATE [OR REPLACE] PACKAGE BODY package_name<br>{AS|IS}<br>private_variable_declarations |<br>private_type_declarations |<br>private_exception_declarations |<br>private_cursor_declarations |<br>function_declarations |<br>procedure_specifications<br>END [package_name]</td> 
  </tr> 
 </tbody> 
</table>

私有数据结构是那些在包主体内部，对被调用程序而言是不可见的。  
  
**　　触发器(Triggers)**  
  
　　触发器是一种自动执行响应数据库变化的程序。可以设置为在触发器事件之前或之后触发或执行。能够触发触发器事件的事件包括下面几种：  
  
　　DML事件  
　　DDL事件  
　　数据库事件  
  
　　DML事件触发器可以是语句或行级触发器。DML语句触发器在触发语句之前或之后触发DML行级触发器在语句影响的行变化之前或之后触发。用户可以给单一事件和类型定义多个触发器，但没有任何方法可以增强多触发器触发的命令。下表列出了用户可以利用的触发器事件：

<table style="WIDTH: 514px; HEIGHT: 278px"> 
 <tbody> 
  <tr> 
   <td>事件</td> 
   <td>触发器描述</td> 
  </tr> 
  <tr> 
   <td>INSERT </td> 
   <td>当向表或视图插入一行时触发触发器</td> 
  </tr> 
  <tr> 
   <td>UPDATE </td> 
   <td>更新表或视图中的某一行时触发触发器</td> 
  </tr> 
  <tr> 
   <td>DELETE</td> 
   <td>从表或视图中删除某一行时触发触发器</td> 
  </tr> 
  <tr> 
   <td>CREATE</td> 
   <td>当使用CREATE语句为数据库或项目增加一个对象时触发触发器</td> 
  </tr> 
  <tr> 
   <td>ALTER</td> 
   <td>当使用ALTER语句为更改一个数据库或项目的对象时触发触发器</td> 
  </tr> 
  <tr> 
   <td>DROP</td> 
   <td>当使用DROP语句删除一个数据库或项目的对象时触发触发器</td> 
  </tr> 
  <tr> 
   <td>START</td> 
   <td>打开数据库时触发触发器，在事件后触发</td> 
  </tr> 
  <tr> 
   <td>SHUTDOWN </td> 
   <td>关闭数据库时触发，事件前触发</td> 
  </tr> 
  <tr> 
   <td>LOGON</td> 
   <td>当一个会话建立时触发，事件前触发</td> 
  </tr> 
  <tr> 
   <td>LOGOFF</td> 
   <td>当关闭会话时触发，事件前触发</td> 
  </tr> 
  <tr> 
   <td>SERVER</td> 
   <td>服务器错误发生时触发触发器，事件后触发</td> 
  </tr> 
 </tbody> 
</table>

创建触发器的语法如下：

<table> 
 <tbody> 
  <tr> 
   <td>CREATE [OR REPLACE] TRIGGER trigger_name<br>{before|after|instead of} event<br>ON {table_or_view_name|DATABASE}<br>[FOR EACH ROW[WHEN condition]]<br>trigger_body</td> 
  </tr> 
 </tbody> 
</table>

只有DML触发器（INSERT、UPDATE、DELETE)语句可以使用INSTEAD OF触发器并且只有表的DML触发器可以是BEFORE或AFTER触发器。  
  
　　象约束一样触发器可以被设置为禁用或启用来关闭或打开他们的执行体(EXECUTE),将触发器设置为禁用或启用使用ALTER TRIGGER语句：

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TRIGGER trigger_name ENABLE;<br>ALTER TRIGGER trigger_name DISABLE;</td> 
  </tr> 
 </tbody> 
</table>

要禁用或启用表的所有触发器，使用ALTER TABLE语句

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TRIGGER table_name DISABLE ALL TRIGGER;<br>ALTER TRIGGER table_name ENABLE ALL TRIGGER;</td> 
  </tr> 
 </tbody> 
</table>

删除触发器使用DROP TRIGGER

<table> 
 <tbody> 
  <tr> 
   <td>DROP TRIGGER trigger_name;</td> 
  </tr> 
 </tbody> 
</table>

**　　数据字典**  
  
　　Oracle数据字典包含了用户数据库的元数据。带下划线的表名称中带OBJ$、UET$、SOURCE$，这些表是在执行CREATE DATABASE语句期间由sql.bsq脚本创建的，一般情况下用户很少访问这些表。脚本catalog.sql（通常位于$oracle\_home/rdbms/admin）在CREATE DATABASE语句之后立即运行，创建数据字典视图。  
  
　　数据字典视图大致可以分为三类：  
  
　　.前缀为USER\_的数据字典视图，包含了用户拥有的对象的信息。  
  
　　.前缀为ALL\_的数据字典视图，包含了用户当前可以访问的全部对象和权限的信息。  
  
　　.前缀为DBA\_的数据字典视图，包含了数据库拥有的所有对象和权限的信息。  
  
　　在绝大多数数据字典视图中都有象DBA\_TABLES,ALL\_TABLES和USER\_TABLES这样的视图家族。Oracle中有超过100个视图家族，所以要全面介绍这些视图家族是单调乏味的而且没有多大的意义。在下表中列出了最重要和最常用的视图家族，需要注意的是每个视图家族都有一个DBA\_,一个ALL\_一个USER\_视图。

<table style="WIDTH: 518px; HEIGHT: 416px"> 
 <tbody> 
  <tr> 
   <td>视图家族(View Family)</td> 
   <td>描述</td> 
  </tr> 
  <tr> 
   <td>COL_PRIVS</td> 
   <td>包含了表的列权限，包括授予者、被授予者和权限</td> 
  </tr> 
  <tr> 
   <td>EXTENTS </td> 
   <td>数据范围信息，比如数据文件，数据段名(segment_name)和大小</td> 
  </tr> 
  <tr> 
   <td>INDEXES</td> 
   <td>索引信息，比如类型、唯一性和被涉及的表</td> 
  </tr> 
  <tr> 
   <td>IND_COLUMNS </td> 
   <td>索引列信息，比如索引上的列的排序方式</td> 
  </tr> 
  <tr> 
   <td>OBJECTS </td> 
   <td>对象信息，比如状态和DDL time</td> 
  </tr> 
  <tr> 
   <td>ROLE_PRIVS</td> 
   <td>角色权限，比如GRANT和ADMIN选项</td> 
  </tr> 
  <tr> 
   <td>SEGMENTS </td> 
   <td>表和索引的数据段信息，比如tablespace和storage</td> 
  </tr> 
  <tr> 
   <td>SEQUECNCES</td> 
   <td>序列信息，比如序列的cache、cycle和ast_number</td> 
  </tr> 
  <tr> 
   <td>SOURCE </td> 
   <td>除触发器之外的所有内置过程、函数、包的源代码</td> 
  </tr> 
  <tr> 
   <td>SYNONYMS </td> 
   <td>别名信息，比如引用的对象和数据库链接db_link</td> 
  </tr> 
  <tr> 
   <td>SYS_PRIVS </td> 
   <td>系统权限，比如grantee、privilege、admin选项</td> 
  </tr> 
  <tr> 
   <td>TAB_COLUMNS </td> 
   <td>表和视图的列信息，包括列的数据类型</td> 
  </tr> 
  <tr> 
   <td>TAB_PRIVS </td> 
   <td>表权限，比如授予者、被授予者和权限</td> 
  </tr> 
  <tr> 
   <td>TABLES</td> 
   <td>表信息，比如表空间(tablespace),存储参数(storage parms)和数据行的数量</td> 
  </tr> 
  <tr> 
   <td>TRIGGERS </td> 
   <td>触发器信息，比如类型、事件、触发体(trigger body)</td> 
  </tr> 
  <tr> 
   <td>USERS</td> 
   <td>用户信息，比如临时的和缺省的表空间</td> 
  </tr> 
  <tr> 
   <td>VIEWS</td> 
   <td>视图信息，包括视图定义</td> 
  </tr> 
 </tbody> 
</table>

在Oracle中还有一些不常用的数据字典表，但这些表不是真正的字典家族，他们都是一些重要的单一的视图。

<table style="WIDTH: 510px; HEIGHT: 117px"> 
 <tbody> 
  <tr> 
   <td>VIEW NAME</td> 
   <td>描述</td> 
  </tr> 
  <tr> 
   <td>USER_COL_PRIVS_MADE</td> 
   <td>用户授予他人的列权限</td> 
  </tr> 
  <tr> 
   <td>USER_COL_PRIVS_RECD </td> 
   <td>用户获得的列权限</td> 
  </tr> 
  <tr> 
   <td>USER_TAB_PRIVS_MADE </td> 
   <td>用户授予他人的表权限</td> 
  </tr> 
  <tr> 
   <td>USER_TAB_PRIVS_RECD</td> 
   <td>用户获得的表权限</td> 
  </tr> 
 </tbody> 
</table>

其他的字典视图中主要的是V$视图，之所以这样叫是因为他们都是以V$或GV$开头的。V$视图是基于X$虚拟视图的。V$视图是SYS用户所拥有的，在缺省状况下，只有SYS用户和拥有DBA系统权限的用户可以看到所有的视图，没有DBA权限的用户可以看到USER\_和ALL\_视图，但不能看到DBA\_视图。与DBA\_,ALL,和USER\_视图中面向数据库信息相反，这些视图可视的给出了面向实例的信息。  
  
　　　在大型系统上化几周时间手工输入每一条语句  
  
　　　手工输入带用户名变量的语句，然后再输入每一个用户名，这需要花好几个小时的时间  
  
　　　写一条SQL语句，生成需要的ALTER USER语句，然后执行他，这只需要几分钟时间  
  
　　很明显我们将选择生成SQL的方法：  
  
　　例：

<table> 
 <tbody> 
  <tr> 
   <td>SELECT 'ALTER USER'||username||<br>'TEMPORARY TABLESPACE temp;'<br>FROM DBA_USERS<br>WHERE username&lt;&gt;'SYS' <br>AND temporary_tablespace&lt;&gt;'TEMP';</td> 
  </tr> 
 </tbody> 
</table>

这个查询的结果将被脱机处理到一个文件中，然后在执行：

<table> 
 <tbody> 
  <tr> 
   <td>ALTER USER SYSTEM TEMPORARY TABLESPACE temp;<br>ALTER USER OUTLN TEMPORARY TABLESPACE temp;<br>ALTER USER DBSNMP TEMPORARY TABLESPACE temp;<br>ALTER USER SCOTT TEMPORARY TABLESPACE temp;<br>ALTER USER DEMO TEMPORARY TABLESPACE temp; </td> 
  </tr> 
 </tbody> 
</table>