DB2数据库常用命令

时间：2022-06-01 16:33:19

--创建数据库

CREATE DATABASE example
      AUTOMATIC STORAGE YES  --自动存储
      ON 'D:\' DBPATH ON'D:\' --指定数据库控制文件的存储路径，和数据库表数据的存储路径
      ALIAS example  --数据库别名
      USING CODESET UTF-8  TERRITORY CN --指定编码集，和地区
      COLLATE USING SYSTEM --指定数据库处理字符串的排序顺序
      PAGESIZE 4096 --默认表空间的大小，单位为字节
      WITH '样本数据库' --注释

     

--编目数据库

CATALOG DATABASE example      AS example --数据库别名      ON D --指定被编目的数据库所在路径      AUTHENTICATION SERVER --身份认证方式      WITH '注释'


     

--除去数据库

UNCATALOG DATABASE example

 

--删除数据库

DROP DATABASE example

 

--创建SMS(System Managed Space)表空间

CREATE TABLESPACE tablespace_name      MANAGED BY SYSTEM      USING path

     

--创建DMS(Database Managed Space)表空间

CREATE TABLESPACE tablespace_name      MANAGED BY DATABASE      USING FILE path size

     

--创建自动存储表空间

CREATE TABLESPACE tablespace_name      MANAGED BY AUTOMATIC STORAGE--可省略，默认值

     

--创建一个常规表空间

CREATE TABLESPACE  tablespace_name      PAGESIZE 4K      MANAGED BY AUTOMATIC STORAGE

     

--加注释

COMMENT ONTABLESPACE tablespace_name IS '注释'

 

--删除表空间

DROP TABLESPACE tablespace_name

     

--数据类型

      

      SMALLINT                     --2字节  --小整数      INT                          --4字节 --整数      DEC(总位数，小数点位数)      --十进制      REAL/FLOAT                   --4字节 --单精度浮点型      DOUBLE                       --8字节 --双精度浮点型      CHAR（字符数）               --固定长度字符型，最长不超过254个字符      VARCHAR(最大字符数)          --可变长度字符型      LONG VARCHAR(最大字符数)     --可变长度长字符型      CLOB                         --字符大对象，最大2G字节      BLOB                         --二进制大对象，常用于存储文档、图片、视音频等非传统数据      GRAPHIC(字节长度)            --定长图形字符串，最大长度127      VARGRAPHIC(字节长度)         --变长图形字符串，最大长度16336字节      LONG VARGRAPHIC(字节长度)    --变长图形长字符串，最大长度16336字节      DATE                         --如：2013-11-29      TIME                         --如：15:40:46      TIMESTAMP                    --如：yyyy-mm-dd-hh.ss.nnnnnn      DECFLOAT                     --v9.5以后引入的新类型，货币数据      XML                          --xml文档

 

--创建表

      CREATE TABLE schema.table_name      (             first_column  CHAR(4) NOT FULL, --非空             second_column SMALLINT WITH DEFAULT0, --默认值为0             third_column  BLOB(1M) LOGGED NOTCOMPACT, --记录，不压缩             forth_column  VARCHAR(120) NOT FULLUNIQUE,--非空、唯一约束             CONSTRAINT primary_key PRIMARYKEY(first_column), --定义主键             CONSTRAINT check_nameCHECK(second_column='0' OR second_column='1' ) --定义检查约束                    ENFORCED --强制此约束                    ENABLE QUERY OPTIMIZATION --查询优化期间考虑此约束             )IN tablespace;


--加注释

      

       COMMENT ON TABLE schema.table_nameIS '注释'             COMMENT ON CONSTRAINT schema.table_name.constraint_name IS '注释'

 

--修改表

ALTERschema.table_name      ADD COLUMN column_name CHAR(6); --增加一列      DROP COLUMN column_name --删除列


--删除表

DROPschema.table_name

 

--插入记录

INSERT INTO schema.table_name(column1,column2,column3)      VALUES(value,value,value)

 

--创建一个与已有表相同的表

CREATE TABLE tableA LIKE tableB

 

--批量插入数据

INSERT INTO student      SELECT * FROM 学生表             WHERE 总学分>50 --将“学生表”中总学分大于50的所有记录都插入到“student”表中

 

--修改记录

UPDATE 学生表      SET 总学分=52             WHERE 姓名='name'

            

--删除记录

DELETE FROM 学生表      WHERE 学号=‘1001’

 

