PL/SQL编程简介

使用PL/SQL可以向数据库应用程序中加入业务逻辑。这种集中化的业务逻辑可以由任何能够访问数据库的程序来运行，包括SQL*Plus，Java程序和C#程序。
1、块结构

PL/SQL代码块包含如下的结构：

[DECLARE
	declaration_statements
]
BEGIN
	executable_statements
[EXCEPTION
	exception_handling_statements
]
END;
/

例 1：给出矩形的面积和高度计算其宽度：

SET SERVEROUTPUT ON

DECLARE
	v_width INTEGER;
	v_height INTEGER := 2;
	v_area INTEGER := 6;
BEGIN
	v_width := v_area / v_height;
	DBMS_OUTPUT.PUT_LINE('v_width='|| v_width);
EXCEPTION
	WHEN ZERO_DIVIDE THEN
		DBMS_OUTPUT.PUT_LINE('Division by zero');
END;
/

运行：
SQL> @ C:\SQL\area_example.sql
v_width=3

2、变量和类型
变量在DECLARE块中声明。
v_product_id INTEGER;
v_name VARCHAR2(30);
v_price NUMBER(5,2);
v_product_price products.price@TYPE;这个变量的类型与products表的price列的类型相同

3、条件逻辑
条件逻辑关键字：IF、THEN、ElSE、ELSIF和ENDIF

IF condition1 THEN
	statements1
ELSIF condition2 THEN
	statements2
ELSE
	ststements3
END IF;

4、循环
3种类型的循环
（1）、简单循环
例：

v_counter :=0;
LOOP
	v_counter := v_counter +1;
	EXIT WHEN v_counter = 5;
END LOOP;

（2）、WHILE循环
例：

v_counter :=0;
WHERE v_counter <6 LOOP
	v_counter := v_counter +1;
END LOOP;

（3）、FOR循环
例：
第一种：

FOR v_counter2 IN 1..5 LOOP
	DBMS_OUTPUT.PUT_LINE(v_counter2);
END LOOP;

第二种(循环变量v_counter2从5开始，每次循环减1，最终达到1)：

FOR v_counter2 IN REVERSE 1..5 LOOP
	DBMS_OUTPUT.PUT_LINE(v_counter2);
END LOOP;

5、游标
使用游标（cursor）获取查询返回的记录。一般下面的五步：
step 1、声明一些变量，用于保存记录的列值
step 2、声明游标，并指定查询
step 3、打开游标
step 4、一次从游标中获取一个记录，并将列值存储在第（1）步声明的变量中。然后对这些变量执行某些操作，例如将他们显示在屏幕上、使用它们进行某种计算，等等。
step 5、关闭游标
例 2：从products表中获取product_id、name、和price列 product_cursor.sql

SET SERVEROUTPUT ON
DECLARE
	-- step1:声明一些变量，用于保存记录的列值
	v_product_id	products.product_id%TYPE;
	v_name		products.name%TYPE;
	v_price		products.price%TYPE;

	-- step 2:声明游标，并指定查询
	CURSOR v_product_cursor IS
		SELECT product_id,name,price
		FROM products
		ORDER BY products_id;
BEGIN
	-- step 3:打开游标
	OPEN v_product_cursor;
	LOOP
		-- step 4:一次从游标中获取一个记录
		FETCH v_product_cursor
		INTO v_product_id,v_name,v_price;
	
		EXIT WHEN v_product_cursor%NOTFOUND;
		DBMS_OUTPUT.PUT_LINEC('v_product_id='||v_product_id||',v_name='||v_name||',v_price='||v_price);
	END LOOP;
	-- step 5:关闭游标
	CLOSE v_product_cursor;
END;
/

运行：
SQL> @ C:\SQL\product_cursor.sql

6、异常
如例1，它包含了一个EXEPTION块，本届内容不详细展开。

7、如何创建过程
CREATE PROCEDURE 语句可以用于创建过程。语法如下：
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] type [,...])] {IS|AS}
BEGIN
     procedure_body
END procedure_name;
其中：
OR REPLACE说明过程已经存在，则替换已有的过程。
procedure_name指定过程名
parameter_name制定传给过程的参数名。可以向一个过程传递多个参数。
IN | OUT | IN OUT定义了参数的模式。每一个参数都可以选择下列模式之一。
type指定参数的类型
procedure_body包含过程的代码

例 3、update_product_price()过程将产品的价格乘以一个倍数，产品的ID和倍数作为参数传递给该过程。如果制定的产品存在，则该过程将产品价格乘以一个倍数，并提交多做的改变。

CRETAE PROCEDURE update_product_price(
	p_product_id	IN products.products_id%TYPE,
	p_factor	In NUMBER
) AS
	v_product_count INTEGER;
BEGIN
	SELECT COUNT(*)
	INTO v_product_count
	FROM products
	WHERE product_id = p_product_id;

	IF v_product_count = 1 THEN
		UPDATE products
		SET price = price * p__factor
		WHERE product_id = p_product_id;
		COMMIT;
	END IF;
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
END update_product_price;
/

调用过程的命令：
Call update_product_price(1,1.5);
其中传递的参数为1（product_id）和1.5（乘以产品价格的倍数）

8、如何创建函数
CREATE FUNCTION 语句可以用于创建过程。语法如下：
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] type [,...])] RETURN type {IS|AS}
BEGIN
function_body
END function_name;
其中：
OR REPLACE说明过程已经存在，则替换已有的过程。
function_name指定函数名
parameter_name制定传给函数的参数名。可以向一个函数传递多个参数。
IN | OUT | IN OUT定义了参数的模式。
type指定参数的类型
function_body包含函数的代码
例 4、创建一个名为average_product_price()函数，他返回product_type_id等于参数值的产品的平均价格；

CREATE FUNCTION average_product_price(
	p_product_type_id IN INTEGER
)RETURN NUMBER AS
	v_average_product_price NUMBER;
BEGIN
	SELECT AVG(price)
	INTO v_average_product_price
	FROM products
	WHERE product_type_id = p_product_type_id;
	RETURN v_average_product_price;
END average_product_price;
/

调用函数的命令：
SELECT average_product_price(1) FROM dual;
其中将参数1传递给函数，从而获得product_type_id等于1的所有产品的平均价格。