lab manual - omgroup.edu.in · of database development in the hands of database administrators (db...

OM INSTITUTE OF TECHNOLOGY & MANAGEMENT

JUGLAN, HISAR-125001

LAB MANUAL

Oracle & SQL Programming

CSP-631

DEPARTMENT OF COMPUTER APPLICATIONS

INTRODUCTION TO DBMS AND ORACLE

DATABASE:A database consists of an organized collection of data for one or more uses. One way

of classifying databases involves the type of their contents, for example: bibliographic, full-text, numeric, image etc. Databases are managed using database management systems, whichstore database contents, allowing data creation and maintenance, and search and other access.

DATABASE MANAGEMENT SYSTEMS:A Database Management System (DBMS) is a set of computer programs that controls

the creation, maintenance, and the use of a database. It allows organizations to place controlof database development in the hands of database administrators (DBAs) and other specialists.A DBMS is a system software package that helps the use of integrated collection of datarecords and files known as databases. It allows different user application programs to easilyaccess the same database. DBMSs may use any of a variety of database models, such as thenetwork model or relational model.

RELATIONAL DBMS:RDBMS may be a DBMS in which data is stored in the form of database objects

called tables and the relationship among the data is also stored in the form of tables. A table isa collections of related data entries and it consists of columns and rows.

In the relational model, related records are linked together with a "key".

RDBMS Examples:RDBMS is the basis for SQL, and for all modern database systems like MS SQL

Server, IBM’s DB2, Oracle, MySQL, SyBase SQL Server and Microsoft Access.

ORACLE:The Oracle Database is a relational database management system (RDBMS) produced

and marketed by Oracle Corporation. In 1979 a company called Relational software, Inc.released the first commercially available implementation of SQL. Relational Software latercame to be known as Oracle Corporation.

AVAILABLE FEATURES IN ORACLE:

• Scalability and Performance: Concurrency, Consistency, Locking Mechanisms,Portability

• Manageability: Self managing database, SQL*Plus, Scheduler, Resource Manager• Backup and Recovery

• High availability• Business Intelligence: Materialized views, Bitmap indexes, Table compression,

Parallel Execution, Analytic SQL, Partitioning• Content Management: LOB, Oracle Text, Oracle Ultra Search• Security

• Data integrity/Triggers• Information Integration Features: Distributes SQL, Oracle Streams

DB Tools for Oracle is an integrated set of "must have" tools for monitoring, tuning,diagnosing, coding, reverse-engineering, creating, editing, debugging, and reporting forOracle databases. It helps Oracle DBAs to maximize the availability, performance andsecurity of their databases. It helps Oracle developers to fine-tune their applications both indevelopment and production phases.

• SQL• SQL * PLUS• PL/SQL• FORMS• REPORTS• MENUS• GRAPHICS• SQL * LOADER• SQL * DBA• ORACLE WEB BROWSER etc.

SQL (STRUCTURED QUERY LANGUAGE)

SQL often referred to as Structured Query Language is a database computerlanguage designed for managing data in relational database management systems (RDBMS),and originally based upon relational algebra. Its scope includes data insert, query, update anddelete, schema creation and modification, and data access control.SQL was one of the first languages for Edgar F. Codd's relational model in his influential1970 paper, "A Relational Model of Data for Large Shared Data Banks" and became the mostwidely used language for relational databases.

SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in theearly 1970s. This version, initially called SEQUEL (Structured English Query Language).The acronym SEQUEL was later changed to SQL. SQL is an ANSI (American NationalStandards Institute) standard.

The basic structure in SQL is the statement. Semicolons separate multiple SQLstatements.

COMPONENTS OF SQL

1. DDLData Definition Language (DDL) statements are used to define the database structure

or schema. Also called SQL-Schema Statements.• 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.• COMMENT - add comments to the data dictionary• RENAME - rename an object• GRANT Statement -- grant privileges on tables and views to other users• REVOKE Statement -- revoke privileges on tables and views from other users

2. DMLData Manipulation Language (DML) statements are used for managing data within

schema objects.• 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• EXPLAIN PLAN - explain access path to data• LOCK TABLE - control concurrency

3. DCLData Control Language (DCL) statements controls access to data and to the database.

• COMMIT Statement -- commit the current transaction• ROLLBACK Statement -- roll back the current transaction• SAVEPOINT – Identify a point in a transaction to which we can later rollback.• SET TRANSACTIION – Change transaction options like what rollback segment to

use.• GRANT - gives user's access rights to database• REVOKE - withdraw access rights given with the GRANT command

4. DQLData query Language (DML) allows getting data from the database and imposingordering upon it. It includes the select statement.

• SELECT Statement -- query tables and views in the database

CREATION AND MANIPULATION OF TABLES USING SQL

1) The CREATE TABLE CommandThe CREATE TABLE statement is used to create a table in a database.

SyntaxCREATE TABLE <table_name>(<column_name1> <data_type>(<size>),<column_name2> <data_type>(<size>),....);

ExampleCREATE TABLE EMP_MSTR(EMPNO NUMBER, FNAME VARCHAR2 (9), LNAME VARCHAR (9));

OutputTable created.

2) The INSERT INTO CommandThe INSERT INTO statement is used to insert new records in a table.

SyntaxINSERT INTO <table_name>( <column_name1>,<column_name2>… )VALUES(<expression>,<expression>...);

Example 1.INSERT INTO EMP_MSTR values(101,'NISHA','CHARAYA');

Output1 row created.

Example 2.INSERT INTO EMP_MSTR values(&no,&fname,&lname);

OutputEnter value for no: 101Enter value for fname: 'Rishika'Enter value for lname: 'Charaya'old 1: INSERT INTO EMP_MSTR values(&no,&fname,&lname)new 1: INSERT INTO EMP_MSTR values(102,'RISHIKA','CHARAYA')

1 row created.

3) The SELECT CommandThe SELECT statement is used to select data from a database. The result is stored in a

result table, called the result-set.

• (All Rows and All Columns)Syntax

SELECT <column_name(s)> FROM <table_name>;OrSELECT * FROM <table_name>;

ExampleSELECT * FROM EMP_MSTR;

OutputEMPNO FNAME LNAME------ --------- ---------

101 NISHA CHARAYA102 RISHIKA CHARAYA103 PINKI PAPREJA

• (Selected Columns and All Rows)Syntax

SELECT <column_name1>, <column_name2>FROM <table_name>;

ExampleSELECT FNAME, LNAME FROM EMP_MSTR;

OutputFNAME LNAME--------- ---------NISHA CHARAYARISHIKA CHARAYAPINKI PAPREJA

• (Selected Rows and All Columns)Syntax

SELECT * FROM <table_name> where <condition>;Example

SELECT * FROM EMP_MSTR WHERE FNAME LIKE 'R%';

OutputEMPNO FNAME LNAME------ --------- ---------

102 RISHIKA CHARAYA

• (Selected Rows and Selected Columns)Syntax

SELECT <column_name1>, <column_name2>FROM <table_name> where <condition>;

ExampleSELECT FNAME FROM EMP_MSTR WHERE LNAME LIKE 'C%';

OutputFNAME---------NISHARISHIKA

4) The DISTINCT ClauseIn a table, some of the columns may contain duplicate values. The DISTINCT

keyword can be used to return only distinct (different) values.Syntax

SELECT DISTINCT <column_name(s)>FROM <table_name>

ExampleSELECT DISTINCT LNAME FROM EMP_MSTR;

OutputLNAME---------CHARAYAPAPREJA

5) The ORDER BY ClauseThe ORDER BY keyword is used to sort the result-set by a specified column. The

