sqlparser/statements.cpp
// DB2, MySQL, Informix, Teradata DECLARE CURSOR
DB2, MySQL, Informix, Teradata DECLARE CURSOR
DB2 MySQL Informix Teradata DECLARE CURSOR
bool SqlParser::ParseDeclareCursor(Token *declare, Token *name, Token *cursor)
{
	if(declare == NULL || name == NULL || cursor == NULL)
		return false;
    STATS_DECL
	PL_STMS_STATS_V("DECLARE CURSOR", declare);
	_spl_declared_cursors.Add(name);
	_spl_current_declaring_cursor = name;
	_spl_current_declaring_cursor_uses_vars = false;
	Token *with = GetNextWordToken("WITH", L"WITH", 4);
	Token *hold = NULL;
	if(with != NULL)
	{
		hold = GetNextWordToken("HOLD", L"HOLD", 4);
		if(hold != NULL)
		{
			if(_target == SQL_ORACLE)
				Token::Remove(with, hold);
		}
		else
		{
			PushBack(with);
			with = NULL;
		}
	}
	Token *with2 = GetNextWordToken("WITH", L"WITH", 4);
	Token *return_ = NULL;
	if(with2 != NULL)
	{
		return_ = GetNextWordToken("RETURN", L"RETURN", 6);
		Token *to = NULL;
		Token *rctype = NULL;
		Token *only = GetNextWordToken("ONLY", L"ONLY", 4);
		if(return_ != NULL)
		{
			to = GetNextWordToken("TO", L"TO", 2);
			if(to != NULL)
				rctype = GetNextToken();
			_spl_result_set_cursors.Add(name);
			_spl_result_sets++;
		}
		if(!Target(SQL_DB2, SQL_TERADATA))
		{
			Token::Remove(with2, return_);
			Token::Remove(only);
			Token::Remove(to, rctype);
		}
	}
	Token *for_ = GetNextWordToken("FOR", L"FOR", 3);
	Token *as = (for_ == NULL)? TOKEN_GETNEXTW("AS") : NULL;
	Token *select = GetNextSelectStartKeyword();
	Token *stmt_id = NULL;
	if(select == NULL)
	{
		Token *next = GetNextToken();
		ListwmItem *i = Find(_spl_prepared_stmts, next);
		if(i != NULL)
			stmt_id = (Token*)i->value;			
		else
		if(next != NULL)
			stmt_id = next;
		_spl_prepared_stmts_cursors.Add(name, stmt_id);
		if(return_ != NULL)
			_spl_prepared_stmts_cursors_with_return.Add(name, stmt_id);
	}
	else
	{
		ListW exp_starts;
		ListW out_cols;
		ParseSelectStatement(select, 0, SQL_SEL_CURSOR, NULL, NULL, &exp_starts, &out_cols, NULL, NULL, NULL, NULL);
		ListwItem *first_exp = exp_starts.GetFirst();
		if(first_exp != NULL)
			_spl_declared_cursors_select_first_exp.Add(name, first_exp->value);
		ListwItem *exp = out_cols.GetFirst();
		while(exp != NULL)
		{
			_spl_declared_cursors_select_exp.Add(name, exp->value);
			exp = exp->next;
		}
	}
	Token *cut_start = NULL;
	if(select != NULL)
	{
		if(return_ == NULL)
		{
			if(_target == SQL_ORACLE)
			{
				Token::Change(declare, "CURSOR", L"CURSOR", 6);
				Token::Change(for_, "IS", L"IS", 2);
				Token::Remove(cursor);
			}
			else
			if(_target == SQL_SQL_SERVER)
				TOKEN_CHANGE(as, "FOR");
		}
		else
		{
			if(Target(SQL_ORACLE, SQL_SQL_SERVER) && _source_app != APP_COBOL)
			{
				cut_start = for_;
				if(_target == SQL_SQL_SERVER)
				{
					cut_start = select;
					Token::Remove(for_);
				}
				Cut(COPY_SCOPE_PROC, COPY_CURSOR_WITH_RETURN, name, for_, GetLastToken());
				Token::Remove(declare, cursor);
				Token::Remove(GetNextCharToken(';', L';'));
			}
		}
	}
	else
	if(stmt_id != NULL)
	{
		if(_target == SQL_ORACLE)
		{
			if(return_ == NULL)
			{
				Token::Change(cursor, "SYS_REFCURSOR", L"SYS_REFCURSOR", 13);
				Token::Remove(declare);
				Token::Remove(for_, stmt_id);
			}
			else
				Token::Remove(declare, GetNextCharOrLastToken(';', L';'));
		}
	}
	if(_spl_current_declaring_cursor_uses_vars)
		_spl_declared_cursors_using_vars.Add(name);
	if(cut_start == NULL && !Source(SQL_SQL_SERVER) && Target(SQL_SQL_SERVER))
	{
		if(_spl_scope == SQL_SCOPE_PROC || _spl_func_to_proc)
			APPEND(cursor, " LOCAL");
		if(_spl_current_declaring_cursor_uses_vars)
			cut_start = declare;
	}
	Token *cursor_end = GetLastToken(TOKEN_GETNEXT(';'));
	_spl_declared_cursors_stmts.Add(name, declare, cursor_end);
	if(_spl_first_non_declare == NULL && cut_start == NULL)
		_spl_last_declare = cursor_end;
	bool moved = false;
	if(Target(SQL_MYSQL, SQL_MARIADB))
		moved = MySQLMoveCursorDeclarations(declare, cursor_end);
	if(!moved)
		SqlServerAddStmtDelimiter();
	_spl_current_declaring_cursor = NULL;
	return true;
}
bool SqlParser :: ParseDeclareCursor ( Token * declare , Token * name , Token * cursor ) { if ( declare == NULL || name == NULL || cursor == NULL ) return false ; STATS_DECL PL_STMS_STATS_V ( " " , declare ) ; _spl_declared_cursors . Add ( name ) ; _spl_current_declaring_cursor = name ; _spl_current_declaring_cursor_uses_vars = false ; Token * with = GetNextWordToken ( " " , L" " , 4 ) ; Token * hold = NULL ; if ( with != NULL ) { hold = GetNextWordToken ( " " , L" " , 4 ) ; if ( hold != NULL ) { if ( _target == SQL_ORACLE ) Token :: Remove ( with , hold ) ; } else { PushBack ( with ) ; with = NULL ; } } Token * with2 = GetNextWordToken ( " " , L" " , 4 ) ; Token * return_ = NULL ; if ( with2 != NULL ) { return_ = GetNextWordToken ( " " , L" " , 6 ) ; Token * to = NULL ; Token * rctype = NULL ; Token * only = GetNextWordToken ( " " , L" " , 4 ) ; if ( return_ != NULL ) { to = GetNextWordToken ( " " , L" " , 2 ) ; if ( to != NULL ) rctype = GetNextToken ( ) ; _spl_result_set_cursors . Add ( name ) ; _spl_result_sets ++ ; } if ( ! Target ( SQL_DB2 , SQL_TERADATA ) ) { Token :: Remove ( with2 , return_ ) ; Token :: Remove ( only ) ; Token :: Remove ( to , rctype ) ; } } Token * for_ = GetNextWordToken ( " " , L" " , 3 ) ; Token * as = ( for_ == NULL ) ? TOKEN_GETNEXTW ( " " ) : NULL ; Token * select = GetNextSelectStartKeyword ( ) ; Token * stmt_id = NULL ; if ( select == NULL ) { Token * next = GetNextToken ( ) ; ListwmItem * i = Find ( _spl_prepared_stmts , next ) ; if ( i != NULL ) stmt_id = ( Token * ) i -> value ; else if ( next != NULL ) stmt_id = next ; _spl_prepared_stmts_cursors . Add ( name , stmt_id ) ; if ( return_ != NULL ) _spl_prepared_stmts_cursors_with_return . Add ( name , stmt_id ) ; } else { ListW exp_starts ; ListW out_cols ; ParseSelectStatement ( select , 0 , SQL_SEL_CURSOR , NULL , NULL , & exp_starts , & out_cols , NULL , NULL , NULL , NULL ) ; ListwItem * first_exp = exp_starts . GetFirst ( ) ; if ( first_exp != NULL ) _spl_declared_cursors_select_first_exp . Add ( name , first_exp -> value ) ; ListwItem * exp = out_cols . GetFirst ( ) ; while ( exp != NULL ) { _spl_declared_cursors_select_exp . Add ( name , exp -> value ) ; exp = exp -> next ; } } Token * cut_start = NULL ; if ( select != NULL ) { if ( return_ == NULL ) { if ( _target == SQL_ORACLE ) { Token :: Change ( declare , " " , L" " , 6 ) ; Token :: Change ( for_ , " " , L" " , 2 ) ; Token :: Remove ( cursor ) ; } else if ( _target == SQL_SQL_SERVER ) TOKEN_CHANGE ( as , " " ) ; } else { if ( Target ( SQL_ORACLE , SQL_SQL_SERVER ) && _source_app != APP_COBOL ) { cut_start = for_ ; if ( _target == SQL_SQL_SERVER ) { cut_start = select ; Token :: Remove ( for_ ) ; } Cut ( COPY_SCOPE_PROC , COPY_CURSOR_WITH_RETURN , name , for_ , GetLastToken ( ) ) ; Token :: Remove ( declare , cursor ) ; Token :: Remove ( GetNextCharToken ( ' ' , L' ' ) ) ; } } } else if ( stmt_id != NULL ) { if ( _target == SQL_ORACLE ) { if ( return_ == NULL ) { Token :: Change ( cursor , " " , L" " , 13 ) ; Token :: Remove ( declare ) ; Token :: Remove ( for_ , stmt_id ) ; } else Token :: Remove ( declare , GetNextCharOrLastToken ( ' ' , L' ' ) ) ; } } if ( _spl_current_declaring_cursor_uses_vars ) _spl_declared_cursors_using_vars . Add ( name ) ; if ( cut_start == NULL && ! Source ( SQL_SQL_SERVER ) && Target ( SQL_SQL_SERVER ) ) { if ( _spl_scope == SQL_SCOPE_PROC || _spl_func_to_proc ) APPEND ( cursor , " " ) ; if ( _spl_current_declaring_cursor_uses_vars ) cut_start = declare ; } Token * cursor_end = GetLastToken ( TOKEN_GETNEXT ( ' ' ) ) ; _spl_declared_cursors_stmts . Add ( name , declare , cursor_end ) ; if ( _spl_first_non_declare == NULL && cut_start == NULL ) _spl_last_declare = cursor_end ; bool moved = false ; if ( Target ( SQL_MYSQL , SQL_MARIADB ) ) moved = MySQLMoveCursorDeclarations ( declare , cursor_end ) ; if ( ! moved ) SqlServerAddStmtDelimiter ( ) ; _spl_current_declaring_cursor = NULL ; return true ; }
DB2, MySQL, Informix, Teradata DECLARE CURSOR
DB2 MySQL Informix Teradata DECLARE CURSOR
// Optional WITH HOLD in DB2 // Remove for Oracle // Optional WITH RETURN in DB2, Teradata // Optional ONLY in Teradata // Optional TO CALLER or TO CLIENT // Remove return options // FOR keyword // AS keyword in Sybase ADS // SELECT statement, variable, statement ID // Statement ID in Informix, Teradata // In Informix, PREPARE stmt_id FROM select_string goes before DECLARE cur FOR stmt_id // In Teradata, cursor declared first, then PREPARE executed // Prepared statement ID can be specified in Informix, find it // No PREPARE was executed in Teradata yet  // Save the first select expression of the declared cursor // Save the columns of the declared cursor // SELECT statement is specified // Cursor with no return to client/caller // CURSOR cur IS SELECT  // Cut FOR SELECT to be used in OPEN // in SQL Server, Standalone SELECT is used in place of OPEN cursor, so FOR keyword is not required // Block will be transferred to OPEN cur // Remove ; following cut DECLARE // Prepared statement ID is specified // In Oracle declare REF CURSOR that will be used in OPEN cur FOR sql_string // Cursor is without return // cur SYS_REFCURSOR;  // OUT SYS_REFCURSOR will be added as an OUT parameter  // In SQL Server cursor are global by default in procedures (but not in functions) i.e if you call procedure multiple time // you can get "already exists error" if the cursor was not deallocated. We will just add LOCAL option to prevent this // SQL Server takes variable values at declare time, not open cursor time, so if there are any dependencies on variable we have to // move the cursor declaration right before OPEN cursor statements // If there are no non-declare statements above, set last declare // MySQL, MariaDB require cursor declaration go before any other statements // Add statement delimiter if not set when source is SQL Server