DBMS-Unit-04-Part-01-PLSQL PDF

AID2PM02A Database Management System SY AIDS SEM-3 AY-2024-25 School of Computer Science & Engineering  Unit 4: Introduction To Programming in SQL(PL/SQL) Unit 4: Introduction To Programming in SQL(PL/SQL) PL/SQL Concepts: Basic PL/SQL block, PL/SQL Functions and Pr...

AID2PM02A Database Management System SY AIDS SEM-3 AY-2024-25 School of Computer Science & Engineering  Unit 4: Introduction To Programming in SQL(PL/SQL) Unit 4: Introduction To Programming in SQL(PL/SQL) PL/SQL Concepts: Basic PL/SQL block, PL/SQL Functions and Procedures, Different modes in procedure, Difference between functions and procedures, Cursors, Database Triggers. Exception Handling in PL/SQL, Application of DBMS DBM 2 S Extensions to SQL (PL/SQL) DBM 3 S What is PL/SQL DBM 4 S PL/SQL Execution Advantages PL/SQL is a completely portable, high- performance transaction-processing language. PL/SQL provides a built-in, interpreted and OS independent programming environment. It supports structured programming through functions and procedures. Direct call can also be made from external programming language calls to DBM database. 5 S  PL/SQL Functions and Procedures  SQL:1999 supports functions and procedures – Functions/procedures can be written in SQL itself, or in an external programming language (e.g., C, Java). – Some database systems support table-valued functions, which can return a relation as a result.  SQL:1999 also supports a rich set of imperative constructs, including – Loops, if-then-else, assignment  Many databases have proprietary procedural extensions to SQL that differ from SQL:1999.  Procedures and functions are stored in mysql.routines and mysql.parameters tables, which are part of the data dictionary. 6 DBM Stored Function DBM 7 S  PL/SQL Functions  Functions are declared using the following syntax: Create function (param_1, …, param_k) returns [not] deterministic allow optimization if same output for the same input (use RAND not deterministic ) Begin -- execution code end; For a FUNCTION, parameters are always regarded as IN parameters. For a Procedure , parameter as IN, OUT, or INOUT is valid. DBM 8 S  Deterministic and Non- deterministic Functions A deterministic function always returns the same result for the same input parameters whereas a non-deterministic function returns different results for the same input parameters. If you don’t use DETERMINISTIC or NOT DETERMINISTIC, MySQL uses the NOT DETERMINISTIC option by default. rand() is nondeterministic function. That means we do not know what it will return ahead of time. Some deterministic functions ISNULL, ISNUMERIC, DATEDIFF, POWER, CEILING, FLOOR, DATEADD, DAY, MONTH, YEAR, SQUARE, SQRT etc. Some non deterministic functions RAND(), RANK(), SYSDATE() DBM 9 S  PL/SQL Functions – Example 1  Define a function that, given the name of a department, returns the count of the number of instructors in that department. create function dept_count (dept_name varchar(20)) returns integer begin declare d_count integer; select count (* ) into d_count from instructor where instructor.dept_name = dept_name return d_count; end DBM 10 S  Example 1 (Cont)..  The function dept_count can be used to find the department names and budget of all departments with more than 12 instructors. select dept_name, budget from department where dept_count (dept_name ) > 12 DBM 11 S  Example 2  A function that returns the level of a customer based on credit limit. We use the IF statement to determine the credit limit. DBM 12 S  Example 2 (Cont..) Calling function: we can call the CustomerLevel() in a SELECT statement as follows: O Output: DBM 13 S Example 3 DBM 14 S Example 3 (cont..) DBM 15 S Stored Procedures DBM 16 S  Stored Procedures in MySQL  A stored procedure contains a sequence of SQL commands stored in the database catalog so that it can be invoked later by a program  Stored procedures are declared using the following syntax: Create Procedure (param_spec1, param_spec2,param_specn ) begin -- execution code end; where each param_spec is of the form: [in | out | inout]  Example 1 – No parameters  The GetAllProducts() stored procedure selects all products from the products table. DBM 18 S  Example 1 (Cont..)  Calling Procedure: CALL GetAllProducts();  Output: DBM 19 S  Example 2 ( with IN parameter) Suppose we want to keep track of the total salaries of employees working for each department We need to write a procedure to update the salaries in the deptsal table BM 2 8/13/2020 S 0  Example 2 (Cont..) 1. Define a procedure called updateSalary which takes as input a department number. 2. The body of the procedure is an SQL command to update the totalsalary column of the deptsal table. DBM 21 S  Example 2 (Cont..) Step 3: Call the procedure to update the totalsalary for each department DBM 22 S  Example 2 (Cont..) Step 4: Show the updated total salary in the deptsal table DBM 23 S  Example 3 (with OUT Parameter)  The following example shows a simple stored procedure that uses an OUT parameter.  Within the procedure MySQL MAX() function retrieves maximum salary from MAX_SALARY of jobs table. mysql> CREATE PROCEDURE my_proc_OUT (OUT highest_salary INT) -> BEGIN -> SELECT MAX(MAX_SALARY) INTO highest_salary FROM JOBS; -> END$$ Query OK, 0 rows affected (0.00 sec) DBM 24 S  (Cont..) Procedure Call: mysql> CALL my_proc_OUT(@M)$$ Query OK, 1 row affected (0.03 sec) To see the result type the following command mysql< SELECT @M$$ Output: + + | @M | + + | 40000 | + + 1 row in set (0.00 sec) DBM 25 S  Example 4 (with INOUT Parameter)  The following example shows a simple stored procedure that uses an INOUT parameter.  ‘count’ is the INOUT parameter, which can store and return values and ‘increment’ is the IN parameter, which accepts the values from user. DBM 26 S  Example 4 (Cont..) Function Call: DBM 27 S  Stored Procedures (Cont..)  Use show procedure status to display the list of stored procedures you have created  Use drop procedure to remove a stored procedure DBM 28 S  Language Constructs for Procedures & Functions  SQL supports constructs that gives it almost all the power of a general- purpose programming language. o Warning: most database systems implement their own variant of the standard syntax below.  Compound statement: begin … end, o May contain multiple SQL statements between begin and end. o Local variables can be declared within a compound statements DBM 29 S  Language Constructs  CASE Statement CASE case_expression WHEN when_expression_1 THEN commands WHEN when_expression_2 THEN commands... ELSE commands END CASE;  While and repeat statements: repeat while boolean expression sequence of statements ; do sequence of statements ; end while until boolean expression end repeat DBM 3 S 0  Language Constructs (Cont.)  Loop, Leave and Iterate statements… – Permits iteration over all results of a query. loop_label: LOOP IF x > 10 THEN LEAVE loop_label; END IF; SET x=x+ 1; IF (x mod 2) THEN ITERATE loop_label; ELSE SET 3 1  Difference between Function and Procedure Based on Function Procedure Basic It calculates the result based on given inputs. It performs certain tasks in order. SQL Query It can be called in a query. It cannot be called in a query. SELECT There can be a function call in a SELECT There is no procedure call in a SELECT statement. statement. Return It returns the resulting value or control to the On the other hand, it returns control but does not return a calling function or code. value to the code or calling function. DML statements DML statements such as (Insert, In a procedure, DML statements can be used. Delete, and Update) cannot be used in a function. Call We can call a function via the procedure. Whereas it is not possible to call a procedure via function. Compilation Functions are compiled whenever they are called. Procedures need to be compiled once, and if necessary, we can call them repeatedly without compiling them every time.  References 1. Silberschatz−Korth−Sudarshan's Database System Concepts, Seventh Edition. http://www.mysqltutorial.org/, http://www.w3schools.com, Difference between Function and Procedure - javatpoint DBMS 32