LightDB中的存储过程（一）

LightDB支持存储过程，除了支持Postgres的plpgsql存储过程，还兼容Oracle的存储过程，新增了plorasql过程语言。

什么是存储过程呢？

简而言之就是过程语言+SQL，也就是在原来SQL语言的非过程化基础上，增加了过程化的元素。包括变量、添加选择、循环控制、错误处理、集合、子程序、包、触发器等。

存储过程程序以块为单位，每个语句块由声明部分、执行部分、异常处理部分构成。在语句块中可以定义过程化的变量、常量、数据类型、异常、子程序等，可以使用各自控制结构，可以嵌入各种SQL语句，从而实现复杂的业务逻辑处理。

declare  -- 声明部分
	lob text := 'abc';
	siz int;
	v constant int default 100;
begin		-- 执行部分
	siz = length(lob);
	raise notice '%', v;
end;
/
1
2
3
4
5
6
7
8
9

匿名块与命名块

plorasql中，块分为两类，匿名块和命名块。

匿名块是指没有名称，不在数据库中存储，不能被其他程序调用，但可以调用其他子程序的程序。匿名块每次执行时都需要程序的源代码（因为没有在数据库中存储），先编译后执行。

begin
	raise notice 'anonymous block';
end;
1
2
3

命名块是指一次编译多次执行的plorasql程序，包括函数、存储过程、包等，它们编译后放在数据库服务器中，由应用程序或系统在特定条件下调用执行。命名块是一次编译多次执行的PL/SQL程序。

create package pkg_showemp as
	cursor c_emp is select * from t1;
	procedure show_fiveemp;
end;

create package body pkg_showemp as
    procedure show_fiveemp
as
	v_emp c_emp%ROWTYPE;
	begin
		open c_emp;
		for i in 1..3 loop
			fetch c_emp into v_emp;
			dbms_output.put_line(v_emp.a);
		end loop;
	end;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

%TYPE 、 %ROWTYPE属性

可以看到上面的例子引用了%ROWTYPE来声明变量的类型。那具体是什么意思呢？

%TYPE属性，如果要声明一个变量、常量、集合元素、记录字段、子程序参数，其数据类型与某个之前声明的变量的数据类型相同，或者与数据库表中某个列的数据类型一致，则可以利用%TYPE来实现，此时，不需要知道参照变量或列的数据类型。

declare
	vid mystudent.id%TYPE;
begin
	select id into vid from mystudent where rownum < 2;
	raise notice 'id is %', vid;
end;
/

-- 运行结果：
NOTICE:  id is 1
DO
1
2
3
4
5
6
7
8
9
10
11

%ROWTYPE属性，与%TYPE属性类似，可以使用%ROWTYPE属性声明与显示游标、游标变量、数据库表或视图结构相一致的记录类型。使用%ROWTYPE属性返回的记录类型中的字段名称、数据类型、字段顺序与被引用的游标、数据库表等被引用的对象的结构完全一致。

declare
	v mystudent%ROWTYPE;
begin
	select * into v from mystudent where rownum < 2;
	raise notice '(%,%,%)', v.id,v.name,v.teachername;
end;
/

-- 运行结果：
NOTICE:  (1,aa,a)
DO
1
2
3
4
5
6
7
8
9
10
11

-- 上述示例中用的表结构为：
postgres@postgres=# \d mystudent ;
                       Table "public.mystudent"
   Column    |         Type          | Collation | Nullable | Default 
-------------+-----------------------+-----------+----------+---------
 id          | integer               |           |          | 
 name        | character varying(20) |           |          | 
 teachername | character varying(20) |           |          | 
1
2
3
4
5
6
7
8

变量与常量的声明

如果要在PL/SQL程序中使用变量或常量，则必须在PL/SQL块中的声明部分声明该变量或者常量。示例如下：

declare
	v mystudent%ROWTYPE;  --变量声明
	va CONSTANT int := 2; -- 常量声明
begin
	select * into v from mystudent where rownum < va;
	raise notice '(%,%,%)', v.id,v.name,v.teachername;
end;
/
1
2
3
4
5
6
7
8

常量必须加上CONSTANT，并且必须赋初值。

变量作用域与可见性

变量的作用域是指变量的有效作用范围从变量声明开始，到块结束。如果PL/SQL块相互嵌套，则在内部块中声明的变量时局部的，只能在内部块中引用。而在外部块中声明的变量是全局的，既可以在外部块中引用，也可以在内部块中引用。

变量的可见性是指在PL/SQL单元中可以直接引用的变量。如果PL/SQL单元相互嵌套，并且在内部块中声明了与外部块中声明的变量同名，则在内部块中，同名的外部变量与内部变量都有效，但外部变量不可见。

declare
	va CONSTANT int := 2; -- 常量
begin
	raise notice 'outer va: %', va;
	
	declare
		va int := 100;    -- := 为赋值运算符
	begin
		raise notice 'inner va %', va; 
	end;
end;
/
-- 执行结果
NOTICE:  outer va: 2
NOTICE:  inner va 100
DO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

更多请参考LightDB官网