basic sql statement

7/31/2019 Basic SQL statement

1/42

TABLE OF CONTENTS

1. BASIC SQL STATEMENT..................................................................................................................4Demo Tables (EMP & DEPT)...........................................................................................................4Basic SQL statement.........................................................................................................................4

Simple rules of SQL statements.........................................................................................................4Eliminating Duplicate Rows.............................................................................................................5SQL versus SQL*Plus........................................................................................................................5

2. WHERE, ORDERBY CLAUSES...........................................................................................................5IS NULL Operator.............................................................................................................................5BETWEEN Operator.........................................................................................................................6IN Operator.......................................................................................................................................6LIKE Operator...................................................................................................................................6NOT Operator....................................................................................................................................6SORT by Multiple Columns...............................................................................................................7

3. SQL FUNCTIONS..............................................................................................................................7Single Row Functions........................................................................................................................7

Date Functions..................................................................................................................................8TO_CHAR Function with Dates........................................................................................................8TO_CHAR Function with Numbers...................................................................................................9

NVL Function..................................................................................................................................10DECODE Function.........................................................................................................................11Nesting Functions............................................................................................................................11

4. JOIN TABLES..................................................................................................................................11Equijoins..........................................................................................................................................11Non-Equijoins..................................................................................................................................12Outer Joins.......................................................................................................................................12Self Joins..........................................................................................................................................12Using Table Aliases.........................................................................................................................13

5. GROUP FUNCTIONS.........................................................................................................................13Types of Group Functions...............................................................................................................13GROUP BY Clause..........................................................................................................................13

Illegal Queries Using Group Functions..........................................................................................14HAVING Clause..............................................................................................................................14

6. SUBQUERIES...................................................................................................................................15Guidelines for Using Subqueries.....................................................................................................15Single-Row Comparison Operators................................................................................................15

Multiple-Row Comparison Operators.............................................................................................16

7. SQL*PLUS...................................................................................................................................17Substitution Variable.......................................................................................................................17

ACCEPT Command.........................................................................................................................17Customizing the SQL*Plus Environment........................................................................................17

8. DATA MANIPULATION LANGUAGE (DML)....................................................................................18INSERT Statement...........................................................................................................................18UPDATE Statement.........................................................................................................................18

DELETE Statement..........................................................................................................................19Transactions....................................................................................................................................19

Before COMMIT or ROLLBACK....................................................................................................19After COMMIT................................................................................................................................20After ROLLBACK............................................................................................................................20SAVEPOINT....................................................................................................................................20

9. MANAGING TABLES.......................................................................................................................20


2/42

Naming Rules Of Database Tables And Columns..........................................................................20CREATE TABLE Statement.............................................................................................................20

Datatypes.........................................................................................................................................21Create Table by Subquery...............................................................................................................21

Add Column.....................................................................................................................................22

Modify Column................................................................................................................................22Drop Table.......................................................................................................................................22Rename Table..................................................................................................................................22Truncate Table.................................................................................................................................23

10. CONSTRAINTS...............................................................................................................................23NOT NULL Constraint....................................................................................................................23UNIQUE Constraint........................................................................................................................23

PRIMARY KEY Constraint..............................................................................................................23FOREIGN KEY Constraint.............................................................................................................24CHECK Constraint..........................................................................................................................24

Add Constraint.................................................................................................................................24Drop Constraint...............................................................................................................................24Disable and Enable Constraints.....................................................................................................24

Viewing Constraints by USER_CONSTRAINTS.............................................................................2511. VIEWS......................................................................................................................................25Advantages of Views........................................................................................................................25Simple Views and Complex Views..................................................................................................25WITH CHECK OPTION & WITH READ ONLY............................................................................26

12. SEQUENCE....................................................................................................................................2713. INDEX......................................................................................................................................27

Guidelines of Creating an Index.....................................................................................................28

14. SYNONYMS...................................................................................................................................2815. USERACCESS..............................................................................................................................28

System Privileges.............................................................................................................................28Object Privileges.............................................................................................................................29

16. PL/SQL FUNDAMENTAL.............................................................................................................30

What is PL/SQL...............................................................................................................................30Benefits of PL/SQL..........................................................................................................................30

17. DECLARING VARIABLESIN PL/SQL...........................................................................................30The %TYPE Attribute......................................................................................................................31

18. EXECUTABLE SECTIONIN PL/SQL.............................................................................................31SELECT Statements in PL/SQL.......................................................................................................31

DML Statements in PL/SQL............................................................................................................31SQL Cursor......................................................................................................................................32

19. CONDITIONAL TESTINGAND LOOPINGIN PL/SQL......................................................................32IF Statements...................................................................................................................................32Basic Loop.......................................................................................................................................33FOR Loop........................................................................................................................................33WHILE Loop....................................................................................................................................34

20. COMPOSITE DATATYPES...............................................................................................................34PL/SQL Table..................................................................................................................................34PL/SQL Records..............................................................................................................................35The %ROWTYPE Attribute.............................................................................................................35

21. EXPLICIT CURSORS......................................................................................................................36Cursor FOR Loops..........................................................................................................................36Cursors with Parameters.................................................................................................................37The SELECT ..... FOR UPDATE Clause........................................................................................37The WHERE CURRENT OF Clause...............................................................................................38


3/42


4/42

1. Basic SQL Statement

Demo Tables (EMP & DEPT)

All the SQL statements in this tutorial notes will base on the following two tables

EMP TABLE (14 records)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-Dec-80 1000 20

7499 ALLEN SALESMAN 7698 20-Feb-81 1800 300 30

7521 WARD SALESMAN 7698 22-Feb-81 1450 500 30

7566 JONES MANAGER 7839 02-Apr-81 3175 20

7654 MARTIN SALESMAN 7698 28-Sep-81 1450 1400 30

7698 BLAKE MANAGER 7839 01-May-81 3050 30

7782 CLARK MANAGER 7839 09-Jun-81 2650 107788 SCOTT ANALYST 7566 09-Dec-82 3200 20

7839 KING PRESIDENT 17-Nov-81 5200 10

7844 TURNER SALESMAN 7698 08-Sep-81 1700 0 30

7876 ADAMS CLERK 7788 12-Jan-83 1300 20

7900 JAMES CLERK 7698 03-Dec-81 1150 30

7902 FORD ANALYST 7566 03-Dec-81 3200 20

7934 MILLER CLERK 7782 23-Jan-82 1500 10

