SQL Procedures And Functions 1 SQL400 03032003 DMC

SQL – Procedures And Functions 1 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures 2 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures SQL: Written in SQL Proc External: Written in HL Languages such as RPGLE SQL Proc 3 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures CREATE PROCEDURE proc-name Parameters… IN - INPUT PARAMETERS OUT - OUTPUT PARAMETERS INOUT - INPUT/OUTPUT PARAMETERS CREATE PROCEDURE TESTPROC (IN EMPNO DECIMAL(5, 0), OUT EMPNAM CHAR(30))… 4 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Internal Syntax: CREATE PROCEDURE TESTPROC (IN EMPNO DECIMAL(5, 0), OUT EMPNAM CHAR(30))… External Syntax: CREATE PROCEDURE TESTPROC (IN DECIMAL(5, 0), OUT CHAR(30))… 5 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Overloading: Its possible to create multiple versions of the same procedure, each with a different number of parameters passed: CREATE PROCEDURE TESTPROC (IN EMPNO DECIMAL(5, 0))… CREATE PROCEDURE TESTPROC (IN EMPNO INTEGER, OUT EMPNAM CHAR(30))… In this case, the system generates a unique specific name for each version of the procedure. This is discussed more later. 6 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Data Types CHAR – Up to 32, 767 bytes of alphanumeric data (32. 765 if Null capable) DECIMAL – Up to 63 digits of packed numeric data NUMERIC – Up to 63 digits of zoned numeric data. DATE – Date Data type using a 4 digit year TIME – Time data type in 24 hr format TIMESTAMP - Timestamp data type include date, time, and microseconds (6 digits) INTEGER (INT) – Large Integer (4 Bytes) SMALLINT – A small integer (2 Bytes) BIGINT – A big integer (8 Bytes) FLOAT – Floating Point data type (8 Byte) VARCHAR - Up to 32, 740 bytes of character data (32. 739 if Null capable) LONG VARCHAR – Variable Length Character Data up to the length of the row BINARY – Up to 32, 767 bytes of binary data (32, 765 if Null Capable) 7 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Data Types VARBINARY - Up to 32, 740 bytes of binary data (32. 739 if Null capable) GRAPHIC – Up to 16, 383 double byte characters (16, 382 if Null capable) VARGRAPHIC – Up to 16370 double byte characters (16, 369 if Null capable) LONG VARGRAPHIC – A string of double byte characters up to the length of the row DOUBLE – Double precision floating point (8 Byte) REAL – Single Precision Floating point (4 Byte) BLOB – Variable length binary string up to 2 gigabytes in length CLOB – Variable length string of characters up to 1 gigabyte in length DATALINK – A string up to 32, 718 bytes containing a URL (32, 717 if Null capable) DBCLOB – Variable length double byte character string up to 1 gigabyte in length User-Defined (UDT) – Custom functions built with the CREATE FUNCTION statement ROWID - System generated key that uniquely defines a row (40 Bytes) 8 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures SQL Procedures create procedure your-lib/getcust (in cust char(10)) language sql dynamic result sets 1 begin declare stmt char(50); declare c 1 cursor for s 1; set stmt = 'SELECT * FROM your-lib/CUST WHERE CNUMBER = ‘ CONCAT Cust; prepare s 1 from stmt; open c 1; return; end 9 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures call kpflib. getcust ('1’); 10 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures create procedure GETCUST (in Custid char(10), out Oname char(50), out Oaddr 1 char(50), out Oaddr 2 char(50), out Ocity char(50), out Ostate char(2), out Ozip char(10)) language sql begin SELECT cname, caddr 1, caddr 2, ccity, cstate, czip into Oname, oaddr 1, oaddr 2, ocity, ostate, ozip from CUST where cnumber = dec(Custid); return; end 11 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures call kpflib. getcust ('1', ' ', ' '); 12 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Create Procedure Options CREATE PROCEDURE proc-name parameters LANGUAGE SQL options RESULT SETS – Number of result sets returned LANGUAGE – C, SQL, RPG, etc… SPECIFIC – Unique name in case of overloading FENCED – Function should run in a separate thread from the main job. NOT FENCED – Can run in the same thread 13 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Create Procedure Options CREATE PROCEDURE proc-name parameters LANGUAGE SQL options NOT DETERMINISTIC – Return value can be unique on every call DETERMINISTIC – Always returns the same results with the same inputs MODIFIES SQL DATA – The Procedure will use SQL to update data in a table READS SQL DATA – The Procedure will use SQL to read data CONTAINS SQL – Some SQL Code is included CALLED ON NULL INPUT – If inputs are null, should the call execute? 14 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Function Statement DETERMINISTIC NOT DETERMINISTIC UPPER(‘a’) = ‘A’ ORDSTAT(100101) = ‘*’ date('09/01/2007') = 09/01/07 Now() = 2007 -09 -01 -11. 30. 453176 15 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Additional commands for Stored Procedures: BEGIN CALL CASE END FOR GET GOTO IF ITERATE LEAVE LOOP REPEAT RESIGNAL RETURN SIGNAL WHILE 16 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Special Registers – (Predefined Fields) CURRENT DATE CURRENT DEGREE CURRENT TIMEZONE CURRENT TIMESTAMP CURRENT SCHEMA CURRENT SERVER USER CURRENT PATH SESSION_USER SYSTEM_USER 17 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Sample Stored Procedures CREATE PROCEDURE UPDATE_SALARY_2 (IN EMPLOYEE_NUMBER CHAR(6), IN RATING INT) LANGUAGE SQL MODIFIES SQL DATA CASE RATING WHEN 1 THEN UPDATE CORPDATA. EMPLOYEE SET SALARY = SALARY * 1. 10, BONUS = 1000 WHERE EMPNO = EMPLOYEE_NUMBER; WHEN 2 THEN UPDATE CORPDATA. EMPLOYEE SET SALARY = SALARY * 1. 05, BONUS = 500 WHERE EMPNO = EMPLOYEE_NUMBER; ELSE UPDATE CORPDATA. EMPLOYEE SET SALARY = SALARY * 1. 03, BONUS = 0 WHERE EMPNO = EMPLOYEE_NUMBER; END CASE 18 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Sample Stored Procedures CREATE PROCEDURE RETURN_DEPT_SALARY 1 of 2 (IN DEPT_NUMBER CHAR(3), OUT DEPT_SALARY DECIMAL(15, 2), OUT DEPT_BONUS_CNT INT) LANGUAGE SQL READS SQL DATA P 1: BEGIN DECLARE EMPLOYEE_SALARY DECIMAL(9, 2); DECLARE EMPLOYEE_BONUS DECIMAL(9, 2); DECLARE TOTAL_SALARY DECIMAL(15, 2)DEFAULT 0; DECLARE BONUS_CNT INT DEFAULT 0; DECLARE END_TABLE INT DEFAULT 0; DECLARE C 1 CURSOR FOR SELECT SALARY, BONUS FROM CORPDATA. EMPLOYEE WHERE WORKDEPT = DEPT_NUMBER; 19 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Sample Stored Procedures DECLARE CONTINUE HANDLER FOR NOT FOUND 2 of 2 SET END_TABLE = 1; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET DEPT_SALARY = NULL; OPEN C 1; FETCH C 1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS; WHILE END_TABLE = 0 DO SET TOTAL_SALARY = TOTAL_SALARY + EMPLOYEE_BONUS; IF EMPLOYEE_BONUS > 0 THEN SET BONUS_CNT = BONUS_CNT + 1; END IF; FETCH C 1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS; END WHILE; CLOSE C 1; SET DEPT_SALARY = TOTAL_SALARY; SET DEPT_BONUS_CNT = BONUS_CNT; END P 1 20 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Sample Stored Procedures CREATE PROCEDURE CREATE_BONUS_TABLE 1 of 2 (IN DEPT_NUMBER CHAR(3), INOUT CNT INT) LANGUAGE SQL MODIFIES SQL DATA CS 1: BEGIN ATOMIC DECLARE NAME VARCHAR(30) DEFAULT NULL; DECLARE CONTINUE HANDLER FOR SQLSTATE ’ 42710’ SELECT COUNT(*) INTO CNT FROM DATALIB. EMPLOYEE_BONUS; DECLARE CONTINUE HANDLER FOR SQLSTATE ’ 23505’ SET CNT = CNT - 1; DECLARE UNDO HANDLER FOR SQLEXCEPTION SET CNT = NULL; IF DEPT_NUMBER IS NOT NULL THEN CREATE TABLE DATALIB. EMPLOYEE_BONUS (FULLNAME VARCHAR(30), BONUS DECIMAL(10, 2), PRIMARY KEY (FULLNAME)); 21 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures Sample Stored Procedures FOR_1: FOR V 1 AS C 1 CURSOR FOR 2 of 2 SELECT FIRSTNME, MIDINIT, LASTNAME, BONUS FROM CORPDATA. EMPLOYEE WHERE WORKDEPT = CREATE_BONUS_TABLE. DEPT_NUMBER DO IF BONUS > 0 THEN SET NAME = FIRSTNME CONCAT ’ ’ CONCAT MIDINIT CONCAT ’ ’CONCAT LASTNAME; INSERT INTO DATALIB. EMPLOYEE_BONUS VALUES(CS 1. NAME, FOR_1. BONUS); SET CNT = CNT + 1; END IF; END FOR_1; END IF; END CS 1 22 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures External Procedures FMYCUSTS D D In. Custs Cname X IF E K DISK S DS 6 1 of 2 0 OCCURS(40) 30 10 U 0 inz(1) S **************************** * Mainline Module **************************** C In. Cust SETLL MYCUSTS C DOU %EOF(MYCUSTS) C In. Cust READ MYCUSTS C IF %EOF(MYCUSTS) OR C (X = 40) C LEAVE C ENDIF C EVAL X = X + 1 C X OCCUR Custs C ENDDO 23 SQL/400 03/03/2003 DMC Consulting, 1993

