开发者

What is wrong with this simple (Oracle) SQL stored procedure code?

I've looked over so many tutorials on the net, and I see quite a few similar questions to look over that don't answer my question since they use different SQL implementations. I'm using SQLPlus, oracle 10g.

CREATE OR REPLACE PROCEDURE getuserid
(uname in varchar) AS
BEGIN
select accountId from "ValidClients"
where username = uname
END getuserid;

The code 'SELECT accountId from "ValidClients" WHERE username = 'testname' ; ' works properly, so I can't understand what I'm doing incorrectly to make SQLPlus give me a compilation error.

EDIT: No INTO clause was the answer, thanks everyone that helped. Problem solved on the one hand, can I just ask to confirm: a java program will be calling these stored procedures and trying to store them in a resultset. Will the following code work fine for the above stored procedure?

CallableStatement cs = connection.prepareCall("{call getuserid(?)}");
cs.setString(1, user);
ResultSet rs = cs.executeQuery();

int userid = Integer.parseInt(rs.getString(1));

EDIT2: Ignore the above, it's kept for posterity. Not only is it开发者_StackOverflow big enough for a question of its own, but it's easily solved by a Google Search and doesn't require a customised answer.


where do you put the result of the query ? Assuming accountId is a number, you may try:

CREATE OR REPLACE PROCEDURE getuserid
(uname_p in varchar, accountId_p out NUMBER) AS
BEGIN
select accountId into accountId_p from "ValidClients"
where username = uname_p;
END getuserid;

Regards


add a semicolon to the end of the select and you need to select into a variable

e.g:

CREATE OR REPLACE PROCEDURE getuserid (uname in varchar) AS 
    v_account Number;
BEGIN 
    SELECT accountId INTO v_account 
    FROM "ValidClients"
    WHERE username = uname; 
END getuserid;


It should be:

CREATE OR REPLACE 
PROCEDURE getuserid (
   uname in varchar2
) 
AS 
   v_account_id ValidClients.account_id%TYPE;
BEGIN 
   select accountId 
    into v_account_id
    from "ValidClients" 
   where username = uname;
END getuserid; 

You might want to consider what you are going to do with the value you return. Either make an OUT parameter or better still, if you are returning one value make it a FUNCTION.

CREATE OR REPLACE 
FUNCTION getuserid (
   uname in varchar2
) 
RETURN ValidClients.account_id%TYPE
AS 
   v_account_id ValidClients.account_id%TYPE;
BEGIN 
   select accountId 
    into v_account_id
    from "ValidClients" 
   where username = uname;

   RETURN v_account_id;
EXCEPTION
   WHEN no_data_found
   THEN
      -- Handle account ID not being present
   WHEN others
   THEN
      -- Handle other errors.
END getuserid; 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