使用存储过程

存储过程

商业规则和业务逻辑可以通过程序存储在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

  

  不涉及变量声明时声明部分可以没有。

  • 对匿名块来说，没有变量声明部分时，可以省去DECLARE关键字。
  • 对存储过程来说，没有DECLARE关键字， AS关键字相当于DECLARE。即使没有变量声明的部分，AS关键字也必须保留。
• 执行部分：过程及SQL语句，程序的主要部分。必选。

  BEGIN

• 执行异常部分：错误处理。可选。

  EXCEPTION

• 结束。必选。

  END;
  /

  

  • 禁止在PL/SQL块中使用连续的Tab，连续的Tab可能会在使用gsql工具并带有“-r”参数执行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