Using Oracle PL/SQL

1. Using Oracle PL/SQL • PL/SQL stands for Procedural Language/SQL. • PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. • The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks

2. block structure DECLARE /* Declarative section: variables, types, and local subprograms. */ BEGIN /* Executable section: procedural and SQL statements go here. */ /* This is the only section of the block that is required. */ EXCEPTION /* Exception handling section: error handling statements go here. */ END;

3. Execute a PL/SQL • To execute a PL/SQL program must follow the program text itself by • A line with a single dot ("."), and then • A line with run; • with Oracle SQL programs, we can invoke a PL/SQL program by typing it in sqlplus

4. Variables and Types • Type • One of the types used by SQL for database columns • A generic type used in PL/SQL such as NUMBER • Declared to be the same as the type of some database column • E.G. DECLARE price NUMBER; myBeer VARCHAR(20);

5. Variables and Types • %TYPE operator DECLARE myBeer Beers.name%TYPE; • %ROWTYPE operator DECLARE beerTuple Beers%ROWTYPE;

6. Variables and Types • ":=" operator • e.g DECLARE a NUMBER := 3; BEGIN a := a + 1; END; . run;

7. T1 e f 3 1 4 2 Simple Programs in PL/SQL • Plain SQL CREATE TABLE T1( e INTEGER, f INTEGER ); DELETE FROM T1; INSERT INTO T1 VALUES(1, 3); INSERT INTO T1 VALUES(2, 4);

8. Simple Programs in PL/SQL T1 • PL/SQL program DECLARE a NUMBER; b NUMBER; BEGIN SELECT e,f INTO :a,:b FROM T1 WHERE e>1; INSERT INTO T1 VALUES(:b,:a); END; . run; e f 1 3 2 4 4 2

9. Control Flow in PL/SQL • IF statement IF <condition_1> THEN ... ELSIF <condition_2> THEN ... ... ... ELSIF <condition_n> THEN ... ELSE ... END IF;

10. Control Flow in PL/SQL • E.g. DECLARE a NUMBER; b NUMBER; BEGIN SELECT e,f INTO :a, :b FROM T1 WHERE e>1; IF b=1 THEN INSERT INTO T1 VALUES(:b,:a); ELSE INSERT INTO T1 VALUES(:b+10,:a+10); END IF; END; . run;

11. Control Flow in PL/SQL • Loops : LOOP <loop_body> /* A list of statements. */ END LOOP; • EXIT EXIT WHEN <condition>;

12. E.G. DECLARE i NUMBER := 1; BEGIN LOOP INSERT INTO T1 VALUES(:i,:i); i := i+1; EXIT WHEN i>100; END LOOP; END; . run;

13. Control Flow in PL/SQL • WHILE loop WHILE <condition> LOOP <loop_body> END LOOP; • FOR loop FOR <var> IN <start>..<finish> LOOP <loop_body> END LOOP;

14. DECLARE x NUMBER := 100; BEGIN FOR i IN 1..10 LOOP IF MOD(i,2) = 0 THEN -- i is even INSERT INTO temp VALUES (i,: x, 'i is even'); ELSE INSERT INTO temp VALUES (i, :x, 'i is odd'); END IF; x := x + 100; END LOOP; COMMIT; END; . run

15. DECLARE acct_balance NUMBER(11,2); acct CONSTANT NUMBER(4) := 3; debit_amt CONSTANT NUMBER(5,2) := 500.00; BEGIN SELECT bal INTO :acct_balance FROM accounts WHERE account_id = :acct FOR UPDATEOF bal; IFacct_balance >= debit_amt THEN UPDATE accounts SET bal = bal - :debit_amt WHERE account_id = :acct; ELSE INSERT INTO temp VALUES (:acct, :acct_balance, 'Insufficient funds'); -- insert account, current balance, and message END IF; COMMIT; END;

16. DECLARE salary emp.sal%TYPE; mgr_num emp.mgr%TYPE; last_name emp.ename%TYPE; starting_empno CONSTANT NUMBER(4) := 7902; BEGIN SELECT sal, mgr INTO :salary, :mgr_num FROM emp WHERE empno = :starting_empno; WHILE salary < 4000 LOOP SELECT sal, mgr, ename INTO :salary, :mgr_num, :last_name FROM emp WHERE empno = :mgr_num; END LOOP; INSERT INTO temp VALUES (NULL, :salary, :last_name); COMMIT; END;

17. Cursors • Definition CURSOR T1 Cursor IS SELECT e, f FROM T1 WHERE e < f;

