270 likes | 439 Views
Stored Procedures. Week 9. Test Details. 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
E N D
Stored Procedures Week 9
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
Stored Procedures • With Stored Procedures, native iSeries code can be executed from any SQL machine on the network • Reduce, Reuse, Recycle!
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
Language language • Indicates which language the stored procedure division is written in. • Possible values: • RPGLE • CobolLE • C • SQL
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
[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
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
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
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
Parameter Style style • Possible values: • General • One parameter for each parameter in the procedure’s parameter list
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
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
Create a procedure to call CC100C Visual Basic?
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
SPL - Assignment Set XfrQtyActual = 0; • Test a local variable or parameter If QtyToXfr 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 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
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
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
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 While (EOF = ‘N’) Do End While;
SPL – Loop Control Structures(Iteration) • Repeat Repeat Until (EOF = ‘Y’) End Repeat;
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.