荣耀彩票代理

IT技术互动交流平台

DB2操作

来源:IT165收集  发布日期:2016-05-27 17:37:35

第一章DB2开发基础

荣耀彩票代理ZAIJINXINGDB2YINGYONGKAIFAZHIQIAN,LEJIEDB2YINGYONGCHENGXUDEJIEGOU,ZHANGWOXIANGGUANGAINIAN,SHEZHIKAIFAHUANJINGSHIHENBIYAODE。BENZHANGZHUYAOJIESHAOZHEIJIGEFANGMIANDENEIRONG。

1.1 DB2应用程序开发概述

1.1.1 程序结构

荣耀彩票代理DB2YINGYONGCHENGXUBAOKUOYIXIAJIGEBUFEN:

1.SHENGMINGHECHUSHIHUABIANLIANG

2.LIANJIEDAOSHUJUKU

3.ZHIXINGYIGEHUOZHEDUOGESHIWU

4.YUSHUJUKUDUANKAILIANJIE

5.JIESHUCHENGXU

YIGESHIWUSHIYIZUSHUJUKUCAOZUO,ZAITIJIAOJISHUJUKUZHIQIAN,BIXUQUERENWANQUANCHENGGONGZHIXING。ZAIQIANRUSHISQLYINGYONGCHENGXUZHONG,DANGYINGYONGCHENGXUCHENGGONGDILIANJIEDAOYIGESHUJUKUSHI,YIGESHIWUJIUZIDONGKAISHILE,JIESHUYUZHIXINGYITIAOCOMMITYUJUHUOZHEROLLBACKYUJU。TONGSHI,XIAYITIAOSQLYUJUKAISHIYIGEXINDESHIWU。

荣耀彩票代理MEIYIGEYINGYONGCHENGXUDEKAISHIBIXUBAOKUO:

l SHUJUKUGUANLIQIYONGLAIYUSUZHUCHENGXUJIAOHUDESUOYOUBIANLIANGHESHUJUJIEGOUDESHENGMING

l SHEZHISQLTONGXINQU(SQLCA),TIGONGCUOWUCHULIDESQLYUJU

ZHUYI:YONGJavaXIEDEDB2YINGYONGCHENGXUZAISQLYUJUCHUCUOSHIPAOCHUYIGESQLExceptionYICHANG,XUYAOZAIcatchKUAILICHULI,ERBUSHISHIYONGSQLCA。

MEIGEYINGYONGCHENGXUDEZHUTIBAOKUOFANGWENHEGUANLISHUJUDESQLYUJU。ZHEIXIEYUJUZUCHENGSHIWU,SHIWUBIXUBAOKUOXIALIEYUJU:

荣耀彩票代理l CONNECTYUJU,QIJIANLIYIGEYUSHUJUKUFUWUQIDELIANJIE

l YITIAOHUODUOTIAO:

▲SHUJUCAOZONGYUJU(LIRU,SELECTYUJU)

荣耀彩票代理▲SHUJUDINGYIYUJU(LIRU,CREATEYUJU)

▲SHUJUKONGZHIYUJU(LIRU,GRANTYUJU)

l COMMITHUOZHEROLLBACKYUJUJIESHUSHIWU

YINGYONGCHENGXUDEJIESHUTONGCHANGBAOKUOSHIFANGCHENGXUYUSHUJUKUFUWUQIDELIANJIEHESHIFANGQITAZIYUANDESQLYUJU。

1.1.2 开发方法选择

KESHIYONGJIZHONGBUTONGDECHENGXUSHEJIJIEKOULAICUNQU DB2 SHUJUKU。NINKEYI:

l JIANGJINGTAIHEDONGTAI SQL YUJUQIANRUYINGYONGCHENGXU。

荣耀彩票代理l ZAIYINGYONGCHENGXUZHONGBIANXIE“DB2 DIAOYONGCENGJIEKOU”(DB2 CLI) DEHANSHUDIAOYONG,YIDIAOYONGDONGTAI SQL YUJU。

l KAIFADIAOYONG“Java SHUJUKULIANJIE”YINGYONGCHENGXUSHEJIJIEKOU (JDBC API) DE Java YINGYONGCHENGXUHEXIAOCHENGXU。

荣耀彩票代理l KAIFAFUHE“SHUJUCUNQUDUIXIANG (DAO) ”HE“YUANCHENGSHUJUDUIXIANG (RDO) ” GUIFANDE Microsoft Visual Basic HE Visual C++ YINGYONGCHENGXU,YIJISHIYONG“DUIXIANGLIANJIEHEQIANRUSHUJUKU (OLE DB) QIAOJIE”DE“ActiveX SHUJUDUIXIANG”(ADO) YINGYONGCHENGXU。

荣耀彩票代理l SHIYONG IBM HUODISANFANGGONGJURU Net.Data、Excel、Perl、“KAIFANGSHISHUJUKULIANJIE”(ODBC) ZUIZHONGYONGHUGONGJURU Lotus Approach JIQICHENGXUSHEJIYUYAN LotusScript LAIKAIFAYINGYONGCHENGXU。

荣耀彩票代理l YAOZHIXINGBEIFENHEFUYUANSHUJUKUDENGGUANLIGONGNENG,YINGYONGCHENGXUKEYISHIYONG DB2 API。

YINGYONGCHENGXUCUNQU DB2 SHUJUKUDEFANGSHIJIANGQUJUEYUXIANGYAOKAIFADEYINGYONGCHENGXULEIXING。LIRU,RUGUOXIANGKAIFASHUJUSHURUYINGYONGCHENGXU,KEYIXUANZEJIANGJINGTAI SQL YUJUQIANRUYINGYONGCHENGXU。RUGUOXIANGKAIFAZAIWANWEIWANG (WWW) SHANGZHIXINGCHAXUNDEYINGYONGCHENGXU,KENENGYAOXUANZE Net.Data、Perl HUO Java。

1.2相关概念

1.2.1 嵌入式SQL编程

QIANRUSHISQLYINGYONGCHENGXUJIUSHIJIANGSQLYUJUQIANRUMOUGESUZHUYUYANZHONG,SQLYUJUTIGONGSHUJUKUJIEKOU,SUZHUYUYANTIGONGYINGYONGCHENGXUDEQITAZHIXINGGONGNENG。

“结构化查询语言”(SQL) 是一种数据库接口语言,它用来存取并处理 DB2 数据库中的数据。可以将 SQL 语句嵌入应用程序,使应用程序能执行 SQL 支持的任何任务,如检索或存储数据。通过使用 DB2,可以用 C/C++荣耀彩票代理、COBOL、FORTRAN、Java (SQLJ) 以及 REXX 程序设计语言来编写嵌入式 SQL 应用程序。

嵌入了 SQL 语句的应用程序称为主程序。用于创建主程序的程序设计语言称为宿主语言荣耀彩票代理。用这种方式定义程序和语言,是因为它们包含了 SQL 语句。

荣耀彩票代理DUIYUJINGTAI SQL YUJU,NINZAIBIANYIQIANJIUZHIDAO SQL YUJULEIXINGYIJIBIAOMINGHELIEMING。WEIYIWEIZHIDESHIYUJUZHENGSOUSUOHUOGENGXINDETEDINGSHUJUZHI。KEYIYONGSUZHUYUYANBIANLIANGBIAOSHINEIXIEZHI。ZAIYUNXINGYINGYONGCHENGXUZHIQIAN,YAOYUBIANYI、BIANYIHEKUNBANGJINGTAI SQL YUJU。JINGTAI SQL ZUIHAOZAIBIANDONGBUDADESHUJUKUSHANGYUNXING。FOUZE,ZHEIXIEYUJUHENKUAIHUIGUOSHI。

XIANGFAN,DONGTAI SQL YUJUSHIYINGYONGCHENGXUZAIYUNXINGQIGOUJIANBINGZHIXINGDENEIXIEYUJU。YIGETISHIZUIZHONGYONGHUSHURU SQL YUJUDEGUANJIANBUFEN(RUYAOSOUSUODEBIAOHELIEDEMINGCHENG)DEJIAOHUSHIYINGYONGCHENGXUSHIDONGTAI SQL YIGEHENHAODESHILI。 YINGYONGCHENGXUZAIYUNXINGSHIGOUJIAN SQL YUJU,RANHOUTIJIAOZHEIXIEYUJUJINXINGCHULI。

KEYIBIANXIEZHIYOUJINGTAI SQL YUJUHUOZHIYOUDONGTAI SQL YUJU,HUOZHEJIANYOULIANGZHEDEYINGYONGCHENGXU。

YIBANLAISHUO,JINGTAI SQL YUJUZUISHIHEYONGYUDAIYOUYUDINGYISHIWUDEGAOXINGNENGYINGYONGCHENGXU。YUDINGXITONGSHIZHEIZHONGYINGYONGCHENGXUYIGEHENHAODESHILI。

YIBANLAISHUO,DONGTAI SQL YUJUZUISHIHEYUBIXUZAIYUNXINGQIZHIDINGSHIWUDE、YAOKUAISUGENGGAISHUJUKUDEYINGYONGCHENGXU。JIAOHUSHICHAXUNJIEMIANSHIZHEIZHONGYINGYONGCHENGXUYIGEHENHAODESHILI。

JIANG SQL YUJUQIANRUYINGYONGCHENGXUSHI,BIXUANYIXIABUZHOUYUBIANYIYINGYONGCHENGXUBINGJIANGQIYUSHUJUKUKUNBANG:

1. CHUANGJIANYUANWENJIAN,YIBAOHANDAIQIANRUSHI SQL YUJUDECHENGXU。

荣耀彩票代理2. LIANJIESHUJUKU,RANHOUYUBIANYIMEIGEYUANWENJIAN。

YUBIANYICHENGXUJIANGMEIGEYUANWENJIANZHONGDE SQL YUJUZHUANHUANCHENGDUISHUJUKUGUANLICHENGXUDE DB2 YUNXINGQI API DIAOYONG。YUBIANYICHENGXUHAIZAISHUJUKUZHONGSHENGCHENGYIGECUNQUCHENGXUBAO,BINGKEXUANZESHENGCHENGYIGEKUNBANGWENJIAN(RUGUONINZHIDINGYAOCHUANGJIANYIGEDEHUA)。

CUNQUCHENGXUBAOBAOHANYOU DB2 YOUHUAQIWEIYINGYONGCHENGXUZHONGDEJINGTAI SQL YUJUXUANZEDECUNQUFANGAN。ZHEIXIECUNQUFANGANBAOHANSHUJUKUGUANLICHENGXUZHIXINGJINGTAI SQL YUJUSUOXUDEXINXI,YIBIANGAIGUANLICHENGXUKEYIYONGYOUHUAQIQUEDINGDEZUIYOUXIAODEFANGSHILAIZHIXINGZHEIXIEYUJU。DUIYUDONGTAI SQL YUJU,YOUHUAQIZAININYUNXINGYINGYONGCHENGXUSHICHUANGJIANCUNQUFANGAN。

KUNBANGWENJIANBAOHANCHUANGJIANCUNQUCHENGXUBAOSUOXUYAODE SQL YUJUHEQITASHUJU。KEYISHIYONGKUNBANGWENJIANZAIYIHOUZHONGXINKUNBANGYINGYONGCHENGXU,ERBUBISHOUXIANYUBIANYIYINGYONGCHENGXU。ZHONGXINKUNBANGCHUANGJIANZHENDUIDANGQIANSHUJUKUZHUANGTAIDEYOUHUACUNQUFANGAN。RUGUOYINGYONGCHENGXUJIANGCUNQUYUYUBIANYISHISUOYONGSHUJUKUBUTONGDESHUJUKU,ZEBIXUZHONGXINKUNBANGYINGYONGCHENGXU。RUGUOSHUJUKUTONGJIXINXIZISHANGCIKUNBANGHOUYIJINGGENGGAI,JIANYININZHONGXINKUNBANGYINGYONGCHENGXU。

3. SHIYONGZHUYUYANBIANYICHENGXUBIANYIXIUGAIDEYUANWENJIAN(YIJIQITAWU SQL YUJUDEWENJIAN)。

4. JIANGMUBIAOWENJIANYU DB2 HEZHUYUYANKULIANJIE,YISHENGCHENGYIGEKEZHIXINGCHENGXU。

5. RUGUOZAIYUBIANYISHIWEIDUIKUNBANGWENJIANJINXINGKUNBANG;HUOZHEZHUNBEICUNQUBUTONGSHUJUKU,ZEYINGDUIKUNBANGWENJIANJINXINGKUNBANGYICHUANGJIANCUNQUCHENGXUBAO。

6. YUNXINGGAIYINGYONGCHENGXU。CIYINGYONGCHENGXUSHIYONGCHENGXUBAOZHONGDECUNQUFANGANCUNQUSHUJUKU。

1.2.2 预编译

荣耀彩票代理CHUANGJIANYUANWENJIANZHIHOU,BIXUDUIMEIYIGEHANYOUSQLYUJUDESUZHUYUYANWENJIANYONGPREPMINGLINGJINXINGYUBIANYI。YUBIANYIQIJIANGYUANWENJIANZHONGDESQLYUJUZHUSHIDIAO,DUINEIXIEYUJUSHENGCHENGDB2YUNXINGSHIAPIDIAOYONG。

荣耀彩票代理ZAIYUBIANYIYIGEYINGYONGZHIQIAN,BIXULIANJIEDAOYIGESHUJUKUFUWUQI,BULUNSHIZIDONGLIANJIEHAISHIXIANXINGLIANJIE。JISHINIZAIKEHUDUANGONGZUOZHANSHANGYUBIANYIYINGYONGCHENGXU、YUBIANYIQIZAIKEHUDUANCHANSHENGDEXIUGAIHOUYUANWENJIANHEXINXI,YUBIANYIQIYEXUYAOSHIYONGFUWUQILIANJIELAIZHIXINGYIXIEQUERENRENWU。

YUBIANYIQIYECHUANGJIANSHUJUKUGUANLIQIZAICHULIZHENDUIMOUGESHUJUKUDESQLYUJUSHIXUYAODEXINXI。ZHEIXIEXINXICUNCHUZAIYIGECHENGXUBAOHUOZHEYIGEKUNBANGWENJIANHUOZHELIANGZHEZHIZHONG,SHIYUBIANYIQIDEXUANXIANGERDING。

XIAMIANSHISHIYONGYUBIANYIQIDEYIGEDIANXINGLIZI。YUBIANYIYIGEMINGJIAOfilename.sqcDECQIANRUSHISQLYUANWENJIAN,FACHUXIAMIANDEMINGLINGCHUANGJIANYIGECYUANWENJIAN,MORENMINGZIWEIfilename.c,HEYIGEKUNBANGWENJIAN,MORENMINGZIWEIfilename.bnd:

DB2 PREP filename.sqc BINDFILE

YUBIANYIQIZUIDUOCHANSHENGSIZHONGLEIXINGDESHUCHU:

l XIUGAIHOUDEYUANWENJIAN

l CHENGXUBAO

l KUNBANGWENJIAN

l XINXIWENJIAN

1、修改后的源文件

荣耀彩票代理ZHEIGEWENJIANSHIYUBIANYIQIJIANGSQLYUJUZHUANHUAWEIDB2YUNXINGSHIAPIDIAOYONGHOU,YUANSHIYUANWENJIANDEXINBANBEN。TABEIFUYULEXIANGYINGSUZHUYUYANDEKUOZHANMING。

