Database Management Systems: SQL Part 4

Procedural Language SQL

• PL/SQL allows for the creation of procedures to perform specific tasks, such as printing output to the screen.

Creating a Simple Procedure

• A basic example is the procedure to print "hello World":
  • Code snippet:

    CREATE OR REPLACE PROCEDURE PrintH IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('hello World');
    END;
    

Parameters in Procedures

• Parameters are optional and can be declared similarly to variables.
  • Example: (X INT) defines an integer parameter.

Displaying Parameter Value

• To display the value of an integer parameter, a procedure can be defined as follows:
  • Code snippet:

    CREATE OR REPLACE PROCEDURE DISPN(N INT) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('N is ' || N);
    END;
    

Types of Parameters

• IN, OUT, IN OUT are the types of parameters:
  • IN: Default that passes data to the procedure.
  • OUT: Used to retrieve data from the procedure.
  • IN OUT: Reads and then modifies the parameter.

Example of OUT Parameter

• A procedure that returns the sum of two integers using an OUT parameter:
  • Code snippet:

    CREATE OR REPLACE PROCEDURE Sum_ab(A INT, B INT, C OUT INT) IS
    BEGIN
        C := A + B;
    END;
    

• To call this procedure within a PL/SQL program:
  • Code snippet:

    DECLARE
        X INT := &X;
        Y INT := &Y;
        Z INT;
    BEGIN
        Sum_ab(X, Y, Z);
        DBMS_OUTPUT.PUT_LINE('the sum of ' || X || ' and ' || Y || ' is ' || Z);
    END;
    

IN OUT Parameter Example

• An example of a procedure that doubles a number:
  • Code snippet:

    CREATE OR REPLACE PROCEDURE DOUBLEN(N IN OUT INT) IS
    BEGIN
        N := N * 2;
    END;
    

Dropping Procedures

• Use the following format to drop a procedure:
  • Code snippet: DROP PROCEDURE procedure_name;

This quiz covers the basics of PL/SQL, focusing on how to create procedures and use parameters in your code. You'll learn how to write simple PL/SQL programs that display outputs, such as printing 'hello world' on the screen. Test your understanding of procedural language concepts in the context of database management systems.