DEPT TABLE (4 records)

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Basic SQL statementSQL> SELECT job, SUM(sal) PAYROLL2 FROM emp3 WHERE job NOT LIKE 'SALES%'4 GROUP BY job5 HAVING SUM(sal)>50006 ORDER BY SUM(sal);

Simple rules of SQL statements

SQL statements are not case sensitive, unless indicated. SQL statements can be entered on one or many lines. Keywords cannot be split across lines or abbreviated. Clauses are usually placed on separate lines for readability and ease of editing. Tabs and indents can be used to make code more readable. Keywords typically are entered in uppercase; all other words, such as table names and columns, are

entered in lowercase.


5/42

Within SQL*Plus, a SQL statement is entered at the SQL prompt, and the subsequent lines arenumbered. This is called the SQL buffer. Only one statement can be current at any time within thebuffer.

Place a semicolon (;) at the end of last clause.

Eliminating Duplicate RowsSQL> SELECT DISTINCT deptno, job2 FROM emp;

DEPTNO JOB------ ---------10 CLERK10 MANAGER10 PRESIDENT20 ANALYST...9 rows selected.

SQL versus SQL*PlusSQL SQL*Plus

Is a language for communicating with the OracleServer to access data

Recognizes SQL statements and sends them to theServer

Is based on American National Standards Institute(ANSI) standard SQL

Is the Oracle proprietary interface for executing SQLstatements

Manipulates data and table definitions in thedatabase

Does not allow manipulation of values in thedatabase

Is entered into the SQL buffer on one or more lines Is entered one line at a time; not stored in the SQLbuffer

Does not have a continuation character Has a dash (-) as a continuation character if thecommand is longer than one line

Cannot be abbreviated Can be abbreviated

Uses a termination character to execute commandimmediately

Does not require termination characters; commandsare executed immediately

Uses functions to perform some formatting Uses commands to format data

2. Where, Order by Clauses

IS NULL OperatorSQL> SELECT ename, mgr2 FROM emp3 WHERE mgr IS NULL;

ENAME

MGR-------------------KING


6/42

BETWEEN Operator

SQL> SELECT ename, sal2 FROM emp

3 WHERE sal BETWEEN 1000 AND 1500;ENAME SAL---------- ---------

MARTIN 1250TURNER 1500

WARD 1250ADAMS 1100MILLER 1300

IN OperatorSQL> SELECT empno, ename, sal, mgr2 FROM emp

3 WHERE mgr IN (7902, 7566, 7788);EMPNO ENAME SAL MGR --------- -------- -------- --------7902 FORD 3000 75667369 SMITH 800 79027788 SCOTT 3000 75667876 ADAMS 1100 7788

LIKE OperatorSQL> SELECT ename2 FROM emp3 WHERE ename LIKE '_A%';

ENAME----------JAMES

WARD

NOT OperatorSQL> SELECT ename, job2 FROM emp3 WHERE job NOT IN ('CLERK','MANAGER','ANALYST');

ENAME JOB---------- ---------KING PRESIDENT

MARTIN SALESMANALLEN SALESMANTURNER SALESMAN

WARD SALESMAN


7/42

SORT by Multiple Columns

SQL> SELECT ename, deptno, sal2 FROM emp

3 ORDER BY deptno, sal DESC;ENAME DEPTNO SAL---------- --------- ---------KING 10 5000CLARK 10 2450

MILLER 10 1300FORD 20 3000...14 rows selected.

3. SQL Functions

Single Row Functions Manipulate data items Accept arguments and return one value Act on each row returned Return one result per row May modify the datatype Can be nested

Function Result

LOWER(Oracle Education) oracle educationUPPER(Oracle Education) ORACLE

EDUCATIONINITCAP(ORACLE

education)

Oracle Education

CONCAT(Certified,DBA)

CertifiedDBA

SUBSTR(Administrator, 1,5)

Admin

LENGTH(Administrator) 13INSTR(Oracle, c) 4LPAD(sal, 10, *) ******5000ROUND(78.926, 2) 78.93TRUNC(78.926, 2) 78.92MOD(2100, 500) 100

SQL> SELECT 'The job title for '||INITCAP(ename)||' is '2 ||LOWER(job) AS "EMPLOYEE DETAILS"

3 FROM emp;EMPLOYEE DETAILS-----------------------------------------The job title for King is presidentThe job title for Blake is managerThe job title for Clark is manager...14 rows selected.


8/42

Date Functions

Function Result Description

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194 Number of months between twodates

ADD_MONTHS ('11-JAN-94',6) '11-JUL-94' Add calendar months to dateNEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95' Next day of the date specifiedLAST_DAY('01-SEP-95') '30-SEP-95' Last day of the monthROUND('25-JUL-95','MONTH') 01-AUG-95 Round dateROUND('25-JUL-95','YEAR') 01-JAN-96TRUNC('25-JUL-95','MONTH') 01-JUL-95 Truncate dateTRUNC('25-JUL-95','YEAR') 01-JAN-95

SQL> SELECT empno, hiredate,2 MONTHS_BETWEEN(SYSDATE, hiredate) TENURE,3 ADD_MONTHS(hiredate, 6) REVIEW,4 NEXT_DAY(hiredate, 'FRIDAY'), LAST_DAY(hiredate)5 FROM emp

6 WHERE MONTHS_BETWEEN (SYSDATE, hiredate)


9/42

HH or HH12 or HH24 Hour of day or hour (112) or hour (023)

MI Minute (059)

SS Second (059)

SSSSS Seconds past midnight (086399)

TH Ordinal number (for example, DDTH for 4TH)SP Spelled-out number (for example, DDSP for FOUR)

SPTH or THSP Spelled-out ordinal numbers (for example, DDSPTH for FOURTH)

fm remove padded blanks or suppress leading zeros

SQL> SELECT ename,2 TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE3 FROM emp;

ENAME HIREDATE---------- -----------------KING 17 November 1981BLAKE 1 May 1981

CLARK 9 June 1981JONES 2 April 1981MARTIN 28 September 1981ALLEN 20 February 1981...14 rows selected.

