Scribd
Upload
5 views7 pages

PLSQL

The document explains the differences between PL/SQL functions and procedures, highlighting their purposes, return types, usage in queries, and call syntax. It provides examples of a function that calculates the square of a number and a procedure that uses a cursor to fetch course names and fees. Additionally, it outlines the flexibility, parameter types, and error handling capabilities of each construct.

Uploaded by

snehal.22420227
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views7 pages

PLSQL

The document explains the differences between PL/SQL functions and procedures, highlighting their purposes, return types, usage in queries, and call syntax. It provides examples of a function that calculates the square of a number and a procedure that uses a cursor to fetch course names and fees. Additionally, it outlines the flexibility, parameter types, and error handling capabilities of each construct.

Uploaded by

snehal.22420227
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

PL/SQL

Return -function
No return -procedure
 FUNCTIONS
drop function if exists square;
DELIMITER //

CREATE FUNCTION square(x INT)


RETURNS INT
DETERMINISTIC
BEGIN
RETURN x * x;
END//

DELIMITER ;

 CURSOR
DROP PROCEDURE IF EXISTS t1;
DELIMITER //

CREATE PROCEDURE t1()


BEGIN
DECLARE nam VARCHAR(20);
DECLARE p INT;
DECLARE r TEXT DEFAULT '';
DECLARE done INT DEFAULT 0;

DECLARE cur CURSOR FOR SELECT course_name, fee FROM courses;


DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;

r1: LOOP
FETCH cur INTO nam, p;

IF done THEN
LEAVE r1;
END IF;
if p>9500 and p< 12000 then
SET r = CONCAT(r, 'name: ', nam, ', age: ', p, '\n');
end if;
END LOOP r1;

CLOSE cur;

SELECT r AS result;
END;
//
DELIMITER ;
Feature Stored Procedure Function
Purpose Performs a specific task Computes and returns a
(e.g., insert, update, single value
complex logic)
Return May or may not return a Must return a value using
Type value (via OUT or INOUT RETURN
parameter)
Usage in Cannot be used inside SQL Can be used inside SELECT,
Queries statements like SELECT WHERE, HAVING, etc.
Call Syntax Called using CALL Called using SELECT
procedure_name(); function_name();
DML Can perform DML Should not perform DML
Operations operations like INSERT, operations; ideally used for
UPDATE, DELETE read-only computations
Parameter Supports IN, OUT, INOUT Supports only IN parameters
Types parameters
Flexibility More flexible – supports More restrictive – generally
complex operations, loops, used for calculations or
cursors transformations
Error Allows custom error Limited error control
Handling handling and control flow
Return Returns values via OUT Must use RETURN to return
Usage parameter or SELECT a value
Example CALL get_data(); SELECT square(5); →
Returns 25

You might also like