sql08_new - 9 Manipulating Data Objectives After completing...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 9 Manipulating Data Objectives After completing this lesson, you should be able to do the following: Describe each DML statement Insert rows into a table Update rows in a table Delete rows from a table Control transactions 9-2 Data Manipulation Language A DML statement is executed when you: Add new rows to a table Modify existing rows in a table Remove existing rows from a table A transaction consists of a collection of DML statements that form a logical unit of work. 9-3 Adding a New Row to a Table 50 DEVELOPMENT DETROIT New row DEPT DEPTNO -----10 20 30 40 DNAME ---------ACCOUNTING RESEARCH SALES OPERATIONS LOC -------NEW YORK DALLAS CHICAGO BOSTON "...insert a new row into DEPT table..." DEPT DEPTNO -----10 20 30 40 DNAME ---------ACCOUNTING RESEARCH SALES OPERATIONS LOC -------NEW YORK DALLAS CHICAGO BOSTON 50 DEVELOPMENT DETROIT 9-4 The INSERT Statement Add new rows to a table by using the INSERT statement. INSERT INTO VALUES table [(column [, column...])] (value [, value...]); Only one row is inserted at a time with this syntax. 9-5 Inserting New Rows 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. SQL> INSERT INTO 2 VALUES 1 row created. dept (deptno, dname, loc) (50, 'DEVELOPMENT', 'DETROIT'); Enclose character and date values within single quotation marks. 9-6 Inserting Rows with Null Values Implicit method: Omit the column from the column list. SQL> INSERT INTO 2 VALUES 1 row created. dept (deptno, dname ) (60, 'MIS'); Explicit method: Specify the NULL keyword. SQL> INSERT INTO 2 VALUES 1 row created. dept (70, 'FINANCE', NULL); 9-7 Inserting Special Values The SYSDATE function records the current date and time. SQL> INSERT INTO 2 3 4 VALUES 5 6 1 row created. emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) (7196, 'GREEN', 'SALESMAN', 7782, SYSDATE, 2000, NULL, 10); 9-8 Inserting Specific Date Values Add a new employee. SQL> INSERT INTO 2 VALUES 3 4 1 row created. emp (2296,'AROMANO','SALESMAN',7782, TO_DATE('FEB 3,97', 'MON DD, YY'), 1300, NULL, 10); Verify your addition. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- -------- ---- --------- ---- ---- -----2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10 9-9 Inserting Values by Using Substitution Variables Create an interactive script by using SQL*Plus substitution parameters. SQL> INSERT INTO 2 VALUES 3 dept (deptno, dname, loc) (&department_id, '&department_name', '&location'); Enter value for department_id: 80 Enter value for department_name: EDUCATION Enter value for location: ATLANTA 1 row created. 9-10 Changing Data in a Table EMP EMPNO ENAME 7839 7698 7782 7566 ... KING BLAKE CLARK JONES JOB PRESIDENT MANAGER MANAGER MANAGER ... DEPTNO 10 30 10 20 "...update a row in EMP table..." EMP EMPNO ENAME 7839 7698 7782 7566 ... 9-11 JOB PRESIDENT MANAGER MANAGER MANAGER ... DEPTNO 10 30 20 10 20 KING BLAKE CLARK JONES The UPDATE Statement Modify existing rows with the UPDATE statement. UPDATE SET [WHERE table column = value [, column = value] condition]; Update more than one row at a time, if required. 9-12 Updating Rows in a Table Specific row or rows are modified when you specify the WHERE clause. SQL> UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782; 1 row updated. All rows in the table are modified if you omit the WHERE clause. SQL> UPDATE employee 2 SET deptno = 20; 14 rows updated. 9-13 Updating Rows: Integrity Constraint Error SQL> UPDATE 2 SET 3 WHERE emp deptno = 55 deptno = 10; 55 er b m UPDATE emp nu * nt ERROR at line 1: e rtm ORA-02291: integrity constraint pa violated - e D parent key not found 9-14 ist ex ot sn oe d (USR.EMP_DEPTNO_FK) Removing a Row from a Table DEPT DEPTNO -----10 20 30 40 50 60 ... DNAME ---------ACCOUNTING RESEARCH SALES OPERATIONS LOC -------NEW YORK DALLAS CHICAGO BOSTON DEVELOPMENT DETROIT MIS "...delete a row from DEPT table..." DEPT DEPTNO -----10 20 30 40 60 ... DNAME ---------ACCOUNTING RESEARCH SALES OPERATIONS MIS LOC -------NEW YORK DALLAS CHICAGO BOSTON 9-15 The DELETE Statement You can remove existing rows from a table by using the DELETE statement. DELETE [FROM] [WHERE table condition]; 9-16 Deleting Rows from a Table Specific row or rows are deleted when you specify the WHERE clause. SQL> DELETE FROM 2 WHERE 1 row deleted. department dname = 'DEVELOPMENT'; All rows in the table are deleted if you omit the WHERE clause. SQL> DELETE FROM 4 rows deleted. 9-17 department; y ar le. rim tab a p er SQL> DELETE FROM dept ins oth 2 WHERE deptno = 10; nta an co y in at ke th n w DELETE FROM dept ro reig * e a a fo t ERROR at line 1: le de d as ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK) e ot record found n violated - child us n ca t i s ou tha Y y ke 9-18 Deleting Rows: Integrity Constraint Error Database Transactions 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 9-19 Advantages of COMMIT and ROLLBACK Ensure data consistency Preview data changes before making changes permanent Group logically related operations 9-20 Controlling Transactions Transaction INSERT COMMIT UPDATE INSERT DELETE Savepoint A Savepoint B ROLLBACK to Savepoint B ROLLBACK to Savepoint A ROLLBACK 9-21 Implicit Transaction Processing 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 9-22 Committing Data Make the changes. SQL> UPDATE emp 2 SET deptno = 10 3 WHERE empno = 7782; 1 row updated. Commit the changes. SQL> COMMIT; Commit complete. 9-23 State of the Data After ROLLBACK Discard all pending changes by using the ROLLBACK statement. Data changes are undone. Previous state of the data is restored. Locks on the affected rows are released. SQL> DELETE FROM 14 rows deleted. SQL> ROLLBACK; Rollback complete. employee; 9-24 Rolling Back Changes to a Marker 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-25 Summary Statement INSERT UPDATE DELETE COMMIT SAVEPOINT ROLLBACK Description Adds a new row to the table Modifies existing rows in the table Removes existing rows from the table Makes all pending changes permanent Allows a rollback to the savepoint marker Discards all pending data changes 9-26 ...
View Full Document

This note was uploaded on 06/27/2011 for the course COMPUTER S 133 taught by Professor Andrewstrauss during the Spring '11 term at Bucks Community College.

Page1 / 26

sql08_new - 9 Manipulating Data Objectives After completing...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online