Oracle 9i Notes

9i SL

Contents

1 2 3 4 5 6

Introduction To DBMS Oracle Data Type Structure Query Language (SQL) Sql* Plus Editing Commands Sql * Plus File Commands Data Retrieval/Query Language (Drl/Dql) (Select) Data Manipulation Language (DML) (Insert, Update, Delete, Merge) Data Dictionary Language (DDL) (Create, Alter, Drop, Truncate, Rename) Data Control Language (DCL) (Grant, Revoke) Transaction Control Language (TCL) (Commit, Rollback, Savepint) Oracle Functions Group By Clause Integrity Constraints Joins Sub Queries Working With Database Objects • Views • Sequences • Inedexes • Synonyms • Cluster Partitions Locking Mechanism More Commands In Oracle 9i Pl/Sql Cursor Exception Handling Procedures Functions PACKAGE And PACKAGE BODY Trigger File Input/Output (I/O) (Utl_File) Oracle Supplied Packages New SQL Function In Oracle 9i Implementing Object Techniques Using Lobs Some Advance Fetures In Oracle 9i Oracle Utilities • Export

7 8 9 10 11 12 13 14 15 16

17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

•

34

Import • Sql Loader Oracle Architecture

Page No

Introduction to DBMS Database: A Database is a collection of inter-related data from which some information can be extract. Database Management System (DBMS) Database Management Systems organize and structure data so that it can be retrieved and manipulating by users and application programmer. Database Model: The data structures and access techniques provided by a particular DBMS are called as Data Model. • Hierarchical • Network • Relational Hierarchical data model: - This model is like a hierarchical tree structure; used to construct a hierarchy of records in the form of nodes and branches. The data elements presnt in the structure of parent/ child relationship. - Every child has one parent. - You can see only one record at a time. - Retrieving the data in a Hierarchical database thus required navigating through the records, Moving up, down, sideways one record at a time. - Example Windows Explorer - Developed By IBM Named as Information Management System (IMS) in 1968 The Drawback of Hierarchical data model - It cannot handle a large data - It’s not support many to many to relation Network data model - The simple structure of a Hierarchical database became a disadvantage when the data had a more complex structure. In an order-processing database, for example, a single order might participate in three different parent/child relationships, linking the order to the customer who placed it, the salesperson who took it, and the product ordered. The Network model extended the Hierarchical model by allowing a record to participate in multiple parent/child relationship Network databases had their disadvantages like Hierarchical database, they where very rigid. The set relationships and the structure of the records had to be specified in advance. The Drawback of Network data model - Changing the database structure typically required rebuilding the entire database. Relational Model • • •

Data is organized in terms of rows and columns in a table knowns as relations The position of a row in a table is of no importance. The intersection of row and column must give a single value and not a set of values.

2

• • •

Column name must be unique Row must be unique It eliminated the explicit parent/child structures from the database, and instead of represented all data in the database as simple row/column tables of data values.

•

A relational database is a database where all data visible to the user is organized strictly as tables of data values, and where all database operations work on these tables.

The publication of the paper “A relational Model of Data for Large Shared Database” by Dr. E.F. Codd in June 1970 in the “Communication of ACM” , set a trend for virgorous and extensive investigation into a theoretical frame work to support further work in the area of Data Modelling. The end result is the Relational Database Management System.

The 12 Rules for an RDBMS (Codd' s Rule) Rule 1: The information Rule All Information is explicitly and logically represented in exactly one way i.e by data values in tables. Rule 2: The rule of guaranteed access Every item of data must be logically addressable by resorting to a combination of a Table name, Primary key and column name. Rule 3: The systematic treatment of null value This rule states that support for null values must be consistent throughout the DBMS, and independent of the data type of the field. Rule 4: The Database Description Rule A description of the database is held and maintained using the same logical structures used to define the data. This allows users to query such information in the same way and using the same language, as they would do for any other data in the database. Rule 5: Comprehensive sub-language Rule There must be at least one language whose statements can be expressed as character strings confirming to some well-defined syntax, which is comprehensive in supporting the following: - Data definition, view Definition, Data Manipulation - Integrity Constraints - Authorization - Transaction Boundaries All the above topics are manageable through Structural Query Language (SQL) statement. Rule 6: The view-updating Rule All views that are theoretically updateable are also updated by the system. A view is a table is non-existent in its own right, but instead derived from one or more base tables. Rule 7: The High Level Insert, Update and Delete Rule The capability to handling a base relation, or infact a derived relation, as a single operand must hold good for all retrieve, update, delete and insert activity. This means that the major DML commands, namely SELECT, UPDATE, DELETE and INSERT must be available and operational on sets of rows in a relation. Rule 8: The physical independence Rule The user access to the database remains logically consistent even if the storage representation is changed.

3

Rule 9: The logical data independence Rule Application programs and terminal activities must remain logically unimpaired whenever information preserving changes of any kind, that are theoretically permitted, are made to the base tables. Rule 10: Integrity independence Rule All integrity constraint defined for a database must be definable in the language and stored in the database as data in tables. The following integrity rules should apply every relational database Entity Integrity: No component of a primary key can have missing values or null values. Referential Integrity: For each distinct foreign key value there must exist a matching primary key value in the same domain. Rule 11: Distribution Rule A RDBMS must have distribution independence. Application running on a non-distributed database must remain logically unimpaired if that data becomes distributed in the context of a distributed relational database. Rule 12: Non-Subversion Rule If an RDBMS supports a lower level language that permits for example, row-at-a-time processing, then this language must not be able to bypass any integrity rules or constraints defined in the higher level, set-at-a-time, relational language. An RDBMS product has to satisfy at least six of the 12 rules of Codd to be accepted as full fledged RDBMS. Entity Relationship Diagram ER Diagram: logical database design (through the papers) One to One _____ | | | | -->--- /\ --<-------\/ One to many _____ | | | | -->--- /\ --<<-------\/ Many to one _____ | | | | -->>--- /\ --<-------\/ Many to Many _____ | | | | -->>--- /\ --<<-------\/

_______ | | | | ------_______ | | | | ------_______ | | | | ------_______ | | | | -------

Degree of Relationship One to one; (1:1) - one department - one HOD

4

One to many: (1:M) - One HOD - many Teacher Many to one (M:1) - Many teachers - HOD Many to Many (M:N) - Many Courses - Many students Normalization: It’s a process of efficiently organizing data in a database. There are two goals of Normalization Process: eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. Description of Normalization Forms: First Normal Form (1NF) sets the very basic rules for an organized database: - Eliminate duplicative columns from the same table. - Create separate tables for each group of related data and identity each row with a unique column (Primary Key) Second Normal Form (2NF) further addresses the concept of removing duplicative data: - Remove subsets of data that apply to multiple rows of a table and place them in separate rows. - Create relationships between these new tables and their predecessors through the use of foreign keys. Third Normal Form (3NF) goes one large step further: - Removes columns that are not dependant upon the primary key. Finally Forth Normal Form (4NF), also known as Boyce- Codd normal form (BCNF) has one requirement: - A relation is in BCNF if and only if determinant is a candidate key. Relational Database Management System (RDBMS) A Relational Database Management System (RDBMS) is an information system that presents information as rows contained in a collection of tables, each table possessing a set of one or more columns. Object-Oriented Relational Database Management System (OORDBMS) An Object-Oriented Relational Database Management System (OORDBMS) integrates a DBMS with the concepts of object-oriented programming. It tries to collate the persistence of a DBMS with the expressiveness of an object-oriented programming language. An Object- oriented databse thus stores persistent objects permanaetly on secondary storage. An OORDBMS permits these objects to be shared among different applications by provididng necessary DBMS functions such as indexing, concurrencey control and recovery. The main advantage of using object – orriented concepts to design is that databse is that such a databse is fully compatible with object – oriented applications and systems. Morever, an OORDBMS is oriented towards operations on single objects. RDBMSs are very inefficient in their performance with single objects. Primary key: In a well- designed relational database every table has some column or combination of columns whose values uniquely identify each row in the table. This column is called the primary key. Foreign Key: A column in one table whose value matches the primary key in some other table is called as a foreign key.

5

Oracle DATA Type S.I

Data Type

Description

1

CHAR

Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters.

2

NCHAR

Fixed-length character data of length size characters. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.

3

VARCHAR2

Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters.

4

NVARCHAR2

Variable-length character string having maximum length size characters. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes.

5

NUMBER (p,s)

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.

6

INTEGER

Stores integer numbers. An Integer number does not contains a floating point.

7

DATE

Valid date range from January 1, 4712 BC to December 31, 9999 AD.

8

LONG

Character data of variable length up to 2 gigabytes, or 231 -1 bytes.

9

BLOB

A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).

10

CLOB/NCLOB

A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size). NCLOB: - Stores national character set data.

11

RAW (size)

Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.

12

LONG RAW

Raw binary data of variable length up to 2 gigabytes. LONG RAW Can be used to stores graph, sound, documents or arrays of binary data.

13

BFILE

Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.

New Data Type in Oracle 9i 14

TIMESTAMP Allow the time to be stored as a data with fraction of seconds. (fractional_seconds_pr fractional_seconds_precision is the number of digits in the fractional ecision) part of the SECOND datetime field.

15

TIMESTAMP All values of TIMESTAMP as well as time zone displacement value, (fractional_seconds_pr where fractional_seconds_precision is the number of digits in the ecision) WITH TIME fractional part of the SECOND datetime field. ZONE

6

16

TIMESTAMP All values of TIMESTAMP WITH TIME ZONE, with the following (fractional_seconds_pr exceptions: ecision) WITH LOCAL • Data is normalized to the database time zone when it is stored TIME ZONE in the database. • When the data is retrieved, users see the data in the session time zone.

17

INTERVAL YEAR Stores a period of time in years and months, where year_precision is (year_precision) TO the number of digits in the YEAR datetime field. Accepted values are 0 MONTH to 9. The default is 2.

18

INTERVAL DAY Allows time to be stored as an interval of days to hours, minutes and (day_precision) TO second. Useful in presenting the precise difference between two date SECOND time values (fractional_seconds_pr ecision)

New Datatypes in 10g 19

BINARY_FLOAT

Stores a single precision 32-bit floating-point number.

20

BINARY_DOUBLE

Stores a single precision 64-bit floating-point number.

Example of The New data Type In Oracle 9i TIMESTAMP

timestamp with time zone

TIMESTAMP (fractional_se conds_precisi on) WITH LOCAL TIME ZONE INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

