# 使用存储过程
#### 存储过程
商业规则和业务逻辑可以通过程序存储在GaussDB数据库中，这个程序就是存储过程。
存储过程是SQL和PL/SQL的组合。存储过程可以将执行商业规则的代码从应用程序中移动到数据库，从而实现代码的一次存储，供多个程序使用。
详细使用方法请参见《参考》中"存储过程"章节。
#### PACKAGE
PACKAGE是一组相关存储过程、函数、变量、常量和游标等PL/SQL程序的组合，具有面向对象的特点，可以对PL/SQL程序设计元素进行封装。PACKAGE中的函数具有统一性，创建、删除、修改都统一进行。
PACKAGE包含包头（Package Specification）和PACKAGE BODY两个部分，其中包头所包含的声明可以被外部函数、匿名块等访问；而在包体中包含的声明不能被外部函数、匿名块等访问，只能被包体内函数和存储过程等访问。
#### PL/SQL标识符
PL/SQL子程序、PACKAGE、参数、变量、异常、游标都会有一个名称，即PL/SQL标识符。
标识符的命名需要遵守如下规范：
- 标识符需要为小写字母（a-z）、大写字母(A-Z)、下划线（_）、数字（0-9）或美元符号（$）。
- 标识符必须以字母或下划线开头。
- 标识符的最小长度为1个字符，最大长度为63个字符。
 
