Tutorial 9 - IBMDB2

SQL PL Stored Procedures

Stored Procedures

• Objek database yang biasanya mengandung satu atau banyak perintah SQL sebaik logika procedural(bisnis)

• Dieksekusi dan diatur oleh DB2(objek sisi server), dapat ditulis menggunakan SQL PL, C/C++,Java, Cobol, Bahasa pendukung CLR, dan OLE

• Manfaat untuk menggunakan stored procedures termasuk :

-  Logika bisnis yang tersentralisasi yang mempromosikan penggunaan kembali kode

-  Meningkatkan keamanan

-  Meningkatkan performa

IBM Data Studio

• Berdasarkan pada Eclipse
• IDE yang berkemampuan yang mengizinkan anda untuk :

-  Membuat,edit,debug,mengembangkan, test Stored Procedures, UDF

-  Mengembangkan aplikasi SQLJ

-  Create, alter, and drop objek database DB2 (dengan analisis akibat)

-  Memperluas dan edit data –relasional dan XML

-  Secara visual membangun perintah SQL dan XQuery

-  Optimasi query-query menggunakan Visual Explain

-  Mengembangkan query-query dan rutinitas untuk aplikasi pureXML

-  Melakukan tugas pemindahan data

-  Mengkolaborasi dan membagi proyek dengan anggota team.

-  Dengan cepat membangun pelayanan web SOAP dan REST

-  Menemukan relasi objek database dengan model data fisik(diagram)

-  Memvisualisasi distribusi data lewat tabel

Membuat Stored Procedure

• Dengan Command Editor:

    connect to sample

    create procedure p1 begin end

•  Dengan menggunakan IBM Data Studio

Atribut Stored Procedure yang opsional

• LANGUAGE SQL : Ini mengatakan pada DB2 bahwa anda akan membuat SQL PL stored procedure.Untuk bahasa lain anda dapat menggunakan LANGUAGE JAVA (untuk Java), LANGUAGE C (untuk C), etc

• RESULT SETS  <n> (dibutuhkan jika mengembalikan result sets) :Anda harus mengindikasi berapa banyak result set yang akan dikembalikan Stored Procedure.Jika tidak ada result set yang dikembalikan anda tidak memerlukan perintah ini.

• SPECIFIC my_unique_name : Ini menyediakan nama unik dalam kasus dimana anda memperbanyak SP (nama sama untuk stored procedure tapi berbeda angka argumennya).Yang bisa sama sebagai nama prosedur atau disarankan untuk :

–         GRANT EXECUTE ON SPECIFIC PROCEDURE ...

–         DROP SPECIFIC PROCEDURE ...

Parameters

CREATE PROCEDURE proc(IN p1 INT, OUT p2 

                                               INT, INOUT p3 INT)

-  IN - Input parameter

-  OUT - Output parameter

-  INOUT - Input and Output parameter

- All params harus bisa menyediakan dalam perintah CALL Komentar dalam SQL PL Stored Procedures : - -(komentar pada sql) dan /* */(bisa dalam SP)

Deklarasi Variabel

DECLARE var_name  <data type> [ DEFAULT value]; Nilai default adalah NULL.

Examples:

  DECLARE temp1 SMALLINT DEFAULT 0;

  DECLARE temp2 INTEGER DEFAULT 10;

  DECLARE temp3 DECIMAL(10,2) DEFAULT 100.10;

  DECLARE temp4 REAL DEFAULT 10.1;

  DECLARE temp5 DOUBLE DEFAULT 10000.1001;

  DECLARE temp6 BIGINT DEFAULT 10000;

  DECLARE temp7 CHAR(10) DEFAULT 'yes';

  DECLARE temp8 VARCHAR(10) DEFAULT 'hello';

  DECLARE temp9 DATE DEFAULT '1998-12-25';

  DECLARE temp10 TIME DEFAULT '1:50 PM';

  DECLARE temp11 TIMESTAMP DEFAULT '2001-01-05-12.00.00';

  DECLARE temp12 CLOB(2G);

  DECLARE temp13 BLOB(2G);

Cursors

• Deklarasi Cursor dan Penggunaannya :

            DECLARE <cursor name> CURSOR [WITH RETURN <return target>] FOR

              <SELECT statement>;

 OPEN  <cursor name>;

            FETCH <cursor name> INTO <variables>

            CLOSE <cursor name>;

• Nilai <return target> bias berupa Klien atau Pemanggil

ƒ        CLIENT:  result set will be returned to the client application

ƒ        CALLER:  result set will be returned to the client or stored procedure that made the call

