嵌入式sql采用游标编程,一般有几步曲
1、EXEC SQL PREPARE sSql FROM :sql ;
2、EXEC SQL DECLARE cSql CURSOR FOR sSql ; 一起jquery,17jquery
3、EXEC SQL OPEN cSql ;
4、EXEC SQL FETCH cSql INTO ...
while(sqlca.sqlcode....){ //判断结束条件
EXEC SQL FETCH cSql INTO ...
}
那mysql中fetch是如何实现的呢,看一下fetch的主要流程 内容来自17jquery
先从sql_parse.cc中dispatch_command
case COM_STMT_FETCH: 一起jquery,17jquery
{ 17jquery.com
mysql_stmt_fetch(thd, packet, packet_length);
break;
}
sql_prepare.cc 17jquery.com
void mysql_stmt_fetch(THD *thd, char *packet, uint packet_length)
{
....
if (!(stmt= find_prepared_statement(thd, stmt_id)))--先看执行了prepare没有
{
char llbuf[22];
my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), sizeof(llbuf),
llstr(stmt_id, llbuf), "mysql_stmt_fetch");
DBUG_VOID_RETURN;
} 17jquery.com
.....
cursor= stmt->cursor; --是否declare游标 一起jquery,17jquery
if (!cursor)
{
my_error(ER_STMT_HAS_NO_OPEN_CURSOR, MYF(0), stmt_id); 内容来自17jquery
DBUG_VOID_RETURN;
} 17jquery.com
cursor->fetch(num_rows); --执行具体的fetch操作
if (!cursor->is_open())--检查游标是否还打开
{ 一起jquery,17jquery
stmt->close_cursor();
thd->cursor= 0;
reset_stmt_params(stmt);
}
thd->restore_backup_statement(stmt, &stmt_backup);
thd->stmt_arena= thd;
DBUG_VOID_RETURN; 17jquery.com
}
cursor->fetch(num_rows)有好几种实现,找个简单的分析一下
sql_cursor.cc
void Materialized_cursor::fetch(ulong num_rows)
{
THD *thd= table->in_use; 内容来自17jquery
int res= 0;
result->begin_dataset();
for (fetch_limit+= num_rows; fetch_count < fetch_limit; fetch_count++)
{ 一起jquery,17jquery
if ((res= table->file->rnd_next(table->record[0]))) 17jquery.com
break;
/* Send data only if the read was successful. */
result->send_data(item_list); //直接从table里面读取,从临时表中读取
}
switch (res) { //根据不同结果,回复客户端不同信息
case 0:
thd->server_status|= SERVER_STATUS_CURSOR_EXISTS;
result->send_eof();
thd->server_status&= ~SERVER_STATUS_CURSOR_EXISTS;
break;
case HA_ERR_END_OF_FILE:
thd->server_status|= SERVER_STATUS_LAST_ROW_SENT;
result->send_eof(); 17jquery.com
thd->server_status&= ~SERVER_STATUS_LAST_ROW_SENT; 17jquery.com
close();
break;
default:
table->file->print_error(res, MYF(0));
close();
break;
}
}
上面Materialized_cursor::fetch不满足事务隔离级别-游标稳定性,有时间看看
Sensitive_cursor::fetch能否满足事务隔离级别的要求
--转自