Using Oracle PLSQL PLSQL stands for Procedural LanguageSQL

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

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;

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

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; my. Name VARCHAR(20);

Variables and Types • %TYPE operator DECLARE my. Name Emp. name%TYPE; • %ROWTYPE operator DECLARE Emp. Tuple Emp%ROWTYPE;

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

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

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

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

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

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

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

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;

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

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 UPDATE OF bal; IF acct_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;

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;

Cursors • Definition CURSOR T 1 Cursor IS SELECT e, f FROM T 1 WHERE e < f;

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

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; EXIT WHEN my_cursor% NOTFOUND; IF my_rec. wages > 2000 THEN INSERT INTO temp VALUES (NULL, my_rec. wages, my_rec. ename); END IF; END LOOP; CLOSE my_cursor; END;

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

DECLARE CURSOR c 1 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 c 1; LOOP FETCH c 1 INTO : my_ename, : my_empno, : my_sal; EXIT WHEN (c 1%ROWCOUNT > 5) OR (c 1%NOTFOUND); INSERT INTO temp VALUES (my_sal, my_empno, my_ename); COMMIT; END LOOP; CLOSE c 1; END;

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

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

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;

Logging In to Oracle • log in to Oracle by typing: sqlplus <your. Name> • Changing Your Password alter user <your. Name identified by <new. Password;

Quitting sqlplus • To leave sqlplus, type quit;

Executing SQL From a File • Executing SQL From a File sqlplus <your. Name/<your. Password @<file. Name • e. g. sqlplus sally/etaoinshrdlu @foo OR @foo. sql

Editing Commands • L lists the command buffer, and makes the last line in the buffer the "current" line • Ln prints line n of the command buffer, and makes line n the current line • L m n prints lines m through n, and makes line n the current line

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 textappends "text" to the end of the current line • DEL deletes the current line

• Exercises 4. 5 using PL/SQL