--查询所有列

SELECT *      FROM 学生表

 

--查询指定列

SELECT 学号,姓名      FROM 学生表

 

--使用case语句替换查询结果中的数据

SELECT 学号，姓名，CASE      WHEN 学分 IS NULL THEN '尚未获得学分''      WHEN 学分<53 THEN '未修够学分'      ELSE '修够学分'      END      AS '是否修够学分' --列名             FROM 学生表                    WHERE 专业='计算机'

 

--使用算数运算符计算列值

SELECT 学号，成绩*2 AS 两倍成绩      FROM 学生表

 

--聚集函数

除count函数外，均忽略null值

    

     SUM--求总和     AVG--求平均值     MAX--求最大值     MIN--求最小值     COUNT--求记录总数

 

SELECT COUNT(DISTINCT 学号) --求选修了课程的总人数，DISTINCT去重复记录      FROM 成绩表
      
 
 
 
--指定表的快捷名称
 
 
 
 
SELECT x.学号,k.课程号,c.成绩      FROM 学生表 x,课程表 k,成绩表 c             WHERE x.学号=k.学号 AND k.课程号=c.课程号 AND x.学号=c.学号
 
 
  
 
 
 
--内连接(JOIN/INNER JOIN) 
 
 
 
 
SELECT x.学号，x.姓名，c.课程名，c.成绩       --查询选修了“计算机基础”且成绩在80以上的学生      FROM 学生表 x JOIN 成绩表 ON x.学号=c.学号   JOIN 课程表 c ON k.课程号=c.课程号             WHERE 课程名="计算机基础"AND 成绩>=80
 
 
             
 
 
 
--自连接
 
 
 
 
SELECT a.学号,a.课程号,b.课程号,a.成绩     --查询不同课程成绩相同的学生的学号、课程号、成绩</span>      FROM 成绩表 a JOIN 成绩表 b             ON a.成绩=b.成绩 AND a.学号=b.学号 AND a.课程号!=b.课程号
 
 
             
 
 
 
--外连接
 
 
--内连接不会保留两表中不匹配的行为（即数据列），而外连接会保留
 
 
--全外连接（ALL OUTER JOIN）两表的不匹配列都保留
 
 
--左外连接（LEFT OUTER JOIN）只保留左侧表的不匹配列
 
 
--右外连接（RIGHT OUTER JOIN）只保留右侧表的不匹配列
 
 
 
 
 
 
 
SLECT x.*，课程号   --查询所有学生及他们选修的课程号，学生未选课也要包含在内,其字段为null      FROM 学生表 x LEFT OUTER JOIN成绩表 c             ON x.学号=c.学号
 
 
  
 
 
 
--限制结果集返回行数
 
 
 
 
SELECT  *      FROM 学生表             FETCH FIRST 6 ROW ONLY
 
 
  
 
 
 
--WHERE子句
 
 
DB2支持6种WHERE子句谓语：比较型谓语（>、<、>=、<=、<>、=、NOT）、BETWEEN、 LIKE、 IN 、EXISTS 、IS NULL/ISNOT NULL
 
 
只可以和谓词使用的比较操作符：
 
 
      ALL 只有当子查询返回的所有行的指定关系为真时，这个谓语才为真
 
 
      SOME/ANY 只有当子查询返回的结果中至少有一行指定关系为真时，谓语的结果才为真
 
 
      EXCEPT 产生一个结果表，该表是有第一条SELECT语句产生的唯一的数据行组成，SELECT语句不是有第二条SELECT语句产生的
 
 
      INTERSEC     产生一个结果表，该表由在两条SELECT语句产生的结果中的共同的唯一的数据行组成
 
 
 
 
 
--查询所有出生时间不在1990年的学生
 
 
 
 
SELECT *      FROM 学生表             WHERE 出生时间 NOTBETWEEN '1990-01-01' AND '1990-12-31'
 
 
  
 
 
 
--LIKE谓语的模式匹配
 
 
      _ 代表一个任意单个字符的通配符
 
 
      %    代表任意多个字符的通配符
 
 
      
 
 
