LewisC’s An Expert’s Guide To Oracle Technology

Today I will be writing about triggers. One of the questions I get fairly often is “what is the difference between a function, a procedure and a trigger?” I already wrote about functions and procedures in Learn Oracle: Procedures and Functions. You should probably read that one before you read this post.

What is a trigger?

A trigger is a special case of stored procedure that is fired during an event rather than being explicitly executed. A function or a procedure can be called from a command line or from within a different calling program.

A few days ago, I posted an entry, Anchored Declarations – %TYPE and %ROWTYPE. That article talked about using anchored declarations in your PL/SQL code.

PeteF ask a couple of questions about that post:

Inglés:

Nice little article – I have a couple of questions… Is there any way to utilise the %rowtype attribute from within a trigger i.e. can I easily populate a rowtype variable with all the old (or new) columns in one shot? Number two – is there an easy way to write the rowtype variable back to the DB without having to mention every column name?

¿Cuál es SQL?

El SQL se compone de DML y de DDL. DML son las palabras claves que utilizas tener acceso y manipular a datos, por lo tanto la lengua de manipulación de datos conocida. DDL son las palabras claves que utilizas crear objetos tales como opiniónes, tablas y procedimientos, por lo tanto el idioma de definición de datos conocido. Los ejemplos de DML son SELECT, UPDATE, INSERT, MERGE, DELETE, etc. Los ejemplos de DDL son CREATE TABLE, ALTER VIEW, CREATE OR REPLACE PROCEDURE, el etc.

Ejemplo DDL:

CREATE TABLE whatever (   column1 NUMBER NOT NULL PRIMARY KEY,   column2 VARCHAR2(10),   column3 DATE );

Ejemplo DML:

INSERT INTO whatever (column1, column2, column3)  VALUES (1, 'Text Data', sysdate );

SELECT column1, column2, column3  FROM whatever;

COMMIT;

¿Cuáles son comandos del SQL?

Los comandos del SQL son los comandos que no son realmente una parte del estándar del SQL pero son apoyados por las herramientas que apoyan el SQL.

Procedimientos CONTRA funciones

Los procedimientos son tradicionalmente el workhorse del mundo de la codificación y las funciones son tradicionalmente los pedazos más pequeños, más específicos del código. Generalmente si necesitas poner al día la carta de cuentas, escribirías un procedimiento. Si necesitas recuperar el código de la organización para una cuenta particular de GL, escribirías una función.

Aquí están algunas más diferencias entre un procedimiento y una función:

• Una función DEBE volver un valor
• Un procedimiento no puede volver un valor
• Los procedimientos y las funciones pueden ambos datos de vuelta en IN y IN OUT parámetros
• La declaración de vuelta en una función vuelve control al programa que llama y vuelve los resultados de la función
• La declaración de vuelta de un procedimiento vuelve control al programa que llama y no puede volver un valor
• Las funciones se pueden llamar de SQL, procedimiento no pueden
• Las funciones se consideran las expresiones, procedimiento no son

Eso está sobre todas las diferencias que puedo pensar en de la tapa de mi cabeza.

I was browsing around YouTube tonight and ran across this video of a webinar that Steve Feuerstein did back in 2005. It’s about an hour long. He covers various pl/sql tips that for the most part you should know by now unless you are just starting out. It is probably a good refresher for someone who doesn’t spend most of their time coding PL/SQL.

Some of the things he calls mistakes, I would not. Some are just preference issues. Worth watching though.

LewisC

oracle video pl/sql tips

For Basic SQL:

1. How do you convert a date to a string? To_char. A bonus would be that they always include a format mask.
2. What is an aggregate function? Grouping, sums or counts, etc.
3. What is an interval? Specifies a period of time.
4. What is a nested subquery? A subquery in a where clause.
5. What is the dual table? A single row table provided by oracle for selecting values and expressions.

For Basic PL/SQL:

1. Describe the block structure of PLSQL. Declaration, Begin, exception, end.
2. What is an anonymous block?

I started working on a task for a project and decided that there were some very good properties that I could use to help me teach people PL/SQL and XML. I expanded a bit on the idea and ended up with a scripting language, implemented in XML, that can be passed into a PL/SQL procedure. The script can execute stored procedures, it has looping logic, conditional (CASE) logic, user defined variables, etc.

The nice thing about it is that it’s very easy to read and modify. The XML structure is also very easy to read.

Oracle provides a lot of XML functionality in the database. This entry will cover the different tools Oracle provides and future entries will get into the details of each.

The three primary XML tools that oracle provides are: XML Developers Kit (XDK), XML DB and SQL Extensions for XML (SQLX and XMLType). Below is a definition of each.

XDK

The XDK is a complete XML development toolkit that was introduced for 8i. I think the first version was labeled XDK9i but the toolkit actually was available for 8i. I remember using the beta product.

In Part 1 of this series, we talked about how Oracle objects compare to Java and how to create Oracle Objects. In Part 2, I covered we covered object comparison and type inheritance. Today, we’ll talk about polymorphism and type evolution.

Like parts 1 and 2, this will be a technical discussion.

Polymorphism

First a definition. What is Polymorphism and why is it important? One of the best definitions I have found is this link at OnJava. You can skip the part about object serialization. Not really that important to Oracle’s OO as you’re already in the database.

Let’s go back to our (most basic) calculator example.

CREATE OR REPLACE TYPE Calculator AS OBJECT (
  value NUMBER,
  MEMBER PROCEDURE print,
  MEMBER PROCEDURE add1( p_amt IN NUMBER ),
  MEMBER PROCEDURE subtract( p_amt IN NUMBER )
  )
  NOT FINAL;
/

CREATE OR REPLACE TYPE BODY Calculator AS
  MEMBER PROCEDURE print IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( TO_CHAR( value ) );
  END;

  MEMBER PROCEDURE add1( p_amt IN NUMBER )  IS
  BEGIN
    value := value + p_amt;
  END;

  MEMBER PROCEDURE subtract( p_amt IN NUMBER )  IS
  BEGIN
    value := value - p_amt;
  END;

END;
/

And our Advanced Calculator:

CREATE OR REPLACE TYPE AdvancedCalculator UNDER Calculator (
  pi FLOAT,
  CONSTRUCTOR FUNCTION AdvancedCalculator
     RETURN SELF AS RESULT ,
  MEMBER PROCEDURE multiply( p_amt IN NUMBER ),
  MEMBER PROCEDURE divide( p_amt IN NUMBER ),
  OVERRIDING MEMBER PROCEDURE print
   )
NOT FINAL;
/

This isn’t exactly the same AdvancedCalculator.

This is a continuation of Object Orientation in Oracle. I guess I should have called it the Sorcerer of OOO. This is a technical entry.

Anyway, in part 1 I covered the basics of creating an Oracle object type and showing how it compared to a Java class. Today I’m going to cover the concepts of comparing objects and Inheritance. In part 3, I will cover type evolution and Polymorphism and in part 4 I will cover Object Views and Object Tables.

Comparing Objects

Many times, you will be working with multiple instances of the same object.

I could have called this OO In Oracle: A Techie’s View. But the Sorcerer of OO is a much cooler title. You have to pronounce OO as ooh! as in Ooh and Aah!

What is OO? OO stands for Object Oriented. OOD is OO Design, OOA is OO Analysis and OOP is Object Oriented Programming. Oracle is an ORDBMS, an Object Relational Database Management System. In this article, I am going to present a sample java class and show how you would implement the same class in Oracle. This is a technical article comparing Oracle objects with Java.