Stored Procedures Operations Navigator Week 10 SPL SPL

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 Qty. To. Xfr Int Default 0; Creates a variable called Qty. To. Xfr as an integer and assigns 0 as the default value

SPL - Assignment Set Xfr. Qty. Actual = 0; • Test a local variable or parameter If Qty. To. Xfr is not Null then…

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 ‘: ’.

SPL – Conditional (Selection) Statements • If statements If Xfr. Part. Id = 123 then set Qty. To. Xfr = 1; Else. If Xfr. To. Rqs > 100 then set Qty. To. Xfr = 100; Else set Qty. To. Xfr = Xfr. Qty. Rqs; End if; • The above statement has 3 possible conditions – If the part number = 123 – If the Xfr. To. Rqs > 100 – otherwise

SPL – Conditional (Selection) Statements • Case Statements Case Part. Id When 123 then Set Qtr. To. Xfr = 1; When 234 then Set Qtr. To. Xfr = 10; Else Set Qty. To. Xfr = Xfr. Qty. Rqs; End Case; • This statement has 3 conditions – If Part. Id = 123 – If Part. Id = 234 – otherwise

SPL – Conditional (Selection) Statements • Case Statements Case When Xfr. Part. Id = 123 then Set Qty. To. Xfr = 1; When Xfr. Part. Id = 234 then Set Qty. To. Xfr = 100; Else Set Qty. To. Xfr = Xfr. Qty. Rqs; End Case; • This condition has 3 conditions – If Xfr. Part. Id = 123 – If Xfrpart. Id = 234 – Otherwise

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

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

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

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

SPL – Loop Control Structures (Iteration) • For Inv. Row as Inv. Cursor cursor for Select Qty From Inventory Where Part. Id = Cur. Part. Id 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.

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

Operations Navigator