2、程序包

荣耀彩票代理RUGUOSHIYONGLEPACKAGEXUANXIANG(MORENDE),HUOZHEMEIYOUZHIDINGRENHEBINDFILE、SYNTAX、SQLFLAGXUANXIANG,CHENGXUBAOCUNCHUZAISUOLIANJIEDAODESHUJUKUZHONG。CHENGXUBAOJINJINBAOHANZHIXINGFANGWENBENSHUJUDESQLYUJUSHIXUYAODESUOYOUXINXI。CHUFEINIYONGPACKAGE USINGXUANXIANGZHIDINGYIGEBUTONGDEMINGZI,FOUZEYUBIANYIQIJIANGSHIYONGYUANWENJIANMINGZIDEQIAN8GEZIFUZUOWEICHENGXUBAOMING。

SHIYONGPACKAGEXUANXIANGSHI,ZAIYUBIANYICHULIGUOCHENGZHONGSHIYONGDESHUJUKUBIXUYONGYOUYUANWENJIANZHONGJINGTAISQLYUJUCANKAODAODESUOYOUSHUJUKUDUIXIANG。LIRUBUNENGGOUYUBIANYIYITIAOSELECTYUJU,RUGUOCANKAODEBIAOZAISHUJUKUZHONGBUCUNZAI。

3、捆绑文件

荣耀彩票代理RUGUOSHIYONGLEBINDFILEXUANXIANG,YUBIANYIQIJIANGCHUANGJIANYIGEKUNBANGWENJIAN(KUOZHANMINGWEI.bnd),TABAOHANCHUANGJIANCHENGXUBAODEYIXIESHUJU。ZHEIGEWENJIANKEYIZAIHOUMIANYONGBINDMINGLINGJIANGYINGYONGKUNBANGDAOYIGEHUODUOGESHUJUKU。RUGUOZHIDINGLEBINDFILEXUANXIANG,MEIYOUZHIDINGPACKAGEXUANXIANG,KUNBANGBEIYANHUANZHIDAOZHIXINGBINDMINGLING。ZHUYI,DUIYUMINGLINGXINGCHULIQI(CLP),PREPMORENBUZHIDINGBINDFILEXUANXIANG。YINCI,RUGUONISHIYONGCLP,YOUXIANGYANHUANKUNBANG,NEIMENIBIXUZHIDINGBINDFILEXUANXIANG。

RUGUOZAIYUBIANYISHIQINGQIUYIGEKUNBANGWENJIANDANSHIMEIYOUZHIDINGPACKAGEXUANXIANG,BUHUIZAISHUJUKUZHONGCHUANGJIANCHENGXUBAO;DUIXIANGBUCUNZAIHEMEIYOUQUANXIANDESQLCODEBEIKANZUOJINGGAOERBUHUIBEIKANZUOCUOWU。ZHEISHIDENINENGGOUYUBIANYICHENGXUHECHUANGJIANYIGEKUNBANGWENJIAN,BUXUYAOCANKAODAODEDUIXIANGBIXUCUNZAI,YEBUXUYAONIYONGYOUZHIXINGZHENGBEIYUBIANYIDESQLYUJUDEQUANXIAN。

4、信息文件(Message File)

RUGUOSHIYONGLEMESSAGESXUANXIANG,YUBIANYIQIJIANGXINXIZHONGDINGXIANGDAOZHIDINGDEWENJIANZHONG。ZHEIXIEXINXIBAOKUOJINGGAOHECUOWUXINXI,TAMENMIAOSHULEZAIYUBIANYIGUOCHENGZHONGCHANSHENGDEWENTI。RUGUOYUANWENJIANMEIYOUYUBIANYICHENGGONG,SHIYONGJINGGAOHECUOWUXINXILAIDUANDINGWENTI,GAIZHENGYUANWENJIAN,RANHOUZAIYUBIANYI。RUGUOMEIYOUSHIYONGMESSAGEXUANXIANG,YUBIANYIXINXIBEIXIEDAOBIAOZHUNSHUCHUSHANG。

1.2.3 程序包

荣耀彩票代理CHENGXUBAOJIUSHICUNCHUZAIXIANGDUIYINGSHUJUKUZHONGDEBAOHANSHUJUKUXITONGZAIKUNBANGSHIDUITEDINGSQLYUJUSUOCHANSHENGDEFANGWENCELVE。

荣耀彩票代理SUOYOUSQLYUJUJINGGUOBIANYIYOUHUAHOUJIUCHANSHENGKEYIZHIJIEDUISHUJUKUJINXINGFANGWENDEFANGWENCELVE,CUNCHUYUXIANGYINGDESHUJUKUZHONG。ZHEIXIEFANGWENCELVEKEYIZAIYINGYONGCHENGXUDIAOYONGXIANGDUIYINGDESQLYUJUSHIDEDAOFANGWEN。CHENGXUBAODUIYINGYUTEDINGDEYINGYONGCHENGXU,DANSHIBINGBUSHIYUYINGYONGCHENGXUYIQICUNFANG,ERSHITONGXIANGDUIYINGDESHUJUKUYIQICUNFANG。

1.2.4 捆绑

KUNBANG(bind)SHICHUANGJIANSHUJUKUGUANLIQIZAIYINGYONGZHIXINGSHIWEILEFANGWENSHUJUKUERXUYAODECHENGXUBAODEGUOCHENG。KUNBANGKEYIZAIYUBIANYISHIZHIDINGPACKAGEXUANXIANGYINHANDIWANCHENG,HUOZHESHIYONGBINDMINGLINGYIJUYUBIANYIGUOCHENGZHONGCHANSHENGDEKUNBANGWENJIANXIANXINGDIWANCHENG。

荣耀彩票代理XIAMIANSHISHIYONGBINDMINGLINGDEYIGEDIANXINGLIZI。JIANGMINGWEIfilename.bndDEKUNBANGWENJIANKUNBANGDAOSHUJUKU,SHIYONGXIAMIANDEMINGLING:

DB2 BIND filename.bnd

荣耀彩票代理MEIYIGEDULIYUBIANYIDEYUANDAIMAMOKUAIDOUXUYAOCHUANGJIANYIGECHENGXUBAO。RUGUOYIGEYINGYONGYOU5GEYUANWENJIAN,QIZHONG3GEXUYAOYUBIANYI,NEIMEYAOCHUANGJIAN3GECHENGXUBAOHUOZHE3GEKUNBANGWENJIAN。MORENSHANG,MEIYIGECHENGXUBAODEMINGZIYUCHANSHENG.bndWENJIANDEYUANWENJIANMINGZIXIANGTONG,DANSHIZHIYAOQIAN8GEZIFU。RUGUOXINJIANDECHENGXUBAOMINGZIYUYICUNZAIYUSHUJUKUZHONGDECHENGXUBAOMINGXIANGTONG,XINDECHENGXUBAOJIANGTIHUANYUANXIANCUNZAIDECHENGXUBAO。YAOXIANXINGDIZHIDINGYIGEBUTONGDECHENGXUBAOMING,BIXUZAIPREPMINGLINGSHIYONGPACKAGE USINGXUANXIANG。

1.2.5 工作单元

荣耀彩票代理YIGEGONGZUODANYUANSHIYIGEDANYIDELUOJISHIWU。TABAOHANYIGESQLYUJUXULIE,ZAIZHEIGEXULIEZHONGDESUOYOUCAOZUO,BEIKANZUOYIGEZHENGTI,YAOMEDOUCHENGGONG,YAOMEDOUSHIBAI。DB2ZHICHILIANGZHONGLEIXINGDELIANJIE(connection)。LIANJIELEIXINGJUEDINGYIGEYINGYONGCHENGXURUHEYUYUANCHENGSHUJUKUGONGZUO,BINGQIEJUEDINGGAIYINGYONGCHENGXUTONGSHINENGYUDUOSHAOGESHUJUKUGONGZUO。

(1)LIANJIELEIXING1

YINGYONGCHENGXUZAIMEIGEGONGZUODANYUANZHONGZHINENGLIANJIEDANGESHUJUKU,CISHI,ZHEIGEGONGZUODANYUANCHENGWEIYUANCHENGGONGZUODANYUAN(RUOW, Remote Unit of Work)

(2)LIANJIELEIXING2

YUNXUYINGYONGCHENGXUZAIMEIGEGONGZUODANYUANZHONGLIANJIEDUOGESHUJUKU,CISHI,ZHEIGEGONGZUODANYUANCHENGWEIFENBUSHIGONGZUODANYUAN(DUOW, Distributed Unit of Work)

WOMENLAIKANXIAMIANDELIZI:

荣耀彩票代理(1)Remote Unit of Work – Type 1 Connect

 
  clip_image001

荣耀彩票代理ZAIZHEIGELIZIZHONG,LIANJIELEIXINGWEI1,GONGZUODANYUANWEIYUANCHENGGONGZUODANYUAN(RUOW),YINGYONGCHENGXUZAILIANJIEDAOSHUJUKUDB2_2ZHIQIAN,BIXUJIESHUDANGQIANDEGONGZUODANYUAN(JISHIWU,TONGGUOZHIXINGCOMMITYUJU)。

荣耀彩票代理(2)Distributed Unit of Work – Type 2 Connect

 
  clip_image002

荣耀彩票代理ZAIZHEIGELIZIZHONG,LIANJIELEIXINGWEI2,GONGZUODANYUANWEIFENBUSHIGONGZUODANYUAN(DUOW)。YINGYONGCHENGXUZAILIANJIEDAOSHUJUKUDB2_2ZHIQIAN,BUXUYAOJIESHUDANGQIANDESHIWU。ZAIYIGEGONGZUODANYUANZHONG,KEYIYOUDUOGESHUJUKULIANJIE,DANSHIZHIYOUYIGECHUYUJIHUOZHUANGTAI,QITADOUCHUYUSHUIMIANZHUANGTAI。YONGSET CONNECTION TO db_nameYUJUQIEHUANSHUJUKULIANJIE。

1.2.6 应用程序、捆绑文件与程序包之间的关系

YIGECHENGXUBAOSHIYIGECUNCHUZAISHUJUKUZHONGDEDUIXIANG,TABAOHANZAIZHIXINGMOUGEYUANWENJIANZHONGTEDINGSQLYUJUSHISUOXUDEXINXI。SHUJUKUYINGYONGCHENGXUDUIYONGLAICHUANGJIANYINGYONGCHENGXUDEMEIYIGEYUBIANYIYUANWENJIANSHIYONGYIGECHENGXUBAO。MEIYIGECHENGXUBAOSHIYIGEDULIDESHITI,TONGYIGEHUOBUTONGYINGYONGCHENGXUSUOSHIYONGDECHENGXUBAOZHIJIANMEIYOURENHEGUANXI。CHENGXUBAOZAIDUIYUANWENJIANZHIXINGFUDAIBANGDINGDEYUBIANYISHICHUANGJIAN,HUOZHETONGGUOBANGDINGBANGDINGWENJIANCHUANGJIAN。

荣耀彩票代理SHUJUKUYINGYONGCHENGXUSHIYONGCHENGXUBAODEYUANYIN:TIGAOXINGNENGHEJINCOUXING。TONGGUOYUBIANYISQLYUJU,SHIDESQLYUJUZAICHUANGJIANYINGYONGCHENGXUSHIBEIBIANYIJINCHENGXUBAO,ERBUSHIZAIYUNXINGSHI。MEIYITIAOYUJUDOUBEIFENXI,GAOXIAOLVDECAOZUOSHUCHUANCUNCHUZAICHENGXUBAOZHONG。ZAIYUNXINGSHI,YUBIANYIQICHANSHENGDEDAIMADIAOYONGSHUJUKUGUANLIQIYUNXINGSHIFUWUAPIs,GENJUSHURUSHUCHUSHUJUDEBIANLIANGXINXI,ZHIXINGCHENGXUBAO。

荣耀彩票代理YUBIANYIDEYOUDIANJINJINDUIJINGTAISQLYUJUYOUXIAO。DONGTAIZHIXINGDESQLYUJUBUYONGYUBIANYI,DANSHITAMENZAIXUYAOZAIYUNXINGSHIWANCHENGCHULIDEZHENGGEBUZHOU。ZHUYI:BUYAORENWEIYITIAOSQLYUJUDEJINGTAIBANBENDEZHIXINGXIAOLVYIDINGBIQIDONGTAIBANBENDEGAO。ZAIMOUGEFANGMIAN,JINGTAISQLYUJUKUAISHIYINWEIBUXUYAODONGTAIYUJUDEZHUNBEIKAIXIAO。ZAILINGYIFANGMIAN,TONGYANGDEYUJU,DONGTAIZHIXINGHUIKUAIXIE,SHIYINWEIYOUHUAQINENGLIYONGDANGQIANSHUJUKUDETONGJIXINXI,ERBUSHIYIQIANDETONGJIXINXI 。YOUGUANJINGTAISQLYUDONGTAISQLDEBIJIAO,CANZHAOXIABIAO:

BIAO JINGTAISQLYUDONGTAISQLDEBIJIAO

KAOLVYINSU ZUIHAODEXUANZE

ZHIXINGSQLYUJUDESHIJIAN:

SHAOYULIANGMIAO JINGTAI

2 DAO10 MIAO LIANGZHEJUNKE

DUOYU10MIAO DONGTAI

SHUJUYIZHIXING

TONGYIDESHUJUFENBU JINGTAI

荣耀彩票代理QINGWEIBUTONGYIFENBU LIANGZHEJUNKE

GAODUBUTONGYIFENBU DONGTAI

FANWEIWEICI(<,>,BETWEEN,LIKE)SHIYONG

HENSHAOSHIYONG JINGTAI

OURANSHIYONG LIANGZHEJUNKE

JINGCHANGSHIYONG DONGTAI

ZHIXINGDEZHONGFUXING

HENDUOCI(10HUOZHEGENGDUO) LIANGZHEJUNKE

JICI(SHAOYU10CI) LIANGZHEJUNKE

YICI JINGTAI

CHAXUNDEZHONGLEI

SUIJI DONGTAI

GUDING LIANGZHEJUNKE

YUNXINGSHIHUANJING(DML/DDL)

SHIWUCHULI(DML Only) LIANGZHEJUNKE

荣耀彩票代理HUNHE(DMLHEDDL – DDL affects packages) DONGTAI

荣耀彩票代理HUNHE(DMLHEDDL – DDL does not affect packages) LIANGZHEJUNKE

荣耀彩票代理YUNXINGrunstatsDEPINDU

HENDI JINGTAI

ZHENGCHANG LIANGZHEJUNKE

PINFAN DONGTAI

1.2.7 定界符

荣耀彩票代理RUGUOYUANCHENGXUZHONGYOUSQLYUJU,YUANCHENGXUBUNENGLIJIBEIYUANYUYANBIANYIQICHULI。TASHOUXIANYAOJINGGUOYIGEFANYIGUOCHENG,JIANGSQLYUJUFANYICHENGYUANYUYANBIANYIQINENGGOULIJIEDEDONGXI。ZUOFANYIGONGZUODECHENGXUJIUJIAOZUOYUBIANYIQI。YUBIANYIQISHIBIESQLYUJUDEFANGFASHITONGGUODINGJIEFUJIANGSQLYUJUBIAOSHICHULAI。

