Data Mining Lab : Oracle

SQL (Structured Query Language)

DDL - Data Definition Language - Create, Alter, Drop

DML - Data Manipulation Language- Insert,Update, Delete

DCL - Data Control Language- Grant, Revoke

DQL- Data Query Language – Select

Data types :

1. Char:

Used to hold alphanumeric values

Min & Default size is 1 byte.

Max size is 256 characters

Ex : sname char(20)

2. Varchar2 (size) :

Used to hold alphanumeric values

Size must be specified

Max size is 2000 bytes

Memory management is the main difference between char and varchar2.

3. Number :

Holds only numeric values

Max no of digits are 38.

Syntax: <data type>(scale [, precession])

Ex: Price number (6, 2)

4. Date:

Holds the Date/Time expressions

Default and fixed size is 7 bytes

Default format of date is DD-Mon-YY (17-Dec-02)

Large Objects (LOB):

* BLOB - Binary LOB

* CLOB - Character LOB

* NCLOB - Holds Multi byte character set

* Bfile - Holds the reference of the binary file in the

database.

Data Definition Language ,Data Manipulation Language, Data Control Language, Transaction Control Language, Data Query Language

1) DDL - Data Definition Language - Create, Alter, Drop

a. Create:

Create table <TN> (field definition1, field efinition2...);

Table Name:

* There should not be any spaces and special characters except _ and $.

* Max no of characters will be 33.

* Keywords should not be used as table name.

Field definition :

<Field name> <Datatype> [(size)][integrity constraint]

8 - 256

8i - 1000

b.Alter : For altering the structure of the existing table.

Alter command is having 3 clauses.

a. Add

b. Modify

c. Drop

a) Add : Alter table <TN> add(field definition1, field definition2....);

Fname data type (size)

b) Modify: Using the modify clause the data types of the variables can be changed.

Size of the field can be increased. (If no records are there it can be decreased)

Possible conversions:

Date - Varchar2

varchar2- char

char- varchar2

number -varchar2

number - char

Date-Char

Not possible Conversions:

char - number

varchar2 - number

date - number

ex :

Alter table <railway> modify (train_time varchar2 (10));

c) Drop Clause:

Alter table <TN> drop column <field name>;

Alter table railway drop column destination

C.Drop: For dropping the database objects.

Dropping table:

Syntax: Drop table <TN>

Drop table<railway>

2) DML - Data Manipulation Language- Insert, Update, Delete

1) Insert : For inserting the records to the table.

a) Insert into <TN> values (val1, val2, val3....);

b) Insert into <TN> values(&field1,'&field2'....);

c) Insert into <TN>(field1, field2) values (val1,val2);

2) UPDATE:

Update <TN> set <Field=><val>

[where <condition>]

3)Delete:

Syntax: Delete from <TN>[ where <cond>]

DQL: Data Query language :

1) Select: For retrieving the information from the database and to display it on the console.

Syntax:

Select <field list> from <TN>

[where <cond>]

[order by <criteria>]

[group by <criteria>[having <cond>]]

Operators:

1) Arithmetical: +, - , * , /

2) Relational : < , >, <=, >=, =, < >

3) Logical: and , or , not

4) Special: between, not between , in , not in, like, not

% Percentile - Works as * in DOS

_ Underscore - Works as? in DOS

Group expressions:

1) Sum(): Returns the sum of all records of the field specified.

2) Count(): Returns the count of the records in the given field

3) Avg(): Returns the average of all values for the field specified.

4) Min() : Returns the min value among all values in the for the given field.

5) Max(): Returns the maximum value among all values for the given field.

Syntax: in(cond1 , cond2 , cond3..)

Set operators and joins:

Set : A collection of similar format/data type of elements.

1) Union

2) Intersect

3) Minus

4) Union All

1. Union: Returns the unique values from both the sets.

2. Intersect: Returns the Common values from both sets

3. Minus: Returns the values which are present in the first relation and not in the second.

4. Union all: Returns all values from both the sets.

Functions:

The function is a set of statements, which is written to perform a specific task.

Types of Functions:

a. User Defined Functions - (PL/SQL)

b. Pre Defined Functions -

Categories of Functions:

1. Date Functions

2. Arithmetical/Numeric functions

3. String/Text Functions

4. Conversion Functions (Data type)

1. DATE FUNCTIONS

