PRO*C 程序实例

7.1SELECT语句

使用SELECT语句,可以将从数据库中检索到的值赋给相应的目标变量。

数据库管理系统执行一个SELECT语句,实际上是导出查询说明对应的导出表。查询说明是SELECT语句中滤除INTO子句后的对应语句。如果导出表的基数为0(结果集为空集),则返回SQLCODE为100;如果基数大于1,则返回SQLCODE小于0;如果基数为1,则返回SQLCODE为0,相应的结果置于选择目标清单中。

如果查询结果多于一个元组,返回一个错误信息(SQLCODE<0),此时应该使用游标查询。

例如,在人员信息表中,查询人员编号为‘1’的姓名、联系电话。

EXEC SQL BEGIN DECLARE SECTION;

varchar person_name[50];

varchar person_phone[25];

varchar person_id[10];

EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT name, phone

INTO :person_name, :person_phone

FROM person.person WHERE personid ='1';

......

7.2插入、更新、删除语句

7.2.1插入语句

例如,设有另一表person_1(name, phone)。将人员信息表中人员编号为‘1’的姓名、联系电话插入到person_1中。

##include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

char username[19];

char password[19];

char servername[19];

EXEC SQL END DECLARE SECTION;

void main( )

{

printf("Input Username: ");

scanf("%s", username);

printf("Input Password: ");

scanf("%s", password);

printf(" Please input servername :");

scanf("%s",servername);

EXEC SQL LOGIN :username PASSWORD :password SERVER :servername;

EXEC SQL INSERT INTO person_1

SELECT name, phone FROM person.person

WHERE personid ='1';

EXEC SQL COMMIT WORK;

EXEC SQL LOGOUT;

}

7.2.2更新语句

更新的方式有两种,一种是游标定位更新,另一种是搜索更新。

游标定位更新对游标所指的当前行的某些属性值进行更新。当打开一个游标时,游标指针处于游标表的第一行之前。因而应先执行FETCH操作,再对当前数据进行更新。

例如,在人员信息表中,使用游标定位更新修改人员编号为‘1’的姓名和联系电话。

##include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

char username[19],password[19],servername[19];

varchar old_id[10],new_id[10];

varchar old_name[50],new_name[50];

varchar old_phone[25],new_phone[25];

EXEC SQL END DECLARE SECTION;

void main( )

{

strcpy(new_id,"1");

strcpy(new_name,"李丽1");

strcpy(new_phone,"01088548562");

printf("Input Username: ");

scanf("%s",username);

printf("Input Password: ");

scanf("%s",password);

printf(" Please input servername :");

scanf("%s",servername);

EXEC SQL LOGIN :username PASSWORD :password SERVER :servername;

EXEC SQL DECLARE C1 CURSOR FOR

SELECT name, phone

FROM person.person WHERE personid='1';

EXEC SQL OPEN C1;

EXEC SQL WHENEVER NOT FOUND GOTO endexit;

for(;;)

{

EXEC SQL FETCH C1 INTO:old_name,:old_phone;

printf("The current tuple is: %s %s", old_name,

old_phone);

......

/* 新的数值 new_no, new_name, new_addr */

EXEC SQL UPDATE person.person

SET name =:new_name, phone =:new_phone

WHERE CURRENT OF C1;

}

endexit:

EXEC SQL CLOSE C1;

EXEC SQL COMMIT WORK;

EXEC SQL LOGOUT;

}

例如,下面的例子使用搜索更新将人员编号为‘1’的元组的联系电话改为"01088548562"。

##include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

char username[19],password[19],servername[19];

EXEC SQL END DECLARE SECTION;

void main( )

{

printf("Input Username:");

scanf("%s",username);

printf("Input Password:");

scanf("%s",password);

printf(" Please input servername :");

scanf("%s",servername);

EXEC SQL LOGIN :username PASSWORD :password SERVER :servername;

EXEC SQL UPDATEperson.person

SET phone = '01088548562'

WHERE personid='1';

EXEC SQL COMMIT WORK;

EXEC SQL LOGOUT;

}