ORDER BY keyword sorts the records in ascending order by default. If you want to sortthe records in a descending order, you can use the DESC keyword.

SyntaxSELECT <column_name(s)>FROM <table_name>ORDER BY <column_name(s)> ASC|DESC;

ExampleSELECT * FROM EMP_MSTR ORDER BY FNAME;

OutputEMPNO FNAME LNAME

----- --------- --------101 NISHA CHARAYA103 PINKI PAPREJA102 RISHIKA CHARAYA

6) The UPDATE CommandThe UPDATE statement is used to update existing records in a table.

SyntaxUPDATE <table_name>SET <column1=value>, <column2=value2>,...WHERE some_column=some_value

ExampleUPDATE EMP_MSTR SET FNAME='SAURABH' WHERE EMPNO=102;

Output1 row updated.

7) The DELETE CommandThe DELETE statement is used to delete rows in a table. The WHERE clause specifies

which record or records that should be deleted. If you omit the WHERE clause, all recordswill be deleted.

SyntaxDELETE FROM <table_name>WHERE some_column=some_value;

ExampleDELETE FROM EMP_MSTR WHERE EMPNO=102;

Output1 row deleted.

8) EXAMINING OBJECTS CREATED BY USER• Finding out the tables created by user

This statement is used to determine the tables to which a user has access.Syntax

SELECT * FROM TAB;Example

SELECT * FROM TAB;

OutputTNAME TABTYPE CLUSTERID------------------------------ ------- ----------BONUS TABLECANDIDATE TABLEDEPARTMENT TABLEDEPT TABLEEMP TABLEEMP_MSTR TABLESALGRADE TABLEST_DETAIL TABLE

8 rows selected.

• Displaying the table structureThis statement is used to display information about the columns defined in a table.

SyntaxDESCRIBE <table_name>;

ExampleDESCRIBE EMP_MSTR;

OutputName Null? Type------------------------- -------- ----------------------------EMPNO NUMBERFNAME VARCHAR2(9)LNAME VARCHAR2(9)

9) The ALTER TABLE CommandThe ALTER TABLE statement is used to add, delete, or modify columns in an

existing table.

• To add a column in a tableSyntax

ALTER TABLE <table_name> ADD <column_name> <datatype>;Example

ALTER TABLE EMP_MSTR ADD DateOfBirth date;

OutputTable altered.

• To delete a column in a tableSyntax

ALTER TABLE <table_name> DROP COLUMN <column_name>;Example

ALTER TABLE EMP_MSTR DROP COLUMN DateOfBirth;


• To change the data type of a column in a table (column must be empty to changethe database).

SyntaxALTER TABLE <table_name>MODIFY (<column_name> <new_datatype>(<new_size>));

ExampleALTER TABLE EMP_MSTR MODIFY DEPTNO VARCHAR2 (4);


10) The DROP TABLE CommandThe DROP TABLE statement is used to delete a table.

SyntaxDROP TABLE <table_name>;

ExampleDROP TABLE EMP_MSTR;

OutputTable dropped.

11) The TRUNCATE TABLE CommandWhat if we only want to delete the data inside the table, and not the table itself?Then, use the TRUNCATE TABLE statement.Syntax

TRUNCATE TABLE <table_name>;Example

TRUNCATE TABLE ST_DETAIL;

OutputTable truncated.

12) Creating a TABLE FROM A TABLE with all records from source tableSyntax

CREATE TABLE <table_name>( <column_name>,<column_name>)AS SELECT <column_name>,<column_name>FROM <table_name>

ExampleCREATE TABLE emp1 ( E_ID,Name)AS SELECT empno,fname FROM emp_mstr;


13) Creating a TABLE FROM A TABLE with no records from source tableSyntax

CREATE TABLE <table_name>( <column_name>,<column_name>)AS SELECT <column_name>,<column_name>FROM <table_name> WHERE <false_condition>

ExampleCREATE TABLE emp1 ( E_ID,Name)AS SELECT empno,fname FROM emp_mstr Where 1=2;


DATA CONSTRAINTS ON TABLE

Constraints are used to limit the type of data that can go into a table. Constraints canbe specified when a table is created (with the CREATE TABLE statement) or after the table iscreated (with the ALTER TABLE statement).

1) The PRIMARY KEY ConstraintThe PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values. A primary key column cannot contain NULLvalues.

• PRIMARY KEY constraint on Create tableExampleDrop the Persons table if it already exists.

CREATE TABLE Persons(P_Id int PRIMARY KEY, LastName varchar(10), FirstName varchar(10),Address varchar(20), City varchar(10));


• PRIMARY KEY constraint on Alter tableExampleTo create a PRIMARY KEY constraint on the column when the table is already created.

ALTER TABLE EMP_MSTRADD CONSTRAINT EID PRIMARY KEY (EMPNO)


• To drop a PRIMARY KEY constraintExample

ALTER TABLE EMP_MSTRDROP CONSTRAINT EID


2) The FOREIGN KEY ConstraintA FOREIGN KEY in one table points to a PRIMARY KEY in another table.

• FOREIGN KEY constraint on Create tableExample

CREATE TABLE Orders(O_Id NUMBER PRIMARY KEY,OrderNo NUMBER,P_Id int REFERENCES Persons);


• FOREIGN KEY constraint on Alter tableExampleTo create a FOREIGN KEY constraint on the column when the table is already created.

ALTER TABLE Orders ADD CONSTRAINT fk_PerOrdersFOREIGN KEY (P_Id) REFERENCES Persons(P_Id);


• To drop a FOREIGN KEY constraintExample

ALTER TABLE OrdersDROP CONSTRAINT fk_PerOrders;


3) The UNIQUE ConstraintThe UNIQUE constraint uniquely identifies each record in a database table. Note that

you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraintper table.

ExampleCREATE TABLE STU( S_Id number UNIQUE, Name varchar(15), Address varchar(25),

City Varchar(10) );Output

Table created.

4) The NOT NULL ConstraintThe NOT NULL constraint enforces a column to NOT accept NULL values.

ExampleCREATE TABLE STU( S_Id number NOT NULL, Name varchar(15), Address varchar(25),

City Varchar(10) );Output

Table created.

5) The CHECK ConstraintThe CHECK constraint is used to limit the value range that can be placed in a column.

ExampleCREATE TABLE STU( S_Id number CHECK (S_Id>10), Name varchar(15), Address varchar(25),

City Varchar(10));Output

Table created.

6) The DEFAULT ConstraintThe DEFAULT constraint is used to insert a default value into a column. The default

value will be added to all new records, if no other value is specified.Example

CREATE TABLE STU( S_Id number CHECK (S_Id>10), Name varchar(15), Address varchar(25),

City Varchar(10) DEFAULT 'HISAR' )Output

Table created.

OPERATORS IN SQL

1) ANDThe AND operators is used to filter records based on more than one condition. The

AND operator displays a record if both the first condition and the second condition is true.Example

SELECT * FROM EMP_MSTR WHERE (EMPNO>100) AND (DEPT='D1');

OutputEMPNO FNAME LNAME DEPT

------ --------- --------- ----102 ANNY ARORA D1101 SIMI SEHGAL D1

2) ORThe OR operators is used to filter records based on more than one condition. The OR

operator displays a record if either the first condition or the second condition is true.Example

SELECT * FROM EMP_MSTR WHERE (EMPNO>100) OR (DEPT='D1');


------ --------- --------- ----102 ANNY ARORA D1101 SIMI SEHGAL D1104 NIKI GOYEL D2