Stored Procedures External Procedures C EVAL X = X - 1 2 of 2 C/EXEC SQL C+ SET RESULT SETS ARRAY : Custs C/END-EXEC C C *ENTRY MOVE RETURN PLIST PARM *ON FOR : X Rows *INLR In. CUst CREATE PROCEDURE your-lib/GETCUSTS (INOUT Parm 1 DECIMAL (6, 0 )) RESULT SETS 1 LANGUAGE RPGLE NOT DETERMINISTIC CONTAINS SQL EXTERNAL NAME GETCUSTS PARAMETER STYLE GENERAL 24 SQL/400 03/03/2003 DMC Consulting, 1993

Functions 25 SQL/400 03/03/2003 DMC Consulting, 1993

Functions Sample SQL Function CREATE FUNCTION ONCRHLD(Cust. In Var. Char(5)) RETURNS CHAR(1) LANGUAGE SQL NOT DETERMINISTIC BEGIN Declare YN Char(1); Set YN = 'N'; Select ONHOLD INTO YN from CUSTMAST WHERE CUST = Cust. In; RETURN YN; END This function can be used in other SQL statements SELECT ORDER, CUSTNO FROM ORDHDR WHERE ONCRHLD(CUSTNO) = ‘Y’ 26 SQL/400 03/03/2003 DMC Consulting, 1993

