1 / 15

Stored Procedures & Operations Navigator

Stored Procedures & Operations Navigator. Week 10. SPL. SPL - Declarations. SPL supports all data types available in the Create Table statement SPL does not support User Defined Types SPL does not support Arrays Declare QtyToXfr Int Default 0;

hamal
Download Presentation

Stored Procedures & Operations Navigator

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Stored Procedures& Operations Navigator Week 10

  2. SPL

  3. SPL - Declarations • SPL supports all data types available in the Create Table statement • SPL does not support User Defined Types • SPL does not support Arrays Declare QtyToXfr Int Default 0; Creates a variable called QtyToXfr as an integer and assigns 0 as the default value

  4. SPL - Assignment Set XfrQtyActual = 0; • Test a local variable or parameter If QtyToXfr is not Null then…

  5. SPL – Data definition and Manipulation Statement • Use SQL data definition and manipulation statements similar to embedded SQL. • You don’t need to use Exec SQL and End-Exec. • You don’t need to prefix variables with an ‘:’.

  6. SPL – Conditional (Selection) Statements • If statements If XfrPartId = 123 then set QtyToXfr = 1; ElseIf XfrToRqs > 100 then set QtyToXfr = 100; Else set QtyToXfr = XfrQtyRqs; End if; • The above statement has 3 possible conditions • If the part number = 123 • If the XfrToRqs > 100 • otherwise

  7. SPL – Conditional (Selection) Statements • Case Statements Case PartId When 123 then Set QtrToXfr = 1; When 234 then Set QtrToXfr = 10; Else Set QtyToXfr = XfrQtyRqs; End Case; • This statement has 3 conditions • If PartId = 123 • If PartId = 234 • otherwise

  8. SPL – Conditional (Selection) Statements • Case Statements Case When XfrPartId = 123 then Set QtyToXfr = 1; When XfrPartId = 234 then Set QtyToXfr = 100; Else Set QtyToXfr = XfrQtyRqs; End Case; • This condition has 3 conditions • If XfrPartId = 123 • If XfrpartId = 234 • Otherwise

  9. SPL – Loop Control Structures(Iteration) • 4 loop structures • Loop • infinite loop (use the Leave statement to exit) • While • Test is at the beginning of the loop • Repeat • Test is at the end of the loop • For • Iterate over a set of rows

  10. SPL – Loop Control Structures(Iteration) • Loop Read: Loop If EOF = ‘Y’ then Leave Read; End If; End Loop Read;

  11. SPL – Loop Control Structures(Iteration) • While While (EOF = ‘N’) Do End While;

  12. SPL – Loop Control Structures(Iteration) • Repeat Repeat Until (EOF = ‘Y’) End Repeat;

  13. SPL – Loop Control Structures(Iteration) • For For InvRow as InvCursor cursor for Select Qty From Inventory Where PartId = CurPartId Do End For; • Builds a cursor full of data, reads a record from the cursor, process it, and then reads the next record. This loop continues until all the records in the cursor are processed.

  14. Write a Stored Procedure that: • Reads through ACTRANSPF and updates records as follows: • Trans Type BD – change to 500 • Trans Type FD – increase by 5% • Trans Type LS – change to 40

  15. Operations Navigator

More Related