SQL> SELECT ename,2 TO_CHAR(hiredate, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM')3 HIREDATE4 FROM emp;

ENAME HIREDATE----------

------------------------------------------------KING Seventeenth of November 1981 12:00:00 AMBLAKE First of May 1981 12:00:00 AM...14 rows selected.

TO_CHAR Function with Numbers

Element Description Example Result

9 Numeric position (number of 9s determine display width) 999999 1234

0 Display leading zeros 099999 001234

$ Floating dollar sign $999999 $1234

L Floating local currency symbol L999999 FF1234

. Decimal point in position specified 999999.99 1234.00, Comma in position specified 999,999 1,234

MI Minus signs to right (negative values) 999999MI 1234-

PR Parenthesize negative numbers 999999PR

EEEE Scientific notation (format must specify four Es) 99.999EEEE 1.234E+03

V Multiply by 10 n times (n = no. of 9s after V) 9999V99 123400

B Display zero values as blank, not 0 B9999.99 1234.00


10/42

SQL> SELECT TO_CHAR(sal,'$99,999') SALARY2 FROM emp3 WHERE ename = 'SCOTT';

SALARY

--------$3,000

NVL Function

Converts null to an actual value Datatypes that can be used are date, character, and number. Datatypes must match

NVL(comm,0) NVL(hiredate,'01-JAN-97') NVL(job,'No Job Yet')

SQL> SELECT ename, sal, comm, (sal*12)+comm2 FROM emp;

ENAME JOB (SAL*12)+COMM---------- --------- -------------KING PRESIDENTBLAKE MANAGERCLARK MANAGERJONES MANAGER

MARTIN SALESMAN 16400...14 rows selected.

SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0)2 FROM emp;

ENAME SAL COMM (SAL*12)+NVL(COMM,0)---------- --------- --------- --------------------KING 5000 60000BLAKE 2850 34200CLARK 2450 29400JONES 2975 35700

MARTIN 1250 1400 16400ALLEN 1600 300 19500...14 rows selected.


11/42

DECODE Function

SQL> SELECT job, sal,2 DECODE(job, 'ANALYST', SAL*1.1,

3 'CLERK', SAL*1.15,4 'MANAGER', SAL*1.20,5 SAL)6 REVISED_SALARY7 FROM emp;

JOB SAL REVISED_SALARY--------- --------- --------------PRESIDENT 5000 5000

MANAGER 2850 3420MANAGER 2450 2940...14 rows selected.

Nesting FunctionsSQL> SELECT ename,2 NVL(TO_CHAR(mgr),'No Manager')3 FROM emp4 WHERE mgr IS NULL;

ENAME NVL(TO_CHAR(MGR),'NOMANAGER')---------- -----------------------------KING No Manager

4. Join Tables

EquijoinsSQL> SELECT emp.empno, emp.ename, emp.deptno,2 dept.deptno, dept.loc3 FROM emp, dept4 WHERE emp.deptno=dept.deptno;

EMPNO ENAME DEPTNO DEPTNO LOC----- -------------- ------ ---------7839 KING 10 10 NEW YORK7698 BLAKE 30 30 CHICAGO7782 CLARK 10 10 NEW YORK7566 JONES 20 20 DALLAS

...14 rows selected.


12/42

Non-Equijoins

SALGRADE TABLE

GRADE LOSAL HISAL----- ----- -------1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999

SQL> SELECT e.ename, e.sal, s.grade2 FROM emp e, salgrade s3 WHERE e.sal4 BETWEEN s.losal AND s.hisal;

ENAME SAL GRADE

---------- --------- ---------JAMES 950 1SMITH 800 1

ADAMS 1100 1...14 rows selected.

Outer JoinsSQL> SELECT e.ename, d.deptno, d.dname2 FROM emp e, dept d3 WHERE e.deptno(+) = d.deptno4 ORDER BY e.deptno;

ENAME DEPTNO DNAME---------- --------- -------------KING 10 ACCOUNTINGCLARK 10 ACCOUNTING...

40 OPERATIONS15 rows selected.

Self JoinsSQL> SELECT worker.ename||' works for '||manager.ename2 FROM emp worker, emp manager3 WHERE worker.mgr = manager.empno;

WORKER.ENAME||'WORKSFOR'||MANAG-------------------------------BLAKE works for KINGCLARK works for KINGJONES works for KING

MARTIN works for BLAKE...13 rows selected.


13/42

Using Table AliasesSQL> SELECT e.empno, e.ename, e.deptno,2 d.deptno, d.loc3 FROM emp e, dept d

4 WHERE e.deptno=d.deptno;

5. Group Functions

Types of Group Functions

Function DescriptionAVG([DISTINCT|ALL]n) Average value of n, ignoring null values

COUNTCOUNT({*|[DISTINCT|ALL]expr}) Number of rows, where expr evaluates to somethingother than null. Count all selected rows using *,including duplicates and rows with nulls

MAXMAX([DISTINCT|ALL]expr) Maximum value of expr, ignoring null values

MIN([DISTINCT|ALL]expr) Minimum value of expr, ignoring null valuesSTDDEV([DISTINCT|ALL]x) Standard deviation of n, ignoring null values

SUM([DISTINCT|ALL]n) Sum values of n, ignoring null values

VARIANCE([DISTINCT|ALL]x) Variance of n, ignoring null values

SQL> SELECT AVG(sal), MAX(sal),2 MIN(sal), SUM(sal)3 FROM emp4 WHERE job LIKE 'SALES%';

AVG(SAL) MAX(SAL) MIN(SAL)SUM(SAL)-------- --------- ------------------

1400 1600 12505600

SQL> SELECT COUNT(*)2 FROM emp3 WHERE deptno = 30;

COUNT(*)---------

6

GROUP BY ClauseSQL> SELECT deptno, AVG(sal)

2 FROM emp3 GROUP BY deptno;

DEPTNO AVG(SAL)------ ---------

10 2916.666720 217530 1566.6667


14/42

SQL> SELECT deptno, job, sum(sal)2 FROM emp3 GROUP BY deptno, job;

DEPTNO JOB SUM(SAL)

------- --------- ---------10 CLERK 130010 MANAGER 245010 PRESIDENT 500020 ANALYST 600020 CLERK 1900

...9 rows selected.

Illegal Queries Using Group Functions

SQL> SELECT deptno, COUNT(ename)2 FROM emp;

SELECT deptno, COUNT(ename)*

ERROR at line 1:ORA-00937: not a single-group group function

SQL> SELECT deptno, AVG(sal)2 FROM emp3 WHERE AVG(sal) > 20004 GROUP BY deptno;

WHERE AVG(sal) > 2000*

ERROR at line 3:ORA-00934: group function is not allowed

here

