达梦 SQL程序设计基础

最新推荐文章于 2024-05-10 14:50:38 发布

Beyond_Imagination

最新推荐文章于 2024-05-10 14:50:38 发布

阅读量2.2k

点赞数 2

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/Beyond_Imagination/article/details/108260391

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

达梦SQL程序设计基础

SQL程序是包含变量，控制结构，过程，函数等要素的一组SQL语句和控制语句的组合，相比普通的sql语句，SQL程序有更强大的复杂事务处理能力。程序块可以分为无名块和命名块

无名块
没有命名的程序块，无法调用
命名块
有存储过程，触发器，函数，包等等

SQL程序块结构

语句块是DMSQL的基本程序单元。由块声明，执行部分，异常处理部分组成。语法如下：

DECLARE(可选) --声明部分，（声明头部）
/* 声明部分：此处声明程序用到的类型，游标，变量 */
BEGIN (必须) --执行部分
/* 执行部分，即主要的程序体，此处编写此程序块的功能 */
EXCEPTION (可选) --异常处理部分
/* 异常处理部分，编写可能出现的异常的处理方法*/
END (必须)

简要解释：

变量声明部分由关键字DECLARE开始，不需要声明时可以不写。程序的执行部分由BEGIN和END关键字包含其中，这两个关键字是必须的。程序体中还可能包含可选的异常处理部分EXCEPTION。

注意

除DECLARE，BEGIN，EXCEPTION后面没有分号;外，其他命令行（包括程序体中的SQL语句）都以英文分号;结束。

SQL程序编写规则

规则部分有变量命名规范，大小写，注释等内容。

单行注释：--

多行注释：/* */

变量声明，赋值及操作符

变量用来临时存储数据，使用前必须先声明。需要声明的内容有：变量名，数据类型，初始值等。数据类型可以是DM数据库中的任意类型，也可以是游标，异常等。

常量在初始化后不能改变数据值，DM SQL中声明常量使用CONSTANT关键字，且常量必须在声明的同时初始化。

变量，常量声明

例如定义一个简单的SQL程序，查询姓名为马学铭的人：

DECLARE
	v_hire_date DATE;
	v_namePre CONSTANT VARCHAR(20) := 'SINO'; --定义常量-姓名前缀字段
	v_employee_name VARCHAR(50) := '马学铭';
	v_salary NUMBER(5) NOT NULL := 3000;
BEGIN
	SELECT *FROM DMHR.employee WHERE employee_name = v_employee_name;
END;

赋值

赋值语法：程序变量 := 值

变量类型

SQL程序中的数据类型包括标量，大对象，记录，数组，集合等。

%TYPE类型

在定义变量来处理某个表数据时，可以直接指定与表列相同的数据类型，但表列的数据类型发生变化后变量类型不能动态改变。%TYPE可以动态获取数据表中的字段类型，即使不知道表字段的类型也可使用。例如定义字段v_employee_name动态获取表EMPLOYEE表中EMPLOYEE_NAME的数据类型：

DECLARE
	v_employee_name employee.employee_name%TYPE;

%ROWTYPE

%ROWTYPE是可以动态获取表中一行记录的符合类型，将变量定义为此类型可以存储特定表中的行数据。例如定义变量emp_record存储EMPLOYEE表中姓名为苏国华的记录：

DECLARE
	emp_record employee%ROWTYPE;
BEGIN
	SELECT * INTO emp_record from DMHR.employee WHERE employee_name = '苏国华';
	PRINT emp_record.employee_id;
	PRINT emp_record.employee_name;
END;

记录类型

记录类型是一组自定义的数据类型

语法格式：

TYPE 记录类型名称 IS RECORD(
...
);

示例：定义记录类型存储EMPLOYEE表中的employee_id, employee_name, email, salary字段

