ORACLE 9i - Harsh Divya



[pic]

SUBJECT: DBE

SEMESTER-4TH

[pic]

SUREN KUMAR SAHU

Asst.prof in CSE

[pic]

[pic]

Oracle 9i is the latest version of oracle, which offers a comprehensive high performance infrastructure for e-business [electronic business]. Generally it is a database based on ORDBMS (object relational database management system) concept.

DATABASE:-

A database is an organized collection of information. The piece of information on database is called data.

RELATIONAL DATABASE MANAGEMENT SYSTEM:-

Oracle provides a flexible RDBMS called Oracle 9i. Using its features, you can store and manage data with all the advantages of a relational structure plus PL/SQL, an engine that provide you with the ability to store and execute program units. Oracle 9i also supports JAVA & XML. The Oracle server offers the option of retrieving data based on optimization techniques. It includes security features that control how a database is accessed and used. Others features include consistency and protection of data through locking mechanisms.

The oracle server provides an open, comprehensive, and integrated approach to information management. An oracle server consists of an oracle database and an oracle server instance. Every time a database is started, a system global area (SGA) is allocated, and oracle background processes are started. The system global area in an area of memory used for database information shared by the database users. The combination of the background processes and memory buffers is called an oracle instance.

PRODUCT OF ORACLE 9i:-

Oracle 9i having two products:-

i) Oracle 9i application server.

ii) Oracle 9i database.

i) Oracle 9i application server:-

This is the product of oracle which runs all the user application.

(ii) Oracle 9i database:-

It is another product of oracle which manages all the user’s data. By using this we can only capable to store the data.

SQL (Structure Querry Language):-

In a relational database a user don’t specify the access root to the table and the user not to know, how the data is arranged physically.

To access the database it needs the help of a querry language which is SQL.

The SQL is first introduced by American standard national institute for operating relational database. This language contains a large set of operators for partitioning a combining relation.

By using SQL the database can be modified. In oracle no command is used in here all are statement are used, or the keywords or clauses.

Using the following simple rules and guidelines, you can construct valid statements that are both easy to read and easy to edit.

• SQL statements are not case sensitive, unless indicated.

• SQL statements can be entered on one or more lines.

• Keywords can’t be split across lines or abbreviated.

• Clauses are usually placed on separated lines for readability and case of editing.

• Indents should be used to make code more readable.

• Keywords typically are entered in uppercase; all other words, such as table names and columns, are entered in lower case.

Advantages:-

• Efficient

• Easy to learn and use.