HAVING ClauseSQL> SELECT deptno, max(sal)2 FROM emp3 GROUP BY deptno4 HAVING max(sal)>2900;

DEPTNO MAX(SAL)------ ---------

10 500020 3000


15/42

6. Subqueries

Guidelines for Using Subqueries

SQL> SELECT ename2 FROM emp3 WHERE sal >4 (SELECT sal5 FROM emp6 WHERE empno=7566);

ENAME----------KINGFORDSCOTT

The subquery (inner query) executes once before the main query. The result of the subquery is used by the main query (outer query).

Enclose subqueries in parentheses. Place subqueries on the right side of the comparison operator. Do not add an ORDER BY clause to a subquery. Use single-row operators with single-row subqueries. Use multiple-row operators with multiple-row subqueries.

Single-Row Comparison Operators

Operator Meaning

= Equal to> Greater than>= Greater than or equal to

< Less than SELECT ename, job2 FROM emp3 WHERE job =4 (SELECT job5 FROM emp6 WHERE empno = 7369)7 AND sal >8 (SELECT sal9 FROM emp10 WHERE empno = 7876);

ENAME JOB---------- ---------

MILLER CLERK


16/42

Multiple-Row Comparison Operators

Operator Meaning

IN Equal to any member in the list

ANY Compare value to each value returned by the subqueryALL Compare value to every value returned by the subquery

SQL> SELECT empno, ename, job2 FROM emp3 WHERE sal < ANY4 (SELECT sal5 FROM emp6 WHERE job = 'CLERK')7 AND job 'CLERK';

EMPNO ENAME JOB----- ------ --------7654 MARTIN SALESMAN

7521 WARD SALESMAN

SQL> SELECT empno, ename, job2 FROM emp3 WHERE sal > ALL4 (SELECT avg(sal)5 FROM emp6 GROUP BY deptno);

EMPNO ENAME JOB----- ------ ---------7839 KING PRESIDENT7566 JONES MANAGER7902 FORD ANALYST

7788 SCOTT ANALYST

SQL> SELECT ename, deptno, sal, comm2 FROM emp3 WHERE (sal, NVL(comm,-1)) IN4 (SELECT sal, NVL(comm,-1)5 FROM emp6 WHERE deptno = 30);

ENAME DEPTNO SAL COMM---------- --------- --------- ---------JAMES 30 950

WARD 30 1250 500MARTIN 30 1250 1400TURNER 30 1500 0

ALLEN 30 1600 300BLAKE 30 2850

6 rows selected.


17/42

7. SQL*Plus

Substitution Variable

Use a variable prefixed with an ampersand (&) to prompt the user for a value.

SQL> SELECT empno, ename, sal, deptno2 FROM emp3 WHERE empno = &employee_num;

Enter value for employee_num: 7369

EMPNO ENAME SAL DEPTNO--------- ---------- --------- ---------

7369 SMITH 800 20

Use single quotation marks for date and character values.

SQL> SELECT ename, deptno, sal*122 FROM emp3 WHERE job='&job_title';

Enter value for job_title: ANALYST

ENAME DEPTNO SAL*12---------- --------- ---------SCOTT 20 36000FORD 20 36000

ACCEPT CommandACCEPT dept PROMPT 'Provide the department name: 'SELECT *

FROM deptWHERE dname = UPPER('&dept')/

Provide the department name: Sales

DEPTNO DNAME LOC--------- -------------- -------------

30 SALES CHICAGO

Customizing the SQL*Plus Environment

SET Variable and Values DescriptionARRAY[SIZE] {20| n} Sets the database data fetch size

COLSEP {_|text} Sets text to be printed between columns. Default is single spaceFEED[BACK] {6|n|OFF|ON} Displays the number of records returned by a query when the query

selects at least n recordsHEA[DING] {OFF|ON} Determine whether column headings are displayed in reports

LIN[ESIZE] {80|n} Sets the number of characters per line to n for reports

LONG {80|n} Sets the maximum width for displaying LONG values

PAGES[IZE] {24|n} Specifies the number of lines per page of output

PAU[SE] {OFF|ON|text} Allows you to control scrolling of your terminal (You must press[Return] after seeing each pause.)


18/42

TERM[OUT] {OFF|ON} Determines whether output is displayed on screen

COL[UMN] [column option] Controls column formats

TTI[TLE] [text|OFF|ON] Specifies a header to appear at the top of each page

BTI[TLE] [text|OFF|ON] Specifies a footer to appear at the bottom of each page of the report

BRE[AK] [ON report_element] Suppresses duplicate values and sections rows of data with line feeds

8. Data Manipulation Language (DML)

INSERT Statement

INSERT INTO table [(column [, column...])]VALUES (value [, value...]);

Only one row is inserted at a time with this syntax. Insert a new row containing values for each column. List values in the default order of the columns in the table. Optionally list the columns in the INSERT clause.

Enclose character and date values within single quotation marks.

SQL> INSERT INTO dept (deptno, dname, loc)2 VALUES (50, 'DEVELOPMENT', 'DETROIT');

1 row created.

Inserting Rows with Null Values Implicit method: Omit the column from the column list. Explicit method: Specify the NULL keyword.

SQL> INSERT INTO dept (deptno, dname )2 VALUES (60, 'MIS');

1 row created.

SQL> INSERT INTO dept2 VALUES (70, 'FINANCE', NULL);

1 row created.

UPDATE Statement

UPDATE tableSET column = value [, column = value][WHERE condition];

Update more than one row at a time, if required. Specific row or rows are modified when you specify the WHERE clause.

SQL> UPDATE emp2 SET deptno = 203 WHERE empno = 7782;

1 row updated.


19/42

All rows in the table are modified if you omit the WHERE clause.

SQL> UPDATE employee2 SET deptno = 20;

14 rows updated.

DELETE Statement

DELETE [FROM] table[WHERE condition];

You can remove existing rows from a table by using the DELETE statement. Specific row or rows are deleted when you specify the WHERE clause.

SQL> DELETE FROM department2 WHERE dname = 'DEVELOPMENT';

1 row deleted.

All rows in the table are deleted if you omit the WHERE clause.

SQL> DELETE FROM department;4 rows deleted.

Transactions

Consist of one of the following statements: DML statements that make up one consistent change to the data One DDL statement One DCL statement

Begin when the first executable SQL statement is executed End with one of the following events:

COMMIT or ROLLBACK DDL or DCL statement executes (automatic commit) User exits System crashes