--查询所有姓王的学生
 
 
 
 
SELECT *      FROM 学生表             WHERE 姓名 LIKE '王%
 
 
             
 
 
 
--IN谓词的使用
 
 
 
 
SELECT * FROM 学生表      WHERE 姓名 IN('王林','程明'.'王燕')     SELECT * FROM 成绩表      WHERE 姓名 IN             SELECT 学号 FROM 学生表 WHERE 专业='计算机'
 
 
             
 
 
 
--EXISTS谓语的使用
 
 
EXISTS用来确定某行数据是否存在于表中（IN适用于外表大而内表小的查询，EXISTS适用于外表小而内表大的查询）
 
 
 
 
SELECT 姓名 FROM 学生表 c          --查询成绩表中成绩大于80的所有学生姓名      WHERE EXISTS             SELECT * FROM 成绩表 c  WHERE >80 AND x.学号=c.学号
 
 
             
 
 
 
--GROUP BY子句
 
 
其列名必须是select子句未使用聚集函数处理的所有列的子集
 
 
 
 
SELECT 专业，COUNT(*)学生数  --查询各专业的学生人数      FROM 学生表             GROUP BY 专业            SELECT 课程号，AVG（成绩），COUNT（学号） 选修人数   --查询被选修的各门课程的平均成绩和选修该课程的人数      FROM 成绩表             GROUP BY 课程号 ASC/DESC                    WITH ROOLUP --除各数据行以外，还包括汇总行（WITH CUBE  --包含各列所有可能组合的汇总行）
 
 
                    
 
 
 
--GROUPING聚集函数
 
 
产生一个附加列，该列为NULL时，其值为1；反之，则为0
 
 
 
 
SELECT 专业，性别      GROUPING（专业） zy,GROUPING(性别) xb             FROM 学生表            --HAVING子句SELECT 学号，AVG(成绩) 平均成绩     --查询平均成绩在85分以上的学生学号和姓名      FROM   成绩表  --筛选由FROM指定的数据对象             GROUP BY 学号 --对WHERE的筛选结果进行分组                    HAVING AVG(成绩)>=85   --对GROUP BY的结果进行过滤                   SELECT 学号     --查询选修课程超过两门且成绩都在80分以上的学生学号      FROM 成绩表             WHERE 成绩>=80                    GROUP BY 学号                           HAVING COUNT(*)>2
 
 

 
 
 
 
 
--ORDER BY子句
 
 
 
 
SELECT 姓名，学号      FROM 学生表             WHERE 姓名 LIKE '王%'                    ORDER BY 姓名 ASC，学号 DESC
 
 
             
 
 
 
--UNION子句
 
 
用来把两个单独的结果集合并成一个结果集，默认情况下删除重复行，UNION ALL则不删除重复行
 
 
 
 
SELECT       学号，姓名，出生时间      FROM 学生表             WHERE 出生时间 BETWEEN'1990-01-01' AND '1990-12-31'UNIONSELECT       学号，姓名，出生时间      FROM 学生表             WHERE 姓名 LIKE '王%'
 
 
  
 
 
 
--创建视图
 
 
不能使用ORDER BY语句，若视图的关联基本表增加了新的字段，必须重建视图
 
 
 
 
CREATE VIEW schema.view_name      AS      SELECT k.学号 k.课程号 k.成绩             FROM 成绩表 k             WHERE k.课程号='101'                    WITH CHECK OPTION(确保始终检查此条件，即始终确保课程号为101，这将限制课程列的输入，如果在INSERT语句中使用此视图，课程列不是101时将被拒绝)
 
 
 视图的增删修改与基本表的操作基本一致 
 
 
 
 
--约束
 
 
包括非空约束、检车约束、主键约束、唯一约束、外键约束，可以使用ALTER TABLE语句修改表中的约束
 
 
 
 
ALTER TABLE 学生表      DROP PRIMARY KEY  --删除主键约束      ADD CONSTRAINT constraint_name UNIQUE  --添加唯一约束     ALTER TABLE 学生表      ADD CONSTRAINT constraint_name CHECK(开课学期>0 AND 开课学期<8)  --添加检查约束，限制开课学期为0-8      ENFORCED      ENABLE QUERY OPTIMIZATION     ALTER TABLE 成绩表      ADD CONSTRAINT constraint_name FOREIN KEY(学号) REFERENCES schema.学生表（学号）             ON DELETE CASCADE/NOACTION/RESTRICT/SET FULL  --NOACTION/RESTRICT，删除发生错误时，不会删除任何行             ON UPDATE NO ACTION/RESTRICT             ENFORCED             ENABLE QUERY OPTIMIZATION
 
 
             
 
 
 
--删除约束
 
 
 
 
ALTER TABLE 学生表      DROP UNIQUE constraint_name     --别名是引用表、昵称或视图的间接方法，在检查约束的检查条件中不能使用别名，别名不能引用已声明的临时表，别名不能与现有的表、视图重名CREATE ALIAS alias_name      FOR table_name
 
 
      
 
 
 
--数据定义语言（DDL）
 
 
CREATE、ALTER、DROP
 
 
 
 
 
