PL/SQl基础概览

本文参考了官方文档：https://www.oracle.com/database/technologies/appdev/plsql.html

PL/SQL的特点：

a，PL/SQL is case-insensitive with regard to identifiers. 通常的做法是关键字大写，变量名等使用小写且用下划线分割。
b，Unless you put double quotation marks around the names of those database objects(tables, indexes etc), Oracle Database will store them as uppercase.

 

1， string variable

Oracle支持的数据类型很多(可参考官方文档)，这里只解释一下string数据类型。To declare a string variable, you must select from one of the many string datatypes Oracle Database offers, including CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes that are prefixed with an ”N” are “national character set” datatypes, which means they are used to store Unicode character data. 其中最常用的就是VARCHAR2。

注意VARCHAR2声明时指定的数字，默认代表字节数。例如VARCHAR2(100)表示最大存储长度是100个字节。对于单字节的字符则等同于最大存储长度是100个字符。但对于NVARCHAR后面的数字则指定的是字符数。因此如果存储的是UTF-16，则NVARCHAR(100)实际的最大存储长度是400个字节。

DECLARE l_company_name VARCHAR2(100); --VARCHAR2是变长的。
DECLARE l_yes_or_no CHAR(1) := 'Y';--CHAR是定长的。
l_variable VARCHAR2 (10) := 'Logic';
l_fixed    CHAR (10) := 'Logic'; --l_variable和l_fixed是不相等的。因为l_variable的实际长度是5，而l_fixed的实际长度是10，后面由空格补齐。

2，=和:=

=为比较操作符，:=为赋值操作符。

3，块结构(block)

Block概念很重要。PL/SQL is a block-structured language. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END.
下面马上要涉及的存储过程(stored procedural)和函数(function)就是命名的块结构。而包(package)则是多个存储过程和函数的容器。

DECLARE
  l_message VARCHAR2 (100) := 'Hello World!';--声明在block中可使用的变量。
BEGIN
  DBMS_OUTPUT.put_line (l_message);
EXCEPTION
  WHEN OTHERS -- WHEN OTHERS 可以捕捉所有的异常。
  THEN
    DBMS_OUTPUT.put_line (SQLERRM); -- SQLERRM函数可以返回错误信息。
END;
-- 一个完整的block。注意只有BEGIN和END之间的可执行部分是必须的，DECLARE和EXCEPTION都不是必须的。

Block结构还可以嵌套，为什么要使用嵌套的block呢？主要是因为异常处理。我们可以将某段容易出错的语句包含在单独的块中，当有语句抛出异常时，该嵌套块抛出异常并终止运行，程序可以跳到外层块继续执行。

4，存储过程(stored procedural)

在需要对数据做某种action的时候，使用存储过程。存储过程可以包含select语句和所有的DML语句。

CREATE OR REPLACE PROCEDURE
hello_place (place_in IN VARCHAR2)
IS
  l_message  VARCHAR2 (100); -- 注意IS后声明存储过程中使用的变量，不需要再使用DECLARE了。
BEGIN
  l_message  := 'Hello ' || place_in;
  DBMS_OUTPUT.put_line (l_message);
END hello_place;
--输入参数分为三部分：参数名称，使用参数的方式，参数的数据类型。上面的IN表示这个参数为输入参数，是只读的。此处还可以为OUT或IN OUT。

5，函数(function)

当需要返回值时，使用函数。

CREATE OR REPLACE FUNCTION
hello_message /*函数名称*/
(place_in IN VARCHAR2) --函数的输入参数
RETURN VARCHAR2 --这里需要指定返回结果的数据类型。这里可以注意到输入参数和输出参数都不指定最大长度。
IS --IS后面跟函数体
BEGIN
   RETURN 'Hello ' || place_in;
END hello_message; --以函数名结尾

6， %TYPE

使用%TYPE可以锚定表中某个字段的类型。例如下例中，我们没有指定l_name变量的数据类型，而是让它的数据类型和employees表中的last_name字段类型相同。

DECLARE
  l_name  employees.last_name%TYPE; -- the ability to anchor the datatype of my variable back to a table’s column
BEGIN
  SELECT last_name
    INTO l_name
    FROM employees
   WHERE employee_id = 138;

  DBMS_OUTPUT.put_line (l_name);
END;

7，SQL%ROWCOUNT

SQL%ROWCOUNT是Oracle的内部游标，其值等于前一条执行的DML语句影响的行数。注意SQL%ROWCOUNT不能保存多条DML影响的行数，且不能用于查询语句。

DECLARE
  l_dept_id employees.department_id%TYPE := 10;
BEGIN
  DELETE FROM employees WHERE department_id = l_dept_id;
  DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;

8，Records

由于PL/SQL的主要作用是操作二维表中的数据，因此PL/SQL提供了记录(records)来拓展这一能力。Record是一种复合数据类型，和普通的如number或string这样的数据类型相比，record可以保存表，视图和游标中的数据行。使用：表名%ROWTYPE就可以声明与表数据有相同结构的record数据类型。

DECLARE
  l_employee  omag_employees%ROWTYPE; --定义l_employee变量，其类型与omag_employees表中的一行数据类型相同。
BEGIN
  SELECT *
    INTO l_employee -- 使用select语句，将返回结果填充进l_employee变量。
    FROM omag_employees
   WHERE employee_id = 100;
END;

下例使用cursor来填充变量：

DECLARE
  CURSOR no_ids_cur
  IS
     SELECT last_name, salary FROM omag_employees;

  l_employee   no_ids_cur%ROWTYPE;
BEGIN
  OPEN no_ids_cur;
  FETCH no_ids_cur INTO l_employee;
  CLOSE no_ids_cur;
END;

除使用表，视图和游标来声明record类型外，用户还可以自定义record类型：

DECLARE
   TYPE customer_info_rt IS RECORD -- 例如当仅需要表中的一部分字段时，就可以使用自定义record。
   (
      name           VARCHAR2 (100),
      total_sales    NUMBER,
      deliver_pref   VARCHAR2 (10)
   );

   l_customer1   customer_info_rt; -- 使用自定义的record类型来声明变量。
   l_customer2   customer_info_rt;