SQL Various Dialect

SQL is the standard language but every platform has their own features. Just like dialects with languages, SQL has its own dialects.  Oracle PLSQL is more suitable to complex programming languages and applications with introduction of Packages, Loop, Records, Application Context, advance triggers etc. With Oracle PLSQL you can construct object relational database system with object types, class and hierarchy. TSQL on the other hand is utilized by Microsoft and Sybase (ASE & Anywhere to a degree) but is not rigged or objected oriented like PLSQL which makes it easier to implement.  Postgres extended the Oracle PLSQL by introducing new features that makes it practical for web and geographical systems.  Each platform has extensions to SQL that makes them suitable for various applications and audience. Listed below are the various dialects of SQL from Oracle PLSQL to DB2 and Postgres. Notice their similarities and differences in composition.  

Oracle PLSQL Codes

Triggers 

SQL>  CREATE OR REPLACE TRIGGER statecodesAdut_inst_trigg

  2     AFTER INSERT on  STATECODES

  3      FOR EACH ROW

  4  DECLARE

  5    NSTID INTEGER;

  6    NCCD INTEGER;

  7        NSTN Varchar2(45);

  8    NSTAB Varchar2(10);

  9    NSTRT Integer;

 10    ACT  char(20);

 11    BEGIN

 12   SELECT :NEW.State_ID INTO NSTID FROM DUAL;

 13   SELECT :NEW.Country_Code INTO NCCD FROM DUAL;

 14   SELECT :NEW.State_Name INTO NSTN FROM DUAL;

 15   SELECT :NEW.State_Abbrv INTO NSTAB FROM DUAL;

 16   SELECT :NEW.State_TaxRate INTO NSTRT FROM DUAL;

 17   SELECT 'INSERT' INTO ACT FROM DUAL;

 18 

 19       INSERT INTO STATECODESAUDIT( State_ID ,Country_Code,State_Name,State_Abbrv,State_TaxRate,

 20    Action_Type)

 21        SELECT NSTID, NCCD, NSTN,NSTAB,NSTRT,ACT FROM DUAL;

 22 

END statecodesAdut_inst_trigg;

Not Null Constraint

SQL> Alter table receipt

  2  modify receipt_finalamt constraint receipt_repfinamt_nn not null

  3  modify receipt_amount constraint receipt_repamount_nn not null;

Function(UDF)

SQL>  CREATE OR REPLACE FUNCTION STUDENTNAME

  2   (studid in student.student_id%TYPE

  3)

  4    RETURN

  5     VARCHAR2 IS

  6

  7   STUDENT_NAME VARCHAR2(50);

  8   BEGIN

  9

 10  SELECT  INITCAP (S.FIRST_NAME||','||S.LAST_NAME)AS NAME

 11   INTO

 12   STUDENT_NAME

 13   FROM STUDENT S

 14   WHERE S.STUDENT_ID =  studid;

 15

 16   RETURN STUDENT_NAME;

 17

 18   END STUDENTNAME;

 19   /

Function created.

Calling Functions

SQL>  SELECT STUDENTNAME(STUDENT_ID)STUDENT_NAME

  2   FROM STUDENT

  3   WHERE STUDENT_ID =120;

Stored  Procedure

create or replace procedure city_insert

 (city_name cities.city_name%TYPE,

 Location_state cities.Location_State%TYPE,

 country cities.country%TYPE

 )

   AS BEGIN

      INSERT INTO cities(city_name, location_state, country)

                     values(city_name, location_state, country);

End city_insert;

/

Procedure created.

Calling stored procedures

SQL>  Begin

  2      city_insert ('Washington','DC','USA');

  3     city_insert ('Chicago','IL','USA');

  4   End;

  5   /

PL/SQL procedure successfully completed.

DB2 SQL Codes

Stored Procedures

CREATE PROCEDURE GOSALESCT.Order_Rec()

     LANGUAGE SQL

      DYNAMIC RESULT SETS 1

      READS SQL DATA

      NOT DETERMINISTIC

      CALLED ON NULL INPUT

 BEGIN 

  DECLARE CUSTORDER CURSOR WITH RETURN  FOR

 SELECT COH.CUST_CODE, CUST.CUST_FIRST_NAME CONCAT ','CONCAT CUST.CUST_LAST_NAME AS Cust_Name,

                  COH.CUST_ORDER_NUMBER ,VARCHAR_FORMAT(COH.CUST_ORDER_DATE,'Day-dd-Month-YYYY')AS Order_Date,

                  COH.CUST_SALES_TAX as Sales_Tax, COH.CUST_SHIP_COST, COH.CUST_SUBTOTAL,

                  COH.CUST_TOTAL, COH.CUST_TOTAL_QUANTITY,CRTCD.CRDT_METHOD_CODE ,

                  VARCHAR_FORMAT(CRTCD.CUST_CC_EXP_DATE, 'Day-dd-Month-YYYY')AS Card_ExpDate,

                  CRTCD.cust_cc_id,PRD.PRODUCT_NUMBER,PRD.product_NAME

                        FROM GOSALESCT.CUST_CUSTOMER CUST

                        INNER JOIN GOSALESCT.CUST_ORDER_HEADER COH

                        ON

                        CUST.CUST_CODE = COH.CUST_CODE

                        INNER JOIN GOSALESCT.CUST_CRDT_CARD CRTCD

                        ON

                        CUST.CUST_CODE = CRTCD.CUST_CODE

                        INNER JOIN GOSALESCT.CUST_ORDER_DETAIL COD

                        ON

                        COD.CUST_ORDER_NUMBER = COH.CUST_ORDER_NUMBER

                        INNER JOIN

                        GOSALES.PRODUCT_NAME_LOOKUP PRD

                        ON COD.PRODUCT_NUMBER = PRD.PRODUCT_NUMBER

                        WHERE PRD.PRODUCT_LANGUAGE ='EN';

OPEN CUSTORDER;

END

Functions(UDF)

create function GOSALESCT.CUST_AGE

(CUSTCODE INTEGER)

RETURNS INTEGER

NOT DETERMINISTIC

LANGUAGE SQL

CALLED ON NULL INPUT

RETURN SELECT CUST_AGE FROM GOSALESCT.CUST_CUSTOMER WHERE CUST_CODE = CUSTCODE

Triggers  

CREATE TRIGGER    GOSALESCT.RECORD_INSERT

AFTER INSERT 

 ON GOSALESCT.CUST_ORDER_REC

 REFERENCING NEW AS INSERTED

FOR EACH ROW MODE DB2SQL

INSERT INTO GOSALESCT.CUST_ORDER_ADUIT (REC_ID,cust_code,cust_name,cust_order_number,order_date,

      cust_sales_tax,cust_ship_cost,cust_subtotal,cust_total,cust_total_quantity,

      crdt_method_code,card_expdate,cust_cc_id,

      product_number,product_name, ACTION_TYPE)

VALUES(INSERTED.REC_ID,INSERTED.cust_code,INSERTED.cust_name,INSERTED.cust_order_number,INSERTED.order_date,

INSERTED.cust_sales_tax,INSERTED.cust_ship_cost,INSERTED.cust_subtotal,INSERTED.cust_total,INSERTED.cust_total_quantity,

      INSERTED.crdt_method_code,INSERTED.card_expdate,INSERTED.cust_cc_id,

      INSERTED.product_number,INSERTED.product_name, 'INSERT');

PostgreSQL

Functions & Trigger

MYSQL

 Functions and Stored Procedures