Oracle的pl/sql编程

Pl/sql是在标准sql语句基础上扩展的一种对oracle数据库进行编程的语句

编写规范

单行注释–

多行注释/* */

1. 当定义变量时，建议用v_作为前缀v_sal

2. 当定义常量时，建议用c_作为前缀c_rate

3. 当定义游标时，建议用_cursor作为后缀emp_cursor

4. 当定义例外时，建议用e_作为前缀e_error

块

存储过程，函数，触发器，包。他们的基本编程单元是块。

块可被独立编译，可以存储在数据库中，任何与数据库相连接的应用程序都可以访问这些存储的PL/SQL程序块。

块的结构：

Declare 定义部分---定义常量，变量，游标，例外，复杂数据类型

Begin 执行部分---要执行的pl/sql语句和sql语句

Exception 例外处理部分---处理运行的各种错误

End;

注：Declare和exception部分是可选，并不是必须的。

存储过程 Procedure

过程有零个或多个参数，没有返回值。

创建过程语法：

CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name
[parameter_lister]
{AS|IS}
declaration_section
BEGIN
executable_section执行语句
[EXCEPTION
exception_section]
END [procedure_name]

注：可不带参数，若带参格式为---（参数名 类型）--（in_empno number）

每个参数的语法如下：paramter_name mode datatype [(:=|DEFAULT) value]

　　mode有三种形式：IN、OUT、INOUT。
　　IN表示在调用过程的时候，实际参数的取值被传递给该过程，形式参数被认为是只读的，当过程结束时，控制会返回控制环境，实际参数的值不会改变。
　　OUT在调用过程时实际参数的取值都将被忽略，在过程内部形式参数只能是被赋值，而不能从中读取数据，在过程结束后形式参数的内容将被赋予实际参数。
　　INOUT这种模式是IN和OUT的组合；在过程内部实际参数的值会传递给形式参数，形势参数的值可读也可写，过程结束后，形势参数的值将赋予实际参数。

创建好过程，需要调用：

Exec 过程名(参数1…);

Dbms_output是oracle所提供的包，该包包含一些过程，put_line就是dbms_output包的一个过程。

1. 只包含执行部分的案例：

输出’hello’

Begin

Dbms_output.put_line(‘hello,world’);

End;

说明：在默认情况下，‘hello world’不输出，需要setserveroutput on;

2.

案例：根据用户输入的雇员编号，显示该雇员的名字

说明：&表示要接收从控制台输入的变量

|| 表示把两个串拼接

Declare

--定义变量的格式是：变量名称 变量的类型

v_enamevarchar2(8);

begin

--into 变量名 把查询的ename值放入v_ename变量

selectename into v_ename form emp where empno=&empno;

--输出v_ename

Dbms_output.put_line(‘雇员名是’||v_name);

End;

把上面的块，改为procedure

Createprocedure pro1(in_empno number) is

v_enamevarchar2(8);

begin

selectename into v_ename from emp where empno=in_empno;

Dbms_output.put_line(‘雇员名是’||v_name);

End;

执行：exec pro1(7369);

包含定义部分，执行部分和例外处理部分的块

1. 比如在实例2中，如果输入了不存在的雇员好，应当做例外处理

如果我们输入的用户编号不存在，则系统会提示异常，为了更加明确的指出错误，oracle提供了exception处理机制

快速入门：

Declare

v_ename varchar2(36)

begin

select ename into v_ename from emp where empno=&empno;

dbms_output.put_line(‘雇员名’||v_ename);

exception

when no_data_found then

dbms_output.put_line(‘你输入的编号有误！’);

end;

对该案例的细节说明：

这里涉及到底异常处理：

异常的基本语法：

Exception

When 异常的名称 then

//对异常进行处理的代码

//对异常进行处理的代码

When异常的名称2 then

//对这种异常处理

end;

注：then 后面都属于处理，可以有添加删除等等操作。

oracle提供的异常（参考pl/sql官方文档）

异常处理作用：

1. 可以给出捕获异常，并给出明确提示。

2. 有时可以利用异常，来进行业务处理

Declare

v_ename varchar2(36)

begin

select ename into v_ename from emp whereempno=&empno;

dbms_output.put_line(‘雇员名’||v_ename);

exception

when no_data_found then

dbms_output.put_line(‘你输入的编号有误！我帮你加一条’);

insert into emp (empno,ename) values(1.’马大哈’)；

end;

过程的进一步讲解：

Oracle过程，可以指定参数是输入的参数，还是输出的参数。

语法：create procedure 过程名(参数名 in 参数类型……参数名 out 参数类型……)is

//定义变量

Begin

//执行的语句

End;

注：变量可以有多个，默认是in

当编写过程时，可以输入show error 来显示具体的错误

函数Function

函数有零个或多个参数，并有一个返回值。

函数和过程的区别：函数必须有返回值(return)，而过程可以没有。

过程和函数都以编译后的形式存放在数据库中；
函数和过程都可以通过参数列表接收或返回零个或多个值；
函数和过程的主要区别不在于返回值，而在于他们的调用方式。

过程是作为一个独立执行语句调用的：pay_involume(invoice_nbr,30,due_date);
函数以合法的表达式的方式调用：order_volumn:=open_orders(SYSDATE,30);

 创建函数的语法和过程的语法基本相同，唯一的区别在于函数有RETUREN子句。

建立函数时，在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据。

语法：

CREATE [ OR REPLACE] FINCTION [schema.]function_name
[parameter_list]
RETURN returning_datatype
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION]
exception_section
END [procedure_name]

Createfunction 函数名(参数1…)

Return返回的数据类型 is

--定义变量

Begin

--执行语句

