ORACLE WORKSHOP

1. ORACLE WORKSHOP Salim Mail : salim.sucipto@gmail.com Phone : 0815-188-2384 YM : talim_bansal FB : Aku Itu Salim Blog : http://salimsribasuki.wordpress.com/

2. W E W I L L L E A R N • View • Trigger • Function • Stored Procedure • Package • Integrate API with Application

3. O U R S C H E D U L E • 08:00 – 08:10 Introduction • 08:10 – 08:30 View • 08:30 – 09:00 Trigger • 09:00 – 10:00 Function • 10:00 – 10:10 Break I  • 10:10 – 11:00 Stored Procedure • 11:00 – 11:45 Hands on & QA • 11:45 – 12:45 Pray, Lunch, Break • 12:45 – 14:30 Package • 14:30 – 15:00 API & Application • 15:00 – 15:30 Pray, Break II  • 15:30 – 16:30 Hands on & QA

4. ACHIEVE OUR TARGET WITH CONCERN STAY COOL, CALM, COZY PASSION HOPE & PRAY

5. R E A D Y ? BISMILLAH….. Here we go…!!!

6. V I E W A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used. For example, if we frequently issue the following query • Benefit: • Commonality of code being used • Security • Predicate pushing

7. V I E W Example -- View One CREATE VIEW vw_layer_one AS SELECT * FROM emp; -- View two CREATE VIEW vw_layer_two_dept_100 AS SELECT * FROM vw_layer_one WHERE deptno=100;

8. T R I G G E R is procedural code that is automatically executed in response to certain events on a particular table in a database. • Triggers are commonly used to: • prevent changes (e.g. prevent an invoice from being changed after it's been mailed out) • log changes (e.g. keep a copy of the old data) • audit changes (e.g. keep a log of the users and roles involved in changes) • enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock, not the client's) • enforce business rules (e.g. require that every invoice have at least one line item) • execute business rules (e.g. notify a manager every time an employee's bank account number changes) • replicate data (e.g. store a record of every change, to be shipped to another database later) • enhance performance (e.g. update the account balance after every detail transaction, for faster queries)

9. T R I G G E R CREATE OR REPLACE TRIGGER orders_before_insertBEFORE INSERT    ON orders    FOR EACH ROW DECLAREv_username varchar2(10); BEGIN     -- Find username of person performing INSERT into table    SELECT user INTO v_username    FROM dual;     -- Update create_date field to current system date    :new.create_date := sysdate;     -- Update created_by field to the username of the person performing the INSERT    :new.created_by := v_username; END;

10. T R I G G E R Kind of Trigger Insert Triggers: BEFORE INSERT Trigger AFTER INSERT Trigger Update Triggers: BEFORE UPDATE Trigger AFTER UPDATE Trigger Delete Triggers: BEFORE DELETE Trigger AFTER DELETE Trigger

11. S T O R E D P R O C E D U R E A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages. We can pass parameters to procedures in three ways: 1) IN-parameters2) OUT-parameters3) IN OUT-parameters There are two ways to execute a procedure: 1) From the SQL prompt. EXECUTE [or EXEC] procedure_name; 2) Within another procedure – simply use the procedure name. procedure_name;

12. S T O R E D P R O C E D U R E CREATE OR REPLACE PROCEDURE employer_details IS CURSOR emp_cur IS SELECT first_name, last_name, salary FROM emp_tbl; emp_recemp_cur%rowtype; BEGIN FOR emp_rec in sales_cur LOOP dbms_output.put_line(emp_cur.first_name || ' ‘ emp_cur.last_name|| ' ' ||emp_cur.salary); END LOOP; END;

13. S T O R E D F U N C T I O N 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 A function can be executed in the following ways. 1) Since a function returns a value we can assign it to a variable. employee_name := employer_details_func; If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning the return type of the function to it. 2) As a part of a SELECT statement SELECT employer_details_func FROM dual; 3) In a PL/SQL Statements like, dbms_output.put_line(employer_details_func); This line displays the value returned by the function

14. S T O R E D F U N C T I O N CREATE OR REPLACE FUNCTION employer_details_func RETURN VARCHAR(20); IS emp_name VARCHAR(20); BEGIN SELECT first_name INTO emp_name FROM emp_tbl WHERE empID = '100'; RETURN emp_name; END;

15. P A C K A G E A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (specfor short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec. • Advantages of PL/SQL Packages: • Modularity • Easier Application Design • Information Hiding • Added Functionality • Better Performance

16. P A C K A G E

17. C L O S I N G THANK YOU !