DECLARE
	TYPE emp_record_type IS RECORD(
    v_id DMHR.employee.employee_id%TYPE,
    v_name DMHR.employee.employee_name%TYPE,
    v_email DMHR.employee.email%TYPE,
    v_salary DMHR.employee.salary%TYPE);
    
    emp_record emp_record_type;
    
BEGIN
	SELECT e.EMPLOYEE_ID,e.employee_name,e.email,e.salary INTO emp_record
	FROM DMHR.EMPLOYEE e  WHERE employee_name = '苏国华';
	PRINT emp_record.v_id||','||emp_record.v_name||','||emp_record.v_email||','||emp_record.v_salary;
END;

数组类型

数组是存储一组相同类型数据的结构，有动态数组和静态数组，这两种又分为一维数组和多维数组。静态数组在创建时指定大小且之后不能更改，动态数组随数据的增加可以重新指定大小。在DM中，数组下标从1开始。

静态数组

语法格式：

TYPE 数组名 IS ARRAY 数据类型 [ 常量表达式 ]；

例如：指定一个INT类型的数组arr_type，长度为3

DECLARE
	TYPE arr_type IS ARRAY INT [3]; --定义一维数组类型 arr_type
	a arr_type; --声明一维数组a，数据类型为arr_type
	TYPE arr1_type IS ARRAY INT [2,3]; --定义二维数组类型 arr1_type
	b arr1_type; --声明二维数组b，数据类型为arr1_type
BEGIN
	FOR i IN 1..3 LOOP --使用FOR..IN..LOOP循环填充数组a
		a[i] := i*10;
		print a[i];
	END LOOP;
	FOR i IN 1..2 LOOP --使用嵌套循环填充二维数组b
		FOR j IN 1..3 LOOP
			b[i][j] := i*10+j;
			print b[i][j];
		END LOOP;
	END LOOP;
END;

动态数组

与静态数组的定义方式基本一致，只是在定义时不用指定下标，使用时才动态分配空间。

语法格式：

TYPE 数组名 IS ARRAY 数据类型[ ，…]

示例：创建动态数组arr

DECLARE
	TYPE arr IS ARRAY INT[];
	a arr;
BEGIN
	a := NEW INT[3]; 
	FOR i IN 1..3
	LOOP
		a[i] := i*10;
		PRINT a[i];
	END LOOP;
END;

利用SQL函数快速插入10万条数据

创建测试表

CREATE TABLE TEST_TABLE(
ID INT,
RANDOM_STRING VARCHAR(50),
RANDOM_ID VARCHAR(50));

创建SQL函数，插入10万条数据

DECLARE
i INT;

BEGIN
i := 0;
WHILE(i < 100000)
LOOP
	i := i+1;
	INSERT INTO TEST_TABLE(ID,RANDOM_STRING,RANDOM_ID) VALUES (i,dbms_random.string('x', 20),dbms_random.value(0, 100));
END LOOP;
COMMIT;
END;

集合类型

DM支持三种集合类型

VARRAY类型
索引表类型
嵌套表类型

VARRAY

可伸缩，每个元素数据类型相同。定义时指定最大容量，下标从1开始

语法格式：

TYPE 数组名 IS VARRAY 最大容量 OF 数据类型；

示例：定义一个名为my_array_type，最大容量为10，数据类型为INTEGER的集合类型；并定义此类型的变量v

TYPE my_array_type IS VARRAY(10) OF INTEGER；

获取集合大小：v.COUNT()

获取最大容量：v.LIMIT()

存储模块

DMSQL存储模块大致分为存储过程，存储函数，客户端DMSQL程序。

存储过程

存储过程是一组具有特定功能的SQL语句集，编译后存储在数据库中。可由用户自己调用或通过应用程序调用。

为什么要用存储过程

效率高

存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本上），都要先分析编译才会执行。所以想对而言存储过程效率更高。

降低网络流量

存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。

复用性高

存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。

可维护性高

当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。

安全性高

语法格式：