荣耀彩票代理DINGJIEFUDEZUOYONGSHISHIYUBIANYIQINENGGOUSHIBIECHUXUBEIFANYIDESQLYUJU,BINGQIEBIXUBIAOSHICHUMEIYITIAOQIANRUDESQLYUJU。BUTONGDESUZHUYUYANSHIYONGBUTONGDEDINGJIEFU,XIABIAOLIECHULESIZHONGCHANGYONGYUYANDEDINGJIEFU:

YUYAN

DINGJIEFU

C;C++

EXEC SQL

sql statement ;

COBOL

EXEC SQL

荣耀彩票代理sql statement END-EXEC.

FORTRAN

EXEC SQL

sql statement

JAVA

荣耀彩票代理#sqlj {sql statement} ;

LIZI:

l SQLYUJU

荣耀彩票代理UPDATE TEMPL

荣耀彩票代理SET WORKDEPT = ‘C02’

WHERE WORKDEPT = ‘C01’

l ZAICCHENGXUZHONGDESQLYUJU

EXEC SQL

UPDATE TEMPL

荣耀彩票代理SET WORKDEPT = ‘C02’

荣耀彩票代理WHERE WORKDEPT = ‘C01’ ;

l ZAICOBOLCHENGXUZHONGDESQLYUJU

EXEC SQL

UPDATE TEMPL

荣耀彩票代理SET WORKDEPT = ‘C02’

WHERE WORKDEPT = ‘C01’

END-EXEC.

l ZAIJavaCHENGXUZHONGDESQLYUJU

#sqlj {UPDATE TEMPL SET WORKDEPT = ‘C02’ WHERE WORKDEPT = ‘C01’} ;

第二章DB2应用程序设计方法

荣耀彩票代理BENZHANGJIESHAODB2YINGYONGCHENGXUSHEJIDEYIBANFANGFA,YIJIRUHESHEZHICESHIHUANJING。

2.1编程方法

2.1.1 访问数据

ZAIGUANXISHUJUKUZHONG,BIXUSHIYONGSQLFANGWENQINGQIUDESHUJU,DANSHIKEYIXUANZERUHEJIANGSQLJIEHEDAOYINGYONGCHENGXUZHONGQU。KEYICONGXIABIAOLIECHUDEJIEKOUHETAMENZHICHIDEYUYANZHONGXUANZE:

JIEKOU

ZHICHIDEYUYAN

QIANRUSQL

C/C++荣耀彩票代理, COBOL, FORTRAN, Java (SQLJ), REXX

DB2 CLI HEODBC

荣耀彩票代理C/C++, Java (JDBC)

荣耀彩票代理Microsoft Specifications, including ADO, RDO, and OLE DB

Visual Basic, Visual C++

Perl DBI

Perl

Query Products

荣耀彩票代理Lotus Approach, IBM Query Management Facility

YI、QIANRUSQL

QIANRUSQLYOUQIYOUSHI,TAKEYIBAOHANJINGTAISQLHUOZHEDONGTAISQL,HUOZHELIANGZHONGLEIXINGHUNHESHIYONG。RUGUOZAIKAIFAYINGYONGCHENGXUSHI,SQLYUJUDEGESHIHENEIRONGYIJINGQUEDING,YINGGAIKAOLVZAICHENGXUZHONGCAIYONGQIANRUDEJINGTAISQL。LIYONGJINGTAISQL,ZHIXINGYINGYONGCHENGXUDERENZANSHIJICHENGJIANGYINGYONGCHENGXUKUNBANGDAOSHUJUKUZHONGDEYONGHUDEQUANXIAN,ERBUXUYAODUICIRENFUYUQITAQUANXIAN(CHULEYINGYONGCHENGXUDEZHIXINGQUAN)。DONGTAISQLDEZHIXINGXUYAOZHIXINGYINGYONGCHENGXUDERENDEQUANXIAN,DANYEYOULIWAIQINGKUANG,JIUSHIZAIKUNBANGYINGYONGCHENGXUDESHIHOUSHIYONGDYNAMICRULES BINDXUANXIANG。YIBANLAIJIANG,RUGUOZHIDAOZHIXINGSHICAINENGQUEDINGSQLYUJU,NEIMEYINGGAICAIYONGQIANRUDEDONGTAISQL。ZHEIBIJIAOANQUAN,ERQIEKEYICHULIGENGDUOXINGSHIDESQL。

ZHUYI:JAVAYUYANDEQIANRUSQL(SQLJ)YINGYONGCHENGXUZHINENGQIANRUJINGTAISQLYUJU。RANER,ZAISQLJYINGYONGCHENGXUZHONG,KEYITONGGUOSHIYONGJDBCDIAOYONGDONGTAISQLYUJU。

ZAISHIYONGBIANCHENGYUYANBIANYIQIQIAN,BIXUDUIYUANWENJIANJINXINGYUBIANYI,JIANGQIANRUDESQLYUJUZHUANHUANWEISUZHUYUYANDESHUJUKUFUWUAPIs。ZAIYINGYONGCHENGXUYUNXINGZHIQIAN,BIXUJIANGCHENGXUZHONGDESQLKUNBANGDAOSHUJUKULI。

WOMENZAIDISANZHANG“JINGTAISQLYINGYONGBIANCHENG”ZHONGYOUXIANGXIJIESHAO。

ER、DB2 CLIHEODBC

荣耀彩票代理DB2DIAOYONGJIJIEKOU(DB2 CLI)SHIIBMGONGSISHUJUKUFUWUQIDEDB2XILIEKEDIAOYONGSQLJIEKOU,TASHIYIGEGUANXISHUJUKUSHUJUFANGWENDECHEC++YUYANBIANCHENGJIEKOU,TAYONGHANSHUDIAOYONGDEFANGSHI,JIANGDONGTAISQLYUJUZUOWEICANSHUCHUANDIJISHUJUKUGUANLIQI。YEJIUSHISHUO,YIGEKEDIAOYONGDESQLJIEKOUJIUSHIYIGEDIAOYONGDONGTAISQLYUJUDEYINGYONGCHENGXUBIANCHENGJIEKOU(API)。CLIKEYITIDAIQIANRUDONGTAISQL,DANSHIYUQIANRUSQLBUTONG,TABUXUYAOYUBIANYIHUOZHEKUNBANG。

DB2 CLISHIJIYUWEIRUANKAIFANGSHUJUKULIANJIE(ODBC)GUIFANHEX/OpenGUIFANKAIFADE。IBMXUANZEZHEIXIEGUIFANSHIWEILEZUNXUNYEJIEBIAOZHUN,SHISHUXIZHEIXIESHUJUKUJIEKOUDEYINGYONGCHENGXUKAIFARENYUANNENGZAIDUANQINEIZHANGWOCLIDEKAIFAFANGFA。

JDBC:

荣耀彩票代理DB2ZAIJavaYUYANFANGMIANDEZHICHIBAOKUOJDBC,JDBCSHIYIGEYUCHANGSHANGWUGUANDEDONGTAISQLJIEKOU,LIYONGTASHIDEYINGYONGCHENGXUKEYITONGGUOBIAOZHUNJavaFANGFADIAOYONGSHIXIANSHUJUDEFANGWEN。

JDBCYUDB2 CLIYIYANGBUXUYAOZUOYUBIANYIHUOZHEKUNBANG,ZUOWEIYIGEYUCHANGSHANGWUGUANDEBIAOZHUN,JDBCYINGYONGCHENGXUJUYOULIANGHAODEYIZHIXING。YONGJDBCKAIFADEYINGYONGCHENGXUZHICAIYONGDONGTAISQL。

SAN、WEIRUANGUIFAN

KAIFAFUHE“SHUJUCUNQUDUIXIANG (DAO) ”HE“YUANCHENGSHUJUDUIXIANG (RDO) ” GUIFANDE Microsoft Visual Basic HE Visual C++ YINGYONGCHENGXU,YIJISHIYONG“DUIXIANGLIANJIEHEQIANRUSHUJUKU (OLE DB) QIAOJIE”DE“ActiveX SHUJUDUIXIANG (ADO) ”YINGYONGCHENGXU。

SI、PerlSHUJUKUJIEKOU

DB2ZHICHIPerlSHUJUKUJIEKOU(DBI)SHUJUFANGWENGUIFAN,SHIYONGDBD::DB2QUDONGCHENGXU。DBD::DB2QUDONGCHENGXUZHICHIXIALIEPINGTAI:

AIX

Operating Systems

荣耀彩票代理Version 4.1.4 and later

荣耀彩票代理C Compilers

IBM C for AIX Version 3.1 and later

HP-UX

荣耀彩票代理Operating Systems

荣耀彩票代理HP-UX Version 10.10 with Patch Levels: PHCO_6134, PHKL_5837,

荣耀彩票代理PHKL_6133, PHKL_6189, PHKL_6273, PHSS_5956

荣耀彩票代理HP-UX Version 10.20

荣耀彩票代理HP-UX Version 11

C Compilers

HP C/HP-UX Version A.10.32

荣耀彩票代理HP C Compiler Version A.11.00.00 (for HP-UX Version 11)

Linux

Operating Systems

荣耀彩票代理Linux Redhat Version 5.1 with kernel 2.0.35 and glibc version 2.0.7

荣耀彩票代理C Compilers

荣耀彩票代理gcc version 2.7.2.3 or later

Solaris

Operating Systems

Solaris Version 2.5.1

荣耀彩票代理Solaris Version 2.6

荣耀彩票代理C Compilers

荣耀彩票代理SPARCompiler C Version 4.2

Windows NT

Operating Systems

Microsoft Windows NT version 4 or later

荣耀彩票代理C Compilers

荣耀彩票代理Microsoft Visual C++ Version 5.0 or later

