Sunday, 23 April 2017

How to Create Procedure in PL/SQL Database Management System But not in SQL

--Creation of procedure--
 
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
   dbms_output.put_line('Hello World!');
END;

--Execution of standalone procedure
EXECUTE greetings;

BEGIN
   greetings;
END;

--Delete procedure
BEGIN
   DROP PROCEDURE greetings;
END;

DROP PROCEDURE greetings;

--Procedure example
DECLARE
   a number;
   b number;
   c number;

PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
   IF x < y THEN
      z:= x;
   ELSE
      z:= y;
   END IF;
END;

BEGIN
   a:= 23;
   b:= 45;
   findMin(a, b, c);
   dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;

--Procedure in and out mode
DECLARE
   a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
  x := x * x;
END;
BEGIN
   a:= 23;
   squareNum(a);
   dbms_output.put_line(' Square of (23): ' || a);
END;

0 comments:

Post a Comment