the routine definition the default is NOT DETERMINISTIC Determinism is

The routine definition the default is not

This preview shows page 23 - 32 out of 32 pages.

the routine definition, the default is NOT DETERMINISTIC Determinism is important because there are restrictions in how functions (use defined and inbuilt), views and procedures can be used. Determinism has implications for database Indexing and security.
Image of page 23
Selection: IF Conditionally execute code IF expression THEN statements ; ELSE else-statements ; END IF OR IF expression THEN statements ; ELSEIF elseif-expression THEN elseif-statements ; ... ELSE else-statements ; END IF; OR (MySQL) SELECT IF ( expression , value_if_true , value_if_false )
Image of page 24
Selection: IF examples PL/SQL I F monthly_value <= 4000 THEN ILevel := 'Low Income'; ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN ILevel := 'Avg Income'; ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN ILevel := 'Moderate Income'; ELSE ILevel := 'High Income'; END IF; Transact-SQL IF DATENAME (weekday, GETDATE()) IN (N'Saturday', N'Sunday’) SELECT 'Weekend’; ELSE SELECT 'Weekday'; MySQL SELECT IF(500<1000, "YES", "NO");
Image of page 25
Selection: CASE Conditionally execute code CASE case_expression WHEN when_expression_1 THEN commands WHEN when_expression _2 THEN commands ... ELSE commands END CASE;
Image of page 26
Selection: CASE example MySQL, PL/SQL and Transact-SQL SELECT SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN "The quantity is greater than 30" WHEN Quantity = 30 THEN "The quantity is 30" ELSE "The quantity is under 30" END FROM OrderDetails;
Image of page 27
Iteration: WHILE Repeatedly execute code WHILE expression DO statements ; END WHILE Or REPEAT statements ; UNTIL expression END REPEAT Or (PL/SQL) WHILE condition LOOP {...statements...} END LOOP;
Image of page 28
Selection: while example Transact-SQL WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN UPDATE Production.Product SET ListPrice = ListPrice * 2 IF (SELECT MAX(ListPrice) FROM Production.Product) > $500 BREAK ELSE CONTINUE END PL/SQL WHILE monthly_value <= 4000 LOOP monthly_value := daily_value * 31; END LOOP; MySQL WHILE v1 > 0 DO ... SET v1 = v1 - 1; END WHILE ;
Image of page 29
Overview Review Sub queries SQL Functions and Relational Operators Virtual Tables – Creating views Transaction Control Language (TCL) - Transactions Stored Procedures and Functions Procedural Language Stored Procedures User defined Functions IF, CASE and WHILE Demonstrations 30
Image of page 30
Demonstrations 31
Image of page 31
References Coronel, C. & Morris, S. 2018, Database Systems: Design Implementation and Management , 13 th Edn, Cengage Learning Elmasri, R. & Navathe, S., 2011, Fundamentals of Database Systems , 6 th Edn, Pearson MySQL Reference Manual W3schools.com Microsoft Transact-SQL Reference Tech on the Net – Oracle / PLSQL 32
Image of page 32

You've reached the end of your free preview.

Want to read all 32 pages?

  • '19

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture