Stored Procedures

1. Stored Procedures Week 9

2. Test Details

3. Stored Procedures • SQL can call code written in iSeries High Level Languages • Called stored procedures • SQL has its own language called SQL Procedural Language (SPL) • An SPL procedure can call another SPL procedure • Execute using the CALL statement in Embedded SQL

4. Stored Procedures • With Stored Procedures, native iSeries code can be executed from any SQL machine on the network • Reduce, Reuse, Recycle!

5. Using an HLL program as a Stored Procedure • Must execute a Create Procedure Statement Create Procedure MyProcX ( In Parm1 Int, InOut Parm2 Char(10), Out RtmMsgId Char(7))) Language RPGLE Specific MyProcXParam3 Reads SQL Data External name MyPgmX Parameter Style General This statement creates a stored procedure from the RPG program, MyPgmX. MyPgmX has 3 parameters

6. Language language • Indicates which language the stored procedure division is written in. • Possible values: • RPGLE • CobolLE • C • SQL

7. Specific specific-name • A unique name within the collection • Some procedures may have the same name, but a different number of parameters • Specific name is used in this case to differentiate these procedures

8. [Not] Deterministic • Not deterministic • For programs that may return different results for the same argument values. Use this if you’re not sure. • The default value • Deterministic • For programs that return the same results for the same argument values and that have no side effects

9. Reads SQL Data, Modifies SQL, Contains SQL, No SQL • Reads SQL Data • Program may use SQL to read data. Cannot change data • Cannot use Delete, Insert, Update, Commit, Rollback, Set Transaction, Create, Drop, Alter Table, Rename, Comment on, Label on, Grant, Revoke, Connect, Disconnect, Release, or Set Connections

10. Reads SQL Data, Modifies SQL, Contains SQL, No SQL • Modifies SQL Data • Program may use SQL to read and/or modify data • Cannot use Commit, Rollback, Set Transaction, Connect, Disconnect, Release or Set Connection • Contains SQL • Program can contain SPL statements • No SQL • Program should not contain any SQL statements

11. External Name Hll-program-name • Specifies the name of the HLL program name • If no program name is given, then the default is the procedure name

12. Parameter Style style • Possible values: • General • One parameter for each parameter in the procedure’s parameter list

13. Parameter Style style • SQL • Used for programs that are to be called from SQL, JDBC or ODBC • Following parameters are passed: • One parameter for each parameter in the parameter list • One indicator variable (InOut SmallInt) for each parameter in the list • InOut CHAR (5) – SQLState • In VarChar (517) – Fully qualified procedure name • In VarChar (128) – Specific name of the procedure • InOut VarChar(70) – message text

14. Remove a procedure definition from the SQL catalog • Drop Procedure AppDta.MyProcX • If more than one procedure has the same name, then: • Drop Procedure AppDta.MyProcX (Int, Char(10), Char(7) Or • Drop specific procedure AppDta.MyProcXparam3 • You must drop a procedure before you can recreate it

15. Create a procedure to call CC100C Visual Basic?

16. SPL

17. 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

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

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

20. 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

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

22. 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

23. 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

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

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

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

27. SPL – Loop Control Structures(Iteration) • For For InvRow as InvCursor 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.