主流数据库的常用存储过程大全
程序员文章站
2024-01-24 19:11:22
...
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:
= 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;ib.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 1
MINUS
SELECT A.RN * B.RN FROM T A, T B
WHERE A.RN 1
AND A.RN 1
AND B.RN 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 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
上一篇： Nodejs实现的一个静态服务器实例_node.js
下一篇： 查询函数定义与用法汇总