SQL> create table emp23 (empno number(2), start_date timestamp(7)) ; SQL> insert into emp23 values (23,'02-may-2005') ; SQL> select * from emp23 ; EMPNO START_DATE --------------------------------------------------------------------------23 02-MAY-20 05.00.00.0000000 AM SQL> create table emp23 (empno number(2), start_date timestamp with time zone) ; SQL> insert into emp23 values (23,'15-nov-04 09:34:34 AM') ; SQL> select * from emp23 ; EMPNO START_DATE --------------------------------------------------------------------------23 15-NOV-04 09.34.34.000000 AM +05:30 SQL> create table emp23 (empno number(2), order_date timestamp with local time zone) ; SQL> insert into emp23 values (23,'15-Nov-2004 09:34:24 AM') ; SQL> select * from emp23 ; EMPNO ORDER_DATE --------------------------------------------------------------------------23 15-NOV-04 09.34.24.000000 AM SQL> create table emp23 (empno number(2), loan_duration interval year (3) to Month) ; SQL> insert into emp23 values (23,interval '120' month(3)) ; SQL> select to_char(sysdate+loan_duration,'dd-mon-yyyy') from emp23 ; TO_CHAR(SYS -----------------12-may-2015 SQL> create table emp23 (empno number(2), day_duarion interval day (3) to second) ; SQL> insert into emp23 values (23,interval '180' day(3)) ; SQL> select sysdate+day_duarion from emp23 ; SYSDATE+D --------08-NOV-05

7

Structure Query Language (SQL) - SQL is a database access, nonprocedural language - ANSI Standard - Keyword cannot be abbreviated - Users describe in SQL what they want done, and the SQL language compiler automatically generates a procedure to navigate the database and perform the desired task

SQL Language SELECT INSERT, UPDATE, DELETE, MERGE CREATE, ALTER, DROP, RENAME, TRUNCATE COMMIT, ROLLBACK, SAVEPOINT GRANT, REVOKE

Data retrieval Language Data Manipulation Language Data Definition Language Transaction Control Data Control Language

To open the isqlplus  http://localhost/isqlplus Desc[ribe] EMP

Desc[ribe] Dept

Desc[ribe] Locations

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOCATION_ID NUMBER(3)

LOCATION_ID LOC_NAME

NUMBER(4) VARCHAR2(10) VARCHAR2(9) NUMBER(4) DATE NUMBER(7,2) NUMBER(7,2) NUMBER(2)

NUMBER(3) VARCHAR2(13)

SQL> select * from emp ; EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

7902

17-DEC-80

800

7499

ALLEN

SALESMAN

7698

20-FEB-81

1600

300

30

7521

WARD

SALESMAN

7698

22-FEB-81

1250

500

30

7566

JONES

MANAGER

7839

02-APR-81

2975

7654

MARTIN

SALESMAN

7698

28-SEP-81

1250

7698

BLAKE

MANAGER

7839

01-MAY-81

2850

30

7782

CLARK

MANAGER

7839

09-JUN-81

2450

10

7788

SCOTT

ANALYST

7566

19-APR-87

3000

20

7839

KING

PRESIDENT

17-NOV-81

5000

10

7844

TURNER

SALESMAN

7698

08-SEP-81

1500

7876

ADAMS

CLERK

7788

23-MAY-87

1100

20

7900

suresh

DBA

7698

03-DEC-81

9000

30

7902

FORD

ANALYST

7566

03-DEC-81

4000

20

7934

MILLER

CLERK

7782

23-JAN-82

1300

10

20

20 1400

0

30

30

8

SQL> select * from dept; DEPTNO

DNAME

LOCATION_ID

10

ACCOUNTING

100

20

RESEARCH

200

30

SALES

300

40

OPERATIONS

400

SQL> select * from locations; LOCATION_ID

LOC_NAME

100

NEW YORK

200

DALLAS

300

CHICAGO

400

BOSTON

SQL* PLUS EDITING COMMANDS SQL> L SQL> select empno from emp ; SQL> A , Job SQL > L SQL> select empno , job from emp ; -----------------------SQL > select * from emp ; SQL > c/emp/dept SQL> L SQL > select * from dept ; --------------------------------------------------------------------------------SQL * PLUS File Commands SQL > SAVE filename SQL> save c:\suresh\ss.sql replace SQL > GET filename SQL > START filename SQL > @ filename SQL > edit filename SQL > Spool on ; SQL > Spool filename SQL > Spool off ; SQL > EXIT

Data Retrieval/Query Language (DRL/DQL) (SELECT) General SQL Commands

9

- Display all the rows and columns in EMP table SQL> Select * from emp ; - Display all the Employees of their EMPNO, name, designation ,salary and department no from EMP table SQL> select EMPNO,ENAME,JOB,SAL,DEPTNO from emp ; ARITHMETRIC EXPRESSION Display all the Employees of their EMPNO, name, adding $1000 to everybodies salary from EMP table with changing the salary column heading SQL> select empno,ename,sal + 1000 "Total Salary" from emp; SQL> select empno,ename,sal + 1000 as TotalSalary from emp; SQL> select empno,ename,sal + 1000 TotalSalary from emp; - Putting 0 in place of null values SQL> select empno,ename,sal, sal + nvl(comm,0) "Total Salary" from emp; Concatenation Operator - Concating employee no and name column SQL> select empno||' '||ename from emp ; SQL> select ename ||' having Employee Is '||empno from emp; Duplicate Row SQL> select distinct deptno from emp; SQL> select distinct job from emp ; Limiting the Row Selected by comparison condition SQL> select * from emp where empno=7369; SQL> select empno,ename,sal from emp where sal > 1000; SQL> select empno,ename,sal from emp where sal >= 1000; SQL> select empno,ename,sal from emp where sal <= 1000; SQL> select empno,ename,sal from emp where sal != 1000; Logical condition SQL> select * from SQL> select * from SQL> select * from SQL> select * from

emp emp emp emp

where where where where

sal > 1000 and job='SALESMAN' ; sal > 1000 or job='SALESMAN' ; job in ('SALESMAN','CLERK') ; job not in ('SALESMAN','CLERK') ;

Other Comparison Condition SQL> select * from emp where comm is null and mgr is null ; SQL> select empno,ename,sal from emp where deptno in (10,20); SQL> select empno,ename,sal from emp where deptno not in (10,20) ; SQL> select empno,ename,sal from emp where job in ('CLERK','ANALYST'); SQL> select empno,ename,sal from emp where job not in ('CLERK','ANALYST'); SQL> select * from emp where sal = any (select sal from emp where deptno=20) ; SQL> select empno,ename,sal from emp where ename like 'S%' ; SQL> select empno,ename,sal from emp where ename not like 'S%' ; SQL> select empno,ename,sal from emp where sal between 1000 and 2000 ; SQL> select empno,ename,sal from emp where sal not between 1000 and 2000 ; --------------------------------------------------------------------------------Data Manipulation Language (DML) (INSERT, UPDATE, DELETE, MERGE) - Inserting rows to dept table

10

SQL> insert into dept (deptno,dname,loc) values (50,'DDDD','dddd') ; SQL> insert into dept (deptno,dname,loc) values (&deptno, &dname, &loc) ; SQL> insert into dept values (50,'DDDD','dddd') ; Copy the record from dept to dept1 table SQL> insert into dept select * from dept1 ; SQL> insert into dept select * from dept1 where deptno=30 ; Updating/Changing the existing employee’s information SQL> update emp set sal=sal*2 ; SQL> update emp set sal=sal*2 where deptno=40 and sal > 2000 ; SQL> update emp1 set sal = sal * 2 where deptno in (select deptno from emp1 where deptno=20) ; - Deleting all rows from emp1 table SQL> delete from emp1 ; Or SQL> delete emp1; Delete the rows from emp1 table whose depart no is 20 SQL> delete from emp1 where deptno = 20 ; Delete with subqury SQL> delete from emp1 where deptno in (select deptno from emp1 where deptno=30) ; - Merging Rows create table copy_emp (EMPNO NUMBER(4), ENAME VARCHAR2(10), SAL NUMBER(7,2), DEPTNO NUMBER(2)) ; Merge into copy_emp c using emp1 e on (c.empno= e.empno) WHEN MATCHED THEN UPDATE SET C.ename = e.ename , c.sal= e.sal, c.deptno = e.deptno WHEN NOT MATCHED THEN INSERT VALUES (e.empno,e.ename,e.sal,e.deptno); ------------------------------------------------------------------------DATA DICTIONARY LANGUAGE (DDL) ( CREATE, ALTER, DROP, TRUNCATE, RENAME) Creating a table create table Employee (empno number(2), ename varchar2(30), sal number(10,2) ) ; ------------------------------------- create a create table student (rollno number(4), sname varchar2(20), DOB date, Branch varchar2(20)); Altering a table by using Add, Modify, Drop, Rename alter table student add address varchar(100) ;

11

alter table stdeunt add (address1 varchar(100), fname varchar2(30)) ; alter table student modify (address varchar2(50), address1 varchar2(50)) ; - Dropping a column in student table alter table student drop column address1 ; - To drop multiple columns at a time is: Alter table student drop (FNAME, branch) ; - To renaming a column Alter table emp rename column ename to empname ;

- To seeting the Unused columns SAL in EMP table Alter table emp set UNUSED COLUMN Sal;

- Dropping the unused columnfrom the EMPtable Alter table emp DROP UNUSED COLUMNS; Dropping a table Drop table tablename ; DROP table tname ; Drop table emp1 ; Truncating a Table truncate table dept; Renaming a table Rename oldtablename to newtabalename; Rename emp to emp1 ; DATA CONTROL LANGUAGE (DCL) (GRANT, REVOKE) Database Security and Previleges Create 2 users, open the screen simultaneously, The synatx for creating a user SQL> SQL> SQL> SQL> SQL> SQL>

Create user user1 identified by user1 Alter user user1 identified by user13 ; Grant Connect, resource to user1 ; Create user user2 identified by user2 ; Alter user user1 identified by user23 ; Grant Connect, resource to user2 ;

The Grant Command - The Grant command is used to grant access to the database - A user can grant access to his database object(s) to other user(s) user1 -Granting the select previledge to user2 SQL> GRANT select on emp to user2 user2 User2 is viewing the user1 ‘s emp table select * from user1.emp ;

12

user1 GRANT update on emp to user2 user2 - update user1.emp set ename='ffff' where empno=7900 ; REVOKE Using the REVOKE command, a DBA can revoke database privileges from the user(s) user1 User1 is revoking the select previledge from user2 on emp table Revoke select on emp from user2 user2 - select * from user1.emp ; user1 revoke update on emp from user2 user2 - update user1.emp set ename='ffff' where empno=7900 ; revoke select,update on emp from suresh with grant option ; DBA can give Grant create table, create session to suresh with admin option; in suresh grant create table to user1 ; TRANSACTION CONTROL LANGUAGE (TCL) (Commit, Rollback, Savepint) The COMMIT Command The commit command is used to make changes to data (DML) permanently SQL> Commit; The ROLLBACK Command The ROLLABCK command is used to discard parts or all the work the user has done in the current transaction To undo the previos command from after the commit . SQL> Rollback; The SAVEPOINT Command Savepoints statements are used to discard or commit all the changes upto a point SQL> Savepoint a ; SQL> Rollback to savepoint a ; ORACLE FUNCTIONS Oracle functions serve the purpose of manipulating data items and returning a result. Functions are also capable of accepting user-supplied variables or constants and operating on them. Such variables or constants are called arguments. Like function_name (arguments1, arguments2,…..) Oracle Function can be clubbed together depending upon whether they operate on a single row or a group rows retrieved from a table. Accordingly, functions can be classified as follows: Group Functions (aggregate function)

13

Functions that act on a set of values are called set of values are called Group Functions. For example, SUM, is a function, which calculates the total set of numbers. A group function returns a single row for a group of queries rows.

Scalar Functions (Single Row Functions) Functions that act on only one value at a time are called Scalar Functions. For example, LENGTH, is a function, which calculates the length of one particular string value. A single row function returns one result for every row of a queried table or view. Aggregate function Select max(sal) from emp ; Select min(sal) from emp ; Select avg(sal) from emp ; select count(*) from emp ; Select sum(sal) from emp ; Numeric Functions select select select select select

abs(-15) from dual ; power(3,2) from dual ; sqrt(25) from dual ; round(10.4567,2) from dual ; trunc(10.4567,2) from dual ;

String Functions select select select select select select select select select select select select

length('SURESH') from dual ; lower('Sql Cousre') from dual ; lower(ename) from emp ; upper(ename) from emp ; initcap(ename) from emp ; concat('Hello','World') from dual ; concat(empno,ename) from emp ; substr(ename,1,3) from emp; instr('helloworld','w') from dual ; ename,instr(ename,'A') from emp ; lpad(sal,10,'*') from emp ; rpad(sal,10,'*') from emp ;

select select select select select select

ascii('s'),ascii('S') from dual ; ename from emp where rowid=CHARTOROWID('AAAH2CAABAAAPAAAAK') ; chr(67) from dual; chr(67)||chr(65)||CHR(84) from dual; dump('abc',1016) from dual ; greatest('ASSASA','DDD','CCCC') from dual ;

Date Functions select empno,hiredate,months_between(sysdate,hiredate) from emp ; select empno,hiredate,add_months(hiredate,3) from emp ; select empno,hiredate,next_day(hiredate,'FRIDAY') from emp ; select empno,hiredate,last_day(hiredate) from emp ; select empno,hiredate,months_between(sysdate,hiredate),add_months(hiredate,3), next_day(hiredate,'FRIDAY'), last_day(hiredate) from emp ; USING CASE Expression

14

select ename,mgr, case job when 'SALESMAN' then 2*sal when 'CLERK' then 3*sal when 'PRESIDENT' then 4*sal when 'MANAGER' then 5*sal else sal*3 end "Revised Salary" from emp ; USING DECODE Function select ename,mgr,sal, decode (job, 'SALESMAN' , 2*sal, 'CLERK' , 3*sal, 'PRESIDENT' , 4*sal, 'MANAGER', 5*sal, sal) "Revised Salary" from emp; Conversion Functions TO_DATE: Converts character field to a date type TO_CHAR: date type to character type TO_CHAR: number type to character TO_NUMBER: Converts character to a number data type select to_date('01/01/2005','mm/dd/yyyy') from dual ; select to_date('23-05-2005','dd-mm-yyyy') from dual ; select to_char(sysdate,'dd/mm/yyyy') from dual ; SELECT TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual ; select to_number('3456') +1 from dual ; select to_number('3456') +1 from dual ;

Group By Clause The Group By clause is used with SELECT to combine a group of rows based on the values of a particular column or expression. Aggregate functions rea used to return summary information for each group. The aggregate functions are applied to the individual groups. - List the department no and number of employees in each department no SQL> select deptno,count(*) from emp group by deptno ; - List the department no and maxumim salary of employees in each department no select deptno,max(sal) from emp group by deptno ; select deptno,job, max(sal) from emp group by deptno,job ; select deptno,job, max(sal) from emp group by deptno,job ; select deptno,job, max(sal) from emp group by deptno,job order by deptno,job ; select deptno,job,sum(sal) from emp group by rollup(deptno,job) ; select deptno,job,sum(sal) from emp group by cube(deptno,job) ; select empno,ename,job,sal,deptno, rank() over (partition by deptno, job order by sal desc) "R" from emp; select lower(ename),upper(ename) from emp ; select upper(rtrim(ename)) + ' working as a ' + upper(rtrim(job)) from emp ; select user, uid from dual; level Command-->

15

select level, empno, ename, mgr from emp start with mgr is null connect by prior empno=mgr Order by level; The Having Clause HAVING clause is used to specify which group are to be displayed, that is, restrict the groups that you return on the basis of aggregate functions. -

List the average salary for all department employing more than five people.

select deptno, max(sal) from emp group by deptno having count(*)> 5 Note: The WHERE clause cannot be used to restrict the groups that are returned. We can only use where to restrict individual rows. -- List the total salary, maximum and minimum salary and the avrage salary of emplyees job wise department no 20 and sipaly only those rows having average salary greater than 1000. The output should be arranged in the descending order of sum(sal). SQL> select job,sum(sal), min(sal), Max(sal), Avg(sal) from emp where deptno=20 group by job having avg(sal) > 1000 order by sum(sal); Order By Clause ORDER BY Clause will arrange the final result in the order specified select select select select

empno,ename,sal empno,ename,sal empno,ename,sal empno,ename,sal

from from from from

emp emp emp emp

order order order order

by by by by

ename asc ; ename desc; empno asc, ename desc; 3,2,1 ;

Integrity Constraints • • • • •

Constraints are enforced on data being stored in a table, are called Constraints. Constraints super control the data being entered into a table for permanent storage. Constraints are preferred Rules applied on thable columns while creating or after creation These are automatically activated whenever DML statement is performed on a table Provies high security

Integrity Constraints are three types 1.Entity Integraty: - check for uniqueness of data ex- primary key, Unique 2. Domain Constraint : - Check for conditional rules ex:- Check, Not null 3. Referential Constraint : Used bind relation between tables NOT NULL: Prevent a column from accepting NULL values UNIQUE : Ensures uniqueness of the values in a column PRIMARY KEY : Same as UNIQUE, but only one column per table is allowed CHECK: Controls the value of a column(s) being inserted DEFAULT: Assigns a default value for the column(s), at the time of insertion when no value is given for that column REFERENCES: Assigns a Foreign Key constraint to maintain “Referential Integrity” ON DELETE CASCADE: Using this option whenever a parent row is deleted then all the corresponding child rows are deleted from the details Tbale. This option is always used with foreing key. Example Create a table using Primary Key and Not null constraints

16

create table employee (empno number(4), ename varchar2(30) not null, Job varchar2(30) unique , deptno number(2) not null, constraint Emp_pk_id primary key (empno)) ; - For foreign key , create the master table first and after that child table. create table department (deptno number(2) primary key , dname varchar2(30) not null, location varchar2(30)) ; - Creating a table using foreign key, primary key, unique , check and default constraint create table employee (empno number(4) primary key, ename varchar2(30) not null, Job varchar2(30) unique , sal number(5,2) check (sal > 0) , DOJ date default sysdate, deptno number(2) , constraint EmpNO_fk foreign key (deptno) references department(deptno) on delete cascade) ; Note: If a user is not giving the constraint name, Oracle automatically create a constraint named as “SYS_C0 (some number)” Dropping the Primary key constraint SQL> alter table employee drop primary key ; or alter table employee drop constraint constraintname; alter table employee add constraint Dept_pk primary key (deptno) ; To see details about the infomration of constraints SQL> desc user_constraints SQL> select constraint_name,constraint)type table_Name=’TABLENAME’ ;