Return变量名 //把变量返回

End;

注：观察和过程的不同，在参数后面多一句return。在executable_section执行部分函数必须有一个或多个return语句。

函数中可以调用单行函数和组函数。

快速入门：

案例：请编写一个函数，可以接收用户名并返回该用户的年薪。

Createor replace function fun1(in_v_ename varchar2)

Returnnumber is

--定义一个变量，来接收年薪

V_annual_salnumber;

Begin

Select(sal+nvl(comm,0))*13 into v_annual_sal from emp where ename=in_v_ename;

Returnv_annual_sal;

End;

调用函数
Select函数名(实际参数) from dual;

注：因为函数体里已经定义了查询哪个表，所以from后面写dual虚表就可以了。

若写emp,相当于查询很多次，会返回很多次结果。

Selectfun1(‘SMITH’) from emp; //函数方法的使用就和MAX,MIN一样了。

包Package

包用于在逻辑上组合过程和函数，它由包规范package和包体package body两部分组成。

包规范只包含了过程和函数的声明，但是没有过程和函数的实现代码。包括函数头，过程头，和外部可视数据结构。（像是java中的接口）

包体用于实现包规范中的过程和函数。包含了所有被捆绑的过程和函数的声明、执行、异常处理部分。

当用户获得包的执行授权时，就等于获得包规范中的所有程序和数据结构的权限。但不能只对包中的某一个函数或过程进行授权。

包可以重载过程和函数，在包内可以用同一个名字声明多个过程，在运行时根据参数的数目和数据类型调用正确的过程。

包体不能脱离包规范独立存在，必须先创建包规范再创建包体。

包规范

基本语法：

CREATE [OR REPLACE] PACKAGE package_name
{AS|IS}
public_variable_declarations |
public_type_declarations |
public_exception_declarations |
public_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]

Createpackage 包名 is

--声明过程

Procedure过程名(参数名 参数类型...);

--声明函数

Function函数名(参数名 参数类型..) return 数据类型；

End;

注：可以把包规范想象为定义接口，而包体是实现接口

包的快速入门：

案例：编写一个包，该包有一个过程，该过程可以接收用户名和新的薪水，（将来用于通过用户名去更新薪水）还有一个函数，该函数可以接收一个用户名，（将来要实现得到用户的年薪是多少）。

Createor replace package mypackage1 is

--声明第一个过程

Procedurepro1(v_in_ename varchar2,v_in_newsal number);

--声明一个函数

Functionfun1(v_in_ename varchar2) return number;

End;

包体

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

CREATE [OR REPLACE] PACKAGE BODY package_name
{AS|IS}
private_variable_declarations |
private_type_declarations |
private_exception_declarations |
private_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]

Create or replace package body 包名 is

--实现过程

Procedure 过程名(参数列表…) is

--定义变量

Begin

--执行语句

End;

--实现过程

Create function 函数名(参数列表…) return 数据类型 is

--定义变量

Begin

--执行

End;

End;

注：每个end是各自匹配的。

私有数据结构是那些在包主体内部，对被调用程序而言是不可见的。

快速入门：

把前面包中的声明的函数和过程实现。

Create ot replace package body mypackeageis

--实现过程

Procedure pro1(v_in_enamevarchar2,v_in_newsal number) is

Begin

Update emp set sal=v_in_newsal whereename=v_in_ename;

End;

--实现函数

Function fun1(v_in_ename varchar2) returnnumber is

V_annual_sal number;

Begin

Select (sal+nvl(comm,0))*13 intov_annual_sal from emp where ename=v_in_ename;

Return v_annual_sal;

End;

End;

注：包体中要实现的方法或者过程，应当先在包中声明才可以

调用包的过程或函数：

exec 方案名.包名.过程名或函数名(参数…)

当调用包的过程或者函数时，在过程和函数前需要带包名，如果要访问其它方案的包，需要在包名前加方案名。

例： Exec scott.mypackage.pro1(参数…);或exec scott.mypackage.fun1(参数…);

触发器

触发器是一个隐含执行的过程。它不是由程序员或者dba来显示调用，而是因为某个操作引发执行的。

触发器是一种自动执行响应数据库变化的程序。

创建触发器的语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name {before|after|instead of} event ON {table_or_view_name|DATABASE} [FOR EACH ROW[WHEN condition]] trigger_body

可以设置为在触发器事件之前或之后触发或执行。能够触发触发器事件的事件包括下面几种：
　　DML事件
　　DDL事件
　　数据库事件

只有DML触发器（INSERT、UPDATE、DELETE)语句可以使用INSTEAD OF触发器并且只有表的DML触发器可以是BEFORE或AFTER触发器。

DML事件触发器可以是语句或行级触发器。DML语句触发器在触发语句之前或之后触发DML行级触发器在语句影响的行变化之前或之后触发。用户可以给单一事件和类型定义多个触发器，但没有任何方法可以增强多触发器触发的命令。下表列出了用户可以利用的触发器事件：

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

象约束一样触发器可以被设置为禁用或启用来关闭或打开他们的执行体(EXECUTE),将触发器设置为禁用或启用使用ALTER TRIGGER语句：

ALTER TRIGGER trigger_name ENABLE; ALTER TRIGGER trigger_name DISABLE;

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

ALTER TABLE table_name DISABLE ALL TRIGGERS; ALTER TABLE table_name ENABLE ALL TRIGGERS;

　　删除触发器使用DROP TRIGGER

DROP TRIGGER trigger_name;

————————————————
原文链接：https://blog.csdn.net/JasonZi/article/details/78401718
原文链接：https://www.cnblogs.com/zcy_soft/archive/2011/05/05/2038216.html