Scribd Logo
Upload

Welcome to Scribd!

  • 15 views

    DBMS 1-5

    Uploaded by

    Raj
    The document discusses procedures and functions in databases. Procedures are used to perform operations like updates and accept parameters. Functions return a single value. Examples are given of creating a procedure to update an employee's salary and a function to calculate years an employee has been at a company.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    DBMS 1-5

    Uploaded by

    Raj
    15 views6 pages
    The document discusses procedures and functions in databases. Procedures are used to perform operations like updates and accept parameters. Functions return a single value. Examples are given of creating a procedure to update an employee's salary and a function to calculate years an employee has been at a company.

    Original Description:

    Practical

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses procedures and functions in databases. Procedures are used to perform operations like updates and accept parameters. Functions return a single value. Examples are given of creating a procedure to update an employee's salary and a function to calculate years an employee has been at a company.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    15 views6 pages

    DBMS 1-5

    Uploaded by

    Raj
    The document discusses procedures and functions in databases. Procedures are used to perform operations like updates and accept parameters. Functions return a single value. Examples are given of creating a procedure to update an employee's salary and a function to calculate years an employee has been at a company.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 6

    EXPERIMENT NO:-09

    Aim: Implement procedure and functions

    Theory:

    Procedure:

    Stored Procedures are created to perform one or more DML operations on Database. It is nothing
    but the group of SQL statements that accepts some input in the form of parameters and performs
    some task and may or may not returns a value.

    Syntax : Creating a Procedure

    CREATE or REPLACE PROCEDURE name(parameters)


    IS
    variables;
    BEGIN
    //statements;
    END;
    The most important part is parameters. Parameters are used to pass values to the Procedure. There
    are 3 different types of parameters, they are as follows:

    1. IN:
    This is the Default Parameter for the procedure. It always receives the values from calling
    program.
    2. OUT:
    This parameter always sends the values to the calling program.
    3. IN OUT:
    This parameter performs both the operations. It receives value from as well as sends the values
    to the calling program.
    Function:
    The CREATE FUNCTION statement is used for creating a stored function and user-defined
    functions. A stored function is a set of SQL statements that perform some operation and return a
    single value.
    Just like Mysql in-built function, it can be called from within a Mysql statement.
    By default, the stored function is associated with the default database.
    The CREATE FUNCTION statement require CREATE ROUTINE database privilege.

    Syntax:
    The syntax for CREATE FUNCTION statement in Mysql is:

    CREATE FUNCTION function_name(func_parameter1, func_parameter2, ..)


    RETURN datatype [characteristics]
    func_body
    Parameters used:
    1. function_name:
    It is the name by which stored function is called. The name should not be same as
    native(built_in) function. In order to associate routine explicitly with a specific database
    function name should be given as database_name.func_name.
    2. func_parameter:
    It is the argument whose value is used by the function inside its body. You can’t specify to
    these parameters IN, OUT, INOUT. The parameter declaration inside parenthesis is provided
    as func_parameter type. Here, type represents a valid Mysql datatype.
    3. datatype:
    It is datatype of value returned by function.
    4. characteristics:
    The CREATE FUNCTION statement is accepted only if at least one of the characteristics {
    DETERMINISTIC, NO SQL, or READS SQL DATA } is specified in its declaration.
    func_body is the set of Mysql statements that perform operation. It’s structure is as follows:
    BEGIN

    Mysql Statements

    RETURN expression;
    END
    The function body must contain one RETURN statement.
    Application of Procedure and Function:

    Procedure Example:

    Imagine a table named with emp_table stored in Database. We are Writing a Procedure to update a
    Salary of Employee with 1000.

    CREATE or REPLACE PROCEDURE INC_SAL(eno IN NUMBER, up_sal OUT NUMBER)


    IS
    BEGIN
    UPDATE emp_table SET salary = salary+1000 WHERE emp_no = eno;
    COMMIT;
    SELECT sal INTO up_sal FROM emp_table WHERE emp_no = eno;
    END;

    • Declare a Variable to Store the value coming out from Procedure :

    VARIABLE v NUMBER;

    • Execution of the Procedure:

    EXECUTE INC_SAL(1002, :v);

    • To check the updated salary use SELECT statement:

    SELECT * FROM emp_table WHERE emp_no = 1002;

    • or Use print statement :

    PRINT :
    Output: 21
    Function Example:
    Consider following Employee Table-

    We have to find the number of years the employee(hospital) has been in the company-

    DELIMITER //

    CREATE FUNCTION no_of_years(join_date DATE) RETURNS INT DETERMINISTIC


    BEGIN
    DECLARE current_date DATE;
    SELECT CURRENT_DATE() INTO current_date;
    RETURN YEAR(current_date) - YEAR(join_date);

    END //
    DELIMITER ;

    Calling of above function:


    SELECT emp_id, fname, surname, join_date, YEAR(CURRENT_DATE()) - YEAR(join_date) AS
    years_in_company FROM employees;
    Output:

    You might also like