7.2.3删除语句

删除的方式有两种,一种是游标定位删除,另一种是搜索删除。

定位删除是指将游标所指的当前行删除。

例如,游标查询人员信息表,询问是否要删除元组。

......

EXEC SQL DECLARE C1 CURSOR FOR

SELECT name, phone FROM person.person ;

EXEC SQL OPEN C1;

EXEC SQL WHENEVER NOT FOUND GOTO endexit;

for(;;)

{

EXEC SQL FETCH C1 INTO :person_name,:person_phone;

printf(" Tuple is %s %s ", person_name, person_phone);

printf(" delete <Yes / No > ? ");

fflush(stdin);

scanf("%c",&select);

if(select==' y' || select=='Y')

EXEC SQL DELETE FROM person.person WHERE CURRENT OF C1;

}

endexit:

EXEC SQL CLOSE C1;

EXEC SQL COMMIT WORK;

EXEC SQL LOGOUT;

......

搜索删除是指对符合某种条件的元组进行删除操作。

例如,使用搜索删除将人员信息表中人员编号为‘1’的元组删除。

......

EXEC SQL DELETE FROM person.person WHERE personid='1';

EXEC SQL COMMIT WORK;

......

7.3日期、时间数据类型的使用

日期、时间数据类型用于存储日期和时间信息,虽然日期和时间信息可以表示成字符串和数值数据类型,但日期和时间类型具有特殊的关联特性。下面我们通过一个例子介绍日期、时间、时间间隔类型的使用。

例如:

##include <stdio.h>

##include <stdlib.h>

EXEC SQL BEGIN DECLARE SECTION;

char username[19],password[19],servername[19];

int year1,month1,day1;

EXEC SQL END DECLARE SECTION;

void main( )

{

printf(" Please input Servername :");

scanf("%s",servername);

printf("Input Username: ");

scanf("%s", username);

printf("Input Password: ");

scanf("%s",password);

EXEC SQL LOGIN :username PASSWORD :password SERVER :servername;

/* 建存储日期时间的基表 */

EXEC SQL CREATE TABLE DATETIME1(

COL1 DATE,

COL2 TIME(6),

COL3 TIME(6),

COL4 TIMESTAMP(6),

COL5 TIMESTAMP(6),

COL6 INTERVAL YEAR TO MONTH,

COL7 INTERVAL YEAR(4) TO MONTH,

COL8 INTERVAL SECOND(6),

COL9 INTERVAL YEAR,

COL10 INTERVAL HOUR,

COL11 INTERVAL DAY(3) TO HOUR,

COL12 INTERVAL HOUR(5) TO SECOND(6)

);

/* 插入日期、时间值 */

EXEC SQL INSERT INTO DATETIME1

VALUES(DATE '1998-06-12',

TIME '08:09:20.123456',

TIME '09:10:21.234567',

TIMESTAMP '1999-07-13 10:11:22.345678',

TIMESTAMP '2000-08-14 11:12:23.456789',

INTERVAL '0022-11'YEAR TO MONTH,

INTERVAL '2000'YEAR,

INTERVAL '21.12345' SECOND,

INTERVAL '0055'YEAR,

INTERVAL '15'HOUR,

INTERVAL '110 22'DAY TO HOUR,

INTERVAL '19:22:10. 654321'HOUR TO SECOND

);

/* 更新日期、时间值 */

EXEC SQL UPDATE DATETIME1

SET COL1=(INTERVAL '0010-10' YEAR TO MONTH - INTERVAL

'0020' YEAR)\*2+COL1;

/* 调用日期、时间函数 */

EXEC SQL SELECT EXTRACT(YEAR FROM COL1),

EXTRACT(MONTH FROM COL1),

EXTRACT(DAY FROM COL1)

INTO :year1,:month1,:day1

FROM DATETIME1;

printf("\n The day is %d-%d-%d \n",year1,month1,day1);

EXEC SQL COMMIT WORK;

EXEC SQL LOGOUT;

exit(0);

}

