<!doctype html>
<html>
<head>
<meta charset='UTF-8'><meta name='viewport' content='width=device-width initial-scale=1'>
<title></title><link href='https://fonts.loli.net/css?family=Open+Sans:400italic,700italic,700,400&subset=latin,latin-ext' rel='stylesheet' type='text/css' /><style type='text/css'>html {overflow-x: initial !important;}:root { --bg-color:#ffffff; --text-color:#333333; --select-text-bg-color:#B5D6FC; --select-text-font-color:auto; --monospace:"Lucida Console",Consolas,"Courier",monospace; }
html { font-size: 14px; background-color: var(--bg-color); color: var(--text-color); font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; -webkit-font-smoothing: antialiased; }
body { margin: 0px; padding: 0px; height: auto; bottom: 0px; top: 0px; left: 0px; right: 0px; font-size: 1rem; line-height: 1.42857; overflow-x: hidden; background: inherit; tab-size: 4; }
iframe { margin: auto; }
a.url { word-break: break-all; }
a:active, a:hover { outline: 0px; }
.in-text-selection, ::selection { text-shadow: none; background: var(--select-text-bg-color); color: var(--select-text-font-color); }
#write { margin: 0px auto; height: auto; width: inherit; word-break: normal; overflow-wrap: break-word; position: relative; white-space: normal; overflow-x: visible; padding-top: 40px; }
#write.first-line-indent p { text-indent: 2em; }
#write.first-line-indent li p, #write.first-line-indent p * { text-indent: 0px; }
#write.first-line-indent li { margin-left: 2em; }
.for-image #write { padding-left: 8px; padding-right: 8px; }
body.typora-export { padding-left: 30px; padding-right: 30px; }
.typora-export .footnote-line, .typora-export li, .typora-export p { white-space: pre-wrap; }
@media screen and (max-width: 500px) {
body.typora-export { padding-left: 0px; padding-right: 0px; }
#write { padding-left: 20px; padding-right: 20px; }
.CodeMirror-sizer { margin-left: 0px !important; }
.CodeMirror-gutters { display: none !important; }
}
#write li > figure:last-child { margin-bottom: 0.5rem; }
#write ol, #write ul { position: relative; }
img { max-width: 100%; vertical-align: middle; }
button, input, select, textarea { color: inherit; font: inherit; }
input[type="checkbox"], input[type="radio"] { line-height: normal; padding: 0px; }
*, ::after, ::before { box-sizing: border-box; }
#write h1, #write h2, #write h3, #write h4, #write h5, #write h6, #write p, #write pre { width: inherit; }
#write h1, #write h2, #write h3, #write h4, #write h5, #write h6, #write p { position: relative; }
p { line-height: inherit; }
h1, h2, h3, h4, h5, h6 { break-after: avoid-page; break-inside: avoid; orphans: 2; }
p { orphans: 4; }
h1 { font-size: 2rem; }
h2 { font-size: 1.8rem; }
h3 { font-size: 1.6rem; }
h4 { font-size: 1.4rem; }
h5 { font-size: 1.2rem; }
h6 { font-size: 1rem; }
.md-math-block, .md-rawblock, h1, h2, h3, h4, h5, h6, p { margin-top: 1rem; margin-bottom: 1rem; }
.hidden { display: none; }
.md-blockmeta { color: rgb(204, 204, 204); font-weight: 700; font-style: italic; }
a { cursor: pointer; }
sup.md-footnote { padding: 2px 4px; background-color: rgba(238, 238, 238, 0.7); color: rgb(85, 85, 85); border-radius: 4px; cursor: pointer; }
sup.md-footnote a, sup.md-footnote a:hover { color: inherit; text-transform: inherit; text-decoration: inherit; }
#write input[type="checkbox"] { cursor: pointer; width: inherit; height: inherit; }
figure { overflow-x: auto; margin: 1.2em 0px; max-width: calc(100% + 16px); padding: 0px; }
figure > table { margin: 0px !important; }
tr { break-inside: avoid; break-after: auto; }
thead { display: table-header-group; }
table { border-collapse: collapse; border-spacing: 0px; width: 100%; overflow: auto; break-inside: auto; text-align: left; }
table.md-table td { min-width: 32px; }
.CodeMirror-gutters { border-right: 0px; background-color: inherit; }
.CodeMirror-linenumber { user-select: none; }
.CodeMirror { text-align: left; }
.CodeMirror-placeholder { opacity: 0.3; }
.CodeMirror pre { padding: 0px 4px; }
.CodeMirror-lines { padding: 0px; }
div.hr:focus { cursor: none; }
#write pre { white-space: pre-wrap; }
#write.fences-no-line-wrapping pre { white-space: pre; }
#write pre.ty-contain-cm { white-space: normal; }
.CodeMirror-gutters { margin-right: 4px; }
.md-fences { font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; overflow: visible; white-space: pre; background: inherit; position: relative !important; }
.md-diagram-panel { width: 100%; margin-top: 10px; text-align: center; padding-top: 0px; padding-bottom: 8px; overflow-x: auto; }
#write .md-fences.mock-cm { white-space: pre-wrap; }
.md-fences.md-fences-with-lineno { padding-left: 0px; }
#write.fences-no-line-wrapping .md-fences.mock-cm { white-space: pre; overflow-x: auto; }
.md-fences.mock-cm.md-fences-with-lineno { padding-left: 8px; }
.CodeMirror-line, twitterwidget { break-inside: avoid; }
.footnotes { opacity: 0.8; font-size: 0.9rem; margin-top: 1em; margin-bottom: 1em; }
.footnotes + .footnotes { margin-top: 0px; }
.md-reset { margin: 0px; padding: 0px; border: 0px; outline: 0px; vertical-align: top; background: 0px 0px; text-decoration: none; text-shadow: none; float: none; position: static; width: auto; height: auto; white-space: nowrap; cursor: inherit; -webkit-tap-highlight-color: transparent; line-height: normal; font-weight: 400; text-align: left; box-sizing: content-box; direction: ltr; }
li div { padding-top: 0px; }
blockquote { margin: 1rem 0px; }
li .mathjax-block, li p { margin: 0.5rem 0px; }
li { margin: 0px; position: relative; }
blockquote > :last-child { margin-bottom: 0px; }
blockquote > :first-child, li > :first-child { margin-top: 0px; }
.footnotes-area { color: rgb(136, 136, 136); margin-top: 0.714rem; padding-bottom: 0.143rem; white-space: normal; }
#write .footnote-line { white-space: pre-wrap; }
@media print {
body, html { border: 1px solid transparent; height: 99%; break-after: avoid; break-before: avoid; }
#write { margin-top: 0px; padding-top: 0px; border-color: transparent !important; }
.typora-export * { -webkit-print-color-adjust: exact; }
html.blink-to-pdf { font-size: 13px; }
.typora-export #write { padding-left: 32px; padding-right: 32px; padding-bottom: 0px; break-after: avoid; }
.typora-export #write::after { height: 0px; }
}
.footnote-line { margin-top: 0.714em; font-size: 0.7em; }
a img, img a { cursor: pointer; }
pre.md-meta-block { font-size: 0.8rem; min-height: 0.8rem; white-space: pre-wrap; background: rgb(204, 204, 204); display: block; overflow-x: hidden; }
p > .md-image:only-child:not(.md-img-error) img, p > img:only-child { display: block; margin: auto; }
p > .md-image:only-child { display: inline-block; width: 100%; }
#write .MathJax_Display { margin: 0.8em 0px 0px; }
.md-math-block { width: 100%; }
.md-math-block:not(:empty)::after { display: none; }
[contenteditable="true"]:active, [contenteditable="true"]:focus { outline: 0px; box-shadow: none; }
.md-task-list-item { position: relative; list-style-type: none; }
.task-list-item.md-task-list-item { padding-left: 0px; }
.md-task-list-item > input { position: absolute; top: 0px; left: 0px; margin-left: -1.2em; margin-top: calc(1em - 10px); border: none; }
.math { font-size: 1rem; }
.md-toc { min-height: 3.58rem; position: relative; font-size: 0.9rem; border-radius: 10px; }
.md-toc-content { position: relative; margin-left: 0px; }
.md-toc-content::after, .md-toc::after { display: none; }
.md-toc-item { display: block; color: rgb(65, 131, 196); }
.md-toc-item a { text-decoration: none; }
.md-toc-inner:hover { text-decoration: underline; }
.md-toc-inner { display: inline-block; cursor: pointer; }
.md-toc-h1 .md-toc-inner { margin-left: 0px; font-weight: 700; }
.md-toc-h2 .md-toc-inner { margin-left: 2em; }
.md-toc-h3 .md-toc-inner { margin-left: 4em; }
.md-toc-h4 .md-toc-inner { margin-left: 6em; }
.md-toc-h5 .md-toc-inner { margin-left: 8em; }
.md-toc-h6 .md-toc-inner { margin-left: 10em; }
@media screen and (max-width: 48em) {
.md-toc-h3 .md-toc-inner { margin-left: 3.5em; }
.md-toc-h4 .md-toc-inner { margin-left: 5em; }
.md-toc-h5 .md-toc-inner { margin-left: 6.5em; }
.md-toc-h6 .md-toc-inner { margin-left: 8em; }
}
a.md-toc-inner { font-size: inherit; font-style: inherit; font-weight: inherit; line-height: inherit; }
.footnote-line a:not(.reversefootnote) { color: inherit; }
.md-attr { display: none; }
.md-fn-count::after { content: "."; }
code, pre, samp, tt { font-family: var(--monospace); }
kbd { margin: 0px 0.1em; padding: 0.1em 0.6em; font-size: 0.8em; color: rgb(36, 39, 41); background: rgb(255, 255, 255); border: 1px solid rgb(173, 179, 185); border-radius: 3px; box-shadow: rgba(12, 13, 14, 0.2) 0px 1px 0px, rgb(255, 255, 255) 0px 0px 0px 2px inset; white-space: nowrap; vertical-align: middle; }
.md-comment { color: rgb(162, 127, 3); opacity: 0.8; font-family: var(--monospace); }
code { text-align: left; vertical-align: initial; }
a.md-print-anchor { white-space: pre !important; border-width: initial !important; border-style: none !important; border-color: initial !important; display: inline-block !important; position: absolute !important; width: 1px !important; right: 0px !important; outline: 0px !important; background: 0px 0px !important; text-decoration: initial !important; text-shadow: initial !important; }
.md-inline-math .MathJax_SVG .noError { display: none !important; }
.html-for-mac .inline-math-svg .MathJax_SVG { vertical-align: 0.2px; }
.md-math-block .MathJax_SVG_Display { text-align: center; margin: 0px; position: relative; text-indent: 0px; max-width: none; max-height: none; min-height: 0px; min-width: 100%; width: auto; overflow-y: hidden; display: block !important; }
.MathJax_SVG_Display, .md-inline-math .MathJax_SVG_Display { width: auto; margin: inherit; display: inline-block !important; }
.MathJax_SVG .MJX-monospace { font-family: var(--monospace); }
.MathJax_SVG .MJX-sans-serif { font-family: sans-serif; }
.MathJax_SVG { display: inline; font-style: normal; font-weight: 400; line-height: normal; zoom: 90%; text-indent: 0px; text-align: left; text-transform: none; letter-spacing: normal; word-spacing: normal; overflow-wrap: normal; white-space: nowrap; float: none; direction: ltr; max-width: none; max-height: none; min-width: 0px; min-height: 0px; border: 0px; padding: 0px; margin: 0px; }
.MathJax_SVG * { transition: none 0s ease 0s; }
.MathJax_SVG_Display svg { vertical-align: middle !important; margin-bottom: 0px !important; margin-top: 0px !important; }
.os-windows.monocolor-emoji .md-emoji { font-family: "Segoe UI Symbol", sans-serif; }
.md-diagram-panel > svg { max-width: 100%; }
[lang="mermaid"] svg, [lang="flow"] svg { max-width: 100%; height: auto; }
[lang="mermaid"] .node text { font-size: 1rem; }
table tr th { border-bottom: 0px; }
video { max-width: 100%; display: block; margin: 0px auto; }
iframe { max-width: 100%; width: 100%; border: none; }
.highlight td, .highlight tr { border: 0px; }
:root { --side-bar-bg-color: #fafafa; --control-text-color: #777; }
html { font-size: 16px; }
body { font-family: "Open Sans", "Clear Sans", "Helvetica Neue", Helvetica, Arial, sans-serif; color: rgb(51, 51, 51); line-height: 1.6; }
#write { max-width: 860px; margin: 0px auto; padding: 30px 30px 100px; }
#write > ul:first-child, #write > ol:first-child { margin-top: 30px; }
a { color: rgb(65, 131, 196); }
h1, h2, h3, h4, h5, h6 { position: relative; margin-top: 1rem; margin-bottom: 1rem; font-weight: bold; line-height: 1.4; cursor: text; }
h1:hover a.anchor, h2:hover a.anchor, h3:hover a.anchor, h4:hover a.anchor, h5:hover a.anchor, h6:hover a.anchor { text-decoration: none; }
h1 tt, h1 code { font-size: inherit; }
h2 tt, h2 code { font-size: inherit; }
h3 tt, h3 code { font-size: inherit; }
h4 tt, h4 code { font-size: inherit; }
h5 tt, h5 code { font-size: inherit; }
h6 tt, h6 code { font-size: inherit; }
h1 { padding-bottom: 0.3em; font-size: 2.25em; line-height: 1.2; border-bottom: 1px solid rgb(238, 238, 238); }
h2 { padding-bottom: 0.3em; font-size: 1.75em; line-height: 1.225; border-bottom: 1px solid rgb(238, 238, 238); }
h3 { font-size: 1.5em; line-height: 1.43; }
h4 { font-size: 1.25em; }
h5 { font-size: 1em; }
h6 { font-size: 1em; color: rgb(119, 119, 119); }
p, blockquote, ul, ol, dl, table { margin: 0.8em 0px; }
li > ol, li > ul { margin: 0px; }
hr { height: 2px; padding: 0px; margin: 16px 0px; background-color: rgb(231, 231, 231); border: 0px none; overflow: hidden; box-sizing: content-box; }
li p.first { display: inline-block; }
ul, ol { padding-left: 30px; }
ul:first-child, ol:first-child { margin-top: 0px; }
ul:last-child, ol:last-child { margin-bottom: 0px; }
blockquote { border-left: 4px solid rgb(223, 226, 229); padding: 0px 15px; color: rgb(119, 119, 119); }
blockquote blockquote { padding-right: 0px; }
table { padding: 0px; word-break: initial; }
table tr { border-top: 1px solid rgb(223, 226, 229); margin: 0px; padding: 0px; }
table tr:nth-child(2n), thead { background-color: rgb(248, 248, 248); }
table tr th { font-weight: bold; border-width: 1px 1px 0px; border-top-style: solid; border-right-style: solid; border-left-style: solid; border-top-color: rgb(223, 226, 229); border-right-color: rgb(223, 226, 229); border-left-color: rgb(223, 226, 229); border-image: initial; border-bottom-style: initial; border-bottom-color: initial; margin: 0px; padding: 6px 13px; }
table tr td { border: 1px solid rgb(223, 226, 229); margin: 0px; padding: 6px 13px; }
table tr th:first-child, table tr td:first-child { margin-top: 0px; }
table tr th:last-child, table tr td:last-child { margin-bottom: 0px; }
.CodeMirror-lines { padding-left: 4px; }
.code-tooltip { box-shadow: rgba(0, 28, 36, 0.3) 0px 1px 1px 0px; border-top: 1px solid rgb(238, 242, 242); }
.md-fences, code, tt { border: 1px solid rgb(231, 234, 237); background-color: rgb(248, 248, 248); border-radius: 3px; padding: 2px 4px 0px; font-size: 0.9em; }
code { background-color: rgb(243, 244, 244); padding: 0px 2px; }
.md-fences { margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; }
.md-task-list-item > input { margin-left: -1.3em; }
@media print {
html { font-size: 13px; }
table, pre { break-inside: avoid; }
pre { overflow-wrap: break-word; }
}
.md-fences { background-color: rgb(248, 248, 248); }
#write pre.md-meta-block { padding: 1rem; font-size: 85%; line-height: 1.45; background-color: rgb(247, 247, 247); border: 0px; border-radius: 3px; color: rgb(119, 119, 119); margin-top: 0px !important; }
.mathjax-block > .code-tooltip { bottom: 0.375rem; }
.md-mathjax-midline { background: rgb(250, 250, 250); }
#write > h3.md-focus::before { left: -1.5625rem; top: 0.375rem; }
#write > h4.md-focus::before { left: -1.5625rem; top: 0.285714rem; }
#write > h5.md-focus::before { left: -1.5625rem; top: 0.285714rem; }
#write > h6.md-focus::before { left: -1.5625rem; top: 0.285714rem; }
.md-image > .md-meta { border-radius: 3px; padding: 2px 0px 0px 4px; font-size: 0.9em; color: inherit; }
.md-tag { color: rgb(167, 167, 167); opacity: 1; }
.md-toc { margin-top: 20px; padding-bottom: 20px; }
.sidebar-tabs { border-bottom: none; }
#typora-quick-open { border: 1px solid rgb(221, 221, 221); background-color: rgb(248, 248, 248); }
#typora-quick-open-item { background-color: rgb(250, 250, 250); border-color: rgb(254, 254, 254) rgb(229, 229, 229) rgb(229, 229, 229) rgb(238, 238, 238); border-style: solid; border-width: 1px; }
.on-focus-mode blockquote { border-left-color: rgba(85, 85, 85, 0.12); }
header, .context-menu, .megamenu-content, footer { font-family: "Segoe UI", Arial, sans-serif; }
.file-node-content:hover .file-node-icon, .file-node-content:hover .file-node-open-state { visibility: visible; }
.mac-seamless-mode #typora-sidebar { background-color: var(--side-bar-bg-color); }
.md-lang { color: rgb(180, 101, 77); }
.html-for-mac .context-menu { --item-hover-bg-color: #E6F0FE; }
#md-notification .btn { border: 0px; }
.dropdown-menu .divider { border-color: rgb(229, 229, 229); }
.ty-preferences .window-content { background-color: rgb(250, 250, 250); }
.ty-preferences .nav-group-item.active { color: white; background: rgb(153, 153, 153); }
</style>
</head>
<body class='typora-export os-windows' >
<div id='write' class = 'is-node'><p><span> 第一章 ESQL介绍 </span></p><p><span> </span>
<span> 本章对ESQL做一概括介绍,主要讨论怎么使用ESQL、ESQL的基本的概念和定义、ESQL程序的各个部分和ESQL 程序中语句的类型. </span>
<span> SQL语言是非过程化语言,大部分语句的执行与其前面或后面的语句无关,而一些高级编程语言都是基于如循环,条件等结构的过程化语言,尽管SQL语言非常有力,但它却没有过程化能力.若把SQL语言嵌入到过程化的编程语言中,则利用这些结构,程序开发人员就能设计出更加灵活的应用系统,具有SQL语言和高级编程语言的良好特征,它将比单独使用SQL或C语言具有更强的功能和灵活性. </span>
<span> COBASE RDBMS提供两种工具在主语言中编程来存取COBASE数据库中的数据.即高级语言预编译程序接口(ESQL)和高级语言的函数调用接口(CCI).目前这些工具仅支持C语言. </span>
<span> COBASE RDBMS提供的ESQL工具把含有SQL语句的C程序转化为可存取和操纵COBASE数据库中数据的C程序,作为一编译器,ESQL把输入文件中的EXEC SQL 语句在输出文件中转化为适当的CCI函数调用.输出文件则可以正常的C程序的方式被编译、连接和执行. </span>
<span> </span></p><p><span> 1.1 ESQL中的基本概念 </span></p><p><span> ESQL中的基本概念主要有: </span>
<span> 1.嵌入的SQL语句: </span>
<span> 嵌入的SQL语句是指在应用程序中使用的SQL语句.该应用程序称作宿主程序,书写该程序的语言称作宿主语言.嵌入的SQL语句与交互式SQL语句在语法上没有太大的差别,只是嵌入式SQL语句在个别语句上有所扩充.如嵌入式SQL中的SELECT语句增加了INTO子句,以便与宿主语言变量打交道.此外,嵌入式SQL为适合程序设计语言的要求,还增加了许多语句,如游标的定义、打开和关闭语句等等. </span>
<span> 2.执行性SQL语句和说明性SQL语句: </span>
<span> 嵌入的SQL语句主要有两种类型:执行性SQL语句和说明性SQL语句.执行性SQL语句可用来连接COBASE,定义、查询和操纵COBASE数据库中的数据,每一执行性语句真正对数据库进行操作 ,执行完成后,在USERCA中存放执行信息.说明性语句用来说明通讯域和SQL语句中用到的变量. 说明性语句不生成执行代码,对USERCA不产生影响. </span>
<span> 3.事务: </span>
<span> 事务是逻辑上相关的一组SQL语句.COBASE把它们视作一个单元.为了保持数据库的一致性,一事务内的所有操作要么都做,要么都不做. </span></p><p><span> </span>
<span> 1.2 ESQL程序的组成和运行 </span></p><p><span> 在ESQL程序中嵌入的SQL语句以EXEC作为起始标识,语句的结束以";"作为标识.在嵌入的SQL语句可以使用主语言(这时是C语言)的程序变量(即主变量),这时主变量名前加冒号(:)作为标志,以区别于字段名. </span>
<span> ESQL程序包括两部分:程序首部和程序体.程序首部定义变量,为ESQL程序做准备, 程序体包括各种SQL语句来操纵COBASE数据库中的数据. </span>
<span> 编制并运行ESQL程序比单独使用纯C语言多一个预编译过程,通常具有以下几个步骤: </span>
<span> 1.编辑ESQL程序(可利用编辑软件如: EDLIN, WS等进行编辑).程序保后缀为.ec. </span>
<span> 2.使用COBASE的预编译器ETE对ESQL源程序进行预处理,该编译器将源程序中嵌入的SQL语言翻译成标准C语言,产生一个C语言编译器能直接进 行编译的文件.其文件的扩展名为.cpp。该cpp文件可以和普通的cpp文件一样被放入一个工程中被C++编译器编译，连接最后运行。 </span></p><p><span> 对COBASE的预编译器的使用的详细说明见第六章. </span></p><p><span> 第二章 ESQL 程序的基本结构 </span></p><p><span> </span>
<span> ESQL程序由两部分组成:程序首部和程序体. </span></p><p><span> </span>
<span> 2.1 程序首部 </span></p><p><span> 每一个ESQL程序的开始,就是程序的首部,它包括以下三部分: </span>
<span> 1.DECLARE部分: </span>
<span> 说明特殊的主变量,这些变量区别于纯C语言程序中的变量,COBASE使用这些变量与程序之间相互作用. </span>
<span> 2.INCLUDE USERCA语句: 说明一个SQL语句的通讯域(USERCA),它提供了错误处理,其功能等价于代替C语言中的#INCLUDE语句. </span>
<span> 3.CONNECT语句: 建立程序与COBASE之间的连接. </span></p><p><span> </span>
<span> 2.1.1DECLARE部分:(描述部分) </span></p><p><span> 在DECLARE SECTION (描述部分),定义所有在SQL语句中用到的主变量,定义部分是以: </span></p><p><span> EXEC SQL BEGIN DECLARE SECTION; </span></p><p><span> 和 EXEC SQL END DECLARE SECTION; </span>
<span> 开始和结束的. </span>
<span> 在这两个语句中,只可以定义 SQL 语句中用到的主变量,每个预编译单元只允许一个BEGIN/END DECLARE SECTION (描述部分),但是一个程序可以包含许多独立的预编译单元。若一个主变量或指示变量在ESQL程序中的SQL语句中引用,但它没有在 描述部分中定义,则程序在预编译时就会出现错误信息. </span>
<span> 在这两个语 句中可以定义的变量有六种类型:INT,SHORT,CHAR,FLOAT,NUMBER,DATE.其中CHAR型允许定义二维数组,其它类型只允许定义一维数组,不允许有指针类型 ,在此处变量可以赋值. </span>
<span> 例如: </span>
<span> EXEC SQL BEGIN DECLARE SECTION; </span>
<span> int sno; </span>
<span> char sname[11]; </span>
<span> short snameid; </span>
<span> EXEC SQL END DECLARE SECTION; </span><br/><span> (1)主变量: </span>
<span> 就是用在SQL语句中的主语言变量.主要用于程序变量和数据库内部交换数据之用,它们的数据类型必须是在主语言中描述部分里定义过的,而且它们的数据类型必须和COBASE数据库中已定义的表中的数据类型相匹配. </span>
<span> 例如: </span>
<span> SELECT 姓名, 等级 </span>
<span> FROM 供应商 </span>
<span> INTO :sname, :status </span>
<span> WHERE 供应商号=:sno; </span>
<span> 该语句表示,从供应商表中在供应商号与主变量sno一致的地方选择供应商姓名和供应商等级,COBASE把结果传送到主变量sname,status中. </span>
<span> 主变量使用规则: </span>
<span> 1.必须在描述部分明确定义. </span>
<span> 2.必须使用与其定义相同的大小写格式. </span>
<span> 3.在SQL语句中使用主变量时必须在主变量前写一个冒号":",在纯C语言 语句中则不要在主变量前写冒号. </span>
<span> 4.不能是SQL命令的保 留字. </span>
<span> 5.在一条语句中只能使用一次. </span>
<span> </span><br/><span> 2.1.2 SQL通讯域 </span></p><p><span> 每个COBASE应用程序必须提供对错误 的处理,为了说明SQL通讯域(USERCA),必须在每个COBASE预编译程序中写上: </span></p><p><span> EXEC SQL INCLUDE USERCA; </span></p><p><span> USERCA是一结构,每一嵌入的执行性SQL语言的执行情况在其执行完成后写入USERCA结构中的各变量中, 根据USERCA中的内容可以获得每一嵌入SQL语句执行后的信息,编制程序时就可以做适当的处理.对其的详细说明见第五章. </span></p><p><span> </span>
<span> 2.1.3 连接COBASE </span></p><p><span> 在 存取COBASE数据之前,每一个预编译程序必须与COBASE连接.连接时,程序必须提供用户名和口令,由COBASE进行校验,若口令和用户名正确,方可登录COBASE,获得使用权,否则,COBASE拒绝登录,则程序就不能使用数据库.缺省的用户名为“cobase”，口令为“cobase”。 </span>
<span> 连接COBASE的格式如下: </span></p><p><span> EXEC SQL CONNECT <用户名>:<用户口令>. </span>
<span> CONNECT语句必须是ESQL程序中第一条可执行的SQL语句. </span></p><p><span> </span>
<span> 2.2 程序体 </span></p><p><span> 程序体可以包含许多SQL语句,以查询或处理存储在COBASE数据库中的数据. </span>
<span> 在应用程序中所包含的SQL语句,可以查询或操纵存储在COBASE中的数据,这些语句叫 </span>
<span> 做数据操纵语言(DML),应用程序体也可以包含DDL语句,用来建立或定义数据结构,如表、视图或索引.在用户程序中写入的任何有效的SQL语句都可以被执行，只需要把完整的SQL语句按嵌入式的要求写入C语言的合适位置即可.嵌入在C语言中的SQL语句以EXEC SQL 开始,以";"为结束标志.SQL语句中可以嵌入主变量,主变量前应有":"标志.如下面例子都 是嵌入式SQL语句: </span></p><p><span> EXEC SQL UPDATE 供应商 </span>
<span> SET 姓名='李 红' </span>
<span> WHERE 供应商号='S1'; </span>
<span> </span></p><p><span> EXEC SQL INSERT INTO 供应商(供应商号,姓名,等级,城市) </span>
<span> VALUES((:sno,:sname,:status,:city)); </span>
<span> </span><br/><span></span><br/><span> EXEC SQL DELETE FROM 供应商 </span>
<span> WHERE 等级 IS NULL; </span></p><p><span> </span></p><p><span> 从上面例可以看出,静态的增,删,改语句与交互方式没有太大的差别,对于查询语句就没有那么简单,查询语句在下一章中介绍. </span></p><p><span> 现用几个例题程序加以说明. </span></p><p><span> 例题程序1 (建立一表并向表中插入数据) </span></p><p><span> </span>
<span> </span></p><p><span> EXEC SQL BEGIN DECLARE SECTION ; </span>
<span> CHAR sno[10],sname[10],city[10]; </span>
<span> INT status; </span>
<span> EXEC SQL END DECLARE SECTION ; </span>
<span> EXEC SQL INCLUDE USERCA; </span></p><p><span> main() </span>
<span> { </span>
<span> int i; </span>
<span> EXEC SQL CONNECT "cobase:cobase" ; </span></p><p><span> EXEC SQL CREATE TABLE S_TEST </span><br/><span> (SNO CHAR (9), </span>
<span> SNAME CHAR (20), </span>
<span> STATUS INT ,CITY CHAR(10)); </span></p><p><span> printf(" create table success,insert?"); </span>
<span> printf(" 0 ---- no "); </span>
<span> printf(" 1 ---- yes "); </span>
<span> printf(" choice:"); </span>
<span> scanf("%d",&i); </span>
<span> while(i) </span>
<span> { </span>
<span> printf("input sno:"); </span>
<span> scanf("%s",sno); </span>
<span> printf("input sname:"); </span>
<span> scanf("%s",sname); </span>
<span> printf("input status:"); </span>
<span> scanf("%d",&status); </span>
<span> printf("input city:"); </span>
<span> scanf("%s",city); </span>
<span> EXEC SQL INSERT INTO S_TEST(SNO,SNAME,STATUS,CITY) </span>
<span> VALUES ((:sno,:sname,:status,:city)); </span>
<span> printf("continue?"); </span>
<span> printf(" 0 ----terminate "); </span>
<span> printf(" 1 ----continue "); </span>
<span> printf(" choice:"); </span>
<span> scanf("%d",&i); </span></p><p><span> } </span>
<span> EXEC SQL COMMIT; </span>
<span> EXEC SQL DISCONNECT; </span><br/><span> exit(0); </span>
<span> } </span></p><p><span> </span>
<span> 例题程序2 (修改和删除表中的数据) </span></p><p><span> EXEC SQL BEGIN DECLARE SECTION ; </span>
<span> CHAR sno[10],sname[10],city[10]; </span>
<span> INT status; </span>
<span> EXEC SQL END DECLARE SECTION ; </span>
<span> EXEC SQL INCLUDE USERCA; </span></p><p><span> main() </span>
<span> { </span>
<span> int i; </span>
<span> printf("Now exec connect ..."); </span>
<span> EXEC SQL CONNECT "COBASE:COBASE" ; </span></p><p><span> printf(" update?"); </span>
<span> printf(" 0 ---- no "); </span>
<span> printf(" 1 ---- yes "); </span>
<span> printf(" choice:"); </span>
<span> scanf("%d",&i); </span>
<span> while(i) </span>
<span> { </span>
<span> printf("input sno:"); </span>
<span> scanf("%s",sno); </span>
<span> printf("input sname:"); </span>
<span> scanf("%s",sname); </span>
<span> printf("input status:"); </span>
<span> scanf("%d",&status); </span>
<span> printf("input city:"); </span>
<span> scanf("%s",city); </span></p><p><span> EXEC SQL UPDATE S_TEST SET sname=:sname,city=:city ,status=:status </span>
<span> WHERE sno=:sno; </span></p><p><span> printf(" continue?"); </span>
<span> printf(" 0 ----no "); </span>
<span> printf(" 1 ----yes "); </span>
<span> printf(" choice:"); </span>
<span> scanf("%d",&i); </span>
<span> } </span>
<span> printf(" delete?"); </span>
<span> printf(" 0 ---- no "); </span>
<span> printf(" 1 ---- yes "); </span>
<span> printf(" choice:"); </span>
<span> scanf("%d",&i); </span>
<span> while(i) </span>
<span> { </span>
<span> printf("input sno:"); </span>
<span> scanf("%s",sno); </span></p><p><span> EXEC SQL DELETE FROM S_TEST WHERE sno=:sno; </span></p><p><span> printf(" continue?"); </span>
<span> printf(" 0 ----no "); </span>
<span> printf(" 1 ----yes "); </span>
<span> printf(" chioce:"); </span>
<span> scanf("%d",&i); </span></p><p><span> } </span></p><p><span> EXEC SQL COMMIT; </span>
<span> EXEC SQL DISCONNECT; </span>
<span> exit(0); </span>
<span> } </span></p><p><span> </span>
<span> 第三章 查 询 </span></p><p><span> </span>
<span> 在ESQL程序中,查询可以分为两大类型:返回一行的查询和返回多行的查询.对于查询,我们不仅对其执行得成功与否感兴趣,其结果更为有用.多行查询要用到游标的概念,本章就介绍查询语句和游标的概念和使用. </span></p><p><span> </span>
<span> 3.1 SELECT 语句 </span></p><p><span> SELECT语句是用于完成查询功能的SQL语句,查询语句因为有返回的结果,故ESQL中的SELECT语句比SQL的SELECT语句多一INTO子句,INTO子句的主变量表对应于程序中主变量,用于存放查询返回的结果. </span>
<span> SELECT语句格式如下: </span></p><p><span> EXEC SQL SELECT <列名> [,<列名>[,...]] INTO <主变量表> </span>
<span> FROM <表名> [,<表名>[,...]] [WHERE <检索条件>]; </span></p><p><span> 其中:1.检索条件中允许有主变量和嵌套子查询语句. </span>
<span> 2.INTO后的主变量可以是数组. </span>
<span> 3.主变量前要用":"标志. </span></p><p><span> 执行该语句时,COBASE找出表中满足检索条件的行,并把结果传送到INTO子句中所对应的主变量中.该语句的查询结果可以是一行或多行.WHERE后的主变量叫输入主变量,它提供了查询所需的信息.INTO子句中的主变量叫输出主变量,它保存SELECT语句运行后的结果. </span></p><p><span> 例题程序3 (返回一行的查询) </span></p><p><span> </span>
<span> EXEC SQL BEGIN DECLARE SECTION ; </span>
<span> INT status; </span>
<span> CHAR sno[10],sname[10],city[10],isno[10]; </span>
<span> EXEC SQL END DECLARE SECTION ; </span>
<span> EXEC SQL INCLUDE USERCA; </span></p><p><span> main() </span>
<span> { </span>
<span> EXEC SQL CONNECT "cobase:cobase" ; </span></p><p><span> printf("input the sno for update:"); </span>
<span> scanf("%s",sno); </span>
<span> EXEC SQL SELECT sno,sname,status,city </span>
<span> INTO :sno , :sname ,:status,:city </span>
<span> from S_TEST WHERE sno=:sno; </span></p><p><span> printf("sno: %s ;",sno); </span>
<span> printf("sname: %s ;",sname); </span>
<span> printf("status: %d ;",status); </span>
<span> printf("city: %s ;",city); </span>
<span> EXEC SQL COMMIT; </span>
<span> EXEC SQL DISCONNECT; </span>
<span> exit(0); </span>
<span></span><br/><span> } </span>
<span> 例题程序4 (返回多行的查询) </span></p><p><span> </span>
<span> </span></p><p><span> </span>
<span> EXEC SQL BEGIN DECLARE SECTION ; </span>
<span> INT status[10]; </span>
<span> CHAR sno</span>[<span>10</span>][10]<span>,sname</span>[<span>10</span>][15]<span>,city</span>[<span>10</span>][20]<span>,isno[10]; </span>
<span> EXEC SQL END DECLARE SECTION ; </span>
<span> EXEC SQL INCLUDE USERCA; </span></p><p><span> main() </span></p><p><span> { int i; </span></p><p><span> EXEC SQL CONNECT "cobase:cobase" ; </span></p><p><span> for(i=0;i<10;i++) </span>
<span> { </span>
<span> strcpy(sno[i],"ttttt"); </span>
<span> strcpy(sname[i],"ttttt"); </span>
<span> strcpy(city[i],"ttttt"); </span>
<span> status[i]=1000; </span>
<span> } </span></p><p><span> EXEC SQL SELECT sno,sname,status,city </span>
<span> INTO :sno , :sname ,:status,:city </span>
<span> from S_TEST; </span></p><p><span> printf("sno sname status city"); </span>
<span> for(i=0;i<10;i++) </span>
<span> { printf("%8s %8s %8d %8s ",sno[i],sname[i],status[i],city[i]); </span>
<span> getchar(); </span>
<span> } </span>
<span> EXEC SQL COMMIT; </span>
<span> EXEC SQL DISCONNECT; </span>
<span> } </span></p><p><span> </span>
<span> 3.2 游标的使用 </span></p><p><span> 我们知道SQL语言是一种面向集合的语言,而普通的高级语言则是面向记录的语言,要想使SQL语言能与宿主语言通讯,ESQL提供了游标操作,能把SQL的集合操作结果,按单个记录方式取出,赋于主变量进行进一步的处理. </span>
<span> 如果查询结果返回多行或不知返回多少行,就可使用带游标的SELECT语句.一个游标(CURSOR)是一个COBASE和ESQL使用的工作区域,COBASE使用这个工作区存放着一个查询结果 .一个已命名的游标是和一条SELECT语句相关联.一个游标必须首先定义(同一个查询相关联),然后用三条可运行的SQL语句使用游标,以操纵数据.四条操纵游标的命令如下: </span></p><p><span> .DECLRE CURSOR </span>
<span> .OPEN CURSOR </span>
<span> .FETCH </span>
<span> .CLOSE CURSOR </span></p><p><span> DECLARE CURSOR 语句用来定义一游标,此时游标处于关闭状态.用OPEN CURSOR语句打开游标后,就可用它从相关的查询中取出多于一行的结果.所有满足查询条件的行组成一个集合,叫做游标活动集.通过FETCH取操作,活动集中的每一行可以一个一个的返回,当查询作完后,游标就可以用CLOSE CURSOR 语句关闭. </span></p><p><span> 3.2.1DECLARE CURSOR 定义游标语句: </span></p><p><span> ESQL中的DECLARE CURSOR语句定义游标,赋给它一个与查询相关的游标名.该语句的格式为: </span></p><p><span> EXEC SQL DECLARE <游标名> CURSOR FOR </span>
<span> <SELECT 语句> [FOR UPDATE]; </span>
<span> 其中:(1)SELECT语句应不含INTO子句. </span>
<span> (2)若无FOR UPDATE 则无法 执行UPDATE(定位)和DELETE(定位)语句. </span>
<span> </span></p><p><span> 定义游标的DECLARE语句必须出现在程序中对游标进行操作的所有语句之前, ESQL不能引 用没有说明的游标,游标的定义范围是整个程序.程序中可包含多个DECLARE语 句,这些语句定义了不同的游标,并把游标与不同的查询联系在一起,所以在同一个程序中的两个DECLARE语句中不能说明同一个游标名. </span></p><p><span> 3.2.2 OPEN CURSOR 打开游标语句 </span></p><p><span> ESQL中OPEN CURSOR语句格式如下: </span>
<span> </span><br/><span> EXEC SQL OPEN <游标名>; </span></p><p><span> </span><br/><span> OPEN语句决定了满足查询的行的集合,游标处于打开状态,它的活动集就是满足WHERE子句条件的行的集合.这时,游标处在活动集的第一行的 前面. </span></p><p><span> </span>
<span> 3.2.3 FETCH CURSOR 语句 </span></p><p><span> ESQL中的FETCH CURSOR语句读出活动集中的行,并把结果送到输出主变量,输出主变量是在相关的FETCH语句中定义的.其 格式如下: </span></p><p><span> </span><br/><span> EXEC SQL FETCH <游标名> INTO <主变量表>; </span>
<span></span><br/><span> 游标必须先定义,然后再打开,只有当游标处于打开状态时,才执行FETCH语句.在第一次运行FETCH时,游标从活动集的第一行前移到当前第一行,使这一行成为当前行.每次运行FETCH时游标在活动集中向前移,把选出的结果送到主变量表中指定的输出主变量中. </span>
<span> 如果游标活动集中是空的,或所有的行已经被取走,COBASE就返回一代码.(USERCA.SQLCODE==2000). </span>
<span> 游标只可在活动集中向前移动,COBASE无法取到已经用FETCH取过的行,要想再取这一行,就必须关闭游标,再重新打开它. </span></p><p><span> </span>
<span> 3.2.4 CLOSE CURSOR 关闭游标语句 </span></p><p><span> 当取完活动集中所有行后,必须关闭游标,以释放与该游标的关的资源.其格式如下: </span></p><p><span> EXEC SQL CLOSE <游 标名>; </span></p><p><span> 例题程序5 (使用游标的查询) </span></p><p><span> </span>
<span> EXEC SQL BEGIN DECLARE SECTION ; </span>
<span> INT status; </span>
<span> CHAR sno[10],sname[15],city[20]; </span>
<span> EXEC SQL END DECLARE SECTION ; </span>
<span> EXEC SQL INCLUDE USERCA; </span></p><p><span> main() </span>
<span> { </span>
<span> int i; </span>
<span> printf("Now exec connect ..."); </span>
<span> EXEC SQL CONNECT "cobase:cobase" ; </span></p><p><span> printf("Now exec declare_open_fetch_close cursor ..."); </span>
<span> </span></p><p><span> EXEC SQL DECLARE cursor1 CURSOR FOR SELECT * FROM S_TEST; </span></p><p><span> EXEC SQL OPEN cursor1 ; </span></p><p><span> printf("sno sname status city\n "); </span></p><p><span> </span>
<span> do </span>
<span> { </span></p><p><span> EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city ; </span>
<span> If (userca.sqlcode==2000) break; </span>
<span> printf("%8s %8s %8d %8s \n",sno,sname,status,city); </span></p><p><span> getchar(); </span></p><p><span> </span><br/><span> }while(1); </span></p><p><span> EXEC SQL CLOSE cursor1 ; </span>
<span> EXEC SQL COMMIT; </span><br/><span> EXEC SQL DISCONNECT; </span><br/><span> } </span></p><p><span> 3.3 定位修改和删除语句 </span></p><p><span> COBASE支持SQL格式" CURRENT OF CURSOR".这条语句将指向一个游标中最新取出的行,以用于修改和删除操作.该语句必须在取操作之后使用 ,它等同于存储一个ROWID,并使用它.其格式如下: </span></p><p><span> (1) EXEC SQL </span>
<span> UPDATE <表名> </span>
<span> SET <列名> = <值表达式> | NULL </span>
<span> [,<列名> = <值表达式> | NULL ....] </span>
<span> WHERE CURRENT OF <游标名> ; </span>
<span> (2) EXEC SQL </span>
<span> UPDATE <表名> </span>
<span> SET ( <列名表> ) = ( <子查询> ) </span>
<span> WHERE CURRENT OF <游标名> ; </span>
<span> (3) EXEC SQL DELETE FROM <表名> </span>
<span> WHERE CURRENT OF <游标名> ; </span></p><p><span> 这些语句执行在游标名的当前行下更新或修改.其中在值表达式或子查询中出现的主变量前应有":"标志. </span></p><p><span> 例题程序6 (定位删除) </span></p><p><span> </span>
<span> EXEC SQL BEGIN DECLARE SECTION ; </span>
<span> INT status; </span>
<span> CHAR sno[10],sname[15],city[20]; </span>
<span> EXEC SQL END DECLARE SECTION ; </span>
<span> EXEC SQL INCLUDE USERCA; </span></p><p><span> main() </span>
<span> { </span>
<span> int i; </span>
<span> printf("Now exec connect ..."); </span>
<span> EXEC SQL CONNECT "COBASE:COBASE" ; </span></p><p><span> printf("Now exec declare cursor ..."); </span>
<span> EXEC SQL DECLARE cursor1 CURSOR FOR </span>
<span> SELECT sno,sname,status,city FROM S_TEST FOR UPDATE; </span>
<span> printf("Now exec open cursor ..."); </span>
<span> EXEC SQL OPEN cursor1; </span>
<span> printf("sno sname status city\n"); </span>
<span> for(;;) </span>
<span> { </span>
<span> printf("Now exec fetch cursor ...\n"); </span>
<span> EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city; </span>
<span> if (userca.sqlcode==2000) break; </span>
<span> printf("%8s %8s %8d %8s \n",sno,sname,status,city); </span></p><p><span> </span>
<span> //Delete the first record that be fetched </span>
<span> printf(" delete current ?(0/1)"); </span>
<span> scanf("%d",&i); </span>
<span> if (i==1) </span>
<span> EXEC SQL DELETE FROM S_TEST </span>
<span> WHERE CURRENT of cursor1; </span>
<span> } </span></p><p><span> EXEC SQL CLOSE cursor1; </span>
<span> EXEC SQL COMMIT; </span>
<span> EXEC SQL DISCONNECT; </span>
<span> } </span></p><p><span> </span>
<span> 例题程序7 (定位修改) </span></p><p><span> </span>
<span> </span></p><p><span> </span>
<span> EXEC SQL BEGIN DECLARE SECTION ; </span>
<span> INT status; </span>
<span> CHAR sno[10],sname[15],city[20]; </span>
<span> EXEC SQL END DECLARE SECTION ; </span>
<span> EXEC SQL INCLUDE USERCA; </span></p><p><span> main() </span>
<span> { </span>
<span> int i; </span></p><p><span> strcpy(sno,"ttttt"); </span>
<span> strcpy(sname,"ttttt"); </span>
<span> strcpy(city,"ttttt"); </span>
<span> status=1000; </span>
<span> EXEC SQL CONNECT "cobase:cobase" ; </span></p><p><span> EXEC SQL DECLARE cursor1 CURSOR FOR </span>
<span> SELECT SNO, SNAME, STATUS,CITY FROM S_TEST FOR UPDATE; </span>
<span> EXEC SQL OPEN cursor1; </span></p><p><span> printf("sno sname status city\n"); </span>
<span> for(;;) </span>
<span> { </span>
<span> EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city; </span>
<span> if (userca.sqlcode==2000) break; </span>
<span> printf("%8s %8s %8d %8s \n",sno,sname,status,city); </span>
<span></span><br/><span> printf(" update current ?(0/1)"); </span>
<span> scanf("%d",&i); </span>
<span> if (i==1) </span>
<span> {printf("input sno="); </span>
<span> scanf("%s",sno); </span>
<span> printf("\ninput sname="); </span>
<span> scanf("%s",sname); </span>
<span> printf("\ninput status="); </span>
<span> scanf("%d",&status); </span>
<span> printf("\ninput city="); </span>
<span> scanf("%s",city); </span>
<span> EXEC SQL UPDATE S_TEST SET sno=:sno,sname=:sname,status=:status,city=:city </span>
<span> WHERE CURRENT of cursor1; </span>
<span> EXEC SQL COMMIT; </span></p><p><span> } </span>
<span> } </span>
<span> EXEC SQL CLOSE cursor1; </span>
<span> EXEC SQL COMMIT; </span>
<span> EXEC SQL DISCONNECT; </span></p></div>
</body>
</html>
ESQL看这一篇写的不错
于 2022-06-07 07:14:03 首次发布
454
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
