9i SL 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Contents 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 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 • • • • •
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
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 •
Import
Sql Loader Oracle Architecture •
34
Page No
Introduction to DBMS Database: 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 form of nodes nodes and branch branches es.. The data data eleme elements nts presnt presnt in the struct structure ure of parent parent/ / child 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 The Netwo Network rk model model exten extended ded the Hierar Hierarchi chical cal model model by allowi allowing ng a record record to partic participa ipate te 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 eliminate eliminated d the explicit explicit parent/child parent/child structures structures from the database, database, and instead of represented 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: 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: 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: 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: 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 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: 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: 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: 8: The physical independence Rule The user access to the database remains logically consistent even if the storage representation is changed.
3
Rule 9: 9: The logical data independence Rule Applic Applicati ation on progra programs ms and termin terminal al activi activitie ties s must must remain remain logica logically lly unimp unimpair aired ed whenev whenever er information preserving changes of any kind, that are theoretically permitted, are made to the base tables. Rule 10: 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: 11: Distribution Rule A RDBMS RDBMS must must have distribution distribution independe independence. nce. Application Application running running on a non-distr non-distribute ibuted d database must remain logically unimpaired if that data becomes distributed in the context of a distributed relational database. Rule 12: 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 T eacher Many to one (M:1) - Many teachers - HOD Many to Many (M:N) - Many Courses - Many students Normalization: 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 expressi expressivene veness ss of an object-o object-orien riented ted programm programming ing language. language. An ObjectObject- oriented 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 orriented concepts concepts to design design is that databse databse is that such a databse is fully compatibl compatible e with object object – orient oriented ed applic applicati ations ons and system systems. s. Morev Morever, er, an OORDBM OORDBMS S is orient oriented ed toward towards s operation operations s on single single objects. objects. RDBMSs are very inefficient inefficient in their performan performance ce 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. T his 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
p,s ) 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 in integer nu numbers. An An In Integer nu number do does no not co 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 2 31 -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 character characters. s. Both fixed-wid fixed-width th and variablevariable-width width character 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 to a la large binary fi file st stored ou outside th the da 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 Allo Allow w the the time time to be stor stored ed as a data data with with frac fracti tion on of seco second nds. s. fractional_seconds_pr fractional_seconds_precision is the number of digits in the fractional ( fractional_seconds_pr ecision ) part of the SECOND datetime field.
15
TIMESTAMP All values of TIMESTAMP TIMESTAMP as well as time zone displacemen displacementt value, value, fractional_seconds_pr where fractional_seconds_precision is the ( fractional_seconds_pr the numb number er of digi digits ts in the the ecision ) WITH TIME fractional part of the SECOND datetime field. ZONE
6
16
TIMESTAMP All values values of TIMEST TIMESTAMP AMP WITH TIME TIME ZONE, ZONE, with with the follow following ing fractional_seconds_pr exceptions: ( fractional_seconds_pr ecision ) WITH WITH LOCA LOCAL L 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 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 ( fractional_seconds_pr ecision )
New Datatypes in 10g 19
BINARY_FLOAT
Stores a single precision 32 32-bit fl floating-point nu number.
20
BINAR INARY_ Y_DO DOUB UBLE LE
Stor Stores es a sing single le prec precis isio ion n 64-b 64-bit it floa floati ting ng-p -poi oint nt numb number er..
Example of The New data Type In Oracle 9i TIMESTAMP TIMESTAMP
SQL> create create table emp23 emp23 (empno (empno number(2 number(2), ), start_ start_date date timestam timestamp(7)) p(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 timestamp SQL> create table emp23 (empno number(2), start_date timestamp with time with time zone) ; 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 TIMESTAMP SQL> create table emp23 fractional_se ( fractional_se (empno number(2), number(2), order_date timestamp timestamp with local time zone) zone) ; conds_precisi SQL> insert into emp23 values (23,'15-Nov-2004 09:34:24 AM') ; on ) WITH SQL> select * from emp23 ; LOCAL TIME ZONE EMPNO ORDER_DATE --------------------------------------------------------------------------23 15-NOV-04 09.34.24.000000 AM INTERVAL SQL> create table emp23 YEAR TO (empno number(2), loan_duration interval year (3) (3) to Month) ; MONTH SQL> insert into emp23 values (23,interval '120' month(3)) ; SQL> select to_char(sysdate+loan_duration to_char(sysdate+loan_duration,'dd-mon-yyyy') ,'dd-mon-yyyy') from emp23 ; TO_CHAR(SYS -----------------12-may-2015 INTERVAL SQL> create table emp23 (empno number(2), number(2), DAY TO day_duarion interval day (3) to second) ; 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 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, INSERT, UPDATE, UPDATE, DELETE, DELETE, MERGE MERGE CREATE, CREATE, ALTER, ALTER, DROP, RENAME, RENAME, TRUNCATE COMM COMMIT IT,, ROL ROLLB LBAC ACK, K, SAVE SAVEPO POIN INT T GRANT, REVOKE To open the isqlplus
Data retrieval Language Data Manipulati Manipulation on Langu Language age Data Definition Language Tran Transa sact ctio ion n Con Contr trol ol Data Control Language
http://localhost/isqlplus
Desc[ribe] EMP
Desc[ribe] Dept
Desc[ribe] Locations
EMPNO NUMBER(4) DEPTNO NUMBER(2) ENAME VARCHAR2(10) DNAME VARCHAR2(14) JOB VARCHAR2(9) LOCATION_ID NUMBER(3) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
LOCATION_ID LOC_NAME
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
20
7654
MARTIN
SALESMAN
7698
28-SEP-81
1250 1250 1400
30
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 0
30
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
8
SQL> select * from dept; DEPTNO
DNAME
LOCATION_ID LOCATION_I D
10
ACCOUNTING
100
20
RESEARCH
200
30
SALES
300
40 OPERATIONS SQL> select * from locations;
400
LOCATION_ID
LOC_NAME
100
NEW YORK
200
DALLAS
300
CHICAGO
400
BOSTON
SQL* PLUS EDITING COMMANDS CO MMANDS 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 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 their EMPNO, name, designation ,salary ,salary and department no from from EMP table SQL> select EMPNO,ENAME,JOB,SAL,DEPTNO EMPNO,ENAME,JOB,SAL,DEPTNO from emp ; ARITHMETRIC EXPRESSION Display all the Employees Employees of of their EMPNO, EMPNO, name, adding $1000 to everybodies everybodies salary salary from EMP table with changing the salary column heading SQL> select empno,ename, empno,ename,sal sal + 1000 "Total Salary" Salary" from emp; SQL> select empno,ename,sal empno,ename,sal + 1000 1000 as TotalSalary from emp; SQL> select empno,ename, empno,ename,sal sal + 1000 TotalSalary TotalSalary from emp; - Putting 0 in place of null values SQL> select empno,ename,sal, empno,ename,sal, 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 emp where where sal > 1000 and job='SALESMAN' job='SALESMAN' ; SQL> select * from emp where sal > 1000 or job='SALESMAN' job='SALESM AN' ; SQL> select select * from emp emp where where job in ('SALESMAN','CLERK') ('SALESMAN','CLERK') ; SQL> select select * from emp emp where job not in ('SALESMAN','CLERK') ('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 empno,ename,sal from emp where deptno not in (10,20) ; SQL> select empno,ename,sal from emp where job in ('CLERK','ANALYST'); ('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 empno,ename,sal from from emp where ename ename not like 'S%' ; SQL> select empno,ename,sal empno,ename,sal from emp where sal between 1000 and 2000 ; SQL> select empno,ename,sal 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) (deptno,dname,loc) values (50,'DDDD','dddd') ; SQL> insert into dept (deptno,dname,loc) (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 set sal=sal*2 where deptno=40 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 deptno in (select deptno from emp1 where deptno=30) 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) 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 (e.empno,e.ename,e.sal,e.deptno); .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 (address1 varchar(100), varchar(100), fname varchar2(30)) ; alter table student modify (address varchar2(50), address1 varchar2(50)) 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; newtabalename; Rename emp to emp1 ; DATA CONTROL LANGUAGE (DCL) (GRANT, REVOKE) Database Security and Previleges Create 2 users, open the screen simultaneously, simultaneously, The synatx for creating a user SQL> Create user user1 identified by user1 SQL> Alter user user1 identified by user13 ; SQL> Grant Connect, resource to user1 ; SQL> Create user user2 identified by user2 ; SQL> Alter user user1 identified by user23 ; SQL> 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 user-supplied variables or constants constants and operating on them. Such variables or constants are called arguments. 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) Functi Functions ons that act on only only one value value at a time time are called called Scalar Scalar Functio Functions. ns. For exampl example, e, 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 abs(-15) from dual ; select power(3,2) power(3,2) from from dual ; select sqrt(25) from from dual ; select round(10.4567,2) from dual ; select trunc(10.4567,2) from dual ; String Functions select length('SURESH') from dual ; select lower('Sql Cousre') from dual ; select lower(ename) from emp ; select upper(ename) from emp ; select initcap(ename) from emp ; select concat('Hello','World') from dual ; select concat(empno,ename) concat(empno,ename) from emp ; select substr(ename,1,3) from emp; select instr('helloworld','w') from dual ; select ename,instr(ename,'A') ename,instr(ename,'A') from emp ; select lpad(sal,10,'*') from emp ; select rpad(sal,10,'*') from emp ; select ascii('s'),ascii('S') from dual ; select ename from emp where rowid=CHARTOROWID('AAAH2CAABAAA rowid=CHARTOROWID('AAAH2CAABAAAPAAAAK') PAAAAK') ; select chr(67) from dual; select chr(67)||chr(65)||CHR(84) chr(67)||chr(65)||CHR(84) from dual; select dump('abc',1016) from dual ; select greatest('ASSASA','DDD','CCCC') greatest('ASSASA','DDD','CCCC') from dual ; Date Functions select empno,hiredate,months_between(sysdate empno,hiredate,months_between(sysdate,hiredate) ,hiredate) from emp ; select empno,hiredate,add_months(hiredate,3) empno,hiredate,add_months(hiredate,3) from emp ; select empno,hiredate,next_day(hiredate,'FRIDAY empno,hiredate,next_day(hiredate,'FRIDAY') ') from emp ; select empno,hiredate,last_day(hiredate) empno,hiredate,last_day(hiredate) from emp ; select empno,hiredate,months_betwe empno,hiredate,months_between(sysdate,hire en(sysdate,hiredate),add_months date),add_months(hiredate,3), (hiredate,3), next_day(hiredate,'FRIDAY'), 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 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') to_date('01/01/2005','mm/dd/yyyy') from dual ; select to_date('23-05-2005','dd-mm-yyyy') to_date('23-05-2005','dd-mm-yyyy') from dual ; select to_char(sysdate,'dd/mm/yyyy') to_char(sysdate,'dd/mm/yyyy') from dual ; SELECT TO_CHAR(sysdate,'DD-MON-YYYY 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 select deptno,count(*) deptno,count(*) from emp group by deptno ;
SQL>
- List the department no and maxumim salary of employees in each department no select deptno,max(sal) 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) deptno,job,sum(sal) from emp group by rollup(deptno,job) ; select deptno,job,sum(sal) 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) lower(ename),upper(ename) from emp ; select upper(rtrim(ename)) upper(rtrim(ename)) + ' working as a ' + upper(rtrim(job)) 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) max(sal) from emp group by deptno having having count(*)> 5 Note: 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, salary, maximum maximum and minimum minimum salary and the avrag avrage e salary of emplyees 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 empno,ename,sal from emp order by ename asc ; select empno,ename,sal empno,ename,sal from emp order by ename desc; select empno,ename,sal from emp order by empno asc, ename desc; select empno,ename,sal empno,ename,sal from emp order by 3,2,1 ; Integrity Constraints • • • • •
Constraints are enforced on data being stored in a table, are called Constraints. Constraints super control 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: REFERENCES: Assigns a Foreign Key constraint to maintain “Referential Integrity” ON DELETE DELETE CASCADE CASCADE:: Using Using this this option option wheneve wheneverr a parent parent row is delete deleted d then 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 mast er 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 a nd 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) department(deptno) on delete cascade) ; Note: If a user is not giving the constraint constraint name, Oracle automatically create 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 user_constraints SQL> selec lect constraint_name,constraint)type table_Name=’TABLENAME’ 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,de emp.empno,emp.ename,dept.dname pt.dname from emp, dept where emp.deptno=dept.deptno emp.deptno=dept.deptno ; Using AND Operator select emp.empno,emp.ename emp.empno,emp.ename,dept.dname ,dept.dname from emp, emp, dept where emp.deptno=dept.de emp.deptno=dept.deptno ptno and emp.deptno=20 ; Using Table Aliases select e.empno,e.ename,d.dna e.empno,e.ename,d.dname me from emp e, dept d where e.deptno=d.deptno e.deptno=d.deptno and e.deptno=20 ; Joining More Than two Tables sele select ct e.em e.empn pno, o,e. e.en enam ame, e,d. d.dn dnam ame, e, l.lo l.loc_ c_Na Name me from from emp emp e, dept dept d, Loca Locati tion ons s l wher where e e.deptno=d.deptno e.deptno=d.deptno and d.location_id=l.location_id d.location_id=l.location_id ; 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 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 emp.empno,emp.ename,dept.dname ,dept.dname from emp, emp, dept where emp.deptno (+) = dept.deptno ; select emp.empno,emp.ename emp.empno,emp.ename,dept.dname ,dept.dname from emp, emp, dept where emp.deptno = dept.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 WORKER.ENAME,, MANAGER.ENAME MANAGER.ENAME “Manager” from emp emp worker, worker, emp manager manager where worker .mgr=manager.empno ; Cross Join select ename,dname from emp cross join dept ; select ename,dname ename,dname from 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 deptno,dname,location_id,loc_name from dept natural join locations ; select select deptno,dn deptno,dname, ame,locat location_ ion_id,lo id,loc_nam c_name e from dept natural natural join locations locations where where deptno 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.de e.empno,e.ename,e.deptno ptno from emp e join dept d ON (e.deptno=d.deptno) (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.dna e.empno,e.ename,d.dname,l.loc_name me,l.loc_name from from emp e join dept d ON (e.deptno=d.deptno) (e.deptno=d.deptno) join locations L ON (d.location_id=l.location_id) (d.location_id=l.location_id) ; Inner Joins Select e.ename,e.deptno,d.dnam e.ename,e.deptno,d.dname e from emp e inner join dept d on (e.deptno=d.deptno) (e.deptno=d.deptno) ; Left Outer Joins Select e.ename,e.deptno,d.dnam e.ename,e.deptno,d.dname e from emp e left outer join dept d on (e.deptno=d.deptno) (e.deptno=d.deptno) ; earlier:
select emp.empno,emp.ename,de emp.empno,emp.ename,dept.dname pt.dname from emp, dept where dept.deptno (+)= emp.deptno ; Right Outer Joins select e.ename,e.deptno,d.dnam e.ename,e.deptno,d.dname e from emp e right outer join dept d on (e.deptno=d.deptno) (e.deptno=d.deptno) ; earlier:
select emp.empno,emp.ename emp.empno,emp.ename,dept.dname ,dept.dname from emp, dept dept where dept.deptno = emp.deptno (+) ; Full Outer Joins select e.ename,e.deptno,d.dnam e.ename,e.deptno,d.dname e from emp e full outer join dept d on (e.deptno=d.deptno) (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 useful when need to select select rows from a table with a condition condition that depends 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 select deptno,min( deptno,min(sal) sal) from emp group group by deptno deptno having min(sal) min(sal) > (select (select min(sal) min(sal) from emp where deptno=20) ; Using Sub Query in the From Clause select e.ename,e.sal,e.deptno,b.salav e.ename,e.sal,e.deptno,b.salavg g 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 emp where sal < all (select avg(sal) 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 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 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 empno,ename,sal from emp where 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 EMPNO,ENAME,DEPTNO FROM EMP A WHERE A.DEPTNO= (SELECT DEPTNO FROM DEPT WHERE DEPTNO=A.DEPTNO); SQL> SELECT ENAME, ENAME, SAL, DEPTNO FROM EMP EMP OUTER WHERE EXISTS EXISTS (SELECT * FROM EMP WHERE DEPTNO= OUTER.DEPTNO) OUTER.DEPTNO) SQL> SELECT SELECT ENAME, ENAME, SAL, DEPTNO FROM FROM EMP OUTER WHERE NOT EXISTS EXISTS (SELECT (SELECT * FROM EMP WHERE DEPTNO= OUTER.DEPTNO); OUTER.DEPTNO); 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 subquery produces 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) emp.mgr=e.empno) ; List the employees details who do not manage any one. SQL> SQL> sele select ct enam ename, e,jo job b from from emp emp e wher where e NOT NOT EX EXIS ISTS TS (sel (selec ectt mgr mgr from from emp emp wher where e mgr=e.empno) 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 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> create view vname as select empno,ename, empno,ename,deptno deptno from emp ; SQL> create view EmpView as select empno,ename, empno,ename,sal sal from emp where empno=7900 ; SQL> select * from EmpView ; SQL> Drop view EMPPVIEW1 ; Insert, Update and Delete statement in a Simple View SQL>insert into vname (empno,ename,sal,deptno) (empno,ename,sal,deptno) values (4567,'XYZ',3456,50) (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 create view vname1 as select select e.ename,e.d e.ename,e.deptno eptno,d.d ,d.dname name 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> SQL> sele select ct sequ sequen ence ce_n _nam ame, e,mi min_ n_va valu lues es,m ,max ax_v _val alue ue,, user_sequences ; SQL> select Dept_seq .currval from dual ; SQL> select Dept_seq .nextval from dual ; SQL> Drop sequence Dept_seq ;
incr increm emen ent_ t_by by,,
last last_n _num umbe berr
from 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.) indexes .) Bitmap indexes, indexes , which store rowids associated with a key value as a bitmap Partitioned indexes, indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table Function-based Function-based indexes, 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 user-defined functions. Domain indexes, 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. 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). block(s). This clauses improvemen improvementt of perform performance ance 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 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 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> SQL> sele select ct tabl tables espa pace ce_n _nam ame, e, part partit itio ion_ n_na name me from from table_name='emp_sub_template' table_name='emp_sub_template' order by tablespace_name; tablespace_name;
dba_ dba_ta tab_ b_su subp bpar arti titi tion ons s
wher where e
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 tunable method of data placement. To Create HASH Partition SQL> create table purchases purchases (inv_no number , day number, number, month number, year number) partition by hash (day, month, year) (partition h1 tablespace ts1, partition h2 ta blespace ts2, partition h3 t ab ablespace 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 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 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; 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 use it for manipulations, but the 1stuser 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 empno,ename from emp where job job = '&job' ; Specifying Column Names, Expressions and Text select empno,ename,job,&column_name empno,ename,job,&column_name from emp where &condition order by &Order_column &Order_column ; Using DEFINE Command DEFINE empno=7902 ; select empno,ename,sal,deptno empno,ename,sal,deptno from emp where empno= &empno ; Using the && Substitution Variable
27
select empno,ename,job,&&column_name empno,ename,job,&&column_name from emp order by &column_name ; Using Verify Commands set verify on ; select empno,ename,sal,deptno empno,ename,sal,deptno from emp where empno=&empno ; SQL Sample Report set feedback off TTITLE 'EMployee|Report' 'EMployee|Report' BTITLE 'Confidential' BREAK ON JOB COLUMN JOB HEADING 'JOB|Cattegory' 'JOB|Cattegory' COLUMN ename HEADING 'EMPLOYEE NAME' COLUMN sal HEADING 'SALARY' ' 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 empno,ename,job,deptno from emp where deptno=20) values (2222,'ddddd','MANAGER',30) (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) (2222,'ddddd','MANAGER',30) ; Using Explicit Default Values insert into emp (empno,ename,sal,hirdate) (empno,ename,sal,hirdate) values (222,'dddd',3333,default) ; Performing TOP - N Analysis select select rownum as rank, ename,sal ename,sal from (select (select ename,sal ename,sal from emp order order by sal desc) where where rownum <= 3 ; Unconditional INSERT ALL INSERT ALL INTO sal_history values (empno,hiredate,sal) (empno,hiredate,sal) INTO mgr_history values (empno,mgr,sal) select empno,hiredate,sal,mgr 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) (empno,hiredate,sal) WHEN MGR > 200 THEN INTO mgr_history values (empno,mgr,sal) select empno,hiredate,sal,mgr empno,hiredate,sal,mgr from emp where empno > 1000 ;
28
Pivoting Insert INSERT ALL INTO sales_info values (empno,week_id,sales_mon) (empno,week_id,sales_mon) INTO sales_info values (empno,week_id,sales_TUE) (empno,week_id,sales_TUE) INTO sales_info values (empno,week_id,sales_WED) (empno,week_id,sales_WED) INTO sales_info values (empno,week_id,sales_THU) (empno,week_id,sales_THU) INTO sales_info values (empno,week_id,sales_FRI) (empno,week_id,sales_FRI) select empno,week_id,sales_mon,s empno,week_id,sales_mon,sales_tue,sales ales_tue,sales_wed,sales_thu _wed,sales_thu,sales_fri ,sales_fri from sales_source_data 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 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. Easy Maintenance 2. Improved data security and integrity. 3. Improved performance. performance. 4. 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 % 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 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 dbms_output.put_line('The monthly salary' || v_sal ) ; end ; Commenting Code /* */ declare V_ename emp.empno%type ; V_sal emp.sal%type; 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 dbms_output.Put('the value of z is '||x) ; dbms_output.Put_line('the dbms_output.Put_line('the value of z is '||y) ; dbms_output.Put_line('the 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 emp.ename%type ; begin select empno,ename into mempno,mename from emp where empno=7902; dbms_output.Put_line('The 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) (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 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 Lo Loop IF STATEMENTS Syntax: IF then elsif then else 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 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) dbms_output.put_line(a) ; end ;
Basic Loop The syntax is; LOOP exit [loop-label] [WHEN Condition] end loop ; Example :
declare i number:=1 ; begin
33
loop dbms_output.put_line(i) dbms_output.put_line(i) ; i := i+1 ; exit when i > 10 ; end loop; end ; WHILE Loop The syntax is; WHILE [condition]LOOP end loop ; Example
declare i number:= 1 ; begin while I < 11 loop dbms_output.put_line(i) dbms_output.put_line(i) ; i := i+1 ; end loop; end ; FOR Loop The Syntax is : FOR IN .. LOOP End loop Example
declare i number ; begin for i in 1..10 loop dbms_output.put_line(i) 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 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 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. 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; 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 TRUE if the most resent resent fetch does not return return a row %FOUND - Evaluates TRUE if the most recent fetch 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 v_empno,V_ename,v_sal ; dbms_output.put_line('The dbms_output.put_line('The Employee No: ' ||v_empno||' ||v_empno||' and name is ' ||v_ename|| ||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; 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; v_empno,v_sal; dbms_output.put_line 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; 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; v_empno,v_sal; exit when c1%notfound; c1%notfound; dbms_output.put_line 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; emp.empno%type; cursor c1 is select empno,ename,job,sal empno,ename,job,sal from emp ; begin open c1; loop fetch c1 into mempno,mename,mjob,msal; mempno,mename,mjob,msal; if msal < 2000 then update emp set sal=msal*2 where empno=mempno; dbms_output.put_line(mempno dbms_output.put_line(mempno||' ||' '||mename||' '||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 dbms_output.put_line(mempno||' ||' '||mename||' '||mename||' '||msal*.5||' '||msal*.5||' '||mjob) ; elsif msal > 4000 then update emp set sal=msal*.2 where empno=mempno; dbms_output.put_line(mempno dbms_output.put_line(mempno||' ||' '||mename||' '||mename||' '||msal*.2||' '||msal*.2||' '||mjob) ; end if ; exit when c1%notfound ; end loop ; close c1 ; end ; FOR LOOP Declare cursor c1 is select empno,ename,deptno empno,ename, deptno from emp; begin for emp_record in c1 loop if emp_record.deptno=20 emp_record.deptno=20 then dbms dbms_o _out utpu put. t.pu put_ t_li line ne(' ('Em Empl ploy oyee ee No ' ||em ||emp_ p_re reco cord rd.e .emp mpno no ||' ||' Name Name is emp_record.ename); end if; end loop; end;
'|| '||
Implicit Cursor Attributes SQL%ISOPE SQL%ISOPEN N - Is always always false because because Oracl Oracle e automatic automatically ally close close an Implicit Implicit curso cursorr after after executing its SQL statement SQL%NOTFOUND - Evaluates TRUE TRUE if the DML statement was not not suceesful suceesful %FOUND - Evaluates TRUE if the DML statement was suceesful 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 cursor_name (parameter (parameter name data type) is select statement statement Begin Open cursor_name 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; emp.ename%type; mempno emp.empno%type; 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') (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’ job=’MANAGER’ ; Elsif vdno = 20 then Open c1 for select * from emp where job=’SALESMAN’ job=’SALESMAN’ ; Else Open c1 for select * from emp where job=’CLERK’ job=’CLERK’ ; End if; Loop Fetch c1 into vrec ; Exit when c1%notfound; c1%notfound; dbms_output.put_line(vrec.em dbms_output.put_line(vrec.empno||’ pno||’ ‘||vrec.ename) ‘||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; 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 v_empno,V_ename,v_sal ; dbms_output.put_line('The dbms_output.put_line('The Employee No: ' ||v_empno||' ||v_empno||' and name is ' ||v_ename|| ||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 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 User-Defined Exception 3) Non-Pre Defined Exception 1) Pre Defined Exception The predefin predefined ed oracle oracle package package standard standard has defined defined exceptio exceptions ns for certain certain common common errors. errors. Exceptions are handled using names not using error numbers. The errors which are not handles using exception, can be handled usinf exception ‘O THERS’ 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; 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 dbms_output.put_line(mename||msal) ||msal) ; end if; exception when no_data_found then dbms_output.put_line('Invaild 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) dbms_output.Put_line(z) ; exception when zero_divide then dbms_output.Put_line('The 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 dbms_output.Put_line('The emp name is '||mename||' '||mename||' salary is '||msal) ; exception when too_many_rows then dbms_output.Put_line(' 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; emp.deptno%type:=&deptno; begin select sal into x from emp where deptno=y; dbms_output.put_line('sal 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 dbms_output.put_line('more then one error selected'); when others then dbms_output.put_line('no 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 DBMS_OUTPUT.PUT_L INE('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) dbms_output.Put_line(msal) ; end if; exception when emp_exc then dbms_output.put_line('salary dbms_output.put_line('salary is greater than 4000') ; end; RAISE_APPLICATION_ERR RAISE_APPLICATION_ERROR OR Procedure: Is used to communicate a predefined exception interactively by defining a non standard error code and error message. RAISE_APPLICATION_ERROR 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_ERRO RAISE_APPLICATION_ERROR R (-20202,'wrong empno') ; end if ; end ; 2) declare x emp.sal%type; y emp.empno%type:=&empno; emp.empno%type:=&empno; sal_exc exception ; begin select sal into x from emp where empno=y; if x > 3000 3000 then then raise sal_exc; else dbms_output.put_line('sal 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, raise_application_error(-20001,'Salary 'Salary is greater than 3000') ;
41
when others then dbms_output.put_line('no dbms_output.put_line('no data found'); end;
Non-Predefined 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_remain EXCEPTION_INIT(e_emp_remaining,-2292) ing,-2292) ; begin delete from dept1 where deptno=10 ; commit; exception when e_emp_remaining then dbms_output.put_line('canot dbms_output.put_line('canot remove dept '); end; -----------------------------------declare z_divide EXCEPTION ; PRAGMA EXCEPTION_INIT(z_divide,-1476) 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 dbms_output.put_line('Number is divided zero '); end; -----------------------------------declare t_row_many EXCEPTION ; PRAGMA EXCEPTION_INIT(t_row_many,-1422) 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 dbms_output.put_line('Too Many Rows returned '); end; ------------------------------------
42
declare t_row_many EXCEPTION ; PRAGMA EXCEPTION_INIT(t_row_many,-1422) 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 dbms_output.put_line('Too Many Rows returned '); end; Error – Trapping Functions Functions:: When an Exception occurs, we can identify the associated error code and error message by using two functions. SQLCODE SQLCODE returns returns number of the Oracle error error for internal internal exception. 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) (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 (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 from the calling enviro environment nment into the proce procedure dure 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 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, emp.empno%type, vname out emp.ename%type, 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; 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 format_phone (p_phone_no in out varchar2) is begin p_phone_no := '(' || substr(p_phone_no,1,3) substr(p_phone_no,1,3) || ')' || substr(p_phone_no,4,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) 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; dept.dname%type; begin select dname into vdname from dept where deptno=mdeptno ; dbms_output.put_line('The 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 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 dbms_output.put_line('The employee name is' ||vename||' Salary is ' || vsal) ; EXCEPTION WHEN no_data_found then dbms_output.put_line('No 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; 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 mename,msal,mjob ; if c1%rowcount < 1 then raise check_emp ; else dbms_output.put_line('the dbms_output.put_line('the employee name is '||mename||' '||mename||' end if ; exit when c1%notfound ; end loop ; exception when check_emp then dbms_output.put_line(' 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 locations.loc_name%type default 'unknown') IS begin insert into locations values (vlocation_id,vloc_name); (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) 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') dbms_output.put_line('right') ; else dbms_output.put_line('wrong') 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 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 Packag Package e is an Oracle Oracle object, object, which which holds holds other other objec objects ts within within it. Objec Objects ts commonly held within a package are procedures, functions, variables, constants, cursors and exceptions. A Package consists of two components - Specification - Body A Package Package Specifica Specification tion declares declares the types types (variable (variable of the Record Record type), type), memory memory variables variables,, constants, exceptions, cursors cursors and subprograms that are available for use. A Packag Package e body body fully fully define defines s curso cursor, r, funct function ions s and proced procedure ure and thus thus implem implement ents s 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; 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 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 dbms_output.put_line('dname is '|| mdname) mdname) ; end;
50
end; To execute the previous program -------------------------------declare begin dbms_output.put('the dbms_output.put('the emp ') ; emp_proc.emp_pr(7369) emp_proc.emp_pr(7369) ; dbms_output.put('the dbms_output.put('the dept ') ; emp_proc.dept_pr(20) 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 dbms_output.put_line('the multiplication of'||x ||'and'||y||':='||z); ||'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 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 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; emp.empno%type; mdname dept.dname%type; dept.dname%type; procedure proc_emp is begin open c1; loop fetch c1 into mempno ; dbms_output.put_line(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) 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 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 functi functions ons.. More More than than one proced procedur ure e and funct function ion with with the same same name name but but with with differ different ent 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. Simila Similarly rly,, multi multiple ple functi functions ons that that are declar declared ed with with the same same name name are called called Overlo Overloade aded d 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') 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('Employe dbms_output.put_line('Employee e name is '||mename) ; exception when no_data_found then dbms_output.put_line('No 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 deptno=mdep tno ; dbms_output.put_line('Employe dbms_output.put_line('Employee e name is '||mename||' salary is '||msal) ; exception when no_data_found then dbms_output.put_line('No 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 Trigger - fires whenever INSERT, UPDATE, UPDATE, DELETE
55
Schema Trigger – CREATE, ALTER, DROP DatabaseTrigger - fires whenever whenever system events (such (such as Logon, shutdown or startup) occurs 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 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') dbms_output.put_line('before') ; end ; -----------------------------------------------------------------------create or replace trigger emp_aft_inst
56
after insert on emp begin dbms_output.put_line('after') 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 dbms_output.put_line('New employee no is ' ||meno) ; end ; -------------------------------------------------create or replace trigger res_salary before insert on emp1 begin RAISE_APPLICATION_ERROR RAISE_APPLICATION_ERROR (-20202,'Employee cannot insert'); end; insert into emp1 (empno,ename,sal,deptno) (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 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 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_ERRO RAISE_APPLICATION_ERROR(-20000, R(-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') to_char(sysdate,'HHMM') into a from dual ; if a between '0100' and '0200' then raise_application_error(-20202,'U 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') (to_char(sysdate,'HH24:MI') not between '08:00' ' 08:00' and '18:00') then raise_application_error 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 raise_application_error (-20500,'You may deleting emp table only during businees'); elsif inserting then raise_application_error raise_application_error (-20500,'You may insert into emp table only during businees'); elsif updating ('SAL') then raise_application_error raise_application_error (-20500,'You may updating emp table only during businees'); else raise_application_error 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') (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 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,'E raise_application_error(-20202,'Employee mployee cannot earn this amount') ; end if ; end; Trigger (ROW LEVEL)(Using LEVEL)(Using :old and :new qulaifier) create table audit_emp_table (USERNAME VARCHAR2(30), TIMESTAMP DATE, ID VARCHAR2(20), VARCHAR2(20), OLD_LAST_NAME VARCHAR2(30), NEW_LAST_NAME VARCHAR2(30), OLD_TITLE VARCHAR2(20), 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 AUDIT_EMP_TABLE (USERNAME,TIMESTAMP,ID,OL (USERNAME,TIMESTAMP,ID,OLD_LAST_NAME, D_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) (empno,ename,job,sal,deptno) values (3456,'dddd','MANAGER',2456,30) (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 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 e.empno,e.ename,e.sal,e.deptno,e.job,d.dname, .job,d.dname,d.loc d.loc from emp e, dept d where d.deptno=e.deptno 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.empno,:new.ename,:new.sal,:new :new.sal,:new.deptno,sysda .deptno,sysdate,:new.job) te,: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','AC (1000,'suresh',3456,30,'MANAGER','ACCOUNTING') COUNTING') ; 2) create table NEW_EMP as select empno,ename,deptno from emp ; create table new_dept as select deptno,dname from dept ; create create view emp_dept_vie emp_dept_view w as select select e.empno,e e.empno,e.ena .ename,e me,e.dept .deptno,d no,d.dna .dname me from emp e ,dept ,dept d where e.deptno=d.deptno ; create or replace trigger ins_trigs instead of insert on emp_dept_view emp_dept_view begin insert into new_emp values (:new.empno,:new.ename,:new.Deptn (:new.empno,:new.ename,:new.Deptno) o) ; insert into new_dept values (:new.deptno,:new.dname) (: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 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 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, (USER_ID,LOG_DATE, ACTION) VALUES (user,sysdate,'Logging (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, (USER_ID,LOG_DATE, ACTION) VALUES (user,sysdate,'Logging (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, Referencing_names, status, trigger_body from USER_TRIGGERS Where trigger_name='CHECK_SALA trigger_name='CHECK_SALARY’; RY’;
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); (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) 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); (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) 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 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 th the DD DDL or or DM DML st ststements: th that is is, ch checks th the statements’s syntax and associates it with the opned cursor (DDL statement are immediately executed when parsed) Binds th the giv give en va value to to th the va variable id identified by by it its na name in in the parsed statement in the given cursor Executes the SQL statement and returns the number of rows processed Retrives a ro row fo for th the specified cu cursor (for multiple ro rows, ca call in a loop) Closes the specified cursor
create or replace procedure delete_all_rows 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_na DBMS_SQL.PARSE(cursor_name,'DELETE me,'DELETE From '||P_tab_name,dbms_sql.native) '||P_tab_name,dbms_sql.native) ;
63
p_rows_del := DBMS_sql.EXECUTE(cursor_nam DBMS_sql.EXECUTE(cursor_name) e) ; DBMS_SQL.close_cursor(cu DBMS_SQL.close_cursor(cursor_name) rsor_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 NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE 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 SESSIONTIMEZONE CURRENT_DATE --------------- --------------------08:00 29-MAY-2000 10:14:33 CURRENT_TIMESTAMP Purpose CURRENT_TIMESTAMP 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 NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE SESSIONTIMEZONE CURRENT_TIMESTAMP 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 SESSIONTIMEZONE CURRENT_TIMESTAMP 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 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_TIMESTA (TO_TIMESTAMP_TZ(CU RRENT_TIMESTAMP, MP, '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, (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 time zone zone names names and their their corre correspo spondi nding ng abbrev abbreviat iation ions, s, query query the V$TIME V$TIMEZON ZONE_ E_NAM NAMES ES dynamic performance view. SELECT EXTRACT(YEAR 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: SELE SELECT CT enam ename, e, empn empno, o, hire hireda date te FROM FR OM TO_DATE(hiredate, 'DD-MON-RR')) < 1998 ORDER BY hiredate;
emp emp
WHER WHERE E
EXT EXTRA RACT CT(Y (YEA EAR R
FRO FROM M
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, 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 Oracle provid provides es full full suppo support rt for all three three differ different ent implem implement entati ation on – Relati Relation onal, al, Object Object-Relational, Object- Oriented Relational. Why Should Objects Be Used? Object Objects s reduce reduce compl complexi exity ty by giving giving an intuit intuitive ive way of repres represent enting ing compl complex ex data data and its relations. Besides Besides simplifyi simplifying ng the interaction interaction with data, objects objects may help in other other ways. Examples 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.Name Client.Person.Address.C Client.Person.Address.City ity FROM customer client client.person.address.city like ‘M%’ ; WHERE client.person.address.city Updating Data using Objects UPDATE customer client SET client.person.addess.city=’Mu client.person.addess.city=’Mumbai’ mbai’ where Client.Person.Address.C Client.Person.Address.City=’Chennai’ ity=’Chennai’ ; Deleting Data using Objects DELETE from customer client where client.person.addess.city= client.person.addess.city=’Mumbai’ ’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 person_ty) person_ty) ;
69
create view cust_obj of address_ty with object oid (customer_id) (customer_id) as select customer_id,person_ty(name customer_id,person_ty(name,address_ty(Stre ,address_ty(Street,city,state,z et,city,state,zip)) ip)) 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 saving storag storage e space. space. Varyin Varying g arrays arrays can be used used to accur accurate ately ly repre represen sentt one –to –many –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 eleme elements nts of data data type type varcha varchar2( r2(100 1000), 0), i.e. i.e. 3 entrie entries s per recor record, d, each each storin storing g addres address s 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); 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 establish relationship relationship between between two-obje two-object ct tables, tables, much much the same way as a primaryprimarykey/foreign-key relationship in relational tables. Relational tables however, have difficulty, if more than one table is needed in a primary-key/foreign-key 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, operation, but takes more space. If with ROWID is not specified with the REF clause, the defaul defaultt is to not store store ROWIDs ROWIDs with with the RE REF F values values.. SCOPE SCOPE clause clauses s preve prevent nt dangli dangling ng 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 T he 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) 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: Table: The names of the tables to be exported need to be specified o 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 username/passward file=filename.dmp log=filename.log Tables=tablename,tab Tables=tablename,tablename lename User Level Export C:\> exp system/manager system/manager file=filename.dmp file=filename.dmp log=filename.log owner=username Entire Database Level C:\> exp system/manager 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 username/passward file=filename.dmp log=filename.log Tables=(tablename,ta Tables=(tablename,tablename) blename)
71
Importing User C:\> C:\> Imp Imp syst system em/m /man anag ager er touser=username
file file=f =fil ilen enam ame. e.dm dmp p
log= log=fi file lena name me.l .log og
from fromus user er
=
user userna name me
Sql Loader •
•
This This tool tool is used used to move move data data from from a Non-Or Non-Oracl acle e standa standard rd sourc source e into into the Oracle 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. Process. Background Process is two types 1) Mandatory 2) Optional Mandatory are 1) Proc Proces ess s Moni Monito torr (PMO (PMON) N) 2) Syst System em Moni Monito torr (SMO (SMON) N) 3) Data Databa base se Wri Write terr (DBW (DBWR) R) 4) Log Log Wri Write terr (LG (LGWR WR))
72
5) Chec Checkp kpoi oint nt (CKP (CKPT) T) Optional are 1) Oracle Oracle Para Paralle llell Query Query Optio Option n (OPQO) (OPQO) 2) Multi DBWR 3) Arch Archiv iver er (ARC (ARCH) H) 4) Reco Recove vere rerr (Rec (Reco) o) Oracle Database consists mainly these files 1) Redo Log Fil File e 2) Contr ntrol File ile 3) Data File 4) Para Parame mete terr File File 5) Initia Initialis lisati ation on File File (init. (init.ora 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 T Bs. One data file belongs only one Tablespace. T ablespace. 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 Oracle Instance Instance.. Its conten contents ts is visibl visible/ e/acc acces essib sible le to severa severall user user applic applicati ations ons and system 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 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 information for each type of object in the library cache. A privat private e SQL area area is creat created ed for each each transa transacti ction on initia initiated ted.. It contai contains ns sessi sessionon-spe specif cific ic 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 improv improver er Oracle Oracle perfor performan mance ce,, Oracle Oracle uses uses a Shared Shared SQL area area to save save in cach cache e the the intermediate results from SQL commands previously returned. So before performing a hard disk operation operation,, Oracle Oracle can service service identical requests simply by retrieving them from its cached memory for reuse. 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. Files. Its size is initialize initialized d according according to the LOG_BUFFER LOG_BUFFER (bytes) parameter. parameter. The number of redo log space requests can be monitored using the v$sysstat view. Large Pool Intr Introd oduc uce ed with with Orac Oracle le 8 to sto store user ser sessio sion glo global bal area area info inforr and and for for para parall llel el processing/recovery. 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. 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. Syst System em Mon Monit itor or (SM (SMON ON)) 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. Proces Process s Monito Monitorr (PMON) (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 colle collecti ction" on",, and restar restarts ts failed failed proces processes ses in the databa database. se. It releas releases es locks locks held held by processes which failed before they released memory allocations. 3. Databa Database se Writer Writer (DBWn (DBWn)) The Database Writer (DBWR) writes and retrieves blocks from datafiles through the buffer cache. cache. Some Some sugge suggest st using using the DB_WRI DB_WRITER TERS S parame parameter ter to define define as many many DBWR DBWR processes as there are physical disks used to store data files. 4. Log Log Writ Writer er (LGW (LGWR) R) The Log Writer writes and retrieves its blocks from datafiles through the Redo Log Buffer. Buffer.
75
5. Check Checkpoi point nt Proc Process ess (CKPT) (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. process. In Oracle 8i, the DBWR_IO_SLAVES parameter defines the number of I/O slaves to improve performance. 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/bdum /admin/INSTANCE_NAME/bdump p under the ORACLE_BASE 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