An automatic commit occurs under the following circumstances: A DDL statement is issued A DCL statement is issued A normal exit from SQL*Plus, without explicitly issuing COMMIT or ROLLBACK

An automatic rollback occurs under an abnormal termination of SQL*Plus or a system failure

Before COMMIT or ROLLBACK

The previous state of the data can be recovered. The current user can review the results of the DML operations by using the SELECT statement.

Other users cannotview the results of the DML statements by the current user. The affected rows are locked; other users cannot change the data within the affected rows.


20/42

After COMMIT

SQL> COMMIT;Commit complete.

Data changes are made permanent in the database. The previous state of the data is permanently lost. All users can view the results. Locks on the affected rows are released; those rows are available for other users to manipulate. All savepoints are erased.

After ROLLBACK

SQL> ROLLBACK;Rollback complete.

Data changes are undone.

Previous state of the data is restored. Locks on the affected rows are released.

SAVEPOINT

Create a marker within a current transaction by using the SAVEPOINT statement. Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement.

SQL> UPDATE...SQL> SAVEPOINT update_done;Savepoint created.SQL> INSERT...SQL> ROLLBACK TO update_done;Rollback complete.

9. Managing Tables

Naming Rules Of Database Tables And Columns

Must begin with a letter Can be 130 characters long Must contain only AZ, az, 09, _, $, and # Must not duplicate the name of another object owned by the same user Must not be an Oracle Server reserved word

CREATE TABLE Statement

CREATE TABLE [schema.]table

(columndatatype [DEFAULT expr], ..., ...); schema is the same as the owners name. table is the name of the table. DEFAULT expr specifies a default value if a value is omitted in the INSERT

statement. column is the name of the column. datatype is the column's datatype and length.


21/42

SQL> CREATE TABLE dept2 (deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13));

Table created.

SQL> DESCRIBE dept

Name Null? Type--------------------------- -------- ---------DEPTNO NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)

Datatypes

Datatype Description

VARCHAR2(size) Variable-length character data (A maximum size must be specified. Default

and minimum size is 1, maximum size is 4000.)CHAR(size) Fixed-length character data of length size bytes (Default and minimum size

is 1, maximum size is 2000.)NUMBER(p,s) Number having precision p and scale s; the precision is the total number of

decimal digits, and the scale is the number of digits to the right of thedecimal point (The precision can range from 1 to 38 and the scale can rangefrom -84 to 127.)

DATE Date and time values between January 1, 4712 B.C., and December 31, 9999A.D.

LONG Variable-length character data up to 2 gigabytes

CLOB Single-byte character data up to 4 gigabytes

RAW(size) Raw binary data of length size. Maximum size is 2000 (A maximum sizemust be specified.)

LONG RAW Raw binary data of variable length up to 2 gigabytesBLOB Binary data up to 4 gigabytes

BFILE Binary data stored in an external file; up to 4 gigabytes

Create Table by Subquery

SQL> CREATE TABLE dept302 AS3 SELECT empno, ename, sal*12 ANNSAL, hiredate4 FROM emp5 WHERE deptno = 30;

Table created.

SQL> DESCRIBE dept30Name Null? Type----------- -------- -----EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(10)

ANNSAL NUMBERHIREDATE DATE


22/42

Add Column

SQL> ALTER TABLE dept302 ADD (job VARCHAR2(9));

Table altered.

The new column becomes the last column.

EMPNO ENAME ANNSAL HIREDATE JOB--------- ---------- --------- --------- ----7698 BLAKE 34200 01-MAY-817654 MARTIN 15000 28-SEP-817499 ALLEN 19200 20-FEB-817844 TURNER 18000 08-SEP-81...6 rows selected.

Modify Column

SQL> ALTER TABLE dept302> MODIFY (ename VARCHAR2(15));

Table altered.

Increase the width or precision of a numeric column. Decrease the width of a column if the column contains only null values or if the table has no rows. Change the datatype if the column contains null values. Convert a CHAR column to the VARCHAR2 datatype or convert a VARCHAR2 column to the

CHAR datatype if the column contains null values or if you do not change the size. A change to the default value of a column affects only subsequent insertions to the table.

Drop Table

SQL> DROP TABLE dept30;Table dropped.

All data and structure in the table is deleted. Any pending transactions are committed. All indexes are dropped. You cannotroll back this statement.

Rename Table

SQL> RENAME dept TO department;Table renamed.

To change the name of a table, view, sequence, or synonym, you execute the RENAME statement.

You must be the owner of the object.


23/42

Truncate Table

SQL> TRUNCATE TABLE department;Table truncated.

Removes all rows from a table Releases the storage space used by that table Cannot roll back row removal when using TRUNCATE

10. Constraints

Column level constraint

column [CONSTRAINT constraint_name] constraint_type,

Table level constraint

column,...[CONSTRAINT constraint_name] constraint_type(column, ...),

NOT NULL Constraint

SQL> CREATE TABLE emp(2 empno NUMBER(4),3 ename VARCHAR2(10) NOT NULL,4 job VARCHAR2(9),5 mgr NUMBER(4),6 hiredate DATE,7 sal NUMBER(7,2),8 comm NUMBER(7,2),

9 deptno NUMBER(7,2) NOT NULL);

UNIQUE Constraint

SQL> CREATE TABLE dept(2 deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13),5 CONSTRAINT dept_dname_uk UNIQUE(dname));

PRIMARY KEY Constraint

SQL> CREATE TABLE dept(

2 deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13),5 CONSTRAINT dept_dname_uk UNIQUE (dname),6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));


24/42

FOREIGN KEY Constraint

SQL> CREATE TABLE emp(2 empno NUMBER(4),

3 ename VARCHAR2(10) NOT NULL,4 job VARCHAR2(9),5 mgr NUMBER(4),6 hiredate DATE,7 sal NUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2) NOT NULL,10 CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno)11 REFERENCES dept (deptno));

CHECK Constraint

..., deptno NUMBER(2),CONSTRAINT emp_deptno_ckCHECK (DEPTNO BETWEEN 10 AND 99),...

Add Constraint

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp_mgr_fk3 FOREIGN KEY(mgr) REFERENCES

emp(empno);Table altered.

Drop Constraint

SQL> ALTER TABLE emp2 DROP CONSTRAINT emp_mgr_fk;

Table altered.

Remove the PRIMARY KEY constraint on the DEPT table and drop the associated FOREIGNKEY constraint on the EMP.DEPTNO column.