--数据操纵语言（DML）
 
 
SELECT、INSERT、UPDATE、DELETE、MERGE
 
 
 
 
 
--数据控制语言（DCL）
 
 
GRANT、REVOKE、DENY
 
 
 
 
 
--MERGE语句
 
 
 
 
MERGE  INTO table1  t1  USING(SELECT  * FROM  table2  WHERE ... ) t2ON  t1.id = t2.idWHEN  MATCHED THEN  UPDATE  SET(      t1.id,      t1.name,      t1.age) = (      t2.id,      t2.name,      t2.age)WHEN  NOT MATCHED  TEHN  INSERT(      t1.id,      t1.name,      t1.age) values (      t2.id,      t2.name,      t2.age)
 
 

 
 
--用户自定义数据类型
 
 
 
 
CREATE DISTINCTTYPE schema.student_id_type --创建单值数据类型      AS CHAR(6)      WITH COMPARISONS  --指定要创建系统生成的比较运算符，且该运算符用于比较一个单值类型的两个实例，LOB或VARCHAR不能用此关键字      COMMENT ON DISTINCT TYPE schema.student_id_type  IS '学号类型'     CREATE TYPE schema.学生结构类型 (UNDER supertype_name) AS  --创建结构数据类型(      学号 CHAR(6)      姓名 CHAR(16)      性别 CHAR(4))MODE DB2SQL CREATE TYPE type_name --创建数组型数据类型      AS INTEGER ARRAY[100]CREATE TYPE type_name      AS DECIMAL(12，2) ARRAY[]
 
 
      
 
 
 
--专用寄存器
 
 
 
 
CURRENT DATECURRENT TIMECURRENT TIMESTAMPCURRENT USERCURRENT PATHCURRENT SCHEMA
 
 
  
 
 
 
--声明游标
 
 
 
 
DECLARE cursor_name FOR      SELECT 学号 FROM 学生表             WITH HOLD/WITHOUT HOLD(默认)--使用WITH HOLD时，COMMIT操作之后，游标保持OPEN；反之，则关闭。ROLLBACK操作后，所有游标关闭             WITHOUT RETURN/WITH RETURN/WITHRETURN TO CALLER/WITH RETURN TO CLIENT              --WITHOUT RETURN/WITH RETURN --指明游标是否携带结果集             --WITH RETURN TO CALLER --指定将来自游标的结果集返回给调用者（默认项）             --WITH RETURN TO CLIENT --指定将来自游标的结果集返回给客户及程序，绕过任何中间的嵌套过程
 
 
  
 
 
 
--IF...ELSE...条件语句
 
 
 
 
CREATE PROCEDURE schema.pro_name (OUT text CHAR(40))BEGIN      IF((SELECT AVG(成绩) FROM 成绩表 WHERE 课程号='1001')<75)      THEN SET text='平均成绩低于75分';      ELSE SET text='平均成绩大于等于75分';      END IF;END# --CASE语句SELECT 学号，      (CASE             WHEN YEAR(CURRENT DATE)-YEAR(出生时间)<18                    THEN '年龄偏小'             WHEN YEAR(CURRENT DATE)-YEAR(出生时间) BETWEEN18 AND 22                    THEN '年龄适中'             ELSE '年龄偏大'      END) 年龄评价      FROM 学生表
 
 
      
 
 
 
--LOOP循环
 
 
 
 
CREATE PROCEDURE pro_name (OUT out_counter INT)  --从学生表中获取学号、姓名、年龄插入新表STU_INFO中 L1:BEGIN ATOMIC      DECLARE var_at_end,var_counter INTEGER DEFAULT 0;  --var_at_end用于判断游标是否在结果集的末尾，var_counter用于记录行数      DECLARE var_sid CHAR(6);  --学号      DECLARE var_sname CHAR(16);  --姓名      DECLARE var_year_old INT;  --年龄           DECLARE c1 CURSOR FOR             SELECT 学号,姓名,(YEAR(CURRENTDATE)-YEAR(出生时间)) 年龄                    FROM 学生表；             DECLARE CONTINUE HANDLER FOR NOTFOUND SET var_at_end=1;--如果发生找不到的异常则将var_at_end变成1             OPEN c1;           FETCH_LOOP:LOOP             FETCH c1 INTOvar_sid,var_sname,var_year_old;                         IF var_at_end<>0                    THEN LEAVE FETCH_LOOP;  --跳出循环             END IF;                         SET var_counter=var_counter+1;             INSERT INTO stu_info                    VALUES(var_sid,var_sname,var_year_old);      END LOOP FETCH_LOOP;           SET out_counter=var_counter; END L1#
 
 
  
 
 
 
--WHILE循环
 
 
 
 
CREATE PROCEDURE pro_name (OUT out_counter INT)  --从学生表中获取学号、姓名、年龄插入新表STU_INFO中 P1:BEGIN ATOMIC      DECLARE var_at_end,var_counter INTEGER DEFAULT 0;  --var_at_end用于判断游标是否在结果集的末尾，var_counter用于记录行数      DECLARE var_sid CHAR(6);  --学号      DECLARE var_sname CHAR(16);  --姓名      DECLARE var_year_old INT;  --年龄           DECLARE c1 CURSOR FOR             SELECT 学号,姓名,(YEAR(CURRENTDATE)-YEAR(出生时间)) 年龄                    FROM 学生表；             DECLARE CONTINUE HANDLER FOR NOTFOUND SET var_at_end=1;--如果发生找不到的异常则将var_at_end变成1             OPEN c1;                              WHILE(var_at_end=0)             DO                    INSERT INTO stu_info                    VALUES(var_sid,var_sname,var_year_old);                    SETvar_counter=var_counter+1;                    FETCH c1 INTOvar_sid,var_sname,var_year_old;             END WHILE;           SET out_counter=var_counter; END P1#
 
 
  
 
 
 
--FOR循环
 
 
 
 
CREATE PROCEDURE pro_name (OUT out_counter INT)  --从学生表中获取学号、姓名、年龄插入新表STU_INFO中 P1:BEGIN ATOMIC      DECLARE var_counter INTEGER DEFAULT 0;           FOR for_loop AS             SELECT  学号,姓名,(YEAR(CURRENTDATE)-YEAR(出生时间)) 年龄                    FROM 学生表      DO             INSERT INTO stu_info                    VALUES(for_loop.学号,for_loop.姓名,for_loop.年龄);             SET var_counter=var_counter+1;      END FOR;                  SET out_counter=var_counter; END P1#
 
 
  
 
 
 
--常用的系统内置函数
 
 
COALESCE函数 
 
 
语法：COALESCE(ARG1,ARG2...)
 
 
COALESCE返回参数集中第一个非null参数。用法类似于VALUE函数。
 
 
NULLIF ( expression-1, expression-2 )
 
 
如果第一个表达式的值与第二个表达式的值相等，NULLIF 返回 NULL。
 
 
如果第一个表达式的值不等于第二个表达式的值，或者第二个表达式为 NULL，则 NULLIF 返回第一个表达式。
 
 
 
 
 