#### PL/SQL数据类型
数据类型是一组值的集合以及定义在这个值集上的一组操作。GaussDB数据库是由表的集合组成的，而各表中的列定义了该表，每一列都属于一种数据类型，GaussDB数据库根据数据类型有相应函数对其内容进行操作，例如：GaussDB数据库可对数值型数据进行加、减、乘、除等操作。
PL/SQL中使用SUBTYPE可以创建自己的子类型，其基类型可以是任何基础类型或用户自定义类型。
#### PL/SQL基本结构
PL/SQL块中可以包含子块，子块可以位于PL/SQL中任何部分。PL/SQL块的结构如下：
- 声明部分：声明PL/SQL用到的变量、类型、游标、局部的存储过程和函数。
  ```
  DECLARE
  ```
  ![](https://support.huaweicloud.com/distributed-devg-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png)
  不涉及变量声明时声明部分可以没有。
  - 对匿名块来说，没有变量声明部分时，可以省去DECLARE关键字。
  
  - 对存储过程来说，没有DECLARE关键字， AS关键字相当于DECLARE。即使没有变量声明的部分，AS关键字也必须保留。
    
- 执行部分：过程及SQL语句，程序的主要部分。必选。
  ```
  BEGIN
  ```
  
- 执行异常部分：错误处理。可选。
  ```
  EXCEPTION
  ```
  
- 结束。必选。
  ```
  END;
  /
  ```
  ![](https://support.huaweicloud.com/distributed-devg-v10-gaussdb/public_sys-resources/notice_3.0-zh-cn.png)
  - 禁止在PL/SQL块中使用连续的Tab，连续的Tab可能会在使用gsql工具并带有"-r"参数执行PL/SQL块时导致异常。
  
  - 只有最顶层匿名块需要/结束符。
    
 
PL/SQL块可以分为以下几类：
- 匿名块：只执行一次，在执行时编写，结果不会存储在数据库中。
- 程序：存储在数据库中的存储过程、函数、操作符和高级包等。当在数据库上建立好后，可以在其他程序中调用它们。
#### PL/SQL基本语句
- 变量声明
  变量通常在DECLARE部分声明，每个变量都需要指定类型。可以在声明变量时给变量赋初始值。
  ```
  DECLARE
  id      INTEGER;  --声明变量
  name    VARCHAR(20);  --声明变量
  age     INT := 20;  --声明变量并赋值
  weight  NUMBER(5,2) := 60.5;  --声明变量并赋值
  address VARCHAR(20) := 'xian';  --声明变量并赋值
  BEGIN
  END;
  /
  ANONYMOUS BLOCK EXECUTE
  ```
  
- 赋值语句
  变量赋值通常在BEGIN...END块中，使用=或:=进行变量赋值。
  ```
  DECLARE
  id      INTEGER;  --声明变量
  name    VARCHAR(20);  --声明变量
  BEGIN
  id := 1;  --变量赋值
  name := 'xiaohua';  --变量赋值
  END;
  /
  ANONYMOUS BLOCK EXECUTE
  ```
  
 
#### PL/SQL动态语句
动态语句允许程序执行期间动态的构造并执行SQL语句或PL/SQL块。
```
gaussdb=# CREATE SCHEMA use_procedure;
CREATE SCHEMA
gaussdb=# CREATE TABLE use_procedure.tb1 (a int);
CREATE TABLE
gaussdb=# INSERT INTO use_procedure.tb1 VALUES (generate_series(1, 10));
INSERT 0 10
gaussdb=# DECLARE
counts int;
BEGIN
EXECUTE IMMEDIATE 'select count(*) from use_procedure.tb1' INTO counts;
dbe_output.print_line(counts);
END;
/
10
ANONYMOUS BLOCK EXECUTE
gaussdb=# DROP SCHEMA use_procedure CASCADE;
NOTICE:  drop cascades to table use_procedure.tb1
DROP SCHEMA
```
#### PL/SQL控制语句
条件语句的主要作用判断参数或者语句是否满足已给定的条件，根据判定结果执行相应的操作。
```
gaussdb=# DECLARE
count int := 0;
BEGIN
LOOP
  IF count  > 10 THEN
    EXIT;
  ELSE
    count := count + 1;
  END IF;
END LOOP;
END;
/
ANONYMOUS BLOCK EXECUTE
```
#### PL/SQL事务
调用存储过程时会自动开启一个事务，在调用结束时自动提交或者发生异常时回滚。除了系统自动的事务控制外，也可以使用COMMIT/ROLLBACK来控制存储过程中的事务。在存储过程中调用COMMIT/ROLLBACK命令，将提交/回滚当前事务并自动开启一个新的事务，后续的所有操作都会在此新事务中运行。
```
gaussdb=# CREATE SCHEMA use_procedure;
CREATE SCHEMA
gaussdb=# CREATE TABLE use_procedure.tb1 (a int);
CREATE TABLE
gaussdb=# DECLARE
BEGIN
FOR i IN 1..10 LOOP
  INSERT INTO use_procedure.tb1 VALUES (i);
  IF i % 2 = 0 THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
END LOOP;
END;
/
ANONYMOUS BLOCK EXECUTE
gaussdb=# DROP SCHEMA use_procedure CASCADE;
NOTICE:  drop cascades to table use_procedure.tb1
DROP SCHEMA
```
#### PL/SQL游标
为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系。游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。
```
gaussdb=# CREATE SCHEMA use_procedure;
CREATE SCHEMA
gaussdb=# CREATE TABLE use_procedure.tb1 (a int);
CREATE TABLE
gaussdb=# INSERT INTO use_procedure.tb1 VALUES (generate_series(1, 10));
INSERT 0 10
gaussdb=# DECLARE
CURSOR my_cur is SELECT a FROM use_procedure.tb1;
tb_a int;
BEGIN
  OPEN my_cur;
  LOOP
    FETCH my_cur into tb_a;
EXIT WHEN my_cur%NOTFOUND;
  END LOOP;
  CLOSE my_cur;
END;
/
ANONYMOUS BLOCK EXECUTE
gaussdb=# DROP SCHEMA use_procedure CASCADE;
NOTICE:  drop cascades to table use_procedure.tb1
DROP SCHEMA
```
#### 管理存储过程
- 创建存储过程
  ```
  gaussdb=# CREATE OR REPLACE PROCEDURE proc1() AS
  DECLARE
  id INT;
  name VARCHAR(20);
  BEGIN
  id := 1;
  name := 'xian';
  END;
  /
  CREATE PROCEDURE
  ```
  
- 更改存储过程
  ```
  --修改存储过程名称
  gaussdb=# ALTER PROCEDURE proc1() RENAME TO proc2;
  ALTER FUNCTION
  --用存储过程名重编译存储过程
  gaussdb=# ALTER PROCEDURE proc2() COMPILE;
  ALTER PROCEDURE
  ```
  
- 使用存储过程
  ```
  --直接调用
  gaussdb=# CALL proc2();
   proc2
  -------
  (1 row)
  --使用匿名块调用
  gaussdb=# BEGIN
  proc2();
  END;
  /
  ANONYMOUS BLOCK EXECUTE
  ```
  
- 删除存储过程
  ```
  gaussdb=# DROP PROCEDURE proc2;
  DROP PROCEDURE
  ```