ROWID, SYSDATE, CURRVAL, NEXTVAL

a) Months_between() :Takes two date values as args.

Returns the no of months between the two dates in

numeric format.

b)Add_Months() : Adds the specified no. Of months to the given date value

Takes two args (Date, Numeric)

Returns date expression.

c) Last_day() : Returns the Last date value of the given date’s month.

Date value (One Argument)

d) Next_Day() : Returns the Next date value of the same weekday when current date and required weekday value are given.

e) Round() : Rounds the given date value to either to month or to the year.

Return value will be date expression

Two arguments have to be given.

1) Date value

2) Y/M

f) Trunc() :

2. NUMERIC FUNCTIONS

1) Abs( ) : Takes a numeric value as argument

returns the absolute value of it ( Without any sign)

2) SQRT( ) : Returns the square root of the given no.

3) MOD( ) : Returns the modulus value when the divisor and divisible are given.

4) sin( ) : Returns the sin value in Radians.

input expression can be given between 0 to Pi(3.14)

5) Cos, tan, atan, acos, asin

10) Log(value, base)

3. STRING FUNCTIONS

1) length() : Returns the length of the string

2) Upper() : Converts to uppercase (CAPS)

3) Lower() : Converts to lowercase ( small )

4) Initcap() : Converts the first character of given word to uppercase and rest to lowercase.

5) lpad() : Pads the given character to the string towards left of it to make up the total length of string to the given number.

lpad(string, No of chars, Char to be padded)

6) rpad()

7) Ltrim() : Trims the given characters from the string if found. (LEFT)

Ltrim (Source String, Characters to be trimmed)

8) Rtrim() :

9)substr() : For retrieving a part of the given string.

Syntax:

substr(source string , start position, Length from start position)

10)instr() : For returning position of the first appearence of the given character in the source string.

syntax : instr([start pos,]source str, search str)

11)translate() : Replaces the given character with other in the given string.

syntax :

translate(source string, search string, replacement string)

12)Replace() :

replace(source string, search string, replacement string)

Conversion Functions :

1. To_Number() : Converts the given string to numeric format, provided if the there are only numbers in it.

2. To_Char ():Used to retrieve the date values from the given date expression.

Date - DD

Month - MM / Mon / MONTH

Year - YY / YYYY / Y,YYY

Week Day - DY / DAY

Hour - HH

Minute - MI

AM - AM

Sec - SE

To_char(Date Expr , Format)

3. To_date() : Returns the given date expression by converting it to the default date format, when the given date format is clearly specified.

MISC FUNCTIONS

1) greatest ( ) :Returns the greatest value among the given set of values (date, string, number)

2) least ( ) :

3) nvl() : It is used to instantiate the required number in place of null value(if found), while that field which is holding null value is part of arithmetical calculation.

Syn: nvl(field, number to be instantited)

4) DECODE ( ): Gives the result depending upon the condition specified by the user.

Syntax: decode (condition, case 1, <statement> , case2, <statement>....);

Integrity Constraints:

Integrity: Restricting the user to enter/store the invalid data.

1) Entity integrity constraints

2) Domain integrity constraints

3) Referential integrity constraints

1) Entity Integrity : Checking the status/validity of the current value being entered.

a) Not null: Checks the value at that field in the newly entered record, whether the value not null or not.

cname varchar2(20)not null,

b) Check: Checks whether the value entered by the user is with in the range or not.( Permissible range)

(1)Checking with relational operators:

a) Salary must be less than 10000

sal number(5) check ( sal < 10000 ),

b) Comm can not be -ve value

comm number(4) check( comm > 0),

(2) Checking with special operators :

a) check (sal between 1000 and 2000)

b) job varchar2(10) check(job

in(upper('manager'),upper('clerk'),upper('analyst')))

1) job_code varchar2(10)check (job_code like 'CA%')

2) pincode number(6)check (pincode like('______'))

2) Domain Integrity Constraints:

a) Unique: Checks the uniqueness of the current value by comparing the same with the existing values in the same field.

sno number(3)unique,

b) Primary key : Unique and not null.

Value must be entered and it should not be repeated.

A primary key can be used as parent key for referring from any other table. ( It acts as a parent key).

There can be only 1 primary key per table.

Syntax: admno number(5)primary key,

3) Referential integrity Constraints:

a) Foreign key: The field which referes the values from any other tables that is primary or unique key can be set as foreign key.

