Function for logged in user

[Hugo Nilsson]

So im fairly new to sql so this function needs to be quite simple. My code example is this:

create or replace function logga_in( p_pnr bankkund.pnr%type, p_passwd bankkund.passwd%type) return number as v_inloggad number(1); begin select count(pnr),count(passwd) into v_inloggad from bankkund where pnr = p_pnr and passwd = p_passwd; return v_inloggad; end;

When i call it like this:
SELECT logga_in(p_pnr,p_passwd) FROM dual;
It should return 1 if found and 0 if not found. I just cant figure it out

[Dave Tolls]

And what problems do you have with that code?
Does it compile/run.
If so what response do you get when you make that call?

[Tim Holloway]

I don't know what problem Hugo has, but I have a problem. This is a stored procedure implementation of the classic safe validation of login credentials. That is:

SELECT COUNT(*) FROM from bankkund WHERE pnr = p_pnr AND passwd = p_passwd

And unless the intent is to call it from some other stored procedure, it's doing the login check the hard way. Actually, it's probably over-complex even for a stored procedure.

Bust as I've said before, stored procedures are something that should be avoided whenever possible. It's better to use simple SQL.

[Hugo Nilsson]

Dave: I get: SQL Statement ignored. And: ORA-00947: not enough values.
Tim: I understand what you say and i agree with you but i need to make it a function

[Tim Holloway]

I do have to wonder why you're doing all this work in stored procedure code if you are "fairly new to SQL". Stored procedures are in the moderate-to-advanced range, not beginning-level stuff.

Nevertheless. Look at my SQL and compare to the SQL in your procedural code. See the difference? It's why you're getting the "Not enough values" message. Again, Oracle is being less helpful than most newer databases are, but despite that the error does accurately, if not precisely indicate what's wrong.

[Hugo Nilsson]

Yes sir i hear you. The thing is that i need to practice funtions(i thought this was mandatory stuff so maybe im no fairly new). So when a pnr and passwd get insertet and i call the function it should return 1 or 0 depending on how it went. Hope you understand it

[Tim Holloway]

Ah. I understand. No, I go for years at a time without using stored procedures. If you are just starting with databases, don't bother about them. Stored procedures are just programs and many databases actually allow stored procedures to be written in a choice of languages. For example, use Java instead of PL/SQL.

SQL, on the other hand, is really tricky. Stuff that seems straightforward in a program can require a lot of thought to express in SQL. And since at the root, all database operations are SQL, it is therefore essential to know how to use SQL.

Here's a copy-and-paste of something I said about stored procedures a while back:

Tim Holloway wrote:
I don't recommend using stored procedures as a general rule. There's no standard for them. They vary considerably between different brands of databases. And if you don't think that's a problem consider that Amazon used to use Oracle extensively until last year, when they completely switched over to a different DBMS platform. Straight SQL is easier to transport, especially if you're using an ORM to front for it.

Maintaining source code control for stored procedures is also a problem. Often there is little or no versioning available for the code stored in the DBMS and tools to work with code versions can be hard to come by.

Use of stored procedures in application programs can also make the applications harder/more expensive to maintain, as you may end up going on "treasure hunts" to find out what logic is in the app and what logic is in the database (stored procedure).

So when should you use stored procedures?

Well in some cases, you cannot do what you need efficiently using plain SQL. In cases like that, code that runs entirely on the server may make it worth the disadvantages I outlined above.

Another instance is when you have a critical business process that operates according to a complex set of rules and you have many applications using those rules. In cases like that, it's certainly more efficient to keep one set of rules (the stored procedure) rather than having to maintain multiple copies of them.

Still another case would be where you have users who use some sort of command-line utility to issue database commands directly such as sql*plus and you want to make those commands simple and easy to use.

So there are some good reasons for using stored procedures. But try to keep them to a minumum. Because, as I said there are some very good reasons not to.

[Hugo Nilsson]

Im sorry to be a pain in the butt. This is a school task(maybe i should have said that earlier) so i really need to do it

[Tim Holloway]

School is forgivable. It is, as I said, useful to know stored procedures. Just that in actual practice, it's good to know when to avoid them.

But again, look at the difference between my SQL statement and the SQL Select that you coded and see if you can spot what caused the error.