Oracle ---DML

上一篇 / 下一篇  2010-03-29 21:04:25 / 个人分类:Oracle

1.insert
SQL> INSERT INTO        emp (empno, ename, job,
  2                     mgr, hiredate, sal, comm,
  3                     deptno)
  4  VALUES             (7196, 'GREEN', 'SALESMAN',
  5                     7782, SYSDATE, 2000, NULL,
  6                     10);
SQL> INSERT INTO emp
  2  VALUES      (2296,'AROMANO','SALESMAN',7782,
  3                 TO_DATE('FEB 3,97', 'MON DD, YY'),
  4                 1300, NULL, 10);
 
  2.Copying Rows
from Another Table
  SQL> INSERT INTO managers(id, name, salary, hiredate)
  2                      SELECT  empno, ename, sal, hiredate
  3                      FROM   emp
  4                      WHERE   job = 'MANAGER';
 
3.update
UPDATE          table
SET             column = value [, column = value]
[WHERE           condition];
SQL> UPDATE  emp
  2  SET     (job, deptno) =
  3                               (SELECT job, deptno
  4                          FROM    emp
  5                          WHERE   empno = 7499)
  6  WHERE   empno = 7698;
 
SQL>    UPDATE  employee
  2     SET     deptno =  (SELECT       deptno
  3                     FROM    emp     
  4                     WHERE   empno = 7788)
  5     WHERE   job    =  (SELECT       job
  6                     FROM    emp
  7                     WHERE   empno = 7788);
 
  4.delete
  SQL> DELETE FROM      department
  2  WHERE               dname = 'DEVELOPMENT';
  SQL> DELETE FROM      department;
  SQL> DELETE FROM      employee
  2  WHERE              deptno =
  3                            (SELECT   deptno
  4                              FROM     dept
  5                              WHERE    dname ='SALES');
6 rows deleted.
Deleting Rows:
Integrity Constraint Error:
If you attempt to delete a record with a value that is tied to an integrity constraint, you will experience an error.
The example above tries to delete department number 10 from the DEPT table, but it results in an error because department number is used as a foreign key in the EMP table. If the parent record you attempt to delete has child records, then you receive the child record found violation ORA-02292.
 
 
5.rollback and commit: if commit, data are saved in bd, can't rollback
SQL> UPDATE...
SQL> SAVEPOINT update_done;
Savepoint created.
SQL> INSERT...
SQL> ROLLBACK TO update_done;
Rollback complete.

TAG:

 

评分:0

我来说两句

Open Toolbar