Functions in PLSQL

In PL/SQL (Procedural Language/Structured Query Language), functions are subprograms that can be stored and executed on the database server. They allow you to perform specific tasks, typically returning a single value. Functions are similar to procedures, but they differ primarily in that a function returns a value while a procedure does not.

Key Features of Functions in PL/SQL:

Return Type: 
Functions must have a return type. This can be any valid PL/SQL data type.

Syntax: The general syntax for creating a function is:

CREATE [OR REPLACE] FUNCTION function_name 
   [ (parameter_list) ]
   RETURN return_datatype
IS | AS
   [declaration_section]
BEGIN
   executable_section
   RETURN return_value;
EXCEPTION
   exception_section
END [function_name];

Parameter Modes:
IN: The parameter can be read, but not modified.
OUT: The parameter can be written to, but not read.
IN OUT: The parameter can be read and written to.

Example of a Simple PL/SQL Function
CREATE OR REPLACE FUNCTION hello_user 
(user_name IN VARCHAR2) 
RETURN VARCHAR2
IS 
BEGIN 
    RETURN 'Hello ' || user_name; 
END hello_user; 

/ 

BEGIN 
dbms_output.put_line(hello_user('Peter')); 
END; 
/


Example of a Simple PL/SQL Function

Here is an example of a simple PL/SQL function that calculates the square of a number:
CREATE OR REPLACE FUNCTION calculate_square (number IN NUMBER)
RETURN NUMBER
IS
   result NUMBER;
BEGIN
   result := number * number;
   RETURN result;
END;
/
Usage of the Function

Once the function is created, you can use it in PL/SQL blocks, SQL statements, or even in other functions or procedures. For example:
DECLARE
   num NUMBER := 5;
   square NUMBER;
BEGIN
   square := calculate_square(num);
   DBMS_OUTPUT.PUT_LINE('The square of ' || num || ' is ' || square);
END;
/
Key Points:
  • Scalability: Functions help modularize your code, making it more manageable and reusable.
  • Performance: They can improve performance by reducing the amount of code sent to the server.
  • Maintainability: Functions make your code easier to maintain by isolating functionality.
Difference Between Functions and Procedures
  • Return Type: Functions return a single value, while procedures do not.
  • Usage: Functions can be used in SQL statements, whereas procedures cannot.
  • Return Clause: Functions must have a RETURN clause, whereas procedures do not.

Understanding and using functions in PL/SQL effectively can help in creating robust and efficient database applications.

Example: factorial of a number

CREATE OR REPLACE FUNCTION factorial (n IN NUMBER)
RETURN NUMBER
IS
   result NUMBER := 1;  -- Initialize result to 1
BEGIN
   IF n < 0 THEN
      RETURN NULL;  -- Return null for negative numbers (not defined)
   ELSIF n = 0 OR n = 1 THEN
      RETURN 1;  -- Return 1 for factorial of 0 or 1
   ELSE
      FOR i IN 1..n LOOP
         result := result * i;  -- Calculate factorial
      END LOOP;
      RETURN result;  -- Return the factorial value
   END IF;
END;
/
DECLARE
   num NUMBER := 5;
   fact NUMBER;
BEGIN
   fact := factorial(num);
   DBMS_OUTPUT.PUT_LINE('The factorial of ' || num || ' is ' || fact);
END;
/


You can use PL/SQL functions inside SQL queries to enhance the functionality of your queries. Functions can be called in the SELECT clause, the WHERE clause, or any place where an expression is allowed. Here’s an example to illustrate this.

Example: Using a Function Inside a SQL Query

Let's create a function that calculates the square of a number, and then use this function in a SQL query.
CREATE OR REPLACE FUNCTION sqr(num IN NUMBER)
  RETURN NUMBER
IS
   result NUMBER:=1;
BEGIN
   result:=num*num;
   RETURN result;
END;
/

CREATE TABLE numbers (
    num NUMBER
);

INSERT INTO numbers (num) VALUES (1);
INSERT INTO numbers (num) VALUES (2);
INSERT INTO numbers (num) VALUES (3);
INSERT INTO numbers (num) VALUES (4);
INSERT INTO numbers (num) VALUES (5);
COMMIT;
SELECT num, sqr(num) AS square
FROM numbers;
/

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more