recherche

Maison >base de données >tutoriel mysql >主流数据库的常用存储过程大全

主流数据库的常用存储过程大全

WBOY
WBOYoriginal
2016-06-07 14:55:051867parcourir

Access、DB2、MySQL、Oracle PL/SQL、SQL Server T-SQL、Sybase ASE数据库的常用存储过程大全，由于本人的精力有限，还有很多天窗等着大家来填。 存储过程 ?DELIMITER $$CREATE PROCEDURE set_col_valuein_table VARCHAR(128),in_column VARCHAR(128),in_new_

Access、DB2、MySQL、Oracle PL/SQL、SQL Server T-SQL、Sybase ASE数据库的常用存储过程大全，由于本人的精力有限，还有很多天窗等着大家来填。 存储过程 
?DELIMITER $$
CREATE PROCEDURE set_col_value
in_table VARCHAR(128),
in_column VARCHAR(128),
in_new_value VARCHAR(1000),
in_where VARCHAR(4000))
BEGIN
DECLARE l_sql VARCHAR(4000);
SET l_sql=CONCAT_ws(' ',
'UPDATE',in_table,
'SET',in_column,'=',in_new_value,
' WHERE',in_where);
SET @sql=l_sql;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END $$

DELIMITER ;

0.创建
0.0.创建数据库
CREATE DATABASE IF NOT EXISTS %%1;

0.1.创建整型字段
CREATE TABLE %%1
(
%%2 INT NOT NULL
);

0.2.创建整型主键字段
CREATE TABLE %%1
(
%%2 INT NOT NULL PRIMARY KEY
);

0.3.创建字符串字段
CREATE TABLE %%1
(
%%2 VARCHAR(50) NOT NULL
);

0.4.创建字符串主键字段
CREATE TABLE %%1
(
%%2 CHAR(50) NOT NULL PRIMARY KEY
);

0.5.创建日期字段
CREATE TABLE %%1
(
%%2 DATE NOT NULL
);

0.6.创建时间字段
CREATE TABLE %%1
(
%%2 TIME NOT NULL
);

0.7.创建时间戳字段
CREATE TABLE %%1
(
%%2 TIMESTAMP NOT NULL
--%%2 DATETIME NOT NULL
);

0.8.创建短整型字段
CREATE TABLE %%1
(
%%2 SMALLINT NOT NULL
);

0.9.创建长整型字段
CREATE TABLE %%1
(
%%2 BIGINT NOT NULL
);

0.10.创建浮点值字段
CREATE TABLE %%1
(
%%2 REAL NOT NULL
);

0.11.创建双精度浮点值字段
CREATE TABLE %%1
(
%%2 FLOAT NOT NULL
);

0.12.创建数学型整数值字段
CREATE TABLE %%1
(
%%2 NUMBERIC NOT NULL
);

0.13.创建二进制值字段
CREATE TABLE %%1
(
%%2 VARBINARY NOT NULL
);

0.14.创建整型外键字段
CREATE TABLE %%1
(
%%2 INT NOT NULL REFERENCES %%3 (id)
);

0.15.创建字符串外键字段
CREATE TABLE %%1
(
%%2 CHAR(50) NOT NULL REFERENCES %%3 (id)
);

0.16.删除表
TRUNCATE %%1;

1.登录

2.数据库全局搜索
2.1.数据库全局搜索字符串
SHOW DATABASES;
SHOW TABLES FROM %%1;
SHOW FULL COLUMNS;
DESCRIBE %%1;

2.2.数据库全局搜索数字或日期
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COMLUMNS
WHERE TABLE_SCHEMA='INFORMATION_SCHEMA' AND TABLE_NAME=%%1;

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='INFORMATION_SCHEMA';

3.多对多表关系查询

4.一年以前的今天
select date_add(now(),interval -1 year);
select date_sub(now(),interval 1 year);

5.分割字符串函数

6.分割字符串后的元素个数

7.分割字符串后指定索引的元素

8.批量模糊匹配
8.1.构造SQL语句搜索字符串出现的表和字段
8.2.构造SQL连续搜索字符串出现的表和字段

9.显示一个表的结构
DESC %%1;
SHOW CREATE TABLE %%1 \G

10.执行一个存储过程 

11.获取环境变量值

12.冒泡排序
12.1.两元素排序

12.2.三元素排序

12.3.四元素排序

12.4.五元素排序

12.5.六元素排序

13.新建用户，授权，获取表的磁盘空间语句
13.1.表的磁盘空间使用信息

13.2.建用户

13.3.用户授权

13.4.删除数据库

13.5.删除用户

14.选择性处理字段数据

15.查询结果创建表
CREATE TABLE IF NOT EXISTS %%1 AS %%2;

16.创建角色
CREATE ROLE %%1;
\

17.删除角色
DROP ROLE %%1;
\

18.限制结果集范围
SELECT * FROM %%1 ORDER BY id DESC LIMIT %%2,%%3

19.计算字符在字符串中出现的次数
SELECT %%2,LENGTH(%%1)-LENGTH(REPLACE(%%1,%%2,''));

20.计算日期记录间相隔的天数
SELECT t.date1,DATEDIFF(t.date2,t.date1) FROM
(
SELECT t2.%%1 date1,
(
SELECT MIN(t1.%%1) t1 WHERE t1.%%1>t2.%%1
) date2
FROM %%2 t2
) t
ORDER BY t.date1;

21.计算日期所在年的天数
SELECT DATEDIFF(CurrentYear+interval 1 year,CurrentYear) FROM
(
SELECT ADDDATE(%%1,-DAYOFYEAR(%%1)+1) CurrentYear
) t;

22.计算日期所在月的天数
SELECT DATEDIFF(LAST_DAY(%%1),DATE_ADD(%%1,interval(-DAY(%%1)+1) DAY))+1;

23.计算日期所在月的首末日
SELECT DATE_ADD(%%1,interval(-DAY(%%1)+1) DAY),LAST_DAY(%%1);

24.计算日期字段间相隔的天数
SELECT DATEDIFF(%%1,%%2);

25.数学函数
25.1.求绝对值
SELECT ABS(%%1);

25.2.求幂
SELECT POWER(%%1,%%2);

25.3.求平方根
SELECT SQRT(%%1,%%2);

25.4.求随机数
SELECT RAND();

25.5.求正弦值
SELECT SIN(%%1);

25.6.求余弦值
SELECT COS(%%1);

25.7.求反正弦值
SELECT ASIN(1/%%1);

25.8.求反余弦值
SELECT ACOS(1/%%1);

25.9.求正切值
SELECT TAN(%%1);

25.10.求反正切值
SELECT ATAN(%%1);

25.11.求两个变量的反正切值
SELECT ATAN2(%%1,%%2);

25.12.求余切值
SELECT COT(%%1);

