Database Management Systems: PL SQL Part 1

Procedural Language SQL Overview

• PL/SQL combines procedural language features with SQL, allowing use of IF statements, loops, and variables alongside declarative SQL.

Variable Types

• SQL supports various column types: BOOLEAN, DATE, NUMBER, CHAR, and VARCHAR2.
• Variables can be declared using the syntax: variable-name data type, e.g., PRICE NUMBER(6,2).
• Columns can be referenced using tablename.columnname%TYPE, e.g., PRODUCT.PRICE%TYPE.
• Rows as a single type can be declared with tablename%ROWTYPE, e.g., PRODUCT%ROWTYPE.

PL/SQL Program Blocks

• PL/SQL programs consist of structured blocks:
  • DECLARE: for variable declarations.
  • BEGIN: contains procedural code.
  • EXCEPTION: for error handling.
  • END: indicates the end of the PL/SQL block.

Operators in PL/SQL

• Supports various operators:
  • Exponentiation: **
  • Not Equals: !=
  • Multiplication: *
  • Division: /
  • Addition: +
  • Subtraction: -
  • Negation: -
  • Comparison operators: <, >, <=, >=
  • Logical OR: OR
  • Assignment: :=
  • String Concatenation: ||

Outputting Text

• To display output in PL/SQL, use SET SERVEROUTPUT ON;
• Example code to print "Hello World":

  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
  END;
  

Type Conversion Functions

• TO_DATE: Converts string to date.
• TO_NUMBER: Converts character string to number.
• TO_CHAR: Converts numbers or dates to character strings.

Character String Functions

• RTRIM(STR): Removes trailing spaces.
• LENGTH(STR): Returns length of the string.
• UPPER(STR): Converts string to uppercase.
• LOWER(STR): Converts string to lowercase.
• INSTR(STR1, STR2): Searches for STR2 within STR1.
• SUBSTR(STR, START, END): Returns a substring starting at START.

IF Statements

• Structures for IF statements:
  • IF - THEN
  • IF - ELSE
  • IF ELSIF

Example of IF - THEN Structure

• Syntax:

  IF condition THEN
    program_statements
  END IF;
  

• Example of comparing two integers:

  DECLARE
    A NUMBER(6);
    B NUMBER(6);
  BEGIN
    A := 23;
    B := 89;
    IF A < B THEN
      DBMS_OUTPUT.PUT_LINE(A || ' is less than ' || B);
    END IF;
  END;
  

Example of IF - ELSE Structure

• Syntax:

  IF condition THEN
    code_if_true
  ELSE
    code_if_false
  END IF;
  

• Example comparing two values:

  DECLARE
    A NUMBER(6);
    B NUMBER(6);
  BEGIN
    A := 23;
    B := 8;
    IF A < B THEN
      DBMS_OUTPUT.PUT_LINE(A || ' is less than ' || B);
    ELSE
      DBMS_OUTPUT.PUT_LINE(A || ' is greater than ' || B);
    END IF;
  END;
  

IF Statement Nesting

• Allows multiple conditions and comparisons within nested structures:
  • Example compares three integers to find the maximum:

  DECLARE
    A NUMBER;
    B NUMBER;
    C NUMBER;
    ABCMAX NUMBER;
  BEGIN
    A := 23;
    B := 89;
    C := 7;
  
    IF A >= B THEN
      IF A >= C THEN
        ABCMAX := A;
      ELSE
        ABCMAX := C;
      END IF;
    ELSE
      IF B >= C THEN
        ABCMAX := B;
      ELSE
        ABCMAX := C;
      END IF;
    END IF;
  
    DBMS_OUTPUT.PUT_LINE('Max of: ' || A || ', ' || B || ', and ' || C || ' is ' || ABCMAX);
  END;
  

IF ELSIF Structure

• Used for multiple conditions:
  • Example to assign letter grades based on numeric grades:

  DECLARE
    NGRADE NUMBER;
    LGRADE CHAR(2);
  BEGIN
    NGRADE := 82.5;
  
    IF NGRADE >= 80 THEN
      LGRADE := 'A';
    ELSIF NGRADE >= 70 THEN
      LGRADE := 'B';
    ELSIF NGRADE >= 60 THEN
      LGRADE := 'C';
    ELSIF NGRADE >= 50 THEN
      LGRADE := 'D';
    ELSE
      LGRADE := 'F';
    END IF;
  
    DBMS_OUTPUT.PUT_LINE('Grade ' || NGRADE || ' is ' || LGRADE);
  END;
  

Explore the fundamentals of Procedural Language SQL in this quiz. Learn about IF statements, loops, variables, and the integration of procedural constructs with declarative SQL. Perfect for students looking to deepen their understanding of database management.