public static void TriggerWithBody_InitiallyDisabled()
{
var body = new PlSqlBlockStatement();
body.Statements.Add(new OpenStatement("c1"));
body.Statements.Add(new CursorForLoopStatement("i", "c1"));
body.Statements.Add(new ReturnStatement());
var statement = new CreateTriggerStatement(ObjectName.Parse("APP.trig1"), new ObjectName("tab1"), body,
TriggerEventTime.Before, TriggerEventType.Insert | TriggerEventType.Update)
{
Status = TriggerStatus.Disabled,
ReplaceIfExists = true
};
var expected = new StringBuilder();
expected.AppendLine("CREATE OR REPLACE TRIGGER APP.trig1 BEFORE INSERT OR UPDATE ON tab1 DISABLE");
expected.AppendLine(" BEGIN");
expected.AppendLine(" OPEN c1");
expected.AppendLine(" FOR i IN c1");
expected.AppendLine(" LOOP");
expected.AppendLine(" END LOOP");
expected.AppendLine(" RETURN");
expected.Append(" END");
Assert.AreEqual(expected.ToString(), statement.ToString());
}
public static void CodeBlock_Nolabel()
{
var block = new PlSqlBlockStatement();
block.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(2)));
var sql = block.ToString();
var expected = new StringBuilder();
expected.AppendLine("BEGIN");
expected.AppendLine(" :a := 2");
expected.Append("END");
Assert.AreEqual(expected.ToString(), sql);
}
public static void WithArguments()
{
var body = new PlSqlBlockStatement();
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(3)));
var statement = new CreateProcedureStatement(ObjectName.Parse("SYS.proc1"), new[] {
new RoutineParameter("a", PrimitiveTypes.Integer()),
}, body);
var sql = statement.ToString();
var expected = new SqlStringBuilder();
expected.AppendLine("CREATE PROCEDURE SYS.proc1(a INTEGER IN NOT NULL) IS");
expected.AppendLine(" BEGIN");
expected.AppendLine(" :a := 3");
expected.Append(" END");
Assert.AreEqual(expected.ToString(), sql);
}
public static void CodeBlock_WithLabel()
{
var block = new PlSqlBlockStatement();
block.Label = "stmt";
block.Statements.Add(new CallStatement(ObjectName.Parse("proc1"), new[] {
SqlExpression.Constant(33)
}));
var sql = block.ToString();
var expected = new StringBuilder();
expected.AppendLine("<<stmt>>");
expected.AppendLine("BEGIN");
expected.AppendLine(" CALL proc1(33)");
expected.Append("END");
Assert.AreEqual(expected.ToString(), sql);
}
public CreateTriggerStatement(ObjectName triggerName, ObjectName tableName, PlSqlBlockStatement body, TriggerEventTime eventTime, TriggerEventType eventType)
{
if (triggerName == null)
{
throw new ArgumentNullException("triggerName");
}
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
if (body == null)
{
throw new ArgumentNullException("body");
}
TriggerName = triggerName;
TableName = tableName;
Body = body;
EventTime = eventTime;
EventType = eventType;
}
protected virtual SqlStatement VisitPlSqlBlock(PlSqlBlockStatement statement)
{
var block = new PlSqlBlockStatement();
foreach (var declaration in statement.Declarations)
{
block.Declarations.Add(VisitStatement(declaration));
}
foreach (var child in statement.Statements)
{
block.Statements.Add(VisitStatement(child));
}
foreach (var handler in statement.ExceptionHandlers)
{
block.ExceptionHandlers.Add(handler);
}
return(block);
}
public static void WithNoParameters()
{
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(3)));
body.Statements.Add(new ReturnStatement(SqlExpression.VariableReference("a")));
var statement = new CreateFunctionStatement(ObjectName.Parse("SYS.func1"), PrimitiveTypes.Integer(), body);
var sql = statement.ToString();
var expected = new SqlStringBuilder();
expected.Append("CREATE FUNCTION SYS.func1() ");
expected.AppendLine("RETURN INTEGER IS");
expected.AppendLine(" DECLARE");
expected.AppendLine(" a INTEGER");
expected.AppendLine(" BEGIN");
expected.AppendLine(" :a := 3");
expected.AppendLine(" RETURN :a");
expected.Append(" END");
Assert.AreEqual(expected.ToString(), sql);
}