PL/SQL的基本应用

一、PL/SQL代码基本结构

Header（块头单元）

只有命名代码块（例如存储过程，函数）才有，这个单元确定了这个命名块或者程序的调用方式。

Declaration Section (声明单元，可选）

声明变量、常量、用户自定义的数据类型以及游标等。

Execution Section（执行单元）

主程序体，包含何种合法的PL/SQL语句。

Exception Section（异常处理单元，可选）

异常处理程序，当主程序体中代码出现异常时执行这一部分的代码。

 

二、基本流程控制结构

IF条件判断

IF condition_1 THEN 
  statements_1
ELSIF condition_2 THEN 
  statements_2
......
[ELSIF condition_n THEN 
   statements_n]
[ELSE
   else_statements]
END IF;

 

CASE表达式匹配

CASE expression
  WHEN result_1 THEN
    statements_1
  WHEN result_2 THEN
    statements_2
  ......
  [WHEN result_n THEN
     statements_n]
  [ELSE
     else_statements]
END CASE;

 

 LOOP循环

LOOP
  statements
  [IF exit_condition THEN
     EXIT;]
END LOOP;

 或者

LOOP
  statements
  [EXIT WHEN exit_condition]
END LOOP;

 

WHILE循环 

WHILE loop_condition LOOP
  statements
  [IF exit_condition THEN 
     EXIT;
   END IF;]
  [EXIT WHEN exit_condition]
END LOOP;

 

数值FOR循环

FOR index IN [REVERSE] lowest_num .. highest_num
LOOP
  statements
  [IF exit_condition THEN 
     EXIT;
   END IF;]
  [EXIT WHEN exit_condition]
END LOOP;

 例：

BEGIN
  --正向循环
  --i从2到10循环执行循环体（自动步进固定为1）
  --输出为2345678910
  FOR i IN 2 .. 10 LOOP
    DBMS_OUTPUT.put(i);
  END LOOP;
  DBMS_OUTPUT.new_line();

  --反向循环
  --i从10到2循环执行循环体（自动步进固定为-1）
  --与正向循环一样，起始数和结束数必须小的在前
  --输出为1098765432
  FOR I IN REVERSE 2 .. 10 LOOP
    DBMS_OUTPUT.put(I);
  END LOOP;
  DBMS_OUTPUT.new_line();
END;

 

游标FOR循环

--cursor_name为游标的名字
--explicit_SELECT_statement为合法的SELECT语句
FOR record IN {cursor_name | explicit_SELECT_statement} LOOP
  statements
  [IF exit_condition THEN 
     EXIT;
   END IF;]
  [EXIT WHEN exit_condition]
END LOOP;

 例：

--测试表与测试数据
CREATE TABLE for_test (
  col NVARCHAR2(10)
);
INSERT INTO for_test values('1');
INSERT INTO for_test values('2');
INSERT INTO for_test values('3');


DECLARE
  CURSOR test_cursor IS
    SELECT * FROM for_test;
BEGIN
  --遍历游标
  --输出123
  FOR record IN test_cursor LOOP
      DBMS_OUTPUT.put(record.col);
  END LOOP;
  DBMS_OUTPUT.new_line();
  
  --遍历SELECT语句的结果集
  --输出123
  FOR record IN (SELECT * FROM for_test) LOOP
      DBMS_OUTPUT.put(record.col);
  END LOOP;
  DBMS_OUTPUT.new_line();
END;

 

 三、存储过程

存储过程（Stored Procedure）是一组为了完成特定功能的PL/SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

CREATE [OR REPLACE] PROCEDURE sp_name[(param1 [IN|OUT|IN OUT] TYPE1[, param2 [IN|OUT|INOUT] TYPE2...])] IS
  [declarations]
BEGIN
  statements
[EXCEPTION
   statements]
END [sp_name];

 例：

 创建存储过程：

--计算base的平方并保存在result里
CREATE OR REPLACE PROCEDURE square(base IN NUMBER, result OUT NUMBER) IS
BEGIN
  result := base*base;
END square;

 调用存储过程：

--调用存储过程
--输出9
DECLARE
  result NUMBER;
BEGIN
  square(3, result);
  DBMS_OUTPUT.PUT_LINE(result);
END;

 删除存储过程：

--删除存储过程square
DROP PROCEDURE square;

 

四、函数

函数（Function）与存储过程类似，也是一组为了完成特定功能的PL/SQL语句集，但是函数是通过RETURN语句而不是通过OUT或者IN OUT参数返回数据。

CREATE [OR REPLACE] FUNCTION func_name[(param1 TYPE1[, param2 TYPE2...])] IS
RETURN return_type
  [declarations]
BEGIN
  statements
  RETURN return_value
  [statements]
  [RETURN return_value]
[EXCEPTION
   statements]
END [func_name];

 例：

创建函数：

--计算并返回base的平方
CREATE OR REPLACE FUNCTION square(base NUMBER) RETURN NUMBER IS
BEGIN
  RETURN(base * base);
END square;

 调用函数：

--调用函数
--输出9
DECLARE
  result NUMBER;
BEGIN
  result:=square(3);
  DBMS_OUTPUT.PUT_LINE(result);
END;

 删除函数：

DROP FUNCTION square;

 

 五、游标

游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。

1. 显示游标

基本使用语法

DECLARE
  var_1 type_1;
  [var_2 type_2;
   ...
   var_n type_n;]
  CURSOR cursor_name IS explicit_SELECT_statement
BEGIN
  OPEN TEST_CSR;
  FETCH cursor_name INTO var_1[, var_2, ..., var_n];
  [statements]
  CLOSE TEST_CSR;
END;

 例：

DROP TABLE for_test;
--测试表与测试数据
CREATE TABLE for_test (
  col_1 NUMBER,
  col_2 NVARCHAR2(10)
);
INSERT INTO for_test values(1, 'a');
INSERT INTO for_test values(5, 'e');
INSERT INTO for_test values(2, 'b');
INSERT INTO for_test values(4, 'd');
INSERT INTO for_test values(3, 'c');

--输出1:a,5:e,2:b,4:d,3:c,3:c,1:a,5:e,2:b,4:d,3:c,3:c,
DECLARE
  var_1  NUMBER;
  var_2  NVARCHAR2(10);
  var_row for_test%ROWTYPE;
  CURSOR my_cursor IS SELECT * FROM for_test;
BEGIN
  OPEN my_cursor;
  --处理第一行，把数据存到与每一列相对应的变量里
  FETCH my_cursor INTO var_1, var_2;
  DBMS_OUTPUT.PUT(var_1||':'||var_2||',');
  
  --把数据存到一个代表一行数据的变量里
  WHILE my_cursor%FOUND LOOP
    FETCH my_cursor INTO var_row;
      DBMS_OUTPUT.PUT(var_row.col_1||':'||var_row.col_2||',');
  END LOOP;
  
  DBMS_OUTPUT.NEW_LINE();
  CLOSE my_cursor;
END;

2. 隐式游标

每一次执行一个SQL DML语句（包括INSERT、UPDATE、MERGE或者DELETE），或者SELECT INTO语句（把数据库的一行返回到一个PL/SQL数据结构中），PL/SQL都会自动声明和使用一个隐式的游标。

DECLARE
  variable_definitions;
BEGIN
  SELECT column_list INTO variable_list [rest_SELECT_statement]
  [statements]
END;

 例：

DECLARE
  var_1 NUMBER;
  var_2 NVARCHAR2(10);
BEGIN
  SELECT * INTO var_1, var_2 FROM for_test WHERE col_1=1;
  DBMS_OUTPUT.put_line(var_2);
END;

 3. 游标属性

%FOUND

若当前游标指向有效的一行，返回TRUE，否则返回FALSE。当游标刚打开还没有FETCH数据时，这个属性会返回FALSE，因此应该在打开游标后先FETCH第一条数据，然后再根据这个属性进行循环获取数据。

%NOTFOUND

与%FOUND相反。

%ISOPEN

如果游标已经打开则返回TRUE，否则返回FALSE。

%ROWCOUNT

返回到目前为止，已经从游标中取出的记录数量。

    

 4. 游标变量

与静态（显式与隐式）游标相比，游标变量不同于特定的查询绑定，而是在打开游标时才能确定所对应的查询，因此游标变量可以依次对应多个查询。

--测试表与测试数据
CREATE TABLE for_test (
  col_1 NUMBER,
  col_2 NVARCHAR2(10)
);
INSERT INTO for_test values(1, 'a');
INSERT INTO for_test values(5, 'e');
INSERT INTO for_test values(2, 'b');
INSERT INTO for_test values(4, 'd');
INSERT INTO for_test values(3, 'c');

--打印出给定table的数据
--table_name为表名（输入）
CREATE OR REPLACE PROCEDURE my_sp(table_name VARCHAR2) IS
  TYPE CSR_REF IS REF CURSOR;
  test_cursor CSR_REF;
  var_1        NUMBER;
  var_2        NVARCHAR2(10);
BEGIN
  OPEN test_cursor FOR
    'SELECT * FROM '||table_name;
  FETCH test_cursor
    INTO var_1, var_2;
  DBMS_OUTPUT.PUT(var_1||','||var_2||',');
    
  WHILE test_cursor%FOUND LOOP
    FETCH test_cursor
      INTO var_1, var_2;
    DBMS_OUTPUT.PUT(var_1||','||var_2||',');
  END LOOP;
  CLOSE test_cursor;
  DBMS_OUTPUT.NEW_LINE;
END ;

 调用

BEGIN
  --输出1,a,5,e,2,b,4,d,3,c,3,c,
  my_sp('for_test');
END;