荣耀彩票代理CONGDB2TONGYONGSHUJUPerl DBIWANGYE(http://www.software.ibm.com/data/db2/perl/)SHANGKEYIXIAZAIZUIXINDEDBD::DB2QUDONGCHENGXUDEZUIXINBANBENYIJIGENGDUOXINXI。

WU、CHAXUNGONGJUCHANPIN

CHAXUNGONGJUCHANPINBAOKUOIBMCHAXUNGUANLIGONGJU(QMF)HELotus Notes,TAMENZHICHICHAXUNKAIFAHEBAOBIAO。

2.1.2 数据值控制

YINGYONGCHENGXUDEBUFENLUOJISHITONGGUOKONGZHISHUJUKUZHONGYUNXUDEZHIDEDAOSHISHIHEBAOHUSHUJUWANZHENGDE。DB2TIGONGLEJIGEBUTONGDEFANGFA。

YI、SHUJULEIXING

SHUJUKUJIANGMEIYIGESHUJUYUANSUCUNCHUZAIMOUGEBIAODELIEZHONG,BINGQIEMEIYILIEDOUYONGYIGESHUJULEIXINGLAIDINGYI,SHUJULEIXINGZENGJIALECILIEFANGZHISHUJUDEXIANZHI。LIRU,ZHENGSHULEIXINGBIXUSHIZAIMOUGEGUDINGFANWEINEIDESHUZI。ZAISQLYUJUZHONGSHIYONGLIEYEBIXUFUHEYIDINGDEXINGWEI,LIRU,SHUJUKUBUNENGJIANGYIGEZHENGSHUYUYIGEZIFUCHUANBIJIAO。DB2YOUYIZUNEIZHIDESHUJULEIXING,DINGYILETEXINGHEXINGWEI。DB2ZHICHIYONGHUDINGYISHUJULEIXING,JIAOZUOUDT,TAMENSHIJIYUNEIZHIDESHUJULEIXINGDINGYIDE。

ER、WEIYIXINGYUESHU

荣耀彩票代理WEIYIXINGYUESHUFANGZHIZAIYIGEBIAOZHONG,ZAIYILIEHUODUOLIESHANGCHUXIANZHONGFUDEZHI。WEIYIZHUGUANJIANZIYESHIWEIYIXINGYUESHU。LIRU,ZAIBIAODEPARTMENTDEDEPTNOLIESHANGDINGYIYIGEWEIYIXINGYUESHU,FANGZHIJIANGXIANGTONGDEBUMENHAOFENPEIJILIANGGEBUMEN。

荣耀彩票代理RUGUODUISUOYOUSHIYONGTONGYIGEBIAOLIDESHUJUDEYINGYONGCHENGXUDOUYAOZHIXINGYIGEWEIYIXINGGUIZE,YINGDANGCAIYONGWEIYIXINGYUESHU。

SAN、BIAOJIANCHAYUESHU

BIAOJIANCHAYUESHU(Table Check Constraint)XIANZHIZAIBIAOZHONGMOULIECHUXIANDEZHIDEFANWEI。

SI、CANKAOWANZHENGXINGYUESHU

荣耀彩票代理TONGGUODINGYIWEIYIYUESHUHEWAIBUGUANJIANZI,KEYIDINGYIBIAOYUBIAOZHIJIANDEGUANXI,CONGERSHISHIMOUXIE SHANGYEGUIZE。WEIYIGUANJIANHEWAIBUGUANJIANZIYUESHUDEZUHETONGCHANGCHENGWEICANKAOWANZHENGXINGYUESHU。 WAIBUGUANJIANZISUOYINYONGDEWEIYIYUESHUCHENGWEIFUGUANJIANZI。 WAIBUGUANJIANZIBIAOSHITEDINGDEFUGUANJIANZI,HUOYUTEDINGDEFUGUANJIANZIXIANGGUAN。 LIRU,MOUGUIZEKENENGGUIDINGMEIGEGUYUAN(EMPLOYEE BIAO)BIXUSHUYUMOUXIANCUNDEBUMEN (DEPARTMENT BIAO)。YINCI,JIANG EMPLOYEE BIAOZHONGDE“BUMENHAO”DINGYIWEIWAIBUGUANJIANZI,ERJIANG DEPARTMENT BIAOZHONGDE“BUMENHAO”DINGYIWEIZHUGUANJIANZI。XIALIETUBIAOTIGONGCANKAOWANZHENGXING YUESHUDEZHIGUANSHUOMING。

TU WAIBUYUESHUHEZHUYUESHUDINGYIGUANXIBINGBAOHUSHUJU

clip_image004

2.1.3 数据关系控制

YINGYONGCHENGXULUOJIDELINGWAIYIGEZHUYAORENWUSHIGUANLIXITONGZHONGBUTONGSHITIZHIJIANDEGUANXI。LIRU,RUGUOZENGJIAYIGEXINDEBUMEN,JIUYAOCHUANGJIANYIGEXINDEZHANGHAO。DB2TIGONGLEGUANLISHUJUKUZHONGBUTONGSHITIZHIJIANDEGUANLIDELIANGZHONGFANGFA:CANKAOWANZHENGXINGYUESHUHECHUFAQI。

YI、CANKAOWANZHENGXINGYUESHU

WOMENZAIQIANMIANYIJINGJIESHAOGUO。

ER、CHUFAQI

荣耀彩票代理YIGECHUFAQIDINGYIYIZUCAOZUO,ZHEIZUCAOZUOTONGGUOXIUGAIZHIDINGJIBIAO ZHONGSHUJUDECAOZUOLAIJIHUO。

荣耀彩票代理KESHIYONGCHUFAQILAIZHIXINGDUISHURUSHUJUDEYANZHENG;ZIDONGSHENGCHENGXINCHARUXINGDEZHI; WEILEJIAOCHAYINYONGERDUQUQITABIAO;WEILESHENCHAGENZONGERXIERUQITABIAO; HUOTONGGUODIANZIYOUJIANXINXIZHICHIJINGBAO。 SHIYONGCHUFAQIJIANGDAOZHIYINGYONGCHENGXUKAIFAJISHANGYEGUIZEDEQUANMIANSHISHIGENGKUAISUBINGQIEYINGYONGCHENGXU HESHUJUDEWEIHUGENGRONGYI。

荣耀彩票代理DB2 TONGYONGSHUJUKUZHICHIJIZHONGLEIXINGDECHUFAQI。 KEDINGYICHUFAQIZAI DELETE、INSERT HUO UPDATE CAOZUOZHIQIANHUOZHIHOUJIHUO。 MEIGECHUFAQIBAOKUOYIZUCHENGWEICHUFACAOZUODE SQL YUJU, ZHEIZUYUJUKEBAOKUOYIGEKEXUANDESOUSUOTIAOJIAN。

荣耀彩票代理KEJINYIBUDINGYIHOUCHUFAQIYIDUIMEIYIXINGDOUZHIXINGCHUFACAOZUO, HUODUIYUJUZHIXINGYICICHUFACAOZUO,ERQIANCHUFAQIZONGSHI DUIMEIYIXINGDOUZHIXINGCHUFACAOZUO。

ZAI INSERT、UPDATE HUO DELETE YUJUZHIQIANSHIYONGCHUFAQI,YIBIANZAIZHIXINGCHUFACAOZUOZHIQIAN JIANCHAMOUXIETIAOJIAN,HUOZAIJIANGSHURUZHICUNCHUZAIBIAOZHONGZHIQIANGENGGAISHURUZHI。 SHIYONGHOUCHUFAQI,YIBIANZAIBIYAOSHICHUANBOZHIHUOZHIXINGQITARENWU,RUFASONGXINXIDENG,ZHEIXIERENWUKENENGSHICHUFAQICAOZUOSUOYAOQIUDE。

2.1.4 服务器上的应用逻辑

DB2TIGONGLEJIANGYINGYONGCHENGXUCHENGXUDEBUFENZAISHUJUKUFUWUQISHANGYUNXINGDEGONGNENG,TONGCHANGSHIWEILETIGAOXINGNENGHEZHICHIGONGGONGGONGNENG。ZHUYAOFANGFAYOU:CUNCHUGUOCHENG,UDF,CHUFAQI。

2.1.5 构造SQL语句的原型

荣耀彩票代理DANGSHEJIHEBIANXIEYINGYONGCHENGXUSHI,LIYONGSHUJUKUGUANLIQIDETEXINGHEYIXIEGONGJULAIGOUZAOSQLYUJUDEYUANXING,TIGAOZHIXINGXINGNENG。KEYIANZHAOXIAMIANDEFANGFAYOUHUASQLYUJU:

1.ZAIYUBIANYIYIGEWANZHENGDECHENGXUZHIQIAN,YONGMINGLINGXINGCHULIQI(CLP)CESHIQIZHONGDESQL(KENENGBUSHIQUANBU)。

荣耀彩票代理2.YONGJIESHISHESHIGUSUANCHENGXUZHONGDEDELETE, INSERT, UPDATE, HE SELECTYUJUDEKAIXIAOHEHUOQUFANGWENCELVE。GENJUJIESHISHESHIDESHUCHU,GAIXIESQLYUJUHUOZHEZENGJIASHUJUKUDUIXIANG(RUSUOYIN)HUOZHEDIAOJIESHUJUKUFUWUQIDECANSHU 。

2.2 设置测试环境

荣耀彩票代理ZAIJINXINGDB2YINGYONGKAIFASHI,XUYAOJIANLICESHIHUANJING。YIGECESHIHUANJING,YINGGAIBAOKUO:

荣耀彩票代理1. YIGECESHISHUJUKU. RUGUOYINGYONGCHENGXUYAOGENGXIN,CHARUHUOZHESHANCHULAIZIBIAOHESHITUDESHUJU,NEIMESHIYONGCESHISHUJUJIANCHAZHIXINGQINGKUANG;RUGUOJINJINCONGBIAOHESHITUZHONGTIQUSHUJU,KEYIKAOLVSHIYONGSHENGCHANSHUJU.

2. CESHIDESHURUSHUJU. YONGLAICESHIYINGYONGCHENGXUDECESHISHUJUYINGGAISHIYOUXIAODE,NENGTIXIANSUOYOUKENENGSHURUQINGKUANG. YEYAOYONGWUXIAODESHUJUQUCESHI,KANYINGYONGCHENGXUNENGFOUBIANBIECHU.

2.2.1 创建测试数据库

荣耀彩票代理KEYISHIYONGCLPFACHUCreate Database dbnameYUJUCHUANGJIANCESHISHUJUKU,YEKEYISHIYONGSHUJUKUGUANLIQIAPIBIANXIEYIGECHENGXULAICHUANGJIANCESHISHUJUKU.

2.2.2 创建测试表

荣耀彩票代理XIANFENXIYINGYONGCHENGXUDESHUJUXUQIU,RANHOUSHIYONGCREATE TABLEYUJUCHUANGJIANCESHIBIAO.

2.2.3 生成测试数据

SHIYONGXIAMIANRENHEYIZHONGFANGFAJIANGSHUJUCHARUBIAOZHONG:

荣耀彩票代理l INSERT...VALUES (an SQL statement) MEICIKEYICHARUYIXINGHUODUOXINGSHUJU

l INSERT...SELECT CONGYIGEYICUNZAIDEBIAOTIQUSHUJU (JIYUYIGESELECTTIAOKUAN),BINGFANGRUINSERTYUJUBIAOSHIDEBIAOZHONG.

l YONGIMPORTHELOADGONGJU CONGDINGYIDESHUJUYUANCHARUDALIANGDESHUJU

l YONGRESTOREGONGJUCONGMOUGESHUJUKUDEBEIFEN,JIANGSHUJUHAIYUANDAOTEDINGDECESHISHUJUKU

第三章 静态SQL应用编程

JINGTAISQLYUJU,SHIZHIQIANRUZAISUZHUYUYANZHONGDESQLYUJUZAIYUBIANYISHIWANQUANZHIDAO。ZHEISHIXIANGDUIYUDONGTAISQLERYANDE,DONGTAISQLYUJUDEYUFAZAIYUNXINGSHICAIZHIDAO。ZHUYI:JIESHIYUYANZHONGBUZHICHIJINGTAISQLYUJU,LIRUREXXZHIZHICHIDONGTAISQLYUJU。

荣耀彩票代理YITIAOSQLYUJUDEJIEGOUZAIYUBIANYISHIWANQUANQINGCHU,CAIBEIRENWEISHIJINGTAIDE。LIRU,YUJUZHONGSHEJIDAODEBIAO(TABLES)HELIEDEMINGZI,ZAIYUBIANYISHI,BIXUWANQUANZHIDAO,ZHINENGZAIYUNXINGSHIZHIDINGDESHIYUJUZHONGYINYONGDESUZHUBIANLIANGDEZHI。RANER,SUZHUBIANLIANGDEXINXI,RUSHUJULEIXING,YEBIXUZAIYUBIANYISHIQUEDING。

DANGJINGTAISQLYUJUBEIZHUNBEISHI,SQLYUJUDEKEZHIXINGXINGSHIBEICHUANGJIAN,CUNCHUZAISHUJUKUZHONGDECHENGXUBAOLI。SQLYUJUDEKEZHIXINGXINGSHIKEYIZAIYUBIANYISHICHUANGJIANHUOZHEZAIKUNBANGSHICHUANGJIAN。BULUNNAZHONGQINGKUANG,SQLYUJUDEZHUNBEIGUOCHENGDOUFASHENGZAIYUNXINGZHIQIAN。KUNBANGYINGYONGCHENGXUDERENXUYAOYOUYIDINGDEQUANXIAN,SHUJUKUGUANLIQIZHONGDEYOUHUAQIHAIHUIGENJUSHUJUKUDETONGJIXINXIHEPEIZHICANSHUDUISQLYUJUJINXINGYOUHUA。DUIJINGTAISQLYUJULAISHUO,YINGYONGCHENGXUYUNXINGSHI,BUHUIBEIYOUHUA。

3.1 静态SQL程序的结构和特点

3.1.1 例程

荣耀彩票代理XIAMIANXIANLAIKANYIGEJINGTAISQLCHENGXUDECYUYANLIZI。ZHEIGELICHENGYANSHILEJINGTAISQLYUJUDESHIYONG,TAJIANGBIAOZHONGLASTNAMELIEDENGYU‘JOHNSON’DEJILUDEFIRSTNMELIEDEZHISHUCHU,FOUZEDAYINCUOWUXINXI。

/******************************************************************************

**

** Source File Name = static.sqc 1.4

**

荣耀彩票代理** Licensed Materials - Property of IBM

**

荣耀彩票代理*******************************************************************************/

#include

#include

#include

#include "util.h"

#ifdef DB268K

荣耀彩票代理/* Need to include ASLM for 68K applications */

#include

#endif

荣耀彩票代理EXEC SQL INCLUDE SQLCA; /* :rk.1:erk. */

#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;

int main(int argc, char *argv[]) {

EXEC SQL BEGIN DECLARE SECTION; /* :rk.2:erk. */

char firstname[13];

char userid[9];

char passwd[19];

EXEC SQL END DECLARE SECTION;

荣耀彩票代理#ifdef DB268K

荣耀彩票代理/* Before making any API calls for 68K environment,

need to initial the Library Manager */

InitLibraryManager(0,kCurrentZone,kNormalMemory);

atexit(CleanupLibraryManager);

#endif

printf( "Sample C program: STATIC/n" );

if (argc == 1) {

荣耀彩票代理EXEC SQL CONNECT TO sample;

CHECKERR ("CONNECT TO SAMPLE");

}

else if (argc == 3) {

strcpy (userid, argv[1]);

strcpy (passwd, argv[2]);

EXEC SQL CONNECT TO sample USER :userid USING :passwd; /* :rk.3:erk. */

CHECKERR ("CONNECT TO SAMPLE");

}

else {

printf ("/nUSAGE: static [userid passwd]/n/n");

return 1;

荣耀彩票代理} /* endif */

EXEC SQL SELECT FIRSTNME INTO :firstname /* :rk.4:erk. */

FROM employee

荣耀彩票代理WHERE LASTNAME = 'JOHNSON';

CHECKERR ("SELECT statement")荣耀彩票代理; /* :rk.5:erk. */

printf( "First name = %s/n", firstname );

EXEC SQL CONNECT RESET; /* :rk.6:erk. */

CHECKERR ("CONNECT RESET");

return 0;

}

荣耀彩票代理/* end of program : static.sqc */

ZHEIGELICHENGZHONGSHIXIANLEYIGEXUANZEZHIDUOYIXING(JIDANXING)DECHAXUN,ZHEIYANGDECHAXUNKEYITONGGUOYITIAOSELECT INTOYUJULAIZHIXING。SELECT INTO YUJUCONGSHUJUKUZHONGDEBIAOXUANZEYIXINGSHUJU,RANHOUJIANGZHEIXINGSHUJUDEZHIFUYUYUJUZHONGZHIDINGDESUZHUBIANLIANG(XIAJIEJIANGYAOTAOLUNSUZHUBIANLIANG)。LIRU,XIAMIANDEYUJUJIANGXINGWEI‘HAAS’DEGUYUANDEGONGZIFUYUSUZHUBIANLIANGempsal:

SELECT SALARY

INTO :empsal

FROM EMPLOYEE

荣耀彩票代理WHERE LASTNAME='HAAS'

YITIAOSELECT INTOYUJUBIXUZHINENGFANHUIYIXINGHUOZHELINGXING。RUGUOJIEGUOJIYOUDUOYUYIXING,JIUHUICHANSHENGYIGECUOWU(SQLCODE –811,SQLSTATE 21000)。RUGUOCHAXUNDEJIEGUOJIZHONGYOUDUOXING,JIUXUYAOYOUBIAO(CURSOR)LAICHULIZHEIXIEXING。ZAIJIE3.2.3ZHONGJIESHAORUHESHIYONGYOUBIAO。

JINGTAICHENGXUSHIRUHEGONGZUODENI?

荣耀彩票代理1.BAOKUOJIEGOUSQLCA。 INCLUDE SQLCAYUJUDINGYIHESHENGMINGLESQLCAJIEGOU,SQLCAJIEGOUZHONGDINGYILESQLCODEHESQLSTATEYU。SHUJUKUGUANLIQIZAIZHIXINGWANMEIYITIAOSQLYUJUHUOZHEMEIYIGESHUJUKUGUANLIQIAPIDIAOYONG,DOUYAOGENGXINSQLCAJIEGOUZHONGDESQLCODEYUDEZHENDUANXINXI。

2.SHENGMINGSUZHUBIANLIANG。SQL BEGIN DECLARE SECTIONHEEND DECLARE SECTION YUJUJIEDINGSUZHUBIANLIANGDESHENGMING。

荣耀彩票代理YOUXIEBIANLIANGZAISQLYUJUZHONGBEIYINYONG。SUZHUBIANLIANGYONGLAIJIANGSHUJUCHUANDIJISHUJUKUGUANLIHUOZHEJIESHOUSHUJUKUGUANLIQIFANHUIDESHUJU。ZAISQLYUJUZHONGYINYONGSUZHUBIANLIANGSHI,BIXUZAISUZHUBIANLIANGQIANJIAQIANZHUIMAOHAO(:)。XIANGXIXINXIKANXIAJIE。

荣耀彩票代理3.LIANJIEDAOSHUJUKU。YINGYONGCHENGXUBIXUXIANLIANJIEDAOSHUJUKU,CAINENGDUISHUJUKUJINXINGCAOZUO。ZHEIGECHENGXULIANJIEDAOSAMPLESHUJUKU,QINGQIUYIGONGXIANGFANGSHIFANGWEN。QITAYINGYONGCHENGXUYEKEYITONGSHIYIGONGXIANGFANGWENFANGSHILIANJIESHUJUKU

4.TIQUSHUJU。SELECT INTOYUJUJIYUYIGECHAXUNTIQULEYIXINGZHI。ZHEIGELIZICONGEMPLOYEEBIAOZHONG,JIANGLASTNAMELIEDEZHIWEIJOHNSONDEXIANGYINGXINGDEFISRTNMELIEDEZHITIQUCHULAI,ZHIYUSUZHUBIANLIANG firstnameZHONG。

5.CHULICUOWU。CHECKERR HONG/HANSHUSHIYIGEZHIXINGCUOWUJIANCHADEWAIBUHANSHU。

3.1.2 创建应用程序

CHUANGJIANYINGYONGCHENGXUDEZHENGGEGUOCHENGRUTUSUOSHI:

clip_image005

3.1.3 静态SQL的特点

JINGTAISQLBIANCHENGBIDONGTAISQLBIANCHENGJIANDANXIE. JINGTAISQLYUJUQIANRUSUZHUYUYANYUANWENJIANZHONG,YUBIANYIQIJIANGSQLYUJUZHUANHUANWEISUZHUYUYANBIANYIQINENGGOUCHULIDESHUJUKUYUNXINGSHIFUWUAPIDIAOYONG。

荣耀彩票代理YINWEIZAIKUNBANGYINGYONGCHENGXUSHI,ZUOKUNBANGDERENXUYAOYOUYIDINGDESHOUQUAN,YINCIZUIZHONGYONGHUBUXUYAOZHIXINGCHENGXUBAOLIDEYUJUDEZHIJIEQUANXIAN。LIRU,YIGEYINGYONGCHENGXUKEYIZHIYUNXUMOUGEYONGHUGENGXINYIGEBIAODEBUFENSHUJU ,ERBUYONGJIANGGENGXINZHENGGEBIAODEQUANLIJIYUZHEIGEYONGHU。ZHEIGEGONGNENGTONGGUOXIANZHIQIANRUDEJINGTAISQLYUJUZHINENGGENGXINBIAOZHONGDEMOUXIELIEHUOZHEYIDINGFANWEINEIDEZHI,ZHIJIANGCHENGXUBAODEZHIXINGQUANXIANJIYUZHEIGEYONGHU。

荣耀彩票代理JINGTAISQLYUJUSHICHIJIUWENGUDE,DONGTAISQLYUJUZHISHIBEIHUANCUN,ZHIDAOBIANWEIWUXIAO、YINWEIKONGJIANGUANLIYUANYINBEIQINGLIHUOZHESHUJUKUBEIGUANBI。RUGUOXUYAO,DANGBEIHUANCUNDEYUJUBIANWEIWUXIAOSHI,DB2 SQLBIANYIQIYINXINGDIZHONGXINBIANYIDONGTAISQLYUJU。

荣耀彩票代理JINGTAISQLYUJUDEZHUYAOYOUDIANSHIJINGTAISQLZAISHUJUKUGUANBIHOURENGRANCUNZAI,ERDONGTAISQLYUJUZAISHUJUKUGUANBIHOUJIUBEIQINGCHULE。LINGWAI,JINGTAISQLZAIYUNXINGSHIBUXUYAODB2 SQLBIANYIQILAIBIANYI,XIANGFAN,DONGTAISQLYUJUXUYAOZAIYUNXINGSHIBIANYI(LIRU,SHIYONGPREPAREYUJU)。YINWEIDB2HUANCUNDONGTAISQLYUJU,ZHEIXIEYUJUYEBUZONGSHIXUYAODB2BIANYI。DANSHI,MEIYICIYUNXINGCHENGXUZHISHAOXUYAOBIANYIYICI。

JINGTAISQLYOUXINGNENGSHANGDEYOUSHI。DUIJIANDAN、YUNXINGSHIJIANDUANDESQLCHENGXU,JINGTAISQLYUJU BIXIANGTONGMUDEDEDONGTAISQLYUJUZHIXINGDEKUAI。YINWEIJINGTAISQLYUJUZHUNBEIZHIXINGXINGSHIDEKAIXIAOZAIYUBIANYISHIJIAN,ERBUSHIZAIYUNXINGSHI。

荣耀彩票代理ZHUYI:JINGTAISQLYUJUDEXINGNENGJUEDINGYUYINGYONGCHENGXUZUIHOUYICIBEIKUNBANGSHISHUJUKUDETONGJIXINXI。 RANER,RUGUOZHEIXIETONGJIXINXIGAIBIANLE,NEIMEBIJIAOQILAI,DENGXIAODEDONGTAISQLYUJUDEXINGNENGKENENGHAOXIE。ZAIMOUGESHIYONGJINGTAISQLDEYINGYONGCHENGXUKUNBANGZHIHOU,SHUJUKUZENGJIALEYIGESUOYIN,RUGUOZHEIGEYINGYONGCHENGXUBUZHONGXINKUNBANG,JIUBUNENGLIYONGZHEIGESUOYIN。HAIYOU,RUGUOZAIJINGTAISQLYUJUZHONGSHIYONGSUZHUBIANLIANG,YOUHUAQIYEBUNENGSHIYONGBIAODEFENBUXINXILAIYOUHUASQLYUJU。

3.2 宿主变量和指示符变量的应用

3.2.1 宿主变量的声明

荣耀彩票代理SUZHUBIANLIANG(Host variables) ZAIZHUYINGYONGCHENGXUZHONGYOUQIANRUSHISQLYUJUYINYONGDEBIANLIANG。SUZHUBIANLIANGSHIGAIYINGYONGCHENGXUZHONGDECHENGXUSHEJIBIANLIANG,BINGQIESHIZAISHUJUKUZHONGDEBIAOYUYINGYONGCHENGXUGONGZUOQUZHIJIANCHUANSONGSHUJUDEZHUYAOJIZHI。WOMENCHENGZHIWEI“SUZHUBIANLIANG”,SHIWEILEYUTONGCHANGFANGFASHENGMINGDEYUANYUYANBIANLIANGQUFENKAILAI,TONGCHANGFANGFASHENGMINGDEBIANLIANGBUNENGBEISQLYUJUYINYONG。SUZHUBIANLIANGZAISUZHUYUYANCHENGXUMOKUAIZHONGYIYIZHONGTESHUDEFANGSHISHENGMING:BIXUZAIBEGIN DECLARE SECTIONHEEND DECLARE SECTIONCHENGXUJIENEIDINGYI。

XIATUXIANSHIZAIBUTONGBIANCHENGYUYANZHONGSHENGMINGSUZHUBIANLIANGDELIZI。

YUYAN

LIZIYUANMA

C/C++

EXEC SQL BEGIN DECLARE SECTION;

荣耀彩票代理short dept=38, age=26;

double salary;

char CH;

char name1[9], NAME2[9];

/* C comment */

荣耀彩票代理short nul_ind;

EXEC SQL END DECLARE SECTION;

Java

荣耀彩票代理// Note that Java host variable declarations follow

荣耀彩票代理// normal Java variable declaration rules, and have

// no equivalent of a DECLARE SECTION

short dept=38, age=26;

double salary;

char CH;

String name1[9], NAME2[9];

/* Java comment */

short nul_ind;

COBOL

荣耀彩票代理EXEC SQL BEGIN DECLARE SECTION END-EXEC.

荣耀彩票代理01 age PIC S9(4) COMP-5 VALUE 26.

01 DEPT PIC S9(9) COMP-5 VALUE 38.

荣耀彩票代理01 salary PIC S9(6)V9(3) COMP-3.

01 CH PIC X(1).

荣耀彩票代理01 name1 PIC X(8).

荣耀彩票代理01 NAME2 PIC X(8).

* COBOL comment

荣耀彩票代理01 nul-ind PIC S9(4) COMP-5.

EXEC SQL END DECLARE SECTION END-EXEC.

XIAMIANSHIYINYONGSUZHUBIANLIANGDELIZI

YUYAN

LIZIYUANMA

C/C++

EXEC SQL FETCH C1 INTO :cm;

荣耀彩票代理printf( "Commission = %f/n", cm );

Java

荣耀彩票代理#SQL { FETCH :c1 INTO :cm };

System.out.println("Commission = " + cm);

COBOL

荣耀彩票代理EXEC SQL FETCH C1 INTO :cm END-EXEC

DISPLAY 'Commission = ' cm

荣耀彩票代理ZAISQLYUJUZHONGYINYONGSUZHUBIANLIANGSHI,BIXUJIAQIANZHUI—MAOHAO(:)。MAOHAODEZUOYONGSHIJIANGSUZHUBIANLIANGYUSQLYUFAZHONGDEYUANSUQUFENKAI。RUGUOMEIYOUMAOHAO,SUZHUBIANLIANGHUIWUJIESHIWEISQLYUJUDEYIBUFEN。LIRU:

荣耀彩票代理WORKDEPT = dept

JIANGBEIJIESHIWEIWORKDEPTLIEDEZHIDENGYUdeptLIEDEZHI。ZAISUZHUYUYANYUJUZHONG,ZEBUXUYAOJIAQIANZHUI,ZHENGCHANGYINYONGJIKE。CONGXIATUZHONGKEKANCHURUHESHIYONGSUZHUBIANLIANG:

clip_image007

荣耀彩票代理DB2MINGZIKONGJIAN(RUBIAOMING、LIEMINGDENGDENG)BUNENGYONGSUZHUBIANLIANGZHIDING。LIRUBUNENGXIERUXIASQLYUJU:

荣耀彩票代理SELECT :col1 FROM :tabname

荣耀彩票代理DANSHI,ZHEIZHONGLEIXINGDEGONGNENGKEYITONGGUOCAIYONGDONGTAISQLSHIXIAN。

ZONGDELAISHUO,SUZHUBIANLIANGYOUYIXIATEDIAN:

l KEXUANDE,ZAIYUJUYUNXINGZHIQIANYONGLAIFUZHI

l SUZHUYUYANBIAOHAOZAISQLYUJUZHONG,QIANMIANJIAMAOHAO

l SUZHUBIANLIANGYULIEDESHUJULEIXINGBIXUPIPEI

荣耀彩票代理l DUIYUSUZHUBIANLIANGYOUYIXIAYAOQIU:

a. SUOYOUBEIQIANRUSQLYINYONGDESUZHUBIANLIANGBIXUZAIBEGINHEEND DECLAREYUJUJIEDINGDEDAIMAQULISHENGMING;

荣耀彩票代理b. SUZHUBIANLIANGDESHUJULEIXINGBIXUYULIEDESHUJULEIXINGPIPEI,ERQIEJINLIANGBIMIANSHUJUZHUANHUANHEJIEQU;

c. SUZHUBIANLIANGMINGBUNENGYIEXEC、SQL、sqlKAITOU;

d. SUZHUBIANLIANGYINGGAIBEIKANZUOSHIMOKUAICHENGXUDEQUANJUBIANLIANG,ERBUSHIDINGYISUOZAIHANSHUDEJUBUBIANLIANG;

e. ZAIJIEDINGQUWAIDINGYIDEBIANLIANGBUNENGYUJIEDINGQUNEIDINGYIDEBIANLIANGTONGMING;

f. ZAIYIGEYUANWENJIANZHONG,KEYIYOUDUOGEJIEDINGQU;

荣耀彩票代理g. BEGIN DECLARE SECTIONYUJUKEYIZAICHENGXUZHONGSUZHUYUYANGUIZEYUNXUBIANLIANGSHENGMINGDERENHEWEIZHICHUXIAN,SUZHUBIANLIANGDINGYIQUYIEND DECLARE SECTIONYUJUJIESHU;

h. BEGIN DECLARE SECTIONHEEND DECLARE SECTIONYUJUBIXUCHENGDUICHUXIAN,BINGQIEBUNENGQIANTAO;

i. SUZHUBIANLIANGSHENGMINGKEYISHIYONGSQL INCLUDEYUJUZHIDING。LINGWAI,YIGESUZHUBIANLIANGSHENGMINGQUBUNENGHANYOUCHUSUZHUBIANLIANGSHENGMINGYIWAIDEYUJU。

3.2.2 宿主变量的使用

XIAMIANWOMENTONGGUOJIGELIZILAISHUOMINGSUZHUBIANLIANGDEYONGFA:

1.ZAIINSERTYUJUZHONGDESHIYONG

l SQLYUJU

INSERT INTO TEMPL (EMPNO, LASTNAME)

VALUES (‘000190’, ‘JONES’)

荣耀彩票代理l QIANRUCHENGXUDESQLYUJU

EXEC SQL INSERT INTO TEMPL (EMPNO, LASTNAME)

荣耀彩票代理VALUES (:empno, :name);

DIYITIAOSQLYUJUKEYIZAICLPZHONGFACHU,TAYEKEYIQIANRUCHENGXUZHONG,DANSHITAMEIYICIZHINENGCHARUYIXINGZHI,RUGUOYAOCHARUBUTONGDEZHIJIUYAOZHONGXINSHURU,CHENGXUYEYAOXIUGAI。

荣耀彩票代理DIERTIAOSQLYUJUZHINENGQIANRUCHENGXUZHONG,MEIYICIZHIXINGXUYAOYONGHUTONGGUOQITADAIMAZHIDINGXINZHIJISUZHUBIANLIANGempnoHEname,SUZHUBIANLIANGDEZUOYONGSHIJIANGYONGHUZHIDINGDEZHICHUANDIJIVALUESZIJU。KEYISHIXIANSHURUDUOXINGZHI(XUNHUANHUODUOCIYUNXINGCHENGXU)。

2.ZAISETHEWHEREZIJUZHONGDESHIYONG

l SQLYUJU

UPDATE TEMPL

SET SALARY = SALARY *1.05

荣耀彩票代理WHERE JOBCODE = 54

l QIANRUCHENGXUDESQLYUJU

EXEC SQL

UPDATE TEMPL

SET SALARY = SALARY * :percent

WHERE JOBCODE = :code;

荣耀彩票代理3. YONGSUZHUBIANLIANGTIQUZHI。ZAICHENGXUZHONGZHIXINGYIGESELECTYUJUSHI,BIXUTIGONGYIGECUNCHUQUYULAIJIESHOUFANHUIDESHUJU,ERQIEDUIYUBEIXUANZE(selected)DEMEIYILIE,DOUYAODINGYIYIGESUZHUBIANLIANG。YUFAWEI:SELECT … INTO :hostvaribale …。LIZI:

EXEC SQL

SELECT LASTNAME, WORKDEPT

荣耀彩票代理INTO :name, :deptno

FROM TEMPL

WHERE EMPNO = :empid;

LIZIZHONGDINGYILESANGESUZHUBIANLIANG,CONGBIAOTEMPLZHONGXUANZEFUHETIAOJIAN—EMPNO=:empid—DELIANGLIE:LASTNAMEHEWORKDEPT,JIEGUOCUNFANGDAOSUZHUBIANLIANGnameHEdeptnoZHONG。CIXINGSHIDEYONGFAYAOBAOZHENGZHINENGFANHUIDANXINGSHUJU,RUGUOFANHUIDUOXINGSHUJUKU,ZEBUNENGSHIYONGZHEIZHONGFANGFA。HOUMIANHUIJIESHAORUGUOSHIYONGYOUBIAO(cursor)CHULIDUOXINGDEFANHUIJIEGUOJI。

CONGSHANGMIANDELIZIKEJIANGSUZHUBIANLIANGFENWEILIANGLEI:

l SHURUSUZHUBIANLIANG

SHURUSUZHUBIANLIANGGUIDINGXUYAOZAIYUJUZHIXINGQIJIANCONGYINGYONGCHENGXUCHUANDIJISHUJUKUGUANLIQIDEZHI。LIRU,ZAIXIAMIANDESQLYUJUZHONGJIANGSHIYONGYIGESHURUSUZHUBIANLIANG:

SELECT name FROM candidate

荣耀彩票代理WHERE name = < input host variable >

l SHUCHUSUZHUBIANLIANG

SHUCHUSUZHUBIANLIANGGUIDINGXUYAOZAIYUJUZHIXINGQIJIANCONGSHUJUKUGUANLIQICHUANDIJIYINGYONGCHENGXUDEZHI。LIRU,ZAIXIAMIANDESQLYUJUZHONGJIANGSHIYONGYIGESHUCHUSUZHUBIANLIANG:

SELECT INTO < output host variable > FROM candidate

WHERE name = ‘ HUTCHISON ’

3.2.3 指示符变量的声明

ZAISHIJIZHONG,YOUXIEDUIXIANGDEZHIWEIZHI,WOMENYONGKONGZHIBIAOSHI。DANGWOMENXUANZESHUJUSHI,RUGUOSHIKONGZHI,SUZHUBIANLIANGDENEIRONGJIANGBUHUIBEIGAIBIAN,SHISUIJIDE。DB2SHUJUKUGUANLIQITIGONGLEYIGEJIZHIQUTONGZHIYONGHUFANHUISHUJUSHIKONGZHI,ZHEIGEJIZHIJIUSHIZHISHIFUBIANLIANG。

ZHISHIFU(indicator)BIANLIANGSHIYIZHONGTESHUDESUZHUBIANLIANGLEIXING,TAYONGLAIBIAOSHILIEDEKONGZHIHUOFEIKONGZHI。DANGZHEIXIESUZHUBIANLIANGZUOWEISHURUJINRUSHUJUKUZHONGSHI,YINGDANGZAIZHIXINGSQLYUJUZHIQIANYOUYINGYONGCHENGXUDUITAMENSHEZHIZHI。DANGZHEIXIESUZHUBIANLIANGZUOWEISHUJUKUDESHUCHUSHIYONGSHI,ZHEIXIEZHISHIFUYOUYINGYONGCHENGXUDINGYI,DANYOUDB2GENGXINHEJIANGTAMENFANHUI。RANHOU,ZAIJIEGUOBEIFANHUISHI,YINGYONGCHENGXUYINGDANGJIANCHAZHEIXIEZHISHIFUBIANLIANGDEZHI。

KANXIAMIANYITIAOSQLYUJU:

SELECT COLA INTO :a:aind

QIZHONGaSHISUZHUBIANLIANG,aindSHIZHISHIFUBIANLIANG。RUGUOCOLALIEDEZHIBUWEIKONG,DB2JIANGaindDEZHISHEZHIWEIFEIFU(TONGCHANGWEI0);RUGUOCOLALIEDEZHIWEIKONG,DB2JIANGaindDEZHISHEZHIWEIFUSHU(TONGCHANGWEI-1);RUGUODB2SHITUTISHIYIGEKONGZHIDECUNZAI,DANSHICHENGXUMEIYOUTIGONGZHISHIFU,JIANGHUICHANSHENGCUOWU,SQLCODEDENGYU-305。

指示符变量的定义:

ZHISHIFUBIANLIANGDEDINGYIYUSUZHUBIANLIANGDEDINGYIFANGFAXIANGTONG,DOUXUYAOZAIBEGIN DECLARE SECTIONHEEND DECLARE SECTIONZHIJIANDINGYI,BINGQIESHUJULEIXINGYUSQLSHUJULEIXINGSMALLINTDUIYING,ZAICYUYANZHONGWEISHORTLEIXING。

例子:

荣耀彩票代理CREATE TABLE TEMPL

( EMPNO CHAR(6) NOT NULL,

荣耀彩票代理LASTNAME VARCHAR(2) NOT NULL,

JOBCODE CHAR(2),

WORKDEPT CHAR(3), NOT NULL,

荣耀彩票代理PHONENO CHAR(10))

EXEC SQL

荣耀彩票代理SELECT JOBCODE, WORKDEPT, PHONENO

荣耀彩票代理INTO :jc:jci, :dpt, :pho:phoi

FROM TEMPL

荣耀彩票代理WHERE EMPNO = :id;

 

EMPNO(6)

LASTNAME(20)

JOBCODE

0-99

WORKDEPT(3)

PHONENO(10)

000070

000120

000320

JOHNSON

SCOTT

MILLIGAN

54

?

?

C01

C01

C01

5137853210

8592743091

?

3.2.4 指示符变量的使用

 

指示符逻辑例子1:

EXEC SQL

SELECT PHONENO, SEX

INTO :phoneno:phoneind, :sex

FROM TEMPL

荣耀彩票代理WHERE EMPNO = :eno;

荣耀彩票代理if (phoneind < 0)

荣耀彩票代理null_phone();

else

good_phone();

ZAIZHEIGELIZILI,DB2WEIHUZHISHIFUBIANLIANG,YINGYONGCHENGXUZAISQLYUJUZHIXINGHOU,XUNWENZHISHIFUBIANLIANGDEZHI,DIAOYONGXIANGYINGDECHULIHANSHU

 

指示符逻辑例子2:

if (some condition)

荣耀彩票代理phoneind = -1;

else

phoneind = 0;

EXEC SQL

荣耀彩票代理UPDATE TEMPL

SET NEWPHONE = :newphoneno :phoneind

荣耀彩票代理WHERE EMPNO = :eno;

ZAIZHEIGELIZILI,YINGYONGCHENGXUWEIHUZHISHIFUBIANLIANG。YINGYONGCHENGXUGENJUTIAOJIANSHEZHIZHISHIFUBIANLIANGphoneindDEZHI。RUGUODB2FAXIANZHISHIFUDEZHIWEIFUSHU,NEIMEJIDINGXINGJIHEZHONGDELIEBEISHEZHIWEIKONGZHI,SUZHUBIANLIANGDEZHIBEIHULVE;RUGUOZHISHIFUDEZHIWEIZHENGSHUHUOZHEWEILING,SUZHUBIANLIANGZHONGDEZHIBEISHIYONG。

荣耀彩票代理ZAIQIANRUSQLYUJUZHONGKEYISHIYONGGUANJIANZINULL。XIAMIANSHIBUSHIYONGZHISHIFUBIANLIANGDEYIGEUPDATEYUJULIZI:

if ( some condition)

EXEC SQL

荣耀彩票代理UPDATE TEMPL

SET PHONENO = NULL

WHERE EMPNO = :eno ;

else

EXEC SQL

UPDATE TEMPL

荣耀彩票代理SEST PHONENO = :newphone

荣耀彩票代理WHERE EMPNO = :eno ;

荣耀彩票代理DANSHI,ZHEIZHONGXIEFAYOUQUEDIAN:RUGUOUPDATEYUJUXUYAOXIUGAI,JIUYAOXIUGAILIANGCHUDAIMA。

如何设置指示符变量:

SHEIWEIHUZHISHIFUBIANLIANG

SQLYUJULEIXING

SUZHUBIANLIANG

:cd

ZHISHIFUBIANLIANG

:cdi

LIE

JOBCODE

DB2

SELECT/

FETCH

60

BUGAIBIAN

0

<0

60

NULL

YINGYONGCHENGXU

UPDATE/

INSERT

50

N/A

0

<0

50

NULL

ZHUJIE:

荣耀彩票代理DB2ZAIZHIXINGSELECTHEFETCHYUJUDEGUOCHENGZHONGSHEZHIZHISHIFUBIANLIANGDEZHI,YINGYONGCHENGXUYINGGAIZAIZHIXINGSELECTHEFETCHYUJUHOUJIANCHATAMENDEZHI。

荣耀彩票代理YINGYONGCHENGXUZAIZHIXINGUPDATEHEINSERTYUJUZHIQIANSHEZHIZHISHIFUBIANLIANGDEZHILAIZHISHIDB2SHIFOUZAISHUJUKUZHONGFANGZHIYIGEKONGZHI(NULL)。

SHANGBIAODEDIYIXING:ZAIYITIAOSELECTHUOZHEFETCHYUJUZHONG,RUGUOLIE(JOBCODE)ZHONGDEZHIBUWEIKONG,ZHIBEISHEZHIDAOSUZHUBIANLIANG(:cd)ZHONG,ZHISHIFUBIANLIANG(:cdi)DEZHIWEILING;RUGUOLIEZHONGDEZHIWEIKONG,ZHISHIFUBIANLIANGDEZHIJIANGWEIFUSHU,SUZHUBIANLIANGDEZHIBUGAIBIAN。

荣耀彩票代理SHANGBIAODEDIYIXING:ZAIYITIAOUPDATEHUOZHEINSERTYUJUZHONG,RUGUOZHISHIFUBIANLIANG(:cdi)ZHONGDEZHIBUWEIFUSHU,SUZHUBIANLIANG(:cd)ZHONGDEZHIBEIFANGDAOXIANGYINGDELIEZHONG;RUGUOZHISHIFUBIANLIANGZHONGDEZHIWEIFUSHU,SUZHUBIANLIANGZHONGDEZHIBEIHULVE,XIANGYINGDELIEBEISHEZHIWEIKONG(NULL)。

指示符变量在数值转换方面的应用:

荣耀彩票代理DANGSUZHUBIANLIANGDESHUJULEIXINGYUXIANGYINGLIEDESHUJULEIXINGBUJIANRONGHUOZHEBUNENGZHUANHUANSHI,DB2YETONGGUOZHISHIFUBIANLIANGTONGZHIYINGYONGCHENGXU。

SHUZHIZHUANHUANYOUSHUJUKUGUANLIQICHULI,NENGZHUANHUANSHIZIDONGWANCHENG,DUICHENGXUTOUMING;

RUGUOLIEZHONGDEZHIBUNENGCUNCHUDAOSUZHUBIANLIANGZHONGSHI(LIRU,LIEDESHUJULEIXINGWEIDECIMAL(15),ZHIDEZHANGDUWEI12GESHUZI,BUNENGCUNCHUDAOINTEGERLEIXINGDESUZHUBIANLIANGZHONG),ZHISHIFUBIANLIANGDEZHIWEI-2。

指示符变量在截取方面的应用:

ZAISQLYUJUZHIXINGHOU,RUGUOZHISHIFUBIANLIANGDEZHIWEIZHENGSHU,SHUOMINGFASHENGLESHUJUJIEQU:

—RUGUOSHISHIJIANSHUJULEIXINGDEMIAOBUFENBEIJIEQU,NEIMEZHISHIFUBIANLIANGZHONGDEZHIWEIJIEQUDEMIAOSHU

—DUIYUQITASHUJULEIXING,ZHISHIFUBIANLIANGBIAOSHISHUJUKUZHONGLIEDESHUJUYUANSHIZHANGDU,TONGCHANGWEIZIJIESHU(SHUJUKUJINKENENGFANHUIGENGDUODESHUJU)。

LIZI:

荣耀彩票代理DINGYISUZHUBIANLIANGHEZHISHIFUBIANLIANG:

荣耀彩票代理EXEC SQL BEGIN DECLARE SECTION;

char too_little[5];

short iv1;

EXEC SQL END DECLARE SECTION;

BIAOBANK_ITEMS:

ITEM#

QTY

荣耀彩票代理DESCRIPTION

101

3000

PASSBOOKS

200

100

CHECKBOOKS

ZHIXINGDESQLYUJU:

SELECT DESCRIPTION INTO :too_little:iv

FROM BANK_ITEMS WHERE ITEM# = 200

JIEGUO:

荣耀彩票代理:too_littleZHONGDEZHIWEI’CHECK’,:iv1ZHONGDEZHIWEI10

3.3 使用游标处理多行结果集

WEILESHIYINGYONGCHENGXUNENGGOUTIQUDUOXINGJIEGUOJI,SQLSHIYONGLEYIZHONGJIZHI,JIAOZUOYOUBIAO(cursor)。

荣耀彩票代理WEILELIJIEYOUBIAODEGAINIAN,WOMENJIASHESHUJUKUGUANLIQICHUANGJIANLEYIGEJIEGUOBIAO(result table),LIMIANBAOHANYOUZHIXINGYITIAOSELECTYUJUSUOTIQUDESUOYOUXING。TONGGUOBIAOSHIHUOZHIXIANGZHEIGEBIAODE“DANGQIANXING”,YOUBIAOSHIDEJIEGUOBIAOZHONGDEXINGDUIYINGYONGCHENGXUKEYONG。DANGYIGESHIYONGYOUBIAOSHI,YINGYONGCHENGXUKEYISHUNXUCONGJIEGUOBIAOZHONGTIQUMEIYIXING,ZHIDAOCHANSHENGYIGESHUJUJIESHUTIAOJIAN,ZHEIGETIAOJIANJIUSHINOT FOUNDTIAOJIAN,SQLCAZHONGDESQLCODEWEI+100(SQLSTATEWEI02000)。ZHIXINGSELECTYUJUDEJIEGUOJI,KENENGYOULINGXING、YIXINGHUOZHEGENGDUOXING,JUEDINGYUYOUDUOSHAOXINGFUHESOUSUODETIAOJIAN。

荣耀彩票代理CHULIYIGEYOUBIAOSHEJIDAOYIXIAJIGEBUZHOU:

1.SHIYONGDECLARE CURSORYUJUSHENGMINGYIGEYOUBIAO

2.SHIYONGOPENYUJUZHIXINGCHAXUNHECHUANGJIANJIEGUOBIAO

3.SHIYONGFETCHYUJUMEICITIQUYIXINGJIEGUO

4.SHIYONGDELETEHUOUPDATEYUJUCHULIXING(RUGUOXUYAO)

荣耀彩票代理5.SHIYONGCLOSEYUJUZHONGZHI(GUANBI)YOUBIAO

荣耀彩票代理YIGEYINGYONGCHENGXUZHONG,KEYISHIYONGDUOGEYOUBIAO,DANSHIMEIYIGEYOUBIAOYAOYOUZIJIDEDECLARE CURSOR,OPEN,CLOSEHEFETCHYUJUJI。

3.3.1声明和使用游标

荣耀彩票代理DECLARE CURSORYUJUDINGYIHEMINGMINGYOUBIAO,QUEDINGSHIYONGSELECTYUJUYAOTIQUDEXINGJIEGUOJI。

YINGYONGCHENGXUJIYOUBIAOFENPEIYIGEMINGZI。ZHEIGEMINGZIZAISUIHOUDEOPEN、FETCHHECLOSEYUJUZHONGDOUYAOBEICANKAODAO。CHAXUNKEYISHIRENHEYOUXIAODESELECTYUJU。

XIAMIANLIZIZHANSHILEYITIAODECLAREYUJURUHEYUYITIAOJINGTAISELECTYUJUGUANLIANQILAI:

YUYAN

YUANMALICHENG

C/C++

EXEC SQL DECLARE C1 CURSOR FOR

SELECT PNAME, DEPT FROM STAFF

荣耀彩票代理WHERE JOB=:host_var;

Java (SQLJ)

#sql iterator cursor1(host_var data type);

#sql cursor1 = { SELECT PNAME, DEPT FROM STAFF

荣耀彩票代理WHERE JOB=:host_var };

