Scribd Logo
Upload

Welcome to Scribd!

  • 45 views

    Aplicationdatabase

    Uploaded by

    api-26355935
    This document discusses various concepts for developing database applications, including embedded SQL, dynamic SQL, JDBC, SQLJ, and stored procedures. Stored procedures allow application logic to execute directly on the database server to minimize data transfer and utilize server resources. They provide benefits such as encapsulation, logic reuse, and reduced network traffic.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd

    Aplicationdatabase

    Uploaded by

    api-26355935
    45 views25 pages
    This document discusses various concepts for developing database applications, including embedded SQL, dynamic SQL, JDBC, SQLJ, and stored procedures. Stored procedures allow application logic to execute directly on the database server to minimize data transfer and utilize server resources. They provide benefits such as encapsulation, logic reuse, and reduced network traffic.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PPT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses various concepts for developing database applications, including embedded SQL, dynamic SQL, JDBC, SQLJ, and stored procedures. Stored procedures allow application logic to execute directly on the database server to minimize data transfer and utilize server resources. They provide benefits such as encapsulation, logic reuse, and reduced network traffic.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    45 views25 pages

    Aplicationdatabase

    Uploaded by

    api-26355935
    This document discusses various concepts for developing database applications, including embedded SQL, dynamic SQL, JDBC, SQLJ, and stored procedures. Stored procedures allow application logic to execute directly on the database server to minimize data transfer and utilize server resources. They provide benefits such as encapsulation, logic reuse, and reduced network traffic.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    of 25

    Database Application

    Development
    Overview
    Concepts covered in this lecture:
     SQL in application code
     Embedded SQL
     Cursors
     Dynamic SQL
     JDBC
     SQLJ
     Stored procedures
    Stored procedure
     It is often important to execute some parts
    of the application directly in the process
    space of Database system.
     Why?
    3. To minimize data transferring between
    database server and its client issuing SQL
    statements.
    4. To utilize the full power of the Database
    server.
    Sudarshan 3
     When SQL statements are issued from a
    remote application.

     What’s happen exactly ?

    Sudarshan 4
    What’s happen exactly ?
     When SQL statements are issued from a
    remote application.
    The records in the result of query need to be transferred from
    Database system back to the application.
    – For that we can make use of cursors.
     DBMS has resources:
    1. Locks
    2. Memory tied up-
    while application is processing the records retrieved
    through the cursor.
    Is there any other way to avoid this?
    Sudarshan 5
    Stored Procedures
     What is a stored procedure:
    – Program executed through a single
    SQL statement
    – Executed in the process space of the
    server.
    – Or-

    – Stored Procedure is a function in


    a shared library accessible to the
    database server.
    Advantages:

    1. Can encapsulate application logic while staying


    “close” to the data.
    2. Reuse of application logic by different users
    3. Application logic can be performed directly at the
    server.
    4. Eliminating duplication of efforts in writing SQL
    queries.
    5. Avoid tuple-at-a-time return of records through
    cursors.
    6. Reduced network traffic.

    Sudarshan 7
    Stored Procedure Language

     Things to remember
    2. can also write stored procedures using languages
    such as C or Java
    3. The more SQL statements that are grouped
    together for execution, the larger the savings in
    network traffic.

    Note:-they do not have to be procedures in the PL


    sense ;they can be functions.

    Sudarshan 8
    Normal Database

    Sudarshan 9
    Applications using
    stored procedures

    Sudarshan 10
    Creating simple stored Procedure
    Eg: CREATE PROCEDURE showreserved
    SELECT S.sid,sS.sname,count(*)
    FROM sailors S ,Reserve R
    WHERE S.Sid=R.sid
    GROUP BY S.sid
    Showreserved is the procedure name,which is
    containing an SQL statement that is
    precompiled and stored at the server .
     Allows parameters local variables), loops.
    Sudarshan 11
    parameters
    Stored procedures can have parameters:
     Three different modes:
     IN: parameters are arguments to the stored
    procedures.
     OUT: parameters are arguments from the
    stored procedures.
     INOUT: both
    CREATE PROCEDURE
    IncreaseRating
    (IN sailor_sid INTEGER, IN increase INTEGER)
    UPDATE Sailors SET rating = rating + increase
    WHERE sid = sailor_sid
    Sudarshan 12
    Stored Procedures: Examples

    Stored procedure do not have to be written in


    SQL:

    CREATE PROCEDURE TopSailors(IN num NTEGER)


    LANGUAGE JAVA
    EXTERNAL NAME file:///c:/storedProcs/rank.jar

    That is dynamically executed by the database


    server whenever it is called by the client.
    NESTED PROCEDURE
     Invoking Procedures
    Can invoke Stored procedure stored at the
    location of the database by using the SQL CALL
    statement

     Nested SQL Procedures:


    To call a target SQL procedure from within a
    caller SQL procedure, simply include a CALL
    statement with the appropriate number and types
    of parameters in your caller.

    Sudarshan 14
    EXAMPLE FOR NESTED PROCEDURE
    CREATE PROCEDURE UPDATE_SALARY
    (IN employee_number VARCHAR(6), IN rating INTEGER)
    LANGUAGE SQL
    BEGIN
    SET counter = 10;
    WHILE (counter > 0) DO
    IF (rating = 1)
    THEN
    UPDATE employee
    SET salary = salary * 1.10, bonus = 1000
    WHERE empno = employee_number;
    ELSEIF (rating = 2)
    THEN
    UPDATE employee
    SET salary = salary * 1.05, bonus = 500
    WHERE empno = employee_number;
    ELSE
    UPDATE employee
    SET salary = salary * 1.03, bonus = 0
    WHERE empno = employee_number;
    END IF;
    SET counter = counter – 1;
    END WHILE;
    END Sudarshan 15
    Example
     LANGUAGE value of SQL and the BEGIN...END
    block, which forms the procedure body, are
    particular to an SQL procedure
     1)The stored procedure name is
    UPDATE_SALARY_1.
     2)The two parameters have data types of
    VARCHAR(6) and INTEGER. Both are input
    parameters.
     3)LANGUAGE SQL indicates that this is an SQL
    procedure, so a procedure body follows the other
    parameters.
     4)The procedure body consists of a single SQL
    UPDATE statement, which updates rows in the
    employee table. Sudarshan 16
    Some Valid SQL Procedure Body
    Statements

    • CASE statement
    • FOR statement
    • GOTO statement
    • IF statement
    • ITERATE statement
    • RETURN statement
    • WHILE statement
    Sudarshan 17
    Calling Stored Procedures
    EXEC SQL BEGIN DECLARE SECTION
    Int sid;
    Int rating;
    EXEC SQL END DECLARE SECTION

    // now increase the rating of this sailor


    EXEC CALL IncreaseRating(:sid,:rating);
    Calling Stored Procedures
    (Cont'd.)
    JDBC: SQLJ:
    CallableStatement #sql iterator
    cstmt= ShowSailors(…);
    con.prepareCall(“{c ShowSailors
    all ShowSailors}); showsailors;
    ResultSet rs = #sql
    cstmt.executeQuer showsailors={CAL
    y(); L ShowSailors};
    while (rs.next()) { while
    … (showsailors.next(
    )) {
    }
    SQL Persistent Stored
    Modules (SQL/PSM).
    Most DBMSs allow users to write stored
    procedures in a simple, general-purpose
    language (close to SQL)  SQL/PSM
    standard is a representative
    In PSM ,we define modules,which are
    collections of
    1. Stored procedure
    2. Temporary relations
    3. And other declarations
    SQL/PSM
    Most DBMSs allow users to write stored procedures in
    a simple, general-purpose language (close to SQL)
     SQL/PSM standard is a representative
    Declare a stored procedure:
    CREATE PROCEDURE name(p1, p2, …, pn)
    local variable declarations
    procedure code;
    Declare a function:
    CREATE FUNCTION name (p1, …, pn) RETURNS
    sqlDataType
    local variable declarations
    function code;
    Main SQL/PSM Constructs
    CREATE FUNCTION rate Sailor
    (IN sailorId INTEGER)
    RETURNS INTEGER
    DECLARE rating INTEGER
    DECLARE numRes INTEGER
    SET numRes = (SELECT COUNT(*)
    FROM Reserves R
    WHERE R.sid = sailorId)
    IF (numRes > 10) THEN rating =1;
    ELSE rating = 0;
    END IF;
    RETURN rating;
    Main SQL/PSM Constructs
     Local variables (DECLARE)
     RETURN values for FUNCTION
     Assign variables with SET
     Branches and loops:
    – IF (condition) THEN statements;
    ELSEIF (condition) statements;
    … ELSE statements; END IF;
    – LOOP statements; END LOOP
     Queries can be parts of expressions
     Can use cursors naturally without “EXEC SQL”
    Summary
     Embedded SQL allows execution of
    parameterized static queries within a host
    language
     Dynamic SQL allows execution of completely ad-
    hoc queries within a host language
     Cursor mechanism allows retrieval of one record
    at a time and bridges impedance mismatch
    between host language and SQL
     APIs such as JDBC introduce a layer of
    abstraction between application and DBMS
    Summary (Cont'd.)
     SQLJ: Static model, queries checked a
    compile-time.
     Stored procedures execute application logic
    directly at the server
     SQL/PSM standard for writing stored
    procedures

    You might also like