Oracle PL/SQL

1. Oracle PL/SQL EyadHusniElshami

2. Why PL/SQL • Block Structures: • PL/SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused. •  Procedural Language Capability: • PL/SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops). • Better Performance: • PL/SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic. • Error Handling: • PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

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

4. PL/SQL Variables and Types • Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be one of the types used by SQL for database columns. • A generic type used in PL/SQL such as NUMBER, CHAR, DATE, …etc • Declared to be the same as the type of some database column

5. PL/SQL Variables and Types Declar Std_id number(2); Std_name Students.sname%TYPE; Std_record Students%ROWTYPE; Begin select sid into std_id from students where sname=‘ALI’; select sname into std_name from students where sid=12345; select * into std_record from students where sid=12345; End

6. PL/SQL Control Structures • Conditional statement: • IF Statement • Iterative Statements: • Simple Loop • While Loop • For Loop

7. PL/SQL Conditional statement IF condition THEN Statements ELSIF condition THEN Statements ELSE Statements END IF; Declare day char(3):= to_char(sysdate,'DY'); Begin If day = 'FRI' then DBMS_OUTPUT.PUT_LINE('Week end'); Elsif day = 'SAT' then DBMS_OUTPUT.PUT_LINE('New work week'); Else DBMS_OUTPUT.PUT_LINE('have a nice day'); End if; End; /

8. PL/SQL Iterative Statements Simple loop syntax: LOOP <loop_body> ; EXIT; /* EXIT WHEN condition;*/ END LOOP; DECLARE     i NUMBER := 1; BEGIN     LOOP         INSERT INTO T1 VALUES(i,i);         i := i+1;         EXIT WHEN i>100;     END LOOP; END;

9. PL/SQL Iterative Statements While loop syntax: WHILE condition LOOP         < loop_body> ;   END LOOP; DECLARE     i NUMBER := 1; BEGIN     WHILE i<100 LOOP         INSERT INTO T1 VALUES(i,i);         i := i+1;            END LOOP; END;

10. PL/SQL Iterative Statements For loop syntax: FOR counter IN val1..val2   LOOP     < loop_body> ;   END LOOP; DECLARE     i NUMBER := 1; BEGIN     FOR i IN 1..100 LOOP         INSERT INTO T1 VALUES(i,i);                END LOOP; END;

11. PL/SQL Cursors • What are Cursors? • A cursor is a temporary work area created in the system memory when a SQL statement is executed. • A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. • A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

12. PL/SQL Implicit Cursors: • When you execute DML statements or SELECT statements, implicit cursors are created to process these statements, there are attributes for each cursor: • %FOUND: • Return TRUE value if DML statement affect at least one row or the select statement return at least on (example SQL%FOUND) • %NOTFOUND: • Return FLASE value if DML statement affect at least one row or the select statement return at least on (example SQL%NOTFOUND) • %ROWCOUNT: • Return the number of rows affected by the DML/SELECT statement (example SQL%ROWCOUNT) • %ISOPEN: • Return TRUE value if the cursor is already open in the program.

13. PL/SQL Implicit Cursors: DECLARE rows_count number(5); BEGIN UPDATE students SET saverage= 40 where saverage is null ; IF SQL%NOTFOUND THEN dbms_output.put_line('None of the averages where updated'); ELSIF SQL%FOUND THEN rows_count := SQL%ROWCOUNT; dbms_output.put_line(‘Averages for ' || rows_count|| ‘ students are updated'); END IF; END;

14. PL/SQL Explicit Cursors • An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row . • There are four steps in using an Explicit Cursor. • DECLARE the cursor in the declaration section. • OPEN the cursor in the Execution Section. • FETCH the data from cursor into PL/SQL variables or records in the Execution Section. • CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

15. PL/SQL Explicit Cursors DECLARE CURSOR std_cur IS SELECT sid, sname, saverage FROM students; std_rec std_cur%rowtype; BEGIN IF NOT std_cur%ISOPEN THEN OPEN std_cur; END IF; LOOP FETCH std_cur INTO std_rec; EXIT WHEN std_cur%NOTFOUND; dbms_output.put_line(std_rec.sid || ' ' ||std_rec.sname || ' ' ||std_rec.saverage+5); END LOOP; END;

16. PL/SQL Procedures • A stored procedure or in simple a procedure is a named PL/SQL block which performs one or more specific task. • This is similar to a procedure in other programming languages. • A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage. • We can pass parameters to procedures in three ways: 1) IN-parameters2) OUT-parameters3) IN OUT-parameters

17. PL/SQL Procedures CREATE OR REPLACE PROCEDURE update_name IS BEGIN UPDATE students SET sname = UPPER(sname); UPDATE collge SET cname = UPPER(cname); COMMIT; END;

18. PL/SQL Procedures CREATE OR REPLACE PROCEDURE upadet_average (push_value IN NUMBER) IS BEGIN UPDATE students SET saverage = saverage +push_value WHERE saverage BETWEEN 55 and 59; COMMIT; END;

19. PL/SQL Procedures CREATE OR REPLACE PROCEDURE student_details IS CURSOR std_cur IS SELECT sname, cid, saverage FROM students; std_rec std_cur%rowtype; BEGIN FOR std_rec in std_cur LOOP dbms_output.put_line(std_cur.sname || ' ' ||std_cur.cid|| ' ' ||std_cur.saverage); END LOOP; END;

20. PL/SQL Procedures Execute procedurename;

21. PL/SQL Functions • A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

22. PL/SQL Functions CREATE OR REPLACE FUNCTION need_to_pass (std_id IN NUMBER) RETURN NUMBER IS point NUMBER; BEGIN SELECT SAVERAGE INTO point FROM students WHERE sid = std_id; IF (point<60) THEN RETURN (60-point); ELSE RETURN 0; END IF; END;

23. PL/SQL Functions • Execution for function like: • Since a function returns a value we can assign it to a variable. X := need_to_pass(12345); • As a part of a SELECT statement SELECT sname,saverage, need_to_pass(sid) FROM STUDENTS; • In a PL/SQL Statements like, dbms_output.put_line(need_to_pass(sid));

24. PL/SQL Triggers • A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. CREATE OR REPLACE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT | UPDATE | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) BEGIN --- sql statements END;

25. PL/SQL Triggers CREATE or REPLACE TRIGGER saverage_history BEFORE UPDATE OF saverage ON stdudents FOR EACH ROW /* WHEN :new.saverage>90*/ BEGIN INSERT INTO archive_average VALUES (:old.sid, :old.saverage, :new.saverage, sysdate, user); END;

26. PL/SQL Exception Handling We’ll study it later

27. create function grade(stdid in number) return char is savg stduents.saverage%type; Begin select saverage into asvg from stduents where sid= stdid; if savg>=65 then return 'A‘; Else Return ‘B’; End if; End;