SOL Commands
SQL :Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle database.SQL provides statements for a variety of tasks, including:
o Querying data
o Inserting, updating, and deleting rows in a table
o Creating, replacing, altering, and dropping objects
o Controlling access to the database and its objects
o Guaranteeing database consistency and integrity
SQL unifies all of the above tasks in one consistent language.
Common Language for All Relational Databases
All major relational database management systems support SQL, so you can transfer all skills you have gained with SQL from one database to another. In addition, all programs written in SQL are portable. They can often be moved from one database to another with very little modification.
Summary of SQL Statements
The tables in the following sections provide a functional summary of SQL statements and are divided into these categories:
o Data Definition Language (DDL) Statements
o Data Manipulation Language (DML) Statements
o Transaction Control Statements
Data Definition Language (DDL) Statements
Data definition language (DDL) statements enable you to perform these tasks:
CREATE, ALTER, AND DROP SCHEMA OBJECTS
GRANT AND REVOKE PRIVILEGES AND ROLES
Data Manipulation Language (DML) Statements
Data manipulation language (DML) statements query and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction.
Statement
DELETE, INSERT, SELECT, UPDATE
Transaction Control Statements
Transaction control statements manage changes made by DML statements.
Statement
COMMIT, ROLLBACK, SAVEPOINT and SET TRANSACTION.
ORACLE DATABASES
An Oracle database is a collection of data that is treated as a unit. The general purpose of a database is to store and retrieve related information.
The database has logical structures and physical structures.
Logical Database Structures:
The logical structures of an Oracle database include tablespaces, schema objects, data blocks, extents, and segments.
Physical Database Structures:
Every Oracle database has one or more physical datafiles. A database's datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.
The characteristics of datafiles are:
• A datafile can be associated with only one database.
• Datafiles can have certain characteristics set to allow them to automatically extend when the database runs out of space.
• One or more datafiles form a logical unit of database storage called a tablespace
The Data Dictionary
Each Oracle database has a data dictionary. An Oracle data dictionary is a set of tables and views that are used as a read-only reference about the database. For example, a data dictionary stores information about both the logical and physical structure of the database. In addition to this valuable information, a data dictionary also stores such information as:
• the valid users of an Oracle database
• information about integrity constraints defined for tables in the database
• how much space is allocated for a schema object and how much of it is in use
Instance and Database Startup
The three steps to starting a Oracle database and making it available for systemwide use are:
1. Start an instance.
2. Mount the database.
3. Open the database.
Database and Instance Shutdown
The three steps to shutting down a database and its associated instance are:
1. Close the database.
2. Dismount the database.
3. Shut down the instance.
CREATE COMMANDS
Create Table
CREATE TABEL EMP(empno number, ename varchar2(30), hiredate date);
CREATE TABLE scott.emp
(empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL CONSTRAINT upper_ename CHECK (ename = UPPER(ename)),job VARCHAR2(9),mgr NUMBER CONSTRAINT fk_mgr REFERENCES scott.emp(empno),hiredate DATE DEFAULT SYSDATE, sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal > 500),comm NUMBER(9,0) DEFAULT NULL, deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL CONSTRAINT fk_deptno REFERENCES scott.dept(deptno))PCTFREE 5 PCTUSED 75;
Display the Constraints in Table:
DESC USER_CONSTRAINTS;
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMPLOYEES';
Storage Example
CREATE TABLE salgrade(grade NUMBER CONSTRAINT pk_salgrade RIMARY KEY USING INDEX TABLESPACE users_a,losal NUMBER,hisal NUMBER ) TABLESPACE human_resource STORAGE (INITIAL 6144 NEXT 6144 MINEXTENTS 1 MAXEXTENTS 5 );
PARALLEL Example
CREATE TABLE emp_dept PARALLEL AS SELECT * FROM scott.emp WHERE deptno = 10;
NOPARALLEL Example
CREATE TABLE emp_dept AS SELECT * FROM scott.emp WHERE deptno = 10;
Partitioned Table Example
CREATE TABLE stock_xactions (stock_symbol CHAR(5),stock_series CHAR(1), num_shares NUMBER(10),price NUMBER(5,2),trade_date DATE) STORAGE INITIAL 100K NEXT 50K) LOGGING PARTITION BY RANGE (trade_date) (PARTITION sx1992 VALUES LESS THAN (TO_DATE('01-JAN-1993','DD-MON-YYYY'))TABLESPACE ts0 NOLOGGING,PARTITION sx1993 VALUES LESS THAN TO_DATE('01-JAN-1994','DD-MON-YYYY'))TABLESPACE ts1,PARTITION sx1994 VALUES LESS THAN (TO_DATE('01-JAN-1995','DD-MON-YYYY'))TABLESPACE ts2);
Object Table Examples
Consider object type DEPT_T:
CREATE TYPE dept_t AS OBJECT(dname VARCHAR2(100),address VARCHAR2(200) );
Object table DEPT holds department objects of type DEPT_T:
CREATE TABLE dept OF dept_t;
CREATE OR REPLACE TYPE salesrep_t AS OBJECT(repId NUMBER,repName VARCHAR2(64));
CREATE TABLE salesreps OF salesrep_t;
REF Example
The following example creates object type DEPT_T and object table DEPT to store instances of all departments. A table with a scoped REF is then created.
CREATE TYPE dept_t AS OBJECT (dname VARCHAR2(100),address VARCHAR2(200) );
CREATE TABLE dept OF dept_t;
CREATE TABLE emp(ename VARCHAR2(100),enumber NUMBER,edept REF dept_t SCOPE IS dept );
The following statement creates a table with a REF column which has a referential constraint defined on it:
CREATE TABLE emp(ename VARCHAR2(100),enumber NUMBER,edept REF dept_t REFERENCES dept);
Create Table Using Select Statements:
CREATE TABLE EMP1 AS SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPTNO AS SELECT * FROM SCOTT.DEPT ORDER BY DEPTNO ASC;
Constraints on Type Columns Example
1.CREATE TYPE address AS OBJECT(hno NUMBER,street VARCHAR2(40),city VARCHAR2(20),zip VARCHAR2(5),phone VARCHAR2(10) );
2. CREATE TYPE person AS OBJECT(name VARCHAR2(40),dateofbirth DATE, homeaddress address,manager REF person );
3.CREATE TABLE persons OF person(homeaddress NOT NULL UNIQUE (homeaddress.phone),CHECK (homeaddress.zip IS NOT NULL),CHECK (homeaddress.city <> 'San Francisco') );
Create Index
An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. A partitioned index consists of partitions containing an entry for each value that appears in the indexed column(s) of the table. A function-based index is an index on expressions. It enables you to construct queries that evaluate the value returned by an expression, which in turn may include functions (built-in or user-defined).
PARALLEL Example
The following statement creates an index using 10 parallel execution servers, 5 to scan SCOTT.EMP and another 5 to populate the EMP_IDX index:
CREATE INDEX emp_idx ON scott.emp (ename) PARALLEL 5;
COMPRESS Example
To create an index with the COMPRESS clause, you might issue the following statement:
CREATE INDEX emp_idx2 ON emp(job, ename) COMPRESS 1;
Cluster Index Example
To create an index for the EMPLOYEE cluster, issue the following statement:
CREATE INDEX ic_emp ON CLUSTER employee;
No index columns are specified, because the index is automatically built on all the columns of the cluster key. For cluster indexes, all rows are indexed.
Partitioned Tables And Indexes
Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways:
We can devided the rows of a single table into multiple parts,Dividing the table's data in this manner is called "Partitioning the table". Partitioning is useful for very large tables and it is used for..
Improve the Qurey performance,it may be easier to load and delete the data in the partitions.
There are two type of prtitions
Range:
CREATE TABLE EMP1(ENO NUMBER PRIMARY KEY,NAME VARCHAR2(20),SAL NUMBER(8,2),
DEPTNO NUMBER REFERENCES DEPT(DEPTNO))
PARTITION BY RANGE(DEPTNO)
(PARTITION PART1 VALUES LESS THAN(20),
PARTITION PART2 VALUES LESS THAN(30));
Hash:
CREATE TABLE EMP1(ENO NUMBER PRIMARY KEY,NAME VARCHAR2(20),SAL NUMBER(8,2),
DEPTNO NUMBER REFERENCES DEPT(DEPTNO))
PARTITION BY HASH(DEPTNO)
PARTITIONS 2;
Composite Partitioning Tables
Composite partitioning allows range partitions to be hash subpartitioned on a different key. The greater number of partitions increases the possiblities for parallelism and reduces the chances of contention. The following example will range partition the table on invoice_date and subpartitioned these on the invoice_no giving a totol of 32 subpartitions:
CREATE TABLE invoices (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL,
comments VARCHAR2(500)) PARTITION BY RANGE (invoice_date) SUBPARTITION BY HASH (invoice_no) SUBPARTITIONS 8
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY'));
Partitioning Indexes
There are two basic types of partitioned index:
• Local - All index entries in a single partition will correspond to a single table partition (equipartitioned). They are created with the LOCAL keyword and support partition independance. Equipartioning allows oracle to be more efficient whilst devising query plans.
• Global - Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and do not support partition independance. Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equipartitioned, but Oracle will not take advantage of this structure.
Both types of indexes can be subdivided further:
• Prefixed - The partition key is the leftmost column(s) of the index. Probing this type of index is less costly. If a query specifies the partition key in the where clause partition pruning is possible, that is, not all partitions will be searched.
• Non-Prefixed - Does not support partition pruning, but is effective in accessing data that spans multiple partitions. Often used for indexing a column that is not the tables partition key, when you would like the index to be partitioned on the same key as the underlying table.
Local Prefixed Indexes
Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning are examples of local prefixed indexes:
CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);
Global Prefixed Indexes
Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning examples is of a global prefixed index:
CREATE INDEX invoices_idx ON invoices (invoice_date) GLOBAL PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE users);
Create Procedures
A procedure is a group of PL/SQL statements that you can call by name.
Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.
CREATE TABEL EMP (EMPNO NUMBER, ENAME VARCHAR2 (30);
USING SELECT STATEMENT:
CREATE OR REPLACE PROCEDURE Get_Ename(Eno IN EMP.EMPNO%TYPE,
NAME OUT EMP.ENAME%ROWTYPE) AS
BEGIN
SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=Eno;
END;
/ (OR)
CREATE OR REPLACE PROCEDURE Get_Ename(Eno IN number,
NAME OUT varcahr2) AS
BEGIN
SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=Eno;
END;/
Create Variable: VARIABLE X VARCHAR2(30);
EXEC GET_ENAME(7788, :X)
PRINT X;
USING DELETE STATEMENT:
CREATE OR REPLACE PROCEDURE fire_emp1(Emp_id NUMBER) AS
BEGIN
DELETE FROM Emp_tab WHERE Empno = Emp_id;
END;
/
USING UPDATE STATEMENT:
CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS
BEGIN
UPDATE accounts
SET balance = balance + amount
WHERE account_id = acc_no;
END;
Create Functions
A stored function (also called a user function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called.
CREATE FUNCTION get_bal(acc_no IN NUMBER)
RETURN NUMBER
IS acc_bal NUMBER(11,2);
BEGIN
SELECT balance
INTO acc_bal
FROM accounts
WHERE account_id = acc_no;
RETURN(acc_bal);
END;
Create Sequence
A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
CREATE SEQUENCE eseq INCREMENT BY 10;
CREATE SEQUENCE empno start with 1 increment by 1 maxvalue 100 minvalue 1 cycle cache 5;
Select empno.nextval from dual;
Select empno.currval from dual;
Create Schema
To create multiple tables and views and perform multiple grants in a single transaction.
To execute a CREATE SCHEMA statement, Oracle executes each included statement. If all statements execute successfully, Oracle commits the transaction. If any statement results in an error, Oracle rolls back all the statements.
Create Synonym
A synonym is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object,
CREATE SYNONYM market FOR scott.market_research;
CREATE PUBLIC SYNONYM emp FOR scott.emp@sales;
A synonym may have the same name as the base table, provided the base table is contained in another schema.
Create Tablespace
A tablespace is an allocation of space in the database that can contain schema objects. When you create a tablespace, it is initially a read-write tablespace.
This statement creates a tablespace named TABSPACE_2 with one datafile:
A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application's objects to simplify some administrative operations.
CREATE TABLESPACE tabspace_2
DATAFILE 'diska:tabspace_file2.dat' SIZE 20M
DEFAULT STORAGE (INITIAL 10K NEXT 50K MINEXTENTS 1 MAXEXTENTS 999)
Create Cluster
A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Oracle stores together all the rows (from all the tables) that share the same cluster key. They can be created to increase the performance of data retrieval.
Clustered Tables Related data stored together more efficiently
Non Clustered Tables Related data stored differently and taking up more space.
CREATE CLUSTER personnel (department_number NUMBER(2)) SIZE 512 STORAGE (INITIAL 100K NEXT 50K);
Adding Tables to a Cluster
The following statements add the EMP and DEPT tables to the cluster:
CREATE TABLE emp (empno NUMBER PRIMARY KEY, ename VARCHAR2(10) NOT NULL CHECK (ename = UPPER(ename)), job VARCHAR2(9), mgr NUMBER REFERENCES scott.emp(empno), hiredate DATE CHECK (hiredate < TO_DATE ('08-14-1998', 'MM-DD-YYYY')), sal NUMBER(10,2) CHECK (sal > 500), comm NUMBER(9,0) DEFAULT NULL, deptno NUMBER(2) NOT NULL ) CLUSTER personnel (deptno);
CREATE TABLE dept (deptno NUMBER(2),dname VARCHAR2(9),loc VARCHAR2(9)) CLUSTER personnel (deptno);
Create Trigger
A database trigger is
• A stored PL/SQL block associated with a table, a schema, or the database
• An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java
Oracle automatically executes a trigger when specified conditions occur.
Example:
CREATE TABLE departments(ID NUMBER(10) NOT NULL,DESCRIPTION VARCHAR2(50) NOT NULL);
ALTER TABLE departments ADD(CONSTRAINT dept_pk PRIMARY KEY (ID));
CREATE SEQUENCE dept_seq;
CREATE OR REPLACE TRIGGER dept_bir BEFORE INSERT ON departments
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT dept_seq.NEXTVAL INTO :new.id FROM dual;
END;
/
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
IF new.id IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified');
ELSE
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END IF;
END;
/
Create User
CREATE USER sidney IDENTIFIED BY welcome DEFAULT TABLESPACE cases_ts QUOTA 10M ON cases_ts QUOTA 5M ON temp_ts QUOTA 5M ON system PROFILE engineer PASSWORD EXPIRE;
CREATE USER ops$george IDENTIFIED EXTERNALLY DEFAULT TABLESPACE accs_ts TEMPORARY TABLESPACE temp_ts QUOTA UNLIMITED ON accs_ts QUOTA UNLIMITED ON temp_ts;
Create View
To define a view, a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.
CREATE VIEW dept20 AS SELECT ename, sal*12 annual_salary FROM emp WHERE deptno = 20;
CREATE VIEW clerk (id_number, person, department, position) AS SELECT empno, ename, deptno, job FROM emp WHERE job = 'CLERK' WITH CHECK OPTION CONSTRAINT wco;
CREATE VIEW EMP_DTLS AS
SELECT E.EMPNO,E.ENAME,D.DNAME,E.SAL+NVL(COMM,0) "TOTALSAL" FROM EMP E,DEPT D WHERE D.DEPTNO=E.DEPTNO;
Join View Example
CREATE VIEW ed AS SELECT e.empno, e.ename, d.deptno, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno
View created.
SELECT column_name, updatable FROM user_updatable_columns WHERE table_name = 'ED';
Read-Only View Example
CREATE VIEW clerk (id_number, person, department, position) AS SELECT empno, ename, deptno, job FROM emp WHERE job = 'CLERK' WITH READ ONLY;
Create Materialized View
For data warehousing purposes, a materialized view definition can include an aggregation (SUM, COUNT(x), COUNT(*), COUNT(DISTINCT x), AVG, VARIANCE, STDDEV, MIN, and MAX) and any number of joins. Such materialized views can be used in query rewrite, an optimization technique that transforms a user request written in terms of master tables into a semantically equivalent request that includes one or more materialized view. In a data warehousing environment, all detail tables must be local.
CREATE MATERIALIZED VIEW mv1 REFRESH FAST ON COMMIT
AS SELECT t.month, p.prod_name, SUM(f.sales) AS sum_sales
FROM time t, product p, fact f
WHERE f.curDate = t.curDate AND f.item = p.item
GROUP BY t.month, p.prod_name
BUILD IMMEDIATE;
DROP MATERIALIZED VIEW LOG ON parts;
ALTER Commands:
Alter Cluster
The following statement alters the CUSTOMER cluster in the schema SCOTT:
ALTER CLUSTER scott.customer SIZE 512 STORAGE (MAXEXTENTS 25);
ALTER CLUSTER scott.customer DEALLOCATE UNUSED KEEP 30 K;
ADD Constraints:
ALTER TABLE EMP ADD CONSTRAINTS PK_EMPNO PRIMARY KEY(EMPNO);
ALTER TABLE EMP ADD CONSTRAINTS FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);
Alter Index
ALTER INDEX scott.customer INITRANS 5 STORAGE (NEXT 100K);
Alter Procedure
ALTER PROCEDURE henry.close_acct COMPILE;
Alter Sequence
ALTER SEQUENCE eseq MAXVALUE 1500;
Alter Table
Add Column Example
ALTER TABLE emp ADD(thriftplan NUMBER(7,2),loancode CHAR(1) NOT NULL);
Modify Column Examples
ALTER TABLE emp MODIFY (thriftplan NUMBER(9,2));
DISABLE Example
ALTER TABLE customers DISABLE UNIQUE (areaco, phoneno) CASCADE;
DROP PRIMARY KEY Example
ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS;
ALTER TABLE t1 DROP (pk, fk, c1);
Drop Constraint Examples
ALTER TABLE dept DROP PRIMARY KEY CASCADE;
ALTER TABLE dept DROP UNIQUE (dname);
Alter Tablespace
ALTER TABLESPACE accounting BEGIN BACKUP;
ALTER TABLESPACE accounting END BACKUP;
Alter Trigger
ALTER TRIGGER reorder DISABLE;
ALTER TRIGGER reorder ENABLE;
Alter View
ALTER VIEW customer_view COMPILE;
DELETE COMMANDS
DELETE FROM temp_assign;
DELETE FROM emp WHERE JOB = 'SALESMAN'AND COMM < 100; DELETE FROM (select * from emp)WHERE JOB = 'SALESMAN'AND COMM < 100; DELETE FROM emp WHERE job = 'SALESMAN' AND COMM < 100 RETURNING sal INTO:1; Delete Table
DROP TABLE test_data;
RENAME dept TO emp_dept;
CREATE TABLE temporary (newname, col2, col3)AS SELECT oldname, col2, col3 FROM static;
DROP TABLE static;
RENAME temporary TO static;
Drop Columns
Logical Delete
On large tables the process of physically removing a column can be very time and resource consuming. For this reason you may decide to logically delete it:
ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);
Physical Delete
To physically drop a column you can use one of the following syntaxes, depending on whether you wish to drop a single or multiple columns:
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP (column_name1, column_name2);
Delete Cluster
DROP CLUSTER geography INCLUDING TABLES CASCADE CONSTRAINTS;
Delete Function
DROP FUNCTION riddley.new_acct;
Delete Index
DROP INDEX monolith;
Delete Procedure
DROP PROCEDURE kerner.transfer
Delete Tablespace
DROP TABLESPACE TableSpaceName INCLUDING CONTENTS CASCADE CONSTRAINTS;
Delete Trigger
DROP TRIGGER ruth.reorder;
Delete User
DROP USER bradley CASCADE;
Delete View
DROP VIEW view_data;
SELECT AND SUBQUERIES
SELECT * FROM EMP;
SELECT ‘SELECT * FROM ‘||TABLE_NAME||’;’ FROM USER_TABLES;
NULL Function
SELECT EMPNO, ENAME,SAL,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,ENAME,SAL,COMM FROM EMP WHERE COMM IS NOT NULL;
SELECT SAL+NVL(COMM,0) FROM EMP;
GROUP BY Examples
SELECT * FROM emp WHERE deptno = 30;
SELECT ename, job, sal, deptno FROM emp WHERE NOT (job = 'SALESMAN' AND deptno = 30);
SELECT a.deptno "Department",a.num_emp/b.total_count "%Employees", a.sal_sum/b.total_sal "%Salary" FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum FROM scott.emp GROUP BY deptno) a,(SELECT COUNT(*) total_count, SUM(sal) total_sal FROM scott.emp) b ;
SELECT COUNT(*) * 100 FROM emp SAMPLE BLOCK (1);
SELECT deptno, MIN(sal), MAX (sal) FROM emp GROUP BY deptno;
SELECT deptno, MIN(sal), MAX (sal)FROM emp WHERE job = 'CLERK' GROUP BY deptno;
SELECT EMP_NO,COUNT(EMP_NO) FROM EMP_ATTENDANCE_DTLS WHERE
SUBSTR(TO_CHAR(ENTRY_DT,'DD/MM/YY'),4,5)='03/05' GROUP BY EMP_NO
Display the Top or Bottom level rows
SELECT LEVEL, MAX(SAL) FROM EMP WHERE LEVEL= 1 OR LEVEL=2 CONNECT BY PRIOR SAL > SAL GROUP BY LEVEL
SELECT LEVEL, MIN(SAL) FROM EMP WHERE LEVEL=2 OR LEVEL =3 CONNECT BY PRIOR SAL < SAL GROUP BY LEVEL SELECT EMPNO,ENAME,SAL FROM EMP A WHERE 3>=(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE B.SAL>=A.SAL) ORDER BY SAL DESC;
SELECT EMPNO,ENAME,SAL FROM EMP A WHERE 3>=(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE A.SAL>=B.SAL) ORDER BY SAL DESC;
SELECT EMPNO,ENAME,SAL FROM (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM<=3; SELECT EMPNO,ENAME,SAL FROM (SELECT * FROM EMP ORDER BY SAL) WHERE ROWNUM<=3; Having Examples
SELECT deptno, MIN(sal), MAX(sal)FROM emp WHERE job = 'CLERK' GROUP BY deptno HAVING MIN(sal) < 1000; ORDER BY Examples
SELECT * FROM emp WHERE job = 'SALESMAN' ORDER BY comm DESC;
SELECT ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;
SELECT ename, deptno, sal FROM emp ORDER BY 2 ASC, 3 DESC;
FOR UPDATE Examples
SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE;
SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE OF emp.sal;
Table Collection Examples
CREATE TYPE ProjectType AS OBJECT(pno NUMBER, pname CHAR(31),budget NUMBER);
CREATE TYPE ProjectSet AS TABLE OF ProjectType;
Sub query Examples
SELECT ename, deptno FROM emp WHERE deptno =(SELECT deptno FROM emp WHERE ename = 'TAYLOR');
UPDATE emp SET sal = sal * 1.1 WHERE empno NOT IN (SELECT empno FROM bonus);
CREATE TABLE newdept (deptno, dname, loc) AS SELECT deptno, dname, loc FROM dept;
SELECT * FROM EMP_DTLS WHERE TOTALSAL= (SELECT MAX(TOTALSAL) FROM EMP_DTLS);
SELECT * FROM EMP_DTLS WHERE TOTALSAL >(SELECT TOTALSAL FROM EMP_DTLS WHERE EMPNO = 7369);
SELECT * FROM EMP_DTLS WHERE TOTALSAL <(SELECT TOTALSAL FROM EMP_DTLS WHERE EMPNO=7839) ORDER BY TOTALSAL; Equijoin Examples
This equijoin returns the name and job of each employee and the number and name of the department in which the employee works:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno;
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND job = 'CLERK';
Ex:
SELECT L.EMP_LEAVE_ID,L.EMP_NO,L.LEAVE_REASON,M.NAME,M.SURNAME FROM EMP_LEAVE_DTLS L,MST_EMPLOYEES M WHERE L.EMP_NO=M.EMP_NO AND L.EMP_NO=1421;
Self Join Example
The following query uses a self join to return the name of each employee along with the name of the employee's manager:
SELECT e1.ename||' works for '||e2.ename "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;
Outer Join Examples
This query uses an outer join to extend the results of the Equijoin example above:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno;
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK';
SELECT orderno, custno, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE" FROM orders;
The following outer join returns all customers and the dates they placed orders. The (+) operator ensures that customers who placed no orders are also returned:
SELECT custname, TO_CHAR (orderdate, 'MON-DD-YYYY') "ORDERDATE" FROM customers, orders WHERE customers.custno = orders.custno (+)
Inner Join
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE
E.DEPTNO=D.DEPTNO
In Oracle 9i
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E inner join DEPT D on
E.DEPTNO=D.DEPTNO
Left Outer Join
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE
E.DEPTNO(+)=D.DEPTNO
In Oracle 9i
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E left outer join DEPT D on
E.DEPTNO)=D.DEPTNO
Right Outer Join
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE
E.DEPTNO=D.DEPTNO(+)
In Oracle 9i
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E right outer join DEPT D on
E.DEPTNO)=D.DEPTNO
Full Outer Join
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE
E.DEPTNO(+)=D.DEPTNO
UNION
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE
E.DEPTNO=D.DEPTNO(+);
In Oracle 9i
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E full outer join DEPT D on
E.DEPTNO)=D.DEPTNO
DISTINCT Example:
SELECT EMPNO,ENAME "Employee Name",SAL,JOB FROM EMP;
SELECT ENAME||JOB AS "EMP INFO" FROM EMP;
SELECT DISTINCT DEPTNO,JOB FROM EMP;
SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL BETWEEN 2000 AND 3000;
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO IN(10,20);
SELECT EMPNO,ENAME,SAL FROM EMP WHERE ENAME LIKE'S%';
SELECT EMPNO,ENAME,SAL FROM EMP WHERE ENAME LIKE'%T';
SELECT EMPNO,ENAME,SAL FROM EMP WHERE JOB NOT IN('MANAGER','PRESIDENT');
SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL;
CHARACTER FUNCTIONS:
SELECT SUBSTR('SQL COMMANDS',1,3) FROM DUAL;
SELECT LOWER('ORACLE') FROM DUAL;
SELECT LENGTH('ORACLE') FORM DUAL;
SELECT INITCAP('ORACLE') FROM DUAL;
SELECT CONCAT(ENAME,JOB) FROM EMP;
SELECT LPAD(SAL,11,'*') FROM EMP; SELECT RPAD(SAL,11,'*') FROM EMP;
NUMBER FUNCTIONS:
SELECT ROUND(2356.2345,2),ROUND(2541.012,0),ROUND(3698.235,-1) FROM DUAL;
SELECT TRUNC(2356.2345,2),TRUNC(2541.012,0),TRUNC(3698.235,-1) FROM DUAL;
SELECT EMPNO,ENAME,SAL,MOD(SAL,2000) FROM EMP WHERE EMPNO=7566;
DATE FUNCTIONS:
SELECT SYSDATE FROM DUAL;
SELECT HIREDATE,HIREDATE+7,HIREDATE-7,SYSDATE-HIREDATE FROM EMP WHERE HIREDATE LIKE'%JUN%';
SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP WHERE EMPNO=7566;
SELECT HIREDATE,ADD_MONTHS(HIREDATE,3),ADD_MONTHS(HIREDATE,-3) FROM EMP WHERE DEPTNO=20;
SELECT HIREDATE,NEXT_DAY(HIREDATE,'FRIDAY'),NEXT_DAY(HIREDATE,6) FROM EMP WHERE DEPTNO=10;
SELECT SYSDATE,NEXT_DAY(SYSDATE,'FRIDAY'),NEXT_DAY(SYSDATE,2) FROM DUAL;
CANVERTION FUNCTIONS:
SELECT TO_CHAR(SYSDATE,'DAY,DDTH MONTH YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SAL,'$9,0000') FROM EMP;
SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>TO_NUMBER('1500');
SELECT * FROM EMP WHERE SUBSTR(HIREDATE,4,3)='JAN';
SELECT * FROM EMP WHERE SUBSTR(TO_CHAR(HIREDATE,'DD/MM/YY'),4,5)='01/82';
SELECT * FROM EMP WHERE SUBSTR(TO_CHAR(HIREDATE,'DD/MM/YYYY'),4,2)='01';
GENERAL FUNCTIONS:
SELECT USER FROM DUAL;
SELECT VSIZE('HELLO') FROM DUAL;
SELECT ENAME,JOB,SAL,DECODE(JOB,'CLERK',1.10*SAL,'MANAGER',2*SAL)"RECEIVED SALARY" FROM EMP;
SELECT DECODE(COMM,NULL,'NOT AVAILABLE',COMM) FROM EMP;
SELECT HIREDATE,DECODE(SUBSTR(HIREDATE,4,3),'DEC','EMPLOYEE JOINED IN DECEMBER','FEB','EMPLOYEE JOINED IN FEBRUARY',
HIREDATE) FROM EMP
SET OPARATORS:
UNION:
SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=10
UNION
SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=30;
UNION ALL:
SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=10
UNION ALL
SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=30;
INTERSECT:
SELECT JOB FROM EMP WHERE DEPTNO=10
INTERSECT
SELECT JOB FROM EMP WHERE DEPTNO=30;
MINUS:
SELECT JOB FROM EMP WHERE DEPTNO=10
MINUS
SELECT JOB FROM EMP WHERE DEPTNO=30;
TRUNCATE
TRUNCATE TABLE emp PRESERVE SNAPSHOT LOG;
TRUNCATE TABLE stock;
UPDATE
UPDATE emp SET comm = NULL WHERE job = 'TRAINEE';
UPDATE emp SET job = 'MANAGER', sal = sal + 1000, deptno = 20 WHERE ename = 'JONES';
INSERT
INSERT INTO dept VALUES (50, 'PRODUCTION', 'SAN FRANCISCO');
INSERT INTO emp (empno, ename, job, sal, comm, deptno)VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);
INSERT INTO (SELECT empno, ename, job, sal, comm, deptno FROM emp) VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);
Sub Query Example
INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal OR job IN ('PRESIDENT', 'MANAGER');
INSERT INTO scott.accounts@sales (acc_no, acc_name) VALUES (5001, 'BOWER');
Sequence Example
INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLERK', 7902, SYSDATE, 1200, NULL, 20);
GRANT PRIVILEGES AND ROLES
GRANT CREATE SESSION TO richard;
GRANT CREATE TABLE TO travel_agent;
GRANT READ ON DIRECTORY bfile_dir1 TO scott WITH GRANT OPTION;
GRANT ALL ON bonus TO jones WITH GRANT OPTION;
GRANT SELECT, UPDATE ON golf_handicap TO PUBLIC;
GRANT REFERENCES (empno), UPDATE (empno, sal, comm)ON scott.emp TO blake;
REVOKE PRIVILEGES AND ROLES
REVOKE DROP ANY TABLE FROM bill, mary;
REVOKE CREATE TABLESPACE FROM controller;
create TABLE : This is a DDL command in SQL that creates a new table in a database.
create TABLE table-name (column-name data-type [size] NOT NULL/DEFAULT default-value] CHECK (column-name 0) UNIQUE (column-name) PRIMARY KEY (column-name) FOREIGN KEY (column-name) REFERENCES table-name)
ALTER TABLE : This is a DDL command in SQL that modifies the structure of an existing table.
Syntax : ALTER TABLE table-name ADD (column-name data-type [size] [NOT NULL DEFAULT]...)
primary key definition / foreign key definition
DROP PRIMARY KEY / DROP FOREIGN KEY)
DROP TABLE : This is DDL command in SQL that deletes the an existing table. Once you delete a table, all data contained in it is lost and cannot be recovered. The storage space used by this table is also released.
Syntax : DROP TABLE table-name
Interacting with a Database :
SELECT : This is a DML command in SQL that retrieves data from the database in the form of query results. The command supports the following keywords and clauses :
FROM This keyword specifies the name of the table.
* This keyword selects all the columns of the table.
WHERE This keyword gives the search condition that specifies the data to be retrieved.
AND This operator is used to combine two or more search conditions.
ORDER BY This keyword sorts the query result on one or more columns.
GROUP BY This keyword groups the query result and lets you generate summary result for each group.
NULL values This value indicates that the data is not present.
Subquery This is the query that is place inside the main query. It passes its query result to the main query.
INSERT : This is a DML command in SQL that you use to add data in rows of a table.
SYNTAX : INSERT INTO table-name (column-names) VALUES (constant/NULL)
UPDATE : This is a DML command in SQL that you use to change data on rows of a table.
Syntax : UPDATE table-name SET column-name-value WHERE condition
DELETE : This is a DML command in SQL that removes one or more rows of data from a table.
Syntax : DELETE FROM table-name WHERE condition.
End-user's View of a Database :
Views : Views are relations that are derived from one or more source tables. Views have the following features:
Views let you restrict the access to data so that end-users see data relevant to them.
Views do not physically exist in the database and only their definition is stored by an RDBMS.
An RDBMS accesses the source tables for data to be retrieved from a view.
Any changes that users make to views do not reflect in the source tables if the view has been created using a Join condition.
Views created WITH CHECK OPTION allows for an added measure of security in a view. For example, the user will not be able to insert or update a row that could not be selected by the view-with check option prevents this from happening.
create VIEW : A view can be created using the create VIEW command.
Syntax : create VIEW view-name (column-names) AS query.
Retrieving Data from a View : Once you create a view, you can retrieve data from it using the SELECT command, just as you do for a table.
Restricting Access to a Database :
GRANT : This is a DCL command in SQL that you use to grant a specific set of authorities to one or more users.
Syntax : GRANT (SQL command) (column-names) ON table-name to user-name.
REVOKE : This is a DCL command in SQL that you use to take away a specific set of authorities from one or more users.
Syntax : REVOKE (SQL command) ON table-name to user-name
Introduction Materialized views
Materialized views are stored summaries of queries containing precomputed results. Materialized views greatly improve data warehouse query processing.The existence of a materialized view is transparent to SQL applications, so a DBA can create or drop materialized views at any time without affecting the validity of SQL applications.
Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution time and storing these results in the database. The query optimizer can make use of materialized views by automatically recognizing when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views. Rewriting queries to use materialized views rather than detail relations results in a significant performance gain.
Materialized views can be refreshed automatically whenever the data is changed in the underlying tables. The refresh method can be incremental (fast refresh) or complete. Incremental method re-populates only the changed data. Complete method truncates and rebuilds the view. The refresh process can be enabled by adding the REFRESH clause while creating the materialized view. You can suppress the refresh process for the entire life of the view.
Example 1 :
The following statement creates and populates a materialized view SALES_BY_MONTH_BY_STATE. The materialized view will be populated with data as soon as the statement executes successfully, and subsequent refreshes will be accomplished by reexecuting the materialized view's query.
CREATE MATERIALIZED VIEW sales_by_month_by_state
TABLESPACE my_ts
PARALLEL (10)
ENABLE QUERY REWRITE
BUILD IMMEDIATE
REFRESH COMPLETE AS
SELECT t.month, g.state, SUM(sales) AS sum_sales
FROM fact f, time t, geog g
WHERE f.cur_date = t.cur_date
AND f.city_id = g.city_id
GROUP BY month, state;
The fact table stores the actual data about the sales by month and state, time table has the day,month, year dimenstions and geog stores the state names. This view is built and populated with data immediately.
Lets asssume that you asked oracle to calculate the same values in a query and if query_rewrite is allowed on your server, the optimizer will automatically use the values in the above precomputed view.
NOTES:
1. Set QUERY_REWRITE_ENABLE=TRUE in init.ora
2. Set JOB_QUEUE_PROCESSES=1 ( 1 or more based on your requirements).
RESTRICTIONS:
1. Materialized views consume storage space. Be aware to provide storage parameters according to the data it holds.
2. There cannot be any set functions like UNION, MINUS in the underlying query for materialized views.
Syntax for Drop the users:
If user BRADLEY's schema contains no objects, you can drop BRADLEY by issuing the statement:
DROP USER bradley;
If BRADLEY's schema contains objects, you must use the CASCADE clause to drop BRADLEY and the objects:
DROP USER bradley CASCADE;
Clustered and Non-clustered Tables
PL/SQL
It Procedural Language, we can execute a block of statements at a time.
Structure of PL/SQL Program:
It contains Three Sections
DECLAR
Declaration of Variable, constraints and cursors;
BEGIN
Execution of SQL and PL/SQL Statements;
EXCEPTION
Exception Handlers;
END;
/
There are two types of PL/SQL blocs.
1. Anonymous Block: According to Oracle server it any PL/SQL block is not having any specific name is called “Anonymous Block”.
2. Name Block: According to Oracle server it any PL/SQL block is having specific name is called “Name Block”.
Datatypes: What ever datatypes SQL supports, the same datatypes PL/SQL support. And addition it support BOOLEN datatype.
CHAR it is fixed length the range is 32,767.
VARCHAR2 the range is 32,767.
CREATING A FILE: SQL> ED
EXECUT THE FILE : SQL> @
DISPLAY THE OUTPUT: SET SERVEROUTPUT ON;
Program for Adding Two Numbers:
DECLARE
A NUMBER :=&A;
B NUMBER :=&B;
C NUMBER;
BEGIN
C:=A+B;
DBMS.OUTPUT.PUT_LINE(‘SUM=’||C);
END;
/
DBMS.OUTPUT: It is a predefine Package is used to display the output on the screen.
PUT_LINE: It is a procedure defined inside of DBMS.OUTPUT package.
Finding the Length of String?
DECLARE
A NUMBER:=&A;
B NUMBER;
BEGIN
B:=LENGTH(A);
DBMS.OUTPUT.PUT_LINE(‘Length of’|| A ||‘is’|| B);
END;
/
Find the First and Last dist of String
DECLARE
A VARCHAR2(32767):='&A';
F CHAR;
L CHAR;
BEGIN
F:=SUBSTR(A,1,1);
L:=SUBSTR(A,-1);
DBMS_OUTPUT.PUT_LINE('First Char is'||F);
DBMS_OUTPUT.PUT_LINE('Last Char is'||L);
END;
/
Flow of PL/SQL Program:
1. SEQUENCIAL.
2. CONDITIONAL/SELECTIVE.
3. LOOPING/ ITERATION.
CONTROL STATEMENTS:
IF STATEMENT:
Syntax: IF CONDITION1 THEN
Statement to Execute;
ELSE
Statement to Execute;
END IF;
IF CONDITION1 THEN
Statement to Execute;
ELSIF CONDITION2 THEN
Statement to Execute;
ELSIF CONDITION3 THEN
Statement to Execute;
ELSE
Statement to Execute;
END IF;
Example:
DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
C NUMBER:=&C;
D NUMBER;
BEGIN
IF A=1 THEN
D:=B+C;
ELSIF A=2 THEN
D:=B-C;
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID OPTION');
END IF;
DBMS_OUTPUT.PUT_LINE('SUM '||D);
END;
/
Loops: If we want to execute the same statement number of time the we will use loops.
There are three types of loops.
1. Simple Loop: In simple loop if the condition is false then execute the given statement.
Syntax:
LOOP
EXIT WHEN CONDITION;
Statement to Execute;
END LOOP;
Example:
DECLARE
I NUMBER:=1;
BEGIN
LOOP
EXIT WHEN I>5;
DBMS_OUTPUT.PUT(' '||I);
I:=I+1;
END LOOP;
DBMS_OUTPUT.NEW_LINE();
END;
/
2. While Loop: If the condition is true then it will execute the given statement.
Syntax:
WHILE CONDITION LOOP
Statement to Execute;
END LOOP;
Example:
DECLARE
I NUMBER :=&I;
BEGIN
WHILE I<=5 LOOP DBMS_OUTPUT.PUT(' '||I); I:=I+1; END LOOP; DBMS_OUTPUT.NEW_LINE(); END; / 3. For Loop: Variable using For Loop is not require to DECLARE, No need of Increment and Variable using by the For Loop is Not editable.
Syntax:
FOR Variable in EXPRESSION1..EXPRESSION2 LOOP
Statement to Execute;
END LOOP;
Example:
BEGIN
FOR I IN 1..5 LOOP
DBMS_OUTPUT.PUT(' '||I);
END LOOP;
DBMS_OUTPUT.NEW_LINE();
END;
/
Print the given String in Reveres Order.
DECLARE
A VARCHAR2(32767):='&A';
--B VARCHAR2(32767)
C CHAR;
BEGIN
FOR I IN 1..LENGTH(A) LOOP
C:=SUBSTR(A,-I,1);
DBMS_OUTPUT.PUT(C);
END LOOP;
DBMS_OUTPUT.NEW_LINE();
END;
/
Find the given String is Palindrome or Not.
DECLARE
A VARCHAR2(32767):='&A';
B VARCHAR2(32767);
C CHAR;
BEGIN
FOR I IN 1..LENGTH(A) LOOP
C:=SUBSTR(A,-I,1);
B:=B||C;
END LOOP;
IF A=B THEN
DBMS_OUTPUT.PUT_LINE(A||' IS PALINDROME');
ELSE
DBMS_OUTPUT.PUT_LINE(A||' IS NOT PALINDROME');
END IF;
END;
/
Find the given Number is Amstrong Number or not.
Amstrong No: It sum of cube of all the digits=Amstrong No
Ex: 13+53+33=153.
DECLARE
A NUMBER :=&A;
C NUMBER:=0;
BEGIN
FOR I IN 1..LENGTH(A) LOOP
C:=C+POWER(SUBSTR(A,I,1),3);
END LOOP;
IF A=C THEN
DBMS_OUTPUT.PUT_LINE(A ||' IS AMSTRONG NUMBER');
ELSE
DBMS_OUTPUT.PUT_LINE(A ||' IS NOT AMSTRONG NUMBER');
END IF;
END;
/
Accessing data from the Tables using PL/SQL:
DECLARE
ENO NUMBER:=&ENO;
N VARCHAR2(32767);
S NUMBER;
J VARCHAR2(32767);
BEGIN
SELECT ENAME,SAL,JOB INTO N,S,J FROM EMP WHERE EMP.EMPNO=ENO;
DBMS_OUTPUT.PUT_LINE('Employee Name is '||N);
DBMS_OUTPUT.PUT_LINE('Employee Salary is '||S);
DBMS_OUTPUT.PUT_LINE('Employee job is '||J);
END;
/
Exception Handle:
In PL/SQL we have two types of errors one is ‘Syntax Errors’ which are encountered during the Compilation. Second one is ‘Runtime Errors’ which are encountered during the Runtime.
If the PL/SQL block is not compiled successfully, whatever transactions we done in the block, that will Rollback automatically.
Syntax:
Exception
When No_Data_Found Then
DBMS_OUTPUT.PUT_LINE(‘Record Not Existed..’);
Exception is used to handle runtime errors in PL/SQL program. It is two types
1. Predefine Exception
a. Define
b. Un define
2. User Define Exception.
Define Exception: It is the exception which is having Error Number with a specific Name.
Un Define Exception: It is the exception which is having Error Number without having any name.
Predefine Exception are
1. NO_DATA_FOUND
2. TOO_MANY_ROWS
3. DUP_VAL_ON_INDEX
4. VALUE_ERROR
5. INVALID_NUMBER
6. ZERO_DEVIDE
7. CURSOR_ALREADY_OPEN
8. INVALID_CURSOR
9. PROGRAM_ERROR
10. STORAGE_ERROR
11. LOGIN_DEFINE.
Example:
DECLARE
A NUMBER:=&A;
N EMP.ENAME%TYPE;
J EMP.JOB%TYPE;
S NUMBER;
INC NUMBER;
EXCESS_SALARY EXCEPTION;
BEGIN
SELECT ENAME,JOB,SAL INTO N,J,S FROM EMP WHERE EMPNO=A;
DBMS_OUTPUT.PUT_LINE('Employee Old Salary='||S);
IF S>=800 OR S<=1500 THEN INC:=500; ELSIF S>1500 OR S<=3000 THEN INC:=1000; ELSE INC:=1000; END IF; UPDATE EMP SET SAL=SAL+INC WHERE EMPNO=A; DBMS_OUTPUT.PUT_LINE('Employee Number='||A); DBMS_OUTPUT.PUT_LINE('Employee Name='||N); DBMS_OUTPUT.PUT_LINE('Employee Job='||J); DBMS_OUTPUT.PUT_LINE('Employee Incremented Salary='||S); IF S+INC>5500 THEN
RAISE EXCESS_SALARY;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Number Not Exist');
WHEN EXCESS_SALARY THEN
UPDATE EMP SET SAL=5500 WHERE EMPNO=A;
DBMS_OUTPUT.PUT_LINE('EXCESS SALARY');
END;
/
CURSOR: It is a work area that can store the executed SQL Statement result data. It is used to handle the multiple records in PL/SQL program. It is two types
1. Implicit Cursor: System can open a cursor for internal processes such type of cursors are called ‘Implicit cursors’.
2. Explicit Cursor: Users can open a cursor for accessing the data, that type of user define cursors are called ‘Explicit Cursors’.
Handle the Cursors: There are three ways to handle the cursors.
1. Open the cursor using ‘open
2. Fetch data from cursor using ‘fetch
3. Close the cursor using ‘close
Attributes of Cursor:
1. %ISOPEN: It returns true if the cursor is open else it returns false
2. %FOUND: It returns true if the record fetch from the workspace else it returns false.
3. %NOTFOUND: It returns true if the record not fetch from the workspace else it returns true.
4. %ROWCOUNT: It returns the count of rows.
Example:
DECLARE
CURSOR C IS SELECT * FROM EMP;
E C%ROWTYPE;
TOT NUMBER:=0;
S NUMBER;
BEGIN
OPEN C;
DBMS_OUTPUT.PUT_LINE(RPAD('EMPNO',15,'-')||RPAD('NAME',20,'-')||RPAD('JOB',15,'-')||'SALARY');
DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));
LOOP
FETCH C INTO E;
EXIT WHEN C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(RPAD(E.EMPNO,15,'-')||RPAD(E.ENAME,20,'-')||RPAD(E.JOB,15,'-')||E.SAL);
S:=E.SAL;
--DBMS_OUTPUT.PUT_LINE(S);
TOT:=TOT+S;
END LOOP;
DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));
DBMS_OUTPUT.PUT_LINE('Total Number of Rows='||C%rowcount);
DBMS_OUTPUT.PUT_LINE('Total Salary='||TOT);
DBMS_OUTPUT.PUT_LINE('Average Salary='||ROUND(TOT/C%ROWCOUNT,2));
CLOSE C;
END;
/
Using For Loop:
DECLARE
CURSOR C IS SELECT * FROM EMP;
E C%ROWTYPE;
TOT NUMBER:=0;
CTR NUMBER:=0;
BEGIN
DBMS_OUTPUT.PUT_LINE(RPAD('EMPNO',15,'-')||RPAD('NAME',20,'-')||RPAD('JOB',15,'-')||'SALARY');
DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));
FOR E IN C LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(E.EMPNO,15,'-')||RPAD(E.ENAME,20,'-')||RPAD(E.JOB,15,'-')||E.SAL);
TOT:=TOT+NVL(E.SAL,0);
CTR:=C%ROWCOUNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));
DBMS_OUTPUT.PUT_LINE('Total Number of Rows='||CTR);
DBMS_OUTPUT.PUT_LINE('Total Salary='||TOT);
DBMS_OUTPUT.PUT_LINE('Average Salary='||ROUND(TOT/CTR,2));
END;
/
PARAMETRIC CURSOR:
DECLARE
A NUMBER:=&DEPTNO;
CURSOR C(DNO NUMBER) IS SELECT * FROM EMP WHERE DEPTNO=DNO;
E C%ROWTYPE;
TOT NUMBER:=0;
CTR NUMBER;
BEGIN
OPEN C(A);
DBMS_OUTPUT.PUT_LINE(RPAD('EMPNO',15,' ')||RPAD('NAME',20,' ')||RPAD('JOB',15,' ')||'SALARY');
DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));
IF C%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20000,'Record Not Found');
END IF;
CLOSE C;
FOR E IN C(A) LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(E.EMPNO,15,' ')||RPAD(E.ENAME,20,' ')||RPAD(E.JOB,15,' ')||E.SAL);
TOT:=TOT+NVL(E.SAL,0);
CTR:=C%ROWCOUNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE(LPAD('_',60,'_'));
DBMS_OUTPUT.PUT_LINE('Number of Rows='||CTR);
DBMS_OUTPUT.PUT_LINE('Totla Salary='||TOT);
DBMS_OUTPUT.PUT_LINE('Average Salary='||ROUND(TOT/CTR,2));
END;
/
PROCEDURES & FUNCTIOS:
Procedure: A procedure is a group of PL/SQL statements that you can call by name. It may or may not return a value.
Syntax:
CREATE OR REPLACE PROCEDURE
LOCAL DECLARATION;
BEGIN
EXECUTED STATEMENTS;
EXCEPTION
EXCEPTION HANDLER;
END;
/
Execution of Procedure: EXEC
Procedure divided into 2 parts
1. Procedure Specifications: Before IS/AS is called procedure specification.
2. Procedure Body: After IS/AS is call body.
Once the procedure is created successfully it will crate object code that will store in the server. This object code is called Pseudo Code.
There are three types of Parameters in Procedure. IN, OUT, INOUT.
IN parameter is readable but not writable. It’s constant.
OUT parameter is writable not readable.
INOUT parameter is both readable and writable.
CREATE OR REPLACE PROCEDURE EMP_DTLS(ENO IN NUMBER,NAME OUT VARCHAR2,SALARY OUT NUMBER,D_NAME OUT VARCHAR2) IS
BEGIN
SELECT E.ENAME,E.SAL,D.DNAME INTO NAME,SALARY,D_NAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND EMPNO=ENO;
DBMS_OUTPUT.PUT_LINE(RPAD('NAME',15,' ')||RPAD('SALARY',15,' ')||'Department');
DBMS_OUTPUT.PUT_LINE(LPAD('-',50,'-'));
DBMS_OUTPUT.PUT_LINE(RPAD(NAME,15,' ')||RPAD(SALARY,15,' ')||D_NAME);
END;
/
DECLAR VARIABLES;
SQL> VAR N VARCHAR2(30);
SQL> VAR S NUMBER;
SQL> VAR D VARCHAR2(30);
SQL> EXEC EMP_DTLS(7369,:N,:S,:D)
Functions: It should return a single value the advantage of function is used to Reusability, Readable:-Decreasing the coding.
Modularity: - Dividing the program into number of modules
Easy to Maintain.
Syntax: CREATE OR REPLACE FUNCTION
DECLARATION STATEMENTS;
BEGIN
EXECTION STATEMENTS;
EXCEPTION
EXCEPTION HANDLERS;
END;
/
EXAMPLES:
CREATE OR REPLACE FUNCTION PRIM(A NUMBER) RETURN VARCHAR2 IS
BEGIN
FOR I IN 2..A-1 LOOP
IF MOD(A,I)=0 THEN
RETURN 'IT IS NOT PRIM NUMBER';
EXIT;
END IF;
END LOOP;
RETURN 'IT IS PRIM NUMBER';
END;
/
EXEC: SELECT PRIM(23) FROM DUAL;
Finding the Employee Experience in EMP TABLE:
CREATE OR REPLACE FUNCTION EXPE(A NUMBER) RETURN NUMBER IS
X NUMBER;
BEGIN
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12,0) INTO X FROM EMP WHERE EMPNO=A;
RETURN X;
END;
/
EXEC: SELECT EXPE(EMPNO) FROM EMP;
SELECT * FROM EMP WHERE EXPE(EMPNO)=23;
Finding the Month from the Given Date:
CREATE OR REPLACE FUNCTION MTH(A DATE) RETURN NUMBER IS
BEGIN
RETURN TO_CHAR(A,'MM');
END;
/
EXEC: SELECT MTH(SYSDATE) FROM DUAL;
SELECT MTH(HIREDATE) FROM DUAL;
SELECT * FROM EMP WHERE MTH(HIREDATE)=3;
