Example Block (OneBlock.old)

1. Example Block (OneBlock.old) DECLARE New_Name char(15) := 'Joe Smith'; New_ID number(4) := 9999; new_salary number(7,2) := 66780; new_mgr number(4) := 5678; BEGIN UPDATE myemps SET salary = new_salary WHERE idnum = New_ID; IF SQL%NOTFOUND THEN INSERT INTO myemps VALUES (New_Name, New_ID, new_salary, new_mgr); END IF; END; /

2. Example Procedure (OneBlock.sql) CREATE OR REPLACE PROCEDURE Csal ( New_Name char, New_ID number, new_salary number, new_mgr number) AS BEGIN UPDATE myemps SET salary = new_salary WHERE idnum = New_ID; IF SQL%NOTFOUND THEN INSERT INTO myemps VALUES (New_Name, New_ID, new_salary, new_mgr); END IF; END; /

3. Running Procedures SQL> BEGIN 2Csal(‘Joe Smith’, 9999, 66780, 5678); 3 END; 4 /

4. Example Function (GetFunc.sql) create or replace function OneGet (I_Name myemps.name%type ) RETURN myemps.idnum%TYPE IS My_IDnum myemps.idnum%TYPE; BEGIN SELECT IDNUM INTO My_IDnum FROM myemps WHERE name = I_Name; RETURN My_IDnum; END OneGet; /

5. Running Functions (CallFunc.sql) SQL> Set ServerOutput ON SQL> Declare 2 temp1 myemps.idnum%type; 3 BEGIN 4 temp1 := OneGet(‘Joe Smith’); 5 dbms_output.put_line(temp1); 6 END; 7 /

6. Running Functions II • More usually: SQL> Select Name, OneGet(Name) 2 From MyEmps;

7. New Data Dictionary Tables/Views • Select * from User_Objects • Displays tables, views, indexes, functions, procedures, triggers, etc. • Select Distinct Name, Type From User_Source; • Name and type of all PL/SQL source • Select Text From User_SourceWhere name = ‘ONEGET’;

8. Errors in Functions/Procedures(GetFunc.err) • Select * From User_Errors • The rows in this table are the errors in the compilation process from the last function or procedure Oracle attempted to compile. • Also: • Show Errors

9. Triggers • Triggers are <Event> <Action> Pairs • The <Event> is insert, update, or delete of a table. • The <Action> is a PL/SQL block which is to be executed whenever the <Event> it is associated with occurs.

10. Create Trigger Syntax Create or Replace Trigger <Trigger-Name>{Before|After} Insert or Delete or Update ON <Table Name> For Each Row <Pl/SQL Block as Trigger Body> To differentiate between insert, update, delete: IF inserting THEN ……ELSEIF updating THEN…..ELSE ……… /* must be deleting */

11. FK as a Trigger (FKTrigger.sql) create or replace trigger fkmyemps before delete on myemps FOR EACH ROW DECLARE Current_Count NUMBER; BEGIN select count(*) into Current_Count from Dept where mgridnum = :old.idnum; DBMS_OUTPUT.PUT_LINE('Foreign Key: The count is ' || Current_Count); If Current_Count > 0 THEN RAISE_APPLICATION_ERROR(-20000, 'May not delete this record. '); END IF; END fkmyemps; /

12. PK As a Trigger (PKTrigger.sql) create or replace trigger pkmyemps before insert on myemps FOR EACH ROW DECLARE Current_Count NUMBER; Exist_Name char(20); BEGIN select count(*) into Current_Count from myemps where idnum = :new.idnum;

13. PK As a Trigger II DBMS_OUTPUT.PUT_LINE('The count is ' || Current_Count); if Current_Count > 0 THEN SELECT Name INTO Exist_Name FROM myemps WHERE idnum = :new.idnum; RAISE_APPLICATION_ERROR(-20000, 'id number is not unique; it has already been assigned to '|| Exist_Name); END IF; END pkmyemps; /

14. Looping Syntax -- LOOP Declare LCV number := 1; BEGIN LOOP Insert into Temp_table Values (LCV, ‘Loop Index’); LCV := LCV + 1; EXIT WHEN LCV > 50; END LOOP; END;

15. Looping Syntax -- WHILE Declare LCV number := 1; BEGIN WHILE LCV <= 50 LOOP Insert into Temp_table Values (LCV, ‘Loop Index’); LCV := LCV + 1; END LOOP; END;

16. Looping Syntax -- FOR Declare LCV number; BEGIN for lcv in 1..50 LOOP Insert into Temp_table Values (LCV, ‘Loop Index’); END LOOP; END;

17. Looping Syntax -- GOTO Declare LCV number := 1; BEGIN <<BeginLoop>> if LCV > 50 THEN GOTO EndLoop; END IF; Insert into Temp_table Values (LCV, ‘Loop Index’); LCV := LCV +1; GOTO BeginLoop; <<EndLoop>> END;

18. Record Structures Declare Type Emp_type is RECORD ( Name Char(15), IDNum number(4), Salary number(7,2), Mgr_IDNum number(4) ); ThisEmpRec Emp_Type; BEGIN ThisEmpRec.Name := ‘Smith’;END;

19. Record Structures II Declare ThisEmpRec MyEmps%RowType; BEGIN ThisEmpRec.Name := ‘Smith’;END;

20. Cursors Declare CURSOR MyEmpList IS Select IDNum, Salary, MgrIDNum From MyEmps Where Name=ThisEmpRec.Name; BEGIN OPEN MyEmpList;

21. Cursors II LOOP Fetch MyEmpList into ThisEmpRec.Name, ThisEmpRec.Salary, ThisEmpRec.MgrIDNum; EXIT WHEN MyEMpList%NOTFOUND; END LOOP; CLOSE MyEmpList; END;

22. Cursor Attributes • %FOUND • %NOTFOUND • %ISOPEN • %RowCount • Returns number of rows fetched so far.

23. Explicit Locking • LOCK TABLE <Table-Name>IN <Lock Mode> MODE[NoWait] • Modes available • Row Exclusive • Row Share or Share Update • Share • Share Row Exclusive • Exclusive

24. Row Level Locking Example Declare CURSOR CS_Faculty IS Select * From MyEmps WHERE Dept# =12 FOR UPDATE; ThisEmp CS_Faculty%RowType; BEGIN OPEN CS_Faculty;

25. Row Locking Example II LOOP Fetch CS_Faculty into ThisEmp; EXIT WHEN CS_faculty%NOTFOUND; ThisEmp.Salary := (ThisEmp.salary+50)*1.03; Update MyEmps Set salary = ThisEmp.Salary WHERE CURRENT OF CS_Faculty; END LOOP; COMMIT; CLOSE CS_Faculty; END;