EX: adno number(5)references SDet(adno),

The field name can differ in the child table but the data type and size must not differ.

EX: Suppliers: SID number(4) primary key, Sname not null Addr phno Exactly 8 digits

Products: PID number , Pname Not null, QTY 50 to 1000, Price, SID

Customers: CID, PID, CNAME, Addr, Phno

Table level constraints:

A constraint which is imposed on more than one field.

Trno SRC Dest TDate

103 Hyd TPTY 1-jan-03

104 Sec CHN 2-jan-03

103 Hyd TPTY 2-jan-03

Create table SCRLY (Trno number(4), SRC varchar2(20), Dest varchar2(20), TDate date, primary key(Trno,TDate));

Disabling & enabling the constraints:

Alter table <TN> disable/enable constraint <Constraint name>;

For dropping the constraint:

Concatenation symbol ,Table alias, Column alias:

EX:

select job as Desg from emp;

select job"Desg" from emp;

JOINS: Comparing the equalities and inequalities between two or more table without using the set operators.

Types:

1. Simple join

2. Outer join

3. Self join

1) Simple Join

a) Equi join - It used to find equalities between two tables.

b) Non- Equi join -

2) Outer join: The outer join extends the functionality of the simple join. It returns the values which are equal and also the values which are not equal.

Table Alias: select a.* from emp a;

3) Self join: To compare two fields of single table.

Sub Queries and Nested Queries :

A Query in a query is a sub query.

The value which there with use must be given as condition only on sub query

The value which we need to display must be given in main query

The comparison between two tables can be done via. the common field in them.

If we are using the '=' operator as link between two queries the sub query must return a single value only.

TCL (Transaction Control Langauage):

1) Commit

2) Rollback

3) Save point

1)Commit : Saves all the current transactions permanently.

Scope : Till the beginning of session

Till the previous commit / Rollback.

2)Rollback : Undoes all transactions.

Scope : Till the beginning of session

Till the previous commit / Rollback.

3)save point :

sql> save point <SN>

save point s1;

Rollback to save point s1;

DCL: Data Control Language:

1.Grant : To grant some privileges to other user

Privileges :

Database : (DBA)

Grant <priv> to <user>

grant resource to user56;

Object :

Grant:

Grant <priv> on <database object> to <user>[with grant option]

select - Read only

all - Read & Write

EX: grant select on train_det to user56;

2.Revoke : To take back the privileges from the other user

Revoke <priv> from <user>;

Revoke <priv> on <database object> from <user>;

DATABASE OBJECTS

Database Objects : Table , Synonym, Sequence , View , Index , Cluster , Snapshot , DBLink etc..

1) SYNONYM :

A synonym is a stored alias name for a table/view.

* The objects identity will be in hidden state, if the synonym is granted to other user.

syntax :

Create synonym <SN> for <Table/View>

Example:

SQL> create synonym supply for supplier;

Synonym created.

SQL> select * from supply;

DROP SYNONYM:

Drop synonym <SN>

Example:

SQL> drop synonym supply;

Synonym dropped.

2) SEQUENCE :

For generating the auto numbers

syntax :

Create [or replace] sequence <Seq Name>

increment by n

start with n

minvalue n

maxvalue n

[cycle/nocycle - Once the sequence reaches maxvalue it will start iteration from minvalue again (Cycle)

Default - Cycle

cache/no cache] - Creates some placeholder values and stores in the buffer.

Default : cache

When the sequence is created the nextval pseudocolumn will hold the value next to the currval to insert it in the next transaction.

The currval pseudocolumn will hold the current value of the sequence.

EXAMPLE:

SQL> create sequence SUPPLIER_SEQ START WITH 100

INCREMENT BY 1

NOCACHE

NOCYCLE;

Sequence created.

To see next value from psedocolumns with NEXTVAL:

SQL> SELECT SUPPLIER_SEQ.NEXTVAL FROM DUAL;

ALTER SEQUENCE:

We can alter the sequence

Alter sequence tseq minvalue 2005;

Alter sequence tseq maxvalue 1500;

Drop sequence <SN>;

3.VIEWS :

View is logical table, which can also be called as a tailored portion of a table.

Uses of Views :

For hiding the complex query definitions within a view. ( If the query is frequently used, it becomes difficult for the user to rewrite it again and again)