CREATE [OR REPLACE ] PROCEDURE<过程声明><AS_OR_IS><模块体>
<过程声明> ::= <存储过程名定义> [WITH ENCRYPTION][(<参数名><参数模式><参数类型> [<默认值表达式>] {,<参数名><参数模式><参数类型> [<默认值表达式>] })][<调用权限子句>]
<存储过程名定义> ::=[<模式名>.]<存储过程名>
<AS_OR_IS>::= AS | IS
<模块体> ::= [<声明部分>]
BEGIN
<执行部分>
[<异常处理部分>]
END [存储过程名]
<声明部分> ::=[DECLARE]<声明定义>{<声明定义>}
<声明定义>::=<变量声明>|<异常变量声明>|<游标定义>|<子过程定义>|<子函数定义>;
<执行部分>::=<DMSQL 程序语句序列>{;<DMSQL 程序语句序列>}
<DMSQL 程序语句序列> ::= [<标号说明>]<DMSQL 程序语句>;
<标号说明>::=<<<标号名>>>
<DMSQL 程序语句>::=<SQL 语句>|<控制语句>
<异常处理部分>::=EXCEPTION<异常处理语句>{;<异常处理语句>}

示例：在SYSDBA模式下创建存储过程proc_1；

CREATE OR REPLACE PROCEDURE SYSDBA.proc_1(a IN OUT INT) AS 
b INT:=10;
BEGIN
a:=a+b;
PRINT a;
EXCEPTION
WHEN OTHERS THEN NULL;
END;

存储函数

存储函数与存储过程在结构和功能上十分相似，主要的差异在于：

存储过程没有返回值，调用者只能通过访问 OUT 或 IN OUT 参数来获得执行结果，
而存储函数有返回值，它把执行结果直接返回给调用者；
存储过程中可以没有返回语句，而存储函数必须通过返回语句结束；
不能在存储过程的返回语句中带表达式，而存储函数必须带表达式；
存储过程不能出现在一个表达式中，而存储函数可以出现在表达式中。

CREATE OR REPLACE FUNCTION SYSDBA.fun_1(a INT, b INT) RETURN INT 
AS
s INT;
BEGIN
s:=a+b;
RETURN s;
EXCEPTION
WHEN OTHERS THEN NULL;
END;

调用存储函数fun_1

SELECT SYSDBA.fun_1(1,2);

FOR CALCULATE 指定存储函数为计算函数。计算函数中不支持对表进行 INSERT、
DELETE、 UPDATE、 SELECT、上锁、设置自增列属性；对游标 DECLARE、 OPEN、 FETCH、
CLOSE；事务的 COMMIT、 ROLLBACK、 SAVEPOINT、设置事务的隔离级别和读写属性；
动态 SQL 的执行 EXEC、创建 INDEX、创建子过程。对于计算函数体内的函数调用必须是系统函数或者计算函数。计算函数可以被指定为表列的缺省值。

示例：创建计算函数并使用

CREATE OR REPLACE FUNCTION F1 FOR CALCULATE
RETURN INT
IS
BEGIN
RETURN 1;
END;
/
--在表 T 中使用
CREATE TABLE T(C1 INT, C2 INT DEFAULT F1());
或者 CREATE TABLE T(C1 INT, C2 INT DEFAULT F1);

客户端DMSQL程序就是普通的，不能调用的SQL程序。但在其中可以调用其他存储过程和存储函数。

DECLARE
r INT:=0;

BEGIN
SELECT RESOURCES.fun_1(1,2)*2 INTO r;
CALL RESOURCES.proc_1(r);
EXCEPTION
WHEN OTHERS THEN NULL;
END;

存储过程和函数中IS和AS是同义词，没有什么区别。

在创建视图（VIEW）时，只能使用AS而不能使用IS。

在声明游标（CURSOR）时，只能使用IS而不能使用AS。

参数

定义参数时必须指定名称，参数模式，数据类型

参数模式有三种：IN（默认），OUT，IN OUT

其中IN参数可被赋值，OUT参数默认为空，调用时不可赋值。

