Re: Stored Procedures/Triggers Standards?

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. >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). ORACLE7 has Stored Procedures, Stored Functions, Stored Packages, Triggers(???) and Declarative Referential Integrity. Oracle Version 6 has none of these. 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 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.