StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's an illustration. Basically, the RETURNING clause lists types (optionally followed by AS name - but the name does not declare a variable); then in the body of the procedure you define variables 'as usual' and then return them.</p> <p>This particular example is a simulation of Oracle's NEXT_DAY function, minus globalization. The file includes a self-test:</p> <h3>Example Procedure</h3> <pre><code>-- @(#)$Id: next_day.spl,v 1.1 2004/10/05 21:39:18 jleffler Exp $ -- -- @(#)Create procedure equivalent to Oracle's NEXT_DAY() function. -- Bugs: not internationalized. CREATE PROCEDURE 'oracle'.next_day(dateval DATE, dayname CHAR(3)) RETURNING DATE AS next_date; DEFINE rv DATE; -- Return value. DEFINE dw INTEGER; -- Weekday corresponding to dayname. DEFINE wd INTEGER; -- Weekday corresponding to dateval. DEFINE dn CHAR(3); LET rv = NULL; IF dateval IS NOT NULL THEN LET dw = NULL; LET dn = UPPER(dayname); IF dn = 'SUN' THEN LET dw = 0; ELIF dn = 'MON' THEN LET dw = 1; ELIF dn = 'TUE' THEN LET dw = 2; ELIF dn = 'WED' THEN LET dw = 3; ELIF dn = 'THU' THEN LET dw = 4; ELIF dn = 'FRI' THEN LET dw = 5; ELIF dn = 'SAT' THEN LET dw = 6; END IF; IF dw IS NOT NULL THEN LET wd = WEEKDAY(dateval); LET rv = dateval - wd + dw; IF wd >= dw THEN LET rv = rv + 7; END IF; END IF; END IF; RETURN rv; END PROCEDURE; </code></pre> <h3>Self-Test Code</h3> <pre><code>-- February 2001 -- S M Tu W Th F S -- 1 2 3 -- 4 5 6 7 8 9 10 -- 11 12 13 14 15 16 17 -- 18 19 20 21 22 23 24 -- 25 26 27 28 CREATE TEMP TABLE test_next_day ( ref_date DATE NOT NULL, nxt_day CHAR(9) NOT NULL, exp_date DATE NOT NULL ); INSERT INTO test_next_day VALUES(MDY(02,01,2001), 'Tuesday', MDY(02,06,2001)); INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Tuesday', MDY(02,06,2001)); INSERT INTO test_next_day VALUES(MDY(02,03,2001), 'Tuesday', MDY(02,06,2001)); INSERT INTO test_next_day VALUES(MDY(02,04,2001), 'Tuesday', MDY(02,06,2001)); INSERT INTO test_next_day VALUES(MDY(02,05,2001), 'Tuesday', MDY(02,06,2001)); INSERT INTO test_next_day VALUES(MDY(02,06,2001), 'Tuesday', MDY(02,13,2001)); INSERT INTO test_next_day VALUES(MDY(02,07,2001), 'Tuesday', MDY(02,13,2001)); INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Monday', MDY(02,05,2001)); INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Wednesday', MDY(02,07,2001)); INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Thursday', MDY(02,08,2001)); INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Friday', MDY(02,09,2001)); INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Saturday', MDY(02,03,2001)); INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Sunday', MDY(02,04,2001)); SELECT ref_date, nxt_day, WEEKDAY(ref_date) AS ref_dow, exp_date, WEEKDAY(exp_date) AS exp_dow, NEXT_DAY(ref_date, nxt_day) AS got_date FROM test_next_day INTO TEMP test_next_day2; SELECT * FROM test_next_day2 ORDER BY ref_date, nxt_day; SELECT "**FAILED**" AS failed, * FROM test_next_day2 WHERE (exp_date IS NOT NULL AND got_date IS NULL) OR (exp_date IS NULL AND got_date IS NOT NULL) OR (exp_date IS NOT NULL AND got_date IS NOT NULL AND exp_date != got_date) ORDER BY ref_date, nxt_day; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload