在使用proc访问数据库时,往往需要批量读取数据库数据,这就需要用到cursor,即游标。
他的基本形式是:
1.声明:
EXEC SQL WHENEVER NOTFOUND DO setNotFound();
EXEC SQL DECLARE 游标名 CURSOR FOR
select .....from .....
2.调用:
EXEC SQL OPEN 游标名;
while(true)
{
获得数据;
if(seNotFound())
{
EXEC SQL CLOSE 游标名称;
}
}
例如:
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL WHENEVER NOTFOUND DO setNotFound();
EXEC SQL DECLARE CURGetpt_ftp_information CURSOR FOR
SELECT NVL(JOB_NAME,'XXX'),
NVL(LOCAL_FTP_DIRECTORY,'xxx'),
NVL(LOCAL_BACKUP_DIRECTORY,'xxx'),
NVL(REMOTE_IP,'_ _ _'),
NVL(REMOTE_DIRECTORY,'xxx'),
NVL(FTP_ACCOUNT,'xxx'),
NVL(FTP_PASSWORD,'xxx'),
NVL(ADDITIONAL_1,'xxx')
FROM pt_ftp_information
WHERE FTP_IND=:h_szftpind;
EXEC SQL OPEN CURGetpt_ftp_information;
while(true)
{
EXEC SQL FETCH CURGetpt_ftp_information
into :h_szJobName,
:h_szLocal_ftp_directory,
:h_szLocal_backup_directory,
:h_szRomote_ip,
:h_szRemote_directory,
:h_szFtp_account,
:h_szFtp_password,
:h_szAdditional_1;
strcnpy(ofile_write.m_stFileDetailer.szJobName,h_szJobName.arr,h_szJobName.len);
strncpy(ofile_write.m_stFileDetailer.szLocal_ftp_directory,h_szLocal_ftp_directory.arr,h_szLocal_ftp_directory.len);
strncpy(ofile_write.m_stFileDetailer.szLocal_backup_directory,h_szLocal_backup_directory.arr,h_szLocal_backup_directory.len);
strncpy(ofile_write.m_stFileDetailer.szRemote_ip,h_szRomote_ip.arr,h_szRomote_ip.len);
strncpy(ofile_write.m_stFileDetailer.szRemote_directory,h_szRemote_directory.arr,h_szRemote_directory.len);
strncpy(ofile_write.m_stFileDetailer.szFtp_account,h_szFtp_account.arr,h_szFtp_account.len);
strncpy(ofile_write.m_stFileDetailer.szFtp_password,h_szFtp_password.arr,h_szFtp_password.len);
strncpy(ofile_write.m_stFileDetailer.szAdditional_1,h_szAdditional_1.arr,h_szAdditional_1.len);
ofile_write.writeDetailer();
szicount++;
if(isNotFound==true)
{
szicount--;
EXEC SQL CLOSE CURGetpt_ftp_information;
return false;
}
if(isError==true)
{
return false;
}
}
return true;