For implementing complex security constraints

Syntax :

Create [or replace] [force] view <VN>

as select <field list> from <TableName>

[where <condition>]

[with check option]

[with read only]

EXAMPLE:

CREATE VIEW:

SQL> CREATE VIEW EMP_VIEW AS SELECT ENAME, SAL*12 ANNUAL_SAL FROM EMP WHERE DEPTNO=30;

View created.

TO VIEW THE VIEW:

SQL> SELECT ENAME, ANNUAL_SAL FROM EMP_VIEW;

4. INDEXES :

Indexing is a concept of creating a logical sortID for each record within the database based on a specific criterion.

On the fields on which the primary key or unique constraints are defined, indexing can not be done.

Indexing can be done on more than one field also(unique index).

Composite index ( If index is created on multiple fields)

Bitmap index (when repeated values are there in the field to be given as criteria)

Syntax

Create index <idxname> on <TN>(<field(s)>);

EXAMPLE:

SQL> CREATE INDEX SUPPLY_IX ON SUPPLIER(SNAME);

Index created.

5. CLUSTERS :

When same field is being maintained in various tables with the same data in order to establish the relationship between the tables , the memory wastage may happen as the redundent data will be maintained.

To avoid this create the cluster including the definition of the field which is going to appear in multiple tables.

Create a cluster index for easy and fast value availability to all the tables which are referring the cluster.

Create the tables by mensioning the cluster name with the field name.

EXAMPLE:

create cluster eclust(eno number(8));

create index eidx on cluster eclust;

create table emp(eno number(8), ename varchar2(20),sal number(4))cluster eclust(eno);

Table Locks :

Locking of resources when the same user is logged on different terminals and trying to manipulate the same table.

1) Share lock

2) Exclusive lock

lock table <TN> in share mode ;

lock table <TN> in exclusive mode ;

ORACLE ARCHITECTURE :

Oracle Database :

It a collection of some files.

1. Database files

2. Control files

3. Redolog files

4. Archivelog files

5. Parameter file etc.

ORACLE Instance :

SGA + 9 background processes

Server Global Area

1. SMON - System monitor

2. PMON - Process monitor

3. ARCH - Archiever

4. Lckn - Locking

5. CKPT- Check point

6. Dnnn - Dispatcher

7. LGWR - Log writer

8. DBWR - Database Writer

ORACLE QUERIES

1. find out the difference between highest and lowest salaries?

2. find all the DEPARTMENTS who have more than three employees?

3. check whether all the employees numbers are indeed unique.

4. list lowest paid employees working for each manager ?

5. list all the employees names and there department names in department order?

6. display all employee name, department number and name?

7.display the department that has no employees ?

8, find all employees who joined the company before their manager?

9. find all the employees who have same job as blake?

10. find the employees who earn more than lowest salary in each department?

11. display employees who earn more than the lowest salary in deptno 30?

12. find the employees who earn more than every employee in deptno 30?

13. find the job with the highest average salary ?

14. display the name job hiredate fro employees whose salary is greater than the highest

salary in the sales department.

15.copy all information on department 10 into the dio history table?

16. delete all information about dept 10 from employee table?

17. create on cluster table cluster index on emp(deptno) and dept(deptno)

18. create synonym on emp table?

19. create index on employee number on emp table?

20. list all the tables which are creater on a particular date?

21. list all the indexes which are created on a particular date and table?

22. list all views which are created for a particular table and date?

23. list all synonyms which are created for a particular table and date?

24.list all the objects which are created on a particular date?

25.display an asterisk against the row of the most recently hired employees

display ename, hiredate, and column name showing "*" and column name

maxdate(for unique records)

26. delete the rows from dept table of those dept numbers do not have matching

rows in the emp table ?

27. select ename, job, sal , mgr, deptno for a given job title?

28. list the meployees name and salary increased by 15% and expressed as a whole

number in dollars?

29. display each employees name with hiredate and review date. assume review

date is one year after hiredate. order the output in ascending review date order?

30. write a query which will return the day of the week for any date entred in the

format dd mm yy?

31. employees hired on or before the 15th of any month are paid on the last friday of

that month. those hired after the 15th are paid the last fridat of the following

month. print a list of employees their hiredate and first pay date sort on hiredate?

32.list the minimum and maximum salary for each job type?

33. find the average salary and average total remuneration for each job type. remember the