Functions Sample Table Function to Return Orders that do not have a ship date yet. The RPG module, and RPG service program are created using commands such as though shown here: CREATE FUNCTION Un. Sched. Orders () RETURNS TABLE (Order CHAR(10)) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION NOT DETERMINISTIC DISALLOW PARALLEL RETURN SELECT ORDER FROM ORDHDR WHERE OSHPDAT = NULL This function can be used in other SQL statements where tables would normally be seen. SELECT * FROM ORDDTL WHERE ORDER IN(Un. Sched. Orders) 27 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE Sub-Procedure RPG Module Service Program SQL Function Create Function Statement 28 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE Sample Function to Return Customer Name H NOMAIN OPTION(*SRCSTMT) FCUST IF E 1 of 2 K DISK * Prototype for Get. CName(Cust, Part) D Get. CName PR 30 D Parm 1 6 0 *************************** * Get. CName - Takes in a 6 digit customer number * and returns a 30 byte customer name. *************************** P Get. CName B EXPORT D Get. CName PI 30 D Cust. In 6 * Define the key list for the contract file. C CUSKEY KLIST C KFLD CUST 29 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE 2 of 2 * Trap for any error that occurs C MONITOR * Chain to the Contract file C EVAL C Con. Key CHAIN C IF C RETURN C ELSE C RETURN C ENDIF CUST = Cust. In Cust %FOUND CName *BLANKS * If any error occurs return a zero value C ON-ERROR *PROGRAM C C RETURN *BLANKS * C ENDMON P E 30 SQL/400 03/03/2003 DMC Consulting, 1993

