oracle 10g lab simple
DESCRIPTION
Simple Oracle 10g queries and programs.Visual Basic 6.0 Applications with Oracle as Back End.Published as Practical Record, in University College of Applied Sciences (MG University), Pathanamthitta.Hope this will help you..- JerrinTRANSCRIPT
DBMS Lab Oracle 10g
1 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
IntroductionDatabase
A database can be defined as a collection of coherent, meaningful data.
RDBMS
A Data Base Management System or DBMS consists of a collection of interrelated data and aset of programs to access those data. A Relational Database Management System is a program thatlets you create, update and administrator a relational database. The primary rule for RDBMS is thatthe Data should be stored in the form of tables. Most of the RDBMS’s use the Structured QueryLanguage to access the database.
Different products of RDBMS are:
ORACLE Oracle Corporation.SQL Server Microsoft Corporation.DB2 UDB IBMMySQL MySQLSybase SybaseTeradata NCR
We are using ORACLE from Oracle Corp. in this Lab.
ORACLE
In June 1970, Dr. E.F. Codd published a paper entitled A Relational Model of Data for Large SharedData Banks. This relational model, sponsored by IBM, then came to be accepted as the definitivemodel for RDBMS. The language developed by IBM to manipulate the data stored within Codd’smodel was originally called Structured English Query Language (SEQUEL) with word English laterbeing dropped in favor Structured Query Language (SQL).
In 1979, Oracle Corp. released the first commercially available implementation of SQL.
SQL (Structured Query Language)
This is a common language through which we can interact with the database SQL is classifiedmainly into following categories.
1. DDL (Data Definition Language)
CREATE To create objects in the database.
ALTER Alters the structure of the database.
DROP Delete objects from the database.
TRUNCATE Remove all records from a table, including all spaces allocated forthe records are removed.
COMMENT Add comments to the data dictionary.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
2 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
GRANT Gives user’s access privileges to database.
REVOKE Withdraw access privileges given with GRANT command.
2. DML (Data Manipulation Language)
INSERT Insert data into a table.
UPDATE Updates existing data within a table.
DELETE Deletes all records from a table, the space for the records remain.
CALL Call a PL/SQL or Java subprogram.
3. TCL (Transaction Control Language) or DCL (Data Control Language)
COMMIT Save work done.
SAVEPOINT Identify a point in a transaction to which you can later roll back.
ROLLBACK Restore database to original since the last COMMIT
SET TRANSACTION Change transaction options like what rollback segment to use.
GRANT/REVOKE Grant or take back permissions to or from the oracle users.
4. DQL (Data Query Language statement)
SELECT Retrieve data from a database.
Oracle Data Types
Data Type DescriptionCHAR(size) To store character strings values of fixed length. (Max. 255 characters).VARCHAR(size)/VARCHAR2(size)
Store variable length alphanumeric data. It’s a more flexible form of the CHAR type.(Max. 4000 characters). Oracle compares VARCHAR values using non-paddedcomparison semantics i.e. the inserted values will not be padded with spaces. Italso represents data of type String, yet stores this data in variable length format.
DATE To represent date and time. The standard format is DD-MON-YY. Date Time storesdate in the 24-hour format.
NUMBER(P,S) To store numbers (fixed or floating point). Numbers of virtually any magnitude maybe stored up to 38 digits of precision. The precision (P), determines the maximumlength of the data, whereas the scale (S), determines the number of places to theright of the decimal.
LONG Store variable length character strings containing up to 2 GB. Can be used to storearrays of binary data in ASCII format. Only one LONG can be used per table.
RAW/LONG RAW
To store binary data, such as digitized picture or image.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
3 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Constraints
Constraint restricts the values that the table can store. We can declare integrity constraintsat the table level or column level.
There are 5 constraints:
1. Not Null
If a column in a table is specified as Not Null, then it’s not possible to insert a null insuch a column. It can be implemented with create and alter commands.
2. Unique Key
It doesn’t allow duplicate values in a column.
3. Check
Used to restrict the values before inserting into a table.
4. Primary Key
The key column with which we can identify the entire Table is called as a primary keycolumn. A PK is a combination of Unique and Not Null constraint; it will not allow null and duplicatevalues. A table can have only one primary key. A primary key can be declared on two or morecolumns as a Composite Primary Key.
5. Foreign Key
Columns defined as foreign keys refer the Primary Key of other tables. The ForeignKey ‘points’ to a primary key of another table, guaranteeing that you can’t enter data into a tableunless the referenced table has the data already which enforces the REFERENTIAL INTEGRITY.
Data Definition Language Syntax
CREATE
CREATE TABLE <table name> (
<column_name1> <data type>(<size>) [<constraint>],
<column_name2> <data type>(<size>),
.
<column_nameN> <data type>(<size>),
);
PROMOTIONAL COPY
DBMS Lab Oracle 10g
4 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
ALTER
Adding New Columns:
ALTER TABLE <Table Name>
ADD(<New Column Name> <Data type>(<Size>),
<New Column Name> <Data type>(<Size>),…);
Dropping a column from a table:
ALTER TABLE <Table Name> DROP COLUMN <Column Name>;
Modifying Existing Columns:
ALTER TABLE <Table Name>
MODIFY (<Column Name> <New Data type>(<New Size>));
RENAME
RENAME <Table Name> TO <New Table Name>;
TRUNCATE
TRUNCATE TABLE <Table Name>;
DROP
DROP TABLE <Table Name>;
Data Manipulation Language Syntax
INSERT
INSERT INTO <Table Name> (<col_name1>,<col_name2>)
VALUES(<expression1>,<expression2>);
DELETE
Removal of all rows:
DELETE FROM <Table Name>;
Removal of specific row(s):
DELETE FROM <Table Name> WHERE <Condition>;
PROMOTIONAL COPY
DBMS Lab Oracle 10g
5 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
UPDATE
Updating all rows:
UPDATE <Table Name>
SET <Col_name1> = <Exp1>, <Col_name2> = <Exp2>;
Updating Records conditionally:
UPDATE <Table Name> SET <Col_name1> = <Exp1>, <Col_name2> =<Exp2>
WHERE <Condition>;
Data Query Language Syntax
SELECT
Figure: Syntax of Select.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
6 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Transaction Control Language Syntax
COMMIT
COMMIT;
SAVE POINT
SAVEPOINT <Save point name>;
ROLLBACK
ROLLBACK TO <Already defined save point name>;
Aggregate Functions
AVG Returns an average value of ‘n’, ignoring null values in a column
MIN Return a minimum values of expression
COUNT(exp) Return the number of rows where expression is not null
COUNT(*) Returns the no. of rows in the table including duplicate and those with nulls
MAX Returns the maximum values of expression
SUM Return the sum of the values.
Oracle Operator Precedence
Operator Operation
+, - Identity, Negation
*, / Multiplication, Division
+, -, || Addition, Subtraction, Concatenation
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN Comparison
NOT Exponentiation, Logical Negation
AND Conjunction
OR Disjunction
PROMOTIONAL COPY
DBMS Lab Oracle 10g
7 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Sub Queries
A sub query is a query within a query. The results of the sub query are used by the DBMS todetermine the results of the higher-level query that contains the sub query. In the simplest forms ofa sub query, the sub query appears within the WHERE or HAVING clause of another SQL statement.Sub queries provide an efficient, natural way to handle query requests that are themselvesexpressed in terms of the results of other queries.
Pattern Matching
The comparison operator usually compared one value exactly to one another value. Thisprecision may not always we decide or necessary. For this purpose oracle provides like predicate. Itallows comparison of one string value with another string value which is not identical. This isachieved by use of wild card character. The wild card characters available are modulus (%) whichallows to match any strings of any length and (“_”) which allows to match a single character.
Example: Select * from emp where ename like”a%”;
Select * from emp where ename like”_a%”;
Oracle 10g
Oracle Database 10g is revolutionary. It is Oracle’s largest introduction of new functionality ever,beginning with Release 1 of Oracle Database 10g in January 2004 and now Release 2 in 2005. It is Oracle’smost innovative release, leading the database industry into entirely new ground in clustering,automation, high availability, and more. And, it is having the largest impact on computing of any databasesoftware release. Oracle Database 10g fundamentally changes the way data centers look and operate
Oracle Database 10g transforms data centers from collections of separate, monolithic systems configuredto handle individual applications to a smaller number of consolidated, shared pools of server and storageresources called infrastructure grids. Oracle’s unique scale-out architecture enables applications of alltypes to dynamically provision additional servers and storage resources as needed to meet their changingprocessing demands. It delivers dramatic cost savings and equally dramatic advancements in quality ofservice.
Cost savings come from multiple sources. Oracle reduces hardware costs significantly by enabling eventhe largest systems to be built out of small, low-cost servers and modular storage arrays. Newautomation capabilities in Oracle Database 10g dramatically decrease the cost of databaseadministration. Also, Oracle full stack solutions eliminate the need for costly 3rd party management toolsand utilities.
The advancements in quality of service are across the board. Particularly significant is the automation ofdatabase administration functions that raise usability to a revolutionary new level. Not only are DBAsvastly more productive, they are empowered to deliver new levels of consistently optimizedperformance, high availability, and more. Oracle Database 10g further increases Oracle’s lead in virtuallyevery area of database capability. This includes high availability, performance, business intelligence, datamovement, security, application development, and more.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
8 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Oracle 10g Installation and Configuration
Follow these steps to install and configure Oracle 10g Enterprise Edition.
Run the Installation Setup (Setup.exe). Select Advanced Installation from the Welcome window.
The File Locations window opens.
Change the Destination Name to: OraHome10
Change the Destination Path to c:\oracle\product\10.1.0\OraHome10
Click the Next button.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
9 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Select Personal Edition (804 MB) from the Select Installation Type windows that appears. Click Next
The Select Database Configuration window opens. Select General Purpose and click Next.PROMOTIONAL COPY
DBMS Lab Oracle 10g
10 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
The Specify Database Configuration Options window opens. Enter:Global Database Name: DB1SID: DB1
Select Create database with samples schemas and click Next.
The Select Database Management Option window opens; simply click Next. The Specify Database File Storage Option window opens. Keep the default or select a
different directory for your database files and click Next.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
11 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
The Specify Backup and Recovery Options opens. Keep Default Settings and click Next. Then, the Specify Database Schema Passwords window opens.
Select Use the same password for all the accounts.
In the Enter Password and Confirm Password fields type your password.
Click Next.
The Summary window opens; click Install.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
12 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
The Install opens. The time to install depends upon the speed and memory of yourcomputer and may take more than 45 minutes. Don’t be alarmed if the installer seems tobe stuck at a certain percentage during the installation. You can also ignore additionalwindows that open during installation.
After some time, End of Installation window will appear. Click Exit and confirm exit. Youhave successfully installed Oracle 10g.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
13 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Now, we’ve installed Oracle 10g. Then we can configure the database as our wish by goingto the Database Home Page. We can log in with the SYSTEM account, with username‘system’ and password that we set during installation.
For Example, to unlock the HR user, Select Manage Users.
Click HR user, and provide a password, select Unlocked from Account status and click AlterUser. We can also set storage settings, add users, create tables etc from the Home Page.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
14 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Table Design using Foreign Key and Normalization
Normalization
Normalization is a process that helps analysis or database designers to design table structures for anapplication. The focus of normalization is to attempt to reduce redundant table data to the veryminimum. Through the normalization process, the collection of data in a single table is replaced, bythe same data being distributed over multiple tables with a specific relationship being setupbetween the tables. By this process RDBMS schema designers try their best to reduce table data tothe very minimum.
Normalization is carried out for the following reasons:
To structure the data between tables so that data maintenance is simplified. To allow data retrieval at optimal speed. To simplify data maintenance through updates, inserts and deletes. To reduce the need to restructure tables as new application requirements arise. To improve the quality of design for an application by rationalization of table data.
Normalization is a technique that:
Decomposes data into two-dimensional tables. Eliminates any relationships in which table data does fully depend upon the primary key of
a record. Eliminates any relationship that contains transitive dependencies.
First Normal Form
When a table is decomposed into two-dimensional tables with all repeating groups of dataeliminated, the table data is said to be in its first normal form. The repetitive portion of databelonging to the record is termed as repeating groups.
Example:
Table(Structure): EmpProj
Field Key TypeProject number --Project name --Employee number -- 1-nEmployee name -- 1-nRate category -- 1-nHourly rate -- 1-nHint: 1-n indicates that there are many occurrence of this field.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
15 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Data help in the above table structure:
Projectnumber
Project name Employeenumber
Employee name Ratecategory
Hourly rate
P001 Using MySQL On Linux E001 Sharanam Shah A 7000P001 Using MySL On Linux E002 Vaishali Shah B 6500P001 Using MySQL On Linux E006 Hansel Colaco C 4500P002 Using Star Office On Linux E001 Sharanam Shah A 7000P002 Using Star Office On Linux E007 Chhaya Bankar B 4000
In the above data there are a few problems:
The Project name in the second record is misspelled. This can be solved by removingduplicates. Do this using normalization.
Data is repeated and thus occupies more space.
A table is in First Normal Form if:
There are no repeating groups. All the key attributes are defined. All attributes are depend on Primary Key.
So far there are no keys, and there are repeating groups. So remove the repeating groups, anddefine the primary key.
To convert to First Normal Form:
The unnormalized data i n the first table is the entiretable.
A key that will uniquely identify each record should beassigned to the table. This key has to be unique because itshould be capable of identifying any specific row from thetable for extracting information for use. This key is calledthe table’s Primary Key.
This table is in First Normal Form.
Second Normal Form
A table is said to be in its second normal form when each record in the table is in the firstnormal form and each column in the record is fully dependent on its primary key. It includes nopartial dependencies (where an attribute is dependent on only a part of a primary key).
Steps to convert a table to its Second Normal Form:
Find and remove fields that are related to the only part of the key. Group the removed items in another table. Assign the new table with the key i.e. part of a whole composite key.
In the above example:
Project name is only dependent on Project number. Employee name, Rate category and Hourly rate are dependent only on Employee number.
Field KeyProject number Primary KeyProject name --Employee number Primary KeyEmployee name --Rate category --Hourly rate --
PROMOTIONAL COPY
DBMS Lab Oracle 10g
16 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
To convert, remove and place these fields in a separate table, with the key being that part of theoriginal key they are depend on.
This leads to the following 3 tables:
Table: EmpProj Table: Emp
Field KeyProject number Primary KeyEmployee number Primary Key
Table: Proj
Field KeyProject number Primary KeyProject name --Table is now in 2nd normal form.
Third Normal Form
Table data is said to be in third normal form when all transitive dependencies (where a non-key attribute is dependent on another non-key attribute) are removed from this data.
A general case of transitive dependencies is as follows:
A, B, C are three columns in table.
If C is related to B,
If B is related to A,
Then C is indirectly related to A
This is when transitive dependency exists.
To convert such data to its 3rd normal form, remove this transitive dependency by splitting eachrelation in two separate relations. This means that data in columns A, B, C must be placed in threeseparate tables, which are linked using a Foreign Key.
In the above example:
Employee table is the only one with more than one non-key attribute. Employee name is not dependent on either Rate category or Hourly rate. Hourly rate is dependent on Rate category.
To convert the table into the 3rd normal form, remove and place these fields in a separate table,with the attribute it was dependent on as key, as follows:
Field KeyEmployee number Primary KeyEmployee name --Rate category --Hourly rate --
PROMOTIONAL COPY
DBMS Lab Oracle 10g
17 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
This leads to the following 4 tables:
Table: EmpProj
Field KeyProject number Primary KeyEmployee number Primary Key
Table: Emp
Field KeyEmployee number Primary KeyEmployee name --Rate category --
Table: Rate
Field KeyRate category Primary KeyHourly rate --
Table: Proj
Field KeyProject number Primary KeyProject name --
These tables are all now in their 3rd normal form, and ready to be implemented. There areother normal forms such as Boyce-Codd Normal Form, Fourth, Fifth Normal forms, but these arerarely used for business applications. In most cases, tables are in their 3rd normal form are alreadyconform to these type of tables formats anyway.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
18 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Working with SQL
Table Creation and Alteration
Create two tables using primary key and connect them using foreign key.
Populate the tables using Insert statement and display them using Select.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
19 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Alter the table using ALTER command.
Adding new column:
Modifying a column:
Defining Integrity constraints:
The structures of the tables are:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
20 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Now, we alter it to add Integrity Constraints.
Check Constraints
A check constraint is a search condition, like the search condition in a WHERE clause, thatproduces a true or false value. When a check constraint is specified for a column, the DBMSautomatically checks the value of that column each time a new row is inserted or a row is updated toensure that the search condition is true. If not, the INSERT or UPDATE statement fails.
Create a table studdetail with CHECK constraints.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
21 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Insertion
Create and maintain a table ‘customers’. Illustrate insert, delete and update queries.
Insert query, standard form:
Insert query, alternate method:
Now, we insert all values we need.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
22 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Data Retrieval
We use the SELECT query to retrieve data from a table.
Retrieve all rows from table ‘customers’.
Select names of customers whose state is ‘Kerala’.
Find customers whose city is ‘cochi’ and date of join is before 5th May 2012.
Find customers from ‘Tamilnadu’ and ‘Karnataka’.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
23 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Find customers from ‘cochi’,’Trivandrum’ and ‘Banglore’.
Find customers who are not from ‘Kerala’ or ‘Tamilnadu’.
Updating
Change balance of customer number ‘c01’ to ‘60000’.
Change name of customer to ‘James’ whose customer number is ‘c05’.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
24 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Deletion
Delete customers whose balance is less than ‘60000’.
Group By
Use group by clause to the following table.
Select number of employees in each branch.
Having Clause
Display customer number and number of accounts (both SB and CA) from following table.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
25 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Aggregate Functions
Table accmaster is used to demonstrate aggregate functions.
Find average balance of customers.
Find minimum balance from the table.
Find maximum balance from the table.
Find the number of accounts by counting accno.
Find the number of accounts by counting rows.
Calculate the total balance.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
26 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Numeric Functions
Absolute
Power
Round
Square Root
Greatest
Least
Modulus
PROMOTIONAL COPY
DBMS Lab Oracle 10g
27 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Truncate
Floor
Ceil
String Functions
LOWER
INITCAP
UPPER
SUBSTR
ASCII
INSTR
PROMOTIONAL COPY
DBMS Lab Oracle 10g
28 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
LENGTH
LTRIM
RTRIM
LPAD
RPAD
Conversion Functions
Table oldprice is used to demonstrate conversion functions.
TO_NUMBER
PROMOTIONAL COPY
DBMS Lab Oracle 10g
29 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
TO_CHAR
TO_DATE
Date Functions
Display date after 4 months from current date.
Display the last date of current month.
Find date of next Friday from current date.
Find the number of months between two dates.
ROUND Date.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
30 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
SET Operators
UNION INTERSECT MINUS
UNIONMultiple queries can be put together and their output can be combined using union. The
union clause merges the output of two or more queries into a single set of rows and column.
Tables used:Employee Address Customer
INTERSECTThe intersect clause outputs only rows produced by the queries intersected i.e., it will
include only those rows that are retrieved common to both the queries.
Table used: account
PROMOTIONAL COPY
DBMS Lab Oracle 10g
31 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
MINUS
It outputs the rows produced by the first query after filtering the rows retrieved by second query.
Table account (listed above) is used to illustrate minus.
Sorting Table
Consider the following table:
Sort the table on the basis of BRANCH_NO.
Sort the table on the basis of BRANCH_NO in descending order.
Sort the table on the basis of BRANCH_NAME.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
32 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Sub Query
Three tables are used:
movie
customers
transaction
The table transaction have reference to both movie and customer tables.
After inserting values:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
33 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Using Sub Queries, retrieve data connecting above tables.
Display the transaction details of the movie ‘FAST AND FURIOUS’.
Find the name of the customer who has the movie ‘FAST AND FURIOUS’.
Find the language of the movie which is with ‘V!5HNU’.
Details of movie which is with ‘R3NJ1TH’.
Details of customers who has the movie ‘HARRY POTTER’.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
34 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Joining Tables
The process of forming pairs of rows by matching the contents of related columns is calledjoining the tables. The resulting table (containing data from both of the original tables) is called ajoin between the two tables. A join based on an exact match between two columns is more preciselycalled an equi-join.
Single Join
Create two tables as follows and insert values:
After insertion:
Joining both tables:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
35 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Self Join
In some situations, it is necessary to join a table to itself, as though joining two separatetables. This is referred to as a self-join. In a self-join, two rows from the same table combine to forma result row.
Outer Join
The SQL join operation combines information from two tables by forming pairs of relatedrows from the two tables where the matching columns in each of the tables have the same values.
Create two tables:
Joining:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
36 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Data/Transaction Control Language
Oracle Transactions
A series of one or more SQL statements that are logically related or a series of operationsperformed on Oracle table data is termed as a Transaction. First, the changes requested are done, tomake these changes permanent a COMMIT statement has to be given at the SQL prompt. AROLLBACK statement given at the SQL prompt can be used to undo a part of or the entiretransaction.
Security Management
Depending on a user’s status and responsibility, appropriate rights on Oracle’s resources canbe assigned to the user by the DBA. The rights that allow the use of some or all of Oracle’s resourceson the Server are called Privileges.
If a user wishes to access any of the objects belonging to another user, the owner of theobject will have to give permissions for such access. This is called Granting of Privileges. Privilegesonce given can be taken back by the owner of the object. This is called Revoking of Privileges.
Working with Control commands:
Connect using the SYSTEM account.
Create a user temp and grant necessary privileges.
Grant all privileges on table clientmaster to the user temp.
Connect using temp account.
We can access the table clientmaster of SYSTEM account now.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
37 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Revoke the create view privilege from the user temp (By logging in as SYSTEM).
Trying to create a view by logging in as temp:
Revoke all privileges from the user temp (By logging in as SYSTEM).
Trying to access the table clientmaster from user temp.
Saving work till now.
Creating a save point.
We have 4 records in clientmaster. We create a save point to this point.
We then insert a row to clientmaster.
We didn’t find it good to insert such a row, or we want to undo anything that has happenedafter the save point, we rollback to that point.
Now, the newly created row will be deleted.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
38 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Views
A view is a virtual table in the database whose contents are defined by a query. To thedatabase user, the view appears just like a real table, with a set of named columns and rows of data.But unlike a real table, a view does not exist in the database as a stored set of data values. Instead,the rows and columns of data visible through the view are the query results produced by the querythat defines the view. SQL creates the illusion of the view by giving the view a name like a tablename and storing the definition of the view in the database.
Create a view which contains nomno, acctno and name from nominee table.
Display nomview.
Work with nomview view as with a table.
Destroy nomview.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
39 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Indexes
Indexing a table is an access strategy, i.e., a way to sort and search records in the table.Indexes are essential to improve the speed with which record(s) can be located and retrieved from atable. An index is an ordered list of the contents of a column, (or a group of columns) of a table.
When data is inserted in the table, the oracle engine automatically insert the data value inthe index. For every data value held in the index, the oracle engine inserts a unique ROWID value;which indicates exactly where the record is stored in the table. The records in the index are stored inthe ascending order of the index column. The value of ROWID cannot be set or deleted using theinsert or update. The address field of an index is called ROWID. The information in the ROWIDcolumn provides the oracle engine about the location of the table and a specific record in the oracledatabase.
Simple Index
An index created on a single column of a table is called a Simple Index.
Create a simple index on cno of table customer.
Display ROWID of the records in table customer.
Create simple index on cname.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
40 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Composite Index
An index created on more than one column is called a Composite Index.
Create composite index on table acc.
Reverse Index
Creating a reverse key index, reverses each byte of column being indexed while keeping thecolumn order. Such an arrangement can avoid performance degradation in indexes wheremodifications to the index are concentrated on a small set of blocks. By reversing the keys of theindex, the insertions become distributed all over the index.
Create a reverse key index on accno of acc table.
Drop Index
PROMOTIONAL COPY
DBMS Lab Oracle 10g
41 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Clusters
Clustering is an important concept for improving Oracle performance. The concept of acluster is where member records are stored physically near parent records. For Oracle, clusters canbe used to define common, one-to-many access paths, and the member rows can be stored on thesame database block as their owner row. Clusters are used to store data from different tables in thesame physical data blocks. Each cluster stores the table’s data, as well as maintains the cluster indexthat is used to sort table data.
The Cluster key: The columns within the cluster index are called the cluster key (i.e. the set ofcolumns that the tables in the cluster have in common).
After the cluster has been created, the cluster index is created on the cluster key columns.After the cluster key index has been created, data can be entered into the tables stored in thecluster. As rows are inserted the database will store a cluster key and its associated rows in each ofthe cluster’s blocks.
Syntax:
CREATE CLUSTER <Cluster Name> (<Column> <Data Type>[, <Column> <Data Type>]…) [<Other Options>];
The cluster name follows the table naming conventions, also column and data type is thename and data type used as cluster key. The column name may be same as one of the columns of atable or it may be any other valid name.
Example:
CREATE CLUSTER “DBA_BANKSYS”.”BRANCH INFO”(“BRANCH_NO” VARCHAR2(10));
CREATE TABLE “DBA_BANKSYS”.”BRANCH_MSTR”(“BRANCH_NO” VARCHAR2(10) PRIMARY KEY,”NAME” VARCHAR2(25))CLUSTER BRANCH_INFO(BRANCH_NO);
CREATE TABLE “DBA_BANKSYS”.”ADDR_DTLS”(“ADDR_NO” NUMBER(6) PRIMARY KEY,”CODE_NO” VARCHAR2(10),“ADDR_TYPE” VARCHAR2(1),”ADDR1” VARCHAR2(50),“ADDR2” VARCHAR2(25), “CITY” VARCHAR2(25),“STATE” VARCHAR2(25), “PINCODE” VARCHAR2(6));CLUSTER BRANCH_INFO(BRANCH_NO);
Advantages: Disk I/O is reduced and access time improves for joins of clustered tables. In a cluster, a cluster key value is the values of the cluster key columns for a particular row.
Each cluster key value is stored only once each in the cluster and the cluster index, nomatter how many rows of different tables contain the value.
Since all rows in clustered tables use the same columns as the common primary key, thecolumns are stored only once for all tables, yielding some storage benefit.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
42 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Sequences
Oracle provides an object called a Sequence that can generate numeric values. The valuegenerated can have a maximum of 38 digits. A sequence can be defined to:
Generate numbers in ascending or descending order. Provide intervals between numbers. Caching of sequence numbers in memory to speed up their availability.
A sequence is an independent object and can be used with any table that requires its output.
Creating a Sequence
Syntax:
CREATE SEQUENCE <Sequence Name>
[INCREMENT BY <Integer Value>
START WITH <Integer Value>
MAXVALUE <Integer Value> / NOMAXVALUE
MINVALUE <Integer Value> / NOMINVALUE
CYCLE / NOCYCLE
CACHE <Integer Value> / NOCACHE
ORDER / NOORDER ]
Create a sequence.
Get the next value of the sequence.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
43 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Create a table addrsdetail and use sequence to generate its primary keys.
To get the current value of a sequence use ‘CurrVal’.
Altering a Sequence
Syntax:
ALTER SEQUENCE <SequenceName>
[INCREMENT BY <IntegerValue> MAXVALUE <IntegerValue> / NOMAXVALUE
MINVALUE <IntegerValue> / NOMINVALUE CYCLE / NOCYCLE
CACHE <IntegerValue> / NOCACHE ORDER / NOORDER]
Alter addrseqe.
Dropping a Sequence
PROMOTIONAL COPY
DBMS Lab Oracle 10g
44 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Synonym
A synonym is an alias or alternate name for a table, view, sequence, or other schema object.They are used mainly to make it easy for users to access database objects owned by other users.They hide the underlying object's identity and make it harder for a malicious program or user totarget the underlying object. Because a synonym is just an alternate name for an object, it requiresno storage other than its definition. When an application uses a synonym, the DBMS forwards therequest to the synonym's underlying base object. By coding your programs to use synonyms insteadof table names, you insulate yourself from any changes in the name, ownership, or table locations. Ifyou frequently refer to a table that has a long name, you might appreciate being able to refer to itwith a shorter name without having to rename the table and alter the code referring to that table.
Example:
We’re trying to access a table in the SYSTEM account from temp account, and we find it lengthy.
We can change the “system.clientmaster” to cmaster to make it short. For that we use a synonym.
Once you have defined a synonym, you can use it just like a table name in SQL queries.
Dropping a Synonym
PROMOTIONAL COPY
DBMS Lab Oracle 10g
45 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
PL/SQL ProgrammingPL/SQL permits the creation of structured logical blocks of code that describe processes,
which have to be applied to data. A single PL/SQL code block consists of a set of SQL statements,clubbed together, and passed to the Oracle engine entirely. This block has to be logically groupedtogether for the engine to recognize it as a singular code block. A PL/SQL block has a definitestructure, which can be divided into sections. The sections of a PL/SQL block are:
1. The Declare Section
Code blocks start with a declaration section, in which, memory variables and other Oracleobjects can be declared, and if required initialized. Once declared, they can be used in SQLstatements for data manipulation.
2. The Begin Section
It consists of a set of SQL and PL/SQL statements, which describe processes that have to beapplied to table data. Actual data manipulation, retrieval, looping and branching constructs arespecified in this section.
3. The Exception Section (Optional)
This section deals with handling of errors that arise during execution of the datamanipulation statements, which make up the PL/SQL code block. Errors can arise due to syntax, logicand/or validation rule violation.
4. The End Section
This marks the end of a PL/SQL block.
Displaying User Messages On The VDU Screen
DBMS_OUTPUT.PUT_LINE(‘Message to be displayed’);
To display messages, the SETSERVEROUTPUT should be set to ON.
SET SERVEROUTPUT [ON/OFF]
Control Structure.
Conditional Control IterativeSimple Loop While Loop For Loop
IF <Condition> THEN<Action>ELSIF <Condition> THEN<Action>ELSE<Action>END IF;
LOOP<Seq. of Stmt.>END LOOP;
WHILE <Condition>LOOP<Action>END LOOP;
FOR variable INstart..endLOOP<Action>END LOOP;
PROMOTIONAL COPY
DBMS Lab Oracle 10g
46 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Factorial up to a Limit
Write a PL/SQL code block to print Factorial of numbers up to a given limit.
Algorithm:
Step1: Start
Step2: Declare the variables limit, l, fact and i as number.
Step3: Initialize fact:= 1 & l:= 2
Step4: Read value for limit
Step5: Repeat for i=1 to limit by 1
Repeat while l<=i
f:= f*l
l:=l+1
[End of inner loop]
[End of outer loop]
Step6: Print the result
Step7: End
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
47 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Generate Fibonacci Series
Write a PL/SQL code block to print Fibonacci series up to a given limit.
Algorithm:
Step1: Start
Step2: Declare variables f1, f2, f3, n, i as number.
Step3: Initialize f1:=0, f2:=1.
Step4: Read the limit n.
Step5: If n=0, then
Print f1
[End of if structure]
Step6: If n=1, then
Print f2
[End of if structure]
Step7: If n>1, then
Print f1, f2
Repeat for i:= 3 to n by 1
f3:=f1+f2
Print f3
Set f1:=f2
Set f2:=f3
[End of loop]
[End of if structure]
Step8: End
PROMOTIONAL COPY
DBMS Lab Oracle 10g
48 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Program:
Output:PROMOTIONAL COPY
DBMS Lab Oracle 10g
49 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Quadratic Equation
Write a PL/SQL code block to solve Quadratic Equation.
Algorithm:
Step1: Start
Step2: Declare the variables a, b, c, d, r1, r2, root as number.
Step3: Read the input values for variables a, b & c
Step4: Set d:= b*b-4*a*c.
Step5: If d=0, Then
(a) Set root=-b/2*a.
(b) Print the root
Else if d>0, then
(a) Set r1=(-b+sqrt(d))/(2*a)
(b) Set r2=(-b-sqrt(d))/(2*a)
(c) Print the results
Else
Print 'Roots are imaginary'.
[End of If structure]
Step6: End
PROMOTIONAL COPY
DBMS Lab Oracle 10g
50 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
51 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Check Prime or Not
Write a PL/SQL code block for checking a number to find whether it is Prime or Not.
Algorithm:
Step1: Start
Step2: Declare variables n, c as number.
Step3: Read the number n
Step4: Set c:=0
Step5: Repeat for i=2 to n by 1
If n%i=0,Then
c:= c+1
[End of if structure]
[End of loop]
Step6: If c=1, Then
Print 'IS PRIME'.
Else
Print 'NOT PRIME'.
[End of if structure]
Step7: End.
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
52 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Multiplication Table
Write a PL/SQL a code block to print Multiplication Table for given number.
Algorithm:
Step1: Start
Step2: Declare variables n, limit & i as number.
Step3: Read the values for n and limit
Step4: Set i:= 1,r:=0
Step5: Repeat for i=1 to limit by 1
Print i*n
[End of loop]
Step6: End
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
53 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Check Balance in Account Table
Write a PL/SQL code block that accept accno and check if user's balance is less than theminimum balance only then reduce Rs. 100 from it.
Algorithm:
Step1: Start
Step2: Create a table account with fields acctno and curbal andpopulate it with records.
Step3: Declare acno, temp, minbal as number.
Step4: Set minbal:=1000,as constant
Step5: Read the acno and assign the curbal field value of thataccount to variable temp
Step6: If temp<minbal, Then
Update the table account,
Set curbal=curbal-100.
Print 'Rs.100/- deducted from your account'
Else
Print 'You have sufficient balance'
[End of if structure]
Step7: End
Program:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
54 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Output:
Table contents before executing the code block.
Table contents after executing the code block.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
55 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Circle Area
Calculate the area of circle for radius varying from 2 to 6 and store them into a table (circle).
Algorithm:
Step1: Start
Step2: Create table circle with fields radius, area.
Step3: Define pi as constant having value 3.14
Step4: Declare variables i, ar as number
Step5: Repeat for i=2 to 6 by 1
ar:=pi*power(i,2)
Insert these values to table circle
[End of loop]
Step6: End
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
56 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
PL/SQL Functions and ProceduresA Procedure or Function is logically grouped set of SQL and PL/SQL statements that perform
a specific task. A Stored Procedure or function is a named PL/SQL code block that has been compiledand stored in one of the Oracle Engine’s system tables.
Creating a Stored Procedure:
CREATE OR REPLACE PROCEDURE <Procedure Name>
(<Argument> {IN,OUT,IN OUT} <Data type>, …) {IS, AS}
<Variable> declarations;
<Constant> declarations;
BEGIN
<PL/SQL subprogram body>;
EXCEPTION
<Exception PL/SQL block>;
END;
Creating a Function
CREATE OR REPLACE FUNCTION <Function Name>
(<Argument> IN <Data type>,…)
RETURN <Data type> (IS, AS)
<Variable> declarations;
<Constant> declarations;
BEGIN
<PL/SQL subprogram body>;
EXCEPTION
<Exception PL/SQL block>;
END;
PROMOTIONAL COPY
DBMS Lab Oracle 10g
57 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Add two numbers using Function
Create and call a PL/SQL Function to add two numbers.
Algorithm:
Step1: Start
Step2: (a) Create a function addno() accepting 2 parameters a and b
(b) Declare a variable result inside the function definition
(c) Set result:= a+b
(d) Return the result
Step3: Define the main code declaring 2 variables a, b as number.
Step4: Read the values for a and b
Step5: Call the function addno(),by passing a and b as parameters
Step6: Print the result
Step7: End
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
58 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Factorial using Function
Use Function to find factorial of any number by calling it.
Algorithm:
Step1: Start
Step2:(a) Create a function named fact() with parameter
(b) Declare variables f, i, l as number
(c) Set f:=1,l:=2
(d) Repeat for i=1 to a by 1
Repeat while l<=i
f:=f*l
l:=l+1
[End of inner loop]
[End of outer loop]
(e) Return f
Step3: Define the main code, declaring a variable n.
Step4: Read the value for n
Step5: Call the function fact() passing n as parameter
Step6: Print the result
Step7: End
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
59 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Prime or Not Function
Create a function to check whether a number is Prime or Not.
Algorithm:
Step1: Start
Step2: (a) Create a function chkprime with a parameter n.
(b) Declare a variable c and set c:=0
(c) Repeat for i=2 to n by 1
If n % i =0, then
c:=c+1
[End of if structure]
[End of loop]
(d) If c=1, then
return 1
Else
return 0
[End of if structure]
Step3: Define the main code, declaring a variable n
Step4: Read the value for n
Step5: Call the function chkprime()
Step6: If return value is 1
Print ‘IS PRIME’
Else
Print ‘NOT PRIME’
Step7: End
PROMOTIONAL COPY
DBMS Lab Oracle 10g
60 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
61 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Fibonacci Series using Procedure
Create a procedure to generate Fibonacci Series.
Algorithm:
Step1: Start
Step2: (a) Create a procedure fib with parameter n
(b) Declare variables f1, f2, f3, i as number.
(c) Initialize f1:=0, f2:=1.
(d) If n=0, then
Print f1
[End of if structure]
(e) If n=1, then
Print f2
[End of if structure]
(f) If n>1, then
Print f1, f2
Repeat for i:= 3 to n by 1
f3:=f1+f2
Print f3
Set f1:=f2
Set f2:=f3
[End of loop]
[End of if structure]
[End of procedure]
Step3: Define the main code declaring a variable x
Step4: Read the value for x
Step5: Call the procedure.
Step6: End
PROMOTIONAL COPY
DBMS Lab Oracle 10g
62 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
63 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Quadratic Equation using Procedure
Create a procedure to solve a Quadratic Equation.
Algorithm:
Step1: Start
Step2: (a) Create a procedure quad with parameters a, b, c
(b) Declare variables d, r1, r2, root as number
(c) Set d:=b*b-4*a*c.
(d) If d=0, then
Set root=-b/2*a.
Print the root
Else if d>0, then
Set r1=(-b+sqrt(d))/(2*a)
Set r2=(-b-sqrt(d))/(2*a)
Print the results
Else
Print 'Roots are imaginary'.
[End of If structure]
[End of procedure]
Step3: Define the main code having variables a, b & c
Step4: Read the values for a, b & c
Step5: Call the procedure
Step6: End
PROMOTIONAL COPY
DBMS Lab Oracle 10g
64 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Program:
Output:PROMOTIONAL COPY
DBMS Lab Oracle 10g
65 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Employee details from Table
Create a procedure to access emp table and display its contents. Procedure should acceptaccno.
Algorithm:
Step1: Start
Step2: Create a procedure empdetails
(a) Declare variables id, name, sal, depno
(b) Select the field values of emp with eno=no and assign toid, name, sal, depno.
(c) Display the details. [End of Procedure].
Step3: Define the main code declaring a variable empno
Step4: Read the value for empno
Step5: Call the procedure empdetails with empno as parameter
Step6: End
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
66 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
TriggersThe Oracle engine allows the definition of procedures that are implicitly executed, when an
insert, update or delete is issued against a table. These procedures are called Database Triggers.
Trigger has 3 basic parts: A triggering event or statement, a trigger restriction, and a trigger action.
Triggering Event or Statement: It is a SQL statement that causes a trigger to be fired. It can beINSERT, UPDATE or DELETE statement for a specific table.
Trigger Restriction: A trigger restriction specifies a Boolean (Logical) expression that must be TRUEfor the trigger to fire.
Trigger Action: A trigger action is the PL/SQL code to be executed when a triggering statement isencountered and any trigger restriction evaluates to TRUE.
Types of Triggers:
Row Triggers A row trigger is fired each time a row in the table is affected by the triggeringstatement.
Statement Triggers A statement trigger is fired once on behalf of the triggering statement,independent of the no. of rows the triggering statement affects.
Before Triggers Executes the trigger action before the triggering statement.After Triggers Executes the trigger action after the triggering statement is executed.
Syntax:
CREATE OR REPLACE TRIGGER <Trigger Name>
{BEFORE, AFTER}
{DELETE, INSERT, UPDATE [OF Column, ..]}
ON <Table Name>
[REFERENCING {OLD AS old, NEW AS new}]
[FOR EACH ROW [WHEN Condition]]
DECLARE
<Variable declarations>;
<Constant declarations>;
BEGIN
<PL/SQL subprogram body>;
EXCEPTION
<Exception PL/SQL block>;
END;
PROMOTIONAL COPY
DBMS Lab Oracle 10g
67 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Create a trigger auditclient to set operation to manage audit1 table when an update isoccurred.
We use contents of table clientmaster to copy to audit1 table.
Program:
We fire an update query to clientmaster.
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
68 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
ODBC ConnectivityTo use the Oracle database with any Front End Application Programs, we use ODBC (Open
Database Connection). Using ODBC, we can connect we can connect to oracle tables and can beused in Programming Languages like ASP.NET, VB, Java etc. The following steps illustrate how to setan ODBC connection with Oracle 10g and use it with Visual Basic 6.0.
Environment:
Database : Oracle 10g Express Edition
Operating System : Microsoft Windows XP (32 bit) or Microsoft Windows 7 (32 bit only).
Follow these steps to configure a Data Source Name (DSN):
Open Data Sources in Control Panel.(Control Panel Administrative Tools Data Sources (ODBC)). In Windows7, go to Control Panel; change the viewto ‘Large Icons’ and selectAdministrative Tools.
Select the System DSN tab from the Data Sources window.
Click Add button.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
69 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
The Create New Data Source appears. Select ‘Oracle in XE’ and click Finish.
The Oracle ODBC Driver Configuration Appears. Provide following information:Data Source Name : payroll (Anything you wish, but this is used later to connect).TNS Service Name : XEUser ID : system (User ID of the account you want to connect).And then click Test Connection.
The connection window appears; type in the Password of your account (Usually system).And click OK. If everything goes well, you’ll get aConnection Successful message. Click OK.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
70 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
After the configuration is complete, we can find the DSN we created listed.
Now, we have configured DSN. We can now use this in any Programming Language whichsupports Database Programming.
Database Programming with Visual Basic (and Oracle)
Follow these steps to make database application with VB as Front end and Oracle as Back end:
Open Visual Basic 6.0, start a new Standard EXE application. We use Microsoft ADO to work with database. So, Enable
‘Microsoft ActiveX Data Objects 2.8 Library’ Project References(Project References).
PROMOTIONAL COPY
DBMS Lab Oracle 10g
71 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Then, design the form with necessary controls and objects.
Declare Command, Connection and Record Set variables. To make the connection, type following code in Form Load() event:
con.Open "payroll", "system", "system"
Here, payroll is the Data Source Name that we’ve configured; system and system are Usernameand Password of the database user which has the required table.
cmd.ActiveConnection = con
rs.CursorLocation = adUseClient
cmd.CommandText = "select * from paytab"
Here, paytab is the actual table that we are accessing.
rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
After the above code will be executed, the first row (complete) in the table paytab will beavailable in rs variable. We can get value of each column by rs(“Column Name”).
Further operations such as INSERT, DELETE, SEARCH etc. can be done by executing SQLqueries with Command Variable through CommandText property.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
72 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Payroll Information System
Create a Payroll Information application in Visual Basic with Oracle as Back End.
Table used: payroll
First, set up a System DSN as mentioned before with DSN name as ‘pay’. Then, create aStandard EXE application in VB and design the form with following controls and necessary labels.
Controls Used:
Controls Properties
Textboxes
Name Textenotxtenametxtdojtxtbsaltxthratxtdatxttatxtgrsaltxt
Command Buttons
Name Captioncmdcalc Calculatecmdnew Newcmdsave Savecmdfirst <<cmdprev <cmdnxt >cmdlast >>
PROMOTIONAL COPY
DBMS Lab Oracle 10g
73 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Code:
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
_________________________________________________________
Private Sub Form_Load()
con.Open "pay", "system", "system"
cmd.ActiveConnection = con
rs.CursorLocation = adUseClient
cmd.CommandText = "select * from payroll"
rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
Call Disp
End Sub
_________________________________________________________
Sub Disp()
If (rs.BOF = True) Then
MsgBox ("Sorry, No Records found in Database!")
rs.MoveFirst
ElseIf (rs.EOF = True) Then
MsgBox ("Sorry, EOF!")
rs.MoveLast
End If
enotxt.Text = rs("eno")
enametxt.Text = rs("ename")
bsaltxt.Text = rs("basic_sal")
hratxt.Text = rs("hra")
datxt.Text = rs("da")
tatxt.Text = rs("ta")
grsaltxt.Text = rs("grsal")
dojtxt.Text = rs("doj")
End Sub
_________________________________________________________
PROMOTIONAL COPY
DBMS Lab Oracle 10g
74 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Private Sub cmdnew_Click()
enotxt.Text = ""
enametxt.Text = ""
bsaltxt.Text = ""
hratxt.Text = ""
datxt.Text = ""
tatxt.Text = ""
grsaltxt.Text = ""
dojtxt.Text = ""
End Sub
_________________________________________________________
Private Sub cmdsave_Click()
cmd.CommandText = "insert intopayroll(eno,ename,basic_sal,hra,da,ta,grsal,doj) values(" &enotxt.Text & ",'" & enametxt.Text & "'," & bsaltxt.Text & "," &hratxt.Text & "," & datxt.Text & "," & tatxt.Text & "," &grsaltxt.Text & ",'" & dojtxt.Text & "')"
cmd.Execute
MsgBox ("One Row Inserted!")
cmd.CommandText = "select * from payroll"
rs.Requery
cmdfirst_Click
End Sub
_________________________________________________________
Private Sub cmdfirst_Click()
rs.MoveFirst
Call Disp
End Sub
_________________________________________________________
Private Sub cmdprev_Click()
rs.MovePrevious
Call Disp
End Sub
_________________________________________________________
PROMOTIONAL COPY
DBMS Lab Oracle 10g
75 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Private Sub cmdnxt_Click()
rs.MoveNext
Call Disp
End Sub
_________________________________________________________
Private Sub cmdlast_Click()
rs.MoveLast
Call Disp
End Sub
_________________________________________________________
Private Sub cmdcalc_Click()
Dim h As Integer
Dim d As Integer
Dim basic As Integer
h = hratxt.Text
d = datxt.Text
basic = bsaltxt.Text
If ((h <= 100) And (d <= 100)) Then
grsaltxt.Text = basic + (basic * (h / 100)) + (basic * (d /100)) + tatxt.Text
Else
MsgBox ("HRA and/or DA Exceeds the limit!")
End If
End Sub
_________________________________________________________
PROMOTIONAL COPY
DBMS Lab Oracle 10g
76 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
OUTPUT
PROMOTIONAL COPY
DBMS Lab Oracle 10g
77 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Student Information System
Create a Student Information System in VB with Oracle as Back End.
DSN : studTable used : studtab
Controls Properties
Textboxes
Name Textrnotxtnametxtdobtxtm1txtm2txtm3txttottxtpertxtpercent %grdtxt
Combo BoxName List
cbo
Command Buttons
Name Captioncmdcalc Calculatecmdclear Clearcmdsave Savecmdfirst <<cmdprev <cmdnxt >cmdlast >>cmdsrch Search
Code:
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cmd2 As New ADODB.Command
PROMOTIONAL COPY
DBMS Lab Oracle 10g
78 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Dim rs As New ADODB.Recordset
Dim rss As New ADODB.Recordset
___________________________________________________________________
Private Sub cmdclear_Click()
rnotxt.Text = ""
nametxt.Text = ""
dobtxt.Text = ""
cbo.ListIndex = 0 'To set default value to the first list item
m1txt.Text = ""
m2txt.Text = ""
m3txt.Text = ""
tottxt.Text = ""
pertxt.Text = ""
grdtxt.Text = ""
End Sub
___________________________________________________________________
Private Sub Form_Load()
cnn.Open "stud", "system", "system"
cmd.ActiveConnection = cnn
cmd.CommandText = "select * from studtab"
rs.CursorLocation = adUseServer
rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
Call Disp
End Sub
___________________________________________________________________
Sub Disp()
If (rs.BOF = True) Then
MsgBox ("Sorry, No Records found in Database!")
rs.MoveFirst
ElseIf (rs.EOF = True) Then
MsgBox ("Sorry, EOF!")
rs.MoveLast
End If
PROMOTIONAL COPY
DBMS Lab Oracle 10g
79 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
rnotxt.Text = rs("rollno")
nametxt.Text = rs("name")
dobtxt.Text = rs("dob")
cbo.Text = rs("course")
m1txt.Text = rs("m1")
m2txt.Text = rs("m2")
m3txt.Text = rs("m3")
tottxt.Text = rs("tot")
pertxt.Text = rs("percent")
grdtxt.Text = rs("grade")
End Sub
___________________________________________________________________
Private Sub cmdcalc_Click()
Dim m1 As Integer
Dim m2 As Integer
Dim m3 As Integer
Dim tot As Integer
Dim p As Integer
Dim g As String
If (IsNumeric((m1txt.Text) And (m2txt.Text) And (m3txt.Text)) =True) And (m1txt.Text <= 100) And (m2txt.Text <= 100) And(m3txt.Text <= 100) Then
m1 = m1txt.Text
m2 = m2txt.Text
m3 = m3txt.Text
tot = m1 + m2 + m3
p = tot / 3
If (p >= 80) Then
g = "A"
ElseIf (p >= 70) Then
g = "B"
ElseIf (p >= 60) Then
g = "C"
ElseIf (p >= 50) Then
PROMOTIONAL COPY
DBMS Lab Oracle 10g
80 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
g = "D"
Else
g = "E"
End If
tottxt.Text = tot
pertxt.Text = p
grdtxt.Text = g
Else
MsgBox ("Enter Valid Marks")
End If
End Sub
___________________________________________________________________
Private Sub cmdsave_Click()
cmd.CommandText = "insert intostudtab(rollno,name,dob,course,m1,m2,m3,tot,percent,grade) values("& rnotxt.Text & ",'" & nametxt.Text & "','" & dobtxt.Text & "','" &cbo.Text & "'," & m1txt.Text & "," & m2txt.Text & "," & m3txt.Text& "," & tottxt.Text & "," & pertxt.Text & ",'" & grdtxt.Text & "')"
cmd.Execute
MsgBox ("One Row Inserted!")
cmd.CommandText = "select * from studtab"
rs.Requery
cmdfirst_Click
End Sub
___________________________________________________________________
Private Sub tottxt_GotFocus()
cmdcalc_Click
End Sub
___________________________________________________________________
Private Sub cmdfirst_Click()
rs.MoveFirst
Call Disp
End Sub
Private Sub cmdprev_Click()
PROMOTIONAL COPY
DBMS Lab Oracle 10g
81 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
rs.MovePrevious
Call Disp
End Sub
Private Sub cmdnxt_Click()
rs.MoveNext
Call Disp
End Sub
Private Sub cmdlast_Click()
rs.MoveLast
Call Disp
End Sub
___________________________________________________________________
Private Sub cmdsrch_Click()
cmd2.ActiveConnection = cnn
cmd2.CommandText = "select * from studtab where rollno=" &InputBox("Enter Roll Number to search!", Search) & ""
rss.Open cmd2, , adOpenStatic, adLockBatchOptimistic
If (rss.EOF = True) Then
MsgBox "No Records!"
Else
rnotxt.Text = rss("rollno")
nametxt.Text = rss("name")
dobtxt.Text = rss("dob")
cbo.Text = rss("course")
m1txt.Text = rss("m1")
m2txt.Text = rss("m2")
m3txt.Text = rss("m3")
tottxt.Text = rss("tot")
pertxt.Text = rss("percent")
grdtxt.Text = rss("grade")
End If
End Sub
___________________________________________________________________
PROMOTIONAL COPY
DBMS Lab Oracle 10g
82 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
OUTPUT
PROMOTIONAL COPY
DBMS Lab Oracle 10g
83 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Banking Application
Create a Banking Application to store and retrieve customer details.
DSN : bankdsnTable used : bank
Controls Properties
Textboxes
Name Textacnotxtcnametxtaddrstxt (Multiline = True)
(Max Length = 50)placetxtbaltxt
Option ButtonName Caption
sbopt SBcaopt CA
Command Buttons
Name Captioncmddel Deletecmdclr Clearcmdsave Savecmdfirst Firstcmdprev Previouscmdnxt Nextcmdlast Lastcmdsrch Search
Code:
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cmd1 As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim rss As New ADODB.Recordset
PROMOTIONAL COPY
DBMS Lab Oracle 10g
84 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
___________________________________________________________________
Private Sub Form_Load()
cnn.Open "bankdsn", "system", "j"
cmd.ActiveConnection = cnn
cmd.CommandText = "select * from bank"
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
Call Disp
End Sub
___________________________________________________________________
Sub Disp()
If (rs.BOF = True) Then
MsgBox ("Sorry, BOF!")
rs.MoveFirst
ElseIf (rs.EOF = True) Then
MsgBox ("Sorry, EOF!")
rs.MoveLast
End If
acnotxt.Text = rs("acno")
If (rs("actype") = "SB") Then
sbopt.Value = True
Else
caopt.Value = True
End If
cnametxt.Text = rs("cname")
addrstxt.Text = rs("addrs")
placetxt.Text = rs("place")
baltxt.Text = rs("balance")
End Sub
___________________________________________________________________
PROMOTIONAL COPY
DBMS Lab Oracle 10g
85 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Private Sub cmdclr_Click()
acnotxt.Text = ""
sbopt.Value = False
caopt.Value = False
cnametxt.Text = ""
addrstxt.Text = ""
placetxt.Text = ""
baltxt.Text = ""
End Sub
___________________________________________________________________
Private Sub cmdsave_Click()
Dim atype As String
If (sbopt.Value = False) And (caopt.Value = False) Then
MsgBox ("Please Select an Account Type!")
Else
If (sbopt.Value = True) Then
cmd.CommandText = "insert intobank(acno,actype,cname,addrs,place,balance) values( " &acnotxt.Text & ", '" & sbopt.Caption & "' ,'" & cnametxt.Text &"','" & addrstxt.Text & "','" & placetxt.Text & "'," & baltxt.Text& ")"
Else
cmd.CommandText = "insert intobank(acno,actype,cname,addrs,place,balance) values( " &acnotxt.Text & ", '" & caopt.Caption & "' ,'" & cnametxt.Text &"','" & addrstxt.Text & "','" & placetxt.Text & "'," & baltxt.Text& ")"
End If
cmd.Execute
MsgBox ("One Row Inserted!")
cmd.CommandText = "select * from bank"
rs.Requery
cmdfirst_Click
End If
PROMOTIONAL COPY
DBMS Lab Oracle 10g
86 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
End Sub
___________________________________________________________________
Private Sub cmdfirst_Click()
rs.MoveFirst
Call Disp
End Sub
___________________________________________________________________
Private Sub cmdnxt_Click()
rs.MoveNext
Call Disp
End Sub
___________________________________________________________________
Private Sub cmdprev_Click()
rs.MovePrevious
Call Disp
End Sub
___________________________________________________________________
Private Sub cmdlast_Click()
rs.MoveLast
Call Disp
End Sub
___________________________________________________________________
Private Sub cmdsrch_Click()
cmd1.ActiveConnection = cnn
cmd1.CommandText = "select * from bank where acno=" &InputBox("Enter Account Number to search!", Search) & ""
rss.Open cmd1, , adOpenStatic, adLockBatchOptimistic
If (rss.EOF = True) Then
MsgBox "No Records!"
Else
acnotxt.Text = rss("acno")
PROMOTIONAL COPY
DBMS Lab Oracle 10g
87 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
If (rs("actype") = "SB") Then
sbopt.Value = True
Else
caopt.Value = True
End If
cnametxt.Text = rss("cname")
addrstxt.Text = rss("addrs")
placetxt.Text = rss("place")
baltxt.Text = rss("balance")
End If
rss.Close
End Sub
___________________________________________________________________
Private Sub cmddel_Click()
Dim d As Integer
d = InputBox("Enter Account Number to Delete")
cmd1.ActiveConnection = cnn
cmd1.CommandText = "select * from bank where acno=" & d & ""
rss.Open cmd1, , adOpenStatic, adLockBatchOptimistic
If rss.EOF = True Then
MsgBox "No Records"
Else
cmd1.CommandText = "delete from bank where acno=" & d & ""
cmd1.Execute
MsgBox "Deleted"
End If
rss.Close
rs.Requery
End Sub
___________________________________________________________________
PROMOTIONAL COPY
DBMS Lab Oracle 10g
88 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
OUTPUT
PROMOTIONAL COPY
DBMS Lab Oracle 10g
89 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Library Information System
Create a Library Information application which can manage, details of books in a library, inVB with Oracle as Back End.
DSN : libraryTable : libtab
Controls Properties
Textboxes
Name Textbidtxttitletxtauthtxtpubtxtpricetxt
Command Buttons
Name Captioncmdclr Clearcmdsave Savecmdfirst <<cmdprev <cmdnxt >cmdlast >>cmdbidsrch By Book IDcmdtitsrch By Title
Code:
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim rss As New ADODB.Recordset
Dim cmds As New ADODB.Command
___________________________________________________________________
Private Sub Form_Load()
cnn.Open "library", "system", "j"
cmd.ActiveConnection = cnn
cmd.CommandText = "select * from libtab"
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
PROMOTIONAL COPY
DBMS Lab Oracle 10g
90 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
Call Disp
End Sub
___________________________________________________________________
Sub Disp()
If (rs.BOF = True) Then
MsgBox ("Sorry, BOF!")
rs.MoveFirst
ElseIf (rs.EOF = True) Then
MsgBox ("Sorry, EOF!")
rs.MoveLast
End If
bidtxt.Text = rs("bid")
titletxt.Text = rs("title")
authtxt.Text = rs("author")
pubtxt.Text = rs("pub")
pricetxt.Text = rs("price")
End Sub
___________________________________________________________________
Private Sub cmdclr_Click()
bidtxt.Text = ""
titletxt.Text = ""
authtxt.Text = ""
pubtxt.Text = ""
pricetxt.Text = ""
End Sub
___________________________________________________________________
Private Sub cmdsave_Click()
cmd.CommandText = "insert into libtab(bid,title,author,pub,price)values(" & bidtxt.Text & ",'" & titletxt.Text & "','" &authtxt.Text & "','" & pubtxt.Text & "'," & pricetxt.Text & ")"
cmd.Execute
PROMOTIONAL COPY
DBMS Lab Oracle 10g
91 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
MsgBox ("Data Saved")
cmd.CommandText = "select * from libtab"
rs.Requery
cmdclr_Click
End Sub
___________________________________________________________________
Private Sub cmdfirst_Click()
rs.MoveFirst
Call Disp
End Sub
___________________________________________________________________
Private Sub cmdlast_Click()
rs.MoveLast
Call Disp
End Sub
___________________________________________________________________
Private Sub cmdprev_Click()
rs.MovePrevious
Call Disp
End Sub
___________________________________________________________________
Private Sub cmdnxt_Click()
rs.MoveNext
Call Disp
End Sub
___________________________________________________________________
Private Sub cmdbidsrch_Click()
cmds.ActiveConnection = cnn
cmds.CommandText = "select * from libtab where bid=" &InputBox("Enter Book ID Number to search!", Search) & ""
rss.Open cmds, , adOpenStatic, adLockBatchOptimistic
PROMOTIONAL COPY
DBMS Lab Oracle 10g
92 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
If (rss.EOF = True) Then
MsgBox "No Records!"
Else
bidtxt.Text = rss("bid")
titletxt.Text = rss("title")
authtxt.Text = rss("author")
pubtxt.Text = rss("pub")
pricetxt.Text = rss("price")
End If
rss.Close
End Sub
___________________________________________________________________
Private Sub cmdtitsrch_Click()
cmds.ActiveConnection = cnn
cmds.CommandText = "select * from libtab where title='" &InputBox("Enter Book Title to search!", Search) & "'"
rss.Open cmds, , adOpenStatic, adLockBatchOptimistic
If (rss.EOF = True) Then
MsgBox "No Records!"
Else
bidtxt.Text = rss("bid")
titletxt.Text = rss("title")
authtxt.Text = rss("author")
pubtxt.Text = rss("pub")
pricetxt.Text = rss("price")
End If
rss.Close
End Sub
___________________________________________________________________
PROMOTIONAL COPY
DBMS Lab Oracle 10g
93 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
OUTPUT
PROMOTIONAL COPY