COBOL

EXEC SQL DECLARE C1 CURSOR FOR

荣耀彩票代理SELECT NAME, DEPT FROM STAFF

WHERE JOB=:host-var END-EXEC.

FORTRAN

荣耀彩票代理EXEC SQL DECLARE C1 CURSOR FOR

+ SELECT NAME, DEPT FROM STAFF

荣耀彩票代理+ WHERE JOB=:host_var

荣耀彩票代理ZHUJIE:DECLAREYUJUDEZAICHENGXUZHONGWEIZHISHIRENYIDE,DANSHITABIXUZAIDIYICISHIYONGYOUBIAODEWEIZHIZHIQIAN。

3.3.2游标与工作单元的考虑

COMMITHUOZHEROLLBACKCAOZUODEDONGZUOSUIYOUBIAODEBUTONGERBUTONG,YILAIYUYOUBIAODEDINGYI。

1.ZHIDUYOUBIAO(Read Only Cursors)

荣耀彩票代理RUGUOYIGEYOUBIAOBEIQUEDINGWEIZHIDUDE,BINGQIESHIYONGKEZHONGFUDUGELIJI(isolation level),NEIMEXITONGBIAORENGHUISHOUJIHEWEIHUGONGZUODANYUANXUYAODEKEZHONGFUDUSUO。YINCI,JISHIZHIDUYOUBIAO,YINGYONGCHENGXUZHOUQIXINGDIFACHUCOMMITYUJUHAISHIHENZHONGYAODE。