7.4多线程

6.7节中介绍了DM的PRO*C对多线程应用的支持,下面的例子生成了多个线程,这些线程使用各自的上下文环境并发执行。

##include <stdio.h>

##include <time.h>

##ifdef WIN32

##include <Windows.h>

##include <PROCESS.H>

##include <winbase.h>

typedef HANDLE os_thread_t;

typedef LPTHREAD_START_ROUTINE os_thread_fun_t;

##else

##include <pthread.h>

##endif

##define NUM_TH 10

struct parameters

{

    sql_context ctx;

    int personid;

};

typedef struct parameters parameters;

EXEC SQL INCLUDE sqlca;

void select_person(parameters *param);

EXEC SQL BEGIN DECLARE SECTION;

    char servername[50];

    char username[50];

    char pwd[50];

EXEC SQL END DECLARE SECTION;

void

CHECKOK()

{

    if(sqlca.sqlcode!=0)

    {

    	printf("fail! [%d] %s\n",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);

    }

}

void

main()

{

    int i;

    int rt;

    sql_context ctx[NUM_TH];

    parameters param[NUM_TH];

##ifdef WIN32

	DWORD code;

	os_thread_t thread_id[NUM_TH];

##else

	pthread_t thread_id[NUM_TH];

##endif

    printf("\n\n BEGIN proc027_main1 dynamic bind number \n");

    	EXEC SQL ENABLE THREADS;

    strcpy(username, "SYSDBA");

    strcpy(pwd, "SYSDBA");

    strcpy(servername, "192.168.0.89:5236");

    for(i=0;i<NUM_TH;i++)

    {

        EXEC SQL CONTEXT ALLOCATE :ctx[i];

        param[i].ctx=ctx[i];

        param[i].personid=i+1;

##ifdef WIN32

    thread_id[i] =
_beginthreadex(NULL,1024*1024,(os_thread_fun_t)select_person,&param[i],0,NULL);

##else

    rt = pthread_create(&thread_id[i],NULL,(void*)select_person,&param[i]);

##endif

    }

##ifdef WIN32

    while(TRUE)

    {

        for(i = 0; i < NUM_TH; i++)

        {

            rt = GetExitCodeThread(thread_id[i], &code);

            if(code == STILL_ACTIVE && rt != 0) //if there is alive thread, then sleep, and
            check again from beginning

            break;

        }

		Sleep(100);

        if(i == NUM_TH)

        	break;

	}

##else

    for(i=0;i<NUM_TH;i++)

    {

    	pthread_join(thread_id[i],NULL);

    }

##endif

	printf("\n\n END MAIN \n");

}

void

select_person(parameters *param)

{

EXEC SQL BEGIN DECLARE SECTION;

    int SQLCODE;

    char SQLSTATE[6];

    SQL_CONTEXT ctx;

    int personid;

    int personid_out;

    char name[51];

EXEC SQL END DECLARE SECTION;

    ctx=param->ctx;

    personid=param->personid;

	EXEC SQL CONTEXT USE :ctx;

/******************** BEGIN select_person 多线程并发 ********************/

        printf("\n BEGIN select_person \n");

        EXEC SQL CONNECT :username IDENTIFIED BY :pwd USING :servername;

	EXEC SQL SELECT personid,name into :personid_out,:name FROM person.person where
personid=:personid;

    CHECKOK();

    printf("personid_out=%d,name=%s namelen=%d\n",personid_out,name,strlen(name));

    EXEC SQL CLOSE C1;

    EXEC SQL COMMIT RELEASE;

    EXEC SQL CONTEXT FREE :ctx;

/******************** END select_person  ********************/

}
微信扫码
分享文档
扫一扫
联系客服