salesmen earn commission.

34. find all departments which have more than 3 employees?

35. list all the employees name, job, sal, grade, and dname for every one in the company

except clerks. sort on salary, displaying the highest salary first?

36. list the ename, job, annual salary, deptno,dname,grade of employees who earn $31000

a year or who are clerks?

37. show name, salary, deptno, forany employee who earns a salary greater than the average

for their department sor on department order?

38. who are the top 3 earners in the company display their name and salary?

39. in which year did most people join the company? display the year and number of employees.

40. find the employees who earn a salary greater than the average salary for their department?

41.find all the employees whose department is not in the dept table?

42. it has been discovered that the sales people in department 30 are not malas ?

produce the following output.

ename deptno job

----- ------ ----

allen 30 salesperson

ward 30 salesperson

blake 30 manager

43. find the employee who earns the minimum salary?

44. display the name,job,hiredate,salary for employees whose salary is greater

than the highest salary in SALES department?

45. list employees with either the same job as jobes or a salary greater than or equal to ford's

order by job and salary?

46. list employees in department 10 with the same job as anyone in the sales department?

47.list the employees having the same job as employees located in chicago?

48. list employee whose salary is equal to that of SCOTT or WARD?

49. set salaries of all salesman equal to 1.1 times the average salary of salesman?

50. delete all employees with the same job as jones and jones should not be deleted?

51. find the department in which there are maximum number of employees?

PL/SQL

( Procedure Language/StructuredQueryLanguge)

PL/SQL( Procedure Language / StructuredQueryLanguge)

Advantages:

Network traffic can be reduced, as multiple SQL statements can be passed to the server at the same time.

We can write the user defined functions and procedures using PL/SQL

Exceptions can be handled

Triggers (Database and Schema) can be written to impose complex security over the database objects.

Characterset:

a - z

0-9

Operators :

: = Assigning operator

- - commenting

Data types :

1. Scalar datatypes

All data types supported by SQL and ANSI.

integer , float, double , boolean(true/false)

Binary integer ( 2 * 10 31 to 2* 10 31)

2. Composite datatypes

Records

Collections - Varying Arrays and Nested Tables

Program Structure :

Declare Block

Begin Block

Exception Block

End;

SQL > Set serverout on

Conditional Statements:

1) Simple if

Only 1 condition will be evaluated.

if <cond> then

statements

else

statements

end if;

2) Nested if

if <cond> then

statements

elsif <cond> then

statements

else

statements

end if;

EXAMPLES:

1)To find the Area?

SQL> declare

2 pi constant NUMBER(9,7):=3.1415927;

3 radius INTEGER (5);

4 area NUMBER(14,2);

5 begin

6 radius := 4; area := pi*power(radius,2);

7 insert into areas values (radius,area);

8 end;

9 /

PL/SQL procedure successfully completed.

SQL> select * from areas;

RADIUS AREA

---------- ----------

3 28.27

4 50.27

2)Fetching Table using %type

SQL> declare

2 enum number := &enum;

3 cm emp.comm%type;

4 pay emp.sal%type;

5 doj emp.hiredate%type;

6 dsg emp.job%type;

7 name emp.ename%type;

8 begin

9 select ename,comm,sal,hiredate,job into name,cm,pay,doj,dsg from emp where empno = enum;

10 dbms_output.put_line('The Employees information is '||'name'||name||'commission'||cm||'salary'|

|pay||'date of joining'||doj||'Designation'||dsg);

11 end;

3)Fetching Table using %rowtype

SQL> declare

2 enum number := &enum;

3 erow emp%rowtype;

4 begin

5 select * into erow from emp where empno = enum;

6 dbms_output.put_line('The Employees information is'||erow.ename||erow.sal||erow.job||

7 erow.comm||erow.hiredate);

8 end;

9 /

GOTO statement:

The goto statement is used to shift the control towards a labelled block of statements.

Iterative statements :

1) Simple loop

2) While loop

3) For loop

1) Simple loop:

loop

statements

Incr/dect

exit when<condition>

end loop;

2) While loop :

while <condition > loop

statements

end loop;

3) For Loop :

for <criteria> in <lowerbound>..<upperbound> loop

statements

end loop;

Examples:

1) To add two numbers using Simple loop

SQL> declare

2 x number := 10;

3 y number := 20;

4 z number;