SQL> ALTER TABLE dept2 DROP PRIMARY KEY CASCADE;

Table altered.

Disable and Enable Constraints

Execute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity constraint. Apply the CASCADE option to disable dependent integrity constraints.

SQL> ALTER TABLE emp2 DISABLE CONSTRAINT emp_empno_pk CASCADE;

Table altered.


25/42

SQL> ALTER TABLE emp2 ENABLE CONSTRAINT emp_empno_pk;

Table altered.

A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key orPRIMARY KEY constraint.

Viewing Constraints by USER_CONSTRAINTS

SQL> SELECT constraint_name, constraint_type,2 search_condition3 FROM user_constraints4 WHERE table_name = 'EMP';

CONSTRAINT_NAME C SEARCH_CONDITION------------------------ - -------------------------SYS_C00674 C EMPNO IS NOT NULL

SYS_C00675 C DEPTNO IS NOT NULLEMP_EMPNO_PK P...

11. VIEWS

Advantages of Views

Restrict access to the database because the view can display a selective portion of the database. Allow users to make simple queries to retrieve the results from complicated queries. For example,

views allow users to query information from multiple tables without knowing how to write a joinstatement.

Provide data independence for ad hoc users and application programs. One view can be used to

retrieve data from several tables. Provide groups of users access to data according to their particular criteria.

Simple Views and Complex Views

Feature Simple Views Complex Views

Number of tables One One or moreContain functions No YesContain groups of data No YesDML through view Yes Not always

Example 1 : Create a simple view.

SQL> CREATE VIEW salvu302 AS SELECT empno EMPLOYEE_NUMBER, ename NAME, sal SALARY3 FROM emp4 WHERE deptno = 30;

View created.


26/42

SQL> SELECT *2 FROM salvu30;

EMPLOYEE_NUMBER NAME SALARY--------------- ---------- ---------

7698 BLAKE 28507654 MARTIN 12507499 ALLEN 16007844 TURNER 15007900 JAMES 9507521 WARD 1250

6 rows selected.

Example 2 : Create a complex view.

SQL> CREATE VIEW dept_sum_vu2 (name, minsal, maxsal, avgsal)3 AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)

4 FROM emp e, dept d 5 WHERE e.deptno = d.deptno6 GROUP BY d.dname;

View created.

SQL> SELECT *2 FROM dept_sum_vu;

NAME MINSAL MAXSAL AVGSAL-------------- --------- --------- ---------

ACCOUNTING 1300 5000 2916.6667RESEARCH 800 3000 2175SALES 950 2850 1566.6667

WITH CHECK OPTION & WITH READ ONLY

SQL> CREATE OR REPLACE VIEW empvu202 AS SELECT *3 FROM emp4 WHERE deptno = 205 WITH CHECK OPTION CONSTRAINT empvu20_ck;

View created.

You can ensure that DML on the view stayswithin the domain of the view by using the WITH CHECK OPTION.

Any attempt to change the department number for any row in the view will fail because it violatesthe WITH CHECK OPTION constraint.


27/42

SQL> CREATE OR REPLACE VIEW empvu102 (employee_number, employee_name, job_title)3 AS SELECT empno, ename, job4 FROM emp

5 WHERE deptno = 106 WITH READ ONLY;View created.

You can ensure that no DML operations occur by adding the WITH READ ONLY option to yourview definition.

Any attempt to perform a DML on any row in the view will result in Oracle Server error ORA-01752.

12. Sequence

Define a sequence to generate sequential numbers automatically

CREATE SEQUENCE sequence

[INCREMENT BY n][START WITH n][{MAXVALUE n | NOMAXVALUE}][{MINVALUE n | NOMINVALUE}][{CYCLE | NOCYCLE}][{CACHE n | NOCACHE}];

Example 1 : Create a sequence named DEPT_DEPTNO to be used for the primary key of theDEPT table and use it for insert.

SQL> CREATE SEQUENCE dept_deptno2 INCREMENT BY 13 START WITH 91

4 MAXVALUE 1005 NOCACHE6 NOCYCLE;

Sequence created.

SQL> INSERT INTO dept(deptno, dname, loc)2 VALUES (dept_deptno.NEXTVAL,3 'MARKETING', 'SAN DIEGO');

1 row created.

13. INDEX

Schema object

Used by the Oracle Server to speed up the retrieval of rows by using a pointer Reduces disk I/O by using rapid path access method to locate the data quickly Independent of the table it indexes Automatically used and maintained by the Oracle Server A unique index is created automatically when you define a PRIMARY KEY or UNIQUE key

constraint in a table definition. Users can create non unique indexes on columns to speed up access time to the rows.


28/42

Example 1 : Improve the speed of query access on the ENAME column in the EMP table

SQL> CREATE INDEX emp_ename_idx2 ON emp(ename);

Index created.

Guidelines of Creating an Index

The column is used frequently in the WHERE clause or in a join condition. The column contains a wide range of values. The column contains a large number of null values. Two or more columns are frequently used together in a WHERE clause or a join condition. The table is large and most queries are expected to retrieve less than 24% of the rows. The table is not updated frequently

14. Synonyms

Refer to a table owned by another user. Shorten lengthy object names.

SQL> CREATE SYNONYM d_sum2 FOR dept_sum_vu;

Synonym Created.

15. User Access

System Privileges

System Privilege Operations Authorized

CREATE SESSION Connect to the database

CREATE TABLE Create tables in the users schema

CREATE SEQUENCE Create a sequence in the users schemaCREATE VIEW Create a view in the users schema

CREATE PROCEDURE Create a stored procedure, function, or package in the users schema

The DBA can grant a user specific system privileges.

SQL> GRANT create table, create sequence, create view2 TO scott;

Grant succeeded.


29/42

Object Privileges

Object Privilege Table View Sequence ProcedureALTER

DELETE EXECUTE

INDEX

INSERT

REFERENCES

SELECT

UPDATE

Object privileges vary from object to object. An owner has all the privileges on his own objects. An owner can grant specific privileges on that owners object to other users.

Example 1 : Grant query privileges on the EMP table.

SQL> GRANT select2 ON emp3 TO sue, rich;

Grant succeeded.

Example 2 : Grant privileges to update specific columns to users and roles.

SQL> GRANT update (dname, loc)2 ON dept3 TO scott, manager;

Grant succeeded.

Example 3 : Give a user authority to pass along the privileges.

