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); }