3) NOTThe NOT operators is used to display only those records that do not satisfy the

condition specified.Example

SELECT * FROM EMP_MSTR WHERE NOT ((EMPNO>100) OR (DEPT='D1'));


------ --------- --------- ----100 RIMPI MEHTA D3

4) INThe IN operator allows you to specify multiple values in a WHERE clause.

ExampleSELECT * FROM EMP_MSTR WHERE LName IN ('ARORA','MEHTA');


------ --------- --------- ----100 RIMPI MEHTA D3102 ANNY ARORA D1

5) NOT INThe NOT IN operator is the opposite of the IN predicate. This will select all the rows

where values do not match the values in the list.Example

SELECT * FROM EMP_MSTR WHERE LName NOT IN ('ARORA','MEHTA');


------ --------- --------- ----101 SIMI SEHGAL D1104 NIKI GOYEL D2

6) BETWEENThe BETWEEN operator is used in a WHERE clause to select a range of data between

two values.Example

SELECT * FROM EMP_MSTR WHERE EMPNO BETWEEN 100 AND 102;


------ --------- --------- ----100 RIMPI MEHTA D3101 SIMI SEHGAL D1102 ANNY ARORA D1

7) LIKEThe LIKE operator is used in a WHERE clause to search for a specified pattern in a

column. SQL wildcards must be used with the SQL LIKE operator.% A substitute for zero or more characters_ A substitute for exactly one character

ExampleSELECT * FROM EMP_MSTR WHERE FNAME LIKE '_I%';


------ --------- --------- ----100 RIMPI MEHTA D3101 SIMI SEHGAL D1104 NIKI GOYEL D2

BUILT-IN FUNCTIONS IN SQL

AGGREGATE FUNCTIONSThe aggregate functions return a single value, calculated from values in a column.

ACCT_MSTR

ACCT_NO TYPE OPR_MODE OPNDT CURBAL STATUS

CA4 CA SI 05-FEB-10 5000 A

SA45 SA JO 21-FEB-10 15000 A

CA14 CA JO 21-MAY-10 8000 A

1) AVGThe AVG( ) function returns the average value of ‘n’ ignoring null values in a column.Syntax

AVG([<DISTINCT>|<ALL>] <N>)Example

SELECT AVG (CURBAL) “AVERAGE BALANCE” FROM ACCT_MSTR;

OutputAVERAGE BALANCE-----------------------------

9333.33333

2) MINThe MIN ( ) function returns the smallest value of the selected column.Syntax

MIN([<DISTINCT>|<ALL>] <expr>)Example

SELECT MIN (CURBAL) “MINIMUN BALANCE” FROM ACCT_MSTR;

OutputMINIMUM BALANCE-----------------------------

5000

3) MAXThe MAX ( ) function returns the largest value of the selected column.Syntax

MAX([<DISTINCT>|<ALL>] <expr>)Example

SELECT MAX (CURBAL) “MAXIMUM BALANCE” FROM ACCT_MSTR;

OutputMAXIMUM BALANCE-----------------------------

150004) COUNTThe COUNT ( ) function returns the number of rows that matches a specified criteria. Syntax

COUNT([<DISTINCT>|<ALL>] <expr>)Example

SELECT COUNT (ALL ACCT_NO)”NO.OF ACCOUNTS” FROM ACCT_MSTR;

OutputNO.OF ACCOUNTS-------------------------

3

5) SUMThe SUM( ) function returns the total sum of a numeric column.

SyntaxSUM([<DISTINCT>|<ALL>] <N>)

ExampleSELECT SUM (CURBAL) “TOTAL BALANCE” FROM ACCT_MSTR;

OutputTOTAL BALANCE---------------------------

28000

NUMERIC FUNCTIONS

1) ABSThe ABS() function returns the absolute value of ‘n’.

SyntaxABS(n)

ExampleSELECT ABS (-43) "ABSOLUTE" FROM DUAL;

OutputABSOLUTE-----------------

43

2) POWERThe POWER() function returns the m raised to the nth power. N must be an integer.

SyntaxPOWER(m,n)

ExampleSELECT POWER (4, 3) "POWER" FROM DUAL;

OutputPOWER-----------

64

3) ROUNDThe ROUND( ) function returns n rounded to the m places to the right of a decimal

places. If m is omitted, n is rounded to 0 places. m can be negative to round off digits to theleft of the decimal places. m must be an integerSyntax

ROUND(n[,m])Example

SELECT ROUND (140.273,1 ) "ROUND" FROM DUAL;

OutputROUND-----------

140.34) EXP( )

The EXP( ) returns e raised to the nth power, where e=2.71828183.Syntax

EXP(n)Example

SELECT EXP (3 ) "EXP" FROM DUAL;

OutputEXP

--------------20.0855369

5) SQRT( )The SQRT( ) returns square root of n. If n<0, Null.

SyntaxSQRT(n)

ExampleSELECT SQRT (36 ) "SQRT" FROM DUAL;

OutputSQRT

----------6

6) GREATEST( )The GREATEST( ) returns the greatest value in a list of expressions.

SyntaxGREATEST(expr1,expr2,…..,expr_n)

ExampleSELECT GREATEST(36,23,19,69 ) "NUM" FROM DUAL;

OutputNUM

-----------69

6) LEAST( )The LEAST ( ) functuion will return the smallest value among all then values.

SyntaxLEAST(expr1,expr2,…..,expr_n)

ExampleSELECT LEAST(36,23,19,69 ) "NUM" FROM DUAL;

OutputNUM

-----------19

7) MOD( )The MOD ( ) return remainder of a first number divided by second number passed as a

parameter. If 2nd no. is 0, then returns 1st no.

SyntaxMOD(m,n)

ExampleSELECT MOD (15,7)"MOD1",MOD(15.7,7)"MOD2" FROM DUAL;

OutputMOD1 MOD2

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

1 1.7

8) TRUNC( )The TRUNC ( ) return truncated to a certain number of decimal places.

SyntaxTRUNC(number,[decimal_places])

ExampleSELECT TRUNC (125.811,1)"TRUNC1" ,TRUNC(135.812,-2)" TRUNC2"FROM DUAL;

OutputTRUNC1 TRUNC2

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

125.8 100

9) FLOOR( )The FLOOR ( ) returns the largest integer value that is equal to or less than a number.

SyntaxFLOOR(n)

ExampleSELECT FLOOR (24.8)”FLOOR1”, FLOOR (13.15)”FLOOR2” FROM DUAL;

OutputFLOOR1 FLOOR2

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

24 13

10) CEIL( )The CEIL ( ) returns the smallest integer value that is equal to or greater than a

number.

SyntaxCEIL(n)

ExampleSELECT CEIL (24.8)”CEIL1”, CEIL (13.15)”CEIL2” FROM DUAL;

OutputCEIL1 CEIL2

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

25 14

STRING FUNCTIONS1) LOWER( )

The LOWER() function converts the value of a field to lowercase.Example

SELECT LOWER (‘ORACLE’)”LOWER” FROM DUAL;Output

LOWER---------

Oracle

2) UPPER( )The UPPER( ) function converts the value of a field to uppercase.

ExampleSELECT UPPER (‘rdbms’) ”UPPER” FROM DUAL;

OutputUPPER---------RDBMS

3) INITCAP( )The INITCAP( ) function return a string with the first letter of each word in capital.

ExampleSELECT INITCAP (‘relational database’) ”INITCAP” FROM DUAL;

OutputINITCAP------------------------Relational Database

4) SUBSTR( )The SUBSTR( ) function return a position of characters, beginning at character m, and

going up to character n.Example