5 begin

6 z := x+y;

7 dbms_output.put_line(z);

8 end;

9 /

2)Print numbers using simple loop

SQL> declare

2 x number :=1;

3 begin

4 loop

5 dbms_output.put_line(x);

6 x := x + 1;

7 exit when x >10;

8 end loop;

9 end;

10 /

3)To find Factorial of given number using While Loop

SQL> declare

2 num number := 6;

3 fact number(5) := 1;

4 begin

5 while num >= 1 loop

6 fact := fact * num;

7 num := num - 1;

8 end loop;

9 dbms_output.put_line('the factorial is' ||fact);

10 end;

11 /

The factorial is : 720

4)Example using - for loop for multiplication table

declare

x number := &x;

y number := 1;

z number;

begin

for y in 1 .. 10 loop

z := x * y;

dbms_output.put_line(x||'x'||y||'='||z);

end loop;

end;

Accesing the records from table.

The record from a table can not be printed directly, the record or a field of the record has to be assigned to a local variable and through the local variable it can be printed.

syntax : select <field> into <local var> from <TN> [where<condition>]

Attributes :

1. %type - It assigns the same data type to the local variable as it is having in the base table.

2. %rowtype - Holds the record type of the table specified.

CURSORS

A cursor is a private memory area/ context area which is used to accommodate multiple records.

We declare a cursor , it will be used as a pointer to handle the information in the temporary buffer.

Types of Cursors :

3 types

1. Static cursors

2. REF cursors

3. Dynamic cursors

1.Static Cursor :

The cursor definition will be given only once, so it's resources will be useful to access a single table only.

Types of Static Cursors :

a)Explicit Cursors

b) Implicit Cursors

a)Explicit Cursor Life Cycle :

1. Declare The Cursor

cursor <cname> is select <field list> from <TN>

[where <cond>]

2. Open the cursor

Open <cname>

3. Fetch the record from cursor into the local variables.

fetch <cname> into <local variable>

4. Close <cname>

Attributes :

1. %found : Used to check whether the scope is there or not in while loop.

2. %notfound : Used to check whether the scope is there or not in simple loop.

3. %rowcount : Returns the number of records fetched during the loop.

4. %isopen : Returns a boolean value, stating that the cursor is opened or closed.

Cursor in for loop :

No need to open the cursor, close the cursor.

Opening and closing of cursor will be done automatically by the for loop.

b)Implicit cursors :

Attributes :

SQL%FOUND

SQL%NOTFOUND

SQL%ISOPEN -- It always evaluate false

SQL%ROWCOUNT

Examples:

1)Cursor with Simple loop

declare

dnum emp.deptno%type := &dnum;

erow emp%rowtype;

cursor c1 is select * from emp where deptno = dnum;

begin

open c1;

loop

fetch c1 into erow;

dbms_output.put_line(erow.empno||' '||erow.ename

||' '||erow.sal||' '||erow.deptno);

exit when(c1%notfound);

end loop;

close c1;

end;

2)Cursor using for loop(to find area of the given radius)

declare

pi constant number(9,7) :=3.1415927;

area number(14,2);

cursor rad_curs is select * from radius_vals;

begin

for rad_val in rad_curs

loop

area :=pi*power(rad_val.radius,2);

insert into AREAS values(rad_val.radius,area);

end loop;

end;

NOTE: RADIUS_VALS & AREAS TABLE should be created before executing this program.

To see the result perform - SELECT * FROM AREAS;

EXCEPTIONS

An error which can be handled is called an exception.

Exceptions are of 2 types.

1. User defined exceptions

2. Pre defined exceptions

1) User Defined Exceptions :

For defining an exception we must declare an exception variable in the declare block.

We must raise it when the condition is getting satisfied

It will be handled in the exception block

For giving huge exception messages use the method raise_application_error( ).

it will have 2 args.

1. The error number (user defined and can be between

-20000 to -20999)

2. Error message ( 512 characters )

2) PreDefined Exceptions :

When predefined exceptions are used, no need to declare and raise it , directly it can be handled in the exception block.

Ex :

Zero_Divide

Invalid cursor

cursor_already_open

value_error etc.

Examples:

1)user defined Exception:

declare

en emp1.empno%type := &en;

pay emp1.sal%type;

bs emp1.basic%type;

exception ex1;

exception ex2;

deduct number(9) := &deduct;