• Functionally complete (with SQL, you can define, retrieve and manipulate data in the tables.

|STAEMENT |LANGAUGE |

| |DQL-data querry language: used for retrieve data from the database. |

|Select | |

| |DML-data manipulation language: enters new rows, changes existing rows |

|Insert, update, delete, merge |and removes unwanted rows from tables in the database, respectively. |

| |DDL-data definition language: Sets up, changes, and removes data |

|Create, alter, drop, rename, truncate |structure from tables. |

| |TCL-transaction control language: manages the changes made by DML |

|Commit, rollback, save point |statements. Changes to the data can be grouped together into logical |

| |transaction. |

| |DCL-data control language: gives or removes access rights to both the |

|Grant, revoke |oracle database and the structures within it. |

• Default user name in oracle is scott and password is tiger.

• Environment( SQL*Plus

• In internate(iSQL*Plus.

SYNTAX TO CREATE YOUR OWN USER:-

create user user name

identified by password;

sql> grant connect, resource to user name;

sql> exit;

To see all the users:- show user;

FOR GRANTING ALL PRIVILEGES TO THE NEWLY CREATED USER:-

grant resources to user name;

grant create season to user name;

FOR REVOKING THE PRIVILEGES OF AN OBJECT:-

We can remove the privileges granted to our user by using revoke statement.

Syntax:-

revoke privilege1, privilege2,…..

on

from user;

e.g.:-

revoke select, insert

on

from XYZ;

CHANGING THE PASSWORD OF A USER:-

alter user

identified by ;

To view all the tables of a user the querry is :-

Select * from tab;

A table is a database object that holds the user data directly. It is a collection of some rows and interrelated columns.

The columns having required data as for the table. For prohibiting the invalid data entry or for separating the data from one another each column having its own data type. The data types are some predefined keywords assigned to different columns of a table for inputting different types of data.

|DATATYPE |DESCRIPTION |

|Char(size) |This data type is used for sharing character values of fixed length. The|

| |maximum no. of character holds is 255. |

|Varchar2(size) |This is also a character data type, but it stores the variable length |

| |data which means it will not used the before and after white spaces with|

| |the data as a character. It can maximally hold 2000 characters. |

|Number(size) |The number data type is used for storing numeric data as well as |

|Number(size-digit, decimal place) |floating point data. |

|Long |This is also a character data type having variable length character, but|

| |contains the character up to 2913. |

|Date |This data type is used to represent date and time data. |

CREATION OF TABLE:-

Syntax:-

create table

( data type,

data type,…….);

E.g.:-

create table friends

(name varchar2(25),

location varchar2(20),

ph_no number(13));

To show the outer structure of a table:-

The ‘desc’ (describe) statement is used to view the column name and data type of a table.

Syntax:-

desc;

e.g.:-

desc friends;

ENTERING DATA INTO A TABLE:-

For entering or inserting the data into a table the ‘insert’ statement is used.

e.g.:-

insert into friends

values(‘nabin’,’berhampur’,9861214323);

INSERTING DATA INTO A SPECIFIED COLUMN:-

To insert data into a particular column the syntax is:-

insert into

(column name 1, column name 2,……)

values(value 1,value 2……);

e.g.:-

insert into friends

(name, location)

values(‘nabin’, ’bam’);

INSERTION BY USING MACRO(PROMTING) OPERATOR(&):-

Syntax-

insert into

(column name 1, column name 2,……)

values(‘&name’, ’&location’, &ph_no);

RETRIBING DATA FROM A TABLE:-

For retriving the records or data from a table the select statement is used. Here in select statement it contains he required columns of the user (to which the user wants o view) and the table name.

Syntax:-

select column name1, column name2……

from;

e.g.:-

select name, location

from friends;

RETRIBING DATA BY USING WHERE CLAUSE:-

In a querry the where clause is used for retribing the records from a table through a particular condition.

Syntax:-

select from

Where

[condition];

e.g.:-

select * from friends

where

name=’nabin’;

[pic]

1. ARITHMATIC OPERATOR:-

|OPERATOR |DESCRIPTION |

|‘+’ |Addition |

|‘-‘ |Substraction |

|‘*’ |Multiplication |

|‘/’ |Division |

( precedence of operator( ‘*’(’/’(’+’(’-‘

2. COMPARISION OPERATOR:

|= |Equal to |

|> |Greater than |

|>= |Greater than equal to |

|< |Less than |

|< = |Less than equal to |

| |Not equal to |

The statement used to clear the screen is: - clear screen.

COMPARISON:-

The comparison operators are used for extracting the records by comparing a certain value to a range of values or to a single value. The operators are:-

|OPERATOR |DESCRIPTION |

|Between |It is used for extracting the records from a range of values. |

|In |It is used for comparing or extracting the column values from a set of |

| |values. |

| |It is used for comparing a character value to another character value. |

| |Here along with the like operator two operators are used for single |

| |character comparison and multi character comparison. For single |

|Like |character comparison (_) is used and for multi character comparison (%) |

| |is used. |

|Is null |It is used for comparing a column value with null value. |

Syntax of between:-

select from

where [condition value] between

[value 1] and [value 2];

e.g.;-

select * from emp

where

sal between

2000 and 3000;

Syntax of in-

select from

where [condition value] in

(value 1, value 2,…….);

e.g.:-

select * from emp

where

sal in (2000,3000,4000);

e.g.:-

using like character for comparing the 1st position of a value.

➢ select ename from emp

where

ename like ‘s%’;

It displays the employee names which are begins with the letter ‘s’.

➢ select ename from emp

where

ename like ‘%s’

It displays the employee names which are ended with the letter ‘s’.

➢ select ename from emp

where

ename like ‘%s%’

It displays the employee names which contain the letter ‘s’ at any position.

➢ select ename from emp

where

e_name like ‘_s%’

It displays the employee names which contain the letter ‘s’ at the 2nd position.

Example of ‘is null’:-

select from emp

where

sal is null;

It shows the records from emp table whose salary is null.

LOGICAL OPERATOR:-

|AND |The and operator is used on that case where if all the conditions are |

| |true then the result is true, which means e end operator returns true |

| |value. It is denoted by the keyword AND. |

|OR |OR operator returns true value if either component condition is true. |

|NOT |It returns true value if the condition is false. |

Example of AND:-

select * from emp

where

sal > 20000 AND ename like ‘A%’;

Example of OR:-

select * from emp

where

sal > 20000 OR ename like ‘A%’;

Example of NOT:-

select * from emp

where

job ‘MANAGER’;

select * from emp

where

job NOT in(‘MANAGER’,’SALESMEN’,’CLERK’);

ORDER BY CLAUSE:-

The order by clause is used to sort the records or rows with a particular column.

Syntax:-

select from

order by ;

E.g.-

select * from emp

order by sal;

In order by clause we are using two keywords after the column name for sorting in ascending or descending order. The keywords are ‘asc’, ‘dsc’.

e.g.-

select * from emp

order by sal desc;

COLUMN ALIAS:-

Column alias are nothing but some temporary names given to columns during the execution of a querry. This name has to be link with the data base.

Syntax:-

select [column alias] from ;

e.g.-

select sal salary from emp;

CREATION OF A DUPLICATE TABLE:-

Syntax:-

Create table

(column name1,column name2…….)

as select from ;

e.g.-

create table my table

(EMPNO,NAME,JOB,MGR,HIREDATE,SAL,COM,DEPTNO)

as select * from emp;

e.g.:-

create table no

as

select * from friends;

DELETE OPERATION:-

For deleting a row(s) from a table the ‘delete’ statement is used. The syntax is:-

delete from [condition];

e.g.:-

delete from emp

where

emp_no = 7369;

UPDATING THE CONTANTS A TABLE:-

To update or change or to modify the data values of a table the ‘update’ statement is used. Here along with the update statement a ‘set’ operator is used to assign some new value.

Syntax:-

update

set = new value [condition];

e.g.:-

update emp

set sal = 5000

where sal = 8000;

e.g.:-

update emp

set sal = 5000

where ename = ‘KING’;

COMMIT AND ROLLBACK:-

The ‘commit’ statement is used for saving or making permanent to the temporary tranjections. We use the commit statement after any tranjection.

Syntax:- commit;

The ‘rollback’ statement is used to cancel the current tranjections made by the user. But after the commit statement the rollback statement will not work. We can’t also use rollback statement after the DLL operations.

Syntax:- rollback;

MODIFYING THE SRUCTURE OF A TABLE:-

To modify the structure of a table like changing of the column data type or size, deleting some columns, adding some new columns etc, we use the ‘alter’ statement.

(1) ADDING NEW COLUMNS:-

Syntax:-

alter table

add(new column name dataype (size)……);

e.g.:-

alter table student

add(sec char(2),age number(3));

(2) MODIFYING AN EXISTING COLUMN:-

Syntax:- alter table

modify(old column name datatype(size),……);

e.g.:-

alter table student

modify(student name varchar2(30));

(3) RENAMING A TABLE:-

To renaming a table the ‘rename’ statement is used.

Syntax:-

rename to ;

e.g.:-

rename emp to employee;

(4) TRUNCATING A TABLE:-

The ‘truncate’ statement is used to delete all the records of a table at a time.

Syntax:-

truncate table ;

e.g.:-

truncate table student;

Attention: this statement has no rollback option.

(5) DESTROYING A TABLE:-

To destroy or delete a table completely from the data base the ‘drop’ statement is used.

Syntax-

drop table ;

e.g.-

drop table student;

(6) DESROYING A COLUMN FROM A TABLE:-

Syntax:-

alter table

drop column ;

e.g.-

alter table emp

drop column ph_no;

[pic]

The rules which are enforce all data being entered and prevent the user entering invalid data into tables are called constraints.

Oracle permits data constraints to be attached to table and columns via SQL syntax that will check data for integrity.

Types of data constraints:-

• input/output constraints (primary key, foreign key, not null, unique)

• business rule constraints (check constraints, default constraints)

Oracle allows the programmers to define constraints at column and table level. In column level constraints the constraints are given along with the column definition while creating or altering the table.

In table level constraints the constraints are given after defining all the columns in create table or alter table statement.

NOT NULL CONSTRAINTS:-

The not null constraint is used for prohibiting the column(s) from null value entry, which means if a column is defined as not null then it is mandatory to insert any value into that column.

Syntax:-

create table

(column name1 data type [size] not null,

column name2 data type [size] not null,……..);

egg.-

create table salary

(name varchar2(25) not null,

salary number(5) not null);

UNIQUE CONSTRAINTS:-

The unique constraint is used for prohibiting the duplicate value entry into a column.

Syntax:-

create table

(column name1 data type [size],

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

constraint

unique (column name));

Where all the column name after the unique keyword is that column on which the unique constraint will act.

e.g.-

create table salary

(empno number(4),

sal number(5),

deptno number(4),

name varchar2(25),

constraint xyz

unique (empno));

PRIMARY KEY CONSTRAINTS:-

A primary key constraint is used for prohibiting the columns from null value as well as duplicate value entry which means it will work like a combination of unique and not null constraints.

Syntax:-

create table

(column name1 data type [size],

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

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

constraint

primary key (column name));

e.g.-

create table salary

(empno number(4),

sal number(5),

deptno number(4),

name varchar2(25),

constraint xyz

primary key (empno));

FOREIGN KEY CONSTRAINTS:-

Foreign key constraint is used to create a relationship between the primary key or a unique key in the same table or a different table. Foreign keys are based on data values which are purely logical not physical pointer. Normally a foreign key is defined in the child table and the table is referenced to the column of the parent table. The default behavior of the foreign key can be changed by using an undelete cascade option. When an undelete cascade option is specified on the foreign key definition, if the user deletes a record in the parent table all the corresponding records in the child table along with the parent table will be deleted.

E.g.:-

Create table emp1

(name varchar2(12), salary number(5), deptno number(4),

Constraint XYZ

Foreign key (deptno)(emp1

References emp(deptno));(emp

CHECK CONSTRAINTS:-

The check constraint is mainly used for inserting value into a table after satisfying the condition applied by the check constraint.

Syntax:-

create table

(column name1 data type,

………………………………..

………………………………..

constraint

check (condition);

e.g.-

create table student

(name varchar2(25),

mark number(4),

constraint xyz

check(mark >=150));

N:B-

The temporary table that provided by oracle is the dual table, which is always inactive and has a single row and a single column.

DROPPING A CONSTRAINT:-

A drop constraint statement is used for dropping a constraint completely.

Syntax:-

drop constraint ;

TO SHOW THE CONSTRAINTS:-

The querry to show all the constraints names and types is:-

select constraint_name, constraint_type

from user_constraints

where table_name= ‘TABLE NAME’;

DISABLING A CONSTRAINT:-

To disable a constraint without dropping the disable constraint statement is used.

Syntax:-

alter table

disable constraint ;

e.g.:-

alter table emp1

disable constraint XYZ;

ENABLING A CONSTRAINT:-

To enable a disabled constraint the enable constraint statement is used.

Syntax:-

alter table

enable constraint ;

e.g.:-

alter table emp1

enable constraint XYZ;

[pic]

Normally querry is a sentence in oracle for retrival of a record. Always the records are retrive from a table by using some conditions containing some column values, but if instead of the column value, any querry is inserted on that particular place then that querry is called sub query.

It is also termed as nested query. The statement containing a sub query is called a parent statement.

The parent statement uses the rows returned by the sub query. Always the sub querry is written with in a bracket (()).

It can be used by the following commands:-

• to insert records in the target table

• to create tables and insert records into the table created.

• To update records in a target table.

• To create views.

• To provide values for conditions in where, having, in etc.

e.g.:-

select * from emp

where sal > (select sal from emp where ename = ‘KING’);

Here the main querry is used for retriving the records from emp table whose salary is greater than the salary of king. But for getting the salary of king we are using another querry after the > symbol of the main querry, which is called a sub querry.

Questions:-

• WAQ to find out the records from emp table where the jobs of the employees are equal to the job of those employees where the empno contains 7369.

• WAQ to find out the name and deptno of those employees whose deptno is equal to the deptno of ‘smith’.

[pic]

A function is a block of statements executed by a particular name called function name for performing a particular operation. A function may or may not take values as parameter or argument.

In oracle the functions are classified into two types:-

• Single row function.

• Multi row function.

a) single row functions:-

The single row functions are the function which is act upon a single row or single record and returns single values.

b) multi row functions:-

It is the function which is act upon multiple rows or a group of rows and returns a single value. These functions are otherwise called as group functions.

SINGLE ROW FUNCTIONS:-

On the basis of the type of data values the single row function is again of different types like:-

a) Character function

b) Number function

c) Convertion function

d) Date function

e) General function

Character function:-

The single row character function accepts character data as input and can return both character and numeric values. The character function is of two types:-

i) Case manipulation function.

ii) Character manipulation function.

Case manipulation function:-

The lower, upper and initcap (initial capital) are the three case convertion functions.

Lower : Converts mixed case or upper case character string to lower case.

Upper : Converts mixed case or lower case character string to upper case.

Initcap : Converts the first letter of the each word to upper case and the remaining letter to lower case.

E: g:-

select lower (ename) from emp;

select upper (ename) from emp;

select initcap (ename) from emp;

Dual table:-

Dual is a temporary table given by the oracle which contains only a single data value. Always the dual table is activated during the execution of the querry and after the completion of the execution it will again deactivated. Always the dual table can have the capacity to contain only a single value since it has only a single row and a single column.

E: g:-

select lower (‘NABIN’) from dual;

similarly it will act in upper and initcap.

Character manipulation function:-

Concat : This function combines two values together.

E: g:-

select concat (‘xyz’,’abc’)

from dual;

select concat (ename, sal) from emp;

Length : This function shows the length of a string as a numeric value.

E: g:-

select length (‘NABIN’) from dual;

select length (ename) from dual;

Instr : This function finds numeric position of a named character.

E: g:-

select instr (‘good morning’,’r’) from dual;

select instr (ename, ‘K’) from emp;

Subinstr : This function extracts a sub string from a string of specified length and specified position.

Syntax:-

substr (string, beginning pos, no of character);

E: g:-

select substr (‘good morning’, 1, 5) from dual;

select substr (ename, 3) from emp;

Trim : This function trims or deletes the heading character from a character string.

E: g:-

select trim (‘G’ from ‘good morning’) from dual;

select trim (‘K’ from ename) from emp;

Lpad/rpad : These two functions pads a character value right justify and left justify.

E: g:-

select lpad (‘NABIN’,5 ,‘@’) from dual;---(NABIN@@@@@

select lpad (ename, 10, ‘@’) from emp;(ename@@@@@@@@@@

Number function:-

Abs : This function will return the absolute value of a numeric value.

E:g:-

select abs (-2) from dual;(2

select abs (sal) from emp;

Power : This function will returns the power value of a number.

E:g:-

select power (2,3) from dual;(8

Round : This function rounds a value up to a specified decimal place.

E:g:-

select round (45.926,2) from dual;(45.93

Trunk : This function truncates a value into specified decimal.

E:g:-

select trunk (45,926,2) from dual;(45,92

Mod : It returns the reminder of a division.

E:g:-

select mod (64,8) from dual;(8

Sqrt : It returns the square root of a given number

E:g:-

select sqrt (36) from dual;(6

Date and time function:-

Sysdate :System date is returns the current data base server date.

E:g:-

select sysdate from dual;

Months between : This function returns the number of months between two dates.

E:g:-

select months between

(’01-sep-07’,’01-sep-05’)

from dual; (12.

Add_months : This function returns a date after adding the specified month to added.

E:g:-

select add_months

(’18-aug-2007’,2)

from dual; (’18-oct-2007’

Next_day : This function returns the next day of a specified date.

E:g:-

select next_day (’01-sep-95’,’monday’)

from dual; (’04-sep-95’

Last_day : This function returns the last date of the month of specified date.

E:g:-

select last_day

(’01-feb-2000’)

form dual;

Questions:-

• WAQ to find out the age of a student at present.(hint:-DOB-SYSDATE)

• WAQ to find out the months of an employee from the hiring date to the current date.

• WAQ to display the ename and no. of weeks, that the employee worked from the hire date to the current date of emp.

GENERAL FUNCTIONS:-

Nvl : This function converts a null value to an actual value.

Syntax:-

select nvl

(column name, value)

from ;

E:g:-

select nvl

(comm.,0) from emp;

Nvl2 : This function is also like nvl but here it takes three expressions. If the first expression contains null then it returns the third expression, else it returns the second expression.

E:g:-

select ename,sal,comm.,

nvl2(comm,’sal+comm’,’sal’) result

from emp;

output:-

|ename | sal | comm | result |

| EM |5000 | 1 | sal+comm |

| SU |2000 | 2 | sal+comm. |

| PR |3000 | | sal |

Nullif : The nullif function compares two expressions, if they are equal it returns null, if they are not equal the function returns the first expression.

E.g.:-

select ename,

nullif(length(ename),length(job)) result

from emp;

Case

Expression : The case expression will be use as like the if….then….else logic in SQL statements,

here in case expression we are using keywords (when, then) for performing the operation of if and else.

Syntax:-

case expression when comparison expression1

then return expression1

when comparison expression2

then return expression2

…………………………………

…………………………………

else expression

end;

e.g.:-

select ename, job, sal,

case job((the column on which the will act)

when ‘manager’ then sal+1000

when ‘clerk’ then sal+500

when ‘peon’ then sal+100

else sal((default column)

end;

DECODE FUNCTION:-

Decode is also performs the same work as the case expression. It only differs in structural view.

Syntax:-

decode(col/exp,search1,result1,

search2,result2,

………………………..

………………………..

default);

E:g:-

select ename, job, sal,

decode(job, ‘manager’, sal+1000,

‘clerk’, sal+500,

‘peon’ sal+100,

sal)

from emp;

MULTIROW FUNCION or GROUP FUNCTION:-

Unlike single row function, group function operates on set of rows to give one result per group. This set may be the whole table or the table split into groups.

Types of group functions:- (1) Avg ,(2) count , (3) max ,(4) min , (5) stddev , (6)sum , (7) variance.

(1) AVG FUNCTION/SUM FUNCTION:-

The avg and sum functions are act upon numeric column data values. It will return the summation value as well as the average value of the column.

E:g:-

select avg(sal)

from emp;

select sum(sal)

from emp;

(2) MIN/MAX FUNCTION:-

These functions are used for displaying the most junior and most senior record of a table. It will act upon any type of data.

E:g:-

select min(sal)/min(ename)

from emp;

select max(sal)/max(ename)

from emp;

(3) COUNT FUNCTION:-

This function returns no. of rows in a table that satisfies the criteria of select statement.

E:g:-

select count(*) from emp

where

deptno=10;

Questions:-

• WAQ to display the number of records whose 1st letter of the ename starts with ‘S’ Ans:-

Select count(*) from emp

Where ename like ‘S%’;

USING DISTINCT KEYWORD:-

The distinct keyword is used for returning the unique column values of a table.

E:g:-

select count(distinct.deptno)

from emp;

GROUP BY CLAUSE:-

We are using the group by clause to divide the rows in a table into groups. Normally this group by clause is used upon any group function.

E:g:-

select deptno, avg(sal)

from emp

group by deptno;

HAVING CLAUSE:-

The having clause is used to specify which group is to be displayed on the basis of aggregate function.

E:g:-

select deptno, max(sal)

from emp

group by deptno

having max(sal)>3000;

[pic]

Join is the method for extracting the data from more than one table with in the single querry. The join requires a condition for extraction of the data which is called join condition. Usually the primary key and foreign key columns are used to joint the rows of the table with another.

NOTE:-

We are writing a select statement that joints the tables precede the column name with the table name for clarity and enhancement of data access.

Syntax:-

select table1.colum1n, table2.column2

from table1,table2

where table1.column1=table2.column2;

TYPES OF JOIN:-

(a) EQUI JOIN:- It is also called simple join or inner join because in such type of joining a (=) symbol is used with in the join condition. Here the data/rows abstracted from more then one tables by comparing the equal values of columns.

E:g:-

select emp.empno, emp.sal, emp.ename

dept.loc, dept.dname

from emp, dept

where emp.deptno = dept.deptno;

(b) NON-EQUI JOIN:- If with in the join condition any other operator is used except equal to operator then such type of join is called as non-equi join.

E:g:-

select emp.empno, emp.sal, emp.ename

dept.loc, dept.dname

from emp, dept

where emp.deptno = dept.deptno;

select emp.empno, emp.sal, salgrade.sal

from emp, salgrade

where emp.sal

between salgrade.lowsal

and salgrade.highsal;

(c) OUTER JOIN:- The outer join is used for extracting the common data from the tables along with the extra data of the tables which don’t satisfy the join condition. Here a ‘+’ sign enclosed with in parenthesis used for extracting the extra data.

E.g.:-

select emp.ename, emp.deptno, dept.loc

from emp,dept

where emp.deptno=dept.deptno(+);(here + symbol shows the extra

data of emp table.

select emp.ename, emp.deptno, dept.loc

from emp,dept

where emp.deptno(+)=dept.deptno;(here + symbol shows the extra data of dept table.

I. Left outer join:- this join is used for extracting the extra data of a table which is present at the left of the condition

E.g.:-

select e.ename , e.deptno, d.loc

from emp e

left outer join dept d

on(e.deptno=d.deptno);

II. Right outer join :- this join is used for extracting the extra data of a table which is present at the left of the condition.

E.g.:-

select e.ename , e.deptno, d.loc

from emp e

right outer join dept d

on(e.deptno=d.deptno);

III. Full outer join:- by using the full outer join we can extract the common rows of both the tables as well as extra data of both the tables.

e.g.:-

select e.ename, e.deptno, d.loc

from emp e

full outer join dept d

on(e.deptno=d.deptno);

(d) SELF JOIN:- the self join is used for extracting the data from a common table by comparing its two or more than two columns. So here the comparison is made within a single table by using the table alias.

E.g.:-

select emp.empno, emp.sal, e1.mgr, e1.deptno

from emp e, emp e1

where e.deptno=e1.deptno;

or where e.empno=e1.mgr;

(e) CROSS JOIN:- the cross join clause is used in the cross join concept which produces the cross product of two tables.

E.g.:-

select ename ,dname

from emp

cross join dept;

(f) NATURAL JOIN:- it was not possible to do a join with out explicitly specifying the columns in the corresponding tables in prior release of oracle. In oracle 9i it is possible to let the join be completed automatically based on columns in the two tables, which has matching data type and names using the keyword “NATURAL JOIN”.

e.g.:-

select deptno, sal, empno, loc

from emp

natural join dept;

NOTE:- Here the join can happen only columns having same name and data type in both the tables. If the columns have the same name but different data type then the natural join syntax causes error.

(g) JOIN WITH “USING” CLAUSE:-

select e.empno,e.ename,d.loc

from emp e join dept d

using (deptno);

Here e & d are the column alias of emp & dept tables.

(h) JOIN WITH “ON” CLAUSE:-

select e.empno, e.ename, d.loc

from emp e join dept d

on(e.deptno=d.deptno);

[pic]

View is a logical table based on another table or view whose main work is to act as a subset

of a view or table. A view contains no of data of its own but it is like a window through data from the table can be viewed or changed. The table on which a view is based is called base table the view is stored as a select statement in data dictionary.

Advantages:-

(i) View restricts access to data because the view can display selective column from the table.

(ii) View can be used to make simple querries to retribe the results of complicated querries.

(iii)View provides data independency for adhoc user and application program. One view can be used to retrieve the data from multiple tables.

TYPES OF VIEWS:-

Based upon the DML operations(insert, delete, and update) views are classified into two types

i. Simple view

ii. Complex view

Characterstics of simple view:-

A simple view is that(

i) Derives data from only one table.

ii) Contains no function / group of data.

iii) Can perform DML operation through the view.

Characterstics of complex view:-

A complex view is that(

i) Derives data from many table.

ii) Contains functions / group of data.

iii) Doesn’t always allows DML operations through the view.

CREATING A VIEW:-

Syntax:-

create[or replace] view

as ;

e.g.:-

create or replace emp1

as

select empno, ename, sal from emp;

N:B: here along with the columns all the records are also selected.

Retrieving data from a view:-

Syntax:-

select * from ;

e.g.:-

select * from emp1;

MODIFYING AN EXISTING VIEW:-

Syntax:-

update

set column name=value

where[condition];

e.g.:-

update emp1

set sal=1200

where ename=’KING’;

DROPPING A VIEW:-

Syntax:-

drop view;

e.g.:-

drop view emp1;

[pic]

The set operator combines the result of two or more component querry into one result. Querries containing set operator are called compound querry.

|OPERATOR |RETURN |

| | |

|UNION |All distinct rows selected by either querry |

| |All rows selected by either querry including all duplicates. |

|UNION ALL | |

| | |

|INTERSECT |All distinct rows selected by both the querries. |

| |All distinct rows that are selected by first select statement and not |

|MINUS |selected in the second select statement. |

UNION OPERATOR:-

The union operator is used to return all the rows from multiple tables and eliminate the duplicate rows.

N:B:-

• The number of columns and data types of the columns being selected must be identical in all the select statement used in the querry. The name of the columns needn’t be identical.

• Union operates over all the column being selected.

• Null values are not ignored during duplicate checking.

• The in operator has a higher precedence than the union operator.

e.g.:-

select deptno, ename from emp

union

select deptno, loc from dept ;

UNION ALL OPERATOR:-

it displays all the rows including duplicate rows.

e.g.:-

select deptno, ename from emp

union all

select deptno, loc from dept ;

INTERSECT OPERATOR:-

it is used to return all the rows, common to multiple querry.

N:B:-

• The number of columns and data types of the columns being selected must be identical in all the select statement used in the querry. The name of the columns needn’t be identical.

• Reversing the order of the intersected tables doesn’t alter the result.

• Intersect doesn’t ignore null values.

e.g.:-

select deptno, ename from emp

intersect

select deptno, ename from dept ;

MINUS OPERATOR:-

N:B:-

• The number of columns and data types of the columns being selected must be identical in all the select statement used in the querry. The name of the columns needn’t be identical.

• All the columns in the where clause must be in the select clause for the minus operator to work.

e.g.:-

select deptno, loc from dept

minus

select deptno, job from emp ;

[pic]

A sequence is a user created data base object can be shared by multiple users to generate unique integer.

A typical usage for sequence is to create a primary key value, which must be unique for each row. The sequence is generated and incremented (or decremented) by an internal oracle routine. This is a time saving object because it can reduce the amount of application code needed to write a sequence generating routine. Sequence number are stored and generated independently of tables. Therefore the same sequence can be used for multiple tables.

Syntax for creating a sequence:-

Create sequence

Increment by n

Start with n

Maxvalue n

Minvalue n

Cycle/nocycle

Cache/nocache ;

Where n is the integer value

‘Increment by’ is used for the increment value.

‘start with’ is used for the starting value.

‘maxvalue’ is used for the least value of the sequence.

‘minvalue’ is used for the beginning value of the sequence.

‘cycle’ is used to restart the sequence from the minvalue again.

‘cache’ specifies the memory allocation for the values by oracle server.

E.g.:-

create sequence ABC

incremented by 1

start with 1

maxvalue 100

minvalue 1

nocache

nocycle ;

TO VIEW THE STRUCTURE OF THE SEQUENCE:-

select * from user_sequences ;

INSERTING THE SEQUENCE INTO THE TABLE:-

Syntax:-

insert into

values(.nextval,column1, colmn2……);

e.g.:-

insert into friends

values(ABC.nextval,&name…..);

TO VIEW THE CURRENT VALUE OF SEQUENCE:-

syntax:-

select .currval

from dual ;

e.g.:-

select ABC.currval

from dual ;

ALTERING/MODIFYING A SEQUENCE:-

To change the increment value, maxvalue, minvalue, cycle & cache option the alter statement is used.

e.g.:-

alter sequence ABC

increment by 1

maxvalue 1000

nocache

nocycle ;

REMOVING A SEQUENCE:-

syntax:-

drop sequence ;

e.g.:-

drop sequence ABC ;

[pic]

To refer a table owned by another user with another name the synonym is used. This method is generally useful for lengthy object name. A synonym can be created for all the object in oracle.

Syntax:-

create synonym

for object name ;

e.g.:-

create synonym XYZ

for emp ;

DROPPING A SYNONYM:-

drop synonym XYZ;

[pic]

An oracle server index is a schema object that can speed up the retrieval of rows by using a pointer. Index can be created explicitly or automatically. If we don’t have an index on a column then a few table scan occurs.

An index provides direct and fast access of rows in a table. Its purpose is to reduce the necessity of the disk i/o by using an indexed path to locate data quickly. The index is used and maintained automatically by the oracle server. Once an index is created, no direct activity is required by the user.

N:B:- If an index is given to a particular column of a table, then if the table is dropped, then the corresponding index is also dropped.

CREATING AN INDEX:-

Syntax:-

create index

on

(column1, column2,………);

e.g.:-

create index NTP

on emp1

(ename, sal);

DROPPING AN INDEX:-

drop index

e.g.:-

drop index NTP;

TO VIEW THE DETAILS OF AN OBJECT:-

select * from user_objects

where

object_type=’OBJECT NAME’;

E.g.:-

select * from user_objects

where

object_type=’TABLE’;( shows the details of table objects.

DATE TIME FUNCTION OF 9I:-

In oracle 9i we can include the time zone in our date and time data. As well as it supports the difference of fractional seconds. By using these functions we are familiar with the concept of time zone and Greenwich Mean Time.

When there is noon in Greenwich, England, it is midnight along the International Date Line. The earth is divided into 24 time zones, 1 for each hour of the day. The time along the prime meridian in Greenwich, England is known as Greenwich Mean Time (GMT). GMT is the time standard against which all other time zones in the world are referenced. The meridian line is an imaginary line that runs from North Pole to South Pole.

TZ_OFFSET (TIME ZONE_OFFSET):-

This function returns the time zone offset of the corresponding time zone value.

Syntax:-

select tz_offset (‘TIME ZONE’)

from dual;

E.g.:-

select tz_offset (‘EUROPE/LONDON’)

from dual;

FOR DISPLAY THE VALID TIME ZONE VALUES THE QUERRY IS:-

select * from v$timezones_names;

CURRENT_DATE FUNCTION:-

This function returns the current date and time in the season time zone.

select seasonitmezone, current_date

from dual:

CURRENT_TIMESTAMP:-

This function returns the current date and time in the season time zone along with the fractional time.

Syntax:-

select current_timestamp

from dual;

DBTIMEZONE:- It displays the value of data base time zone.

SEASONTIMEZONE:- It displays the value of the season time zone.

select dbtimezone/seasontimezone

from dual;

EXTRACT FUNCTION:-

The extract function is used to extract the year or month from a date.

EXTRACTING THE YEAR FROM SYS.DATE:-

select extract (year from sys.date)

from dual;

PL/SQL EXAMPLE:

Example1:

DECLARE

/* Declaration of memory variables and constants to be used in the

Execution section.*/

pi constant number(4,2) := 3.14 ;

radius number(5);

area number(14,2);

BEGIN

/* Initialize the radius to 3, since calculations are required

for radius 3 to 7 */

radius := 3;

/* Set a loop so that it fires till the radius value reaches 7 */

WHILE radius = MIN_BAL THEN

UPDATE Client_Master

SET Bal_due = Bal_due + add_amt

WHERE Client_no = mClient_no;

END IF;

END;

Cursor example1:

DECLARE

CURSOR Crsr_Emp IS SELECT Emp_No, Salary FROM Emp

WHERE Dept_Code = 20;

str_Emp_Code Emp.Emp_Code%type;

num_Salary Emp.Salary%type;

BEGIN

OPEN Crsr_Emp;

/* If the cursor is open continue with the data processing else display an appropriate error message */

IF Crsr_Emp%ISOPEN THEN

LOOP

FETCH Crsr_Emp INTO str_Emp_Code, num_Salary;

EXIT WHEN Crsr_Emp%NOTFOUND;

IF Crsr_Emp%FOUND THEN

UPDATE Emp SET Salary = num_Salary + (num_Salary * 0.05)

WHERE Emp_Code = str_Emp_Code;

/* Inserting a record in the Emp_Raise table. */

INSERT INTO Emp_Raise VALUES (str_Emp_Code, sysdate, num_Salary * 0.05);

END IF;

END LOOP;

/* Make the changes permanent. */

COMMIT;

ELSE

dbms_output.put_line ('Unable to open Cursor');

END IF;

CLOSE Crsr_Emp;

END;

Cursor example2:

DECLARE

/* Declaration of a cursor which fetches the records having Dept_Code as 20 */

CURSOR Crsr_Emp IS

SELECT Emp_Code, Salary FROM Emp WHERE Dept_Code = 20;

BEGIN

/* Use of a cursor FOR LOOP. */

FOR Emp_Rec IN Crsr_Emp

LOOP

/* Updating salaries (raise by 5%) and inserting each record into the Emp_Raise table to keep track of the date of change and raise in salary. */

UPDATE Emp SET Salary = Emp_Rec.Salary + (Emp_Rec.Salary * 0.05)

WHERE Emp_Code= Emp_Rec.Emp_Code;

INSERT INTO Emp_Raise

VALUES (Emp_Rec.Emp_Code, sysdate, Emp_Rec.Salary * 0.05);

END LOOP;

COMMIT ;

END;

FUNCTION EXAMPLE:

CREATE FUNCTION f_itemidchk(vitemidno IN number) RETURN number IS

/* Variable that hold data from the item_master table */

dummyitem number(4);

BEGIN

SELECT itemid INTO dummyitem

FROM item_master WHERE itemid = vitemidno ;

/* If the SELECT statement retrieves data, valexists is set to 1. */

RETURN 1;

EXCEPTION

/* If the SELECT statement does not retrieve data, valexists is set to 0 */

WHEN NO_DATA_FOUND THEN

RETURN 0;

END;

Main program:

DECLARE

/* Cursor scantable retrieves all the records of table itemtran */

CURSOR scantable IS

SELECT itemid, quantity, description FROM item_transaction;

/* Variables that hold data from the cursor scantable */

vitemidno item_transaction.itemid%type;

vdescrip item_transaction.description%type;

vquantity item_transaction.quantity%type;

/* Variable that stores the value returned by the f_itemidchk function i.e. 1 or 0 */

valexists number(1);

BEGIN

OPEN scantable;

LOOP

FETCH scantable INTO vitemidno, vquantity,vdescrip;

EXIT WHEN scantable%NOTFOUND;

/* Call function f_itemidchk to check if itemid is present in item_master table */

valexists := f_itemidchk(vitemidno);

/* If itemid does not exist insert a record in the item_master table */

IF valexists = 0 THEN

INSERT INTO item_master(itemid, description, bal_stock)

VALUES (vitemidno, vdescrip, vquantity);

/* If the record is found then update quantity in the item_master table */

ELSIF valexists = 1 THEN

UPDATE item_master

SET bal_stock = bal_stock + vquantity

WHERE itemid = vitemidno;

END IF;

END LOOP;

CLOSE scantable;

COMMIT;

END;

Procedure example:

CREATE OR REPLACE PROCEDURE proc_update(vproductno IN char, vsorderno IN char, quantity IN number) IS

/* Variable declarations */

total_qty_ordered number(8);

total_qty_disp number(8);

BEGIN

/* Updating the qty_on_hand in the product_master table*/

UPDATE Product_Master

SET qty_on_hand = qty_on_hand - quantity

WHERE product_no = vproductno;

/* Checking in the sales_order_details table the total quantity ordered and the total quantity dispatched for a certain sales_order and stuffing the values into memory variables*/

SELECT sum(qty_ordered), sum(qty_disp)

INTO total_qty_ordered, total_qty_disp

FROM Sales_Order_Details

WHERE detlorder_no = vsorderno;

/* Comparing the total quantity ordered with the total quantity dispatched and updating the order_status in the sales_order table*/

IF total_qty_ordered = total_qty_disp THEN

UPDATE Sales_Order

SET order_status = 'Fulfilled'

WHERE order_no = vsorderno;

ELSIF total_qty_disp = 0 THEN

UPDATE Sales_Order

SET order_status = 'Backorder'

WHERE order_no = vsorderno;

ELSE

UPDATE Sales_Order

SET order_status = 'In Process'

WHERE order_no = vsorderno;

END IF;

END;

Main program:

DECLARE

/* Cursor c_mast_check retrieves all the records of table Challan_header */

CURSOR c_mast_check IS

SELECT challan_no, order_no FROM Challan_Header;

/* Declaration of memory variables that will hold values */

vproductno varchar2 (6);

vsorderno varchar2(6);

quantity number (3);

vmastchallan varchar2(6);

vdetlchallan varchar2(6);

BEGIN

/* Accepting values for product_no, quantity_dispatched and challan_no from the user and stuffing them in memory variables */

vproductno := '&vproductno';

quantity := &quantity;

vdetlchallan :='&vdetlchallan';

OPEN c_mast_check;

LOOP

FETCH c_mast_check INTO vmastchallan, vsorderno;

EXIT WHEN c_mast_check %NOTFOUND;

IF vdetlchallan =vmastchallan THEN

INSERT INTO challan_details

VALUES ( vdetlchallan,vproductno,quantity);

/* Call procedure proc_update to update sales_order and product_master tables.*/

proc_update (vproductno, vsorderno, quantity);

EXIT;

END IF;

END LOOP;

IF c_mast_check %NOTFOUND THEN

DBMS_OUTPUT.PUT_LINE ('The given

challan_no does not have a master record');

END IF;

CLOSE c_mast_check;

COMMIT;

END;

PACKAGE:

Example 1:

CREATE OR REPLACE PACKAGE myPackage AS

FUNCTION f_Client_no_chk(vClientno IN varchar2) RETURN number;

END myPackage;

/* Package body */

CREATE OR REPLACE PACKAGE BODY myPackage IS

FUNCTION f_Client_no_chk(vClientno IN varchar2)

RETURN number IS dummyClient varchar2(6);

BEGIN

SELECT client_no INTO dummyclient

FROM Client_Master WHERE client_no = vClientno;

DBMS_OUTPUT.PUT_LINE('Valid Client');

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Invalid Client');

END;

END myPackage;

CREATE OR REPLACE PACKAGE BODY salesman_mgmt AS

PROCEDURE hire (name varchar2, address1 varchar2, sal number,

tgt_to_get number, ytd_sales number,rate_of_commission number) IS

new_sman_no varchar2(6);

BEGIN

SELECT salesmanseq.nextval INTO new_sman_no

FROM DUAL;

INSERT INTO salesman_master (salesman_no, salesman_name,

address1, tgt_to_get, ytd_sales, sal_amt,

rate_of_commission)

VALUES (new_sman_no, name, address1, sal,

tgt_to_get, ytd_sales, rate_of_commission);

END;

PROCEDURE increase_sal (sman_no varchar2, sal_incr number) IS

curr_sal number(8,2);

BEGIN

SELECT sal_amt into curr_sal FROM salesman_master

WHERE salesman_master.salesman_no = sman_no;

UPDATE salesman_master

SET sal_amt = (curr_sal + ( sal_incr * curr_sal / 100))

WHERE salesman_master.salesman_no = sman_no;

INSERT INTO emp_raise VALUES

(sman_no, sysdate, (sal_incr * curr_sal / 100));

END;

END salesman_mgmt ;

Save point example:

DECLARE

Total_Sal number(9);

BEGIN

/* Insertion of a record in the 'Emp' table */

INSERT INTO Emp VALUES('E006', 'Ajay', 10, 5400);

/* Defining a savepoint */

SAVEPOINT no_update;

/* Updating the salaries of Mamta and Chhaya in the 'Emp' table. */

UPDATE Emp SET Salary = Salary + 1600 WHERE Emp_Name = 'Mamta';

UPDATE Emp SET Salary = Salary + 1500 WHERE Emp_Name = 'Chhaya';

/* Storing the total salary from the 'Emp' table into a variable. */

SELECT sum(Salary) INTO Total_Sal FROM Emp;

/* Checking if total salary exceeds 35000. */

IF Total_Sal > 35000 THEN

/* Undo the changes made to the 'Emp' table. */

ROLLBACK To SAVEPOINT no_update;

END IF;

/* Make the changes permanent. */

COMMIT;

END;

Trigger example1:

CREATE TRIGGER audit_trail

AFTER UPDATE OR DELETE ON Client_Master

FOR EACH ROW

DECLARE

/* The value in the oper variable will be inserted into the operation field in the auditclient table */

oper varchar2(8);

/* These variables will hold the previous values of client_no, name and bal_due */

client_no varchar2(6);

name varchar2(20);

bal_due number(10, 2);

BEGIN

/* if the records are updated in Client_Master table then oper is set to 'update'.*/

IF updating THEN

oper := 'update';

END IF;

/* if the records are deleted in Client_Master table then oper is set to 'delete' */

IF deleting THEN

oper := 'delete';

END IF;

/* Store :old.client_no, :old.name, and :old.bal_due into client_no, name and bal_due. These variables can then be used to insert data into the auditclient table */

client_no := :old.client_no ;

name := :old.name ;

bal_due := :old.bal_due ;

INSERT INTO auditclient

VALUES (client_no, name, bal_due, oper, user, sysdate);

END;

Trigger example2:

CREATE OR REPLACE TRIGGER client_no_generation

BEFORE INSERT

ON client_master

FOR EACH ROW

DECLARE

primary_key_value varchar2(5);

BEGIN

SELECT lpad(to_char(client_seq.nextval) , 5 , '0')

INTO primary_key_value FROM dual;

:new.client_no := 'C' || primary_key_value;

END;

Trigger example3:

CREATE OR REPLACE TRIGGER product_no_generation

BEFORE INSERT

ON Product_Master

FOR EACH ROW

DECLARE

max_pkey_value varchar2(5);

new_pkey_value varchar2(5);

BEGIN

SELECT nvl(substr(max(product_no),2,5), 0)

INTO max_pkey_value FROM product_master;

new_pkey_value := lpad(to_char((to_number(max_pkey_value) + 1)) , 5 , '0');

:new.product_no := 'P' || new_pkey_value;

END;

Open Database Connectivity (ODBC):

ODBC defines a standard method for communication between the application and the DBMS. This accomplished by defining an Application programming Interface (API). An API in programming terms a set of functions that perform a particular task.

The application will call an API function to specify a particular task. The API call is passed on to the translator. It is the translator that issues command in the DBMS language to specify the requested action.

The translator is referred to as the ODBC driver. It is the driver that implements the various API such that they are understood by the DBMS. Thus there is a driver for each DBMS to be accessed..

The ODBC driver accepts the command from the application and converts it to a format understood by the target DBMS. In addition, the ODBC driver also receives the result of the command execution from the DBMS and passes it back to the application.

[pic]

Option Explicit

Dim c As Integer

Dim cnn1 As ADODB.Connection

Dim CNN2 As ADODB.Connection

Dim cnn3 As ADODB.Connection

Dim con As ADODB.Connection

Dim rs As ADODB.Recordset

Dim rs1 As ADODB.Recordset

Dim RS2 As ADODB.Recordset

Dim rs3 As ADODB.Recordset

Dim addflag As Boolean

Private Sub cmdadd_Click()

cmdfirst.Enabled = True

cmdlast.Enabled = True

cmdnext.Enabled = True

cmdprev.Enabled = True

clear

enab

cmddelete.Visible = False

cmdadd.Visible = False

cmdmodify.Visible = False

cmdcancel.Visible = True

cmdsave.Visible = True

cmdexit.Visible = False

cmdms.Visible = False

cmdsearch.Visible = False

cmdmc.Visible = False

Frame5.Visible = False

FILLCODE

End Sub

Private Sub cmdCancel_Click()

disab

cmddelete.Visible = True

cmdadd.Visible = True

cmdmodify.Visible = True

cmdcancel.Visible = False

cmdsave.Visible = False

cmdexit.Visible = True

cmdexit.Visible = True

cmdsearch.Visible = True

Frame4.Visible = True

clear

End Sub

Private Sub cmddelete_Click()

Dim s1 As String

Dim opt As Integer

Dim rs4 As ADODB.Recordset

Dim opt_value As String

Set cnn3 = New ADODB.Connection

Set rs4 = New ADODB.Recordset

cnn3.Open "Provider=MSDAORA.1;User ID=reena;password=manu;datasource=HSM;"

rs4.Open "vendor", cnn3, adOpenDynamic, adLockOptimistic, adCmdTable

opt = MsgBox("Want to delete it?", vbYesNo, "Confirmation of delete")

If opt = 6 Then

s1 = "delete from vendor where vcode ='" & txtvcode.Text & "'"

cnn3.Execute s1

clear

End If

cnn3.Close

combocn.clear

Form_Load

End Sub

Private Sub cmdexit_Click()

Unload Me

End Sub

Private Sub cmdfirst_Click()

rs.MoveFirst

display

End Sub

Private Sub cmdlast_Click()

rs.MoveLast

display

End Sub

Private Sub cmdmc_Click()

disab

cmddelete.Visible = True

cmdadd.Visible = True

cmdmodify.Visible = True

cmdmc.Visible = False

cmdms.Visible = False

cmdsave.Visible = False

cmdexit.Visible = True

cmdexit.Visible = True

cmdsearch.Visible = True

Frame4.Visible = True

End Sub

Private Sub cmdmodify_Click()

enab

Frame4.Visible = False

cmddelete.Visible = False

cmdadd.Visible = False

cmdmodify.Visible = False

cmdcancel.Visible = False

cmdms.Visible = True

cmdmc.Visible = True

cmdexit.Visible = False

cmdsave.Visible = False

cmdsearch.Visible = False

End Sub

Private Sub cmdms_Click()

Dim opt As Integer

Dim cnn3 As ADODB.Connection

Dim s1 As String

enab

Set cnn3 = New ADODB.Connection

Dim rs4 As ADODB.Recordset

Set rs4 = New ADODB.Recordset

cnn3.Open "Provider=MSDAORA.1;User ID=REENA;password=MANU;datasource=HSM;"

rs4.Open "vendor", cnn3, adOpenDynamic, adLockOptimistic, adCmdTable

opt = MsgBox("want to modify it", vbYesNo, "modify confirmation")

If opt = 6 Then

s1 = "update vendor set Vcode = '" & txtvcode.Text & "', VNAME='" & txtvname.Text & "', VTELNO='" & txtvtelno.Text & "',VADDRESS='" & txtvadd.Text & "' where Vcode='" & txtvcode.Text & "'"

cnn3.Execute s1

End If

rs4.Close

cmddelete.Visible = True

cmdadd.Visible = True

cmdmodify.Visible = True

cmdcancel.Visible = False

cmdsave.Visible = False

cmdexit.Visible = True

Frame4.Visible = True

cmdexit.Visible = True

cmdsearch.Visible = True

disab

cnn1.Close

combocn.clear

Form_Load

End Sub

Private Sub cmdnext_Click()

With rs

.MoveNext

If .EOF Then .MoveLast

End With

display

End Sub

Private Sub cmdprev_Click()

With rs

.MovePrevious

If .BOF Then .MoveFirst

End With

display

End Sub

Private Sub cmdsave_Click()

Dim opt_val As String

Dim rd, bd, S As String

Dim DATE1 As String

Set cnn3 = New ADODB.Connection

Set rs3 = New ADODB.Recordset

cnn3.Open "Provider=MSDAORA.1;User ID=REENA;password=MANU;datasource=HSM;"

rs3.Open "vendor", cnn3, adOpenDynamic, adLockOptimistic, adCmdTable

If Len(txtvname.Text) > 0 And Len(txtvadd.Text) > 0 Then

S = "insert into vendor VALUES ('" & txtvcode.Text & "','" & txtvname.Text & "','" & txtvtelno.Text & "','" & txtvadd.Text & "')"

cnn3.Execute S

Else

MsgBox "data is left blank"

End If

combocn.clear

Form_Load

Frame4.Visible = True

cmddelete.Visible = True

cmdadd.Visible = True

cmdmodify.Visible = True

cmdcancel.Visible = False

cmdsave.Visible = False

cmdexit.Visible = True

cmdsearch.Visible = True

disab

End Sub

Private Sub cmdsearch_Click()

Frame5.Visible = True

Set CNN2 = New ADODB.Connection

Set RS2 = New ADODB.Recordset

CNN2.Open "Provider=MSDAORA.1;User ID=reena;password=manu;datasource=HSM;"

RS2.Open "vendor", CNN2, adOpenDynamic, adLockOptimistic, adCmdTable

While Not RS2.EOF

combocn.AddItem RS2.Fields(0)

RS2.MoveNext

Wend

If (combocn.ListCount = 0) Then

MsgBox "No data are present in the data base"

End If

CNN2.Close

End Sub

Private Sub combocn_click()

Dim find_str As String

Dim cnn3 As ADODB.Connection

Dim rsearch As ADODB.Recordset

Set rsearch = New ADODB.Recordset

find_str = "select * from vendor where Vcode = '" & combocn.Text & "' "

Set cnn3 = New ADODB.Connection

cnn3.Open "Provider=MSDAORA.1;User ID=reena;password=manu;datasource=HSM;"

rsearch.Open find_str, cnn3, adOpenDynamic, adLockOptimistic

txtvcode.Text = rsearch.Fields(0)

txtvname.Text = rsearch.Fields(1)

txtvtelno.Text = rsearch.Fields(2)

txtvadd.Text = rsearch.Fields(3)

End Sub

Private Sub Command1_Click()

Set MSHFlexGrid1.DataSource = rs

End Sub

Private Sub Form_Load()

StatusBar1.Panels(2).Text = Format(Date, "dd/mm/yyyy")

StatusBar1.Panels(3).Text = Time()

Set cnn1 = New ADODB.Connection

cnn1.Open "Provider=MSDAORA.1;User ID=reena;password=manu;datasource=HSM;"

Set rs = New ADODB.Recordset

rs.Open "vendor", cnn1, adOpenDynamic, adLockOptimistic, adCmdTable

addflag = False

Frame5.Visible = False

If rs.RecordCount = 0 Then

cmdfirst.Enabled = False

cmdlast.Enabled = False

cmdnext.Enabled = False

cmdprev.Enabled = False

End If

disab

Set MSHFlexGrid1.DataSource = rs

End Sub

Public Sub display()

txtvcode.Text = rs.Fields(0)

txtvname.Text = rs.Fields(1)

txtvtelno.Text = rs.Fields(2)

txtvadd.Text = rs.Fields(3)

End Sub

Private Sub Form_Unload(Cancel As Integer)

cnn1.Close

End Sub

Public Sub disab()

txtvcode.Enabled = False

txtvname.Enabled = False

txtvtelno.Enabled = False

txtvadd.Enabled = False

End Sub

Public Sub enab()

txtvcode.Enabled = True

txtvname.Enabled = True

txtvtelno.Enabled = True

txtvadd.Enabled = True

End Sub

Public Sub clear()

txtvcode.Text = ""

txtvname.Text = ""

txtvtelno.Text = ""

txtvadd.Text = ""

disab

End Sub

Private Sub Timer1_Timer()

StatusBar1.Panels(3).Text = Time

End Sub

Private Sub txtvname_GotFocus()

StatusBar1.Panels(1).Text = "Enter vendor name"

End Sub

Private Sub txtvname_LostFocus()

If txtvname.Text = "" Then

MsgBox "desc can not be left blank"

txtvname.SetFocus

End If

End Sub

Private Sub txtvtelno_gotfocus()

StatusBar1.Panels(1).Text = "Enter vendor telephone no "

End Sub

Private Sub txtvadd_GotFocus()

StatusBar1.Panels(1).Text = "Enter vendor address"

End Sub

Public Sub FILLCODE()

Dim m, MAX As Integer

Set cnn1 = New ADODB.Connection

Set rs1 = New ADODB.Recordset

cnn1.Open "Provider=MSDAORA.1;User ID=reena;password=manu;datasource=HSM;"

rs1.Open "vendor", cnn1, adOpenDynamic, adLockOptimistic

If rs1.EOF = True Then

MAX = 1

Else

rs1.MoveFirst

m = Val(Mid$(rs1.Fields(0), 2))

MAX = m

rs1.MoveNext

While Not rs1.EOF

m = Val(Mid$(rs1.Fields(0), 2))

If MAX < m Then

MAX = m

End If

rs1.MoveNext

Wend

End If

MAX = MAX + 1

txtvcode.Text = "V" + LTrim(Str(MAX))

End Sub

Why JDBC???

• JDBC Vs. ODBC

• JDBC do not use Pointer

• ODBC use C programming language so it uses Pointer

• Design of JDBC and ODBC is different

• JDBC is simple

• ODBC is complex

Introduction to JDBC Interface

• JDBC is a layer of abstraction that allows users to chose between databases

• JDBC allows you to write to a single API

• JDBC allows you to change to a different database engine

• JDBC supports ANSI SQL-92 compatible databases, but can be used on other databases

Types of JDBC Driver

• Type 1(JDBC-ODBC bridge)

• JDBC-ODBC

• Uses ODBC API

• Maps JDBC calls to ODBC driver calls on the client

• Software needs to be install in client side

• Type 2(Native-API partly-Java driver)

• Native API drivers

• Maps JDBC calls to native calls on the client

• driver contains Java code that calls native C / C++ methods provided by individual database vendor

• software needs to be install in client side

• Type 3(Net-protocol all-Java driver)

• Provides a client with a generic network API

• Maps JDBC calls to “network” protocol, which calls native methods on the server

• Client uses socket for any request to server

• flexible,since it requires no code installed on the client

• Type 4(Native-protocol all-Java driver)

• talk directly to the database using Java socket

• Directly calls RDBMS from the client machine

• Direct pure Java solutions

JDBC Interfaces

• java.sql.Driver

• java.sql.Connection

• java.sql.Statement

• java.sql.PreparedStatement

• java.sql.CallableStatement

• java.sql.ResultSet

• java.sql.ResultSetMetaData

• java.sql.DatabaseMetaData

JDBC Steps for Connection

• Import the java.sql package

• Load and register the driver

• Establish a connection to the database server

• Create a statement

• Execute the statement

• Retrive the results

• Close the statement and connection

The JDBC Flow

• A URL string is passed to the getConnection method of the DriverManager, which in turn locates a Driver

• With the Driver, you can obtain a Connection

• With the Connection, you can create a Statement

• When a Statement is executed with an excuteQuery( ) method, a ResultSet can be returned

Java Database Connectivity:

← JDBC is a Java API for executing SQL statements

← it consist of a set of classes and interfaces written in Java programming language

← using JDBC, it is easy to send SQL statements to virtually any relational database

← JDBC is a low-level API and Base for Higher-level APIs

← low-level interface means that it can invoke SQL commands directly

← higher-level interface is “user-friendly” using more convenient API that translate in a low-level interface

← There are two kinds of higher-level API’s under development on top of JDBC

← a direct mapping of relational database tables to Java classes

Why JDBC Over ODBC?

➢ ODBC is not appropriate for direct use from Java because it uses a C interface

➢ A literal translation of the ODBC C API into a Java API would not be desirable

➢ ODBC is hard to learn

➢ JDBC is needed in order to enable a pure ‘Java’ solution

JDBC Model Support

➢ JDBC Supports:

← Two-tier Models : Java applet or application talks direct to the database through JDBC driver

← Three-tier Models : commands are sent to a ‘middle tier’ (implement in Java), which then send SQL statements to the database

Main JDBC Classes

➢ Driver Manager

➢ Connection

➢ Statement and PreparedStatement

➢ Callable Statement

➢ ResultSet

➢ Creating a DataSource with name JAVAPUB

➢ ResultSet Methods

➢ Printing out the Metadata

➢ Accessing Data from an Applet

Driver Manager

➢ JDBC Driver Manager is the backbone of the JDBC architecture

➢ Driver Manager connects Java applications to the correct JDBC driver

➢ JDBC-ODBC Bridge allows ODBC drivers to be used as JDBC drivers

➢ JDBC-Net pure Java driver : translates JDBC calls into a DBMS

➢ Native-protocol pure java driver: This driver connects JDBC calls into the network protocol used by DBMSs directly.

Connection:

➢ A connection object represents a connection with a database

➢ To establish a connection with a database is to call the method DriverManager.getConnection

Database URLs

We need a way to open JDBC connections : For lots of different kinds of database drivers - Where different databases need different syntax - Without requiring human intervention!

The answer seemed obvious : use URLs - It’s the Internet’s flexible naming scheme you can bridge to other names (e.g. ODBC)

Typical names use jdbc : : - e.g. jdbc:odbc:axx - or jdbc : odbcnet://wombat:344/fred-or jdbc:sybase://wombat:344/fred

Statement & PreparedStatement

← Statement allows simple SQL execution

← “executeQuery” can be used for SELECT

← “executeUpdate” can be used for other simple SQL

← “execute”covers the weird cases

← PreparedStatement adds support for IN params

← Through a collection of setXXX methods

← It can be used for complied SQL statements

Callable Statement

← CallableStatement extends PreparedStatement

← For use with stored procedures

← It adds support for OUT parameters

← Unfortunately you have to register OUTs - using “registerOutParameter” Then retrieve the value after call execution

← Using one of the “getXXX” methods.

ResultSet

← ResultSet provides results from A select

← You can iterate over the rows using “next”

← Within a row you can retrieve result columns - using a set of “getXXX” methods - using either column names or column indexes

EXAMPLE:

ResultSet rs = ….while(rs.next())

{ int a = rs.getInt(“a”);

Numeric b = rs.getNumeric(“b”);

String key = rs.getString(3);}

Creating a DataSource with name JAVAPUB

← Test connection class

try{Class.forName(“sun.jdbc.odbcOdbc Driver”)}

← initiative Driver

con=DriverManager.getConnection(“jdbc:odbc: JAVAPUB”, “sa”, “”);

← Query Statement

ResultSet rs=smt.executeQuery(“SELECT*FROM authors;”);

ResultSet Methods

← ResultSet Methods

ResultSet rs = stmt.executeQuery(“select”from employee”);

resultSetMetaData rsmd=rs.getMetadata();

← Retreving Number of Columns

for(int i=1; i ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download