SELECT SUBSTR (‘SECURE’,3,4) ”SUBSTRING” FROM DUAL;Output

SUBS--------CURE

4) ASCII( )The ASCII( ) function return the ASCII value of the given character.

ExampleSELECT ASCII (‘A’)”ASCII1”, ASCII (‘a’)”ASCII2” FROM DUAL;

OutputASCII1 ASCII2

---------- ----------65 97

5) INSTR( )The INSTR( ) function return the location of a substring in a string.

ExampleSELECT INSTR ('SCT ON THE NET','T')"INSTR1", INSTR ('SCT ON THE NET','T', 1, 2) "INSTR2"FROM DUAL;Output

INSTR1 INSTR2---------- ----------

8 8

6) TRANSLATE ( )The TRANSLATE ( ) function replaces a sequence of characters in a string with

another set of characters.Example

SELECT TRANSLATE ('1sct523','123','7A9') "CHANGE" FROM DUAL;Output

CHANGE------------

7sct5A9

7) LENGTH ( )The LENGTH ( ) function returns the length of a word.

ExampleSELECT LENGTH ('ORACLE') "LENGTH" FROM DUAL;

OutputLENGTH------------

68) RTRIM ( )

The RTRIM ( ) function remove chars from the left to char with initial chars removedup to the first character not in the set. Set defaults to spaces.Example

SELECT RTRIM ('SUNILA','A')"RTRIM" FROM DUAL;Output

RTRIM---------SUNIL

9) LTRIM ( )The LTRIM ( ) function returns char, with final chars removed after the last character

not in the set. Set defaults to spaces.Example

SELECT LTRIM ('NISHA','N')"LTRIM" FROM DUAL;Output

LTRIM---------

ISHA

10) LPAD ( )The LPAD ( ) function returns char1, left-padded to length n with the sequence of

chars specified in char2. Oracle uses blanks by default.Example

SELECT LPAD ('page1', 10,'*')"Lpad" FROM DUAL;Output

Lpad--------------*****page1

11) LPAD ( )The RPAD ( ) function returns char1, right-padded to length n with the sequence of

chars specified in char2. Oracle uses blanks by default.Example

SELECT RPAD ('page1', 10,'*')"Lpad" FROM DUAL;Output

Rpad---------------page1*****

CONVERSION FUNCTIONS

1) TO_NUMBER ( )The TO_NUMBER ( ) function converts char, to a number datatype.

ExampleUPDATE ACCT_MSTR SET CURBAL=CURBAL + TO_NUMBER (SUBSTR (‘$100’, 2, 3));Output

3 rows updated.The value 100 will be added to every accounts balance in ACCT_MSTR table.

2) TO_CHAR ( )The TO_CHAR ( ) function converts a value of a datatype to CHAR.

Example 1SELECT TO_CHAR (17145,'$099,999')"CHAR" FROM DUAL;

OutputCHAR------------$017,145

Example 2SELECT TO_CHAR(OPNDT,'Month DD,YYYY')"new date format" FROM ACCT_MSTRWHERE ACCT_NO='CA4'

Outputnew date format----------------------February 05,2010

DATE FUNCTIONS1) ADD_MONTH ( )

The ADD_MONTH ( ) function returns date after adding the no. of months specifiedin the function.Example

SELECT ADD_MONTHS(SYSDATE,4)”ADD MONTH” FROM DUAL;Output

ADD MONTH-----------------14-MAR-11

2) LAST_DAY ( )The LAST_DAY ( ) function returns the last date of the month specified with the

function.Example

SELECT SYSDATE, LAST_DAY (SYSDATE)"last day “FROM DUAL;Output

SYSDATE last day-------------- --------------14-NOV-10 30-NOV-10

3) MONTH_BETWEEN ( )The MONTH_BETWEEN ( ) function returns no. of months between d1 and d2.

ExampleSELECT MONTHS_BETWEEN ('02-FEB-2009','12-AUG-2008')"MONTHS" FROM DUAL;

OutputMONTHS---------5.6774194

3) NEXT_DAY ( )The NEXT_DAY ( ) function returns the date of first weekday named by char that is

after the date named by date.Example

SELECT NEXT_DAY(’14-NOV-2010’,’SUNDAY’)”NEXT_DAY” FROM DUAL;Output

NEXT_DAY---------------21-NOV-10

3) ROUND ( )The ROUND ( ) function returns a date rounded to a specific unit of measure.

ExampleSELECT ROUND (TO_DATE('01-AUG-2010'),'YYYY')"YEAR" FROM DUAL;

OutputYEAR

--------------01-JAN-11

4) NEW_TIME ( )The NEW_TIME ( ) function returns the date after converting it from time zone 1 to

time zone 2.Example

SELECT NEW_TIME(TO_DATE(‘2010/11/14 01:45’,’YYYY/MM/DD HH24:MI’),’AST’,’MST’)”MST” FROM DUAL;

OutputMST--------------13-NOV-10

5) TO_CHAR ( )The TO_CHAR ( ) function facilitates the retrieval of a date different from the default

format.Example

SELECT TO_CHAR(SYSDATE,’MM-DD-YYYY’) “SYSDATE” FROM DUAL;Output

SYSDATE--------------11-14-2010

6) TO_DATE ( )The TO_DATE ( ) converts a char value into a date value.

ExampleSELECT TO_DATE('4/11/10','MM-DD-YYYY') “DATE1” FROM DUAL;

OutputDATE1--------------11-APR-10

GROUPING DATA FROM TABLES IN SQL

ACCT_MSTR

ACCT_NO TYPE OPR_MODE OPNDT CURBAL STATUS

CA4 CA SI 05-FEB-10 5000 A

SA45 SA JO 21-FEB-10 15000 A

CA14 CA JO 21-MAY-10 8000 A

CA4 SA JO 05-FEB-10 18000 A

CA14 SA SI 05-FEB-10 7000 A

1) The GROUP BY CommandThe GROUP BY statement is used in conjunction with the aggregate functions to

group the result-set by one or more columns.Syntax

SELECT <column_name>, aggregate_function(<column_name>)FROM <table_name>WHERE <column_name> <operator value>GROUP BY <column_name>

ExampleSELECT ACCT_NO"BRANCH NO.",COUNT(CURBAL)"NO. OF CUSTOMER" FROMACCT_MSTR GROUP BY ACCT_NO;

OutputBRANCH NO. NO. OF CUSTOMER------------------- -----------------------------

CA14 2CA4 2SA45 1

2) The HAVING CLAUSEThe HAVING clause was added to SQL because the WHERE keyword could not be usedwith aggregate functions.

SyntaxSELECT <column_name>, aggregate_function(<column_name>)FROM <table_name>WHERE <column_name> <operator value>GROUP BY <column_name>

Example

SELECT ACCT_NO, SUM (CURBAL) FROM ACCT_MSTR GROUP BY ACCT_NO HAVINGSUM (CURBAL)>10000;

OutputACCT_NO SUM(CURBAL)---------- -----------CA14 15100CA4 23100SA45 15100

3) The GROUP BY Using the ROLLUP OperatorThe ROLL UP operator is used to calculate aggregates and super aggregates in a

Group By statement.Example

SELECT ACCT_NO"BRANCH NO.",TYPE,SUM(CURBAL)"TOTAL BALANCE" FROMACCT_MSTR GROUP BY ROLLUP (ACCT_NO,TYPE);

OutputBRANCH NO. TYPE TOTAL BALANCE------------------- -------- -------------------------CA4 CA 5100CA4 SA 18000CA4 23100CA14 CA 8100CA14 SA 7000CA14 15100SA45 SA 15100SA45 15100

