I am taking a database course and am suppose to create a stored procedure for my database in DB2. We were just given a basic idea of a generic stored procedure and told the syntax might vary among vendors like db2, postgres etc. So I have wrote the following procedure using what we learned in class:
Connect to cs***;
CREATE PROCEDURE FRANCHISE_INFO (IN franchID INTEGER)
LANGUAGE SQL
BEGIN
DECLARE at_end INTEGER DEFAULT 0;
DECLARE vfranchID INTEGER;
DECLARE vownerID INTEGER;
DECLARE vname VARCHAR(25);
DECLARE vemail VARCHAR(30);
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE C1 CURSOR FOR
SELECT F.franchiseID, O.ownerID, O.name, O.email
FROM Franchise F, Owner O
WHERE F.franchiseID = franchID AND F.ownerID = O.ownerID;
DECLARE CONTINUE_HANDLER FOR not_found SET at_end = 1;
OPEN C1;
FETCH C1 INTO vfranchID, vownerID, vname, vemail;
WHILE @at_end = 0 DO
IF(O.name == NULL)
THEN UPDATE owner SET O.name = 'R McDonald' WHERE O.ownerID = vownerID;
END IF;
IF(O.email == NULL)
THEN UPDATE owner SET O.email = 'headoffice@mcdonald.ca' WHERE O.ownerID = vownerID;
END IF;
FETCH C1 INTO vfranchID, vownerID, vname, vemail;
END WHILE;
CLOSE C1;
END
@
I have saved this in a file named storedproc.sql and trying to compile it on the terminal using
db2 -t -f storedproc.sql
but i get the following
Database Connection Information
Database server = DB2/LINUXX8664 10.5.3
SQL authorization ID = CS******
Local database alias = CS***
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "nd
INTEGER DEFAULT 0". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=4. SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "INTEGER" was found following "DECLARE vfranchID
". Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=1.
SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "INTEGER" was found following "DECLARE vownerID
". Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=1.
SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "DECLARE vname VARCHAR" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<compile_fragment>".
LINE NUMBER=1. SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "DECLARE vemail VARCHAR" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<compile_fragment>".
LINE NUMBER=1. SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "'02000'" was found following "NDITION FOR
SQLSTATE". Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=1.
SQLSTATE=42601
DB21031E The SQL statement using the cursor "C1" ("SQLCUR1") returned:
SQL0206N "FRANCHID" is not valid in the context where it is used.
SQLSTATE=42703
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "<space>" was found following
"CONTINUE_HANDLER". Expected tokens may include: "FOR". LINE NUMBER=1.
SQLSTATE=42601
DB21028E The cursor "C1" has not been declared.
SQL0104N An unexpected token "INTO" was found following "<identifier>".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "WHILE" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<variable_set>".
SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END IF".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "=" was found following "IF(O.email =".
Expected tokens may include: "<space>". SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END IF".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
SQL0104N An unexpected token "INTO" was found following "<identifier>".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END
WHILE". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
DB21028E The cursor "C1" has not been declared.
DB21007E End of file reached while reading the command.
I have been searching the web to find what the entire process of setting up a stored procedure but no luck. I do not understand if i am using the wrong syntax for my procedure, or am i setting it up wrong. Any help will be much appreciated. Thank you :)
DB2 expects the stored procedure to be terminated (finished) by a semicolon by default. You followed good practice and used the @ to end that CREATE PROCEDURE statement, now you have to tell DB2.
db2 -td@ -f storedproc.sql
The -td@ tells DB2 to use the @ as statement terminator. Thereafter make sure that all statements in your file end with that terminator.
You could also set the terminator within that file.