PL/SQL编程

PL/SQL编程

1.1 块结构
PL/SQL程序可以划分成成为块(block)的结构，每一个块都包含PL/SQL和SQL语句。PL/SQL代码块包含如下结构：
[ DECLARE
declaration_statements
]
BEGIN
executable_statements
[exception
exception_handling_statements
]
end;
declaration_statements声明PL/SQL块其余部分使用的变量。DECLARE块是可选的。
executable_statements是块中实际可执行的语句，其中可能包括执行诸如循环、条件逻辑等任务语句。
exception_handling_statements中的语句负责当块运行时可能发生的任何可执行错误。exception是可选的。

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;

1.2 变量和类型
变量在declare块中声明。变量声明中同时包含名称和类型。
v_width integer;
还可以通过%type关键字来定义变量的类型，这个关键字告诉PL/SQL使用表中指定列相同的类型。
v_product_price products.price%type;

1.3 条件逻辑
在PL/SQL中，IF、THEN、ELSE、ELSIF和END IF等关键字用于执行条件逻辑：
IF condition1 THEN
statements1
ELSIF condition2 THEN
statements2
ELSE
statements3
END IF;
其中：
condition1和condition2是布尔表达式，其值为真或假。
statements1、statements2、statements3是PL/SQL语句。
条件逻辑的流程如下：
如果condition1为真，就执行statements1.
如果condition1为假而condition2为真，就执行statement2.
如果condition1和condition2都为假，就执行statement3.
也可以在一条IF语句中嵌入另一条IF语句，如下所示：

IF v_count > 0 THEN
	v_message := 'v_count is positive';
	IF v_area > 0 THEN
		v_message := 'v_count and v_area are positive';
	END IF
ELSIF v_count = 0 THEN
	v_message := 'v_count is zero';
ELSE
	v_message := 'v_count is negative';
END IF;

1.4 循环
循环可以对语句运行零次或多次。在PL/SQL中有3种类型的循环：
简单循环 直到显式结束循环之前一直运行。
WHILE循环 直到某个特定条件出现之前一直运行。
FOR循环 运行预先确定的次数。
1.4.1 简单循环
简单循环在显式结束之前会一直运行。简单循环的语法如下：

LOOP
	statements
END LOOP;

要结束简单循环，可以使用EXIT或EXIT WHEN语句。EXIT语句立即结束循环，EXIT WHEN语句在指定条件出现时结束循环。
变量v_counter在循环开始之前被初始化为0，每次循环给v_counter加1.EXIT WHEN语句表示，当v_counter等于5时循环结束。

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

EXIT WHEN 语句可以出现在循环代码中的任何位置。
1.4.2 WHILE循环
WHILE循环在某个特定条件出现之前一直运行。WHILE循环的语法如下：

WHILE condition LOOP
	statements
END LOOP;

当v_counter变量小于6时WHILE循环会一直执行：

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

1.4.3 FOR循环
FOR循环会运行预先确定的次数，可通过给循环变量指定下限和上限来确定循环运行的次数，然后，循环变量在每次循环中递增(或在反向循环中递减)。FOR循环的语法如下：

FOR loop_variable IN [REVERSE] lower_bound .. upper_bound LOOP
	statements
END LOOP;

loop_variable指定循环变量。可以将已经存在的变量用作循环变量，也可以让循环语句自己创建循环变量。循环变量的值在每一次循环中都增加1。
REVERSE指定在每一次循环中循环变量都会递减。循环变量先被初始化为其上限，然后在每一次循环中递减1，直至达到其下限。下限依然要在上限之前定义。
lower_bound指定循环的下限。在没有使用REVERSE的情况下，循环变量初始化为这个下限值。
upper_bound指定循环的上限。如果使用REVERSE，循环变量初始化为这个下限。
举个栗子

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

下面这个栗子使用了REVERSE

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

1.5 游标
可以使用游标(cursor)获取查询返回的行。在通过查询将行检索到游标中后，可以一次从游标中取出一行。使用游标时一般需要遵循以下5个步骤：
①声明一些变量，用于保存一行的列值。
②声明游标，它包含一个查询。
③打开游标。
④一次从游标中获取一行，并将列值存储在步骤①声明的变量中。然后对这些变量执行某些操作，例如将它们显示在屏幕上、使用它们进行某种计算。
⑤关闭游标

declare 
    --step 1: 声明变量
    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 product_id;
begin
    -- step3: 打开游标
    open v_product_cursor;
    
    LOOP 
    --step4: 从游标中获取行
    fetch v_product_cursor
    into v_product_id, v_name, v_price;
    
    exit when v_product_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE(
        'v_product_id = ' || v_product_id || ', v_name = ' || v_name ||
        ',v_price = ' || v_price
    );
    end loop;
    --step5：关闭游标
    close v_product_cursor;
 end;

1.5.1 游标与FOR循环
利用FOR循环可以访问游标中的行。当使用FOR循环时，可以不显式地打开和关闭游标——FOR循环会自动执行这些操作。

--游标与FOR循环
declare
    cursor v_product_cursor IS 
        select product_id, name, price
          from products
         order by product_id;
begin
    for v_product in v_product_cursor loop
     DBMS_OUTPUT.PUT_LINE(
        'v_product_id = ' || v_product.product_id || ', v_name = ' || v_product.name ||
        ',v_price = ' || v_product.price
    );
    end loop;
end;

1.5.2 OPEN-FOR语句
也可以对游标使用OPEN-FOR语句，由于可以将游标分配给不同的查询，因此可以更加灵活地处理游标。

--open-for语句
declare
    type t_product_cursor is 
    REF cursor return products%rowtype;
    
    v_product_cursor t_product_cursor;
    
    v_product products%rowtype;
    
begin
    open v_product_cursor for
    select * from products where product_id < 5;
    
    loop
    fetch v_product_cursor into v_product;
    exit when v_product_cursor%notfound;
    DBMS_OUTPUT.PUT_LINE(
        'v_product_id = ' || v_product.product_id || ', v_name = ' || v_product.name ||
        ',v_price = ' || v_product.price
    );
    end loop;
end;

1.5.3 无约束游标
约束游标的返回类型必须与游标运行的查询中的列相匹配。无约束游标没有返回类型，因此可以运行任何查询。

declare
    type t_cursor is ref cursor;
    v_cursor t_cursor;
    v_product products%rowtype;
    v_customer customers%rowtype;
begin
    open v_cursor for
    select * from products where product_id < 5;
    
    loop
    fetch v_cursor into v_product;
    exit when v_cursor%notfound;
     DBMS_OUTPUT.PUT_LINE(
        'v_product_id = ' || v_product.product_id || ', v_name = ' || v_product.name ||
        ',v_price = ' || v_product.price
    );
    end loop;
    
    open v_cursor for
    select * from customers where customer_id < 3;
   
    loop
    fetch v_cursor into v_customer;
    exit when v_cursor%notfound;
     DBMS_OUTPUT.PUT_LINE(
        'v_product_id = ' || v_customer.customer_id || ', v_name = ' || v_customer.first_name ||
        ',v_price = ' || v_customer.last_name
    );
    
    end loop;
end;