533004) The GROUP BY Using the CUBE Operator

The CUBE operator can be applied to all aggregates functions and count with in aGroup By statement.Example

SELECT ACCT_NO"BRANCH NO.",TYPE,SUM(CURBAL)"TOTAL BALANCE" FROMACCT_MSTR GROUP BY CUBE (ACCT_NO,TYPE);

OutputBRANCH NO. TYPE TOTAL BALANCE------------------- -------- -------------------------

53300CA 13200SA 40100

CA4 23100CA4 CA 5100CA4 SA 18000CA14 15100CA14 CA 8100CA14 SA 7000SA45 15100SA45 SA 15100

11 rows selected.

SUB QUERIES AND JOINS IN SQL

STUS_ID NAME ADDRESS CITY

101 ANNY ASHOK VIHAR DELHI

102 PINKI SECTOR 10 HISAR

103 RISHU SECTOR 5 AGRA

104 RIMPY SCHEME 2 DELHI

STU_DETAILS_ID SUBJECT MARKS

101 MATHS 78

101 ENGLISH 88

101 SCIENCE 56

102 MATHS 68

102 ENGLISH 54

102 SCIENCE 65

103 MATHS 64

1) SUB QUERIESA SUB QUERIES is a form of SQL statement that appears inside another SQL

statement. A query result can also be used in a condition of a where clause. In such a case thequery is called a subquery and the complete select statement is called a nested query.

Example 1SELECT S_ID,NAME FROM STU WHERE(S_ID) IN (SELECT S_ID FROM STU_DETAIL);

OutputS_ID NAME---- ----------101 ANNY102 PINKI103 RISHU

Example 2SELECT S_ID, NAME, CITY FROM STU S WHERE EXISTS (SELECT S_ID FROMSTU_DETAIL WHERE S_ID=S.S_ID);

OutputS_ID NAME CITY---- ---------- -------101 ANNY DELHI102 PINKI HISAR103 RISHU AGRA

2) JOINSThe JOIN keyword is used in an SQL statement to query data from two or more tables,

based on a relationship between certain columns in these tables. Tables in a database are oftenrelated to each other with keys.

2.1) The INNER JOINThe INNER JOIN keyword return rows when there is at least one match in both tables.

SyntaxSELECT <column_name(s)>FROM <table_name1>INNER JOIN <table_name2>ON <table_name1>.<column_name>=<table_name2>.<column_name>

ExampleSELECT STU.S_ID, STU.Name,STU_DETAIL.SUBJECTFROM STU INNER JOIN STU_DETAILON STU.S_ID=STU_DETAIL.S_IDORDER BY STU.Name;

OutputS_ID NAME SUBJECT

---------- - -------- ---------------101 ANNY MATHS101 ANNY ENGLISH101 ANNY SCIENCE102 PINKI MATHS102 PINKI ENGLISH102 PINKI SCIENCE103 RISHU MATHS

7 rows selected.

2.2) The LEFT JOINThe LEFT JOIN keyword returns all rows from the left table (table_name1), even if there areno matches in the right table (table_name2).Syntax

SELECT <column_name(s)>FROM <table_name1>LEFT JOIN <table_name2>ON <table_name1>.<column_name>=<table_name2>.<column_name>

ExampleSELECT STU.S_ID, STU.Name,STU_DETAIL.SUBJECTFROM STU LEFT JOIN STU_DETAILON STU.S_ID=STU_DETAIL.S_IDORDER BY STU.Name;


---------- - -------- ---------------101 ANNY MATHS101 ANNY ENGLISH101 ANNY SCIENCE102 PINKI MATHS102 PINKI ENGLISH102 PINKI SCIENCE104 RIMPY103 RISHU MATHS

8 rows selected.

2.3) The RIGHT JOINThe RIGHT JOIN keyword Return all rows from the right table (table_name2), even if

there are no matches in the left table (table_name1).Syntax

SELECT <column_name(s)>FROM <table_name1>RIGHT JOIN <table_name2>ON <table_name1>.<column_name>=<table_name2>.<column_name>

ExampleSELECT STU.S_ID, STU.Name,STU_DETAIL.SUBJECTFROM STU RIGHT JOIN STU_DETAILON STU.S_ID=STU_DETAIL.S_IDORDER BY STU.Name;


---------- - -------- ---------------101 ANNY MATHS101 ANNY ENGLISH101 ANNY SCIENCE102 PINKI MATHS102 PINKI ENGLISH102 PINKI SCIENCE103 RISHU MATHS

7 rows selected.

2.4) The FULL JOINThe FULL JOIN keyword return rows when there is a match in one of the tables.

SyntaxSELECT <column_name(s)>FROM <table_name1>FULL JOIN <table_name2>ON <table_name1>.<column_name>=<table_name2>.<column_name>

ExampleSELECT STU.S_ID, STU.Name,STU_DETAIL.SUBJECTFROM STU FULL JOIN STU_DETAILON STU.S_ID=STU_DETAIL.S_IDORDER BY STU.Name;


---------- - -------- ---------------101 ANNY MATHS101 ANNY ENGLISH101 ANNY SCIENCE102 PINKI MATHS102 PINKI ENGLISH102 PINKI SCIENCE104 RIMPY103 RISHU MATHS

8 rows selected.

SET OPERATIONS

Table 1 : sales2005

person amountJoe 1000

Alex 2000

Bob 5000

Table 2 : sales2006

person amountJoe 2000

Alex 2000Zach 35000

1) UNIONCombines the results of two SELECT statements into one result set, and theneliminates any duplicate rows from that result set.

ExampleSELECT * FROM sales2005UNIONSELECT * FROM sales2006;

OutputPERSONS AMOUNT---------- ----------Alex 2000Bob 5000Zach 35000joe 1000joe 2000

2) UNION ALLCombines the results of two SELECT statements into one result set. it will noteliminate duplicates.

ExampleSELECT * FROM sales2005UNION ALLSELECT * FROM sales2006;

OutputPERSONS AMOUNT---------- ----------joe 1000Alex 2000Bob 5000

Zach 35000joe 2000Alex 2000

6 rows selected.

3) MINUSTakes the result set of one SELECT statement, and removes those rows that are alsoreturned by a second SELECT statement.

ExampleSELECT * FROM sales2005MINUSSELECT * FROM sales2006;

OutputPERSONS AMOUNT---------- ----------Bob 5000joe 1000

4) INTERSECTReturns only those rows that are returned by each of two SELECT statements.

ExampleSELECT * FROM sales2005INTERSECTSELECT * FROM sales2006;

OutputPERSONS AMOUNT---------- ----------Alex 2000

CREATING INDEXES ON A TABLE

INDEXESAn index can be created in a table to find data more quickly and efficiently. The users

cannot see the indexes; they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (becausethe indexes also need an update). So you should only create indexes on columns (and tables)that will be frequently searched against.

1) The CREATE INDEX CommandCreates an index on a table. Duplicate values are allowed:.

SyntaxCREATE INDEX <index_name>ON <table_name> (<column_name>)

ExampleCREATE INDEX PIndex ON Persons (LastName);

OutputIndex created.

2) The CREATE UNIQUE INDEX CommandCreates a unique index on a table. Duplicate values are not allowed.

SyntaxCREATE UNIQUE INDEX <index_name>ON <table_name> (<column_name>)

ExampleCREATE UNIQUE INDEX SIndex ON STU (S_ID);


3) View the USER INDEX informationExample

SELECT index_name,table_name from USER_INDEXES;Output