25.13.求圆周率值
SELECT PI();

25.14.弧度制转换为角度制
SELECT DEGREES(%%1);

25.15.角度制转换为弧度制
SELECT RADIANS(%%1);

25.16.求符号
SELECT SIGN(%%1);

25.17.求整除余数
SELECT MOD(%%1,%%2);

25.18.求以10为底的对数
SELECT LOG10(%%1);

25.19.求自然对数
SELECT LOG(%%1);

25.20.取小数的整数部分
SELECT CEILING(%%1);

26.取字符串长度
SELECT LENGTH(%%1);

27.转换为小写
SELECT LOWER(%%1);

28.转换为大写
SELECT UPPER(%%1);

29.截去左侧空格
SELECT LTRIM(%%1);

30.截去右侧空格
SELECT RTRIM(%%1);

31.截去两侧空格
SELECT TRIM(%%1);

32.字符串替换
SELECT REPLACE(%%1,%%2,%%3);

33.字符转ACII码
SELECT ASCII(%%1);

34.ACII码转字符
SELECT CHAR(%%1);

35.取当前时间
35.1.取时间戳
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;

35.2.取日期
SELECT CURDATE(),CURRENT_DATE;

35.3.取时间
SELECT CURTIME(),CURRENT_TIME;

36.星期数计算
SELECT DAYNAME(%%1);

37.空值不显示
SELECT IFNULL(%%1,'');
--SELECT NULLIF(%%1,'');

