第一章 ESQL介绍
1.1 ESQL中的基本概念
1.2 ESQL程序的组成和运行
第二章
ESQL 程序的基本结构
2.1 程序首部
2.1.1DECLARE部分:(描述部分)
2.1.2 SQL通讯域
2.1.3 连接COBASE
CONNECT语句必须是ESQL程序中第一条可执行的SQL语句.
2.2 程序体
做数据操纵语言(DML),应用程序体也可以包含DDL语句,用来建立或定义数据结构,如表、视图或索引.在用户程序中写入的任何有效的SQL语句都可以被执行,只需要把完整的SQL语句按嵌入式的要求写入C语言的合适位置即可.嵌入在C语言中的SQL语句以EXEC SQL 开始,以";"为结束标志.SQL语句中可以嵌入主变量,主变量前应有":"标志.如下面例子都 是嵌入式SQL语句:
例题程序1
(建立一表并向表中插入数据)
EXEC SQL BEGIN DECLARE SECTION ;
CHAR sno[10],sname[10],city[10];
INT
status;
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
CHAR sno[10],sname[10],city[10];
INT
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
main()
{
int i;
EXEC SQL CONNECT "cobase:cobase" ;
{
int i;
EXEC SQL CONNECT "cobase:cobase" ;
EXEC SQL CREATE TABLE S_TEST
(SNO CHAR (9),
SNAME CHAR (20),
STATUS INT ,CITY CHAR(10));
printf(" create table success,insert?");
printf("
0 ---- no ");
printf("
1 ---- yes ");
printf("
choice:");
scanf("%d",&i);
while(i)
{
printf("input sno:");
scanf("%s",sno);
printf("input sname:");
scanf("%s",sname);
printf("input status:");
scanf("%d",&status);
printf("input city:");
scanf("%s",city);
EXEC SQL INSERT INTO S_TEST(SNO,SNAME,STATUS,CITY)
VALUES ((:sno,:sname,:status,:city));
printf("continue?");
printf("
0 ----terminate
");
printf("
1 ----continue
");
printf("
choice:");
scanf("%d",&i);
printf("
printf("
printf("
scanf("%d",&i);
while(i)
{
printf("input sno:");
scanf("%s",sno);
printf("input sname:");
scanf("%s",sname);
printf("input status:");
scanf("%d",&status);
printf("input city:");
scanf("%s",city);
EXEC SQL INSERT INTO S_TEST(SNO,SNAME,STATUS,CITY)
printf("continue?");
printf("
printf("
printf("
scanf("%d",&i);
}
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
exit(0);
}
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
exit(0);
}
例题程序2
EXEC SQL BEGIN DECLARE SECTION ;
CHAR sno[10],sname[10],city[10];
INT status;
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
CHAR sno[10],sname[10],city[10];
INT status;
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
main()
{
int i;
printf("Now exec connect ...");
EXEC SQL CONNECT "COBASE:COBASE" ;
{
int i;
printf("Now exec connect ...");
EXEC SQL CONNECT "COBASE:COBASE" ;
printf(" update?");
printf("
0 ---- no
");
printf("
1 ---- yes
");
printf("
choice:");
scanf("%d",&i);
while(i)
{
printf("input sno:");
scanf("%s",sno);
printf("input sname:");
scanf("%s",sname);
printf("input status:");
scanf("%d",&status);
printf("input city:");
scanf("%s",city);
printf("
printf("
printf("
scanf("%d",&i);
while(i)
{
printf("input sno:");
scanf("%s",sno);
printf("input sname:");
scanf("%s",sname);
printf("input status:");
scanf("%d",&status);
printf("input city:");
scanf("%s",city);
EXEC SQL UPDATE S_TEST SET sname=:sname,city=:city ,status=:status
WHERE sno=:sno;
printf(" continue?");
printf("
0 ----no
");
printf("
1 ----yes
");
printf("
choice:");
scanf("%d",&i);
}
printf(" delete?");
printf("
0 ---- no
");
printf("
1 ---- yes
");
printf("
choice:");
scanf("%d",&i);
while(i)
{
printf("input sno:");
scanf("%s",sno);
printf("
printf("
printf("
scanf("%d",&i);
}
printf(" delete?");
printf("
printf("
printf("
scanf("%d",&i);
while(i)
{
printf("input sno:");
scanf("%s",sno);
EXEC SQL DELETE FROM S_TEST WHERE sno=:sno;
printf(" continue?");
printf("
0 ----no
");
printf("
1 ----yes
");
printf("
chioce:");
scanf("%d",&i);
printf("
printf("
printf("
scanf("%d",&i);
}
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
exit(0);
}
EXEC SQL DISCONNECT;
exit(0);
}
3.1 SELECT 语句
SELECT语句是用于完成查询功能的SQL语句,查询语句因为有返回的结果,故ESQL中的SELECT语句比SQL的SELECT语句多一INTO子句,INTO子句的主变量表对应于程序中主变量,用于存放查询返回的结果.
SELECT语句格式如下:
SELECT语句格式如下:
执行该语句时,COBASE找出表中满足检索条件的行,并把结果传送到INTO子句中所对应的主变量中.该语句的查询结果可以是一行或多行.WHERE后的主变量叫输入主变量,它提供了查询所需的信息.INTO子句中的主变量叫输出主变量,它保存SELECT语句运行后的结果.
例题程序3 (返回一行的查询)
EXEC SQL BEGIN DECLARE SECTION ;
INT status;
CHAR sno[10],sname[10],city[10],isno[10];
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
main()
{
EXEC SQL CONNECT "cobase:cobase" ;
{
EXEC SQL CONNECT "cobase:cobase" ;
printf("input the sno for update:");
scanf("%s",sno);
EXEC SQL SELECT sno,sname,status,city
INTO :sno , :sname ,:status,:city
from S_TEST WHERE sno=:sno;
scanf("%s",sno);
EXEC SQL SELECT sno,sname,status,city
printf("sno: %s ;",sno);
printf("sname: %s ;",sname);
printf("status: %d ;",status);
printf("city: %s ;",city);
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
exit(0);
}
例题程序4 (返回多行的查询)
printf("sname: %s ;",sname);
printf("status: %d ;",status);
printf("city: %s ;",city);
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
exit(0);
}
例题程序4 (返回多行的查询)
EXEC SQL BEGIN DECLARE SECTION ;
INT status[10];
CHAR sno[10][10],sname[10][15],city[10][20],isno[10];
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
main()
{ int i;
EXEC SQL CONNECT "cobase:cobase" ;
for(i=0;i<10;i++)
{
strcpy(sno[i],"ttttt");
strcpy(sname[i],"ttttt");
strcpy(city[i],"ttttt");
status[i]=1000;
}
{
strcpy(sno[i],"ttttt");
strcpy(sname[i],"ttttt");
strcpy(city[i],"ttttt");
status[i]=1000;
}
EXEC SQL SELECT sno,sname,status,city
INTO :sno , :sname ,:status,:city
from S_TEST;
printf("sno sname
status
city");
for(i=0;i<10;i++)
{ printf("%8s %8s %8d %8s ",sno[i],sname[i],status[i],city[i]);
getchar();
}
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
}
for(i=0;i<10;i++)
{ printf("%8s %8s %8d %8s ",sno[i],sname[i],status[i],city[i]);
getchar();
}
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
}
3.2 游标的使用
3.2.1DECLARE CURSOR 定义游标语句:
3.2.2 OPEN CURSOR 打开游标语句
3.2.3 FETCH CURSOR 语句
3.2.4 CLOSE CURSOR 关闭游标语句
例题程序5
(使用游标的查询)
EXEC SQL BEGIN DECLARE SECTION ;
INT status;
CHAR sno[10],sname[15],city[20];
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
main()
{
int i;
printf("Now exec connect ...");
EXEC SQL CONNECT "cobase:cobase" ;
{
int i;
printf("Now exec connect ...");
EXEC SQL CONNECT "cobase:cobase" ;
printf("Now exec declare_open_fetch_close cursor ...");
EXEC SQL DECLARE cursor1 CURSOR FOR SELECT * FROM S_TEST;
EXEC SQL OPEN cursor1 ;
printf("sno sname
status
city\n ");
do
{
EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city ;
If (userca.sqlcode==2000) break;
printf("%8s %8s %8d %8s \n",sno,sname,status,city);
If (userca.sqlcode==2000) break;
printf("%8s %8s %8d %8s \n",sno,sname,status,city);
getchar();
}while(1);
EXEC SQL CLOSE cursor1 ;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
}
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
}
3.3 定位修改和删除语句
这些语句执行在游标名的当前行下更新或修改.其中在值表达式或子查询中出现的主变量前应有":"标志.
例题程序6
(定位删除)
EXEC SQL BEGIN DECLARE SECTION ;
INT status;
CHAR sno[10],sname[15],city[20];
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
main()
{
int i;
printf("Now exec connect ...");
EXEC SQL CONNECT "COBASE:COBASE" ;
{
int i;
printf("Now exec connect ...");
EXEC SQL CONNECT "COBASE:COBASE" ;
printf("Now exec declare cursor ...");
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT sno,sname,status,city FROM S_TEST FOR UPDATE;
printf("Now exec open cursor ...");
EXEC SQL OPEN cursor1;
printf("sno sname
status
city\n");
for(;;)
{
printf("Now exec fetch cursor ...\n");
EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city;
if (userca.sqlcode==2000) break;
printf("%8s %8s %8d %8s \n",sno,sname,status,city);
EXEC SQL DECLARE cursor1 CURSOR FOR
printf("Now exec open cursor ...");
EXEC SQL OPEN cursor1;
printf("sno sname
for(;;)
{
printf("Now exec fetch cursor ...\n");
EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city;
if (userca.sqlcode==2000) break;
printf("%8s %8s %8d %8s \n",sno,sname,status,city);
//Delete the first record that be fetched
printf(" delete current ?(0/1)");
scanf("%d",&i);
if (i==1)
EXEC SQL DELETE FROM S_TEST
}
EXEC SQL CLOSE cursor1;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
}
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
}
例题程序7
EXEC SQL BEGIN DECLARE SECTION ;
INT status;
CHAR sno[10],sname[15],city[20];
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
main()
{
int i;
{
int i;
strcpy(sno,"ttttt");
strcpy(sname,"ttttt");
strcpy(city,"ttttt");
status=1000;
EXEC SQL CONNECT "cobase:cobase" ;
strcpy(sname,"ttttt");
strcpy(city,"ttttt");
status=1000;
EXEC SQL CONNECT "cobase:cobase" ;
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT SNO, SNAME, STATUS,CITY FROM S_TEST FOR UPDATE;
EXEC SQL OPEN cursor1;
EXEC SQL OPEN cursor1;
printf("sno sname
status
city\n");
for(;;)
{
EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city;
if (userca.sqlcode==2000) break;
printf("%8s %8s %8d %8s \n",sno,sname,status,city);
printf(" update current ?(0/1)");
scanf("%d",&i);
if (i==1)
{printf("input sno=");
scanf("%s",sno);
printf("\ninput sname=");
scanf("%s",sname);
printf("\ninput status=");
scanf("%d",&status);
printf("\ninput city=");
scanf("%s",city);
EXEC SQL UPDATE
S_TEST SET sno=:sno,sname=:sname,status=:status,city=:city
WHERE CURRENT of cursor1;
EXEC SQL COMMIT;
for(;;)
{
EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city;
if (userca.sqlcode==2000) break;
printf("%8s %8s %8d %8s \n",sno,sname,status,city);
printf(" update current ?(0/1)");
scanf("%d",&i);
if (i==1)
{printf("input sno=");
scanf("%s",sno);
printf("\ninput sname=");
scanf("%s",sname);
printf("\ninput status=");
scanf("%d",&status);
printf("\ninput city=");
scanf("%s",city);
EXEC SQL UPDATE
EXEC SQL COMMIT;
}
}
EXEC SQL CLOSE cursor1;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
}
EXEC SQL CLOSE cursor1;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;