INDEX_NAME TABLE_NAME--------------------- ------------------PINDEX PERSONSPK_DEPT DEPTPK_EMP EMPSINDEX STUSYS_C003006 DEPARTMENTSYS_C003009 PERSONSYS_C003014 ORDERS

4) The DROP INDEX CommandIndex associated with the table will be removed.

SyntaxDROP INDEX <index_name>;

ExampleDROP INDEX SIndex;

OutputIndex dropped.

5) The REVERSE Key IndexesCreates a reverse key index on a table.

SyntaxCREATE INDEX <index_name>ON <table_name> (<column_name>) REVERSE

ExampleCREATE UNIQUE INDEX SIndex ON STU (S_ID) REVERSE;


6) The ALTER INDEX CommandA reverse key index can be rebuilt into a normal index using REBUILD

NOREVERSE.Syntax

ALTER INDEX <index_name>REBUILD NOREVERSE;

ExampleALTER INDEX SIndex REBUILD NOREVERSE;

OutputIndex altered.

CREATING VIEWS ON A TABLE

VIEWA view is a virtual table based on the result-set of an SQL statement. A view contains

rows and columns, just like a real table. The fields in a view are fields from one or more realtables in the database.We can add SQL functions, WHERE, and JOIN statements to a view and present the data as ifthe data were coming from one single table. View is created to maintain security and toreduce redundancy.

1) The CREATE VIEW CommandSyntax

CREATE VIEW <view_name> AS SELECT <column_name(s)>FROM <table_name> WHERE <condition>GROUP BY <grouping criteria> HAVING <predicate>

ExampleCREATE VIEW student AS SELECT S_ID,NameFROM STU WHERE S_ID>102;

OutputView created.

2) The CREATE OR REPLACE VIEW CommandSyntax

CREATE OR REPLACE VIEW <view_name> AS SELECT <column_name(s)>FROM <table_name> WHERE <condition>GROUP BY <grouping criteria> HAVING <predicate>

ExampleCREATE OR REPLACE VIEW student AS SELECT S_ID, Name, AddressFROM STU WHERE S_ID>102;

OutputView created.

2) The DROP VIEW CommandSyntax

DROP VIEW <view_name>;Example

DROP VIEW student;OutputView dropped.

CREATING SEQUENCE ON A TABLE

SEQUENCES (AUTONUMBER)In Oracle, you can create an autonumber field by using sequences. A sequence is an

object in Oracle that is used to generate a number sequence. This can be useful when youneed to create a unique number to act as a primary key.

1) The CREATE SEQUENCE CommandSyntax

CREATE SEQUENCE <sequence_name>[INCREMENT BY <integer_value>]MINVALUE <integer_value> / NOMINVALUEMAXVALUE <integer_value> / NOMAXVALUESTART WITH valueCYCLE / NOCYCLECACHE <integer_value> / NOCACHE[ORDER / NOORDER];

If you omit the MAXVALUE option, your sequence will automatically default to:MAXVALUE 999999999999999999999999999

ExampleCREATE SEQUENCE supplier_seqMINVALUE 1START WITH 1INCREMENT BY 1CACHE 20;

OutputSequence created.

2) REFERENCES A SEQUENCEExample

SELECT supplier_seq.CurrVal FROM DUAL;

OutputCURRVAL-------------

1Example

SELECT supplier_seq.NextVal FROM DUAL;

OutputNEXTVAL-------------

2

3) The ALTER SEQUENCE CommandSyntax

ALTER SEQUENCE <sequence_name>[INCREMENT BY <integer_value>]MINVALUE <integer_value> / NOMINVALUEMAXVALUE <integer_value> / NOMAXVALUESTART WITH valueCYCLE / NOCYCLECACHE <integer_value> / NOCACHE[ORDER / NOORDER];

ExampleALTER SEQUENCE supplier_seqINCREMENT BY 2CACHE 30;

OutputSequence altered.

4) The DROP SEQUENCE CommandSyntax

DROP SEQUENCE <sequence_name>;Example

DROP SEQUENCE supplier_seq;

OutputSequence dropped.

TRANSACTION CONTROL LANGUAGE (TCL)

A transaction is a set of SQL statements which Oracle treats as a Single Unit. i.e. allthe statements should execute successfully or none of the statements should execute.

ROLLBACK :Rollbacks the state of database to the last commit point.SAVEPOINT :Use to specify a point in transaction to which later you can rollback.

1) The COMMIT CommandTo make the changes done in a transaction permanent issue the COMMIT statement.

SyntaxCOMMIT [WORK] [COMMENT ‘your comment’];

ExampleINSERT INTO STU VALUES (104,'NIKI','ASHOK VIHAR', 'ALWAR');

1 row created.SQL> COMMIT;

OutputCommit complete.

2) The ROLLBACK CommandTo rollback the changes done in a transaction give rollback statement. Rollback restore

the state of the database to the last commit point.Syntax

ROLLBACKExample

SQL> DELETE FROM STU WHERE S_ID=102;1 row deleted.SQL> ROLLBACK;

OutputRollback complete.

3) The SAVEPOINT CommandSpecify a point in a transaction to which later you can roll back.

SyntaxSAVEPOINT <savepoint_name>;

ExampleSQL> insert into STU values (105,'NIVI','BUDH VIHAR', 'SHIMLA');1 row created.SQL> savepoint a;Savepoint created.SQL> insert into STU values (106,'MINI','SECTOR 15', 'HISAR');1 row created.SQL> savepoint b;

Savepoint created.SQL> insert into STU values (107,'EKTA','SECTOR 3B', 'GURGAON');1 row created.SQL> SELECT * FROM STU;

S_ID NAME ADDRESS CITY------- ------------ --------------------- ----------101 ANNY ASHOK VIHAR DELHI102 PINKI SECTOR 10 HISAR103 RISHU SECTOR 5 AGRA104 NIKI ASHOK VIHAR ALWAR105 NIVI BUDH VIHAR SHIMLA106 MINI SECTOR 15 HISAR107 EKTA SECTOR 3B GURGAON

7 rows selected.SQL> ROLLBACK TO A;Rollback complete.SQL> SELECT * FROM STU;

S_ID NAME ADDRESS CITY------- ------------ --------------------- ----------101 ANNY ASHOK VIHAR DELHI102 PINKI SECTOR 10 HISAR103 RISHU SECTOR 5 AGRA104 NIKI ASHOK VIHAR ALWAR105 NIVI BUDH VIHAR SHIMLA

If we give ROLLBACK;Then the whole transactions is roll backed.If we give COMMIT; Then the whole transaction is committed and all savepoints areremoved.

4) The AUTOCOMMIT OptionOracle also supports an AUTOCOMMIT option. With this option set to ON each

individual SQL statement is treated as a transaction an will be automatically commited rightafter it is executed. A user can change the AUTOCOMMIT option by typingSET AUTOCOMMIT ON

orSET AUTOCOMMIT OFF

whereas by typingSHOW ALL

a user can see the current setting for the option (including other ones).

PL/SQLPL/SQL is a database-orientated programming language that extends Oracle SQL with

procedural capabilities. We will review in this lab the fundamental features of the languageand learn how to integrate it with SQL to help solving database problems.PL/SQL is a block-structured language. That is, the basic units that make up a PL/SQL program are logicalblocks, which can contain any number of nested sub-blocks. Typically, each logical blockcorresponds to a problem or subproblem to be solved. Thus, PL/SQL supports the divide-and-conquer approach to problem solving called stepwise refinement.