SQL> GRANT select, insert2 ON dept3 TO scott4 WITH GRANT OPTION;

Grant succeeded.

Example 4 : Allow all users on the system to query data from Alices DEPT table.

SQL> GRANT select2 ON alice.dept3 TO PUBLIC;

Grant succeeded.


30/42

Example 5 : revoke the SELECT and INSERT privileges given to user Scott on the DEPT table.

SQL> REVOKE select, insert2 ON dept3 FROM scott;

Revoke succeeded.

Privileges granted to others through the WITH GRANT OPTION will also be revoked.

16. PL/SQL Fundamental

What is PL/SQL

PL/SQL is an extension to SQL with design features of programming languages. Data manipulation and query statements of SQL are included within procedural units of code.

Benefits of PL/SQL

Take Advantage of Portability Because PL/SQL is native to the Oracle Server, you can move programs to any hostenvironment (operating system or platform) that supports the Oracle Server and PL/SQL.In other words, PL/SQL programs can run anywhere the Oracle Server can run; you do notneed to tailor them to each new environment.

You can also move code between the Oracle Server and your application. You can writeportable program packages and create libraries that can be reused in differentenvironments.

Declare Identifiers Declare variables, cursors, constants, and exceptions and then use them in SQL and

procedural statements. Declare variables belonging to scalar, reference, composite, and large object (LOB)

datatypes. Declare variables dynamically based on the data structure of tables and

columns in the database. Program with Procedural Language Control Structures

Execute a sequence of statements conditionally. Execute a sequence of statements iteratively in a loop. Process individually the rows returned by a multiple-row query with an explicit cursor.

Handle Errors Process Oracle Server errors with exception-handling routines. Declare user-defined error conditions and process them with exception-handling routines.

17. Declaring Variables in PL/SQL

identifier[CONSTANT] datatype [NOT NULL][:= | DEFAULT expr];

Examples :

Declarev_hiredate DATE;v_deptno NUMBER(2) NOT NULL := 10;v_location VARCHAR2(13) := 'Atlanta';c_comm CONSTANT NUMBER := 1400;

Guildelines : Follow naming conventions.


31/42

Initialize variables designated as NOT NULL. Initialize identifiers by using the assignment operator (:=) or by using the DEFAULT reserved word. Declare at most one identifier per line. Two variables can have the same name, provided they are in different blocks. The variable name (identifier) should not be the same as the name of table columns used in the

block.

The %TYPE Attribute

Declare a variable according to: A database column definition Another previously declared variable

Prefix %TYPE with: The database table and column The previously declared variable name

Examples :

...

v_ename emp.ename%TYPE;v_balance NUMBER(7,2);v_min_balance v_balance%TYPE :=

10;...

18. Executable Section in PL/SQL

SELECT Statements in PL/SQL

Syntax

SELECT select_list

INTO {variable_name[, variable_name]...| record_name}

FROM tableWHERE condition;

Example :

DECLAREv_deptno NUMBER(2);v_loc VARCHAR2(15);

BEGINSELECT deptno, locINTO v_deptno, v_loc

FROM deptWHERE dname = 'SALES';

...END;

DML Statements in PL/SQL

Example 1 : Insert statement in PL/SQL


32/42


33/42

IF condition THENstatements;

[ELSIF condition THEN

statements;][ELSEstatements;]

END IF;

Example :

. . .IF v_start > 100 THENv_start := 2 * v_start;

ELSIF v_start >= 50 THENv_start := .5 * v_start;

ELSEv_start := .1 * v_start;

END IF;. . .

Basic Loop

Syntax

LOOPstatement1;

. . .EXIT [WHEN condition];

END LOOP;

Example

DECLAREv_ordid item.ordid%TYPE := 101;v_counter NUMBER(2) := 1;

BEGINLOOPINSERT INTO item(ordid, itemid)VALUES(v_ordid, v_counter);v_counter := v_counter + 1;EXIT WHEN v_counter > 10;

END LOOP;END;

FOR Loop

Syntax

FORcounterin [REVERSE]lower_bound..upper_boundLOOP

statement1;


34/42

statement2;. . .

END LOOP;

Example

DECLAREv_ordid item.ordid%TYPE := 101;

BEGINFOR i IN 1..10 LOOPINSERT INTO item(ordid, itemid)

VALUES(v_ordid, i);END LOOP;

END;

WHILE Loop

Syntax

WHILE condition LOOP statement1;statement2;. . .

END LOOP;

Example

ACCEPT p_price PROMPT 'Enter the price of the item: 'ACCEPT p_itemtot PROMPT 'Enter the maximum total for purchase of item:'

DECLARE...v_qty NUMBER(8) := 1;v_running_total NUMBER(7,2) := 0;BEGIN...WHILE v_running_total < &p_itemtot LOOP...

v_qty := v_qty + 1;v_running_total := v_qty * &p_price;END LOOP;

...

20. Composite Datatypes

PL/SQL Table

Are composed of two components: Primary key of datatype BINARY_INTEGER Column of scalar or record datatype

Increase dynamically because they are unconstrained

Primary Key Column


35/42

... ...1 Jones2 Smith3 Maduro... ...

BINARY_INTEGER Scalar

Example

...TYPE ename_table_typeIS TABLE OF emp.ename%TYPEINDEX BY BINARY_INTEGER;

ename_table ename_table_type;...

PL/SQL Records

Must contain one or more components of any scalar, RECORD, or PL/SQL TABLE datatype-called

fields Are similar in structure to records in a 3GL Are not the same as rows in a database table Treat a collection of fields as a logical unit Are convenient for fetching a row of data from a table for processing

Example

...TYPE emp_record_type IS RECORD(ename VARCHAR2(10),job VARCHAR2(9),sal NUMBER(7,2));

emp_record emp_record_type;...

The %ROWTYPE Attribute

Declare a variable according to a collection of columns in a database table or view. Prefix %ROWTYPE with the database table. Fields in the record take their names and datatypes from the columns of the table or view. The number and datatypes of the underlying database columns may not be known. The number and datatypes of the underlying database column may change at runtime. Useful when retrieving a row with the SELECT statement.

Example 1 : Declare a variable to store the same information about a department as it is stored inthe DEPT table.

dept_record dept%ROWTYPE;

Example 2 : Declare a variable to store the same information about a employee as it is stored in theEMP table.

emp_record emp%ROWTYPE;


36/42

21. Explicit Cursors

DECLARE OPENOPEN FETCH EMPTY?

