introduction to pl sql online training classes part 4
DESCRIPTION
Quontra Solutions is a leading training institute providing Online training and have the best Trained and Certified Consultants on place and having more than 7 years of experience... SQL PL/SQL Online Training SQL • Basic SQL Statement, SELECT,FROM Clause • Sorting : ORDER BY , WHERE Clause • Singlerowfun • Multiple Tables Data – Joins • Group Functions – GROUP BY Clause • Sub Queries • Manipulating data • Create tables • constraints • views • indexes • useraccess. • Datetime • SET Operators • Hierarchial Table Design, Query • Database Architecture • SQL DataModeller • ERD Concepts PL SQL • variables • interaction_for_loop_if_else • composite data type • cursors – 3 types • Exceptions • procedures • functions • packages • BULK Concepts – Update, Delete, Insert • Triggers • Oracle Supplied Packages • LOB Concepts • UTL_FILE Concepts • Custom Schema Development • Custom Packages Development Database : Oracle Database 11G Schema : HR, SCOTT, SYSTEM Tool 1 : SQL Developer Tool 2 : SQL Developer Data Modeller ERD Design Tool3 : TOAD We free Market your Resume and Provide online training videos for your future reference which you’re attended… Call us for Free Demo Contact : (404)-900-9988 (USA) Email Id : [email protected] Website: http://www.quontrasolutions.comTRANSCRIPT
![Page 2: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/2.jpg)
Function
• Is a block with a name
• Returns one value only.
• The DECLARE key word is not used
• Parameters can only be IN
• Is stored
www.quontrasolutions.com [email protected]
![Page 3: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/3.jpg)
Creating or Replacing a Function
CREATE OR REPLACE FUNCTION fname( ) RETURN datatype IS
BEGIN
EXECPTION
END;/
www.quontrasolutions.com [email protected]
![Page 4: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/4.jpg)
Creating or Replacing a Function
CREATE OR REPLACE FUNCTION sum_dept_sal(p_deptno IN NUMBER)RETURN emp.sal%TYPE IS
v_sum_sal emp.sal%TYPE;
BEGIN
SELECT SUM(sal)INTO v_sum_salFROM empWHERE deptno = p_deptno;
RETURN v_sum_sal;
END;/
www.quontrasolutions.com [email protected]
![Page 5: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/5.jpg)
Invoking a Function
SET SERVEROUTPUT ON;
DECLARE…v_sal emp.sal%TYPE;…
BEGIN…v_sal := sum_dept_sal(10);DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_sal));…
END;/
www.quontrasolutions.com [email protected]
![Page 7: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/7.jpg)
Exercise
• Write down a procedure that displays all records (all columns) from EMP table. Make sure program should restricted to fix number of records of this table. Display Date and TIME portions of column that has data type DATE.
• Write down a procedure that can insert large number of records entered at the time of its execution. You can use EMP table and appending counter with some columns’ values
www.quontrasolutions.com [email protected]
![Page 8: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/8.jpg)
Packages
• Package
– Package Specification
– Package Body
• Invoking Package subprogram
www.quontrasolutions.com [email protected]
![Page 9: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/9.jpg)
Package Specification
CREATE OR REPLACE PACKAGE emp_info is
v_count INTEGER;
PROCEDURE insert_record( p_empno IN NUMBER, p_ename IN VARCHAR2, p_job IN VARCHAR2 , p_sal IN NUMBER, p_comm IN NUMBER, p_deptno IN VARCHAR2);
PROCEDURE delete_record(p_empno IN NUMBER);
FUNCTION sum_dept_sal( p_deptno IN dept.deptno%TYPE) RETURN is dept.sal%TYPE;
END emp_info;/
www.quontrasolutions.com [email protected]
![Page 10: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/10.jpg)
Package Body
CREATE OR REPLACE PACKAGE BODY emp_info is`
PROCEDURE insert_record(p_empno IN NUMBER,p_ename IN VARCHAR2,p_job IN VARCHAR2,p_sal IN NUMBER,p_comm IN NUMBER,p_deptno IN VARCHAR2) ISBEGIN
INSERT INTO EMP(empno,ename,job,sal,comm,deptno)VALUES(p_empno,p_ename,p_job,p_sal,p_comm,p_deptno);
END insert_record;
PROCEDURE delete_record(p_empno IN NUMBER) ISBEGIN
DELETE FROM EMPWHERE empno=p_empno;
END delete_record;
FUNCTION sum_dept_sal(p_deptno IN NUMBER) RETURN emp.sal%TYPE ISv_sum_sal emp.sal%TYPE;BEGIN
SELECT SUM(sal)INTO v_sum_salFROM empWHERE deptno = p_deptno;
RETURN v_sum_sal;END;
END emp_info; --end of package body/
www.quontrasolutions.com [email protected]
![Page 11: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/11.jpg)
Invoking Package Subprogram
DECLAREv_sum_sal emp.sal%TYPE;
BEGIN…v_sum_sal := emp_info.sum_dept_sal(10);…emp_info.delete_record(1234567);…
END;/
OrSQL> exec emp_info.insert_record(…,…)
www.quontrasolutions.com [email protected]
![Page 12: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/12.jpg)
Triggers
• Is a stored subprogram associated with a table.
• Are fired by an event
• Are mainly used for
– Security
– Enforce complex integrity constraint
– Prevent invalid transaction
– Event logging
www.quontrasolutions.com [email protected]
![Page 13: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/13.jpg)
Creating or Replacing Triggers
CREATE OR REPLACE TRIGGER del_emp( p_empno emp.empno%TYPE)
BEFORE DELETE ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_audit
VALUES(p_empno, USER, sysdate);
END;
/
www.quontrasolutions.com [email protected]
![Page 14: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/14.jpg)
Trigger with function usage Example
![Page 15: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/15.jpg)
Exercise
Write a trigger for the following:
When a record is added or deleted from an employee table, DEPT.NoOfEmp column gets updated accordingly to number of employees in EMP table corresponding to department number.
www.quontrasolutions.com [email protected]
![Page 16: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/16.jpg)
Cursors
• Is a pointer to a row.
• Its is mainly used with tables which return more than one row.
• It is handled implicitly and explicitly.
www.quontrasolutions.com [email protected]
![Page 17: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/17.jpg)
Cusrors
CURSOR c_emp IS
SELECT empno, ename, job
FROM emp
WHERE deptno = 20;
7369 Smith Clerk
7566 Jones Manager current row
7788 SCOTT Analyst
7876 Adams Clerk
7906 FORD Analyst
www.quontrasolutions.com [email protected]
![Page 18: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/18.jpg)
Cursors
DECLARE
CURSOR c_emp ISSELECT empno, ename, jobFROM empWHERE deptno = 20;
BEGIN
FOR v_c IN c_emp LOOP…DBMS_OUTPUT.PUT_LINE(v_c.ename);…
END LOOP;…
end;/
www.quontrasolutions.com [email protected]
![Page 19: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/19.jpg)
Example
• Given a table with first three columns are composite keys. Write a PL/SQL program using cursor to update cat_template1 with LONG column cat_template. Assuming this tables contains more than 40,000 records.
www.quontrasolutions.com [email protected]
![Page 21: Introduction to pl sql online training classes part 4](https://reader034.vdocuments.us/reader034/viewer/2022051617/5598e26b1a28abab7d8b463e/html5/thumbnails/21.jpg)
Dynamic SQL
begin
execute immediate 'create table tt(id number(3))';
end;
/
www.quontrasolutions.com [email protected]