PL/SQL is structured into blocks and can use conditional statements, loops and branches tocontrol program flow. Variables can be scoped so that they are only visible within the blockwhere they are defined. PL/SQL programs are organized in functions, procedures andpackages. There is a limited support for object-oriented programming. PL/SQL is based onthe ADA programming language.

STRUCTURE OF PL/SQL BLOCK[<Block header>][declare

<Constants><Variables><Cursors><User defined exceptions>]

beginPL/SQL statements>

[exception<Exception handling>]

end;

To display messages, the SERVEROUTPUT should be set to ON.SET SERVEROUTPUT [ON/OFF]

Program 1. To increment the given number

DECLAREa NUMBER := 3;

BEGINa := a + 1;dbms_output.put_line('value of a is ' ||a);

END;

Outputvalue of a is 4

Control Structures

Control structures are the most important PL/SQL extension to SQL. Not only doesPL/SQL let you manipulate Oracle data, it lets you process the data using conditional,iterative, and sequential flow-of-control statements such as IF-THEN-ELSE, FOR-LOOP,WHILE-LOOP, and GOTO. Collectively, these statements can handle any situation.

CONDITIONAL CONTROLOften, it is necessary to take alternative actions depending on circumstances. The IF-

THEN-ELSE statement lets you execute a sequence of statements conditionally. The IF clausechecks a condition; the THEN clause defines what to do if the condition is true; the ELSEclause defines what to do if the condition is false or null.

Program 2. To check the balance of an account and to deduct 100 Rupees if currentbalance is less than the minimum balance.

DECLAREcur_bal number(11,2);macct_no varchar (12);mfine number(4):=100;min_bl constant number(7,2):=5000.00;

BEGINmacct_no:=&macct_no;select curbal into mcur_bal from acct_mstr whereacct_no=macct_no;IF mcur_bal<min_bl THEN

update acct_mstr set curbal=curbal-mfine whereacct_no=macct_no;

END IF;END ;/

OutputEnter value for macct_no: 'CA4'old 7: macct_no:=&macct_no;new 7: macct_no:='CA4';

PL/SQL procedure successfully completed.

ITERATIVE CONTROLLOOP statements let you execute a sequence of statements multiple times. You place

the keyword LOOP before the first statement in the sequence and the keywords END LOOPafter the last statement in the sequence.

SIMPLE LOOPThe SIMPLE-LOOP statement associates a condition with a sequence of statements.

Syntax:LOOP

<Action><Exit criteria>

END LOOP;

Program 3: To display a message when a loop exceeds a particular value.DECLARE

i number:=0;BEGIN

LOOPi:=i+2;EXIT WHEN i>10;

END LOOP;dbms_output.put_line ('loop exit as the value reached'||to_char(i));

END;

Outputloop exit as the value reached 12


WHILE-LOOPThe WHILE-LOOP statement associates a condition with a sequence of statements.

Before each iteration of the loop, the condition is evaluated. If the condition is true, thesequence of statements is executed, then control resumes at the top of the loop

Syntax:WHILE <condition>LOOP

<Action>END LOOP;

Program 4: To find the area of a circle whose radius ranging from (3 to 7) using WHILEloop.

DECLAREpi constant number(4,2):=3.14;radius number(5);area number(14,2);

BEGINradius:=3;

WHILE radius<=7LOOP

area:=pi*power(radius,2);dbms_output.put_line('Radius is '|| to_char(radius));dbms_output.put_line('area is '||area);radius:=radius+1;

END LOOP;END;

OutputRadius is 3area is 28.26Radius is 4area is 50.24Radius is 5area is 78.5Radius is 6area is 113.04Radius is 7area is 153.86


FOR LOOPThe FOR-LOOP statement lets you specify a range of integers, then execute a

sequence of statements once for each integer in the range.

Syntax:FOR variable IN[REVERSE] start .. endLOOP

<Action>END LOOP;

Program 5: To find the reverse of a number using FOR loop.

DECLAREGiven_no varchar(5):='12345';Str_Length number(2);Invert number(5);

BEGINStr_Length:=length(given_no);For cntrl IN reverse 1..str_lengthLOOP

Invert:=invert||substr(given_no,cntrl,1);END LOOP;dbms_output.put_line('the given number is '||given_no);dbms_output.put_line('the invert number is '||invert);

END;

Outputthe given number is 12345the invert number is 54321


SEQUENTIAL CONTROLThe GOTO statement lets you branch to a label unconditionally. The label, an

undeclared identifier enclosed by double angle brackets, must precede an executablestatement or a PL/SQL block. When executed, the GOTO statement transfers control to thelabeled statement or block, as the following example shows:

Syntax:GOTO <codeblock name>;

Program 6: To check whether a student obtained passing marks or not using GOTOstatement.

DECLAREmarks number(2);

BEGINmarks:=&marks;IF marks>=40 then

GOTO test;ELSEdbms_output.put_line('fail');goto e1;END IF;

<<test>>dbms_output.put_line('pass');goto e1;

<<e1>>dbms_output.put_line('result is completed');

END;

OutputEnter value for marks: 85old 4: marks:=&marks;new 4: marks:=85;passresult is completed


SQL> /Enter value for marks: 12old 4: marks:=&marks;new 4: marks:=12;

failresult is completed


Program 7: To print the Prime nos. from 1 to 100.DECLARE

I number;J number;S number;

BEGINdbms_output.put_line('prime no. upto 100');For I in 1..100

Loops:=1;For J in 2..I-1

LoopIf (mod (I, J)=0)thenS:=0;

End if;End loop;If(S=1)then

dbms_output.put_line(I);End if;

End loop;END;

Outputprime no. upto 100123571113171923293137414347535961

67717379838997

Program 8: To print the Fibbonacci series.

DECLAREa number;b number;f number;d number;

BEGINdbms_output.put_line('the fibbonacci series is : ');a:=0;b:=1;f:=1;dbms_output.put_line(a);dbms_output.put_line(b);while(f<=10)

loopd:=a+b;a:=b;b:=d;dbms_output.put_line(d);f:=f+1;

end loop;END;

Outputthe fibbonacci series is :01123581321345589

IMPLEMENTATION OF CURSORS IN PL/SQL

CURSOR

The Oracle engine uses a work area for its internal processing in order to execute an SQLstatement. This work area is private to SQL’s operations and is called a Cursor.

IMPLICIT CURSORProgram 9: Updating a record and displaying user defined messages that shows whetherthe record has been updated or not using SQL%FOUND and SQL%NOTFOUNDattributes.

DECLAREBEGIN

Update emp_mstr set empno=&empno where fname=&fname;If (SQL%FOUND) THEN

dbms_output.put_line('Employee Record Successfully Updated');End if;if (SQL%NOTFOUND)THEN

dbms_output.put_line('Employee does not exist');end if;

END;

OutputEnter value for empno: 104Enter value for fname: 'xyz'old 3: Update emp_mstr set empno=&empno where fname=&fname;new 3: Update emp_mstr set empno=104 where fname='xyz';Employee does not exist


Program 10: Updating a record and displaying user defined messages that showswhether the record has been updated or not using SQL%ROWCOUNT attribute.

DECLAREBEGIN

Update emp_mstr set empno=&empno where fname=&fname;IF (SQL%ROWCOUNT>0) THEN

dbms_output.put_line('Employee Record Successfully Updated');ELSE

dbms_output.put_line('No such Employee Exists');END IF;

END;

OutputEnter value for empno: 104Enter value for fname: 'xyz'old 3: Update emp_mstr set empno=&empno where fname=&fname;new 3: Update emp_mstr set empno=104 where fname='xyz';'No such Employee Exists'