38.提取路径名
SELECT LEFT(%%1,INSTR(REVERSE(%%1),'\')+1);

39.提取文件名
SELECT RIGHT(%%1,LENGTH(%%1)-INSTR(REVERSE(%%1),'\')-1);

40.提取扩展名
SELECT RIGHT(%%1,LENGTH(%%1)-INSTR(REVERSE(%%1),'.')-1);

41.带事务的游标插入记录
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
   DECLARE name varchar(128);
   -- 定义游标
   DECLARE ordernumbers CURSOR
   FOR
   SELECT callee_name FROM account_tbl where acct_timeduration=10800;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
   SET no_more_departments=0;
   -- 打开游标
   OPEN ordernumbers;
   -- 循环所有的行
   REPEAT
       -- Get order number
      FETCH ordernumbers INTO name;
      update account set allMoney=allMoney+72,lastMonthConsume=lastMonthConsume-72 where NumTg=@name;
  -- 循环结束
      UNTIL no_more_departments
     END REPEAT;
   -- 关闭游标
   CLOSE ordernumbers;
COMMIT;

42.带事务的游标修改记录
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
    DECLARE name varchar(128);
   -- 定义游标
   DECLARE ordernumbers CURSOR
   FOR
   SELECT callee_name FROM account_tbl where acct_timeduration=10800;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
   SET no_more_departments=0;
   -- 打开游标
   OPEN ordernumbers;
   -- 循环所有的行
   REPEAT
       -- Get order number
      FETCH ordernumbers INTO name;
      update account set allMoney=allMoney+72,lastMonthConsume=lastMonthConsume-72 where NumTg=@name;
  -- 循环结束
      UNTIL no_more_departments
     END REPEAT;
   -- 关闭游标
   CLOSE ordernumbers;
COMMIT;

43.带事务的游标删除记录
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
   DECLARE name varchar(128);
   -- 定义游标
   DECLARE ordernumbers CURSOR
   FOR
   SELECT callee_name FROM account_tbl where acct_timeduration=10800;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
   SET no_more_departments=0;
   -- 打开游标
   OPEN ordernumbers;
   -- 循环所有的行
   REPEAT
       -- Get order number
      FETCH ordernumbers INTO name;
      update account set allMoney=allMoney+72,lastMonthConsume=lastMonthConsume-72 where NumTg=@name;
  -- 循环结束
      UNTIL no_more_departments
     END REPEAT;
   -- 关闭游标
   CLOSE ordernumbers;
COMMIT;

44.跨数据库复制全部表
CREATE DATABASE IF NOT EXISTS %%1;

DECLARE CountTableRecords CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION SCHEMA.COLUMNS;
OPEN CountTableRecords;
FETCH CountTableRecords INTO COLUMN_NAME,;
CLOSE CountTableRecords;

SET @sql := 'SELECT actor_id, first_name, last_name FROM sakila.actor WHERE first_name = ?'
PREPARE stmt_fetch_actor FROM @sql;
SET @actor_name := 'Penelope'; 
EXECUTE stmt_fetch_actor USING @actor_name;  
DEALLOCATE PREPARE stmt_fetch_actor;

DROP PROCEDURE IF EXISTS optimize_tables;
DELIMITER $$
CREATE PROCEDURE optimize_tables(db_name VARCHAR(64))
BEGIN 
DECLARE t VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR 
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = db_name AND TABLE_TYPE ='BASE TABLE';
DECLARE CONTINUE HANDLERFOR SQLSTATE'02000' SET done = 1;
OPEN c;
tables_loop: LOOP
FETCH cINTO t;
IF doneTHEN 
CLOSE c;
LEAVE tables_loop;
END IF;
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name,".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE c;
END $$

DELIMITER ;

CALL optimize_tables('%%1'); 

REPEAT
FETCH c INTO t;
IF NOT done THEN 
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name,".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;

45.跨实例复制全部表
CREATE DATABASE IF NOT EXISTS %%1

46.记录日志文件
TEE %%1;
%%2
NOTEE;

47.遍历所有表统计行数
DECLARE table_name VARCHAR(80),stmt_text VARCHAR(1024); 
DECLARE done INT DEFAULT 0; 
DECLARE listTables CURSOR FOR  
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA='INFORMATION_SCHEMA';
DECLARE CONTINUE HANDLERFOR SQLSTATE '02000' SET done = 1; 
OPEN listTables; 
tables_loop: LOOP 
FETCH listTables INTO table_name; 
IF done THEN  
CLOSE listTables; 
LEAVE tables_loop; 

48.重命名表
rename table %%1 to %%2;
--ALTER Table %%1 rename %%2;

49.重命名数据库

50.MD5算法

51.遍历字段求算MD5值
52.保存本地文件到二进制字段
53.保存二进制字段数据到本地磁盘

54.显示版本
select version();

55.获取当前用户名
select user();

56.判断质数

57.计算100以内的质数
How it works:
1. a MySQL variable (@num) is used to track the number being
tested for primality
2. a table is kept of the odd prime numbers and the respective
modulos of @num
3. each iteration:
  a. @num is increased by 2 (only checking odd numbers)
  b. each of the modulos are UPDATEd by increasing by 2
  c. if any of the modulos are >= their respective primes,
     they're decreased by that amount
  d. if any of the resulting modulos = 0,
       then the number is composite,
       else the number is prime and is added to the modulos
       table

Efficiency is O(n**2), but it should perform better in the long
run than a sieve both in terms of speed and storage.

in MySQL:
/* First-Time Setup: */
DROP TABLE IF EXISTS modulos;
CREATE TABLE modulos (
  prime INT UNSIGNED NOT NULL,
  modulo INT UNSIGNED NOT NULL
);
INSERT INTO modulos VALUES (3,0);
SELECT @num := 3;

/* repeat these four lines until max(prime) > maxint */
SELECT @num := @num+2;
UPDATE modulos SET modulo = modulo+2;
UPDATE modulos SET modulo = modulo-prime WHERE modulo >= prime;
INSERT INTO modulos (prime,modulo) SELECT @num,0 FROM modulos WHERE 0 NOT IN (SELECT
modulo FROM
modulos) LIMIT 1;


same thing in PHP:
<?PHP

set_time_limit(0);

mysql_connect('localhost','user','pass');
mysql_select_db('pfind');

mysql_query('DROP TABLE IF EXISTS modulos');

mysql_query('CREATE TABLE modulos (
  prime INT UNSIGNED NOT NULL,
  modulo INT UNSIGNED NOT NULL
)');
mysql_query('INSERT INTO modulos VALUES (3,0)');

mysql_query('SELECT @num := 3');

for ($i=0;$i<100;$i++)
{
  mysql_query('SELECT @num := @num+2');
  mysql_query('UPDATE modulos SET modulo = modulo+2');
  mysql_query('UPDATE modulos SET modulo = modulo-prime WHERE modulo >= prime');
  mysql_query('INSERT INTO modulos (prime,modulo) SELECT @num,0 FROM modulos WHERE 0 NOT
IN
(SELECT modulo FROM modulos) LIMIT 1;');
  if (mysql_affected_rows())
  {
    print (2*$i+5)."\n";
  }
}

$row = mysql_fetch_row(mysql_query('select max(prime) from modulos'));
print $row[0]."\n";
?>

58.最大公约数

59.最小公倍数

60.阶乘
DELIMITER $$

CREATE PROCEDURE `Squair`(input int, n int)
BEGIN
    declare totail bigint default input;
    declare _index int default 1;
    emp_loop: LOOP
        if _index >= n then
            LEAVE emp_loop;
        end if;
        set totail = totail * input;
    set _index = _index + 1;
    end loop emp_loop;
    select totail;
END $$

DELIMITER ;

CALL Squair(%%1,%%2); --10,5

61.构造阶乘数列

62.平均偏差

63.中位数

64.均方差
select STDDEV(%%1);

65.完全平方数

66.整数逆序数

67.进制转换

68.金额转换为大写

69.控制小数输出位数

70.将空值转换为0

71.保留十位有效数字

72.创建限制视图
73.查询从未使用的索引

74.反转字符串

75.亲密数

76.自守数

77.水仙花数

78.回文素数

79.平方回文数

80.分解质因数
    public void fengjie(int n){
        for(int i=2;i<=n/2;i++){
            if(n%i==0){
                System.out.print(i+"*");
                fengjie(n/i);
                }
        }
        System.out.print(n);
        System.exit(0);///不能少这句，否则结果会出错
    }
             String str="";
             exp2 c=new exp2();
             str=javax.swing.JOptionPane.showInputDialog("请输入N的值（输入exit退出）：");
             int N;
             N=0;
             try{
                     N=Integer.parseInt(str);
                     }catch(NumberFormatException e){
                         e.printStackTrace();
                         }
            System.out.print(N+"分解质因数："+N+"=");
            c.fengjie(N);

81.查找重复记录
SELECT * FROM %%1 a where exists(SELECT 1 FROM %%1 b where b.%%2=a.%%2 and a.rowid>b.rowid )

82.查看最大连接数
mysqladmin -uusername -ppassword variables

83.修改最大连接数
mysql -uusername -ppassword
set GLOBAL max_connections=200
show processlist
show status
exit

84.计算两个日期之间的月数差额

85.给日期加上指定的月数

86.获取后续的日期

87.获取指定月份的最后一天

88.设置单词首字母大写

89.执行SQL文件
SOURCE %%1.sql

90.显示所有触发器
SHOW TRIGGERS;
?exec(sql)
exec sp_remotesql sql

declare   @sqlstatment   varchar(255) 
select   @sqlstatment= "select   *   from   mytable " 
exec   execsql   @sql=@sqlstatment 
go 

drop   procedure   test 
go 
create   procedure   test   
@sqlstring   varchar(255) 
as 
begin 
        print   @sqlstring 
        exec(@sqlstring) 
end 
go 

declare   @sqlstring   varchar(255) 
select   @sqlstring   = "select   *   from   sysobjects   where   type   = 'U ' " 
exec   test   @sqlstring 



0.创建
0.0.创建数据库
CREATE DATABASE %%1

0.1.创建整型字段
CREATE TABLE %%1
(
%%2 INT NOT NULL
)

0.2.创建整型主键字段
CREATE TABLE %%1
(
%%2 INT NOT NULL PRIMARY KEY
)

0.3.创建字符串字段
CREATE TABLE %%1
(
%%2 VARCHAR(50) NOT NULL
)

0.4.创建字符串主键字段
CREATE TABLE %%1
(
%%2 CHAR(50) NOT NULL PRIMARY KEY
)

0.5.创建日期字段
CREATE TABLE %%1
(
%%2 DATETIME NOT NULL
)

0.6.创建时间字段
CREATE TABLE %%1
(
%%2 DATETIME NOT NULL
)

0.7.创建时间戳字段
CREATE TABLE %%1
(
%%2 DATETIME NOT NULL
)

0.8.创建短整型字段
CREATE TABLE %%1
(
%%2 SMALLINT NOT NULL
)

0.9.创建长整型字段
CREATE TABLE %%1
(
%%2 BIGINT NOT NULL
)

0.10.创建浮点值字段
CREATE TABLE %%1
(
%%2 REAL NOT NULL
)

0.11.创建双精度浮点值字段
CREATE TABLE %%1
(
%%2 FLOAT NOT NULL
)

0.12.创建数学型整数值字段
CREATE TABLE %%1
(
%%2 NUMBERIC NOT NULL
)

0.13.创建二进制值字段
CREATE TABLE %%1
(
%%2 VARBINARY NOT NULL
)

0.14.创建整型外键字段
CREATE TABLE %%1
(
%%2 INT NOT NULL REFERENCES %%3 (id)
)

0.15.创建字符串外键字段
CREATE TABLE %%1
(
%%2 CHAR(50) NOT NULL REFERENCES %%3 (id)
)

0.16.删除表
DROP TABLE %%1

1.登录

2.数据库全局搜索
name sysname 对象名
id int 对象 ID
uid int 对象所有者的用户 ID
type char(2) 可以为以下对象类型之一：
D － 缺省值
F － SQLJ 函数
L － 日志
P － Transact-SQL 或 SQLJ 过程
PR － 准备对象（由 Dynamic SQL 创建）
R － 规则
RI － 参照约束
S － 系统表
TR － 触发器
U － 用户表
V － 视图
XP － 扩展存储过程
userstat smallint 与应用程序相关的类型信息（十进制 32768 [ 十六进制 0x8000] 向 Data
Workbench& 表明过程就是报告）
sysstat smallint 内部状态信息（十进制 256 [ 十六进制 0x100] 表示表是只读的）
indexdel smallint 索引删除计数（当删除索引时增加）
schemacnt smallint 对象方案中的更改计数（当添加规则或缺省值时增加）
sysstat2 int 其它内部状态信息
crdate datetime 创建对象时的日期
expdate datetime 保留
deltrig int 当条目为表时，为删除触发器的存储过程 ID。当条目是触发器时，为表 ID。
instrig int 当条目为表时，是表的插入触发器的存储过程 ID
updtrig int 当条目为表时，是表的更新触发器的存储过程 ID
seltrig int 保留
ckfirst int 表上第一个检查约束的 ID
cache smallint 保留
audflags int 对象的审计设置
objspare int 备用
versionts binary
loginame varchar(30) 创建对象的用户的登录名
2.1.数据库全局搜索字符串

2.2.数据库全局搜索数字或日期

3.多对多表关系查询

4.一年以前的今天

5.分割字符串函数

6.分割字符串后的元素个数

7.分割字符串后指定索引的元素

8.批量模糊匹配
8.1.构造SQL语句搜索字符串出现的表和字段
8.2.构造SQL连续搜索字符串出现的表和字段

9.显示一个表的结构

10.执行一个存储过程 

11.获取环境变量值

12.冒泡排序
12.1.两元素排序

12.2.三元素排序

12.3.四元素排序

12.4.五元素排序

12.5.六元素排序

13.新建用户，授权，获取表的磁盘空间语句
13.1.表的磁盘空间使用信息

13.2.建用户

13.3.用户授权

13.4.删除数据库

13.5.删除用户

14.选择性处理字段数据

15.查询结果创建表
Select * into %%1 from %%2

16.创建角色
CREATE ROLE %%1;
\

17.删除角色
DROP ROLE %%1;
\

18.限制结果集范围
SELECT TOP %%2 * FROM %%1 WHERE id NOT IN
(
SELECT TOP %%3 id FROM %%1 ORDER BY id DESC
)
ORDER BY id DESC

19.计算字符在字符串中出现的次数
SELECT %%2,LENGTH(%%2)-LENGTH(REPLACE(%%2,%%3,'')) FROM %%1

20.计算日期记录间相隔的天数
SELECT t.date1,DATEDIFF(t.date2,t.date1) FROM
(
SELECT t2.%%1 date1,
(
SELECT MIN(t1.%%1) FROM %%2 t1 WHERE t1.%%1>t2.%%1
) date2
FROM %%2 t2
) t
ORDER BY t.date1

21.计算日期所在年的天数
SELECT DATEDIFF(DAY,CurrentYear,DATEADD(YEAR,1,CurrentYear)) FROM
(
SELECT DATEADD(DAY,-DATEPART(DAY,%%1)+1,%%1) CurrentYear FROM %%2
) t

22.计算日期所在月的天数
SELECT DATEDIFF(DAY,DATEADD(DAY,-DAY(%%1),DATEADD(MONTH,1,%%1)),DATEADD(DAY,-DAY(%%1)+1,%%1))+1 FROM %%2

23.计算日期所在月的首末日
SELECT DATEADD(DAY,-DAY(%%1)+1,%%1),DATEADD(DAY,-DAY(%%1),DATEADD(MONTH,1,%%1)) FROM %%2

24.计算日期字段间相隔的天数
SELECT DATEDIFF(%%1,%%2) FROM %%3

25.数学函数
25.1.求绝对值
SELECT ABS(%%1) FROM %%2

25.2.求幂
SELECT POWER(%%1,%%2) FROM %%3

25.3.求平方根
SELECT SQRT(%%1,%%2) FROM %%3

25.4.求随机数

25.5.求正弦值
SELECT SIN(%%1) FROM %%2

25.6.求余弦值
SELECT COS(%%1) FROM %%2

25.7.求反正弦值
SELECT ASIN(1/%%1) FROM %%2

25.8.求反余弦值
SELECT ACOS(1/%%1) FROM %%2

25.9.求正切值
SELECT TAN(%%1) FROM %%2

25.10.求反正切值
SELECT ATAN(%%1) FROM %%2

25.11.求两个变量的反正切值
SELECT ATAN2(%%1,%%2) FROM %%2

25.12.求余切值
SELECT COT(%%1) FROM %%2

25.13.求圆周率值
SELECT PI()

25.14.弧度制转换为角度制
SELECT DEGREES(%%1) FROM %%2

25.15.角度制转换为弧度制
SELECT RADIANS(%%1) FROM %%2

25.16.求符号
SELECT SIGN(%%1) FROM %%2

25.17.求整除余数
SELECT %%1 % %%2 FROM %%3

25.18.求以10为底的对数
SELECT LOG10(%%1) FROM %%2

25.19.求自然对数
SELECT LOG(%%1) FROM %%2

25.20.取小数的整数部分
SELECT CEILING(%%1) FROM %%2

26.取字符串长度
SELECT LEN(%%1) FROM %%2

27.转换为小写
SELECT LOWER(%%1) FROM %%2

28.转换为大写
SELECT UPPER(%%1) FROM %%2

29.截去左侧空格
SELECT LTRIM(%%1) FROM %%2

30.截去右侧空格
SELECT RTRIM(%%1) FROM %%2

31.截去两侧空格
SELECT LTRIM(RTRIM(%%1)) FROM %%2

32.字符串替换
SELECT REPLACE(%%1,%%2,%%3) FROM %%4

33.字符转ACII码
SELECT ASCII(%%1) FROM %%2

34.ACII码转字符
SELECT CHAR(%%1) FROM %%2

35.取当前时间
35.1.取时间戳
SELECT GETDATE()

35.2.取日期
SELECT CONVERT(VARCHAR(50),GETDATE(),101)

35.3.取时间
SELECT CONVERT(VARCHAR(50),GETDATE(),108)

36.星期数计算
SELECT DATENAME(%%1) FROM %%2

37.空值不显示
SELECT ISNULL(%%1,'') FROM %%2
--SELECT NULLIF(%%1,'') FROM %%2

38.提取路径名
SELECT LEFT(%%1,INSTR(REVERSE(%%1),'\')+1)

39.提取文件名
SELECT RIGHT(%%1,LENGTH(%%1)-INSTR(REVERSE(%%1),'\')-1)

40.提取扩展名
SELECT RIGHT(%%1,LENGTH(%%1)-INSTR(REVERSE(%%1),'.')-1)

sybase游标
declare station_cur cursor for  
select  STATION_MODI_ID  from faultdb..FB_JC_J_MODI_DIG_STATION where CIRCUIT_MODI_ID = 93 
  open  station_cur  
    while @@sqlstatus !=2 
    begin 
  fetch  station_cur  into @digMStationId
        insert into faultdb..FB_JC_J_DIGITAL_STATION 
( 
    TELECOM_STATION_ID , 
      )select 
      TELECOM_STATION_ID , 
        from faultdb..FB_JC_J_MODI_DIG_STATION where STATION_MODI_ID = @digMStationId
    select @digStationId=max(STATION_ID)  FROM faultdb..FB_JC_J_DIGITAL_STATION
    set @digStationIds=convert(varchar,@digStationId) + ','      /*****想实现全局变量*******/ 
    print @digStationIds 

end 
  close  station_cur  
  deallocate  cursor  station_cur

41.带事务的游标插入记录
declare @error int


declare @rowcount int


begin transaction 


insert table1 (col1, col2) values (@param1, @param2)


select @error = @@error, @rowcount = @@rowcount


if (@error != 0)


begin
Rollback transaction


raiserror 99999 "error inserting to table1"


return 2 --2代表错误


end
if (@rowcount !> 0)


begin
Rollback transaction


raiserror 99999 "No row affected"


return 1 --1代表警告


end
commit transaction

42.带事务的游标修改记录
declare @error int


declare @rowcount int


begin transaction 


insert table1 (col1, col2) values (@param1, @param2)


select @error = @@error, @rowcount = @@rowcount


if (@error != 0)


begin
Rollback transaction


raiserror 99999 "error inserting to table1"


return 2 --2代表错误


end
if (@rowcount !> 0)


begin
Rollback transaction


raiserror 99999 "No row affected"


return 1 --1代表警告


end
commit transaction

43.带事务的游标删除记录
declare @error int
declare @rowcount int
begin transaction 
insert table1 (col1, col2) values (@param1, @param2)
select @error = @@error, @rowcount = @@rowcount
if (@error != 0)


begin
Rollback transaction


raiserror 99999 "error inserting to table1"


return 2 --2代表错误


end
if (@rowcount !> 0)


begin
Rollback transaction


raiserror 99999 "No row affected"


return 1 --1代表警告

end
commit transaction

44.跨数据库复制全部表
if not exists (select * from syscat.tablespaces where tbspace=%%1)
begin

end

45.跨实例复制全部表
if not exists (select * from syscat.tablespaces where tbspace=%%1)
begin

end

46.记录日志文件

47.遍历所有表统计行数

48.重命名表
sp_rename %%1,%%2

49.重命名数据库

50.MD5算法

51.遍历字段求算MD5值
52.保存本地文件到二进制字段
53.保存二进制字段数据到本地磁盘
54.显示版本
select @@version

55.获取当前用户名
select user_name()

56.判断质数

57.计算100以内的质数
WITH T 
   AS 
   (SELECT ROWNUM * 2 + 1 RN FROM DUAL CONNECT BY LEVEL < 4999)
   SELECT 2 FROM DUAL
   UNION ALL
   (
   SELECT RN FROM T 
   WHERE RN > 1
   MINUS
  SELECT A.RN * B.RN FROM T A, T B
  WHERE A.RN <= B.RN
  AND A.RN > 1
  AND A.RN <= 100
  AND B.RN > 1
  AND B.RN <= 5000
  )

58.最大公约数

59.最小公倍数

60.阶乘

61.构造阶乘数列

62.平均偏差

63.中位数

64.均方差
select STDDEV(%%1) from %%2;

65.完全平方数

66.整数逆序数

67.进制转换

68.金额转换为大写

69.控制小数输出位数

70.将空值转换为0

71.保留十位有效数字
72.创建限制视图
73.查询从未使用的索引

74.反转字符串

75.亲密数

76.自守数

77.水仙花数

78.回文素数

79.平方回文数

80.分解质因数
    public void fengjie(int n){
        for(int i=2;i<=n/2;i++){
            if(n%i==0){
                System.out.print(i+"*");
                fengjie(n/i);
                }
        }
        System.out.print(n);
        System.exit(0);///不能少这句，否则结果会出错
    }
             String str="";
             exp2 c=new exp2();
             str=javax.swing.JOptionPane.showInputDialog("请输入N的值（输入exit退出）：");
             int N;
             N=0;
             try{
                     N=Integer.parseInt(str);
                     }catch(NumberFormatException e){
                         e.printStackTrace();
                         }
            System.out.print(N+"分解质因数："+N+"=");
            c.fengjie(N);

81.查找重复记录
SELECT * FROM %%1 a where exists(SELECT 1 FROM %%1 b where b.%%2=a.%%2 and a.rowid>b.rowid )

82.查看最大连接数
sp_who go
sp_configure 'number of user connections' 
sp_configure 'number of user connections',200  

83.修改最大连接数
sp_configure "user connections",1000

84.计算两个日期之间的月数差额

85.给日期加上指定的月数

86.获取后续的日期

87.获取指定月份的最后一天

88.设置单词首字母大写

89.执行SQL文件

90.显示所有触发器
?Create     procedure   get_data_to_model_table(churn_month   integer) 
              Begin 
              decalre   str_month   char(6); 
              set   str_month=char(churn_month);
            set   sql_str   = 'select   COUNT(1)     from   from   sysibm.systables   where   name= '|| ' ' ' '||test_ '||str_month|| ' ' ' '|| '   and   type= '|| ' ' ' '|| 'T '|| ' ' ' '|| '   and   creator= '|| ' ' ' '|| 'mymodel '|| ' ' ' '; 
            execute   immediate   sql_str   INTO   rec_count_total; 
            commit; 
if   re_count_total=0   then 

 
        else   
            ALTER     TABLE   mymodel.test_200312   ACTIVATE     NOT     LOGGED     INITIALLY     WITH     EMPTY     TABLE 
        end   if; 

0.创建
0.0.创建数据库
CREATE DATABASE %%1

0.1.创建整型字段
CREATE TABLE %%1
(
%%2 INT NOT NULL
)

0.2.创建整型主键字段
CREATE TABLE %%1
(
%%2 INT NOT NULL PRIMARY KEY
)

0.3.创建字符串字段
CREATE TABLE %%1
(
%%2 VARCHAR(50) NOT NULL
)

0.4.创建字符串主键字段
CREATE TABLE %%1
(
%%2 CHAR(50) NOT NULL PRIMARY KEY
)

0.5.创建日期字段
CREATE TABLE %%1
(
%%2 DATETIME NOT NULL
)

0.6.创建时间字段
CREATE TABLE %%1
(
%%2 DATETIME NOT NULL
)

0.7.创建时间戳字段
CREATE TABLE %%1
(
%%2 DATETIME NOT NULL
)

0.8.创建短整型字段
CREATE TABLE %%1
(
%%2 SMALLINT NOT NULL
)

0.9.创建长整型字段
CREATE TABLE %%1
(
%%2 BIGINT NOT NULL
)

0.10.创建浮点值字段
CREATE TABLE %%1
(
%%2 REAL NOT NULL
)

0.11.创建双精度浮点值字段
CREATE TABLE %%1
(
%%2 FLOAT NOT NULL
)

0.12.创建数学型整数值字段
CREATE TABLE %%1
(
%%2 NUMBERIC NOT NULL
)

0.13.创建二进制值字段
CREATE TABLE %%1
(
%%2 VARBINARY NOT NULL
)

0.14.创建整型外键字段
CREATE TABLE %%1
(
%%2 INT NOT NULL REFERENCES %%3 (id)
)

0.15.创建字符串外键字段
CREATE TABLE %%1
(
%%2 CHAR(50) NOT NULL REFERENCES %%3 (id)
)

0.16.删除表
DROP TABLE %%1

1.登录

2.数据库全局搜索
select * from sysibm.systables
DB2表信息以及字段信息的表

记录表信息的表：syscat.tables;
 
记录字段信息的表：syscat.COLUMNS;
 
 除了这两个外，还有
 
记录存储过程的表：syscat.procedures
 
等等，估计都还有的
 
如果要创建和这些表一样的表结构的，可以用
 
create table tableName like syscat.procedures;来创建。


列出当前schema的所有表：

D:\Documents and Settings\lsq>db2 list tables|more

表/视图   　　
 模式                            类型  创建时间
------------------------------- --------------- ----- --------------------------
ACT                             LSQ             T     2009-02-14-13.22.08.515010
ADEFUSR                         LSQ             S     2009-02-14-13.22.12.546001
BUFFERPOOL_TEST1                LSQ             T     2009-02-18-20.35.25.968001
CATALOG                         LSQ             T     2009-02-14-13.22.23.703001
CL_SCHED                        LSQ             T     2009-02-14-13.22.05.562001
CONNHEADER_TEST1                LSQ             T     2009-02-18-20.35.25.890001
CONTROL_TEST1                   LSQ             T     2009-02-18-20.35.26.015000
CUSTOMER                        LSQ             T     2009-02-14-13.22.22.015006
DBMEMUSE_TEST1                  LSQ             T     2009-02-18-20.35.26.031001
DB_TEST1                        LSQ             T     2009-02-18-20.35.24.796000
DEADLOCK_TEST1                  LSQ             T     2009-02-18-20.35.25.625001
DEPARTMENT                      LSQ             T     2009-02-14-13.22.05.937001
DEPT                            LSQ             A     2009-02-14-13.22.07.015004
DLCONN_TEST1                    LSQ             T     2009-02-18-20.35.25.656001
EMP                             LSQ             A     2009-02-14-13.22.07.296004
EMPACT                          LSQ             A     2009-02-14-13.22.08.515005
EMPLOYEE                        LSQ             T     2009-02-14-13.22.07.015006


列出指定schema的所有表：

D:\Documents and Settings\lsq>db2 list tables for schema sysstat

表/视图   　　
 模式                            类型  创建时间
------------------------------- --------------- ----- --------------------------
COLDIST                         SYSSTAT         V     2009-02-14-13.21.45.437003
COLGROUPDIST                    SYSSTAT         V     2009-02-14-13.21.45.500007
COLGROUPDISTCOUNTS              SYSSTAT         V     2009-02-14-13.21.45.500011
COLGROUPS                       SYSSTAT         V     2009-02-14-13.21.45.500003
COLUMNS                         SYSSTAT         V     2009-02-14-13.21.45.453003
FUNCTIONS                       SYSSTAT         V     2009-02-14-13.21.45.515003
INDEXES                         SYSSTAT         V     2009-02-14-13.21.45.531003
ROUTINES                        SYSSTAT         V     2009-02-14-13.21.45.531007
TABLES                          SYSSTAT         V     2009-02-14-13.21.45.531011

查看表结构

D:\Documents and Settings\lsq>db2 describe table sales

                                数据类型                      列
列名                             模式       数据类型名称      长     小数位      NULL
------------------------------- --------- ------------------- ---------- ----- ------
SALES_DATE                      SYSIBM    DATE                         4     0 是
SALES_PERSON                    SYSIBM    VARCHAR                     15     0 是
REGION                          SYSIBM    VARCHAR                     15     0 是
SALES                           SYSIBM    INTEGER                      4     0 是

2.1.数据库全局搜索字符串

2.2.数据库全局搜索数字或日期


3.多对多表关系查询

4.一年以前的今天

5.分割字符串函数

6.分割字符串后的元素个数

7.分割字符串后指定索引的元素

8.批量模糊匹配
select * from company left joiner strtab on company.Company_Name like '%'+strtab.str+'%'
8.1.构造SQL语句搜索字符串出现的表和字段
8.2.构造SQL连续搜索字符串出现的表和字段

9.显示一个表的结构

10.执行一个存储过程 

11.获取环境变量值

12.冒泡排序
12.1.两元素排序

12.2.三元素排序

12.3.四元素排序

12.4.五元素排序

12.5.六元素排序

13.新建用户，授权，获取表的磁盘空间语句
13.1.表的磁盘空间使用信息

13.2.建用户

13.3.用户授权

13.4.删除数据库

13.5.删除用户

14.选择性处理字段数据

15.查询结果创建表
Create Table %%1 As %%2;

16.创建角色
CREATE ROLE %%1;
\

17.删除角色
DROP ROLE %%1;
\

18.限制结果集范围
SELECT * FROM %%1 WHERE id NOT IN
(
SELECT id FROM %%1 ORDER BY id DESC FETCH FIRST %%3 ROWS ONLY
)
ORDER BY id DESC FETCH FIRST 3 ROWS ONLY

19.计算字符在字符串中出现的次数
SELECT %%2,LENGTH(%%2)-LENGTH(REPLACE(%%2,%%3,'')) FROM SYSIBM.SYSDUMMY1

20.计算日期记录间相隔的天数
SELECT t.date1,DAYS(t.date2)-DAYS(t.date1) FROM
(
SELECT t2.%%1 date1,
(
SELECT MIN(t1.%%1) FROM %%2 t1 WHERE t1.%%1>t2.%%1
) date2
FROM %%2 t2
) t
ORDER BY t.date1

21.计算日期所在年的天数
SELECT DAYS(CurrentYear+1 year)-DAYS(CurrentYear) FROM
(
SELECT (%%1-DAYOFYEAR(%%1) DAY + 1 DAY) CurrentYear FROM SYSIBM.SYSDUMMY1
) t

22.计算日期所在月的天数
SELECT DAYS(%%1+1 MONTH-DAY(%%1) DAY)-DAYS(%%1-DAY(%%1) DAY+1 DAY)+1 FROM SYSIBM.SYSDUMMY1

23.计算日期所在月的首末日
SELECT (%%1-DAY(%%1) DAY+1 DAY),(%%1+1 MONTH-DAY(%%1) DAY) FROM SYSIBM.SYSDUMMY1

24.计算日期字段间相隔的天数
SELECT DAYS(%%1)-DAYS(%%2) FROM SYSIBM.SYSDUMMY1

25.数学函数
25.1.求绝对值
SELECT ABS(%%1) FROM SYSIBM.SYSDUMMY1

25.2.求幂
SELECT POWER(%%1,%%2) FROM SYSIBM.SYSDUMMY1

25.3.求平方根
SELECT SQRT(%%1,%%2) FROM SYSIBM.SYSDUMMY1

25.4.求随机数
SELECT RAND() FROM SYSIBM.SYSDUMMY1

25.5.求正弦值
SELECT SIN(%%1) FROM SYSIBM.SYSDUMMY1

25.6.求余弦值
SELECT COS(%%1) FROM SYSIBM.SYSDUMMY1

25.7.求反正弦值
SELECT ASIN(1/%%1) FROM SYSIBM.SYSDUMMY1

25.8.求反余弦值
SELECT ACOS(1/%%1) FROM SYSIBM.SYSDUMMY1

25.9.求正切值
SELECT TAN(%%1) FROM SYSIBM.SYSDUMMY1

25.10.求反正切值
SELECT ATAN(%%1) FROM SYSIBM.SYSDUMMY1

25.11.求两个变量的反正切值
SELECT ATAN2(%%1,%%2) FROM SYSIBM.SYSDUMMY1

25.12.求余切值
SELECT COT(%%1) FROM SYSIBM.SYSDUMMY1

25.13.求圆周率值
SELECT acos(-1)

25.14.弧度制转换为角度制
SELECT (%%1*180)/acos(-1) FROM SYSIBM.SYSDUMMY1

25.15.角度制转换为弧度制
SELECT (%%1*ACOS(-1)/180) FROM SYSIBM.SYSDUMMY1

25.16.求符号
SELECT SIGN(%%1) FROM SYSIBM.SYSDUMMY1

25.17.求整除余数
SELECT MOD(%%1,%%2) FROM SYSIBM.SYSDUMMY1

25.18.求以10为底的对数
SELECT LOG10(%%1) FROM SYSIBM.SYSDUMMY1

25.19.求自然对数
SELECT LOG(%%1) FROM SYSIBM.SYSDUMMY1

25.20.取小数的整数部分
SELECT int(CEILING(%%1)) FROM SYSIBM.SYSDUMMY1

26.取字符串长度
SELECT LENGTH(%%1) FROM SYSIBM.SYSDUMMY1

27.转换为小写
SELECT LCASE(%%1) FROM SYSIBM.SYSDUMMY1

28.转换为大写
SELECT UCASE(%%1) FROM SYSIBM.SYSDUMMY1

29.截去左侧空格
SELECT LTRIM(%%1) FROM SYSIBM.SYSDUMMY1
/*
SELECT LTRIM('%%1') FROM SYSIBM.SYSDUMMY1
*/

30.截去右侧空格
SELECT RTRIM(%%1) FROM %%2
/*
SELECT RTRIM('%%1') FROM SYSIBM.SYSDUMMY1
*/

31.截去两侧空格
SELECT LTRIM(RTRIM(%%1)) FROM SYSIBM.SYSDUMMY1

32.字符串替换
SELECT REPLACE(%%1,%%2,%%3) FROM SYSIBM.SYSDUMMY1
/*
SELECT REPLACE(%%1,%%2,%%3) FROM SYSIBM.SYSDUMMY1
*/

33.字符转ACII码
SELECT ASCII(%%1) FROM SYSIBM.SYSDUMMY1

34.ACII码转字符
SELECT CHR(%%1) FROM SYSIBM.SYSDUMMY1
/*
SELECT CHR(%%1) FROM SYSIBM.SYSDUMMY1
*/

35.取当前时间
35.1.取时间戳
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1

35.2.取日期
SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1

35.3.取时间
SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1

36.星期数计算
SELECT DAYNAME(%%1) FROM %%2

37.空值不显示
SELECT NULLIF(%%1,'') FROM %%2

38.提取路径名
SELECT LEFT(%%1,INSTR(REVERSE(%%1),'\')+1) FROM SYSIBM.SYSDUMMY1
/*
SELECT LEFT(%%1,INSTR(REVERSE(%%1),'\')+1) FROM SYSIBM.SYSDUMMY1
*/

39.提取文件名
SELECT RIGHT(%%1,LENGTH(%%1)-INSTR(REVERSE(%%1),'\')-1) FROM SYSIBM.SYSDUMMY1
/*
SELECT RIGHT(%%1,LENGTH(%%1)-INSTR(REVERSE(%%1),'\')-1) FROM SYSIBM.SYSDUMMY1
*/

40.提取扩展名
SELECT RIGHT(%%1,LENGTH(%%1)-INSTR(REVERSE(%%1),'.')-1) FROM SYSIBM.SYSDUMMY1
/*
SELECT RIGHT(%%1,LENGTH(%%1)-INSTR(REVERSE(%%1),'.')-1) FROM SYSIBM.SYSDUMMY1
*/

DB2中游标
begin
declare sqlcode integer default 0;
declare app_code varchar(10);
declare cursor1 cursor for select app_code from kf_app_class ;
open cursor1;
cursorLoop:
loop
fecth cursor1 into app_code ;
if sqlcode=100 then leave cursorLoop;
end if;
end loop;
end;

41.带事务的游标插入记录
  LANGUAGE SQL   
  NOT DETERMINISTIC   
  CALLED ON NULL INPUT   
  MODIFIES SQL DATA   
  INHERIT SPECIAL REGISTERS   
     
  begin    
  declare sqlcode integer default 0;
  declare  bb decimal(10,0);
  declare c1 cursor with hold for  select sid from t_p;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION   
        begin
             --goto fetch_loop ;   
        end;
begin
  open c1;
  fetch_loop:   
  LOOP   
    fetch c1 into bb;
    IF SQLCODE=100 THEN    
       LEAVE fetch_loop;   
    END IF;   
    -- do some work , may be caught exceptions       
  END LOOP fetch_loop;   
  close c1;
end;

42.带事务的游标修改记录
  LANGUAGE SQL   
  NOT DETERMINISTIC   
  CALLED ON NULL INPUT   
  MODIFIES SQL DATA   
  INHERIT SPECIAL REGISTERS   
     
  begin    
  declare sqlcode integer default 0;
  declare  bb decimal(10,0);
  declare c1 cursor with hold for  select sid from t_p;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION   
        begin
             --goto fetch_loop ;   
        end;
begin
  open c1;
  fetch_loop:   
  LOOP   
    fetch c1 into bb;
    IF SQLCODE=100 THEN    
       LEAVE fetch_loop;   
    END IF;   
    -- do some work , may be caught exceptions       
  END LOOP fetch_loop;   
  close c1;
end;

43.带事务的游标删除记录
  LANGUAGE SQL   
  NOT DETERMINISTIC   
  CALLED ON NULL INPUT   
  MODIFIES SQL DATA   
  INHERIT SPECIAL REGISTERS   
     
  begin    
  declare sqlcode integer default 0;
  declare  bb decimal(10,0);
  declare c1 cursor with hold for  select sid from t_p;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION   
        begin
             --goto fetch_loop ;   
        end;
begin
  open c1;
  fetch_loop:   
  LOOP   
    fetch c1 into bb;
    IF SQLCODE=100 THEN    
       LEAVE fetch_loop;   
    END IF;   
    -- do some work , may be caught exceptions       
  END LOOP fetch_loop;   
  close c1;
end;

44.跨数据库复制全部表
if not exists (select * from syscat.tablespaces where tbspace=%%1)
begin

end


45.跨实例复制全部表
if not exists (select * from syscat.tablespaces where tbspace=%%1)
begin

end

46.记录日志文件
47.遍历所有表统计行数
48.重命名表
rename table %%1 to %%2

49.重命名数据库

50.MD5算法

51.遍历字段求算MD5值
52.保存本地文件到二进制字段
53.保存二进制字段数据到本地磁盘
54.显示版本
select * from table (sysproc.ENV_GET_INST_INFO()) as t

55.获取当前用户名
select current user from sysibm.sysdummy1
values current user

56.判断质数

57.计算100以内的质数
58.最大公约数

59.最小公倍数

60.阶乘

61.构造阶乘数列
WITH temp(LEVEL, RESULT) AS  (  SELECT 1,1  FROM SYSIBM.SYSDUMMY1  UNION ALL  SELECT LEVEL+1,(LEVEL+1)*RESULT  FROM temp  WHERE LEVEL < 10  )  SELECT * FROM temp;

62.平均偏差
63.中位数

64.均方差
select STDDEV(%%1) from %%2;

65.完全平方数

66.整数逆序数

67.进制转换

68.金额转换为大写

69.控制小数输出位数

70.将空值转换为0

71.保留十位有效数字
72.创建限制视图
73.查询从未使用的索引

74.反转字符串

75.亲密数

76.自守数

77.水仙花数

78.回文素数

79.平方回文数

80.分解质因数
    public void fengjie(int n){
        for(int i=2;i<=n/2;i++){
            if(n%i==0){
                System.out.print(i+"*");
                fengjie(n/i);
                }
        }
        System.out.print(n);
        System.exit(0);///不能少这句，否则结果会出错
    }
             String str="";
             exp2 c=new exp2();
             str=javax.swing.JOptionPane.showInputDialog("请输入N的值（输入exit退出）：");
             int N;
             N=0;
             try{
                     N=Integer.parseInt(str);
                     }catch(NumberFormatException e){
                         e.printStackTrace();
                         }
            System.out.print(N+"分解质因数："+N+"=");
            c.fengjie(N);

81.查找重复记录
SELECT * FROM %%1 a where exists(SELECT 1 FROM %%1 b where b.%%2=a.%%2 and a.rowid>b.rowid )

82.查看最大连接数
db2 get db cfg for database
;db2 get snapshot for dbm 

83.修改最大连接数
db2 update db cfg for database using maxappls 100
;db2 connect to netdb user sbnetdba using sbnetdba

84.计算两个日期之间的月数差额

85.给日期加上指定的月数

86.获取后续的日期

87.获取指定月份的最后一天
select last_day( to_date('02/1/2005','DD/MM/YYYY') )

88.设置单词首字母大写

89.执行SQL文件

90.显示所有触发器

?1.修改字符串时转换为大写

2.修改字符串时转换为小写

3.级联添加
create trigger %%0 on tb_%%1
AS
instead of insert
as
insert into tb_%%2 select type from tb_%%1
insert into tb_%%1 select * from inserted 
GO

?1.修改字符串时转换为大写

2.修改字符串时转换为小写

3.级联添加
create trigger %%0 on tb_%%1
AS
instead of insert
as
insert into tb_%%2 select type from tb_%%1
insert into tb_%%1 select * from inserted 
GO

?1.修改字符串时转换为大写
Create Trigger %%0
AFTER INSERT OR UPDATE 
For EACH ROW
BEGIN
UPDATE  %%1
SET %%2=UPPER( %%2)
Where %%1.id=:OLD.id
END;

2.修改字符串时转换为小写
Create Trigger %%0
AFTER INSERT OR UPDATE 
For EACH ROW
BEGIN
UPDATE  %%1
SET %%2=LOWER( %%2)
Where %%1.id=:OLD.id
END;

3.级联添加
create trigger %%0 on tb_%%1
AS
instead of insert
as
insert into tb_%%2 select type from tb_%%1
insert into tb_%%1 select * from inserted 
GO

?1.修改字符串时转换为大写
Create Trigger %%0
ON %%1
For INSERT,UPDATE
AS
UPDATE %%1
SET %%2=UPPER(%%2)
WHERE %%1.id=INSERTED.id

2.修改字符串时转换为小写
Create Trigger %%0
ON %%1
For INSERT,UPDATE
AS
UPDATE %%1
SET %%2=LOWER(%%2)
WHERE %%1.id=INSERTED.id

3.级联添加
create trigger %%0 on tb_%%1
AS
instead of insert
as
insert into tb_%%2 select type from tb_%%1
insert into tb_%%1 select * from inserted 
GO

?1.修改字符串时转换为大写

2.修改字符串时转换为小写

3.级联添加
create trigger %%0 on tb_%%1
AS
instead of insert
as
insert into tb_%%2 select type from tb_%%1
insert into tb_%%1 select * from inserted 
GO

3.级联添加
create trigger %%0 on tb_%%1
AS
instead of insert
as
insert into tb_%%2 select type from tb_%%1
insert into tb_%%1 select * from inserted 
GO
Déclaration：
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Article précédent：汉化SqlDbxPersonal3.51Article suivant：SQL求小于1000的所有水仙花数

Articles Liés

Voir plus