2.YOUWITH HOLDXUANXIANG

RUGUOYINGYONGCHENGXUTONGGUOFACHUYITIAOCOMMITYUJULAIWANCHENGYIGEGONGZUODANYUAN,CHULESHENGMINGSHIYOUWITH HOLDXUANXIANGDEYOUBIAO,SUOYOUYOUBIAOJIANGZIDONGDIBEISHUJUKUGUANLIQIGUANBI。

荣耀彩票代理YONGWITH HOLDSHENGMINGDEYOUBIAOWEIHUTAFANGWENDEKUADUOGEGONGZUODANYUANDEZIYUAN。YONGWITH HOLDSHENGMINGDEYOUBIAOSHOUDAODEYINGXIANGYILAIYUGONGZUODANYUANRUHEJIESHU。

RUGUOGONGZUODANYUANSHIYONGYITIAOCOMMITYUJUJIESHU,YIDAKAIDEDINGYIWEIWITH HOLDDEYOUBIAOJIANGBAOCHIDAKAIZHUANGTAI。YOUBIAODEWEIZHIZAIJIEGUOJIDEXIAYIGELUOJIXINGZHIQIAN。LINGWAI,CANKAOYONGWITH HOLDDINGYIDEYIZHUNBEIHAODEYUJUYEHUIBEIBAOLIU。JINGENCOMMITYUJUHOUMIAN, ZHIYOUYUYIGEMOUGETEDINGYOUBIAOXIANGGUANLIANDEFETCHHECLOSEQINGQIUCAIYOUXIAO。UPDATE WHERE CURRENT OFHEDELETE WHERE CURRENT OF YUJUJINJINDUIZAITONGYIGEGONGZUODANYUANZHONGTIQUDEXINGYOUXIAO。RUGUOCHENGXUBAOZAIGONGZUODANYUANQIJIANBEIZHONGXINBANGDING,NEIMESUOYOUBAOCHIDEYOUBIAODOUHUIBEIGUANBI。

