PLSQL

1. PLSQL Dr .Hayk Melikyan Departmen of Mathematics and CS melikian@bambi.acc.nccu.edu

2. Program Structure PL/SQL like other programming languages, groups its statements into units called BLOCKS. Blocks can be unnamed (anonymous ) or named ( sub-programs). The subprograms can be either functions or procedures and they can be grouped into a packages. PL/SQL also allows access to databases via the cursors, triggers, stored procedures and functions.

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

4. What You Can and can’t ? The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other The SELECT statement has a special format. CREATE, DROP, or ALTER are not allowed. PL/SQL is not case sensitive C style comments (/* ... */) or ( -- ….) . To execute a PL/SQL program, we must follow the program text itself by a line with a single dot ("."), and then a line with run;

5. PL /SQL: Datatypes and Variables DESCRIPTION ( scalar data types) Numeric BINARY INTEGER Integer in the range -231 - 1 to 231 -1. NATURAL Integer in the range 0 to 231 POSITIVE Integer in the range 1 to 231 NUMBER(p,s) Same as Oracle SQL's NUMBER, where p is the precision and s is the scale Character CHAR(N) Fixed-length character string of length N. VARCHAR2(N) Variable-length character string of maximum length N Boolean BOOLEAN Boolean data typ (TRUE, FALSE) Date-Time DATE Same as Oracle SQL's DATE

6. Variables and Types That type can be 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 DECLARE     price  NUMBER;     myBeer VARCHAR(20); or DECLARE     myBeer Beers.name%TYPE;

7. Variables and Types(2) A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name DECLARE      beerTuple Beers%ROWTYPE; The initial value of any variable, regardless of its type, is NULL.

8. PL/SQL: Anchored Declarations cnum customers.cno%type; cname customers.cname%type; commission REAL(5,2) := 12.5; x commission%type; Anchored variables are synchronized with the database columns. The database columns may change their datatypes and the PL/SQL program remains compatible. Anchored variables declared within the PL/SQL program are normalized, i.e. changing one data type does not a_ect how the corresponding anchored variables will be used. Anchored data types are evaluated at compile time.

9. Variables and Types(3) We can assign values to variables, using the ":=" operator. The assignment can occur either immediately after the type of the variable is declared, or anywhere in the executable portion of the program. An exampl DECLARE      a NUMBER := 3; BEGIN     a := a + 1; END; . run;

10. PL/SQL: Variable/Constant Declarations i BINARY_INTEGER; cno NUMBER(5) NOT NULL := 1111; cname VARCHAR2(30); commision NUMBER(5,2) := 12.5; MAXCOLUMNS CONSTANT INTEGER(2) := 30; hired_date DATE; done BOOLEAN; Any variable that is declared to be NOT NULL must be initialized in its declaration. All variables that are not defined to be NOT NULL are initialized to have the value NULL.

11. Simple Programs in PL/SQL After the SELECT clause, we must have an INTO clause listing variables, one for each attribute in the SELECT clause, into which the components of the retrieved tuple must be placed. CREATE TABLE T1( e INTEGER, f INTEGER ); DELETE FROM T1; INSERT INTO T1 VALUES(1, 3); INSERT INTO T1 VALUES(2, 4); Above is plain SQL; below .

12. NEXT 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; /*this is the PL/SQL program

13. Control Flow in PL/SQL if (cnum > 10) and (cnum < 90) then i := i + 1; dbms_output.put_line('Cust ' || cnum); end if; There are three version of of statement in PL/SL: if-then: IF <condition> THEN <statement_list> END IF; if-then-else: IF <condition> THEN <statement_list> ELSE <statement_list> END IF; if (cnum > 10) and (cnum < 90) then i := i + 1; dbms_output.put_line('Valid Cust ' || cnum); else j := j + 1; dbms_output.put_line('Invalid Cust ' || cnum); end if;

14. if –then- elsif statement if (score > 90) then na := na + 1; elsif (score > 80) then nb := nb + 1; elsif (score > 70) then nc := nc + 1; elsif (score > 60) then nd := nd + 1; else nf := nf + 1; end if; IF <condition_1> THEN statement-list-1 ELSIF <condition_2> THEN statement-list-2 ELSIF <condition_n> THEN statement-list-n ELSE statement-list-n+1 END IF;

15. Exampole 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;

16. Loops Loops are created with the following: LOOP     <loop_body> /*A list of statements. */ END LOOP; At least one of the statements in <loop_body> should be an EXIT statement of the form EXIT WHEN <condition>; The loop breaks if <condition> is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into T1 of the above two examples:

17. Example loop i := i + 1; if i > 10 then exit; end if; sum := sum + i; end loop; DECLARE     i NUMBER := 1; BEGIN     LOOP         INSERT INTO T1 VALUES(i, i);       i := i+1;         EXIT WHEN i>13;     END LOOP; END; . run;