Functions Sample Function to Return Customer Name CRTRPGMOD MODULE(SQLFUNCS) DBGVIEW(*SOURCE) CRTSRVPGM(SQLFUNCS) EXPORT(*ALL) Once created, the sub procedure can be registered with the SQL engine using the SQL command shown below: CREATE FUNCTION your-schema/GETCName (IN DECIMAL(6 0)) RETURNS CHAR(30) EXTERNAL NAME 'your-schema/SQLFUNCS(GETCNAME)' LANGUAGE RPGLE PARAMETER STYLE GENERAL 31 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE Sample Function to Convert a Date H 1 of 3 NOMAIN ***************************** * SQLFUNCS - This module is used to create the Service * * program SQLFUNCS, which includes the * * following exports: * * Cvt. Date - Converts CYYDDD into *USA DATE (MM/DD/YYYY)* * * ***************************** * Prototype for Cvt. Date(DATEIN) D Cvt. Date PR D D Parm 1 6 32 VARYING SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE 2 of 3 ***************************** * Cvt. Date - Takes in a 6 byte parameter and translates * * it to a date and returns that date. * ***************************** P Cvt. Date B EXPORT D Cvt. Date PI D D Date. In 6 VARYING D D D Date. Work Long. Jul Wrk. Cent. Cd Wrk. Yr Wrk. Dys D Date. Fld DS 1 1 2 3 5 7 2 2 4 7 S 0 0 0 D 33 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE 3 of 3 * Right justify CYYDDD in 7 digit number C EVALR Date. Work = Date. In * Change the 1 digit code to a 2 digit number C IF Cent. Cd = 0 C Z-ADD 19 Wrk. Cent C ELSE C Z-ADD 20 Wrk. Cent C ENDIF * Convert the long Julian number into a date field C *LONGJUL MOVE Long. Jul Date. Fld * Return the date C P RETURN Date. Fld E 34 SQL/400 03/03/2003 DMC Consulting, 1993

Functions Sample Function to Convert a Date CRTRPGMOD MODULE(SQLFUNCS) DBGVIEW(*SOURCE) CRTSRVPGM(SQLFUNCS) EXPORT(*ALL) Once created, the sub procedure can be registered with the SQL engine using the SQL command shown below: CREATE FUNCTION your-schema/Cvt. Date (IN VARCHAR(6)) RETURNS DATE EXTERNAL NAME 'your-schema/SQLFUNCS(Cvt. Date)' LANGUAGE RPGLE PARAMETER STYLE GENERAL 35 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE Sample Function to Return a Running Total * Prototype for Run. Sum(QTY) D Run. Sum PR D Parm 1 D Qty. Out D In. Null 1 D Out. Null D Sql. State D Func. Name D Spec. Name D Mesg. Text D Scratch D Flag 1 of 4 8 F 8 F 5 I 0 5 517 A VARYING 128 A VARYING 70 A VARYING 8 F 10 I 0 36 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE Sample Function to Return a Running Total 2 of 4 ***************************** * Run. Sum - Takes in a floating point value and adds it * * to the previous value. * ***************************** P Run. Sum B EXPORT D Run. Sum PI D Qty. In 8 F D Qty. Out 8 F D In. Null 1 5 I 0 D Out. Null 5 I 0 D Sql. State 5 D Func. Name 517 A VARYING D Spec. Name 128 A VARYING D Mesg. Text 70 A VARYING D Scratch 8 F D Flag 10 I 0 37 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE Sample Function to Return a Running Total 3 of 4 * Trap for any error that occurs C MONITOR * If this is the first time it’s called * for this select, set the balance to 0 C IF FLAG = -1 C Clear C ENDIF Scratch * Add the input quantity to the scratch quantity C ADD Qty. In Scratch C Z-ADD Scratch Qty. Out 38 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE 4 of 4 * Return the quantity C RETURN * If any error occurs return a zero C ON-ERROR *PROGRAM C C EVAL Qty. Out = 0 C RETURN * C ENDMON P E 39 SQL/400 03/03/2003 DMC Consulting, 1993

Functions Sample Function to Calculate a Running Total CRTRPGMOD MODULE(SQLFUNCS) DBGVIEW(*SOURCE) CRTSRVPGM(SQLFUNCS) EXPORT(*ALL) CREATE FUNCTION your-schema/Run. Sum (INOUT DOUBLE) RETURNS DOUBLE EXTERNAL NAME 'your-schema/SQLFUNCS(Run. Sum)' LANGUAGE RPGLE PARAMETER STYLE DB 2 SQL NOT DETERMINISTIC SCRATCHPAD 8 FINAL CALL DISALLOW PARALLEL 40 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Function Statement General Parameter Style DB 2 SQL Parameter Style Return Values as Normal (on PI) All Input/Output Parameters All Input Parameters All Output Parameters Null Indicators for all Input Parameters Null Indocators for all Output Parameters SQL STATE Function Name Specific Name These parameters Message Text are required by Scratch Pad Data the format, but Final Flag may not need to be used. 41 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE Scratch Pad Data Selected Data Returned SQL Function Scratch Pad Data SQL Function 42 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Functions with RPGLE Final Call Data Selected Data Returned SQL Function Final Flag -1 SQL Function 0 1 43 SQL/400 03/03/2003 DMC Consulting, 1993

Functions 44 SQL/400 03/03/2003 DMC Consulting, 1993