How to write a stored procedure? 2

[spook007]

I'm a newbie trying to write a stored procedure in Oracle 9i. I would like to send a parameter to the stored procedure that would then look for articles that have fulltext. Here is my pseudocode.

procedure (p_fulltext)
SELECT *
FROM ARTICLE
WHERE ARTICLE_FULLTEXT=p_fulltext;

I've read info regarding cursors and such, but I haven't found an example where they show you how to set up a cursor inside of a procedure. If anyone know of a good source I would really appreciate it if you could let me know. Thanks

 

[JediDan]

I learned most of what I know about PL/SQL from looking at other stored procedures/functions already in our database. I always prefer to look at real examples when learning something.

But, a real quick introduction:

CREATE OR REPLACE PROCEDURE findFullText(p_fulltext varchar2) is

cursor articles is
SELECT ArticleNbr
FROM Article
WHERE Article_FullTest = p_fulltext;

begin
     for art in articles loop
        dbms_output.put_line(art.ArticleNbr);
     end loop;
end;

Notice that I made up an ArticleNbr column for your Article table. It's better to select only the specific columns that you'll need to access when declaring a cursor.

After declaring the cursor, which is nothing more than your SELECT statement, you BEGIN the actual procedure.

Here, there's a cursor for loop that goes through all of the elements returned by the cursor. (A cursor for loop has an implicit open, fetch and close, so you don't need to worry about that).

Each time through the cursor, you can reference the columns in the cursor by saying art.<column_name). Note that &quot;art&quot; is the arbitrary identifier I specified in the cursor for loop, referencing the cursor named &quot;articles&quot;.

Then, for each iteration, it simply writes the ArticleNbr to the output.

That's about it, there's tons more examples in this forum and elsewhere on the Web. If you're going to be doing a lot of PL/SQL coding, I would recommend picking up a book. The O'Reilly book is good by Feuerstein/Pribyl.

Good luck.

 

[spook007]

JediDan;

Thank you, your example certainly made some point clear to me. I've made some changes to my procedure, but how do I run the procedure?

 

[JediDan]

Since this is a procedure (and not a function), you need to place it inside another (anonymous) PL/SQL block:

begin
     findFullText('some text');
end;
/

Just type this in or load it from a file into SQL*Plus.

 

[SantaMufasa]

Or, as an even more abreviated invocation from a SQL*Plus prompt, you can invoke your procedure as follows:

SQL> exec findFullText('some text')

Cheers,

Dave

 

[SantaMufasa]

Spook,

As long as you are learning about cursors, here is a slightly more compact version of JediDan's example from four posts above:

CREATE OR REPLACE PROCEDURE findFullText(p_fulltext varchar2) is
begin
for art in (SELECT ArticleNbr
FROM Article
WHERE Article_FullText = p_fulltext) loop
dbms_output.put_line(art.ArticleNbr);
end loop;
end;
/