from

user_constraints

where

JOINS Joins, the information from any number of tables can be accessed. To join two tables, the retrieval criteria will typically specify the condition that a column in the first table (which is defined as a foreign key) is equal to a column in the second table (which is the prrimary key

Types of Joins Oracle Proprietary Joins (8i and Prior) - Equi join -Cartesian Joins - Non-equi join - Outer Join - Self Join

SQL: 1999 Complaint Joins: - Cross Joins - Natural Joins - Using Clause Joins - Inner Joins - Full or two side outer joins - Joins with the ON Clause

17

Equi –Joins When two tables are joined together using equality of values in one or more columns, they make an Equi Join. This is also called as Simple Joins or Inner Joins - Primary Key and Foreign Key select emp.empno,emp.ename,dept.dname from emp, dept where emp.deptno=dept.deptno ; Using AND Operator select emp.empno,emp.ename,dept.dname from emp, dept where emp.deptno=dept.deptno and emp.deptno=20 ; Using Table Aliases select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno and e.deptno=20 ; Joining More Than two Tables select e.empno,e.ename,d.dname, l.loc_Name from e.deptno=d.deptno and d.location_id=l.location_id ;

emp

e,

dept

d,

Locations

l

where

Cartesian Joins When no join condition clause is specified in WHERE clause, each row of one matches every row of the other table. This results in a Cartesian product. SQL> select * from emp, dept ; If the number or rows in emp Table is 14 and dept table has 4 , then the total number rows produced is 56. Non-Equi-joins: select e.name,e.sal,j.grade from emp e , salgrade j where e.sal between j.losal and j.hisal ; Using (+) operator Outer-joins If there are any values in one table that do not have corresponding value(s) in the other, in an euqi join that row will not be selected. Such rows can be forcefully selected by using the outer join symbol (+). The corresponding columns for that will have NULLs. select emp.empno,emp.ename,dept.dname from emp, dept where emp.deptno (+) = dept.deptno ; select emp.empno,emp.ename,dept.dname from emp, dept where emp.deptno = dept.deptno (+) ; Self-joins To Join a table itself means that each row of the table is combined with itself and with every other row of the table. The self join be viwed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as through it were. select WORKER.ENAME, MANAGER.ENAME “Manager” from emp worker, emp manager where worker .mgr=manager.empno ; Cross Join select ename,dname from emp cross join dept ; select ename,dname from emp, dept ; Natural Join - The NATURAL JOINS clause is based on all columns in the two tables that have same name

18

select deptno,dname,location_id,loc_name from dept natural join locations ; select deptno,dname,location_id,loc_name from dept natural join locations where deptno in (10,20) ; Creating Joins with the USING Clause - Do not use a table name ot alias in the referenced columns - The natural join and using Clauses are mutually exclusive select l.loc_name , d.dname from locations l join dept d using (location_id) where location_id=100 ; you can not write l.location_id = 1400 - same as equi joins Creating Joins with the ON Clause - The join condition for the NATURAL JOIN is basically an equijoin of all columns with the same name. select e.empno,e.ename,e.deptno from emp e join dept d ON (e.deptno=d.deptno) ; select e.ename, m.ename from emp e Join emp m on (e.empno=m.mgr) ; Creating Three-Way Joins with the ON Clause select e.empno,e.ename,d.dname,l.loc_name from emp e join dept d ON (e.deptno=d.deptno) join locations L ON (d.location_id=l.location_id) ; Inner Joins Select e.ename,e.deptno,d.dname from emp e inner join dept d on (e.deptno=d.deptno) ; Left Outer Joins Select e.ename,e.deptno,d.dname from emp e left outer join dept d on (e.deptno=d.deptno) ; earlier: select emp.empno,emp.ename,dept.dname from emp, dept where dept.deptno (+)= emp.deptno ; Right Outer Joins select e.ename,e.deptno,d.dname from emp e right outer join dept d on (e.deptno=d.deptno) ; earlier: select emp.empno,emp.ename,dept.dname from emp, dept where dept.deptno = emp.deptno (+) ; Full Outer Joins select e.ename,e.deptno,d.dname from emp e full outer join dept d on (e.deptno=d.deptno) ; Set Operator Set Operators are used to combine information of similar type from one or more than one table Data types of corresponding columns must be the same The types of SET operators in ORACLE are: Union: - Rows of first query plus rows of second query, less duplicate rows union all - Rows of first query plus rows of second query, with duplicate rows Intersection – Common rows frm all the queies Minus – Rows unique to the first query UNION select * from emp union select * from emp1 ; UNION ALL select * from emp union all select * from emp1; INTERSECT select * from emp intersection select * from emp1;

19

MINUS select * from emp minus select * from emp1;

SUB QUERIES Sub query: A sub query is a SELECT statement is embedded in a clause of another SELECT statement. You can build powerful statements out of simple ones by using sub queries. They can be very useful when need to select rows from a table with a condition that depends on the data in the table itself: You can place the sub query in a number of SQL clause, including - The WHERE Clause - The HAVING Clause - The FROM Clause - The sub query (inner query) executes once before the main query. - The result of the sub query is used by the main query (Outer query). Comparison conditions falls into two classes: Single row Operators (<, >, =, >=, <=, <>) Multiple row operators (IN, ANY, ALL) Single Row Sub Queries select ename,job from emp where deptno = (select deptno from emp where empno=7902) ; select ename,sal from emp where sal > (select sal from emp where ename='SMITH') and deptno = (select deptno from emp where ename='SMITH') ; Using Group Function in a Subquery select ename,sal from emp where sal > (select avg(sal) from emp); Having Clause with Sub queries select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20) ; Using Sub Query in the From Clause select e.ename,e.sal,e.deptno,b.salavg from emp e, (select deptno, avg(sal) salavg from emp group by deptno) b where e.deptno=b.deptno and e.sal > b.salavg ; Multiple Row Sub Queries IN - Equal to any members in the list ANY - Compare Value to each value returned by the subquery ALL - Compare Value to every value returned by the subquery (IN) select ename,sal from emp where sal in (select min(sal) from emp group by sal) ; select sal from emp where sal < all (select avg(sal) from emp ) ; (ANY) select ename,sal from emp where sal < any (select min(sal) from emp group by sal) ;

20

(ALL) select empno,ename,job,sal from emp where sal < all (select sal from emp where job='MANAGER') ; select ename,sal from emp where sal < any (800,1600) order by sal ; select ename,sal from emp where sal < any (1200,1600) ; -----select ename,sal from emp where sal = any (select min(sal) from emp group by sal) ; select ename,sal from emp where sal < all (1000,2000,3000) ; select empno,ename,sal from emp where any < (1200,1600); Correlated Sub Query Correlated sub queries are used for row-by-row processing. Each sub query is executed once for every row of the outer query. SQL> SELECT ENAME, SAL, DEPTNO FROM EMP OUTER WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO=OUTER.DEPTNO ) ; SELECT EMPNO,ENAME,DEPTNO FROM EMP A WHERE A.DEPTNO= (SELECT DEPTNO FROM DEPT WHERE DEPTNO=A.DEPTNO); SQL> SELECT ENAME, SAL, DEPTNO FROM EMP OUTER WHERE EXISTS (SELECT * FROM EMP WHERE DEPTNO= OUTER.DEPTNO) SQL> SELECT ENAME, SAL, DEPTNO FROM EMP OUTER WHERE NOT EXISTS FROM EMP WHERE DEPTNO= OUTER.DEPTNO);

