public void SimpleCursorForLoop()
{
var query = (SqlQueryExpression)SqlExpression.Parse("SELECT * FROM table1");
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareCursorStatement("c1", query));
var loop = new CursorForLoopStatement("i", "c1");
loop.Statements.Add(new DeclareVariableStatement("a", PrimitiveTypes.String()));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"),
SqlExpression.FunctionCall("cast",
new SqlExpression[] { SqlExpression.VariableReference("i"), SqlExpression.Constant("varchar") })));
loop.Statements.Add(
new ConditionStatement(SqlExpression.Equal(SqlExpression.VariableReference("i"), SqlExpression.Constant(50)),
new SqlStatement[] { new ReturnStatement(SqlExpression.VariableReference("a")) }));
block.Statements.Add(new OpenStatement("c1"));
block.Statements.Add(loop);
var result = AdminQuery.ExecuteStatement(block);
Assert.IsNotNull(result);
Assert.AreEqual(StatementResultType.Result, result.Type);
var value = result.Result.GetValue(0, 0);
Assert.IsNotNull(value);
Assert.IsFalse(Field.IsNullField(value));
}
public void RaiseSystemError()
{
var block = new PlSqlBlockStatement();
block.Statements.Add(new RaiseStatement("ZERO_DIVIDE"));
var expected = Is.InstanceOf <StatementException>()
.And.Property("ErrorCode").EqualTo(100067);
Assert.Throws(expected, () => AdminQuery.ExecuteStatement(block));
}
public void LoopAndExitWithNoReturn()
{
var loop = new LoopStatement();
loop.Statements.Add(new ReturnStatement(SqlExpression.Constant(45)));
var result = AdminQuery.ExecuteStatement(loop);
Assert.IsNotNull(result);
Assert.AreEqual(StatementResultType.Result, result.Type);
}
public void ExceptionInitAndRaise()
{
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareExceptionInitStatement("MY_ERROR", 340059));
block.Statements.Add(new RaiseStatement("MY_ERROR"));
var expected = Is.InstanceOf <StatementException>()
.And.Property("ErrorCode").EqualTo(340059);
Assert.Throws(expected, () => AdminQuery.ExecuteStatement(block));
}
public void SimpleForLoop()
{
var loop = new ForLoopStatement("i", SqlExpression.Constant(0), SqlExpression.Constant(200));
loop.Statements.Add(new DeclareVariableStatement("a", PrimitiveTypes.String()));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"),
SqlExpression.FunctionCall("cast",
new SqlExpression[] { SqlExpression.VariableReference("i"), SqlExpression.Constant("varchar") })));
loop.Statements.Add(
new ConditionStatement(SqlExpression.Equal(SqlExpression.VariableReference("i"), SqlExpression.Constant(200)),
new SqlStatement[] { new ReturnStatement(SqlExpression.VariableReference("a")) }));
var result = AdminQuery.ExecuteStatement(loop);
Assert.IsNotNull(result);
Assert.AreEqual(StatementResultType.Result, result.Type);
var value = result.Result.GetValue(0, 0);
Assert.IsNotNull(value);
Assert.IsFalse(Field.IsNullField(value));
// TODO: the context should return the value of RETURN statement
}
public void WhileLoop()
{
var loop = new WhileLoopStatement(SqlExpression.SmallerThan(SqlExpression.VariableReference("a"), SqlExpression.Constant(33)));
loop.Statements.Add(new DeclareVariableStatement("b", PrimitiveTypes.Integer()));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("b"),
SqlExpression.Add(SqlExpression.VariableReference("a"), SqlExpression.Constant(40))));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"),
SqlExpression.Add(SqlExpression.VariableReference("a"), SqlExpression.Constant(1))));
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
var result = AdminQuery.ExecuteStatement(block);
Assert.IsNotNull(result);
Assert.AreEqual(StatementResultType.Result, result.Type);
var value = result.Result.GetValue(0, 0);
Assert.IsNotNull(value);
Assert.IsFalse(Field.IsNullField(value));
}