| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with stored procedures
A copy of this was sent to bjones8888_at_aol.com (BJones8888)
(if that email address didn't require changing)
On 28 Aug 1999 15:27:12 GMT, you wrote:
>Help! I'm a developer out onsite for a new client's installation of our
>software. To date we have only supported SQL Server and Sybase SQL Anywhere,
>but now we're implementing support for Oracle as our back end. I have 6
>working stored procedures for SQL Anywhere, but need the syntax for Oracle.
>Any assistance you can provide will be greatly appreciated, since this has to
>be working by Monday morning. (I'm in a hotel for the weekend, with Oracle
>Personal Edition on my laptop - and am assured by Oracle that there is no
>incompatibility between them -- though I've already seen some.) This message is
>rather long. I apologize. If anyone would like to have a crack at any one or
>two of them, that would be a great help.
>
>Actually, the SQL is quite simple. The problem I have is that I don't know the
>syntax for the stored procedures as Oracle requires. I took the first one and
>changed it, mostly groping in the dark, but I got it to compile. I haven't
>actually been able to test it yet, but I will later.
>
>There are six procedures that we use. Simple ones, as I mentioned, but
>important.
>
>1. PROC_AUTOID. The purpose of this one is to take the value found in the
>AUTOID table's AUTO_ID field, increment it, save that incremented number, and
>return the new number. Here's the code from SQLAnywhere:
>
>alter procedure PROC_AUTOID(@AUTOID_VALUE integer output) as declare @AUTO
>integer
>begin transaction
>select @AUTO=AUTO_ID from AUTOID holdlock
>update AUTOID set AUTO_ID=@AUTO+1
>select @AUTOID_VALUE=AUTO_ID from AUTOID
>commit transaction
>return
>
>When I tried to do this in Oracle, it didn't seem to like the idea of a
>variable parameter (which we signify in SQLAnywhere by putting the '@' in front
>of the parameter name), so what I did with Oracle was try making it a Function
>- to facilitate the returning of a value. Here's what I came up with (it
>compiles ok):
>
1- consider using a sequence. It will not block. for example, i might:
create sequence auto_id_seq;
and then code:
create or replace procedure proc_autoid( autoid_value OUT number )
as
begin
select auto_id_seq.nextval into autoid_value; end;
no commit needed -- sequences are non-blocking ID generators useful for generating these types of things.
2- if you cannot really use a sequence for whatever reason, a procedure would look like this:
create or replace procedure proc_autoid( autoid_value OUT number )
as
begin
update autoid set auto_id = auto_id+1 returning auto_id into autoid_value;
commit;
end;
/
>FUNCTION PROC_AUTOID RETURN INTEGER IS
> AUTO INTEGER;
>BEGIN
> set transaction use rollback segment AUTOID;
> select AUTO_ID INTO AUTO from AUTOID holdlock;
> update AUTOID set AUTO_ID=AUTO+1;
> select AUTO_ID INTO AUTO from AUTOID;
> commit;
>END;
>
>2. PROC_CHECKLOCK This one checks to see if there is a record in the LOCKS
>table which contains the tablename, username and recordno which are passed in.
>If there are no records in LOCKS which match the tablename, username and
>recordno, it returns false.
>
>alter procedure PROC_CHECKLOCK(@TBLINNAME char(32),@RNO
> integer,@TBLOUTNAME char(32) output,@USROUTNAME char(10)
> output,@BSTAT integer output) as
>select @TBLOUTNAME=TABLENAME, @USROUTNAME=USERID, @BSTAT=RECNO from LOCKS
>
> where TABLENAME=@TBLINNAME and RECNO=@RNO
>if @@ROWCOUNT=0 begin
> select @BSTAT=0
>end
>return
>
create procedure proc_checklock( tname in varchar2, rno in number,
tout out varchar2, userout out varchar2,
bstat out number )
as
select tablename, userid, recno
into tout, userout, bstat
from locks
where tablename = tname
and recno = rno;
>3. PROC_DELMEMO Rather than implement large strings or BLOBs in our tables, we
>take memo fields and store them in a MEMOS table, in chunks of 254 characters.
>When we delete a record in a table, and that table has a memo associated with
>it, we must also delete the pertinent records from the MEMOS table. Here's the
>simple SQLAnywhere code for it:
>
>alter procedure PROC_DELMEMO(@TableName char(24),@Name
> char(24),@RecNo integer)
>begin
> delete from Memos where TableName=@TableName and "Name"=@Name and
> RecNo=@RecNo
>end
>
I would just use an "ON DELETE CASCADE" option in the create table, however the plsql is virtually identical:
create procedure delmemo( p_tname in varchar2, p_name in varchar2, p_recno in
number )
as
begin
delete from memos where tablename = p_tname and name = p_name and recno =
p_recno;
end;
this shows why you might consider naming all inputs/output to/from plsql stored procedures with p_. if you didn't, then "and name = name and recno = recno" would match all records deleting them all. be careful of that.
>4. PROC_GETMEMO Here's the flipside of the other. Here we're returning a set
>of records from MEMOS. There are 3 parameters passed in: TABLENAME, NAME, &
>RECNO. TABLENAME is the name of the associated table for which this memo is
>stored. NAME is the field in the associated table. (Since 'Name' is a
>reserved word in SQLAnywhere, it had to be in quotes.) And RECNO is the unique
>record identifier from the associated table. The task is to find all the notes
>records from MEMOS and put them in order - hence the SEQUENCE field in MEMOS.
>Here's the SQLAnywhere code.
>
>alter procedure PROC_GETMEMO(in @TableName char(24),in @Name
> char(24),in @RecNo integer)
> result(Notes varchar(254)) begin
>select Notes from Memos where TableName=@TableName and "Name"=@Name
> and RecNo=@RecNo order by Sequence asc
>end
see the url in my signature below. there is a paper on howto return result sets from stored procedures.
>
>5. PROC_LOCK The purpose of this procedure is to enter a record into the LOCKS
>table for a given tablename, username, and recordno, if there isn't already one
>there, returning true if successful.
>
>alter procedure PROC_LOCK(@TBLINNAME char(32), @USRINNAME
> char(10), @RNO integer, @TBLOUTNAME char(32) output, @USROUTNAME
> char(10) output, @BSTAT integer output) as
>select @TBLOUTNAME=TABLENAME, @USROUTNAME=USERID,
> @BSTAT=RECNO from LOCKS
> where TABLENAME=@TBLINNAME and RECNO=@RNO
>if @@ROWCOUNT=0 begin
> insert into LOCKS(TABLENAME,USERID,RECNO)
> values(@TBLINNAME,@USRINNAME,@RNO)
> select @BSTAT=0
>end
>return
>
procedure proc_lock( p_tname in varchar2, p_userin in varchar2,
p_rno in number, p_bstat out number )
as
insert into locks values ( p_tname, p_userin, p_rno );
p_bstat := p_rno;
exception
when dup_val_on_index then p_bstat := 0; end;
>6. PROC_UNLOCK This one simply removes the record from the LOCKS table
>matching the specified tablename and recordno. Since we're only dealing with
>locks on the record level rather than field, we can ignore the fieldname in
>this procedure. We remove all locks based on the tablename and recordno.
>
>alter procedure PROC_UNLOCK(@TBLINNAME char(32),@RNO integer) as
> delete from LOCKS where TABLENAME=@TBLINNAME and RECNO=@RNO
>return
>
just like the above -- its very similar.
>Thanks for your help!!!!!!!
>
>Bob Jones
>Omni Developments, Inc. --- (Custom Software Development)
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Aug 28 1999 - 13:58:26 CDT
 |
 |