No

CLOSECLOSE

Yes

1. Declare the cursor by naming it and defining the structure of the query to be performed within it.

2. Open the cursor. The OPEN statement executes the query and binds any variables that are

referenced. Rows identified by the query are called the active setand are now available for

fetching.

3. Fetch data from the cursor. The FETCH statement loads the current row from the cursor into

variables. Each fetch causes the cursor to move its pointer to the next row in the active set.

Therefore each fetch accesses a different row returned by the query. In the flow diagram shown

in the slide, each fetch tests the cursor for any existing rows. If rows are found, it loads the

current row into variables; otherwise it closes the cursor.

4. Close the cursor. The CLOSE statement releases the active set of rows. It is now possible to

reopen the cursor to establish a fresh active set.

Cursor FOR Loops

Syntax

FOR record_name IN cursor_name LOOP

statement1;statement2;. . .

END LOOP;

Shortcut to process explicit cursors. Implicit open, fetch, and close occur. Do not declare the record; it is implicitly declared.


37/42

Example : Retrieve employees one by one until there are no more left.

DECLARECURSOR c1 ISSELECT empno, enameFROM emp;

BEGINFOR emp_record IN c1 LOOP

-- implicit open and implicit fetch occurIF emp_record.empno = 7839 THEN...

END LOOP; -- implicit close occursEND;

Cursors with Parameters

Syntax

CURSOR cursor_name[(parameter_name datatype, ...)]

ISselect_statement;

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.

Example

DECLARECURSOR c1

(v_deptno NUMBER, v_job VARCHAR2) ISSELECT empno, enameFROM empWHERE deptno = v_deptnoAND job = v_job;

BEGINOPEN c1(10, 'CLERK');

...

The SELECT ..... FOR UPDATE Clause

Syntax

SELECT ...FROM ...FOR UPDATE [NOWAIT]

Explicit locking lets you deny access for the duration of a transaction. Lock the rows before the update or delete.


38/42

Example

DECLARECURSOR c1 ISSELECT empno, enameFROM empFOR UPDATE NOWAIT;

The WHERE CURRENT OF Clause

Syntax

WHERE CURRENT OF cursor

Use cursors to update or delete the current row. Include the FOR UPDATE clause in the cursor query to lock the rows first. Use the WHERE CURRENT OF clause to reference the current row from an explicit cursor.

Example

DECLARECURSOR c1 ISSELECT ...FOR UPDATE NOWAIT;

BEGIN...FOR emp_record IN c1 LOOPUPDATE ...WHERE CURRENT OF c1;

...

END LOOP;COMMIT;END;

22. Handling Exceptions

Trapping Exceptions

Syntax

EXCEPTIONWHEN exception1 [ORexception2. . .] THEN

statement1;. . .[WHEN exception3[ORexception4 . . .] THEN

statement1;. . .]

[WHEN OTHERS THENstatement1;. . .]

WHEN OTHERS is the last clause. EXCEPTION keyword starts exception-handling section.


39/42

Several exception handlers are allowed. Only one handler is processed before leaving the block.

Predefined Oracle Server Errors

Exception Name Oracle ServerError Number

Description

DUP_VAL_ON_INDEX ORA-00001 Attempted to insert a duplicate value.

INVALID_CURSOR ORA-01001 Illegal cursor operation occurred.

NO_DATA_FOUND ORA-01403 Single row SELECT returned no data.

TOO_MANY_ROWS ORA-01422 Single row SELECT returned more than one row.

ZERO_DIVIDE ORA-01476 Attempted to divide by zero.

Example : Trapping Predefined Oracle Server Errors

BEGIN SELECT ... COMMIT;EXCEPTION

WHEN NO_DATA_FOUND THEN statement1;

statement2;WHEN TOO_MANY_ROWS THEN statement1;WHEN OTHERS THEN

statement1;statement2;statement3;

END;

Non-Predefined Oracle Server Errors

Declare Associate

Declarative Section

Reference

Exception-Handling

Section


40/42

Example : Trapping Non-Predefined Oracle Server Errors

DECLAREe_products_invalid EXCEPTION;PRAGMA EXCEPTION_INIT (

e_products_invalid, -2292);v_message VARCHAR2(50);

BEGIN. . .EXCEPTIONWHEN e_products_invalid THEN:g_message := 'Product code

specified is not valid.';. . .END;

User-Defined Exception

Declare

Declarative

Section

Raise

Executable

Section

Reference

Exception-Handling

Section

Example : Trapping User-Defined Exceptions

[DECLARE]e_amount_remaining EXCEPTION;

. . .BEGIN. . .RAISE e_amount_remaining;

. . .EXCEPTIONWHEN e_amount_remaining THEN:g_message := 'There is still an amount

in stock.';. . .END;


41/42

Frequently Asked Questions in SQL and PL/SQL

Q1 : Can one drop a column from a table?

Oracle does not provide a way to DROP a column before Oracle 8i

Other workarounds:

1. update t1 set column_to_drop = NULL;rename t1 to t1_base;create view t1 as select from t1_base;

2. create table t2 as select from t1;drop table t1;rename t2 to t1;

Q2 : How can I eliminate duplicates values in a table?

SQL> create table table_name2 as select distinct * from table_name1;SQL> drop table_name1;SQL> rename table_name2 to table_name1;

Q3 : How does one count different data values in a column?

select dept_no, sum( decode(sex,'M',1,0)) MALE,sum( decode(sex,'F',1,0)) FEMALE,count(decode(sex,'M',1,'F',1)) TOTAL

from emp

group by dept_no;

Q4 : Can one rename a column in a table?

No!

Other Workarounds:

1. rename t1 to t1_base;create view t1 as select * from t1_base;

2. create table t2 as select * from t1;drop table t1;rename t2 to t1;


42/42

Q5 : How can I change my Oracle password?

Issue the following SQL command: ALTER USER IDENTIFIED BY

/

From Oracle8 you can just type "password" from SQL*Plus, or if you needto changeanother user's password, type "password user_name".

Q6 : Can I update through a view ?

You can do this iff

1.Your view is a simple subset of a single table.2.All "not null" columns for the table must be in the view.

3.The primary key is in the view.

Q7 : Are views updated when I update base tables ?

Yes, that is the whole idea of views. The only thing Oracle stores fora view is the text of the definition. When you select from a view,Oracle looks up the text used to define the view and then executes thatquery.

basic sql statement

Documents