ressal number(9,4);

begin

select sal,basic into pay,bs from emp1 where empno=en;

ressal:=sal-deduct;

if ressal<=0 then

raise ex1;

elsif ressal<bs then

raise ex2;

else

update emp1 set sal=ressal where empno=en;

end if;

exception

where ex1 then

raise_application_error(-20146,'THE EMPLOYEE WILL NOT GET ANY SALARY');

when ex2 then

raise_appllication_error(-20147,'SALARY IS LESSTHAN BASIC');

end;

2) Predifined/Systemdefined - Zero_Divide:

declare

x number := &x;

y number := &y;

z number;

begin

z := x/y;

dbms_output.put_line(z);

exception

when Zero_Divide then

Raise_application_Error(-20143,'Number Cannot be divided by Zero');

end;

3)TWO_MANY_ROWS-Predifined exception

declare

dn emp1.dno%type:=&dn;

erow emp1%rowtype;

begin

select * into erow from emp1 ;

dbms_output.put_line(erow.empno||' '||erow.sal||' '||erow.dno);

exception

when TOO_MANY_ROWS then

RAISE_APPLICATION_ERROR(-20145,'A PL/SQL PROGRAM UNIT CANNOT RETURN MULTIPLE RECORDS');

end;

SUB Programs :

1. Procedures,

2. Functions,

3.Packages.

PROCEDURES

PROCEDURE: A procedure is a PL/SQL program which does not return any value during its execution.

It Will need some arguments.

The Arguments are of 3 types

1. In 2. Out 3. InOut

Default argument is in argument.

Syntax :

Create [or replace] procedure <PN>(args) is

[local Declarations] begin statements

Exception <Exception handlers>

End;

2 ways to execute procedures

1. At SQL prompt

If any out parameters are there, the value of it must be captured in any local variable. So Delcare the varible as follows

Then pass the local variable to the procedure as a bind variable.

:var

The procedure can be executed by the command execute .

Syntax :

SQL> Execute <procname>(args);

SQL> Vari <variable> <datatype>;

1)simple procedure program:

create or replace procedure proc1(a number,b number) is

c number;

begin

c:= a+b;

dbms_output.put_line(c);

end;

2. Through PL/SQL program

create or replace procedure salvss(eno number, name char, BP number) is

hra number(5,2); ta number(5,2); da number(5,2); cca number(4); pf number(5,2); tax number(6,2);

gs number(7,2); ns number(7,2);

begin

hra:=BP*0.2; ta:= BP*0.3; da:=BP*0.4; pf:=BP*0.1;

tax:=BP*0.3; cca:=200; gs:=BP+hra+ta+da+cca; ns:=gs-pf-tax;

insert into empvss values( eno, name, BP,hra,ta,da,cca,pf,tax,gs,ns);

end;

3)procedure using cursors:

create or replace procedure empproc(dn emp1.dno%type) is

cursor c1 is select * from emp1 where dno = dn;

er emp1%rowtype;

begin

open c1;

loop

fetch c1 into er;

dbms_output.put_line(er.empno||' '||er.sal||' '||er.basic);

exit when c1%notfound;

end loop;

close c1;

exception

when INVALID_CURSOR then

raise_application_error(-20134,'please check the cursor name');

when CURSOR_ALREADY_OPEN then

raise_application_error(-20135,'close the cursor initially to open it again');

end;

Functions :

Function is a sub program which will return a value by default.

The value which is returned by the function must be specified while declaring the function.

We can not perform any database related manipulations through functions.

Syntax:

create or replace function <FN> return <datatype> is

[local declarations]

begin

Statements

Exception < Exception handlers >

end;

We can call a function as a single row function

SQL> select function (args) from <TN>;

EXAMPLE FOR FUNCTION:

create or replace function overdue_charges (aname IN VARCHAR2)

return NUMBER

owed_ammt NUMBER(10,2);

begin

select SUM(((RETURNEDDATE - CHECKOUTDATE) -14) * 0.20)

into owed_ammt from bookshelf_checkout

where NAME = aname;

RETURN(owed_ammt);

end;

TO EXECUTE:

SQL> select overdue_charges('ABC') from bookshelf_checkout;

OVERDUE_CHARGES('ABC')

----------------------

-10

You can check the function by creating a variable and setting its value equal to functions return value

SQL> variable owed_ammt NUMBER;

