Re: Stored Procedures/Triggers Standards

------------------------------------------------------------------------------ REPLY FROM: RICHARDSON Marv Return-Path: <ilist@rmy.emory.edu> Received: from technet1.shl.com by mm-gw.shl.com id <2BEFE769@mm-gw.shl.com>; Tue, 11 May 93 10:50:33 PDT Received: from rmy.rmy.emory.edu by technet1.shl.com (4.1/SMI-4.1.7) id AA19367; Tue, 11 May 93 10:54:33 PDT Received: by rmy.rmy.emory.edu (5.65/Emory_rmy.3.4.0) via MAILPROG id AA23881 ; Tue, 11 May 93 13:56:59 -0400 Return-Path: ilist@rmy.emory.edu From: graeme@pyra.co.uk (Graeme Sargent) Message-Id: <1993May11.132132.5553@pyra.co.uk> Subject: Re: Stored Procedures/Triggers Standards? Date: Tue, 11 May 1993 13:21:32 GMT Reply-To: graeme@uk.pyramid.com (Graeme Sargent) Organization: Pyramid Technology Ltd. Sender: informix-list-owner@rmy.emory.edu To: informix-list@rmy.emory.edu X-Informix-List-To: mrichardso@shl.com X-Informix-List-Id: <news.3280> In <C6sx0r.sA@cbfsb.cb.att.com> nh@cbnewsg.cb.att.com (nicholas.hounsome) writes: >>Can anyone help me with two small questions? > >>1. I get the impression from recent postings that Informix 5.0 >>has stored procedures or triggers or whatever the hell they are called. > >Stored procedures, Yes. Triggers, No. > >>(You can tell that I am a bit in the dark here but I have some broad idea >>what they are, that at least Sybase has them and that they might be very >>useful to us). Is this the case and can anyone give a brief description? >Sybase has had triggers since the beginning. I don't think they have >Stored Procedures yet, although I believe this is "imminent" in System >10. In fact, Sybase 4.9 does have stored procedure as well as triggers. I believe they have had sp throughout 4.x, if not earlier. >>2. How similar are different vendors implemenations given that (as far as >>I know) they have not been put into the ANSI standard? What I realy want to >>know here is whether we could keep most of our code the same if we wanted >>to use either of Informix or Oracle using embedded SQL. >Informix 5.00 has Stored Procedures. Triggers are believed to be >coming, but i can't remember whether it's 5.10 or 6.00. 5.00 also has >Declarative Referential Integrity (referential integrity is what most >people are believed to want triggers for). Triggers are useful for *many* more purposes. However, well implemented declarative RI is a better way to accomplish RI than triggers. Declarative RI is required for SQL92 entry-level compliance. >ORACLE7 has Stored Procedures, Stored Functions, Stored Packages, >Triggers(???) and Declarative Referential Integrity. Oracle Version 6 >has none of these. ORACLE7 does have triggers. Oracle V6 supported declarative RI syntax, but did not enforce it (basicly letting them get by a few more checklists!). >Oracle define a Stored Function as a Stored Procedure which returns a >value. Informix Stored Procedures can return a value using a RETURNING >clause. ORACLE7 Stored Procedures also appear to be able to do this! >An ORACLE7 Stored Package is a named group of Stored Procedures and/or >Functions. You can think of it as similar to a library in 3GL terms, >although the intent seems to be that it should have one specific >purpose, rather than be a collection of loosely related routines. It >allows you to hide Procedures/Functions in the Package, so that they can >only be executed by other Procedures/Functions in that Package, and >cannot be executed by name. >The CREATE PROCEDURE syntax is very similar in Informix and ORACLE7, and >I believe is derived from proposals for future ANSI standards. Oracle >use their PL/SQL language as the syntax for writing procedures in. >Informix uses SQL + SPL (Stored Procedure Language, which is broadly >similar to, but syntactically different from the procedural bits of >I4GL). >I believe that the upshot of this is that you could, with careful >design, use Stored Procedures in both products, as they can both achieve >the same kind of goal. You would, however, need two sets of source, to >cope with the differences between PL/SQL and SPL. You would probably >be best advised to steer clear of Stored Packages, which would be >undesirable in an Oracle only environment. >The Declarative Referential Integrity is I think syntactically compatible >in both products, except that ORACLE7 has Cascade Delete, which OnLine >does not yet have. This syntax is also based on ANSI proposals, I >believe. As mentioned, this is in the ANSI standard released Nov 92. Oracle7 has been certified at the entry level by NIST. I'm not sure about Informix. Sybase claims that System 10 will have declarative RI, and Ingres claims that 6.5 (due Q194) will as well. >As for Triggers, I believe I've seen them in the ORACLE7 marketing >blurb, but haven't yet stumbled across them in the product, although I >haven't yet specifically looked for them. >graeme >-- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.