Home » SQL & PL/SQL » SQL & PL/SQL » How to define and use procedures
How to define and use procedures [message #288366] Mon, 17 December 2007 04:36 Go to next message
drimades
Messages: 13
Registered: June 2007
Junior Member
is the following procedure correct?

CREATE OR REPLACE PROCEDURE PrintAuthors AS
DECLARE
Surname Author.Surname%TYPE;
Name Author.Name%TYPE;
i NUMBER;
CURSOR c IS
SELECT Surname, Name
FROM Author ;
BEGIN
OPEN c; i:=0;
LOOP
FETCH c INTO Surname, Name;
i:=i+1;
PRINT: Surname ;
PRINT: Name ;
EXIT WHEN c%NOT FOUND;
END LOOP;
CLOSE c;
END PrintAuthors

how can I use it in a complete pl/sql program for testing?

Re: How to define and use procedures [message #288369 is a reply to message #288366] Mon, 17 December 2007 04:50 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
your procedure seems to have a many mistakes.

1) you don't use a DECLARE in procedures.


create or replace procedure test_proc is
cursor c1 is select ename from emp;
l_name emp.ename%type;
begin
open c1;
loop
fetch c1 into l_name;
exit when c1%notfound;
dbms_output.put_line(l_name);
end loop;
end;


executing this proc:(from sql+)
exec test_proc;


refer the doc's for cursors and procedures syntax.

[edit] removed my 2nd remark.

regards,

[Updated on: Mon, 17 December 2007 04:54]

Report message to a moderator

Re: How to define and use procedures [message #288370 is a reply to message #288366] Mon, 17 December 2007 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

is the following procedure correct?

Correct in which way?
Syntaxically? Just try to compile it. (the answer is no, for instance there is no PRINT instruction in PL/SQL)
Does it fit your business requirement? Only you can know it.
Does PL/SQL the good way to do it? No.
...

Regards
Michel
Re: How to define and use procedures [message #288482 is a reply to message #288370] Mon, 17 December 2007 14:06 Go to previous messageGo to next message
darrinallen
Messages: 2
Registered: December 2007
Junior Member
I like to be carefull. I am not sure I would use the REPLACE, when you are initially declaring the procedure
Re: How to define and use procedures [message #288490 is a reply to message #288482] Mon, 17 December 2007 14:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
There is no harm in using "create or replace"
This construct is the normal way of defining a procedure/function/package and enables you to both create the object if it does not yet exist and recreate it if it does exist.
Re: How to define and use procedures [message #288494 is a reply to message #288490] Mon, 17 December 2007 15:11 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
... i.e. saves you from combination of two statements: DROP PROCEDURE and CREATE PROCEDURE. CREATE OR REPLACE does both in one step.
Re: How to define and use procedures [message #288566 is a reply to message #288494] Tue, 18 December 2007 00:47 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...and you don't have to re-grant all the privileges you granted.

Regards
Michel
Previous Topic: One record per id
Next Topic: how can we know the execution performance
Goto Forum:
  


Current Time: Sun Dec 04 14:56:46 CST 2016

Total time taken to generate the page: 0.16770 seconds