示例：现要发奖金，给公司中部门的员工加薪bonus元

DECLARE
	emp_num INT := 1001;
	bonus DEC(19,4) := 6000;
	name VARCHAR(50);
	
	PROCEDURE raise_salary (emp_id IN INT, --输入参数
        amount IN OUT DEC(19,4), --输入输出参数
        emp_name OUT VARCHAR(50) --输出参数
	)
    IS
    BEGIN
		UPDATE DMHR.EMPLOYEE SET SALARY = SALARY + amount WHERE
		EMPLOYEE_ID = emp_id;
		SELECT EMPLOYEE_NAME,SALARY INTO emp_name,amount FROM
		DMHR.EMPLOYEE
		WHERE EMPLOYEE_ID = emp_id;
		COMMIT;
	END raise_salary;
BEGIN
    raise_salary(emp_num, bonus, name);
    DBMS_OUTPUT.PUT_LINE
    ('工号： '||emp_num||' '||'姓名： '||name||' '||'加薪后薪水： '||bonus);
END;

定义变量默认值

CREATE PROCEDURE proc_def_arg(a varchar(10) default 'abc', b INT:=123) AS
BEGIN
    PRINT a;
    PRINT b;
END;

调用此存储过程

CALL proc_def_arg;

定义变量时，变量只在定义它的语句块(包括其下层的语句块)内可见，并且定义在下一层语句块中的变量可以屏蔽上一层的同名变量。当遇到一个变量名时，系统首先在当前语句块内查找变量的定义；如果没有找到，再向包含该语句块的上一层语句块中查找，如此直到最外层。

DECLARE
    a INT :=5;
BEGIN
	DECLARE
	a VARCHAR(10); /* 此处定义的变量 a 与上一层中的变量 a 同名 */
	BEGIN
	a:= 'ABCDEFG';
	PRINT a; /* 第一条打印语句 */
	END;
PRINT a; /* 第二条打印语句 */
END;

调用存储模块

调用存储过程时，可直接通过名字+参数调用，也可使用CALL 调用。

调用存储函数时，可通过CALL调用，名字调用，SELECT调用

通过 CALL 和直接使用名字调用存储函数时，不会返回函数的返回值，仅执行其中
的操作；
通过 SELECT 语句调用存储函数时，不仅会执行其中的操作，还会返回函数的返
回值。 SELECT 调用的存储函数不支持含有 OUT、 IN OUT 模式的参数。

CREATE OR REPLACE FUNCTION proc(A INT) RETURN INT AS
DECLARE
    s INT;
    lv INT;
    rv INT;
BEGIN
	IF A = 1 THEN
    	s = 1;
    ELSIF A = 2 THEN
    	s = 1;
    ELSE
        rv = proc(A - 1);
        lv = proc(A - 2);
        s = lv + rv;
        print lv || '+' || rv || '=' || s;
END IF;
	RETURN S;
END;

重新编译

当存储模块中应用的数据库表，索引等对象发生改变或测试存储模块使用还有效时，可重新编译

示例

ALTER PROCEDURE RESOURCES.person_account COMPILE;

删除存储模块

DROP PROCEDURE RESOURCES.proc_1;
DROP FUNCTION RESOURCES.fun_1;

Beyond_Imagination

关注

2
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
达梦 SQL程序设计基础

达梦SQL程序设计基础SQL程序是包含变量，控制结构，过程，函数等要素的一组SQL语句和控制语句的组合，相比普通的sql语句，SQL程序有更强大的复杂事务处理能力。程序块可以分为无名块和命名块无名块没有命名的程序块，无法调用命名块有存储过程，触发器，函数，包等等SQL程序块结构语句块是DMSQL的基本程序单元。由块声明，执行部分，异常处理部分组成。语法如下：DECLARE(可选) --声明部分，（声明头部）/* 声明部分：此处声明程序用到的类型，游标，变量 */BEGIN (必须)
复制链接

扫一扫