I'm completely new to SQL and am trying to execute a stored procedure that I'm not even sure I wrote correctly. It's supposed to give the total number of patients for a given dr, and this is the stored procedure I managed to compile in Oracle SQL Developer:
CREATE PROCEDURE Totalptcount
(drid IN INTEGER,
totalpts OUT INTEGER)
IS
BEGIN
SELECT COUNT(pts) AS totalpts
INTO totalpts
FROM ptlist
WHERE drid = '1';
END Totalptcount;
I'm trying to input the value '1' and get out the total pt count (row count) for dr w/the ID '1'.
How do I execute this? Thanks so much in advance
Tried a number of answers I found here for similar questions but none worked. I also don't know how to use cursors or declare but I don't think they'd be needed for my stored procedure (I think). I've tried:
var totalpts NUMBER
EXEC totalptcount (:totalpts);
SELECT totalpts;
- This gave the INTO clause requirement error
BEGIN
Exec totalptcount
END;
- I don't really know what I'm doing and just tried this
BEGIN
Exec totalptcount('1')
END;
- Trying to input value '1' to get out the count
- Unknown command error
Thank you in advance!
Calling a stored procedure (named PL/SQL unit) is done within another PL/SQL block. SQL*Plus however has a shortcut, EXEC
which simply wraps the command in a PL/SQL BEGIN .... END;
wrapper. You can retrieve the value into a bind variable or a local PL/SQL variable and print it to your display in various ways:
In SQL*Plus:
var totalpts NUMBER
EXEC Totalptcount(1,:totalpts);
print totalpts
Or
var totalpts NUMBER
BEGIN
Totalptcount(1,:totalpts);
END;
print totalpts
Or:
set serveroutput on;
DECLARE
totalpts number;
BEGIN
Totalptcount(1,totalpts);
dbms_output.put_line(totalpts);
END;
Or even:
var totalpts NUMBER
DECLARE
totalpts number;
BEGIN
Totalptcount(1,var_totalpts);
:totalpts := var_totalpts;
END;
print totalpts
Lots of options, and this is just for SQLPlus client. How you call a procedure and retrieve the result depends on the client technology stack you are using (in this case, SQLPlus, but it could be many things).
Keep in mind, however, that for most basic SQL needs, you don't need procedures at all. Simply write SQL that obtains the result you need and execute it directly. We use PL/SQL when we have more complex needs that require full programmatic flow control that SQL by itself doesn't provide.