0

What's the proper syntax for writing stored procedures in sqlplus? The internet is surprisingly unhelpful on this point since SQL tutorials don't seem to specify their environment, or the example they give is a little confusing. I think the simplest way to figure this is that I'll post two commands (out of several I need to make), and if someone can write what they'd look like as stored procedures, I'm sure I can figure the rest out on my own.

Command 1: code:

SELECT COUNT(username)
FROM "ValidClients"
WHERE username = [username goes here];

Command 2: code:

INSERT into "ValidClients"
/*zero is overridden by a sequence trigger*/
VALUES (0, [username], [password]);

As you can see, it's really basic stuff, I'm just used to Microsoft SQL Server and so SQLPlus syntax is tripping me up a little. Thanks in advance.

Ollie
  • 17,058
  • 7
  • 48
  • 59
buggritall
  • 151
  • 2
  • 6

1 Answers1

2

Here's an example from Google:

CREATE PROCEDURE addtuple1(i IN NUMBER) AS
BEGIN
    INSERT INTO T2 VALUES(i, 'xxx');
END addtuple1;

The entire tuturial looks kindof nice.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • I appreciate your answer, I have looked at that tutorial. It's just that (forgive me for seeming a little dumb), I had a little bit of trouble mapping the answer onto my solution. For example, I don't really understand why sometimes the NUMBER constraint is used, and sometimes INTEGER (perhaps the simple answer is that number allows decimals). It's tangential to my answer but it confuses me when looking at the syntax. – buggritall Oct 09 '11 at 10:47
  • Oracle specifies INTEGER as a subtype of NUMBER. You are correct in that NUMBER can accept fractions whereas INTEGER cannot. Internally though Oracle will still sotre an INTEGER as a NUMBER. – Ollie Oct 14 '11 at 14:43