EXPLICIT CURSORProgram 11: Display name and id of all the students present in STU table.

DECLARECURSOR c1 IS SELECT s_id, name FROM stu;sid number(3);sname varchar(10);

BEGINOPEN c1;dbms_output.put_line(' ID NAME');dbms_output.put_line('-----------------------------------');LOOP

FETCH c1 INTO sid, sname;EXIT WHEN (c1%NOTFOUND);dbms_output.put_line(sid||' '||sname);

END LOOP;END;

OutputID NAME--------------------------101 ANNY102 PINKI103 RISHU

Program 12: Fetch name and id of all the students present in STU table and store in atemp table.

DECLARE CURSOR c1 IS SELECT s_id, name FROM stu;sid number(3);sname varchar(10);

BEGINOPEN c1;FOR i IN 1..5 LOOP

FETCH c1 INTO sid, sname;

EXIT WHEN (c1%NOTFOUND);INSERT INTO temp VALUES(sid,sname);COMMIT;

END LOOP;CLOSE c1;

END;

OutputPL/SQL procedure successfully completed.SQL> select * from temp;

SID NAME---------- --------------

101 ANNY102 PINKI103 RISHU

3 rows selected.

PROCEDURES AND FUNCTIONS IN PL/SQL

PROCEDURE/ FUNCTIONSA procedure or function is a logically grouped set of SQL and PL/SQL statement that

perform a specific task. A function must return a value back to the caller.

PROCEDURESyntax

CREATE OR REPLACE PROCEDURE [schema] <procedure_name>(<argument> {IN,OUT,IN OUT} <data_type>, …) {IS, AS}<variable> declaration; <constant> declarations;

BEGIN<PL/SQL subprogram body>;

EXCEPTION<exception PL/SQL block>;

END;

Program 13: To implement the salary of employee through procedure.

CREATE OR REPLACE PROCEDURE Incr(eid number, amt number)salary emp.sal%type;

BEGINSelect sal into salary from emp where empno=eid;If(salary >= 3000) then

Update emp set sal= sal+amt where empno=eid;end if;

END incr;

DECLAREI number:=7788;J number:=500;

BEGINIncr(I,J);dbms_output.put_line('Salary Incremented ');

END;Output

Salary Incremented


Program 14: To insert a new record in EMP_MSTR table.

CREATE PROCEDURE proc1(x EMP_MSTR.EMPNO%TYPE,yEMP_MSTR.FNAME%TYPE,Z EMP_MSTR.LNAME%TYPE) ASBEGIN

insert into emp_mstr(empno, fname,lname) values(x,y,z);END a1;

DECLAREBEGIN

a1(105,'yogesh','aggarwal');dbms_output.put_line('insertion is successfully completed');

END;

OUTPUT

insertion is successfully completedPL/SQL procedure successfully completed.

FUNCTIONSSyntax

CREATE OR REPLACE FUNCTION [schema] <function_name>(<argument> {IN} <data_type>, …) RETURN {IS, AS}<variable> declaration; <constant> declarations;



END;

Program 15: To find out Max number using functions.

CREATE OR REPLACE FUNCTION m( a number, b number, c number)RETURN number IS maximum number;BEGIN

if((a>=b)and(a>=c))thenmaximum:=a;

elseif(b>=c) then

maximum:=b;else

maximum:=c;

end if;end if ;return (maximum);

END m;

DECLAREMaximum number(8);

BEGINMaximum:=m(24,6,12);dbms_output.put_line('maximum no. is '|| Maximum);

END;

Output:maximum no. is 24


DELETING A STORED PROCEDURE OR FUNCTION

Syntax

DROP PROCEDUE/FUNCTION <proc_name>/<func_name>

Example

DROP FUNCTION m;

Output

Function dropped.

EXCEPTION HANDLING IN PL/SQL

EXCEPTION HANDLINGPL/SQL makes it easy to detect and process predefined and user-defined error

conditions called exceptions. When an error occurs, an exception is raised. That is, normalexecution stops and control transfers to the exception-handling part of your PL/SQL block orsubprogram. To handle raised exceptions, you write separate routines called exceptionhandlers.

Program 16: To display user defined message when an error occur while fetching arecord from table EMP.

1 DECLARE2 acct_no int;3 fname varchar2(10);4 empno int;5 bal int;6 BEGIN7 acct_no:=&acct_no;8 select acct_no,fname,empno,curbal into acct_no,fname,empno,bal from

emp_mstr,acct_mstr where e9 INSERT into temp values(bal,acct_no,fname);

10 EXCEPTIOM11 when no_data_found then12 dbms_output.put_line('account no'||acct_no||'is not present in the acct number table');13* END;

SQL> /Enter value for acct_no: 2345old 7: acct_no:=&acct_no;new 7: acct_no:=2345;account no2345is not present in the acct number table


DATABASE TRIGGERS IN PL/SQL

A database triggers is stored PL/SQL program unit associated with a specific databasetable or view. The code in the trigger defines the action the database needs to performwhenever some database manipulation (INSERT, UPDATE, DELETE) takes place.

The database triggers are fires (executed) or called implicitly whenever the table isaffected by any of the above said DML operations.

A Row trigger fires once for each row affected. It uses FOR EACH ROW clause.Statement Trigger fires once, irrespective of number of rows affected in the table.

BEFORE triggers The trigger action here is run before the trigger statement.AFTER triggers The trigger action here is run after the trigger statement.INSTEAD of Triggers provide a way of modifying views that can not be modified directlyusing DML statements.LOGON triggers fires after successful logon by the user and LOGOFF trigger fires at thestart of user logoff.

1) THE CREATE OR REPLACE TRIGGER CommandSyntax

CREATE OR REPLACE TRIGGER [schema] <trigger_name>{BEFORE, AFTER}{DELETE, INSERT, UPDATE [Of column,…]}

ON [schema] <table_name>[REFERENCING {OLD AS old, NEW AS new}][FOR EACH ROW [ WHEN CONDITION]]

DECLARE<variable declarations>;<constant declarations>;



END;

Program 17: Create a Trigger to check whether it’s a holiday or not before inserting.Updating or deleting a record in the EMP table;

2) DELETING A TRIGGERSyntax:

DROP TRIGGER <trigger_name>;Example:

DROP TRIGGER t1;Output:

Trigger dropped.

3) ENABLING A TRIGGER

By default, a trigger is automatically enabled when it is created.

Syntax:ALTER TRIGGER <trigger_name> ENABLE;

Example:ALTER TRIGGER T1 ENABLE;

Output:Trigger altered

All triggers associated with a table can be disabled with one statement using the ALTER TABLE

statement with the DISABLE clause and the ALL TRIGGERS option.

Syntax:ALTER TABLE <table_name> ENABLE ALL TRIGGERS;

Example:ALTER TABLE emp_mstr ENABLE ALL TRIGGERS;


4) DISABLING A TRIGGER

You might temporarily disable a trigger if:

• An object it references is not available.• You need to perform a large data load, and you want it to proceed quickly without

firing triggers.• You are reloading data.

Syntax:ALTER TRIGGER <trigger_name> DISABLE;

Example:ALTER TRIGGER T1 DISABLE;


ALTER TRIGGER Reorder DISABLE;

All triggers associated with a table can be disabled with one statement using the ALTER TABLE

statement with the DISABLE clause and the ALL TRIGGERS option.

Syntax:ALTER TABLE <table_name> DISABLE ALL TRIGGERS;

Example:ALTER TABLE emp_mstr DISABLE ALL TRIGGERS;


lab manual - omgroup.edu.in · of database development in the hands of database administrators (db...

Documents