荣耀彩票代理RUGUOGONGZUODANYUANSHIYONGYITIAOROLLBACKYUJUJIESHU,SUOYOUDAKAIDEYOUBIAOBEIGUANBI,SUOYOUZAIGONGZUODANYUANZHONGHUODEDESUOBEISHIFANG,YIJISUOYOUYILAIYUZHEIGEGONGZUODANYUANDEYIZHUNBEIHAODEYUJUBEISHANCHU。

举个例子荣耀彩票代理,假设TEMPL表中有1000条记录。要更新所有雇员的工资,应该每更新100行就要发出一条COMMIT语句。

荣耀彩票代理A. SHIYONGWITH HOLDXUANXIANGSHENGMINGYOUBIAO:

EXEC SQL DECLARE EMPLUPDT CURSOR WITH HOLD FOR

SELECT EMPNO, LASTNAME, PHONENO, JOBCODE, SALARY

FROM TEMPL FOR UPDATE OF SALARY

B. DAKAIYOUBIAO,MEIYICICONGJIEGUOBIAOZHONGTIQUYIXINGSHUJU:

荣耀彩票代理EXEC SQL OPEN EMPLUPDT

.

.

.

荣耀彩票代理EXEC SQL FETCH EMPLUPDT

荣耀彩票代理INTO :upd_emp, :upd_lname, :upd_tele, :upd_jobcd, :upd_wage,

C. DANGXIANGYAOGENGXINHUOZHESHANCHUYIXINGSHI,SHIYONGDAIWHERE CURRENT OFXUANXIANGDEUPDATEHUOZHEDELETEYUJU。LIRU,YAOGENGXINDANGQIANXING,CHENGXUKEYIFACHUXIAMIANDEYUJU:

EXEC SQL UPDATE TEMPL SET SALARY = :newsalary

WHERE CURRENT OF EMPLUPDT

ZAIYITIAOCOMMITYUJUFACHUZHIHOU,ZAIGENGXINQITAXINGZHIQIANBIXUFACHUFETCHYUJU。

RUGUOYINGYONGCHENGXUSHIYONGLEYONGWITH HOLDSHENGMINGDEYOUBIAOHUOZHEZHIXINGLEDUOGEGONGZUODANYUANBINGQIEYOUYIGEYONGWITH HOLDSHENGMINGDEYOUBIAOKUAGONGZUODANYUANCHUYUDAKAIZHUANGTAI,NEIMEZAICHENGXUZHONGYINGGAIJIARUDAIMAJIANCEHECHULISQLCODEWEI-501(SQLSTATEWEI24501)DECUOWU,ZHEIGECUOWUYOUFETCHHUOZHECLOSEYUJUFANHUI。

荣耀彩票代理RUGUOYINGYONGCHENGXUDECHENGXUBAOYOUYUQIYILAIDEBIAOBEISHANCHUERBIANDEWUXIAO,CHENGXUBAOHUIZIDONGBEIZHONGXINBANGDING。ZHEIZHONGQINGKUANGXIA,FETCHHUOCLOSEYUJUFANHUISQLCODE –501(SQLSTATE 24501),YINWEI SHUJUKUGUANLIQIGUANBIYOUBIAO。ZAICIQINGXINGXIA,CHULISQLCODE –501(SQLSTATE 24501)DEFANGFAJUEDINGYUSHIFOUYAOCONGYOUBIAOTIQUXINGSHUJU。

l RUGUOYAOCONGYOUBIAOTIQUXING,DAKAIYOUBIAO,RANHOUYUNXINGFETCHYUJU。ZHUYI,OPENYUJUSHIDEYOUBIAOZHONGDINGWEIDAOKAISHICHU。YUANLAIDEWEIZHIXINXIDIUSHI。

l RUGUOBUZHUNBEICONGYOUBIAOTIQUXING,NEIMEBUYAODUIYOUBIAOFACHURENHESQLQINGQIU。

WITH RELEASE XUANXIANG:DANGYINGYONGCHENGXUYONGWITH RELEASEXUANXIANGGUANBIYIGEYOUBIAOSHI,DB2SHITUQUSHIFANGYOUBIAOCHIYOUDESUOYOUDUSUO(READ locks)。YOUBIAOZHIJIXUCHIYOUXIESUO(WRITE locks)。RUGUOYINGYONGCHENGXUMEIYOUYONGRELEASEXUANXIANGGUANBIYOUBIAO,NEIMEZAIGONGZUODANYUANWANCHENGSHI,SUOYOUDEDUSUOHEXIESUODOUBEISHIFANG。

3.3.3例程

游标程序

C Example: CURSOR.SQC

#include

#include

#include

荣耀彩票代理#include "util.h"

荣耀彩票代理#ifdef DB268K

/* Need to include ASLM for 68K applications */

#include

#endif

荣耀彩票代理EXEC SQL INCLUDE SQLCA;

荣耀彩票代理#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;

int main(int argc, char *argv[]) {

荣耀彩票代理EXEC SQL BEGIN DECLARE SECTION;

char pname[10];

荣耀彩票代理short dept;

char userid[9];

荣耀彩票代理char passwd[19];

荣耀彩票代理EXEC SQL END DECLARE SECTION;

#ifdef DB268K

/* Before making any API calls for 68K environment,

荣耀彩票代理need to initial the Library Manager */

荣耀彩票代理InitLibraryManager(0,kCurrentZone,kNormalMemory);

荣耀彩票代理atexit(CleanupLibraryManager);

#endif

荣耀彩票代理printf( "Sample C program: CURSOR /n" );

if (argc == 1) {

EXEC SQL CONNECT TO sample;

荣耀彩票代理CHECKERR ("CONNECT TO SAMPLE");

}

else if (argc == 3) {

strcpy (userid, argv[1]);

strcpy (passwd, argv[2]);

荣耀彩票代理EXEC SQL CONNECT TO sample USER :userid USING :passwd;

CHECKERR ("CONNECT TO SAMPLE");

}

else {

printf ("/nUSAGE: cursor [userid passwd]/n/n");

return 1;

} /* endif */

荣耀彩票代理EXEC SQL DECLARE c1 CURSOR FOR (1)

SELECT name, dept FROM staff WHERE job='Mgr'

FOR UPDATE OF job;

荣耀彩票代理EXEC SQL OPEN c1; (2)

荣耀彩票代理CHECKERR ("OPEN CURSOR");

do {

荣耀彩票代理EXEC SQL FETCH c1 INTO :pname, :dept; (3)

荣耀彩票代理if (SQLCODE != 0) break;

printf( "%-10.10s in dept. %2d will be demoted to Clerk/n",

荣耀彩票代理pname, dept );

荣耀彩票代理} while ( 1 );

EXEC SQL CLOSE c1; (4)

荣耀彩票代理CHECKERR ("CLOSE CURSOR");

EXEC SQL ROLLBACK;

荣耀彩票代理CHECKERR ("ROLLBACK");

荣耀彩票代理printf( "/nOn second thought -- changes rolled back./n" );

荣耀彩票代理EXEC SQL CONNECT RESET;

荣耀彩票代理CHECKERR ("CONNECT RESET");

return 0;

}

/* end of program : CURSOR.SQC */

Java Example: Cursor.sqlj

荣耀彩票代理import java.sql.*;

荣耀彩票代理import sqlj.runtime.*;

荣耀彩票代理import sqlj.runtime.ref.*;

#sql iterator CursorByName(String name, short dept) ;

#sql iterator CursorByPos(String, short ) ;

荣耀彩票代理class Cursor

{ static

{ try

{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();

}

catch (Exception e)

荣耀彩票代理{ System.out.println ("/n Error loading DB2 Driver.../n");

荣耀彩票代理System.out.println (e);

System.exit(1);

}

}

public static void main(String argv[])

{ try

{ System.out.println (" Java Cursor Sample");

String url = "jdbc:db2:sample";

// URL is jdbc:db2:dbname

Connection con = null;

荣耀彩票代理// Set the connection

if (argv.length == 0)

{ // connect with default id/password

con = DriverManager.getConnection(url);

}

else if (argv.length == 2)

荣耀彩票代理{ String userid = argv[0];

String passwd = argv[1];

// connect with user-provided username and password

荣耀彩票代理con = DriverManager.getConnection(url, userid, passwd);

}

else

{ throw new Exception("Usage: java Cursor [username password]");

}

// Set the default context

荣耀彩票代理DefaultContext ctx = new DefaultContext(con);

荣耀彩票代理DefaultContext.setDefaultContext(ctx);

// Enable transactions

荣耀彩票代理con.setAutoCommit(false);

// Using cursors

try

{ CursorByName cursorByName;

荣耀彩票代理CursorByPos cursorByPos;

String name = null;

荣耀彩票代理short dept=0;

// Using the JDBC ResultSet cursor method

System.out.println("/nUsing the JDBC ResultSet cursor method");

荣耀彩票代理System.out.println(" with a 'bind by name' cursor .../n");

#sql cursorByName = {

荣耀彩票代理SELECT name, dept FROM staff

荣耀彩票代理WHERE job='Mgr' FOR UPDATE OF job }; (1)

荣耀彩票代理while (cursorByName.next()) (2)

{ name = cursorByName.name(); (3)

dept = cursorByName.dept();

System.out.print (" name= " + name);

System.out.print (" dept= " + dept);

荣耀彩票代理System.out.print ("/n");

}

荣耀彩票代理cursorByName.close(); (4)

荣耀彩票代理// Using the SQLJ iterator cursor method

System.out.println("/nUsing the SQLJ iterator cursor method");

荣耀彩票代理System.out.println(" with a 'bind by position' cursor .../n");

#sql cursorByPos = {

SELECT name, dept FROM staff

WHERE job='Mgr' FOR UPDATE OF job }; (1) (2)

荣耀彩票代理while (true)

{ #sql { FETCH :cursorByPos INTO :name, :dept }; (3)

if (cursorByPos.endFetch()) break;

System.out.print (" name= " + name);

System.out.print (" dept= " + dept);

System.out.print ("/n");

}

cursorByPos.close(); (4)

}

catch( Exception e )

{ throw e;

}

finally

{ // Rollback the transaction

System.out.println("/nRollback the transaction...");

#sql { ROLLBACK };

System.out.println("Rollback done.");

}

}

荣耀彩票代理catch( Exception e )

荣耀彩票代理{ System.out.println (e);

}

}

}

游标程序如果工作:

1.SHENGMINGYOUBIAO。DECLARE CURSORYUJUJIANGYOUBIAOc1YUYIGECHAXUNGUANLIANQILAI。CHAXUNQUEDINGYINGYONGCHENGXUSHIYONGFETCHYUJUTIQUDEXING。BIAOstaffDEjobZIDUANBEIDINGYIWEIKEGENGXINDE,JISHITAZAIJIEGUOBIAOZHONG。

荣耀彩票代理2.DAKAIYOUBIAO。YOUBIAOc1BEIDAKAI,SHUJUKUGUANLIQIZHIXINGCHAXUNBINGCHUANGJIANJIEGUOBIAO。YOUBIAODEZHIXIANGWEIYUDIYIXINGDEQIANMIAN。

3.TIQUYIXING。FETCHYUJUSHIYOUBIAOZHIXIANGXIAYIXING,JIANGNEIXINGDENEIRONGYIDONGDAOSUZHUBIANLIANGZHONG。NEIXINGCHENGWEIDANGQIANXING。

荣耀彩票代理4. GUANBIYOUBIAO。FACHUCLOSEYUJU,SHIFANGYUYOUBIAOXIANGGUANLIANDEZIYUAN。YOUBIAORENGRANKEYIZAICIDAKAI。

3.3.4更新和删除提取的数据

KEYIGENGXINHESHANCHUYIGEYOUBIAOZHIXIANGDEXING。YAOSHIXINGNENGBEIGENGXIN,DUIYINGYUYOUBIAODECHAXUNBUNENGSHIZHIDUDE。

更新提取的数据

WEILELIYONGYOUBIAOLAIGENGXINSHUJU,ZAIUPDATEYUJUZHONGSHIYONGWHERE CURRENT OFZIJU。SHIYONGFOR UPDATEZIJUGAOSUXITONGJIEGUOBIAOZHONGDENAXIELIEYAOGENGXIN。YEKEYIZAIFOR UPDATEZHONGZHICHUBUZAIXUANZEFANWEIZHINEIDELIE,CONGLINGYIGEJIAODUJIANG,KEYIGENGXINBUBEIYOUBIAOTIQUDELIE。RUGUOFOR UPDATEZIJUZHONGMEIYOUZHIDINGRENHELIEMING,NEIMEZAIDIYIGEFROMZIJUZHONGBIAOSHIDEBIAOHUOZHESHITUDESUOYOULIEDOUBEIKANZUOSHIKEGENGXINDE。XINGNENGSHANGYAOQIUZHIZAIFOR UPDATEZIJULIZHIDINGYAOGENGXINDELIE,RUGUOZHIDINGBUXUYAOGENGXINDELIE,DB2JIANGLANGFEIMEIYOUBIYAODEKAIXIAOQUFANGWENSHUJU。

