lect(2) PLSQL(1).pptx

Database Management Systems Procedural Language SQL Part(1) Procedural Language SQL The PL stands for Procedural Language −IF statements, −loops, −variables, − and other procedural things −with declarative SQL statements. Variables Variables Types: − SQL column ty...

Database Management Systems Procedural Language SQL Part(1) Procedural Language SQL The PL stands for Procedural Language −IF statements, −loops, −variables, − and other procedural things −with declarative SQL statements. Variables Variables Types: − SQL column types  BOOLEAN, DATE, NUMBER, CHAR, and VARCHAR2.  Variable-name data type;  For example : PRICE NUMBER(6,2); − Type of a particular column:  (tablename.columname) followed by %TYPE.  For example: PRODUCT.PRICE%TYPE. −Row as a single type  tablename%ROWTYPE  For example: PRODUCT%ROWTYPE. PL/SQL Program Blocks PL/SQL programs are structured in blocks: DECLARE variable_declarations BEGIN procedural_code EXCEPTION error_handling END; Operators  PL/SQL supports several operators to do various things: Exponentiation ** Not Equals Comparison =! Multiplication * Greater Than Comparison > Division / Less Than Comparison < Addition + Greater Than or Equal >= Subtraction - Comparison Negation - Less Than or Equal < The obvious OR operation OR Assignment := String Concatenation || PL/SQL hello world example If your programs attempts to write some output to the screen: SET SERVEROUTPUT ON; Write PL/SQL program that print hello world! On the screen. BEGIN DBMS_OUTPUT.PUT_LINE(’Hello World’); END;/ The hello program and its output on SQL*PLUS window Type Conversion Functions.TO_DATE Converts a string to a date TO_DATE.Converts a character string to a number TO_NUMBER.Converts numbers or dates to character strings TO_CHAR  Character String Functions.Removes blank spaces from right side of string RTRIM(STR).Returns the length of the string LENGTH(STR).Converts the string to upper case UPPER(STR).Converts the string to lower case LOWER(STR).Looks for STR2 in STR1 INSTR(STR1,STR2).Returns a substring that starts at START SUBSTR(STR,START,END) PL/SQL IF Statement The general format of an IF statement is: − IF - THEN Structure − IF - ELSE Structure − IF Statement nesting − IF ELSIF Structure IF - THEN Structure The general format of an IF statement is: IF condition THEN program_statements END IF; Example: Write Pl/SQL program that declare two integer variables and compare them if the first is less than the second print: − The value of the first is less than the second value Sol.: 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; Which produces the expected output of: 23 is less than 89 IF - ELSE Structure The full structure of an IF statement is thus: IF condition THEN if_condition_is_true_code ELSE if_condition_is_false_code END IF; Example: Write Pl/SQL program that declare two integer variables and compare them if the first is less than the second print: − The value of the first is less than the second value − Otherwise : The value of the first is greater than the second value Sol.: 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 Example: Write PL/SQL program that declare three integers , compare them and print the maximum. Sol.: DECLARE ELSE ABCMAX := C; A NUMBER; END IF; B NUMBER; ELSE C NUMBER; IF B >= C THEN ABCMAX NUMBER; ABCMAX := B; BEGIN ELSE ABCMAX := C; A := 23; END IF; B := 89; END IF; C := 7; DBMS_OUTPUT.PUT_LINE(’Ma IF A >= B THEN x of: ’ || A || ’, ’ || B || IF A >= C THEN ’, and ’ || C || ’ is ’ || ABCMAX); ABCMAX := A; END; IF ELSIF Structure Example: Write PL/SQL program to calculate the student’s letter grade given a number grade. Sol.: DECLARE ELSE NGRADE NUMBER; LGRADE := ’F’; LGRADE CHAR(2); END IF; BEGIN DBMS_OUTPUT.PUT_LINE(’Grad NGRADE := 82.5; e ’ || NGRADE || ’ is ’ || LGRADE); IF NGRADE >= 80 THEN END; LGRADE := ’A’; ELSIF NGRADE >= 70 THEN LGRADE := ’B’; ELSIF NGRADE >= 60 THEN LGRADE := ’C’; ELSIF NGRADE >=50 THEN LGRADE := ’D’;