SQLCODE dan SQLSTATE

-  Akses membutuhkan deklarasi eksplisit

Ÿ  DECLARE SQLSTATE CHAR(5);

Ÿ  DECLARE SQLCODE INT;

-  Can be declared ONLY at outermost scope and automatically set by DB2 after each operation

-  SQLCODE

-  = 0,  successful. 

-  > 0,  successful with warning

-  < 0,  unsuccessful

-  = 100, no data was found.

-  i.e. FETCH statement returned no data

Kondisi

-  A condition represents a given SQLSTATE

-  It can be raised by any SQL statement

-  You can provide names to a condition to make your code more readable

-  Built-in names for general conditions:

-  SQLWARNING, SQLEXCEPTION, NOT FOUND

-  These map to the SQLSTATEs mentioned in the previous slide

-  Specific conditions:

-  SQLSTATE '01004‘

-  To assign a user-defined name to a condition:

     DECLARE truncation CONDITION FOR SQLSTATE '01004'

Condition Handler

• A condition handler must specify

•         handled conditions

•         where to resume execution (CONTINUE, EXIT or UNDO)

•         action to perform to handle the condition

• Action can be any statement (including control structures)
• Upon SQLEXCEPTION condition,if no handler exists, the procedure  terminates and returns to the client with error

Perintah Flow Control

• CASE (selects an execution path (simple / searched))
• IF
• FOR (executes body for each row of table)
• WHILE
• ITERATE (forces next iteration. Similar to CONTINUE in C)
• LEAVE (leaves a block or loop. "Structured Goto")
• LOOP  (infinite loop)
• REPEAT
• GOTO
• RETURN
• CALL (procedure call)

SQL Dinamis

• Useful when the final form of SQL is known only at RUN TIME

• Example: if col1 and tabname are variables:

            'SELECT ' || col1 || ' FROM ' || tabname;

• Also recommended for DDL to avoid dependency problems and package invalidation, and to implement recursion.

• Keywords:

ƒ        EXECUTE IMMEDATE - ideal for single execution SQL

ƒ        PREPARE + EXECUTE - ideal for multiple execution SQL

Memanggil Stored Procedure

1. CLI

SQLCHAR *stmt = (SQLCHAR *)  "CALL MEDIAN_RESULT_SET( ? )" ;

    SQLDOUBLE   sal = 20000.0;      /* Bound to parameter marker in stmt */

    SQLINTEGER  salind  = 0;           /* Indicator variable for sal */

    sqlrc = SQLPrepare(hstmt, stmt, SQL_NTS);

    sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT,

                  SQL_C_DOUBLE, SQL_DOUBLE, 0, 0, &sal, 0, &salind);

    SQLExecute(hstmt);

    if (salind == SQL_NULL_DATA)

        printf("Median Salary = NULL\n");

    else

        printf("Median Salary = %.2f\n\n", sal );

    sqlrc = StmtResultPrint(hstmt);      /* Get first result set */

    sqlrc = SQLMoreResults(hstmt);    /* Check for another result set */

    if (sqlrc == SQL_SUCCESS)  {        /* There is another result set */

       sqlrc = StmtResultPrint(hstmt);

    }

2. Aplikasi VB.NET

Try

            ‘ Create a DB2Command to run the stored procedure

            Dim procName As String = “TRUNC_DEMO”

            Dim cmd As DB2Command = conn.CreateCommand()

            Dim parm As DB2Parameter

            cmd.CommandType = CommandType.StoredProcedure

            cmd.CommandText = procName

           

            ‘ Register the output parameters for the DB2Command

            parm           = cmd.Parameters.Add(“v_lastname”, DB2Type.VarChar)

            parm.Direction = ParameterDirection.Output

            parm           = cmd.Parameters.Add(“v_msg”, DB2Type.VarChar)

            parm.Direction = ParameterDirection.Output

            ‘ Call the stored procedure

            Dim reader As DB2DataReader = cmd.ExecuteReader

           

Catch myException As DB2Exception

            DB2ExceptionHandler(myException)

Catch

            UnhandledExceptionHandler()

End Try

3. Aplikasi Java

try

{

            // Connect to sample database

            String url = “jdbc:db2:sample”;

            con = DriverManager.getConnection(url);

            CallableStatement cs = con.prepareCall(“CALL trunc_demo(?, ?)”);

           

            // register the output parameters

            callStmt.registerOutParameter(1, Types.VARCHAR);

            callStmt.registerOutParameter(2, Types.VARCHAR);

           

            cs.execute();

            con.close();

}

catch (Exception e)

{

            /* exception handling logic goes here */

}