메뉴 건너뛰기

검색창 닫기

tnt_db

Oracle Basic Delete Statements

구퍼 2008.09.17 16:02 조회 수 : 4674

Basic Delete Statements

Delete All Rows		 DELETE <table_name>
or
DELETE FROM <table_name>;
CREATE TABLE t AS
SELECT *
FROM all_tables;

SELECT COUNT(*)
FROM t;

DELETE FROM t;

COMMIT;

SELECT COUNT(*)
FROM t;

Delete Selective Rows		 DELETE FROM <table_name>
WHERE <condition>;
CREATE TABLE t AS
SELECT *
FROM all_tables;

SELECT COUNT(*)
FROM t;

DELETE FROM t
WHERE table_name LIKE '%MAP';

COMMIT;

SELECT COUNT(*)
FROM t;

Delete From A SELECT Statement		 DELETE FROM (<SELECT Statement>);
CREATE TABLE t AS
SELECT *
FROM all_tables;

SELECT COUNT(*)
FROM t;

DELETE FROM (SELECT * FROM t WHERE table_name LIKE '%MAP');

SELECT COUNT(*)
FROM t;

Delete With Returning Clause		 DELETE FROM (<SELECT Statement>);
CREATE TABLE t AS
SELECT *
FROM all_tables;

set serveroutput on

DECLARE
 r  urowid;
BEGIN
  DELETE FROM t
  WHERE rownum = 1
  RETURNING rowid INTO r;

  dbms_output.put_line(r);
END;
/
Delete Restricted To A Partition DELETE FROM <table_name>
PARTITION <partition_name>;
DELETE FROM sales PARTITION (q1_2001_invoices);
Delete From A Remote Database DELETE FROM <table_name>@<database_link>
DELETE FROM t@remote_db;
 
Related Topics
Conditions
Database Link
Error Logging
Insert
Joins
Nested Tables
Select
Types
Update
Where Clause
 
 
이 게시물을
이 글의 추천인 목록 수정 삭제 목록
쓰기 태그
위로