SQL> EXECUTE:owed_ammt := overdue_charges('ABC');

PL/SQL procedure successfully completed.

SQL> PRINT owed_ammt;

OWED_AMMT

----------

-10

NOTE:The table bookshelf_checkout dhould be there before executing this table

Ex:

SQL> select * from bookshelf_checkout;

RETURNEDDATE CHECKOUTDATE NAME

--------- --------- ----------

12-JUL-05 28-JUN-05 XYZ

PACKAGES

A package is a collection of variables, procedures and functions.

It is a sub program which does not takes any arguments.

Advantages:

1) Granting privileges is easier using the packages compared to individual granting.

2) Location of related functions and procedures of a project is easy.

The Creation of a Package will include 2 steps.

Create the package specification initially, mentioning only the names of all sub programs to be embedded in it.

Create the Package body along with procedure and function scripts.

Syntax for package specification:

Create or replace package <Pack Name> as

function <function name>(args) return <datatype>;

procedure <Proc name>(args);

End <pack name>;

Syntax for Package Body:

Create or replace package body <Pack Name> as

function <Fname> (args) return <datatype> is

local declarations

statements

end Function

Procedure <Pname> (args) is

local declarations

Statements

end Procedure

End <Packname>

Using the Functions and procedures of Package :

<pack name>.<object name>([args]);

Example:

First create table emp1(empno,sal,basic,dno)

1)Package Specification:

create or replace package epack as

function esal(en emp1.empno%type)return number;

procedure updatesal(dn emp.dno%type);

end epack;

2) Package Body:

create or replace package body epack as

function esal(en emp1.empno%type)return number is

pay emp1.sal%type;

begin

select sal into pay from emp1 where empno=en;

return pay;

end esal;

procedure updatesal(dn emp1.dno%type)is

er emp1%rowtype;

cursor c1 is select * from emp1 where dno=dn for update of sal;

begin

for er in c1 loop

update emp1 set sal=sal+10 where current of c1;

end loop;

dbms_output.put_line('RECORDS UPDATED');

end updatesal;

end epack;

To Execute function:

SQL> select epack.esal(111) from dual;

EPACK.ESAL(111)

---------------

5000

To execute procedure:

SQL> execute epack.updatesal(225);

RECORDS UPDATED

TRIGGERS

Database Triggers :

A trigger is a sub program, which restricts the user to perform any DML operation (Database Trigger (DBT'S)) or

Restricts the user to perform any DDL Operation (Schema Trigger)

Types of Triggers (Data base):

1. Insert , Delete , Update

2. Before / After

3. Statement / Row level

1. Before Insert Row level

2. After Insert Row level

3. Before Insert statement level

4. After Insert statement level

5. Before update Row level

6. After update Row level

7. Before update statement level

8. After update statement level

9. Before Delete Row level

10. After Delete Row level

11. Before Delete statement level

12. After Delete statement level

Syntax to Create a Trigger :

Create or replace trigger <Trg name> <before/after/instead of> <insert/update/delete>[for each row] on <Table Name>

begin

Statements

End;

Enabling/Disabling the triggers:

Alter trigger <Trg Name> enable/disable;

Dropping the Trigger :

Drop trigger <Trg Name>

:New Vs :Old Trigger :

:new.field name -> Used to refer the field value after updation

:old.fieldname -> Used to refer the field value before updation

1)create or replace trigger rest1 before insert or update or delete on emp1

begin

if to_char(sysdate,'dy')='fri' or to_char(sysdate,'dy')='sun' then

end if

raise_application_error(-20143,'today is holiday');

end;

2)create or replace trigger elg1 before insert on emp2 for each row

mths number;

yrs number;

begin

select months_between(sysdate,:new.dob) into mths from emp2;

yrs := round(mths)/12;

if yrs<21 then

raise_application_error(-20345,'the candiate is not ELIGIBLE ');

end if;

end;

create or replace trigger inc_rec INSTEAD OF insert on eview

begin

insert into emp1(empno,sal,basic,dno)values(:new.empno,:new.sal,:new.basic,:new.dno);

insert into dept1(dno,dname,loc)values(:new.dno,:new.dname,:new.loc);

end;

SQL> Insert Into eview(empno,sal,basic,dno,dname,loc)values(116,4000,2000,222,

2 'production','mumbai');

1 row created.