删除提取的数据

荣耀彩票代理WEILELIYONGYOUBIAOLAISHANCHUSHUJU,ZAIDELETEYUJUZHONGSHIYONGWHERE CURRENT OFTIAOKUAN。TONGCHANGSHANG,WEILESHANCHUYOUBIAODEDANGQIANXING,DINGYIYOUBIAOSHI,BUXUYAOFOR UPDATETIAOKUAN。YIGELIWAIQINGKUANGSHI,ZAIYINGYONGCHENGXUZHONGSHIYONGDONGTAIDESELECTYUJUHUOZHEDELETEYUJU,YUBIANYISHILANGLEVELXUANXIANGSHEWEISAA1,KUNBANGSHISHIYONGBLOCKING ALLXUANXIANG。ZHEIZHONGQINGKUANGXIA,SELECTYUJUXUYAOFOR UPDATETIAOKUAN。(XIANGXIXINXIQINGKAN3.3JIEKAIFADONGTAISQLYINGYONGCHENGXU)

DELETEYUJUSHIDEYOUBIAOZHISHIDENEIXINGBEISHANCHU。YOUBIAODEWEIZHIZHIXIANGXIAYIXINGDEQIANMIAN。YAOJINXINGXIAYIGEWHERE CURRENT OFCAOZUOZHIQIAN,BIXUDUIYOUBIAOFACHUYITIAOFETCHYUJU。

游标的类型

YOUBIAOKEYIFENWEISANLEI:

1.ZHIDULEIXING

YOUBIAOZHIXIANGDEXINGZHINENGGOUDU,BUNENGGOUGENGXIN。YINGYONGCHENGXUZHISHIDUQUSHUJUBUXIUGAISHI,SHIYONGZHIDUYOUBIAO。RUGUOYOUBIAOSHIJIYUZHIDUSELECTYUJU,NEIMETABIANSHIZHIDUDE。ZHIDUYOUBIAOZAIXINGNENGFANGMIANJIAOHAO。

2.KEGENGXINLEIXING

YOUBIAOZHIXIANGDEXINGKEYIGENGXIN。YINGYONGCHENGXUXUYAOXIUGAITIQUDESHUJUSHI,SHIYONGKEGENGXINYOUBIAO。ZHIDINGDECHAXUNZHINENGSHEJIDAOYIGEBIAOHUOSHITU。CHAXUNBIXUBAOHANFOT UPDATEZIJU,BINGXIECHUMEIYIGEYAOGENGXINDELIE(CHUFEIYUBIANYISHISHIYONGLANGLEVEL MIAXUANXIANG)。

3.BUMINGQUELEIXING

CONGYOUBIAODEDINGYIHUOZHESHANGXIAWENLAIQUEDINGYOUBIAOSHIKEGENGXINDEHAISHIZHIDUDE。

DANGYUBIANYIHUOBANGDINGSHISHIYONGBLOCKING ALLXUANXIANG,BUMINGQUEDEYOUBIAOBEIKANZUOSHIZHIDUDE。FOUZE,BEIKANZUOSHIKEGENGXINDE。

ZHUYI:DONGTAICHULIDEYOUBIAOTONGCHANGSHIBUMINGQUELEIXINGDE。

例子 OPENFTCH程序

荣耀彩票代理ZHEIGELIZISHIYONGYIGEYOUBIAOCONGYIGEBIAOZHONGXUANZEXING,DAKAIYOUBIAO,CONGBIAOZHONGTIQUXING。DUITIQUDEMEIYIXING,PANDUANSHIFOUYAOSHANCHUHUOZHEGENGXIN(GENJUYIGEJIANDANDETIAOJIAN)。ZHEIGELIZIYOUYIXIAYUYANDEBANBEN:

荣耀彩票代理CYUYAN:openftch.sqc

JavaYUYAN:Openftch.sqlj and OpF_Curs.sqlj

COBOLYUYAN:openftch.sqb

FORTRAN YUYAN:openftch.sqf

C Example: OPENFTCH.SQC

#include

#include

#include

#include "util.h"

荣耀彩票代理#ifdef DB268K

/* Need to include ASLM for 68K applications */

#include

#endif

EXEC SQL INCLUDE SQLCA;

#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;

荣耀彩票代理int main(int argc, char *argv[]) {

EXEC SQL BEGIN DECLARE SECTION;

char pname[10];

short dept;

char userid[9];

char passwd[19];

EXEC SQL END DECLARE SECTION;

荣耀彩票代理#ifdef DB268K

荣耀彩票代理/* Before making any API calls for 68K environment,

need to initial the Library Manager */

荣耀彩票代理InitLibraryManager(0,kCurrentZone,kNormalMemory);

atexit(CleanupLibraryManager);

#endif

printf( "Sample C program: OPENFTCH/n" );

if (argc == 1) {

荣耀彩票代理EXEC SQL CONNECT TO sample;

CHECKERR ("CONNECT TO SAMPLE");

}

荣耀彩票代理else if (argc == 3) {

strcpy (userid, argv[1]);

strcpy (passwd, argv[2]);

EXEC SQL CONNECT TO sample USER :userid USING :passwd;

CHECKERR ("CONNECT TO SAMPLE");

}

else {

荣耀彩票代理printf ("/nUSAGE: openftch [userid passwd]/n/n");

return 1;

荣耀彩票代理} /* endif */

EXEC SQL DECLARE c1 CURSOR FOR (1)

SELECT name, dept FROM staff WHERE job='Mgr'

FOR UPDATE OF job;

EXEC SQL OPEN c1; (2)

CHECKERR ("OPEN CURSOR");

do {

EXEC SQL FETCH c1 INTO :pname, :dept; (3)

if (SQLCODE != 0) break;

荣耀彩票代理if (dept > 40) {

printf( "%-10.10s in dept. %2d will be demoted to Clerk/n",

荣耀彩票代理pname, dept );

EXEC SQL UPDATE staff SET job = 'Clerk' (4)

WHERE CURRENT OF c1;

荣耀彩票代理CHECKERR ("UPDATE STAFF");

} else {

printf ("%-10.10s in dept. %2d will be DELETED!/n",

pname, dept);

EXEC SQL DELETE FROM staff WHERE CURRENT OF c1;

CHECKERR ("DELETE");

} /* endif */

} while ( 1 );

EXEC SQL CLOSE c1; (5)

荣耀彩票代理CHECKERR ("CLOSE CURSOR");

荣耀彩票代理EXEC SQL ROLLBACK;

CHECKERR ("ROLLBACK");

printf( "/nOn second thought -- changes rolled back./n" );

EXEC SQL CONNECT RESET;

CHECKERR ("CONNECT RESET");

return 0;

}

荣耀彩票代理/* end of program : OPENFTCH.SQC */

Java Example: Openftch.sqlj

OpF_Curs.sqlj

// PURPOSE : This file, named OpF_Curs.sqlj, contains the definition

荣耀彩票代理// of the class OpF_Curs used in the sample program Openftch.

import sqlj.runtime.ForUpdate;

#sql public iterator OpF_Curs implements ForUpdate (String, short);

Openftch.sqlj

荣耀彩票代理import java.sql.*;

荣耀彩票代理import sqlj.runtime.*;

import sqlj.runtime.ref.*;

class Openftch

{ static

{ try

荣耀彩票代理{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();

}

catch (Exception e)

荣耀彩票代理{ System.out.println ("/n Error loading DB2 Driver.../n");

荣耀彩票代理System.out.println (e);

System.exit(1);

}

}

荣耀彩票代理public static void main(String argv[])

{ try

{ System.out.println (" Java Openftch Sample");

String url = "jdbc:db2:sample"; // URL is jdbc:db2:dbname

荣耀彩票代理Connection con = null;

荣耀彩票代理// Set the connection

荣耀彩票代理if (argv.length == 0)

{ // connect with default id/password

con = DriverManager.getConnection(url);

}

else if (argv.length == 2)

荣耀彩票代理{ String userid = argv[0];

荣耀彩票代理String passwd = argv[1];

// connect with user-provided username and password

荣耀彩票代理con = DriverManager.getConnection(url, userid, passwd);

}

else

{ throw new Exception(

"/nUsage: java Openftch [username password]/n");

荣耀彩票代理} // if - else if - else

// Set the default context

荣耀彩票代理DefaultContext ctx = new DefaultContext(con);

DefaultContext.setDefaultContext(ctx);

荣耀彩票代理// Enable transactions

荣耀彩票代理con.setAutoCommit(false);

荣耀彩票代理// Executing SQLJ positioned update/delete statements.

try

{ OpF_Curs forUpdateCursor;

String name = null;

荣耀彩票代理short dept=0;

荣耀彩票代理#sql forUpdateCursor =

{ SELECT name, dept

FROM staff

WHERE job='Mgr'

FOR UPDATE OF job

}; // #sql (1)(2)

while (true)

{ #sql

{ FETCH :forUpdateCursor

INTO :name, :dept

}; // #sql (3)

荣耀彩票代理if (forUpdateCursor.endFetch()) break;

if (dept > 40)

荣耀彩票代理{ System.out.println (

荣耀彩票代理name + " in dept. "

荣耀彩票代理+ dept + " will be demoted to Clerk");

#sql

{ UPDATE staff SET job = 'Clerk'

WHERE CURRENT OF :forUpdateCursor

}; // #sql (4)

}

else

荣耀彩票代理{ System.out.println (

name + " in dept. " + dept

+ " will be DELETED!");

#sql

荣耀彩票代理{ DELETE FROM staff

WHERE CURRENT OF :forUpdateCursor

}; // #sql

荣耀彩票代理} // if - else

}

forUpdateCursor.close(); (5)

}

catch( Exception e )

荣耀彩票代理{ throw e; }

finally

{ // Rollback the transaction

荣耀彩票代理System.out.println("/nRollback the transaction...");

荣耀彩票代理#sql { ROLLBACK };

System.out.println("Rollback done.");

荣耀彩票代理} // try - catch - finally

}

catch( Exception e )

荣耀彩票代理{ System.out.println (e); } // try - catch

} // main

荣耀彩票代理} // class Openftch

OPENFTCH程序是如何工作的:

荣耀彩票代理1.SHENGMINGYOUBIAO。DECLARE CURSORYUJUJIANGYOUBIAOc1YUYIGECHAXUNGUANLIANQILAI。CHAXUNQUEDINGYINGYONGCHENGXUSHIYONGFETCHYUJUTIQUDEXING。BIAOstaffDEjobZIDUANBEIDINGYIWEIKEGENGXINDE,JISHITABUZAIJIEGUOBIAOZHONG。

2.DAKAIYOUBIAO。YOUBIAOc1BEIDAKAI,DAOZHISHUJUKUGUANLIQIZHIXINGCHAXUNBINGCHUANGJIANYIGEJIEGUOBIAO。YOUBIAODEWEIZHIWEIYUDIYIXINGDEQIANMIAN。

荣耀彩票代理3.TIQUYIXING。FETCHYUJUJIANGYOUBIAOZHIXIANGXIAYIXING,BINGJIANGXINGDENEIRONGYIDAOSUZHUBIANLIANGZHONG。NEIXINGCHENGWEIDANGQIANXING。

4.GENGXINHUOSHANCHUDANGQIANXING。DANGQIANXINGBEIGENGXINHUOZHEBEISHANCHU,JUEDINGYUFETCHYUJUFANHUIdeptDEZHI。

RUGUOYIGEUPDATEYUJUBEIZHIXING,YOUBIAODEWEIZHIBAOCHIBUBIAN,YINWEIUPDATEYUJUBUGAIBIANDANGQIANXINGDEWEIZHI。

RUGUOYIGEDELETEYUJUBEIZHIXING,YOUBIAODEWEIZHIWEIYUXIAYIXINGDEQIANMIAN,YINWEIDANGQIANXINGYIBEISHANCHU。ZAIJINXINGLINGWAIDEWHERE CURRENT OFCAOZUOZHIQIAN,BIXUZHIXINGYITIAOFETCHYUJU。

5.GUANBIYOUBIAO。CLOSEYUJUBEIFACHU,SHIFANGYUYOUBIAOGUANLIANDEZIYUAN。YOUBIAOKEYIBEIZAICIDAKAI。

3.4 诊断信息处理与SQLCA结构

3.4.1 SQLCA结构

荣耀彩票代理SQLCADEQUANCHENGWEISQLTONGXINQU(Communication Area),SHIYINGYONGCHENGXUYONGLAIJIESHOUSHUJUKUGUANLIFANHUIDESQLZHIXINGQINGKUANGXINXIDESHUJUJIEGOU。

XIAMIANSHISQLCADECYUYANDINGYI:

SQL_STRUCTURE sqlca

{

荣耀彩票代理_SQLOLDCHAR sqlcaid[8]; /* Eyecatcher = 'SQLCA ' */

荣耀彩票代理sqlint32 sqlcabc; /* SQLCA size in bytes = 136 */

sqlint32 sqlcode; /* SQL return code */

荣耀彩票代理short sqlerrml; /* Length for SQLERRMC */

荣耀彩票代理_SQLOLDCHAR sqlerrmc[70]; /* Error message tokens */

_SQLOLDCHAR sqlerrp[8]; /* Diagnostic information */

sqlint32 sqlerrd[6]; /* Diagnostic information */

荣耀彩票代理_SQLOLDCHAR sqlwarn[11]; /* Warning flags */

_SQLOLDCHAR sqlstate[5]; /* State corresponding to SQLCODE */

};

荣耀彩票代理XIABIAOSHISQLCASHUJUJIEGOUDEXIANGXIMIAOSHU:

YUANSUMING

   

sqlcaid

sqlcabc

sqlcode

sqlerrml

sqlerrmc

sqlerrp

sqlerrd

sqlwarn

sqlstate

CHAR(8)

INTEGER

INTEGER

SMAIIINT

荣耀彩票代理VARCHAR(70)

CHAR(8)

荣耀彩票代理INTEGER ARRAY

荣耀彩票代理CHARACTER ARRAY

CHAR(5)

荣耀彩票代理YOUZHUYUCONGWAIGUANSHANGSHIBIEGAISHUJUDESHIJUE—BUZHUOQI。TAYINGDANGBAOHANZIFUCHUAN‘SQLCA’

荣耀彩票代理BAOHANSQLCADEZHANGDU。TAYINGDANGZONGSHIBAOHANZHI136

ZHEIKENENGSHISQLCAJIEGOUDEZUIZHONGYAOYUANSU。RUGUOSQLBEICHENGGONGDICHULI,ZHEIGEZHIBAOHANCUOWUDAIMAHUOZHESHILINGZHI。RUGUOGAIZHISHIZHENGSHU,NEIMEFANHUIJINGGAOXINXIBINGQIECHULISQLYUJU。RUGUOGAIZHISHIFUSHU,NEIMEYOUCUOWUXINXIFASHENGBINGQIEBUCHULISQLYUJU。RUGUOGAIZHISHILING(0),NEIMEMEIYOUCHUXIANCUOWUHEJINGGAOXINXIBINGQIECHULILESQLYUJU

荣耀彩票代理BAOHANZAIYUANSUsqlerrmcZHONGZIFUCHUANDEZHANGDU

包含用X’FF’间隔的一个到多个信息

延伸阅读:

Tag标签:   
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规