lect(3) PLSQL(2).pptx

Database Management Systems Procedural Language SQL Part(2) %TYPE: Declare variable with type of certain column in the certain table: Variable-name Table-name.Column-name %TYPE;  Example: decare variable X as type of STNO in the STUDENT table: X STUDENT.STNO%TYPE; ...

Database Management Systems Procedural Language SQL Part(2) %TYPE: Declare variable with type of certain column in the certain table: Variable-name Table-name.Column-name %TYPE;  Example: decare variable X as type of STNO in the STUDENT table: X STUDENT.STNO%TYPE; PL/SQL - SQL run SQL statements inside PL/SQL! − only use DML : like SELECT, INSERT, UPDATE, and DELETE, and − transaction control statements, like COMMIT, ROLLBACK, SAVEPOINT. Simple Example In this example, we’ll insert a new PRODUCT into a simple database. DECLARE P PRODUCT.PID%TYPE; BEGIN P := 20; INSERT INTO product VALUES (P,’tv’,32,199.99); P := P + 1; INSERT INTO product VALUES (P,’vcr’,16,799.98); COMMIT; END; Looping Structures PL/SQL has five looping structures, LOOP... EXIT Loop LOOP... EXIT WHEN Loop WHILE... LOOP Loop FOR Loop LOOP... EXIT Loop The general format of such a loop is: LOOP various_statements IF condition THEN EXIT; END IF; various_statements END LOOP; Example DECLARE Output: I NUMBER(6); BEGIN aI: 1 I := 1; bI: 2 LOOP aI: 2 DBMS_OUTPUT.PUT_LINE(’aI: ’ || I); bI: 3 I := I + 1; IF I > 5 THEN aI: 3 EXIT; bI: 4 END IF; DBMS_OUTPUT.PUT_LINE(’bI: ’ || I); aI: 4 END LOOP; bI: 5 END; aI: 5 LOOP... EXIT WHEN Loop The general format : LOOP various_statements EXIT WHEN condition; various_statements END LOOP; Example Rewrite the previous program using loop Exit when loop: DECLARE I NUMBER(6); BEGIN I := 1; LOOP DBMS_OUTPUT.PUT_LINE(’aI: ’ || I); I := I + 1; EXIT WHEN I > 5; DBMS_OUTPUT.PUT_LINE(’bI: ’ || I); END LOOP; END; WHILE... LOOP Loop The general format : WHILE condition LOOP various_statements END LOOP; Example  write PL/SQL program that print integers from 1 to 5 DECLARE I NUMBER := 1; BEGIN WHILE I