18. 1)DECLARE • /* Output variables to hold the result of the query: */ • 2) a T1.e%TYPE; • 3) b T1.f%TYPE; • /* Cursor declaration: line 4-8*/ • 4) BEGIN • 9) OPEN T1Cursor; • 10) LOOP • /* Retrieve each row of the result of the above query • into PL/SQL variables: */ • 11) FETCH T1Cursor INTO a, b; • /* If there are no more rows to fetch, exit the loop: */ • 12) EXIT WHEN T1Cursor%NOTFOUND; • /* Insert the reverse tuple: */ • 13) INSERT INTO T1 VALUES(b, a); • 14) END LOOP; • /* Free cursor used by the query. */ • 15) CLOSE T1Cursor; • 16) END; • 17) . • 18) run;

19. DECLARE CURSOR my_cursor IS SELECT sal + NVL(comm, 0) wages, ename FROM emp; my_rec my_cursor%ROWTYPE; BEGIN OPEN my_cursor; LOOP FETCH my_cursor INTO :my_rec; EXITWHEN my_cursor% NOTFOUND; IFmy_rec.wages > 2000 THEN INSERT INTO temp VALUES (NULL, my_rec.wages, my_rec.ename); END IF; END LOOP; CLOSE my_cursor; END;

20. DECLARE CURSOR num1_cur IS SELECT num FROM num1_tab ORDER BY sequence; CURSOR num2_cur IS SELECT num FROM num2_tab ORDER BY sequence; num1 num1_tab.num%TYPE; num2 num2_tab.num%TYPE; pair_num NUMBER := 0; BEGIN OPEN num1_cur; OPEN num2_cur; LOOP -- loop through the two tables and get -- pairs of numbers FETCH num1_cur INTO :num1; FETCH num2_cur INTO :num2; EXIT WHEN (num1_cur%NOTFOUND) OR (num2_cur%NOTFOUND); pair_num := pair_num + 1; INSERT INTO sum_tab VALUES (pair_num, num1 + num2); END LOOP; CLOSE num1_cur; CLOSE num2_cur; END;

21. DECLARE CURSOR c1 is SELECT ename, empno, sal FROM emp ORDER BY sal DESC; -- start with highest-paid employee my_ename CHAR(10); my_empno NUMBER(4); my_sal NUMBER(7,2); BEGIN OPEN c1; LOOP FETCH c1 INTO :my_ename, :my_empno, :my_sal; EXIT WHEN (c1%ROWCOUNT > 5) OR (c1%NOTFOUND); INSERT INTO temp VALUES (my_sal, my_empno, my_ename); COMMIT; END LOOP; CLOSE c1; END;

22. DECLARE num1 data_table.n1%TYPE; -- Declare variables num2 data_table.n2%TYPE; -- to be of same type as num3 data_table.n3%TYPE; -- database columns result temp.num_col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1; BEGIN OPEN c1; LOOP FETCH c1 INTO :num1, :num2, :num3; EXIT WHEN c1%NOTFOUND; -- the c1%NOTFOUND condition evaluates -- to TRUE when FETCH finds no more rows /* calculate and store the results */ result := num2/(num1 + num3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; CLOSE c1; COMMIT; END;

23. I/O Control • DBMS_OUTPUT.NEW_LINE(); • DBMS_OUTPUT.PUT_LINE(); • DBMS_OUTPUT.PUT();

24. I/O Example BEGIN /* Get Current User Name */ SELECT username INTO :l_current_user FROM USER_USERS; DBMS_OUTPUT.NEW_LINE(); DBMS_OUTPUT.PUT('Connect As '||l_current_user); DBMS_OUTPUT.PUT(' And Add Details For '||RTRIM(Empname)); DBMS_OUTPUT.PUT_LINE(' having Id '||RTRIM(empid)); INSERT INTO Employee_table VALUES(Empid, Empname, Empadd, Deptcd, Grade, SYSDATE); /* Increment Department strength */ Increment_Dept_Strength(Deptcd); END Insert_Emp_Details;

25. Logging In to Oracle • log in to Oracle by typing: sqlplus <yourName> • Changing Your Password alter user <yourName identified by <newPassword;

26. Quitting sqlplus • To leave sqlplus, type quit;

27. Executing SQL From a File • Executing SQL From a File sqlplus <yourName/<yourPassword @<fileName • e.g. sqlplus sally/etaoinshrdlu @foo OR @foo.sql

28. Editing Commands • L lists the command buffer, and makes the last line in the buffer the "current" line • Lnprints line n of the command buffer, and makes line n the current line • Lm nprints lines m through n, and makes line n the current line

29. Editing Commands - cont. • I enters a mode that allows you to input text following the current line; you must terminate the sequence of new lines with a pair of "returns" • C /old/new replaces the text "old" by "new" in the current line • A text appends "text" to the end of the current line • DEL deletes the current line

30. Exercises 4.5 using PL/SQL