stored procedure problem

jemz · #1 (**permalink**) 08-09-11, 09:54

Hi can you help me please,

I want to make a stored procedure in ibm data studio,to select in the employee table that there last name starts with vowel letter.
how can i make an sql statement on this?I use this statement

create procedure vowe()
dynamic result sets 1
language sql

begin

select lastname from employee
where lastname like '[AEIOU]%'

end
but it's not working,can you please help me on this...please,
by the way how can i see the records or the output?like we did in querring in the db2 command editor.

ARWinner · #2 (**permalink**) 08-09-11, 10:29

This should answer both you questions:

Code:

create procedure vowel()
dynamic result sets 1
language sql

begin
DECLARE CURSOR1 CURSOR WITH RETURN FOR
select lastname from employee
where ucase(substr(lastname,1,1)) in ('A','E','I','O','U');

OPEN CURSOR1;
end

Andy

tonkuma · #3 (**permalink**) 08-09-11, 10:32

I have no experience of data studio.

But, after seeing your requirements.

Quote:

... to select in the employee table that there last name starts with vowel letter

I thought that a table function might be better than a stored procedure.

Example:

Code:

------------------------------ Commands Entered ------------------------------
CREATE OR REPLACE
  FUNCTION vowel()
  RETURNS TABLE
( lastname VARCHAR(15) )
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
RETURN
SELECT lastname
 FROM  employee
 WHERE LOCATE( LEFT(lastname , 1) , 'AEIOU' ) > 0
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

Sample usage:

Code:

------------------------------ Commands Entered ------------------------------
SELECT *
 FROM  TABLE( vowel() ) t;
------------------------------------------------------------------------------

LASTNAME       
---------------
O'CONNELL      
ADAMSON        

  2 record(s) selected.

jemz · #4 (**permalink**) 08-09-11, 12:24

Quote:

Originally Posted by ARWinner

This should answer both you questions:

Code:

create procedure vowel()
dynamic result sets 1
language sql

begin
DECLARE CURSOR1 CURSOR WITH RETURN FOR
select lastname from employee
where ucase(substr(lastname,1,1)) in ('A','E','I','O','U');

OPEN CURSOR1;
end

Andy

Hello sir Andy,

It's working i saw the output beside in the parameter tab in the lower level part..Thank you so much sir....sir can i ask something what is declare cursor1 cursor with return for?please help me to enligthen my mind.

more power to you...

Best Regards,

Jemz

jemz · #5 (**permalink**) 08-09-11, 12:31

Quote:

Originally Posted by tonkuma

I have no experience of data studio.

But, after seeing your requirements.

I thought that a table function might be better than a stored procedure.

Example:

Code:

------------------------------ Commands Entered ------------------------------
CREATE OR REPLACE
  FUNCTION vowel()
  RETURNS TABLE
( lastname VARCHAR(15) )
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
RETURN
SELECT lastname
 FROM  employee
 WHERE LOCATE( LEFT(lastname , 1) , 'AEIOU' ) > 0
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

Sample usage:

Code:

------------------------------ Commands Entered ------------------------------
SELECT *
 FROM  TABLE( vowel() ) t;
------------------------------------------------------------------------------

LASTNAME       
---------------
O'CONNELL      
ADAMSON        

  2 record(s) selected.

Hi sir Tonkuma,

Sir it's working also, but can i ask something favor is it okay to put some comments
on this line or please help me to undersand the Deterministic,No external Action.I have not yet seen this.and i am still beginner in making sp.please help me..Thank you so much for your help.

CREATE OR REPLACE
FUNCTION vowel()
RETURNS TABLE
( lastname VARCHAR(15) )
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
SELECT lastname
FROM employee
WHERE LOCATE( LEFT(lastname , 1) , 'AEIOU' ) > 0
;

ARWinner · #6 (**permalink**) 08-09-11, 12:42

Quote:

Originally Posted by jemz

Hello sir Andy,

It's working i saw the output beside in the parameter tab in the lower level part..Thank you so much sir....sir can i ask something what is declare cursor1 cursor with return for?please help me to enligthen my mind.

more power to you...

Best Regards,

Jemz

In order to get a result set from a stored procedure, you need a CURSOR. You declare the cursor, then leave it open for the result to be returned.

Andy

jemz · #7 (**permalink**) 08-10-11, 00:39

Quote:

Originally Posted by ARWinner

In order to get a result set from a stored procedure, you need a CURSOR. You declare the cursor, then leave it open for the result to be returned.

Andy

Hello sir,

Thank you so much for this...it really helps me...more power to you always.

tonkuma · #8 (**permalink**) 08-10-11, 02:18

Quote:

... please help me to undersand the Deterministic,No external Action.

Please read documents.

For example,
you can find the Descriptions of DETERMINISTIC and NO EXTERNAL ACTION in here....

CREATE PROCEDURE (SQL) statement
CREATE FUNCTION (SQL scalar, table, or row) statement

jemz · #9 (**permalink**) 08-10-11, 10:33

Quote:

Originally Posted by tonkuma

Please read documents.

For example,
you can find the Descriptions of DETERMINISTIC and NO EXTERNAL ACTION in here....

CREATE PROCEDURE (SQL) statement
CREATE FUNCTION (SQL scalar, table, or row) statement

Sir thank you for the links...how to marked this trhead as solved?