(SELECT *

EXISTS Operator   

This operator is used to check for the existence of values This operator produces a Boolean result It takes a subquery as an argument and evaluates it to True, if the subquery produces any opuput and False., if the subquery does not produce any output.

List all employees who have atleast one person reporting to them SQL> Select empno,ename,job,deptno from emp e where EXISTS (select empno from emp where emp.mgr=e.empno) ; List the employees details who do not manage any one. SQL> select ename,job from emp e where NOT EXISTS (select mgr from emp where mgr=e.empno) ;

WORKING WITH DATABASE OBJECTS VIEWS VIEWS are Database Objects whose contents are derived from another table - A VIEW Contains no data of its own - The command for creating VIEW is CREATE VIEW command - The changes in the tables are automatically reflected in the VIEWS A VIEW is like a ‘windows’ through which data can viewed or changed. Advantages of a VIEW - To restrict data access - To make complex queries easy

21

- To provide data independence - To present different views of the same data A Simple View is one that: - Derives data from only one table - Contains no functions or groups of data - Can perform DML operations through the view A Complex view is one that: - Derives data from many tables - Contains funcions or groups of data - Does not always allow DML opearations through the view Simple View SQL> SQL> SQL> SQL>

create view vname as select empno,ename,deptno from emp ; create view EmpView as select empno,ename,sal from emp where empno=7900 ; select * from EmpView ; Drop view EMPPVIEW1 ;

Insert, Update and Delete statement in a Simple View SQL>insert into vname (empno,ename,sal,deptno) values (4567,'XYZ',3456,50) ; SQL>update vname set ename='FFFF' where empno=4567 ; SQL>delete from vname where empno=4567 ; Complex View - Creating a View using Group by function SQL> create or replace view emppview1 as select deptno, count(*) total from emp group by deptno ; SQL> select rownum as rank, ename,sal from (select ename,sal from emp order by sal desc) where rownum <= 3 ; alter view vname as select empno,ename,sal,deptno from emp ; create view vname1 as select e.ename,e.deptno,d.dname from emp e left outer join dept d on (e.deptno=d.deptno); VIEWS with Check Option create view vname as select empno,ename,deptno from emp where deptno=10 with check option ; insert into vname values (2345,'fffff',10) ; Views with Read Only create view vname as select empno,ename,deptno from emp where deptno=10 with read only ; Creating a Force view - Creating a view with out the table existing Create a view first, and then create a table sql> create force view bcd as select * from blank ; SQL> Create table blank ( a number) ; SQL> insert into blank values (33) ; SQL> insert into blank values (33) ; SQL> insert into blank values (33) ; SQL> select * from bcd ;

22

SQL> DESC bcd

Dropping a View SQL> Drop view viewname ;

SEQUENCES A Sequence: -

Automatically generates unique numbers Is a sharable object Is typically used to create a primary key value Replaces application code Speeds up effeciency of accessing sequence values when cached in memory

SQL> create sequence Dept_seq increment by 10 start with 120 MAXVALUE 9999 ; SQL> select sequence_name,min_values,max_value, user_sequences ; SQL> select Dept_seq .currval from dual ; SQL> select Dept_seq .nextval from dual ; SQL> Drop sequence Dept_seq ;

increment_by,

last_number

from

INDEXES An Index: - Is used by the Oracle Server to speed up the retrieval of rows by using pointer. - Can reduce disk I/O by using a rapid path access method to locate data quickly. Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition. Manually: Users can create non-unique indexes on column to speed up access to the rows. Oracle Database supports several types of index: • • • • •

Normal indexes. (By default, Oracle Database creates B-tree indexes.) Bitmap indexes, which store rowids associated with a key value as a bitmap Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions. Domain indexes, which are instances of an application-specific index of type indextype

SQL> CREATE INDEX EMPIDX on emp (empno) ; FUNCTION - BASED INDEXES - A function-based index is an index based on expressions. SQL> Create Index Upper_dept_idx on dept (upper(deptno)) ; SQL> Drop Index Upper_dept_idx ;

23

BIT MAP INDEXES SQL> CREATE BIT MAP INDEX EMPIDX on emp(sex) ; Dropping a Index SQL> Drop Index ;

SYNONYMS Simplify access to objects by creating a synonym. - Ease referring to a table owned by another user - Shorten lengthy object names SQL> Create synonym dept for scott.dept; SQL> select * from dept; SQL> DROP synonym dept;

CLUSTER Clustering is a method of storing tables that are intimately related and often join together into the same area on disk. It requires at least one cluster column from each of the tables. These must be of the same data types and size, but are not required to possess identical names. For the tables in a cluster, rows with identical column values are kept together on disk in the same area, the same logical block(s). This clauses improvement of performance when the cluster columns are the columns by which the tables are generally joined. SQL> create cluster emp_add (empno number(4) ) ; SQL> create table emp2 (empno number(4) primary key, ename varchar2(30), address varchar2(20)) cluster emp_add (empno) ; SQL> create table emp_address (empno number(4) primary key, address varchar2(20)) cluster emp_add (empno) ; SQL> create table emp_address1 (empno number(4) primary key, address varchar2(20)) cluster emp_add (empno) ; SQL> drop cluster emp_add including tables ; SQL> create index emp_address on cluster emp_add ; -

Delete the cluster item

SQL> drop index emp_address ;

24

PARTITIONS Storing partitions in separate tablespace enables you to: ----Reduce the possibility of data corruption in multiple partitions ----Back up and recover each partition independently ----Improve manageability, availability, and performance ----Partitioning is transparent to existing applications and standard DML statements run against partitioned tables. However, an application can be programmed to take advantage of partitioning by using partitionextended table or index names in DML. Partitioning Method There are several partitioning methods offered by Oracle Database: - Range partitioning - Hash partitioning - List partitioning Range Partitioning Use range partitioning to map rows to partitions based on ranges of column values. SQL> create table emp345 (empno number(3) primary key, ename varchar2(30), sal number(8,2), deptno number(2)) Partition by range (deptno) (Partition p1 values less than (10) tablespace tbs1, Partition p2 values less than (20) tablespace tbs2, Partition p3 values less than (30) tablespace tbs3, Partition p4 values less than (40) tablespace tbs4) List Partitioning Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column in the description for each partition. SQL> CREATE TABLE Dept_part (deptno number, deptname varchar2(20), state varchar2(2)) PARTITION BY LIST (state) (PARTITION q1 VALUES ('OR', 'WA') tablespace tbs1, PARTITION q2 VALUES ('AZ', 'UT', 'NM') tablespace tbs2, PARTITION q3 VALUES ('NY', 'VM', 'NJ') tablespace tbs3, PARTITION q4 VALUES ('FL', 'GA') tablespace tbs4); SQL> select tablespace_name, partition_name from table_name='emp_sub_template' order by tablespace_name;

dba_tab_subpartitions

where

Hash Partitioning

25

Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key. It controls the physical placement of data across a fixed number of partitions and gives you a highly tunable method of data placement. To Create HASH Partition SQL> create table purchases (inv_no number , day number, month number, year number) partition by hash (day, month, year) (partition h1 tablespace ts1, partition h2 ta blespace ts2, partition h3 tablespace ts3); Adding a Partition to a Partitioned Table alter table emp345 add partition p5 values less than (50) tablespace tbs4 ; Truncating Partitions alter table emp345 truncate partition p5 ; Dropping a Table Partition Alter table emp345 drop partition p5; Merging Partitions ALTER TABLE emp345 MERGE PARTITIONS p1 , p2 into PARTITION p6 ; Moving a Partitions ALTER TABLE emp345 MOVE PARTITION p4 tablespace tbs4 ; Renaming a Table Partition ALTER TABLE emp345 rename PARTITION p4 to p45 ; Coalescing a Partitioned Table ALTER TABLE emp345 COALESCE PARTITION;

LOCKING MECHANISM Locks : Locks are of two types, 1. Row Level Locks 2. Table Level Locks --> Row Level Locks: This lock is used to lock either a single or group of records, for the purpose of updations. A Row Level Lock is implemented with in a select query using "for update of " clause. Ex: Select * from emp where empno=7788 for update of comm,sal; note: another user cannot manipulate comm&sal of 7788,for the rest of the numbers & entities he can do manipulation Ex: Select * from emp for update of comm, sal; Note: another user cannot manipulate comm&sal all the numbers, for the rest of the entities he can do manipulation Ex: Select * from EMP for update;

26

note: another user cannot manipulate all the entities of the table Note: only manipulations r not allowed but he can retrive values (select)of the table --> Table Level Locks: These are further classified into three caregories... -> Share Lock -> Share Update Lock -> Exclusive Lock -> Share Lock : This is a sharable lock, i.e., multiple users can implement this sharable lock on a single table at the same time. note: the 2nd user who didn`t lock the table which is locked by the first user should wait uptill the 1st user`s end transaction. when it ends 2nd user can use it for manipulations, but the 1stuser need not wait for the 2nd user`s end transaction E.g. Lock table emp in share mode; -> Share Update Lock: Even though this lock fall under Table Level locks, but still is used to lock records, to use this lock first we need to lock the required records using row level lock and then implement this lock. E.g. Lock table emp in share update mode; -> Exclusive Lock : When a user locks a table in Exclusive mode, no other user can access that table except the user who has locked it in Exclusive mode. E.g. Lock table emp in Exclusive Mode;

More Commands In Oracle 9i Substitution Variables Temporarily Store Values - Single ampersand (&) - Double ampersand (&&) - DEFINE command Using the & Substitution Variable For Integer select empno,ename from emp where empno = &empno ; For Character or date select empno,ename from emp where job = '&job' ; Specifying Column Names, Expressions and Text select empno,ename,job,&column_name from emp where &condition order by &Order_column ; Using DEFINE Command DEFINE empno=7902 ; select empno,ename,sal,deptno from emp where empno= &empno ; Using the && Substitution Variable

27

select empno,ename,job,&&column_name from emp order by &column_name ; Using Verify Commands set verify on ; select empno,ename,sal,deptno from emp where empno=&empno ; SQL Sample Report set feedback off TTITLE 'EMployee|Report' BTITLE 'Confidential' BREAK ON JOB COLUMN JOB HEADING 'JOB|Cattegory' COLUMN ename HEADING 'EMPLOYEE NAME' COLUMN sal HEADING 'SALARY' select job,ename,sal from emp where sal < 5000 order by job,ename,sal set feedback on COLUMN JOB clear COLUMN ename clear COLUMN sal clear clear Break Using Sub query in an INSERT Statements insert into (select empno,ename,job,deptno from emp where deptno=20) values (2222,'ddddd','MANAGER',30) ; Using Sub query in an INSERT Statements with check option insert into (select empno,ename,job,deptno from emp where deptno=20 with check option) values (2222,'ddddd','MANAGER',30) ; Using Explicit Default Values insert into emp (empno,ename,sal,hirdate) values (222,'dddd',3333,default) ; Performing TOP - N Analysis select rownum as rank, ename,sal from (select ename,sal from emp order by sal desc) where rownum <= 3 ; Unconditional INSERT ALL INSERT ALL INTO sal_history values (empno,hiredate,sal) INTO mgr_history values (empno,mgr,sal) select empno,hiredate,sal,mgr from emp where empno > 1000 ; Conditional INSERT ALL INSERT ALL WHEN SAL > 10000 then INTO sal_history values (empno,hiredate,sal) WHEN MGR > 200 THEN INTO mgr_history values (empno,mgr,sal) select empno,hiredate,sal,mgr from emp where empno > 1000 ;

28

Pivoting Insert INSERT ALL INTO sales_info INTO sales_info INTO sales_info INTO sales_info INTO sales_info

values values values values values

(empno,week_id,sales_mon) (empno,week_id,sales_TUE) (empno,week_id,sales_WED) (empno,week_id,sales_THU) (empno,week_id,sales_FRI)

select empno,week_id,sales_mon,sales_tue,sales_wed,sales_thu,sales_fri from sales_source_data ;

PL/SQL Introduction to PL/SQL -PL/SQL required for data processing -It is a procedural language extension to the non-procedural SQL. Blocks of PL/SQL code are passed to and processed by a PL/SQL engine. PL/SQL Architecture

Benefits of PL/SQL -

PL/SQL is portable. You can declare variable. You can program with procedural language control structures. PL/SQL can handle errors. Improved Performance

Benefits of Subprogram 1. 2. 3. 4.

Easy Maintenance Improved data security and integrity. Improved performance. Improved code clarity.

PL/SQL Block Structure DECLARE (Optional) Variables, cursor, user-defined exception

29

Begin (Mandatory) - SQL statements - PL/SQL statements Exception (Optional) Actions to perform when errors occur End; Declaring PL/SQL Variables := (assign operator) DECLARE v_hiredate date; v_deptno number(2) := 10 ; Naming Rules DECLARE memp_id number(4):= 1000 ; begin select empno into mempno from emp where empno=1000; end ; Base Scalar Data Types -

CHAR VARACHAR2 LONG LONG RAW NUMBER BOOLEAN

Scalar Values Declaration Declare V_job Varchar(10) ; V_orderdate date := sysdate + 7 ; v_valid BOOLEAN NOT NULL := TRUE ; Using %TYPE - The database table and column - The previously declared variable name - Declaring Variables with the %TYPE Attribute v_Name emp.ename%type ; v_job emp.job%type ; Using %ROWTYPE The %ROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table or fetched by a cursor (declared later). In case, variables for the entire row of a table need to be declared, then instead of declaring them individually, the attribute %type is used EMP_ROW_VAR EMP%ROWTYPE: Here, the variable EMP_ROW_VAR will be a composite variable, consisting of the column names of the table as its members. To refer to a specifiec variable, sal SAL, the following syntax will be used: EMP_ROW_VAR.sal := 2000 ;

30

Using BIND Variables VARIABLE g_salary number ; BEGIN select sal into :g_salary from emp where empno=7902 ; end ; Print g_salary For Printing the values :- DBMS_OUTPUT.PUT_LINE set serveroutput on ; DEFINE p_anual_sal = 60000 ; Declare v_sal begin v_sal = v_sal /12 ; dbms_output.put_line('The monthly salary' || v_sal ) ; end ; Commenting Code /*

*/

declare V_ename emp.empno%type ; V_sal emp.sal%type; v_conat varchar2(30; begin v_conat := V_ename || V_sal ; end ; Nested Block Declare x number; begin Declare Y Number; Begin ......... End ; ....... end ; Some Sample Progamme using PL/SQL Block declare x number:=45 ; y number:=56 ; z number ; begin z:= x+y ; dbms_output.Put('the value of z is '||x) ; dbms_output.Put_line('the value of z is '||y) ; dbms_output.Put_line('the value of z is '||z) ; end;

31

Operators in PL/SQL --Logical --Arithmetic --Concatenation Same as SQL -----------------------Select Statement in PL/SQL --------------------------- The Into clause declare mempno emp.empno%type ; mename emp.ename%type ; begin select empno,ename into mempno,mename from emp where empno=7902; dbms_output.Put_line('The sasasasasa'); end ; Manipulating data Using PL/SQL Make Changes to database tables by using tables. -

Insert delete Update Merge

Insert command using begin insert into dept1 (deptno,dname,loc) values (50,'EDP','America') ; end; Update command using declare V_sal_inc emp.sal%type := 200 ; begin update emp set sal = sal + v_sal_inc where deptno= 50 ; end; Delete command using declare V_deptno emp.deptno%type := 50 ; begin delete from emp where deptno= v_deptno ; end; ------------------------------------------------------------------------declare v_deptid dept1.deptno%type := 50 ; begin delete from dept1 where deptno=v_deptid ; dbms_output.put_line (sql%rowcount || 'Row deleted') ; end ;

32

Conditional and Iterative Control The conditional control available with PL/SQL are: IF-THEN –ELSE statement The type of Loops available with PL/SQL 1) Basic Loop 2) While Loop 3) For Loop IF STATEMENTS Syntax: IF then <statement1> elsif then <statement1> else <statement1> end if; Example : if v_name='KING' then v_job := 'President' ; elsif v_name='JONES' then v_job := 'Manager' ; else v_job := 'Clerk' ; end if ; -------------------------------------declare mempno emp.empno%type:=&empno ; msal emp.sal%type; a varchar2(100) ; begin select sal into msal from emp where empno=mempno; if msal > 100 and msal <= 2000 then a:='Salary is between 1000 and 2000'; elsif msal > 2000 and msal <= 3000 then a:='Salary is between 2001 and 3000'; else a:='Salary is greater than 3000' ; end if ; dbms_output.put_line(a) ; end ;

Basic Loop The syntax is; LOOP <Statement> exit [loop-label] [WHEN Condition] end loop ; Example: declare i number:=1 ; begin

33

loop dbms_output.put_line(i) ; i := i+1 ; exit when i > 10 ; end loop; end ; WHILE Loop The syntax is; WHILE [condition]LOOP <Statement> end loop ; Example declare i number:= 1 ; begin while I < 11 loop dbms_output.put_line(i) ; i := i+1 ; end loop; end ; FOR Loop The Syntax is : FOR IN .. LOOP <Statement> End loop Example declare i number ; begin for i in 1..10 loop dbms_output.put_line(i) ; end loop; end ; Using CASE EXPRESSIONS declare v_grade char(1) := upper( '&V_grade') ; v_appraisal varchar2(30) ; begin v_appraisal := CASE v_grade WHEN 'A' then 'Execllent' WHEN 'B' then 'Very Good' WHEN 'C' then 'Good' else 'No Such Grade' end; dbms_output.put_line ('Grade: ' ||v_grade|| ' Appraisal ' ||v_appraisal) ; end; -----Same program but we can write some other way declare v_grade char(1) := upper( '&V_grade') ;

34

v_appraisal varchar2(30) ; begin v_appraisal := CASE WHEN v_grade ='A' then 'Execllent' WHEN v_grade ='B' then 'Very Good' WHEN v_grade = 'C' then 'Good' else 'No Such Grade' end; dbms_output.put_line ('Grade: ' ||v_grade|| ' Appraisal ' ||v_appraisal) ; end; Using GOTO Statement declare x number; y number; z number; begin x:=&x; y:=&y; z:=x+y; if z>=50 then goto pqr; else dbms_output.put_line('Wellcome'); end if; <> dbms_output.put_line('fjbfbfbb'); end;

Cursor The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL’s operations and called a Cursor. Two Types Cursor A) Implicit: Declared for all DML and PL/SQL SELECT statements B) Explicit: Declared and named by the programmer. Controlling Explicit Cursors 1. Open the Cursor 2. Fetch a row. 3. Close the Cursor -Declaring the Cursor CURSOR emp_cursor is Select empno,ename from emp ; -Opening the Cursor OPEN emp_cursor ; -Fetching Data from the Cursor Fetch emp_cursor into v_empno,V_ename,V_sal; -Closing the Cursor Close Emp_cursor ; Explicit Cursor Attributes

35

%ISOPEN - Evaluates TRUE if the Cursor Open %NOTFOUND - Evaluates TRUE if the most resent fetch does not return a row %FOUND - Evaluates TRUE if the most recent fetch return a row %ROWCOUNT - Evaluates to the total number of rows returned so far. Examples Using BASIC LOOP declare v_empno emp.empno%type; v_sal emp.sal%type ; v_ename emp.ename%type ; cursor emp_cursor is select empno,ename,sal from emp ; begin Open emp_cursor ; LOOP fetch emp_cursor into v_empno,V_ename,v_sal ; dbms_output.put_line('The Employee No: ' ||v_empno||' and name is ' ||v_ename|| ' The salary is' ||v_sal); exit WHEN emp_cursor%rowcount > 10 or emp_cursor%notFOUND; End LOOP ; end; Using FOR LOOP declare v_empno emp.empno%type; v_sal emp.sal%type ; cursor emp_cursor is select empno,sal from emp; begin open emp_cursor; for i in 1..10 loop fetch emp_cursor into v_empno,v_sal; dbms_output.put_line (v_empno||' end loop; Close emp_cursor; end;

'||v_sal);

Same Program using %notfound (Implicit Cursor ) declare v_empno emp.empno%type; v_sal emp.sal%type ; cursor c1 is select empno,sal from emp; begin open c1; for i in 1..10 loop fetch c1 into v_empno,v_sal; exit when c1%notfound; dbms_output.put_line (v_empno||' '||v_sal); end loop; end; ---------------------------------------------------------------------------declare mename emp.ename%type ; msal emp.sal%type ; mjob emp.job%type;

36

mempno emp.empno%type; cursor c1 is select empno,ename,job,sal from emp ; begin open c1; loop fetch c1 into mempno,mename,mjob,msal; if msal < 2000 then update emp set sal=msal*2 where empno=mempno; dbms_output.put_line(mempno||' '||mename||' '||msal*2||' '||mjob) ; elsif msal between 2000 and 4000 then update emp set sal=msal*.5 where empno=mempno; dbms_output.put_line(mempno||' '||mename||' '||msal*.5||' '||mjob) ; elsif msal > 4000 then update emp set sal=msal*.2 where empno=mempno; dbms_output.put_line(mempno||' '||mename||' '||msal*.2||' '||mjob) ; end if ; exit when c1%notfound ; end loop ; close c1 ; end ; FOR LOOP Declare cursor c1 is select empno,ename,deptno from emp; begin for emp_record in c1 loop if emp_record.deptno=20 then dbms_output.put_line('Employee No ' ||emp_record.empno emp_record.ename); end if; end loop; end;

||'

Name

is

'||

Implicit Cursor Attributes SQL%ISOPEN - Is always false because Oracle automatically close an Implicit cursor after executing its SQL statement SQL%NOTFOUND - Evaluates TRUE if the DML statement was not suceesful %FOUND - Evaluates TRUE if the DML statement was suceesful %ROWCOUNT – Returned to the total number of rows affected by an INSERT,UPDATE, DELETE or single row SELECT OPEN, FETCH, CLOSE : cannot be used to maipulating the implicit cursor SQL

Cursor with parameter -Pass parameter values to a cursor when the cursor is opened and the query is executed -Open an explicit cursor several times with a different active set each time. Syntax CURSOR cursor_name (parameter name data type) is select statement Begin Open cursor_name (parameter values) End ; - Paremeter data types are the same as scalar data type but do not give them sizes. Example:

37

declare mename emp.ename%type; mempno emp.empno%type; cursor emp_cursor (mdeptno number, mjob varchar2) is select empno,ename from emp where deptno=mdeptno and job=mjob; begin for emp_record in emp_cursor (30,'SALESMAN') loop fetch emp_cursor into mempno,mename ; dbms_output.put_line(mename||' '||mempno); end loop ; end; --------------------------------Cursor with Subqueries Subquery returns a value or set of values to outer query. Syntax Cursor emp_cur is select empno, ename, sal from EMP where deprno in (select deptno from dept) Defining REF Cursor Type - Explicit cursor is a static cursor - It is always refers only one work area in memory - REF CURSOR is dynamic cursor - It is like a pointer in C - It refer different work area in memory at run time more than one SQL statement can be associated to ref cursor at run time. - Cursor variable is like a pointer define with REF type Types of REF CURSOR STRONG REF CURSOR – the REF CURSOR which is return type WEAK REF CURSOR – the REF CURSOR without return type - Define a REF cursor type: Type ref_type_name is REF CURSOR - Declare a variable of the type ref_cv ref_type_name ; STRONG REF CURSOR Declare Type ref_c is ref cursor return emp%rowtype; C1 ref_c ; Vrec emp%rowtype; Vdno number(2) := &vdno ; Begin If vdno =10 then Open c1 for select * from emp where job=’MANAGER’ ; Elsif vdno = 20 then Open c1 for select * from emp where job=’SALESMAN’ ; Else Open c1 for select * from emp where job=’CLERK’ ; End if; Loop Fetch c1 into vrec ; Exit when c1%notfound; dbms_output.put_line(vrec.empno||’ ‘||vrec.ename) ;

38

end loop; close c1; end; WEAK REF CURSOR declare v_empno emp.empno%type; v_sal emp.sal%type ; v_ename emp.ename%type ; mdname dept.dname%type; type r is ref cursor; emp_cursor r; begin Open emp_cursor for select empno,ename,sal from emp ; LOOP fetch emp_cursor into v_empno,V_ename,v_sal ; dbms_output.put_line('The Employee No: ' ||v_empno||' and name is ' ||v_ename|| ' The salary is ' ||v_sal); exit WHEN emp_cursor%rowcount > 10 or emp_cursor%notFOUND; End LOOP ; Open emp_cursor for select dname from dept ; LOOP fetch emp_cursor into mdname; dbms_output.put_line('The dept name is ' ||mdname); exit WHEN emp_cursor%rowcount > 10 or emp_cursor%notFOUND; End LOOP ; end;

EXCEPTION HANDLING An Exception is an identifier in PL/SQL that is raised during execution that terminates main body of action. EXCEPTION is raised by two methods - An Oracle error occurs and then associated exception is raised automatically. - Using RAISE statement within the block, the exception being raised may be user defined or predefined EXCEPTION is three type 1) Pre Defined Exception 2) User-Defined Exception 3) Non-Pre Defined Exception 1) Pre Defined Exception The predefined oracle package standard has defined exceptions for certain common errors. Exceptions are handled using names not using error numbers. The errors which are not handles using exception, can be handled usinf exception ‘OTHERS’ Examples Too_many_rows No_data_found Zero_divide others Some Sample Programs Using Pre-Defined Exceptions 1) Using no_data_found Exception declare mename emp.ename%type; msal emp.sal%type;

39

begin select ename,sal into mename,msal from emp where empno=7902 ; if sql%rowcount > 0 then dbms_output.put_line(mename||msal) ; end if; exception when no_data_found then dbms_output.put_line('Invaild empno') ; end; 2) Using zero_divide Exceptions declare x number(2):=22 ; y number(2):=0 ; z number ; begin z:=x/y ; dbms_output.Put_line(z) ; exception when zero_divide then dbms_output.Put_line('The values is divided by Zero') ; end; 3) Using too_many_rows Exception declare mename emp.ename%type ; msal emp.sal%type ; begin select ename,sal into mename,msal from emp where deptno=30 ; dbms_output.Put_line('The emp name is '||mename||' salary is '||msal) ; exception when too_many_rows then dbms_output.Put_line(' Too many rows returned') ; end; 4) Using too_many_rows and others Exception declare x emp.sal%type; y emp.deptno%type:=&deptno; begin select sal into x from emp where deptno=y; dbms_output.put_line('sal of empno is='||x); dbms_output.put_line('wellcome'); exception when too_many_rows then dbms_output.put_line('more then one error selected'); when others then dbms_output.put_line('no record found'); end; User Defined Exception User defined exceptions must be - declared in the declare section of a PL/SQL Block - Raised Explicitely with RAISE statements Some Sample Programs Using User Defined Exceptions 1) declare

40

v_empno number(4):=7902 ; E_invalidDept Exception; begin delete from emp where empno=v_empno ; if SQL%NOTFOUND Then RAISE E_invalidDept; end if ; Exception WHEN E_invalidDept then DBMS_OUTPUT.PUT_LINE('Wrong EMPNO') ; end ; 2) declare mename emp.ename%type ; msal emp.sal%type ; emp_exc exception ; begin select ename,sal into mename,msal from emp where empno=7902 ; if msal > 4000 then Raise emp_exc ; else dbms_output.Put_line(msal) ; end if; exception when emp_exc then dbms_output.put_line('salary is greater than 4000') ; end; RAISE_APPLICATION_ERROR Procedure: Is used to communicate a predefined exception interactively by defining a non standard error code and error message. RAISE_APPLICATION_ERROR can be used in either the executable section or exception section of a PL/SQL program. 1) declare v_empno number(4):=&empno ; begin delete from emp where empno=v_empno ; if SQL%NOTFOUND Then RAISE_APPLICATION_ERROR (-20202,'wrong empno') ; end if ; end ; 2) declare x emp.sal%type; y emp.empno%type:=&empno; sal_exc exception ; begin select sal into x from emp where empno=y; if x > 3000 then raise sal_exc; else dbms_output.put_line('sal of empno is='||x); dbms_output.put_line('wellcome'); end if; exception when sal_exc then raise_application_error(-20001,'Salary is greater than 3000') ;

41

when others then dbms_output.put_line('no data found'); end;

Non-Predefined Exception Using PRAGMA EXCEPTION_INIT The technique that is used to bind a number exception handler to a named using Pragma Exception_init(). This binding of a numbered of a numbered exception handler, to a name (i.e. String), is done in the declare section of a PL/SQL block. The Pragma action word is a call to a pre-compiler, which immediately binds the numbered exception handler to a name when encountered. The function Exception_init() takes two parameters the first is the user defined exception name the second of the oracle engine’s exception number. These lines will be included in the Declare section of the PL/SQL block. -create a parent child relationship table -2292 > Entity integrity error declare e_emp_remaining EXCEPTION ; PRAGMA EXCEPTION_INIT(e_emp_remaining,-2292) ; begin delete from dept1 where deptno=10 ; commit; exception when e_emp_remaining then dbms_output.put_line('canot remove dept '); end; -----------------------------------declare z_divide EXCEPTION ; PRAGMA EXCEPTION_INIT(z_divide,-1476) ; x number:= 20 ; y number:= 0 ; z number; begin z:=x/y; exception when z_divide then dbms_output.put_line('Number is divided zero '); end; -----------------------------------declare t_row_many EXCEPTION ; PRAGMA EXCEPTION_INIT(t_row_many,-1422) ; begin select sal into msal from emp where deptno=20; exception when t_row_many then dbms_output.put_line('Too Many Rows returned '); end; ------------------------------------

42

declare t_row_many EXCEPTION ; PRAGMA EXCEPTION_INIT(t_row_many,-1422) ; msal emp.sal%type; begin select sal into msal from emp where deptno=20; exception when t_row_many then dbms_output.put_line('Too Many Rows returned '); end; Error – Trapping Functions: When an Exception occurs, we can identify the associated error code and error message by using two functions. SQLCODE returns number of the Oracle error for internal exception. We can pass an error number to SQLERRM, which then return the message associated with the error number. Declare Err_num number ; Err_msg Varchar2(100) ; Begin --------Exception Err_num = SQLCODE ; Err_msg := sunstr(SQLERRM,100) ; Insert into errors values (err_num,err_msg) ; End ;

PROCEDURES A Procedure is type of subprogram that performs an action. - A procedure can be stored in the database, as a schema object, for repeated execution. Syntax for Creating Procedure Create (or Replace) procedure (procedure name) (Parameter1 (mode1) datatype1, Parameter2 (mode2) datatype2) AS|IS PL/SQL BLOCK Procedural Parameter Modes Type of Parameter Description ----------------------------IN Parameter Passes a constant value from the calling environment into the procedure. OUT Parameter

Passes a value from the procedure to the calling environment

IN OUT Parameter Passes a value from the calling environment into the procedure and a Possibly different value from the procedure back to the calling environment using the same parameter.

43

IN Parameter 1) create or replace procedure Add_proc (a in number, b in number) is c number; begin c:= a+b ; dbms_output.put_line('The values of c is' ||c); end ; 2) create or replace procedure emp_proc (mempno in number) is begin update emp set sal=sal*2 where empno=mempno; end ; OUT Parameter create or replace procedure query_emp (vempid in emp.empno%type, vname out emp.ename%type, vsal out emp.sal%type, vcomm out emp.comm%type) IS begin select ename,sal,comm into vname,vsal,vcomm from emp where empno=vempid; end ; -- steps for the printing the out parameter VARIABLE A VARCHAR2(30) VARIABLE B NUMBER VARIABLE C NUMBER EXEC QUERY_EMP (7900,:A,:B,:C) print A print B print C IN OUT Parameter create or replace procedure format_phone (p_phone_no in out varchar2) is begin p_phone_no := '(' || substr(p_phone_no,1,3) || ')' || substr(p_phone_no,4,3) || '-' || substr(p_phone_no,7) ; end; Steps for printing the IN OUT result variable g_phone_no varchar2(15) begin :g_phone_no := '8006330575' ; end; / -------

44

execute format_phone(:g_phone_no) ------print g_phone_no Invoking Procedure from another procedure 1) create or replace procedure view_proc_dept (mdeptno in number) is vdname dept.dname%type; begin select dname into vdname from dept where deptno=mdeptno ; dbms_output.put_line('The department name is ' ||vdname) ; end ; 2) create or replace procedure view_proc_emp (mdeptno in number) is vename emp.ename%type; vsal emp.sal%type ; cursor emp_cursor is select ename,sal from emp where deptno=mdeptno ; begin open emp_cursor ; loop fetch emp_cursor into vename,vsal; dbms_output.put_line('The employee name is' ||vename||' Salary is ' || vsal) ; exit when emp_cursor%notfound; end loop; close emp_cursor ; view_proc_dept(mdeptno); end ; Using Exception in Procedure 1) create or replace procedure view_exec_emp (mempno in number) is vename emp.ename%type; vsal emp.sal%type ; begin select ename,sal into vename,vsal from emp where empno=mempno; dbms_output.put_line('The employee name is' ||vename||' Salary is ' || vsal) ; EXCEPTION WHEN no_data_found then dbms_output.put_line('No such Employee number exist'); end ; 2) create or replace procedure emp_proc (mdeptno emp.deptno%type ) as cursor c1 is select ename,sal,job from emp where deptno=mdeptno; mename emp.ename%type; msal emp.sal%type ; mjob emp.job%type; check_emp exception ; begin open c1;

45

loop fetch c1 into mename,msal,mjob ; if c1%rowcount < 1 then raise check_emp ; else dbms_output.put_line('the employee name is '||mename||' end if ; exit when c1%notfound ; end loop ; exception when check_emp then dbms_output.put_line(' the deptno not found') ; end;

salary is '||msal||' '||mjob) ;

Default Option for Parameter in Procedure create or replace procedure add_location (vlocation_id in locations.location_id%type default 800, vlocname IN locations.loc_name%type default 'unknown') IS begin insert into locations values (vlocation_id,vloc_name); End ; Dropping Procedure drop procedure view_proc_emp; drop procedure view_exec_emp ; drop procedure view_proc_dept ;

Functions A Function is a named PL/SQL block that returns a value. Syntax for Creating Function Create (or Replace) function (function name) (parameter1 (mode1) datatype1, parameter2 (mode2) datatype2) RETURN datatype AS|IS PL/SQL BLOCK Some Programs Using Functions 1) create or replace function tax (p_value in number) return number is Begin return (p_value * 2); end ; select tax(3) from dual ; 2) create or replace function total ( a number , b number ) return number

46

is begin return (a+b) ; end ; select area (3,2) from dual ; 3) Create or replace function get_sal (mempid in emp.empno%type) return number IS v_sal number; BEGIN select sal into v_sal from emp where empno=mempid ; return v_sal; end; execution Method - 1 -------------------select get_sal(7900) from dual ; execution Method - 2 -------------------Variable g_sal number execute :g_sal := get_sal(7900) Print :g_sal --------------------------------

select empno,ename, sal, tax(sal) from emp where deptno=20 ; Create or replace function get_sal (mempid in emp.empno%type) return number IS v_sal number;

47

x number; begin select sal into v_sal from emp where empno=mempid ; x := v_sal*.5 ; return x ; end ; --------------------------------------------------------create or replace function func_name (mempno in number) return number is msal emp.sal%type; begin select sal into msal from emp where empno=mempno ; if msal > 0 then return msal ; else return msal*2 ; end if ; end ; ----------------------------------------------------------------------------------create or replace function func_name (mempno in number) return boolean is msal emp.sal%type; begin select sal into msal from emp where empno=mempno ; if msal > 0 then return (true) ; else return (false) ; end if ; end ; ---------------begin if func_name(7902) then dbms_output.put_line('right') ; else dbms_output.put_line('wrong') ; end if ; end; ----------------------------------------------------------------------------------create or replace function func_name (mempno in number) return varchar2 as mename emp.ename%type ; begin select ename into mename from emp where empno=mempno ; if sql%rowcount > 0 then return mename ; end if ; end; select func_name(7902) from dual ;

48

----------------------------------------------------------------------------------Using Exception create or replace function func_name (mempno in number) return varchar2 as mename emp.ename%type ; begin select ename into mename from emp where empno=mempno ; if sql%rowcount > 0 then return mename ; end if ; exception when no_data_found then mename:='no data' ; return mename ; end ; Dropping a Function Drop function function_name ; Desc user_objects ; select * from user_objects ; desc user_source select text from user_source where NAME='FUNC_NAME' ; desc user_errors select * from user_errors ;

PACKAGE and PACKAGE BODY Packages: A Package is an Oracle object, which holds other objects within it. Objects commonly held within a package are procedures, functions, variables, constants, cursors and exceptions. A Package consists of two components - Specification - Body A Package Specification declares the types (variable of the Record type), memory variables, constants, exceptions, cursors and subprograms that are available for use. A Package body fully defines cursor, functions and procedure and thus implements the specification.

49

Why Use Package Packages offer the following advantages: 1) Packages enable the organization applications into efficient modules. Each package is easily understood, and the interfaces between packages are simple, clear and well defined. 2) Package allows granting of privileges efficiently. 3) A Package's public variables and cursor persist for the duration of the session. Therefore all cursors and procedures that execute in the environment can store them. 4) Packages enable the overloading of procedures and functions when required. 5) Package improves performance by loading multiple objects into memory at once. Therefore, subsequent calls to related sub programs in the package require no I/O. 6) Package promotes code reuse through the use of libaries that contain shared procedures and functions. Thereby reducing redundant coding. Creating the Package Specification Syntax CREATE [Or Replace] PACKAGE package_name IS | AS public type and item declarations subprogram specifications End package_name; Creating the Package Body Syntax CREATE [Or Replace] PACKAGE BODY package_body_name IS | AS private type and item declarations subprogram body End package_body_name; Some Sample Program using Package and Package Body 1) create or replace package emp_proc as Procedure emp_pr (mempno number) ; Procedure dept_pr (mdeptno number) ; end; create or replace package body emp_proc as Procedure emp_pr (mempno number) as msal number ; begin select sal into msal from emp where empno=mempno ; dbms_output.put_line('sal is '|| msal) ; end; Procedure dept_pr (mdeptno number) as mdname varchar2(10) ; begin select dname into mdname from dept where deptno=mdeptno; dbms_output.put_line('dname is '|| mdname) ; end;

50

end; To execute the previous program -------------------------------declare begin dbms_output.put('the emp ') ; emp_proc.emp_pr(7369) ; dbms_output.put('the dept ') ; emp_proc.dept_pr(20) ; END; 2) create or replace package p2 as function f1(x number) return number ; procedure pro (x number,y number) ; end ; create or replace package body p2 as function f1(x number) return number as y number; begin y:=x*(30/100); return y; end f1; procedure pro (x number, y number) as z number; begin z:=x*y; dbms_output.put_line('the multiplication of'||x ||'and'||y||':='||z); end pro; end; To execute this Program exec p2.pro(45,3); declare x number ; begin x:=p2.f1(10); dbms_output.put_line(x); end; Using Cursors in Packages 1) create or replace package pack_cur IS cursor c1 is select empno from emp ; procedure proc_emp1 ; procedure proc_emp2 ;

51

end ; create or replace package body pack_cur IS mempno number; procedure proc_emp1 is begin open c1; loop fetch c1 into mempno ; dbms_output.put_line(mempno) ; exit when c1%rowcount >=3 ; end loop ; end proc_emp1 ; procedure proc_emp2 IS begin loop fetch c1 into mempno ; dbms_output.put_line(mempno) ; exit when c1%rowcount >=6 ; end loop ; end proc_emp2 ; end ; 2) create or replace package pack_cur IS cursor c1 is select empno from emp ; cursor c2 is select dname from dept ; procedure proc_emp ; procedure proc_dept; end ; create or replace package body pack_cur IS mempno emp.empno%type; mdname dept.dname%type; procedure proc_emp is begin open c1; loop fetch c1 into mempno ; dbms_output.put_line(mempno) ; exit when c1%rowcount >=3 ; end loop ; close c1; end proc_emp ; procedure proc_dept IS begin open c2; loop fetch c2 into mdname ; dbms_output.put_line(mdname) ; exit when c2%rowcount >=2 ; end loop ; close c2; end proc_dept ; end ;

52

Declaring a Bodiless Package CREATE or REPLACE PACKAGE global_consts IS mile_2_kilo CONSTANT NUMBER := 1.6093; kilo_2_mile CONSTANT NUMBER := 0.6214; yard_2_meter CONSTANT NUMBER := 0.9144; meter_2_yard CONSTANT NUMBER := 1.0936; end global_consts; CREATE OR REPLACE PROCEDURE meter_to_yaard (p_meter IN number, p_yard out Number) IS BEGIN p_yard := p_meter + global_consts.meter_2_yard ; End ; Droppimg/ Removing Package and Package Body DROP PACKAGE package_name ; DROP PACKAGE BODY package_body_name ;

OVERLOADING PROCEDURES AND FUNCTIONS A Package is an Oracle object that can hold a number of other objects like procedure and functions. More than one procedure and function with the same name but with different parameters can be defined within a package or within a PL/SQL declaration block. Multiple procedures that are declared with the same name are called Overloaded Procedures. Similarly, multiple functions that are declared with the same name are called Overloaded Functions. 1) create or replace package p1 as function f1(x number) return number; function f1(x number,y number) return number; end; create or replace package body p1 as function f1(x number) return number as y number; begin y:=x+30; return y; end f1; function f1(x number, y number) return number as z number; begin z:=x+y; return z; end f1; end; To Execute the Package and Package Body declare x number;

53

begin x:=p1.f1(10); dbms_output.put_line(x); end; ---------------------declare x number; begin x:=p1.f1(10,30); dbms_output.put_line(x); end; 2) create or replace package p1 as function f1(x number) return boolean; function f1(x number,y number) return boolean; end; create or replace package body p1 as function f1(x number) return boolean as begin if x >30 then return true; else return false; end if; end f1; function f1(x number,y number) return boolean as z number; begin z:=x+y; if z>100 then return true; else return false; end if; end f1; end; To execute the Program declare x boolean; begin if p1.f1(60) then dbms_output.put_line('true'); end if; end; declare x boolean; begin if p1.f1(10) then dbms_output.put_line('true'); end if; end;

54

declare x boolean; begin if p1.f1(10) then dbms_output.put_line('true'); else dbms_output.put_line('false') ; end if ; end ; declare x boolean; begin if p1.f1(10,200) then dbms_output.put_line('true'); end if; end; --------------------------------------------------------------------------------------------------------create or replace package pack_over as procedure emp_proc (mempno number) ; procedure emp_proc (mempno number, mdeptno number) ; end ; -------------------------------------------------------------create or replace package body pack_over as procedure emp_proc (mempno number) as mename emp.ename%type ; begin select ename into mename from emp where empno=mempno ; dbms_output.put_line('Employee name is '||mename) ; exception when no_data_found then dbms_output.put_line('No data found') ; end emp_proc; procedure emp_proc (mempno number,mdeptno number) as mename emp.ename%type ; msal emp.sal%type; begin select ename,sal into mename,msal from emp where empno=mempno and deptno=mdeptno ; dbms_output.put_line('Employee name is '||mename||' salary is '||msal) ; exception when no_data_found then dbms_output.put_line('No data found') ; end emp_proc; end;

Trigger A Trigger: - Is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema or the database. - Executes implicitly whenever a particular event takes place. - Can be either: DML Trigger - fires whenever INSERT, UPDATE, DELETE

55

Schema Trigger – CREATE, ALTER, DROP DatabaseTrigger - fires whenever system events (such as Logon, shutdown or startup) occurs on a schema or a database Creating DML Triggers A triggering statement contains: - Trigger Timing - for table: BEFORE, AFTER - for View: INSERT, UPDATE or DELETE - Triggering Event: On table, View - Trigger Type: Row or Statement - WHEN Clause: Restricting condition - Trigger body: PL/SQL block DML Trigger Components BEFORE: Execute the trigger body before the triggering DML event on a table. AFTER: Execute the trigger body after the triggering DML event on a table. INSTEAD OF: Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable. Triggering user events: The triggering event is an Insert, Update & Delete statement on a table. Trigger type Statements: The trigger body executes once for the triggering events. This is the default. A statement trigger fires once, even if no rows are affected at all. Row: The trigger body executes once for each row affected by the triggering events. A row trigger is not executed if the triggering events affect no rows.

Firing Sequence -------------------> BEFORE statement trigger Empno Ename Deptno ----- ------ ------ -----> BEFORE row trigger 7902 SMITH 20 7876 JONES 10 -----> AFTER row trigger 7878 SURESH 30

----> AFTER statement trigger Syntax for creating DML Statement Triggers CREATE [OR REPLACE] trigger Trigger_name timing events1 [OR events2 OR events3 ] ON table_name Trigger_body -----------------------------------------------------------------------create or replace trigger emp_Bef_inst before insert on emp begin dbms_output.put_line('before') ; end ; -----------------------------------------------------------------------create or replace trigger emp_aft_inst

56

after insert on emp begin dbms_output.put_line('after') ; end ; --------------------------------------------create or replace trigger res_salary before insert on emp begin dbms_output.put_line('welcome'); end; -----------------------------------------create or replace trigger aft_ins after insert on emp for each row declare meno emp.empno%type ; begin meno := :new.empno ; dbms_output.put_line('New employee no is ' ||meno) ; end ; -------------------------------------------------create or replace trigger res_salary before insert on emp1 begin RAISE_APPLICATION_ERROR (-20202,'Employee cannot insert'); end; insert into emp1 (empno,ename,sal,deptno) values (3333,'rrr',3333,40) ; -----------------------------------------------------------------------create or replace trigger res_salary before update of sal on emp1 begin RAISE_APPLICATION_ERROR (-20202,'Employee cannot update'); end; update emp1 set sal=3333 where empno=7900 ; -----------------------------------------------------------------------create or replace trigger res_salary before delete on emp1 begin RAISE_APPLICATION_ERROR (-20202,'Employee cannot delete'); end; delete from emp1 where empno=7900 ; -----------------------------------------------------------------------CREATE TRIGGER SalaryCheck AFTER INSERT OR UPDATE OF sal ON emp1 FOR EACH ROW BEGIN IF (:new.sal < 0) THEN RAISE_APPLICATION_ERROR(-20000, 'no negative salary allowed'); END IF;

57

END; -----------------------------------------------------------------------create or replace trigger lunch before insert or update or delete on emp declare a varchar2(4); begin select to_char(sysdate,'HHMM') into a from dual ; if a between '0100' and '0200' then raise_application_error(-20202,'U canot do ant DML operation during lunch time') ; end if ; end ; ---------------------------------------------------------------------------------------------create or replace trigger secure_emp before insert on emp1 begin if (to_char(sysdate,'DY') in ('SAT','SUN')) or (to_char(sysdate,'HH24:MI') not between '08:00' and '18:00') then raise_application_error (-20500,'You may insert into emp table only during businees'); end if ; end; Trigger using conditional Predicates create or replace trigger secure_emp before insert or update or delete on emp1 begin if (to_char(sysdate,'DY') in ('SAT','SUN')) or (to_char(sysdate,'HH24:MI') not between '08:00'and '18:00') then if deleting then raise_application_error (-20500,'You may deleting emp table only during businees'); elsif inserting then raise_application_error (-20500,'You may insert into emp table only during businees'); elsif updating ('SAL') then raise_application_error (-20500,'You may updating emp table only during businees'); else raise_application_error (-20500,'You may update into emp table only during businees'); end if; end if ; end; Restricting a ROW triggers create or replace trigger derive_comm_pct after insert or update or delete on emp1 for each row WHEN (new.job='MANAGER') begin if inserting then :new.comm := 0 ; elsif :old.comm is null then :new.comm := 0 ; else :new.comm := :old.comm + 0.05 ; end if; end;

58

Trigger (ROW LEVEL)(Using :old qualifier) create or replace trigger restrict_salary before insert or update of sal on emp1 for each row begin if not (:new.job in ('MANAGER','CLERK')) and :new.sal > 15400 then raise_application_error(-20202,'Employee cannot earn this amount') ; end if ; end; Trigger (ROW LEVEL)(Using :old and :new qulaifier) create table audit_emp_table (USERNAME VARCHAR2(30), TIMESTAMP DATE, ID VARCHAR2(20), OLD_LAST_NAME VARCHAR2(30), NEW_LAST_NAME VARCHAR2(30), OLD_TITLE VARCHAR2(20), NEW_TITLE VARCHAR2(20), OLD_SALARY NUMBER(9,2) NEW_SALARY NUMBER(9,2));

------------------------------------------create or replace trigger audit_emp_values after insert or update or delete on emp1 for each row begin insert into AUDIT_EMP_TABLE (USERNAME,TIMESTAMP,ID,OLD_LAST_NAME, NEW_LAST_NAME,OLD_TITLE,NEW_TITLE,OLD_SALARY,NEW_SALARY) values (user, sysdate, :old.empno, :old.ename, :new.ename, :old.job, :new.job, :old.sal, :new.sal); end; select * from audit_emp_table ; insert into emp1 (empno,ename,job,sal,deptno) values (3456,'dddd','MANAGER',2456,30) ;

Trigger (ROW LEVEL) (Using as Instead of Trigger) INSTEAD OF Triggers Use INSTEAD OF Triggers to modify data in which the DML statement has been issued against an inherently non-updateable view. These triggers are called INSTEAD OF triggers. Syntax for Creating an INSTEAD of Trigger ----------------------------------------CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF events [or event2 or event3] ON view_name [Referencing ---------------------------------------------create table new_emp as select empno,ename,sal,deptno,hiredate,job from emp ;

59

create table new_depts as select e.deptno,e.dname, sum(d.sal) tot_dept_sal from emp d, dept e where d.deptno=e.deptno group by e.deptno,e.dname ; create view emp_details as select e.empno,e.ename,e.sal,e.deptno,e.job,d.dname,d.loc from emp e, dept d where d.deptno=e.deptno ;

create or replace trigger new_emp_det instead of insert or update or delete on emp_details for each row begin if inserting then insert into new_emps values ( :new.empno,:new.ename,:new.sal,:new.deptno,sysdate,:new.job) ; update new_depts set tot_dept_sal = tot_dept_sal + :new.sal where deptno = :new.deptno ; elsif deleting then delete from new_emps where empno= :old.empno; update new_depts set tot_dept_sal = tot_dept_sal - :old.sal where deptno = :new.deptno ; elsif updating ('sal') then update new_emps set sal = :new.sal where empno = :new.empno ; update new_depts set tot_dept_sal = tot_dept_sal + :new.sal - :old.sal where deptno = :new.deptno ; elsif updating ('deptno') then update new_emps set deptno = :new.deptno where empno = :old.empno ; update new_depts set tot_dept_sal = tot_dept_sal - :old.sal where deptno = :old.deptno ; update new_depts set tot_dept_sal = tot_dept_sal + :new.sal where deptno = :new.deptno ; end if ; end; insert into EMP_DETAILS values (1000,'suresh',3456,30,'MANAGER','ACCOUNTING') ; 2) create table NEW_EMP as select empno,ename,deptno from emp ; create table new_dept as select deptno,dname from dept ; create view emp_dept_view as select e.empno,e.ename,e.deptno,d.dname from emp e ,dept d where e.deptno=d.deptno ; create or replace trigger ins_trigs instead of insert on emp_dept_view begin insert into new_emp values (:new.empno,:new.ename,:new.Deptno) ; insert into new_dept values (:new.deptno,:new.dname) ; end ; insert into emp_dept_view values (2345,'dddd',20,'ffff') ; -------------------------------------------------------------------------------------------MANAGING TRIGGERS Alter trigger trigger_name Disable | enable ; Alter table table_name Disable|ename ALL triggers ; Drop trigger trigger_name ;

60

Creating Database Triggers Triggering user Events: - CREATE, ALTER or DROP - Logging on or off Triggering database or system event: - Shutting down or starting up the database - A specific error (or any error) being raised. Creating Trigger on DDL Statements These Trigger will be fired implicitely whenever user performs any DDL operations like create, alter or dropping an object The Syntax is : Create or replace trigger
Example Before create Create or replace trigger ddl_trigger Before create on schema begin Raise_application_error(-20222,’U cannot create a table’) end; After create Create or replace trigger ddl_trigger after create on schema begin insert into audit_objects_use values to_char(sysdate,’dd/mm/yyyy hh24:mi:ss’) ; end;

(user||’

has

created

an

object

at’||

LOGON and LOGOFF Trigger Example create table LOG_TRIG_TABLE ( USER_ID varchar2(10), LOG_DATE date, ACTION varchar2(20)) ; create or replace trigger logon_trigs AFTER LOGON ON SCHEMA BEGIN INSERT INTO LOG_TRIG_TABLE (USER_ID,LOG_DATE, ACTION) VALUES (user,sysdate,'Logging On') ; end; create or replace trigger logoff_trigs before LOGOFF ON SCHEMA BEGIN

61

INSERT INTO LOG_TRIG_TABLE (USER_ID,LOG_DATE, ACTION) VALUES (user,sysdate,'Logging Off') ; end; select * from LOG_TRIG_TABLE ; Viewing Trigger Information You can view the following trigger inormation by using USER_TRIGGERS SQL> select trigger_name, trigger_type, triggering_event, table_name, Referencing_names, status, trigger_body from USER_TRIGGERS Where trigger_name='CHECK_SALARY’;

File Input/Output (I/O) (UTL_FILE) - It is a DB Package - Used for - to create flat file - to load data into Oracle DB from flat file - It do validation also FOPEN FCLOSE GET_LINE NEW_LINE PUT Datatype = File_type create or replace procedure proc_a as vfile_id utl_file.file_type; vstring varchar2(100); vcode varchar2(10); vname varchar2(10); begin vfile_id := utl_file.fopen('C:\', 'flat.txt','W') ; loop utl_file.get_line (vfile_id,vstring); vcode := substr(vstring,1,2) ; vname := substr(vstring,1,2) ; insert into person (code,name) values (vcode,vname) ; commit ; end loop ; exception when no_data_found then utl_file.fclose(vfile_id) ; end ; create or replace procedure proc_a as vfile_id utl_file.file_type; vstring varchar2(100); vcode varchar2(10); vname varchar2(10); begin vfile_id := utl_file.fopen('C:\', 'flat.txt','W') ; loop utl_file.get_line (vfile_id,vstring); vcode := substr(vstring,1,2) ; vname := substr(vstring,1,2) ;

62

insert into person (code,name) values (vcode,vname) ; commit ; end loop ; exception when no_data_found then utl_file.fclose(vfile_id) ; end ;

Oracle Supplied Packages Oracle Supplied Package -

Are provided with the Oracle Server Extend the functionality of the database Enables access to certain SQL features normally restricted for PL/SQL.

Using Native Dynamic SQL - Is a SQL statement that contains variables that can change during runtime - Is a SQL staement with placeholders abd is stored as a character string. - Enables general-purpose code to be written - Enables data-defination, data_control or session-control statements to be written and executed from PL/SQL. - Is written using either DBMS_SQL or native dynamic SQL. Using the DBMS_SQL Package The DBMS_SQL package is used to write dynamic SQL in stored procedures and to parse DDL statements. Some of the procedures and functions of the package includes: -

OPEN_CURSOR PARSE BIND_VARIABLE EXECUTE FETCH_ROWS CLOSE_CURSOR

Componets of the DBMS_SQL Package Function or Porcedure OPEN_CURSOR PARSE BIND_VARIABLE EXECUTE FETCH_ROWS CLOSE_CURSOR

Description Opnes a new cursor and assigns a cursor ID number Parses the DDL or DML ststements: that is, checks the statements’s syntax and associates it with the opned cursor (DDL statement are immediately executed when parsed) Binds the given value to the variable identified by its name in the parsed statement in the given cursor Executes the SQL statement and returns the number of rows processed Retrives a row for the specified cursor (for multiple rows, call in a loop) Closes the specified cursor

create or replace procedure delete_all_rows (p_tab_name in varchar2, p_rows_del out number) is cursor_name integer; begin cursor_name := DBMS_SQL.open_cursor ; DBMS_SQL.PARSE(cursor_name,'DELETE From '||P_tab_name,dbms_sql.native) ;

63

p_rows_del := DBMS_sql.EXECUTE(cursor_name) ; DBMS_SQL.close_cursor(cursor_name) ; end; Dynamic SQL Using EXECUTE IMMEDIATE Create procedure del_rows (p_table_name in varchar2, p_rows_deld out number) IS Begin Execute immediate ‘delete from ‘||p_table_name ; P_rows_deld := SQL%ROWCOUNT ; End ; SQL> variable a number SQL> execute del_rows ('EMP1', :a) SQL> print a Using DBMS_JOB for Scheduling DBMS_JOB enables the scheduling and execution of PL/SQL programs: -

Submitting jobs Executing jobs Changing execution parameters of jobs Remove jobs Suspending Jobs

New SQL function in Oracle 9i COALESCE You can also use COALESCE as a variety of the CASE expression. For example, COALESCE (expr1, expr2) is equivalent to: CASE WHEN IS expr1 NOT NULL THEN expr1 ELSE expr2 END Similarly, COALESCE (expr1, expr2, ..., exprn), for n>=3 is equivalent to: CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE COALESCE (expr2, ..., exprn) END select comm,COALESCE(0.9*comm, comm,5) from emp ; CURRENT_DATE Purpose CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian

64

calendar of datatype DATE. Examples The following example illustrates that CURRENT_DATE is sensitive to the session time zone: ALTER SESSION SET TIME_ZONE = '-5:0'; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE CURRENT_DATE --------------- --------------------05:00 29-MAY-2000 13:14:03 ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE CURRENT_DATE --------------- --------------------08:00 29-MAY-2000 10:14:33 CURRENT_TIMESTAMP Purpose CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The time zone offset reflects the current local time of the SQL session. If you omit precision, then the default is 6. The difference between this function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value. In the optional argument, precision specifies the fractional second precision of the time value returned. Examples The following example illustrates that CURRENT_TIMESTAMP is sensitive to the session time zone: ALTER SESSION SET TIME_ZONE = '-5:0'; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE CURRENT_TIMESTAMP --------------- ---------------------------------------------------05:00 04-APR-00 01.17.56.917550 PM -05:00 ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE CURRENT_TIMESTAMP --------------- ----------------------------------------------------08:00 04-APR-00 10.18.21.366065 AM -08:00 If you use the CURRENT_TIMESTAMP with a format mask, take care that the format mask matches the value returned by the function. For example, consider the following table:

65

CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE); The following statement fails because the mask does not include the TIME ZONE portion of the type returned by the function: INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM')); The following statement uses the correct format mask to match the return type of CURRENT_TIMESTAMP: INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ (CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM')); EXTRACT (DATETIME) Purpose EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view. SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL; EXTRACT(YEARFROMDATE'1998-03-07') --------------------------------1998 The following example selects from the sample table hr.employees all employees who were hired after 1998: SELECT ename, empno, hiredate FROM TO_DATE(hiredate, 'DD-MON-RR')) < 1998 ORDER BY hiredate;

emp

WHERE

EXTRACT(YEAR

FROM

ENAME EMPNO HIREDATE ---------- ---------- --------suresh 2345 02-JAN-76 SMITH 7369 17-DEC-80 ALLEN 7499 20-FEB-81 WARD 7521 22-FEB-81 FIRST Purpose FIRST and LAST are very similar functions. Both are aggregate and analytic functions that operate on a set of values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. If only one row ranks as FIRST or LAST, the aggregate operates on the set with only one element. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.

66

When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self joins or views and enable better performance. select deptno, min(sal) KEEP (DENSE_RANK FIRST ORDER BY comm) "Worst", MAX(sal) KEEP (DENSE_RANK LAST ORDER BY comm) "Best" from emp group by deptno ; DEPTNO Worst Best ---------- ---------- ---------10 1300 5000 20 800 3000 30 1500 2850 40 3456 3456 60 2345 2345 90 5657 5657 NULLIF Purpose NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1. If both arguments are numeric datatypes, then Oracle Database determines the argument with the higher numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype. If the arguments are not numeric, then they must be of the same datatype, or Oracle returns an error. The NULLIF function is logically equivalent to the following CASE expression: CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END Examples The following example selects those employees from the sample schema hr who have changed jobs since they were hired, as indicated by a job_id in the job_history table different from the current job_id in the employees table: SELECT e.ename, NULLIF(e.job, j.job) "Old Job" FROM emp e, job_history j WHERE e.empno = j.empno ORDER BY ename; ENAME Old Job I ---------- --------ADAMS ALLEN BLAKE CLARK FORD ggggg suresh Program

67

Implementing Object Techniques Object Technology: • Object Technology is another step forward in the software enginnering life cycle that allows us to: - Model real Thing - Represent real thing as objects • Object Technology is a method of managing complexity so that we can develop solutions that simulate real-world problems. • Minimizing cost through reusibility Oracle provides full support for all three different implementation – Relational, ObjectRelational, Object- Oriented Relational. Why Should Objects Be Used? Objects reduce complexity by giving an intuitive way of representing complex data and its relations. Besides simplifying the interaction with data, objects may help in other ways. Examples of benefits that come from using OO features are: Object reuse: - If OO code is written; the chance of reusing previously written code modules is increased. Similarly, if OO database objects are created the chances that these database objects will be reused in increased. Standard adherence:- If database objects are built using standards, then the chances they will be used increase exponentially. If multiple applications or tables use the same set of database objects, then a de facto standard for application of tables is being created.

OBJECT TYPES Oracle supports many different types of objects. In the following sections, major object types are described. Abstract Data type Abstract Data type is a datatype that consists of one or more subtypes. Rather that being constrained to the standard Oracle data type of NUMBER, DATE, and VARCHAR2, the abstract data type can more accurately describe data. Creation of an Object CREATE TYPE address_ty AS OBJECT (Street varchar2(50), city varchar2(25), state vachar2(25), zip number) ; CREATE TYPE person_ty AS OBJECT (name vachar2(250), address address_ty) ; An abstract data type can be used to create an Object Table. In an object table, the columns of the table map to the columns of an abstract datatype. CREATE TYPE address_ty AS OBJECT (Street varchar2(50), city varchar2(25), state varchar2(25), zip number) ; CREATE TYPE person_ty AS OBJECT (name vachar2(250), address address_ty) ;

68

CREATE TABLE customer (customer_id number, person person_ty) Retrieving Data using Objects SELECT client.Person.Name Client.Person.Address.City FROM customer client WHERE client.person.address.city like ‘M%’ ; Updating Data using Objects UPDATE customer client SET client.person.addess.city=’Mumbai’ where Client.Person.Address.City=’Chennai’ ; Deleting Data using Objects DELETE from customer client where client.person.addess.city=’Mumbai’ ; Nested Tables A nested table is a table within a table. A nested is a collection of rows, represented as a column within the main table. For each record within the main table, the nested table as a column. In one sense, it’s a way of storing a one-to-many relationship within one table. Varying Arrays A varying array is a set if objects, each with the same data type. The size of the array is limited when it is created. When a table is created with a varying array, the array is a nested table a limited set of rows Varying Arrays, also known as VARRAYS, allows storing repeating attributes in tables. References Nested tables and Varying Arrays are embedded objects. They are physically embedded within another object. Another type of object, called referenced objects, are Physically separated from the objects that refer to them. References (also known as REFs) are essentially pointers to row objects. A row object is different from a column object. An example of a column object would be a varying array, it is an object that is treated as a column in a table. A row object, on the other hand, always represents a row. References are typically among the last OO features implemented when migrating a relational database to an object-relational or pure OO one. Object views Object views allow adding OO concepts on top of existing relational tables. For example, an abstract data type can be created based on an existing table definition. Thus, object views give the benefits of relational table storage and OO structures. Object views allow the development of OO features within a relational database, a kind of bridge between the relational and OO worlds. CREATE TYPE address_ty AS OBJECT (Street varchar2(50), city varchar2(25), state varchar2(25), zip number) ; CREATE TYPE person_ty AS OBJECT (name varchar2(25), address address_ty) ; CREATE TABLE customer (customer_id number, person person_ty) ;

69

create view cust_obj of address_ty with object oid (customer_id) as select customer_id,person_ty(name,address_ty(Street,city,state,zip)) from customer ; VARIABLE ARRAYS A varying array allows the storing of repeated attributes of a record in a single row. Varying arrays are collectors that allow repetition of only those column values that change, potentially saving storage space. Varying arrays can be used to accurately represent one –to –many relationships where the maximum number of elements on the many side of the relationship is known and where the order of these elements is important. CREATE TYPE COMPANY_ADDRESS_TY AS VARRAY (3) OF varchar2 (1000); This statement creates a varray type called company_address_typ, which can hold a maximum of 3 elements of data type varchar2(1000), i.e. 3 entries per record, each storing address information for the company. Now that the varying array company_address_ty is created, this can be used as a part of the creation of either a table or an abstract datatype Create table company_info (company_name varchar2(50), address company_address_ty); this SQL statement creates a table called company_info, which contains an embedded object called address that is a varray of type company_address_ty. ORCLE stores the varying array data internally using the RAW datatype. Reference object The referencing object (REFs data type) is something that is new to ORACLE 8i. This data type acts as a pointer to an object. A REF can also be used in a manner similar to a foreign key in a RDBMS. A REF is used primarily to store an object identifier, and to allow the user to select that object. REF’s establish relationship between two-object tables, much the same way as a primarykey/foreign-key relationship in relational tables. Relational tables however, have difficulty, if more than one table is needed in a primary-key/foreign-key relationship related to a single table. For example, an address table, that stores addresses from several entities. The use of REFs eliminates this problem, because an unscoped REF can refer to any accessible object table. A scope clause in a definition forces a set of REFs for a given column to be confined to a single object table. There can be only one REF clause for a given REF column. REF scope can be set at either the column or table level. REF values can be stored with or without a ROWID. Storing a REF with a ROWID seeds dereferencing operation, but takes more space. If with ROWID is not specified with the REF clause, the default is to not store ROWIDs with the REF values. SCOPE clauses prevent dangling references, as they will not allow REF values unless the corresponding entries in the SCOPE table is present. REF columns can be added to nested tables with the ALTER TABLE command. A call to a REF returns the OID of the object instance. An OID is a 128 bytes base –64 number, which is not very useful except as handle to the object instance. To get the value stored in the instance that is referred to by a REF, the DEREF routine is used. DEREF returns values in the object instance referenced by a specific REF value.

70

Some advance features in Oracle 9i MATERIALIZED VIEW A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases CREATE MATERIALIZED VIEW sales_mv REFRESH FAST ON COMMIT AS SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales FROM times t, products p, sales s WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id GROUP BY t.calendar_year, p.prod_id; FLASHBACK TABLE Purpose Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.

Oracle Utilities Export • A backup utility provided by Oracle • Writes data from an Oracle database into Oracle binary-format files • Exported data can only be read by the “Import” utility • A User must have connect or DBA privilege to export table • Exprt Modes: o Table: The names of the tables to be exported need to be specified o User: All objects belonging to the user are exported o Database: All database objects are exported • The command used is : exp Table(s) Level Export C:\> exp username/passward file=filename.dmp log=filename.log Tables=tablename,tablename User Level Export C:\> exp system/manager file=filename.dmp log=filename.log owner=username Entire Database Level C:\> exp system/manager file=filename.dmp log=filename.log full=y Import • Is used for importing data exported using the exp command into the Oracle database Importing tables C:\> Imp username/passward file=filename.dmp log=filename.log Tables=(tablename,tablename)

71

Importing User C:\> Imp system/manager touser=username

file=filename.dmp

log=filename.log

fromuser

=

username

Sql Loader • •

This tool is used to move data from a Non-Oracle standard source into the Oracle Database Load data into multiple datafiles

by Sql Loader - sqlldr step -1 - create a table ex create table person (code number(2), name varchar2(10)); setp - 2 - create a data file ex test.txt 10 MILLER 20 FORD step - 3 - create a control file load data ex - ctrl.txt infile into table person (code position (01:03) char, name postion ( 04:10) char) step - 4 c:\> sqlldr userid=sss/sss control=c:\ctrl.txt

To create multiple table in a SINGLE SQL Query Create schema authorization sss Create table a (a number) Create table b (a number); Where SSS is the User Name

Oracle Architecture In Every Oracle Server consists of an Oracle Instance and Database . An Oracle Instance is the combination of System Global Area(SGA) and the Background Process. Background Process is two types 1) Mandatory 2) Optional Mandatory are 1) Process Monitor (PMON) 2) System Monitor (SMON) 3) Database Writer (DBWR) 4) Log Writer (LGWR)

72

5) Checkpoint (CKPT) Optional are 1) Oracle Parallel Query Option (OPQO) 2) Multi DBWR 3) Archiver (ARCH) 4) Recoverer (Reco) Oracle 1) 2) 3) 4) 5)

Database consists mainly these files Redo Log File Control File Data File Parameter File Initialisation File (init.ora)

Redo Log file: It stores the changed information made by the Redolog buffer Cache by the help of Log Writer (LGWR). -It stores the redo entries -Atleast two files is required and the size should be in KB’s. -It contains File Id- Block Id –New Contents Data file: - The actual data stores in the data file. The file size should be in MBs or GBs or may be TBs. - One data file belongs only one Tablespace. Control File: It Stores the crushial information about the database like - No of data files - Name of each data files - Location of each data files - Database original creation date Parameter File: While datatabase is starting, first it reads from the parameter file, what parameter u have set in the database. There must be atleast some parameter require to start. Such as : Database Name Control File Location Whether Rollback Segmnet or Undo Management Background/User/Core Dump Location etc.. Password File: Password file is required to connect database remotely from one serever to another server. Program Global Area (PGA) When single-threaded servers are used, the Program Global Area (PGA) contains data (such as user’s session variables and arrays) for use only by a single process. Its contents is not visible to other processes (unless Oracle multithreaded server (MTS) is used). System Global Area (SGA) The Shared System Global Area (SGA) contains user data and control information for a single Oracle Instance. Its contents is visible/accessible to several user applications and system processes. Shared SQL Pool When an SQL statement is issued by a user process, it goes through a parse tree in the shared pool. The shared pool contains the library cache, data dictionary cache, execution plan and parse tree

73

for SQL statements. contains library cache performance information for each type of object in the library cache.

Data Dictionary Cache This area saves the re-reading of data from the Dictionary Table on a hard disk referenced while parsing SQL statements. It contains information such as segment information, security and access privileges, and available free storage space. It contains information such as user account data, datafile names, segment names, extent locations, table descriptions, and Privileges. Library Cache The Library Cache contains both shared and private SQL areas. The v$librarycache view lists performance information for each type of object in the library cache. A private SQL area is created for each transaction initiated. It contains session-specific information, such as bind variables, environment and session parameters, runtime stacks and buffers, and so on. Each private area is deallocated after the cursor to the session is closed. The number of private SQL areas a user session can have open at one time is limited by the value of the OPEN_CURSORS init.ora parameter. Within the private SQL area of the library cache, the persistent area contains information that is valid and applicable through multiple executions of the SQL statement. The runtime area contains data that is used only while the SQL statement is being executed. To improver Oracle performance, Oracle uses a Shared SQL area to save in cache the intermediate results from SQL commands previously returned. So before performing a hard disk operation, Oracle can service identical requests simply by retrieving them from its cached memory for reuse. Specifically, the shared area contains the parse tree and execution path for SQL statements. It also contains headers of PL/SQL packages and procedures that have been executed. Database Buffer Cache The data block buffer stores the most recently queried data from the database. This is done for efficiency -- to avoid time-consuming hard disk operations. Its size is controlled by the variable DB_BLOCK_BUFFERS parameter, which has a value calculated from the size of about 1 to 2 percent of the database. This space is managed by latching and writing the Least Recently Used (LRU) block to hard disk.

74

The v$sqlarea view displays the text of SQL statements in the shared SQL area, plus the number of users accessing the statements, disk blocks and memory blocks accessed while executing the statement, and other information. Redo Log Buffer Cache The redo log buffer is used to store redo information in memory before it is flushed to the Online Redo Log Files. Its size is initialized according to the LOG_BUFFER (bytes) parameter. The number of redo log space requests can be monitored using the v$sysstat view. Large Pool Introduced with Oracle processing/recovery.

8

to

store

user

session

global

area

infor

and

for

parallel

System Background Processes The PROCESSES parameter in init.ora specifies the maximum number of processes accessing the database. The v$process view displays all processes that are connected to the database, including background and user processes. The V$bgprocess view lists all background processes. User and Server Processes (Snnn) Applications and utilities access the RDBMS through a user process. The user process connects to a server process, which can be dedicated to one user process or shared by many user processes. The server process parses and executes SQL statements that are submitted to it and returns the result sets back to the user process. It is also the process that reads data blocks from the data files into the database buffer cache. Four background processes are mandatory -- required for startup of each Oracle instance: 1. System Monitor (SMON) The System monitor performs automatic crash recovery on the Oracle instance in the event it fails. If the command shutdown abort is issued by the user, Oracle doesn't have time to write modified database changes from the System Global Area (SGA) down to disk. It wakes itself up routinely to clean up free space in datafiles (much like a defragmenter for hard drives). It looks for free spaces and organizes them into contiguous areas. 2. Process Monitor (PMON) The Process monitor performs recovery (such as releasing locks failed processes held on resources). PMON analyzes user processes that access the database, performs "garbage collection", and restarts failed processes in the database. It releases locks held by processes which failed before they released memory allocations. 3. Database Writer (DBWn) The Database Writer (DBWR) writes and retrieves blocks from datafiles through the buffer cache. Some suggest using the DB_WRITERS parameter to define as many DBWR processes as there are physical disks used to store data files. 4. Log Writer (LGWR) The Log Writer writes and retrieves its blocks from datafiles through the Redo Log Buffer.

75

5. Checkpoint Process (CKPT) The checkpoint (CKPT) process frees up limited memory space and ensures instance recovery. It is a background process that monitors the number of dirty buffers that have not been written to disk. Depending on the LOG_CHECKPOINT_INTERVAL parameter, it also gives the DBWR a wakeup call to write dirty buffer to disk. At a specified time, it updates all the data and control files with the new log file control number. This process is optionally enabled if parameter CHECKPOINT_PROCESS contains a TRUE value. Other background system processes ... Redo log files are achived to tape or other media by the ARCH background process. In Oracle 8i, the DBWR_IO_SLAVES parameter defines the number of I/O slaves to improve performance. The v$archive view provides information on the archived logs written by ARCn (ARCH pre Oracle8i). Alert Logs Database start-ups and other commands and responses to commands to a database are stored in an Alert Log. Trace Writer (TRWR) The operating system Process ID for each running process are maintained in a trace file located in the file name specified in parameter BACKGROUND_DUMP_DEST. By default, that's folder /admin/INSTANCE_NAME/bdump under the ORACLE_BASE folder. User trace Database trace start-up and other commands and responses to commands to a database are stored in an Alert Log. Archival Process (ARCn) The full online redo logs are copied to the archived redo log files by this process. If the database is operating in archivelog mode AND the ARCHIVE_LOG_START parameter in the init.ora file is not set to TRUE, the database will hang.

76