存储过程概述
存储过程数据库系统中,一组为了完成特定功能的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<异常处理语句>{;<异常处理语句>}
注 可执行部分是存储过程的核心部分,由SQL语句和流控制语句构成。支持的SQL语句包括:
数据查询语句(SELECT) 数据操纵语句(INSERT、DELETE、UPDATE) 游标定义及操纵语句(DECLARE CURSOR、OPEN、FETCH、CLOSE) 事务控制语句(COMMIT、ROLLBACK) 动态SQL执行语句(EXECUTE IMMEDIATE)
SQL语句必须以分号结尾,否则语法分析报错。
达梦数据库简单存储过程编写 首先我们先在一个模式名下创建存储过程,最好不要在SYSDBA模式下创建
编写存储过程
使用DM8初始化创建的数据库实例里面的JOB表
然后我们编辑存储过程,设置变量,将sql进行修改,给变量赋值,并将条件设置为输入变量,并最终进行出参打印出来 as后为我们设置的出参变量,变量名+变量数据类型,用分号隔开 查询的指定列名into出参,按查询顺序依次赋值 条件为入参A 最终打印B1B2B3,并用||分割显示
AS B1 VARCHAR;B2 INT;B3 INT ; BEGIN /*执行体*/ SELECT JOB_ID, MIN_SALARY, MAX_SALARY INTO B1,B2,B3 FROM DMHR.JOB WHERE JOB_TITLE=A; print('ID='||B1||' 最低工资:'||B2||' 最高工资:'||B3); END;
点击确定保存结束后,我们在查询语句中,调用存储过程,查看是否正确显示 这里因为我们设置的入参变量为varchar类型,如果设置的为int型,则不需要用引号,但是入参必须和相应的列名数据类型相同,否则会出错
调用存储过程
CALL DMHR.T1('总经理助理')
触发器的定义
触发器(TRIGGER)是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时,会激活促发其执行相应的操作。达梦数据库的触发器包括:库级别、表级别、模式级别、视图级别。下面主要讲解下库级别、表级别的触发器。 触发器的创建 1、库级触发器的创建。对数据库进行drop操作,创建触发器,把操作信息进行记录。 (1)我们先创建一个TB_CHANGE表触发时进行信息记录的表。表不要创建在SYSDBA下,我们创建在DMHR这个模式下。 CREATE TABLE TB_CHANGE(USER_NAME VARCHAR(50),OPERATE_TIME DATETIME);
(2)创建触发器 (3)填写触发器名称,选择需要触发的事件
(4)编写触发体,点击【确定】按钮,完成触发器的创建。
(5)我们进行测试,新建一张测试表,然后查看TB_CHANGE是否有记录信息。
2、案例:创建表级别的触发器,在DMHR模式下当修改EMPLOYEE的电话号码时,触发记录旧的电话号码及新的电话号码。在TB_CHANGE表中增加旧的电话号码列及新的电话号码列
(1)、创建表级别触发器
(2)选择要触发的事件、触发的表、触发的字段及触发的级别
旧行别名、新行别名建议大写,本人用小写出错。
(3)编写触发体
(4)、修改EMPLOYEE表,测试触发器是否被触发
看到表中已经记录了新、旧电话号码,表级别触发器创建成功。
DM数据库游标
DMSQL 程序为用户提供了游标,供用户对查询到的多行数据进行逐条处理。游标可以从多条数据记录的结果集中每次提取一条记录供用户访问处理,通过循环控制,遍历结果集中的所有记录。 达成梦数据库中的游标分为静态游标和动态游标,其中静态游标又可分为显式游标和隐式游标。
一、静态游标
静态游标是只读游标,它总是按照打开游标时的原样显示结果集,在编译时就能确定静态游标使用的查询。 1.隐式游标 每当用户在 DMSQL 程序中执行一个DML语句(INSERT、UPDATE、DELETE)或者 SELECT …INTO 语句时,DMSQL 程序都会自动声明一个隐式游标并管理这个游标。 隐式游标不必专门去声明,数据库自动为我们做好了后台工作,如定义、打开、取值及关闭操作,由达梦数据库自动完成,用户通过隐式游标的相关属性,来完成相应的操作。隐式游标的名称为“SQL”,用户可以通过隐式游标获取语句执行的一些信息。DMSQL 程序中的每个游标都有%FOUND、%NOTFOUND、%ISOPEN 和%ROWCOUNT 四个属性,对于 隐式游标,这四个属性的意义如下:
%FOUND:语句是否修改或查询到了记录,是返回 TRUE,否则返回 FALSE;
%NOTFOUND:语句是否未能成功修改或查询到记录,是返回 TRUE,否则返回FALSE;
%ISOPEN:游标是否打开。是返回 TRUE,否返回 FALSE。由于系统在语句执行 完成后会自动关闭隐式游标,因此隐式游标的%ISOPEN 属性永远为 FALSE;
%ROWCOUNT:DML 语句执行影响的行数,或 SELECT…INTO 语句返回的行数。
例如:
总共1个语句正依次执行... [执行语句1]: DECLARE v_name varchar; v_date date; BEGIN SELECT EMPLOYEE_NAME, HIRE_DATE into v_name, v_date FROM DMHR.EMPLOYEE; IF SQL%FOUND THEN PRINT v_name||','||v_date; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN PRINT 'NO DATA FOUND!'; WHEN TOO_MANY_ROWS THEN PRINT 'TOO MANY ROWS!'; END; 执行成功, 执行耗时0毫秒. 执行号:86 TOO MANY ROWS! 影响了0条记录 1条语句执行成功
通过直接利用%FOUND属性,判断游标中集合各字段值是否存在。同时,可以看到,select …into主要是单行结果集的处理,对于多行结果查询异常处理模块捕获到了异常。
总共1个语句正依次执行... [执行语句1]: DECLARE v_name varchar; v_date date; BEGIN SELECT EMPLOYEE_NAME, HIRE_DATE into v_name, v_date FROM DMHR.EMPLOYEE WHERE EMPLOYEE_ID='100001'; IF SQL%FOUND THEN PRINT v_name||','||v_date; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN PRINT 'NO DATA FOUND!'; WHEN TOO_MANY_ROWS THEN PRINT 'TOO MANY ROWS!'; END; 执行成功, 执行耗时0毫秒. 执行号:88 NO DATA FOUND! 影响了0条记录 1条语句执行成功
这段过程中,查询结果集是为空,SQL%FOUND属性判断后跳过打印,最终通过异常处理模块正常捕获到。
总共1个语句正依次执行... [执行语句1]: DECLARE v_name varchar; v_date date; BEGIN SELECT EMPLOYEE_NAME, HIRE_DATE into v_name, v_date FROM DMHR.EMPLOYEE WHERE EMPLOYEE_ID='1001'; IF SQL%FOUND THEN PRINT v_name||','||v_date; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN PRINT 'NO DATA FOUND!'; WHEN TOO_MANY_ROWS THEN PRINT 'TOO MANY ROWS!'; END; 执行成功, 执行耗时0毫秒. 执行号:89 马学铭,2008-05-30 影响了1条记录 1条语句执行成功
上面这段处理过程,查询只有一条匹配结果,正常打印了记录中的指定字段内容。通过上面的例子,可以看到,隐式游标使用上较为方便,省去了游标定义、打开、关闭等操作,但使用场景上有局限性,程序中限于INSERT、UPDATE、DELETE、SELECT …INTO 等语句。
2.显式游标
显式游标指向一个查询语句执行后的结果集区域。当需要处理返回多条记录的查询时,应显式地定义游标以处理结果集地每一行。 使用显式游标一般包括四个步骤: (1)定义游标:在 DMSQL 程序的声明部分定义游标,声明游标及其关联的查询语句; (2)打开游标:执行游标关联的语句,将查询结果装入游标工作区,将游标定位到结果集的第一行之前; (3)拨动游标:根据应用需要将游标位置移动到结果集的合适位置; (4)关闭游标:游标使用完后应关闭,以释放其占有的资源。DECLARE
v_name VARCHAR(50); v_phone VARCHAR(50); c1 CURSOR; BEGIN OPEN c1 FOR SELECT NAME,PHONE FROM PERSON.PERSON A,RESOURCES.EMPLOYEE B WHERE A.PERSONID=B.PERSONID; LOOP FETCH c1 INTO v_name,v_phone; EXIT WHEN c1%NOTFOUND; PRINT 'name:' || v_name || ' phone number: ' ||v_phone; END LOOP; CLOSE c1; END;
二、动态游标
动态游标在声明部分只是先声明一个游标类型的变量,并不指定其关联的查询语句,在执行部分打开游标时才指定查询语句。动态游标的使用主要在定义和打开时与显式游标不同。
DECLARE my_ename CHAR(10); my_empno NUMERIC(4); my_sal NUMERIC(7,2); c1 CURSOR; BEGIN OPEN C1 FOR SELECT * FROM OTHER.EMPSALARY; LOOP FETCH c1 INTO my_ename, my_empno, my_sal; EXIT WHEN c1%NOTFOUND; PRINT '姓名:'||my_ename || '工号:' || my_empno || ' 薪水:' || my_sal; END LOOP; CLOSE c1; END; /
动态游标关联的查询语句还可以带有参数,参数以“?”指定,同时在打开游标语句中使用 USING 子句指定参数,且参数的个数和类型与语句中的“?”必须一一匹配。
使用关联的语句中带有参数的动态游标。
DECLARE str VARCHAR; CURSOR csr; BEGIN OPEN csr FOR 'SELECT LOGINID FROM RESOURCES.EMPLOYEE WHERE TITLE =? OR TITLE =?' USING '销售经理','总经理'; LOOP FETCH csr INTO str; EXIT WHEN csr%NOTFOUND; PRINT str; END LOOP; CLOSE csr; END; /
系统表,系统视图
表空间是一个逻辑概念, 由多个段组成, 一个表空间可以包含多个数据文件, 默认数据库会自动出创建5个表空间
SYSTEM表空间(系统表空间): 存储有数据字典信息,用户数据不能存放在该表空间上; ROLL表空间(回滚表空间):存放了数据库运行过程中产生的回滚记录,支持MVCC(事务多版本); TEMP表空间(临时表空间):存放临时数据,临时表默认都存放在临时表空间上; MAIN表空间(用户默认表空间):创建数据对象时,如果不指定存储位置,默认存放到该表空间; HMAIN表空间(默认HTS表空间):用于存放的是huge table的信息。 查找数据库中的表空间 SQL> select tablespace_name from dba_tablespaces;
查询表空间及其所属用户和数据文件路径 SQL> select username,user_id,default_tablespace,profile from dba_users;
表空间相关视图
V$TABLESPACE 当前数据库中有多少表空间
V$HUGE_TABLESPACE HTS表空间相关信息
在达梦数据库的使用过程中,会接触到许多存储系统信息的表,通过查询这些表,用户可以了解当前系统的状态,同时,用户也可以直接修改某些字段来实现某些操作。这些表由系统创建和维护,主要可以分为三大类,第一类是数据字典,表名以SYS开头,第二类是动态性能视图,表名以“v$”开头,第三类是为了兼容ORACLE而提供的一批视图,以dba开头
dm_ini用于存储所有ini参数和dminit建库参数信息,其中关键字段有:
PARA_NAME:参数名称
PARA_VALUE:系统参数值
instance用于存储当前数据库实例信息,其中关键字段有:
NAME:实例名称
HOST_NAME:主机名称
STATUS: 系 统 状 态 M O D E :系统状态 MODE:系统状态MODE:模式
V$parameter用于存储ini参数和dminit建库参数的类型和参数值信息,其中关键字段有:
NAME:参数名称
TYPE:参数类型
VALUE:当前会话的参数值
V$rlogfile用于显示日志文件的具体信息,其中关键字段有:
GROUP_ID:表空间ID
FILE_ID:文件ID
PATH:文件路径
CREATE_TIME:创建时间
V$database用于存储当前数据库信息,其中关键字段有:
NAME:数据库名称
CREATE_TIME:数据库创建时间
ARCH_MODE:归档模式,默认是不归档的,在执行备份操作的时候可能需要修改
STATUS:状态,有六种状态,1表示启动,2表示启动,redo完成,3表示MOUNT状态,4表示打开状态,5表示挂起状态,6表示关闭状态
TOTAL_SIZE:总大小
V$dm_arch_ini用于存储当前系统归档参数信息,其中关键字段有:
ARCH_NAME:归档名称
ARCH_TYPE:归档类型
ARCH_FILE_SIZE:归档文件大小ARCH_IS_VALID:归档是否有效
Dba_users用于显示系统中所有的用户,其中的关键字段有:
USERNAME:用户名
PASSWORD:密码
EXPIRY_DATE:密码有效期
CREATED:创建时间
Dba_tables用于描述用户能够看到的所有表,其中关键字段有:
OWNER:表拥有者
TABLE_NAME:表名
TABLESPACE_NAME:表所在的表空间名
Dba_constraints用于存储当前用户拥有的所有约束信息,其中关键字段有:
CONSTRAINT_NAME:约束名
CONSTRAINT_TYPE:约束的类型
TABLE_NAME:约束所在的表名或者视图名
STATUS:约束的状态
Dba_views用于存储数据库中所有的视图,其中的关键字段有:
OWNER:视图拥有者
VIEW_NAME:视图名
TEXT:视图文本(视图执行的具体内容)
Dba_indexes用于存储数据库中的所有索引信息,其中关键字段有:
OWNER:索引拥有者
INDEX_NAME:索引名
INDEX_TYPE:索引类型
Dba_role_privs用于存储系统中的所有角色权限,其中关键字段有:
GRANTEE:被授权用户名
GRANTED_ROLE:被授予的角色
ADMIN_OPTION:是否可以转授,yes表示可以,no表示不可以