下载文档原格式
SYBASE IQ 知识整理目录SYBASE IQ 知识整理 --------------------------------------------------------------------------------------- 01概述--------------------------------------------------------------------------------------------------- 4 1.1内容简介 (4)1.2Iq数据库的物理限制 (5)2SybaseIQ的安装 ---------------------------------------------------------------------------------- 5 2.1Sybase IQ单服务器系统的安装 (5)2.1.1操作系统级别的配置-------------------------------------------------------------------- 52.1.2搭建IQ数据库的用户环境------------------------------------------------------------ 62.1.3安装IQ数据库软件 --------------------------------------------------------------------- 72.1.4创建IQ数据库 --------------------------------------------------------------------------- 7 2.2Sybase IQ Multiplex多机系统安装 (8)2.2.1基础 ----------------------------------------------------------------------------------------- 82.2.2安装和配置方法-------------------------------------------------------------------------- 82.2.3MultiPlex的注意事项------------------------------------------------------------------- 92.2.4Multiplex重建查询服务器步骤 ------------------------------------------------------ 9 2.3注册插件 (9)2.4Utility数据库 (10)3优化和配置数据库参数 ------------------------------------------------------------------------- 10 3.1配置数据库优化参数 (10)3.2多个iq数据库的设置 (10)3.3配置内存和高速缓存 (11)3.3.1配置高速缓存分区---------------------------------------------------------------------- 113.3.2示例 ---------------------------------------------------------------------------------------- 11 3.4设置并发查询 (12)4Sybase IQ 数据加载----------------------------------------------------------------------------- 12 4.1数据加载语法 (12)4.2从定长的文本文件装载数据是装载数据的最快方式 (13)4.3使用LOAD TABLE装载文本文件注意事项 (13)4.4使用LOAD TABLE如何装载bcp导出的数据文件 (13)4.5数据装载时候遇到不能装载也不报错的问题 (14)4.6LOAD TABLE 如何进行错误处理 (14)4.7Loadtable 其他一些问题处理 (15)4.7.1数据库升级后load错误--------------------------------------------------------------- 154.7.2LOAD TABLE对缺省值的支持----------------------------------------------------- 164.7.3Sybase iq 加载时跳过错误 ----------------------------------------------------------- 165数据导出 ------------------------------------------------------------------------------------------- 16 6索引-------------------------------------------------------------------------------------------------- 17 6.1IQ的索引特性： (17)6.2索引的适用情况 (18)6.3并行执行创建索引 (19)7Sybase IQ 的主要语法简介-------------------------------------------------------------------- 19 7.1变量 (20)7.1.1局部变量 ---------------------------------------------------------------------------------- 207.1.2全局变量 ---------------------------------------------------------------------------------- 207.1.3连接级变量 ------------------------------------------------------------------------------- 20 7.2注释 (21)7.3表达式 (21)7.3.1IF 表达式 --------------------------------------------------------------------------------- 217.3.2CASE 表达式的语法 ------------------------------------------------------------------ 217.3.3游标FETCH 语句 ---------------------------------------------------------------------- 227.3.4循环语句FOR --------------------------------------------------------------------------- 237.3.5LOOP 语句------------------------------------------------------------------------------- 24 7.4日期和字符串比较 (25)7.5ALTER TABLE 语句 (25)7.6BACKUP 语句 (26)7.6.1语法 ---------------------------------------------------------------------------------------- 26 7.7创建iq空间设备（dbspace）语法 (26)8系统管理 ------------------------------------------------------------------------------------------- 27 8.1使用存储过程获取信息 (27)8.2查看数据库文件 (27)8.3Multiplex多机系统管理 (28)8.3.1主数据库空间的计数在写入和查询服务器上不同 ----------------------------- 288.3.2Multiplex 同步“Not enough space”错误---------------------------------------- 288.3.3备份和恢复Multiplex ----------------------------------------------------------------- 288.3.4更换写入服务器------------------------------------------------------------------------- 30 8.4系统表 (32)8.4.1DUMMY 系统表------------------------------------------------------------------------ 328.4.2IQ_MPX_INFO 系统表 --------------------------------------------------------------- 328.4.3Sysobjects系统表 ----------------------------------------------------------------------- 328.4.4SYSTABLE 系统表 -------------------------------------------------------------------- 338.4.5SYSCOLUMN 系统表----------------------------------------------------------------- 338.4.6SYSFKCOL 系统表 -------------------------------------------------------------------- 338.4.7SYSFOREIGNKEY 系统表 ---------------------------------------------------------- 338.4.8SYSINDEX 系统表 -------------------------------------------------------------------- 33 8.5恢复数据库 (33)8.6系统字符集问题 (34)8.7数据库权限 (34)8.7.1添加新用户 ------------------------------------------------------------------------------- 348.7.2授予对表、视图、存储过程的权限------------------------------------------------ 358.7.3撤消用户权限 ---------------------------------------------------------------------------- 358.7.4删除Sybase IQ 用户帐户------------------------------------------------------------ 368.7.5数据库连接问题------------------------------------------------------------------------- 368.7.6管理组 ------------------------------------------------------------------------------------- 368.7.7资源管理 ---------------------------------------------------------------------------------- 37 8.8系统日志清理 (37)8.8.1单机系统日志清理---------------------------------------------------------------------- 378.8.2SybaseIQ multiplex 日志清理-------------------------------------------------------- 38 8.9IQ数据仓库清理消息文件*.iqmsg (38)8.10数据库锁 (39)8.10.1锁定用户 ---------------------------------------------------------------------------------- 398.10.2解锁用户 ---------------------------------------------------------------------------------- 398.10.3锁表查看 ---------------------------------------------------------------------------------- 39 9存储过程 ------------------------------------------------------------------------------------------- 40 9.1存储过程的语法规则 (40)9.2输出存储过程中print消息； (40)9.3系统存储过程 (43)9.4Multiplex 系统过程 (43)10常用的函数 ---------------------------------------------------------------------------------------- 43 10.1日期和时间函数 (43)10.2CONVERT数据类型转换函数 (43)10.3字符串函数 (44)11故障排除（添加中） ---------------------------------------------------------------------------- 45 11.1insert thrashing detected 或者Hash find thrashingdetected (45)11.2ASA Error -1009134: Insufficient buffers for sort. (45)11.3Multiplex出现other version 数值过大 (46)11.4删除数据库中损坏的表 (46)11.5使用强制模式恢复数据库 (47)11.6删除损坏的数据库空间 (48)11.7删除重复记录 (49)11.8一个实现IQ结果集分页SQL查询的调优 (49)12系统诊断工具SQL Trace ----------------------------------------------------------------------- 51 12.1SYBASE IQ 15之前的SQL Trace (51)12.2SYBASE IQ 15的SQL Trace功能增强 (52)13IQ 15 集群搭建----------------------------------------------------------------------------------- 53 13.1环境要求 (53)13.2创建Simplex IQ Server (54)13.3创建IQ Multiplex (55)13.4设置IQ Multiplex (56)14获取iq数据库文件和日志路径 --------------------------------------------------------------- 57 15IQ存储过程返回集的问题 --------------------------------------------------------------------- 57 16Utility数据库-------------------------------------------------------------------------------------- 58 16.1命令行下面启动和执行 (58)17IQ的官方资源 ------------------------------------------------------------------------------------ 5818附录-------------------------------------------------------------------------------------------------- 60 18.1Iq数据库创建脚本 (60)18.1.1以普通方式创建数据库（非裸设备）--------------------------------------------- 6018.1.2以裸设备方式创建数据库示例 ------------------------------------------------------ 60 18.2开机自动启动数据库示例 (61)18.3数据库配置文件params.cfg (61)18.4IQ15以上版本安装 (61)18.5Iq命令行 (62)18.5.1dbsqlc命令实例： ---------------------------------------------------------------------- 6218.5.2isql命令实例：-------------------------------------------------------------------------- 62 19硬件方面问题 ------------------------------------------------------------------------------------- 63 19.1对每块磁盘进行独立的磁盘扫描 (63)19.2为linux设定swap分区 (64)19.3Aix 磁盘操作 (64)19.4配置NFS (66)19.5如何格式化2T以上的分区 (66)19.6时间设置 (66)19.7配置图形安装界面 (66)19.8为Red Hat Linux5添加裸设备 (67)19.9为linux4添加裸设备 (67)19.10Liunx清理swap和缓存 (68)19.10.1清理swap --------------------------------------------------------------------------------- 6819.10.2清理缓存 ---------------------------------------------------------------------------------- 68 19.11磁盘柜多路径驱动 (68)20优化指导 ------------------------------------------------------------------------------------------- 721概述1.1 内容简介本手册主要针对sybase iq文档内容进行提炼和总结，主要包括：Sybase IQ 单机系统的安装方法Sybase IQ 的性能配置Sybase IQ 多机系统搭建Sybase IQ 的主要语法简介Sybase IQ 的系统管理Sybase IQ 的存储过程Sybase IQ 的数据加载和导出Sybase IQ 的索引1.2 Iq数据库的物理限制2SybaseIQ的安装2.1 Sybase IQ单服务器系统的安装主要介绍linux系统下单机系统的安装，对linux3、4、5的版本均适用，windows系统的安装比较简单，无需额外配置，故不作说明。
Sybase入门教程概述70年代，随着大型共享数据库应用的普及，美国SYBASE公司基于当时的关系代数理论所提出的数据库关系模型，开发了Sybase数据库系统；Sybase是基于SQL（Structured Query Language）的著名关系模型数据库系统，是世界上最流行的DBMS软件之一；它能在多种计算机硬件平台上运行，并能通过连网形成分布式数据库环境；SQL Server是指运行在主机上，能提供SQL语言查询、修改或控制数据库中内容等操作的一个服务进程，形象地把它理解为一个数据库服务器；一般地数据库服务器有Data Server 和Backup Server两个服务进程组成。
数据库就是一系列表格（关系）所描述的客观世界中事实的集合；Sybase中的系统数据库基本包括有：master、model、sysprocs、tempdb，其它为用户库。
Sybase Adaptive Server Enterprise 12.5是Sybase公司开发的智能型数据库。
现场Sybase数据库系统结构1.1 参考资料《SYBASE 数据库维护快速参考手册Version 1.1》，SYBASE公司出版2 基础操作、配置和维护2.1 安装Sybase 12.5包括安装前环境配置、安装过程说明、安装成功测试、卸载等。
（详细请参考V1.00》）2.2 访问SQL Server在安装配置好的Sybase服务器，可以通过以下操作开始访问SQL Server进行数据的查询和控制等操作：? % isql –U用户名–S服务名password: //该处输入该明户的密码2.3 Sybase的启动和关闭2.3.1 启动Sybase数据库在$SYBASE/ASE-12_5/install目录下执行startserver命令启动Sybase数据库，如：%$SYBASE/ASE-12_5/install/startserver -f RUN_服务名%$SYBASE/ASE-12_5/install/Startserver –f RUN_服务名_BAKUP2.3.2 关闭Sybase数据库使用isql命令登录Sybase数据库系统，在查询sysservers表查看可关闭的服务，如isql –Usa -SjoliPassword:1> select * from sysservers2> go一般需要关闭的服务有：系统备份服务、系统主服务在isql提示里先关闭系统备份服务（默认为SYB_BACKUP），再关闭主服务（默认可以不输入主服务名），如：1>shutdown SYB_BACKUP2>go1>shutdown2>go2.4 Sybase用户管理2.4.1 创建登录用户（login）sp_addlogin login_name,passwd[,defaultdb[,deflanguage[,fullname]]]例子：1> sp_addlogin joli,joli,joli_db2> go2.4.2 删除登录用户（login）例子：1> sp_droplogin test2> go2.4.3 创建数据库用户（user）sp_adduser login_name[,name_in_db[,grpname]]例子：1> sp_adduser joli,joli2> go2.4.4 删除数据库用户（user）例子：1> sp_dropuser test2> go2.4.5 修改该用户为该数据库dbo例子：1>use joli_db3> sp_changedbowner joli2.4.6 查看数据库用户信息sp_displaylogin [login_name]sp_helpuser例子：1> sp_helpuser2> go1> sp_displaylog joli2> go1> sp_who2> go2.4.7 修改用户口令如果是修改sa口令，可用sa用户登录，然后执行以下命令修改1>sp_password “旧命令| null”,”new_password”2>go如果不是sa用户，则使用希望修改口令的user登录数据库，然后执行1> sp_password 旧命令，新命令2> go2.4.8 修改系统缺省设备? 关闭创建数据库master缺省设备：? 1>exec sp_diskdefault "master","defaultoff"? 指定user_db_dev为缺省设备：? 1>exec sp_diskdefault "user_db_dev","defaulton"2.5 创建、删除、修改Sybase设备、数据库2.5.1 创建设备例子：1> disk init2> name="lwz_dev",3> physname="/opt/sybase-12.5/data/lwz.dat",4> vdevno=2,5> size=5120006> go说明：物理设备名称为：joli_dev物理设备文件路径为：/opt/Sybase/joli/joli_dev.dat设备编号（该号不能重复）为：9大小（单位为2K）为：1000M（一般为用户数据库创建一个数字设备后，再单独为用户数据库创建一个日志设备）2.5.2 镜像设备例子：1> disk mirror2> name="joli_dev",3> mirror="/opt/sybase/joli/joli_dev_mirror.dat"4> go说明：创建设备joli_dev的镜像，镜像后的镜像设备为/opt/sybase/joli/joli_dev_mirror.dat。
sp_dboption [dbname,optname,{true┃false}]其中：dbname为用户所需设置选项的数据库名称。
如果执行带参数的sp_dboption，则当前使用的库必须是master。
不带参数便显示数据库选项清单。
但是，用户不能设置master库的数据库选项。
optname为用户所要设置或关闭的选项名称。
ASE 能识别选项名的任何唯一的字符串。
当其名称为关键字或含嵌入空格或标点符号时，用引号括起来。
{true┃false}——设置该选项时为true，关闭该选项时为false。
使用系统存储过程sp_dboption来设置数据库选项，它的语法规则如下：数据库选项的用途（1）Selectinto/bulkcopy使用bcp或批拷贝程序接口将数据高速拷贝到一张没有索引和触发器的表，Writetext或者Select into生成永久表，则此选项必须设置为true。
因此，当你使用bcp实用程序往用户数据库加载数据时，或者调用dbwretetext函数把一幅图片插入到某张表的image字段，或者通过Select into命令形成结果表时，别忘了把该选项置为true。
SQL Server为了快速执行上述操作，减少记录修改操作的日志。
但是，这种不记日志的操作会使事务日志与数据库中数据不同步。
这种事务日志对于系统失败后的恢复就没有用处。
系统禁止在这种情况下，dump transation到转储设备。
因此，这些不记日志记录的操作完成后，将Select into/bulkcopy选项关闭后，执行dump database。
由Select into或批拷贝对数据库产生未被日志记录的修改后，执行dump transaction命令时，显示错误信息，并指导用户改用dump database（而Wrrietext命令没有这种保护）。
由于临时库tempdb从不恢复，所以使用Select into产生临时表时不必将Selectinto/bulkcopy选项打开。
发现进程死掉，如何杀掉？查看环境变更配置是否正确？查看内存大小，CPU ？一般生产系统容易出现的故障，如何解决的？1．IQ数据库适用场合和不适用场合适用场合：Decision support system (DSS)决策支持系统Distributed data mart分布式数据集市Data warehouse数据仓库不适用场合：实时数据更新，在线事务处理不适合OLTP（联机事务处理系统）。
适合OLAP(联机分析处理, OLAP是数据仓库系统的主要应用)2．IQ特点1.高压缩比2.列存储，提高访问效率3.独特的索引结构（一般数据库使用B-Tree，IQ采用BitMap和BitWise结构）4.高效的数据加载3．IQ数据库几个数据文件的名字、作用IQ 存储：数据库名.iq信息日志：数据库名.iqmsg临时存储：数据库名.iqtmp目录存储：数据库名.db事务日志：数据库名.logIQ表定义保存在元数据表空间（目录存储）数据在IQ数据表空间中带索引存放4．创建用户数据库1．启动utility 数据库（start_asiq -n myserver -gu utility_db）或asiqdemo库（start_asiq @$ASDIR/asiqdemo.cfg $ASDIR/asiqdemo.db）2．通过IQ的客户端连接asiqdemo后，执行以下脚本建库。
create database '/home/sybiq/hdsdb/hdsdb.db' --（目录存储，必须在文件系统上）log on '/home/sybiq/hdsdb/hdsdb.log' --（事务日志）case respectpage size 4096java onjconnect onCOLLATION 'EUC_CHINA'iq path '/dev/rlvhdsdata1' --（IQ存储，可在裸设备和文件系统）iq page size 131072message path '/home/sybiq/hdsdb/hdsdb.iqmsg' –(信息日志)temporary path '/dev/rlvhdstmp1'; --（临时表空间，可为裸设备或文件系统）commit;5．cfg配置/*hdsdb.cfg*/-n hdsdb169 –服务名-c 48m -- minimum cache size for Catalog Store-gd dba-gm 100 –用户并发数-gp 4096 --Catalog store page size-ti 4400 --客户端超时时间（分）-tl 300 –网络超时时间（秒）-x tcpip{port=2648} –访问端口-iqtss 1000-iqmc 800 --main cache size（M）-iqtc 1200 --temporary cache size（M）6．环境变量配置set option public.minimize_storage='on';set option public.load_memory_mb=500;set option public.notify_modulus=1000000;set option public.append_load='OFF';set option Public.Force_No_Scroll_Cursors ='ON';set option Public.query_temp_space_limit=0;set option Public.Disk_Striping = 'ON';set option Public.Disk_Striping_Packed = 'ON' ;set option public.query_plan = 'OFF';set option Public.Query_Detail ='OFF';set option Public.AUTO_COMMIT='ON';set option Public.CHAINED='OFF';SET OPTION Public.DA TE_FORMAT = 'YYYY-MM-DD';SET OPTION Public.TIME_FORMAT = 'HH.NN.ss';SET OPTION Public.TIMESTAMP_FORMAT = 'YYYY-MM-DD-HH.NN.ss.SSSSSS'; 7．启动和关闭IQ启动IQ：start_asiq start_asiq @asiqdemo.cfg asiqdemo.db关闭IQ：stop_asiq命令模式: dbstop启动utility数据库:start_asiq –n myserver –gu utility.db8．查看IQ进程，杀进程查看进程：ps -ef|grep sybiq杀进程：kill -9 1171524(对应的进程号)9．增加用户1.通过Sybase Central图形化操作2.通过sp_iqaddlogin（loginname, password）存储过程3.通过grant语句（GRANT CONNECT TO hds IDENTIFIED BY hds;）10．LOAD加载数据LOAD TABLE customer(customer_id '|',cust_type '|',organization '|',contact_name '|',contact_phone '|',address '|',city '|',state_province '|',postalcode '|',country '\x0a'FROM '/work/data/customer1.dat'ESCAPES OFFQUOTES OFF;（这两个参数必须要有）其他参数参考相关资料11．卸载（导出）数据1.客户端导出select * from employee ># empfile.txt，最大为2G2.服务器导出，最大为128Gset temporary option TEMP_EXTRACT_NAME1='/export/home/sybase/data/customer.dat';set temporary option TEMP_EXTRACT_COLUMN_DELIMITER='|';set temporary option TEMP_EXTRACT_BINARY='OFF';set temporary option TEMP_EXTRACT_SW AP='OFF';Select * from residential_customer;12．索引类型，创建索引FP索引（默认创建的，不能被显示删除，需要通过删除表的列来删除该索引）LF索引（适用于唯一值小于1500的列）HNG索引HG索引（适用于唯一值大于1500的列；可跨多列；如将列定义为主键，自动创建HG 索引）CMP索引WD索引（主要用于文本匹配，CONTAINS和LIKE操作中）DA TE索引DTTM索引TIME索引创建索引命令，例：CREATE HG INDEX cust_customer_id_hg ON customer(customer_id)13．备份和恢复数据库三种备份方式Full全备份Full backup of Catalog StoreFull backup of IQ StoreIncremental增量备份Full backup of Catalog StoreBacks up changes to IQ Store since last IQ backup of any typeIncremental-since-full全备份后的增量备份Full backup of Catalog StoreBacks up changes to IQ Store since last full IQ backup备份命令BACKUP DATABASE... [CRC ON | OFF]... [ATTENDED ON | OFF]... [BLOCK FACTOR integer]... [{FULL|INCREMENTAL|INCREMENTAL SINCE FULL}]... TO 'archive_device' [SIZE #_of_KB][ STACKER #_of_tapes_in_stack] ...[WITH COMMENT ’string’]恢复前提1．有DBA权限2．连接到utility_db database3．对于完全恢复，.db和.log文件不能存在4．对于增量恢复，.db和.log必须存在恢复命令RESTORE DA TABASE 'db_file'FROM 'archive_device'[FROM archive_device]...14．重要存储过程sp_iqcontext显示当前执行的语句和活动的连接sp_iqstatus显示数据库的多种状态信息sp_iqdbsize 显示当前数据库的大小sp_iqdbspace显示每个表空间的详细信息，包括它的文件ID，文件名，大小，使用的百分比以及读写状态等sp_iqcheckdb检测当前数据库的可用性及修复索引sp_iqconnection显示连接和版本信息，包括哪个用户正在使用的临时表空间，用户保持的版本信息，连接状态，数据库版本状态等sp_iqtable 显示表的详细信息sp_iqtablesize 显示指定表的大小sp_iqaddlogin、sp_iqdroplogin 增加删除用户sp_iqindex ,sp_iqindex_alt列出所有索引15．远程执行SQL dbisqldbisql -c "uid=DBA;pwd=SQL;eng=SERV1_asiqdemo;links=tcpip(host=SERV2;port=1234)" 16．基础SQL语句select，insert，update等基本的操作增加字段：ALTER TABLE customer ADD country char(20)删除字段：ALTER TABLE customer DROP cust_title增加表空间：CREATE DBSPACE mydb_2 AS 'e:\\s2\data\mydb_2.iq'IQ STORE SIZE 200 数据类型转换：castCase when语句17．常用调度脚本数据库重启脚本数据库备份脚本数据入库调度流程脚本18．Sybase安装(了解)SYBASE IQ for AIX软件要求：IQ126_for_aix_64bit.tarEBF12693.tar安装环境：AIX 5.2 64Bit安装步骤：1．建立起安装IQ的用户，以及IQ安装的文件系统；2．在安装主目录下，vi .profile文件，将当前目录在profile时面export.如：SYBASE="/home/sybiq"Export SYBASE3．将安装程序和补丁程序解压；Tar –xvf IQ126_for_aix_64bit.tar4．解压后，可以查看readme.txt，了解整个安装过程，也可能直接安装，直接执行Sybinstall如：% ./sybinstall5．安装完安装程序后，必须配置环境变量，将% source $SYBASE/ASIQ-12.6/ASIQ-12_6.sh 复制到IQ用户的环境变量.profile中。
目录2 SYBASE数据库2 一、数据库系统（Database system）的组成1．计算机系统 3 2．数据库 3 3．数据库管理系统（DBMS） 3 4．应用程序和用户 34 二、数据模型的基本概念1. 关系数据模型 42. 层次数据模型 53．网状数据模型 56 Sybase数据库简介1．版本 6 2．Sybase数据库的特点 6 3．Sybase数据库的组成78 四、SYBASE有关概念1．数据库设备8 2．系统数据库9 （1） master数据库9 （2） model数据库9 （3） sybsystemprocs数据库11 （4）临时数据库tempdb 11 （5）安全审核数据库sybsecurity 11 （7）命令语法数据库sybsyntax 11 3．数据库对象11 （2）视图(Views) 14 （3）索引(Index) 14 （4）存储过程(Stored procedures) 14 （5）触发器(Triggers) 16 （6）缺省与规则(Defaults and rules) 17 4．SQL Server的用户及权限1717（1）建立SQL服务器用户名（登录帐号）（2）增加数据库用户名17 5．SQL Server的启动与关闭19(1) 启动19(2) 关闭1920五、T-SQL语言1．标准的SQL语言20 （1）数据定义语句20(2) 数据操作语句21(3) 数据控制语言2426六、数据库备份与恢复1．系统软件故障恢复26 2．介质故障的恢复27 （1）备份数据库和事务日志27 （2）恢复数据库2829七、几个实用程序1. bcp 292. isql 303．showserver 30 4．startserver 30 5．syman 31 6．sybinit 3134八、SQL客户端软件1. DOS状态下SQL客户端软件3434 练习题36 附录附录1 只有在master数据库中才能有的系统表3636附录2 在用户数据库和系统数据库中都有的系统表36附录3 由SQL Server提供的系统过程功能和执行权限附录4 常用SQL命令38SYBASE数据库版权声明本文档是廖鹏翔在空余时间copy/paster并将遇到的问题整理处理的一个供大家参考的小东东，希望大家给点鼓励。
SYBASE存储过程⏹一、存储过程简介⏹二、存储过程的创建、修改、删除⏹三、存储过程中的参数、返回值和变量⏹四、存储过程中的流程控制语言⏹五、存储过程中的事务、游标⏹六、ASE存储过程和IQ存储过程的常见区别（附例子）1 存储过程简介⏹存储过程(Stored Procedure)是为了完成特定的功能而汇集成一组的SQL语句集，并为该组SQL语句命名、经编译后存储在SQL Server的数据库中。
用户可以根据需要决定是否在每次执行时让SQL Server进行重新编译。
用户可以指定存储过程的名字和给出参数来执行它。
⏹允许多个用户(有权)访问相同的代码。
⏹提供一种集中且一致的实现数据完整性逻辑的方法。
⏹存储过程用于实现：⏹-- 频繁使用的查询⏹-- 业务规则⏹-- 被其他过程使用的公共例行程序，例如错误处理例行程序等1.1 存储过程的优点⏹使用存储过程可加快运行速度⏹可减少网络交通⏹可重用、可共享性⏹存储过程也是一种安全机制⏹使用存储过程实现数据库完整性⏹提高数据与应用的独立性1.2 存储过程的分类⏹系统提供存储过程系统存储过程主要用于从系统表中获取信息、为系统管理员和有权用户提供更新系统表的途径。
系统存储过程的名字都以"sp_"为前缀。
如：sp_help 。
⏹用户定义存储过程用户定义的存储过程是由用户为完成某一特定功能而编写的存储过程。
1.2.1 系统存储过程⏹在SQL Server安装时自动建立了一些以sp_为前缀的系统存储过程，这些系统过程通常用来显示或修改系统表它们可为各用户所供享：⏹例如2 存储过程的创建、修改、删除⏹创建存储过程⏹执行存储过程⏹查看、修改、删除存储过程⏹存储过程中的注释2.1 创建存储过程⏹create proc procedure_nameasbeginSQL_statements[return]end2.1 创建存储过程⏹存储过程被放在当前正在使用的数据库中。
⏹在存储过程中可以引用在其他数据库中的对象⏹创建存储过程(create proc)语句不能与其他的SQL语句在同一个批中，即创建存储过程语句必须单独成为一个批。
⏹在存储过程中可以包含SQL语句，但是不能包含：use, create view, create rule, create default, create proc, create trigger 2.2 执行存储过程⏹语法:[exec[ute]] procedure_name [参数]2.3 查看、修改和删除存储过程⏹查看存储过程查看创建存储过程的源代码，使用： sp_helptext procedure_name查看存储过程所依赖的表和视图信息，使用： sp_depends procedure_name查看存储过程的一般信息，如创建日期等，使用： sp_help procedure_name2.3 查看、修改和删除存储过程⏹重新命名存储过程语法：sp_rename old_name , new_name例：将已创建的存储过程reports_1改名为reports_1b：exec sp_rename reports_1, report_lb2.3 查看、修改和删除存储过程⏹删除存储过程语法：drop proc procedure_name例：删除已创建的存储过程reports：drop proc reports2.4 存储过程中的注释⏹ SQL Server提供了两种在T－SQL中的注释方法：⏹¡ª使用斜杠星号对/* 注示内容*/例如:/*bind the rule to allcolumns with datatype*/exec sp_bindrule ul_tid,tid2.4 存储过程中的注释⏹¡ª使用双连字符¡°--¡±例如：--bind the rule to all columns--with datatype tidexec sp_bindrule ul_tid,tid3 存储过程中的参数、返回值和变量⏹存储过程中的参数⏹存储过程的返回状态⏹存储过程中的变量3.1 存储过程中的参数⏹输入参数(Input Parameters) 是指由调用程序向存储过程提供的变量值。
它们在创建存储过程语句中被定义，而在执行该存储过程语句中给出相应的变量值。
使用输入参数的优点是使存储过程得更加灵活。
3.1 存储过程中的参数语法： create proc procedure_name(@parameter_name datatype[, @parameter_name datatype...])asbeginSQL_statementsreturnend3.1.1 带参数存储过程举例：创建带参数的存储过程create proc proc_author_addr (@lname varchar(40))asbeginselect phone, address, city, statefrom authorswhere au_lname = @lnamereturn-- 在调用程序的执行存储过程命令中，将相应的值传递给-- 这个输入参数：用'Green'替换@lnameexec proc_author_addr @lname = 'Green'或exec proc_author_addr 'Green¡®end3.1.2 带有返回参数的存储过程举例：创建向调用程序返回值的存储过程：create proc proc_num_sales(@book_id char(6) = null, /* 输入参数 */@tot_sales int output /* 输出参数 */asbegin/* 过程将返回对于给定书号的书的总销售量 */select @tot_sales = sum(qty) from salesdetailwhere title_id = @book_idreturnend3.2 存储过程返回状态⏹每个存储过程的执行，都将自动返回一个返回状态，用于告知调用程序执行该存储过程的状况。
调用程序可根据返回状态作相应的处理。
⏹语法create proc procedure_name ( ...... )asbeginSQL_statementsreturn [ integer ]end3.2 存储过程返回状态⏹其中：integer为一整数。
如果不指定，系统将自动返回一个整数值。
系统使用0表示该过程执行成功；-1至¨C14 表示该过程执行有错，-15至 -99为系统保留值。
用户一般使用大于0的整数，或小于 -100的负整数。
3.3 存储过程中的变量⏹局部变量⏹全局变量3.3.1.1 局部变量的定义与声明⏹局部变量是－用户自定义变量－使用declare语句定义－具有名和数据类型－通过用户赋值－说明时给变量赋值为空－局部变量可在存储过程，或触发器中定义3.3.1.1 局部变量的定义与声明⏹语法DECLARE @var_name data_type[, @var_name data_type] 。
⏹举例declare @msg varchar(40)declare @myqty int, @myid char(4)3.3.1.2 为局部变量赋值⏹局部变量被声明时，它的初值为NULL ，使用SELECT语句将指定值赋给局部变量。
⏹语法select @var = expression[,@var = expression ][from…[where…]…⏹举例declare @var1 intselect @var1=993.3.1.2 为局部变量赋值⏹注意—在一个赋值给局部变量的select 语句中，可以使用常数、从表中取值、或使用表达式给局部变量赋值。
—不能使用同一SELECT 语句既给局部变量赋值，又检索数据返回给客户。
—一个赋值给局部变量的SELECT 语句，不向用户显示任何值。
3.3.1.3 对局部变量的限制⏹局部变量必须先用DECLARE定义，再用SELECT语句赋值后才能使用。
⏹局部变量只能使用在T－SQL语句中使用常量的地方。
⏹局部变量不能使用在表名、列名、其它数据库对象名、保留字使用的地方。
⏹局部变量是标量，它们拥有一个确切的值。
⏹赋值给局部变量的SELECT语句应该返回单个值。
如果赋值的SELECT语句没有返回值，则该局部变量的值保持不变；如果赋值的SELECT语句返回多个值，则该局部变量取最后一个返回的值。
3.3.1.4 使用局部变量时通常发生的错误⏹在程序中，使用局部变量通常容易发生的错误是数据类型不匹配。
即使用DECLARE 语句定义局部变量的数据类型与赋值给局部变量的值的数据类型不匹配。
如果发生这种情况，SQL Server 总是试图隐式转换为局部变量的数据类型。
3.3.2 全局变量⏹全局变量( Gloabal Variable )是SQL Server系统提供并赋值的变量。
用户不能建立全局变量，也不能使用SELECT语句去修改全局变量的值。
全局变量的名字用@@开始。
大多数全局变量的值报告本次SQL Server启动后发生的系统活动，可以使用系统存储过程sp_monitor显示全局变量的当前值。
通常全局变量的值赋给在同一批中的局部变量，以便保存和作进一步处理3.3.2.1 常用的全局变量⏹全局变量赋给的值⏹@@error 由最近一个语句产生的错误号⏹@@rowcount 被最近一个语句影响的行数⏹@@version SQL Server的版本号⏹@@max_connections允许与该SQL Server连接的最大用户个数⏹@@Servername 该SQL Server的名字、3.3.2.2 全局变量举例⏹举例select @@versiondeclare @book_price moneyselect @book_price = price from titleswhere title_id = 'BU1032'if @@rowcount = 0print 'no such title_id'elsebeginprint 'title_id exists with'select 'price of' = @book_priceend4 存储过程中的流程控制语言⏹流程控制SQL语句的执行顺序，这在存储过程、触发器、批中非常有用。
流控制关键字（命令）包括：⏹IF ELSE⏹IF EXISTS 和IF NOT EXISTS⏹BEGIN…END⏹RETURN⏹WHILE⏹BREAK和CONTINUE⏹WAITFOR⏹PRINT4.1 IF ELSE⏹部分语法(ASE)if boolean_expressionstatement[else [if boolean_expression1]statement1 ]⏹部分语法(IQ)if boolean_expression thenstatement[else [if boolean_expression1]statement1 ]End if4.2 IF EXISTS 和IF NOT EXISTS⏹功能当你关心数据是否存在时，在IF 语句中使用[NOT] EXISTS 是很有用的。
