introduction to pl sql online training classes part 4

www.quontrasolutions.com info@quontrasolutions.com

info@quontrasolutions.com

Introduction to PL SQL Online Training Classes

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

Creating or Replacing a Function

CREATE OR REPLACE FUNCTION fname( ) RETURN datatype IS

EXECPTION

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;

SELECT SUM(sal)INTO v_sum_salFROM empWHERE deptno = p_deptno;

RETURN v_sum_sal;

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));…

Example, passing parameters

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

Packages

• Package

– Package Specification

– Package Body

• Invoking Package subprogram

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;/

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/

Invoking Package Subprogram

DECLAREv_sum_sal emp.sal%TYPE;

BEGIN…v_sum_sal := emp_info.sum_dept_sal(10);…emp_info.delete_record(1234567);…

OrSQL> exec emp_info.insert_record(…,…)

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

Creating or Replacing Triggers

CREATE OR REPLACE TRIGGER del_emp( p_empno emp.empno%TYPE)

BEFORE DELETE ON emp

FOR EACH ROW

INSERT INTO emp_audit

VALUES(p_empno, USER, sysdate);

Trigger with function usage Example

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.

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.

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

Cursors

DECLARE

CURSOR c_emp ISSELECT empno, ename, jobFROM empWHERE deptno = 20;

FOR v_c IN c_emp LOOP…DBMS_OUTPUT.PUT_LINE(v_c.ename);…

END LOOP;…

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.

Example’s solution

Dynamic SQL

execute immediate 'create table tt(id number(3))';

introduction to pl sql online training classes part 4

Education

pl/sql : introduction. pl/sql pl/sql is oracle's procedural...

pl/sql programming concepts: review - elte...

lecture 4 pl/sql language. pl/sql – procedural sql allows...

oracle sql-pl sql

pl/sql , database objects & security fileadvantages of...

oracle 8 training pl/sql. what is pl/sql? pl/sql stands for...

sql within pl / sql

my sql sql pl/sql

cours sql sql*plus pl/sql

pl/sql (embedded sql)

introduction to pl sql online training classes part 2

pl sql lesson دروس pl/sql

sql & pl/sql - nielit

introduction to pl/sql. main topic: oracle pl/sql what is...

pl/sql pl/sql stands for procedural language/sql. pl/sql...

introduction au pl/sql oracle - alexandre...

oracle pl/sql practices. critical elements of pl/sql best...

pl/sqlpl/sql oracle11g: pl/sql programming chapter 1...

1. ¿qué es pl/sql? introducción a pl/sql

pl/sqlpl/sql oracle10g developer: pl/sql programming chapter...