18. Other loop-forming statements EXIT by itself is an unconditional loop break. Use it inside a conditional if you like. A WHILE loop can be formed with WHILE <condition> LOOP  <loop_body>      END LOOP; A simple FOR loop can be formed with: FOR <var> IN <start>..<finish>LOOP     <loop_body>   END LOOP; Here, <var> can be any variable; it is local to the for Loop and need not be declared. Also, <start> and <finish> are constants.

19. Cursors A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position. Cursor< cname> [ return <return-spec>] is <select statement>; Once a cursor has been declared, it can beproccessed using open, fetch and close statements; Open <cname>; Fetch <cname> into < record | Variablrlist>; Close <cname>;

20. The nest example illustrates a cursor loop. It uses our example relation T1 (e,f) whose tuples are pairs of integers. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1. DECLARE          a T1.e%TYPE;   b T1.f%TYPE;     CURSOR T1 cursor IS   SELECT e, f   FROM T1  WHERE e < f FOR UPDATE;

21. BEGINOPEN T1Cursor;   LOOP    FETCH T1Cursor INTO a, b;   EXIT WHEN T1Cursor%NOTFOUND; DELETE FROM T1 WHERE CURRENT OF T1Cursor;       INSERT INTO T1 VALUES(b, a);  END LOOP;    CLOSE T1Cursor; END; . run

22. PL/SQL: Cursors CURSOR c1 RETURN customers%ROWTYPE IS select * from customers; CURSOR c2 IS select pno, pname, price*markdown sale_price from parts; _ Cursor c1 uses the RETURN clause which is consistent with the select list of the select statement in the SQL query associated with the cursor. _ Cursor c2 uses a PL/SQL variable markdown in the select statement associated with the cursor. Cursor attributes are %found, %notfound, %rowcount, %isopen Example:

23. DECLARE CURSOR c1 IS select cno,cname,city from customers,zipcodes where customers.zip = zipcodes.zip; c1_rec c1%rowtype; BEGIN if not c1%isopen then open c1; end if; fetch c1 into c1_rec; while c1%found loop dbms_output.put_line('Row Number ' || c1%rowcount || '> ' || c1_rec.cno || ' ' || c1_rec.cname || ' ' || c1_rec.city); fetch c1 into c1_rec; end loop; close c1; END;

24. Cursor for loop DECLARE CURSOR c1 IS select cno,cname,city from customers,zipcodes where customers.zip = zipcodes.zip; BEGIN for c1_rec in c1 loop dbms_output.put_line('Row Number ' || c1%rowcount || '> ' || c1_rec.cno || ' ' || c1_rec.cname || ' ' || c1_rec.city); end loop; END; here we have implicit cursors for i in (select * from employees) loop ... ... end loop;

25. Functions DECLARE enum customers.cno%type; total number(10,2); status boolean; function total_emp_sales(emp_no IN employees.eno%type) return number is sales number; begin select sum(price*qty) into sales from orders, odetails, parts where orders.eno = emp_no and orders.ono = odetails.ono and odetails.pno = parts.pno; return (sales); end;

26. Example1 BEGIN enum := 1000; total := total_emp_sales(enum); DBMS_OUTPUT.PUT_LINE('Total sales for employee ' || enum || ' is ' || total); END;

27. Stored Procedure and Functions CREATE [ or replace] procedure < proced_name> [ (< parameter_list>)] as <declarations> BEGIN <executable_section> [ exception < exception_handler>] END;

28. Function (Example_1) create [ or replace] function < func_name>(<parameter_list>) return <datatypr> as <declarations> begin < executable section> [exception <exception_section>] end;

29. Function Example_2 Create or replace function get_city(cname in customers.cno%type) return zipcodes.city%type as ccity zipcodes.city%type; Begin select city into ccity from customers, zipcodes where cno = cnum and customers.zip = zipcodes.sip; return (ccity); End;

30. Triggers An SQL trigger is a mechanism that automatically executes a specified PL/SQL block ( triggers action) when a triggering event occurs on table. The triggering event may be one of insert, delete or update. The trigger is associated with DB table and is fired when the triggering event takes place on the table. Triggers are created in Oracle using the Create trigger statement whose syntax is

31. Create Trigger(syntax) create [or replace] trigger <trig_name> {before | after} { delete | insert | update[ of column [, column] …]} [ or { delete | insert | update[ of column [, column] …]}] … ON <table_name> [ [referencing { old [as] <old> [new [as] <new>] | new [as]<new > [ old [as] <old> ]}] For each row [when (codition)]] PL/SQL_ blo ck

32. Example create or replace trigger update_price_of_parts after update of price on parts for each row begin insert into parts_log values(:old.pno, user, sysdate, ; old.price, :new.price); end;