ABS()  --取绝对值
 
 
RAND() --返回一个0-1间的随机数，类型为real
 
 
ASCII(string)  --返回字符串最左端字符的ASCII码，非纯数字的字符串需用单引号括起来
 
 
CHAR(integer) --将ASCII码转换为字符，integer介于0-225之间
 
 
UPPER()/LOWER()  --大小写转换
 
 
LTRIM()  --去掉前导空格
 
 
RTRIM()  --去掉尾部空格
 
 
LEFT(character_expresion,integer_expresion)   --返回character_expresion左边integer_expresion个字符    RIGHT()函数类似
 
 
CHARINDEX(substring,expresion)  --返回字符串中指定的字串的开始位置；若没有，则返回0
 
 
REVERSE()  --颠倒字符串序列
 
 
REPLACE(string,string1,string2)  --替换指定字符
 
 
SUBSTR(string_expresion,stat_index,end_index)  --按照指定的其实下标截取字符串
 
 
CAST(expresionAS data_type)
 
 
SELECT 学号 CAST(成绩 ASDEC(5,2)
 
 
      FROM 成绩表
 
 
      
 
 
DAY(date_expresion)  --返回日期值
 
 
MONTH(date_expresion)  --返回月份值
 
 
YEAR(date_expresion)  --返回年份值
 
 
DATEADD(yy/mm/dd,number,date_expresion)  --返回date_expresion加上number之后的新日期
 
 
DATEDIFF(yy/mm/dd,number,date_expresion)  --返回date_expresion在yy/mm/dd方面的不同之处，结果为带正负号的整数值
 
 
GETDATE()  --以DATETIME的默认格式返回系统当前的日期和时间
 
 
 
 
 
--创建有源函数
 
 
 
 
CREATE FUNCTION schema.AVG(schema.year_old_type)      RETURNS schema.year_old_type  --自定义单值类型      SOURCE SYSIBM.AVG(INTEGER)  --创建基于内置函数SYSIBM.AVG(INTEGER)的有源函数
 
 
      
 
 
 
--创建一个SQL标量函数
 
 
 
 
CREATE FUNCTION schema.this_month()      RETURN VARCHAR(12)             SPECIFIC schema.this_month  --指定分配给这个函数的特定名称，这个名称可以用来引用或删除函数，但是不能用来调用函数             LANGUAGE SQL             NOT DETERMINISTIC --表示在相同的参数值调用函数时，函数是否总返回相同的结果             NO EXTERNAL ACTION --表示函数执行的操作是否会改变不有DB2管理的对象的状态，外部操作包括发送电子邮件或在外部文件中写记录等             CONTAIN SQL/READS SQL DATA/MODIFIESSQL DATA --可执行的sql语句不读也不修改数据/可执行的sql语句读数据而不修改数据/可执行的sql语句既可读数据，也可修改数据             RETURN MONTHBANE(CURRENT_DATE)  --返回当前月份
 
 
             
 
 
 
--创建一个SQL表函数
 
 
 
 
CREATE FUNCTION schema.funName(in_sid VARCHAR(6))      RETURN TABLE      (             学号 CHAR(6),             姓名 CHAR(8),             课程名 CHAR(40),             成绩 SMALLINT,             学分 SMALLINT,      )      SPECIFIC schema.funName      LANGUAGE SQL      NOT DETERMINISTIC      NO EXTERNAL ACTION      READS SQL DATA           BEGIN ATOMIC  --如果发生任何未处理的复合语句中错误条件，执行到这个点的所有执行语句全部回滚             RETURN                    SELECT x.学号  x.姓名 k.课程名 c.成绩 k.学分                           FROM 学生表 x                                  INNER JOIN 成绩表 c ON (x.学号=c.学号 AND x.学号=in_sid)                                  INNER JOIN 课程表 k ON (c.课程号 AND k.课程号)                           ORDER BY x.学号,k.课程号      END
 
 
      
 
 
 
--删除函数
 
 
 
 
DROP FUNCTION function_name
 
 
  
 
 
 
--查看已存在的存储过程
 
 
 
 
SELECT * FROM SYSCAT.PROCEDURES  --SYSCAT.PROCEDURES所有存储过程的系统目录视图      WHERE PROCNAME=pro_name
 
 
      
 
 
 
--创建存储过程
 
 
 
 
CREATE PROCEDURE schema.pro_name             (IN/OUT parameter_name data_type)             SPECIFIC specific_name             DYNAMIC RESULT SETS number  --指定存储过程返回结果集的最大数量             CONTAIN SQL/READS SQL DATA/MODIFIESSQL DATA             NOT DETERMINISTIC/DETERMINISTIC             CALLED ON NULL INPUT  --表示可以调用存储过程，不管输入参数是否为空             LANGUAGE SQL             NO EXTERNAL ACTION/EXTERNAL ACTION                         P1:BEGIN ATOMIC  --存储过程主体开始的标识符BEGIN，P1为行标                    DECLARE parameter data_typeDEFAULT value;                    DECLARE c2 CURSOR WITHRETURN FOR                           SELECT *                                  FROM 学生表;                    OPEN c2;             END
 
 
             
 
 
 
--调用存储过程
 
 
 
 
CALL pro_name(data_value,data_value,?)  --问号代表OUT参数的占位符
 
 
  
 
 
 
--创建全局变量,在存储过程外面声明
 
 
 
 
CREATE VARIABLE var_name DATA_TYPE DEFAULT value
 
 
  
 
 
 
--诊断语句
 
 
用于获取刚刚被执行的存储过程的信息
 
 
 
 
CALL pro_name();GET DIAGNOSTICS var_name=DB2_RERURN_STATUS
 
 
  
 
 
 
--外部存储过程使用内部存储过程的结果集数据
 
 
 
 
DECLARE rs_locator_var RESULT_SET_LOCATOR VARYING;--声明一个结果集定位器ASSOCIATE RESULTSET LOCATOR (rs_locator_var) WITH PROCEDURE pro_called  --为内部被调用的存储过程分配一个结果集定位器ALLOCATE cursor1 CURSOR FOR RESULT SET rs_locator_var  --分配一个游标，指向调用存储过程的结果集                                    --此后，cursor1就携带了被调用存储过程的结果集，使用方法与自己声明的游标类似
 
 
                                                                                                                                                                                   
 
 
--临时表的使用
 
 
 
 
DECALRE GLOBLALTEMPORARY TABLE SESSION.temporary_table_name --临时表的模式必须为SESSION，其使用方法和普通表类似      (      学号 CHAR(4),      姓名 CHAR(16),      ...      )WITH REPLACE --      如果存在临时表，则替换      NOT LOGGED; --不在日志里记录临时表
 
 
      
 
 
 
--存储过程的重载
 
 
--DB2允许同名单参数数目不同的存储过程，但不允许同名且参数数目相同的存储过程，即使数据类型不同也不行
 
 
 
 
 
--删除存储过程
 
 
 
 
DROP PROCEDURE pro_name  --如果存在同名的存储过程，必须使用DROP SPECIFIC PROCEDURE才能删除
 
 

 
 
--存储过程的异常处理
 
 
SQL PL允许为给定的SQLSTATE声明用。户命名的条件，以用于之后的错误处理。条件名称在整个复合语句中必须是唯一的，并且只能在声明它的复合语句中引用它
 
 
 
 
DECALARE condition_name CONDITION FOR      SQLSTATE [VALUE] string_constant DECLARE foreign_key_violation CONDITION FOR SQLSTATE ‘23503’;
 
 
如果发生一个错误，存储过程的行为是根据条件处理程序来决定的，可以为 
 
 
 
一个普通的或有名称的条件和特定的SQLSTATE声明一个或多个条件处理程序，当一个SQL语句产生一个SQLEXCEPTION或者SQLWARNING（SQLCODE<>0）时，控制被转移到一个声明的处理程序中，以获取普通的异常或特定的SQLSTATE
 
 
 
 
 
 
 
DECLARE<CONDITION | EXIT |UNDO> HANDLER FOR<specific_condition_value| general_condition_value> procedure_statementWHERE specific_condition_value[SQLSTATE[VALUE] string_constant | condition_name]
 
 

 
 
--强制发出异常 SIGNAL SQLSTATE
 
 
 
 
SIGNAL [SQLSTATE[VALUE] <sqlstate_string_constant | variable_name |condition_name>][SETMESSAGE_TEXT = diagnostic_expression]
 
 

 
 
  
 
 
--创建触发器
 
 
 
  
      --前触发器，在更新、插入或删除操作前执行
 
  
      --后触发器，在更新、插入或删除操作后执行
 
  
      --INSTEAD OF触发器，描述如何对视图进行插入、更新和删除操作
 
  


 
 
 
 
CREATE TRIGGER schema_name.trigger_name [NO CASCADE]      [BEFORE | AFTER | INSTEAD OF]      [INSERT | DELETE | UPDATE [OF column_name]]      ON <table_name | view_name>REFERENCING
 
 
      指定转换变量的相关名和转换表的表名。触发器执行前后数据库的状态不同，有 
 两类方法可以标识数据库的不同状态。 
 
 
 
第一类是把即将（或已经）受触发器影响的行集合中的一行定义成“转换变量”，并且给转换变量定义一个相关名。
 
 
OLD AS correlation_name：当前触发器执行前，受影响的行集合中的一行的相关名为correlation_name，每当引用当前触发器执行前的某一行的某一列的值时，就在列名前加上“correlation_name”
 
 
NEW AS correlation_name：当前触发器或者BEFORE触发器中的SET语句执行后，受影响的行集合中的一行的相关名为correlation_name，每当引用当前触发器或者BEFORE触发器执行后的某一行的某一列的值时，就在列名前加上“correlation_name”
 
 
第二类方法是把触发器执行前即将影响（或触发器执行后已经受影响）的行集合看成一个“转换表”，并且给转换表定义一个表名
 
 
OLD TABLE AS identifier：将前触发器执行前即将受影响的行的集合组成名为identifier的转换表
 
 
NEW TABLE AS identifier：当前触发器或者BEFORE触发器中的SET语句执行后，受影响的行的集合组成转换表，该表命名为identifier
 
 
 
 
 
[FOR EACH<ROW | STATEMENT>]
 
 
被触发的SQL语句的动作应用到受影响的表或视图的每一行
 
 
被触发的SQL语句集合中的每一个SQL语句的动作只执行一次
 
 
注意：
 
 
OLD TABLE 和NEW TABLE在每个触发器中只能指定一次，而且只能指定给AFTER或者INSTEAD OF触发器
 
 
DELETE触发器中不允许NEW相关名和NEW_TABLE名
 
 
INSERT触发器中不允许OLD相关名和OLD_TABLE名
 
 
BEFORE触发器中不允许TABLE名和NEW_TABLE名
 
 
FOR EACHSTATEMENT不可以指定给BEFORE和INSTEAD OF 触发器
 
 
WHEN条件语句不得用在INSTEADOF触发器
 
 
只有后触发器和INSTEAD OF 触发器可以包含下列一个或多个SQL语句：UPDATE、DELETE、INSERT、MERGE
 
 
 
 
 
 
 
CREATE TRIGGER schema_name.trigger_name      AFTER INSERT ON table_name  –为表创建插入操作的后触发器      REFERENCING NEW AS N –将触发器执行后受影响的行定义为N      FOR EACH ROWWHEN(60<=(SELECT 成绩 FROM 成绩表 WHERE 学号 = N.学号 AND 课程号 = N.课程号 ))BEGIN ATOMIC      DECLARE credit SMALLINT DEFAULT 0;IF(0<(SELECTc.学分 FROM 课程表 c  WHERE c.课程号 = N.课程号))THEN UPDATE 学生表 s SET 总学分 = 总学分 + creditWHERE  s.学号 = N.学号END IF;END#--每次向成绩表增加一条记录且该记录的成绩大于60分时，就将学生表中相应学生的总学分更新 CREATE TRIGGER schema_name.trigger_name      BEFORE INSERT ON 成绩表  --前触发器      REFERENCING NEW AS N      FOR EACH ROWBEGIN ATOMICIF(N.学号 NOT IN(SELECT s.学号 FROM 学生表 s))      THEN SIGNALSQLSTATE ‘70001’(‘错误：该记录的学号在学生表中不存在！’);END IF;IF(N.课程号 NOTIN(SELECT c.课程号 FROM 课程表 c))      THEN SIGNAL SQLSTATE ‘70002’(‘错误：该记录的课程号在课程表中不存在！’);END IF;END# 
 
 

 
 
--INSTEAD OF 触发器实例
 
 
 
 
CREATE TRIGGER schema_name.trigger_name      INSTEAD OF INSERT ON view_name      REFERENCING NEW AS N      FOR EACH ROWINSERT INTO table_name(column1,column2)      VALUES(value1,value2)
 
 

 
 
--查询触发器
 
 
 
 
SELECT * FROM syscat.triggers
 
 

 
 
--删除触发器
 
 
 
 
DROP TRIGGER schema_name.trigger_name
 
 

 
 
--身份验证
 
 
SERVER        在服务器上进行验证
 
 
SERVER_ENCRYPT            在服务器上验证，密码在客户机上加密，然后再发送到服务器
 
 
CLIENT         在客户机上进行验证
 
 
KERBEROS          有KERBEROS安全软件进行身份验证
 
 
DATA_ENCRYPT         服务器接受加密的用户ID和密码，并对数据进行加密
 
 
--管理权限
 
 
实例权限级别：SYSADM（最高权限）、SYSCTRL、SYSMAINT、SYSMON
 
 
数据库权限级别：DBAMD、SECADM、LOAD、CONNECT、BINDADD、CREATETAB、CREATE_NOT_FENCED、IMPLICITSCHEMA、QUIESCE_CONNECT、CREATE_EXTERNAL_ROUTINE
 
 
--权限授予
 
 
 
 
GRANT database_authorities_type ON DATABASE TO [USER | GROUP]
 
 

 
 
--权限撤销
 
 
 
 
REVOKE database_authorities_type ON DATABASE FROM [USER | GROUP]
 
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 



标签：
相关文章