 |
Radzishevskyi Sergey blog수업내용이 27Day와 별반 차이가 없어 CRUD 예제로 대체. |
CRUD(create, read, update, delete)
Create (insert)
Insert a single row into a table:
INSERT INTO dept VALUES (10, 'pre-sales', 'New York');
Insert rows from one table into another table:
INSERT INTO emp2 SELECT * FROM emp;
CREATE TABLE emp3 AS SELECT * FROM emp;
Insert multiple rows into a table with a single statement:
INSERT ALL
INTO dept (deptno, dname, loc) VALUES (11, 'Sales', 'New York')
INTO dept (deptno, dname, loc) VALUES (12, 'Pre-sales', 'Dallas')
SELECT 1 FROM dual;
INSERT INTO dept
SELECT 13, 'Sales', 'New York' FROM dual UNION
SELECT 14, 'Pre-sales', 'Dallas' FROM dual;
Insert multiple rows into different tables with a single statement:
INSERT ALL
WHEN type=1 THEN INTO tab1 VALUES (myseq.NEXTVAL, val)
WHEN type=2 THEN INTO tab2 VALUES (myseq.NEXTVAL, val)
WHEN type IN (3,4,5) THEN INTO tab3 VALUES (myseq.NEXTVAL, val)
ELSE INTO tab4 VALUES (myseq.NEXTVAL, val)
SELECT type, val FROM source_tab;
| cs |
Retrieve (select)
Select all columns (* = all columns) and all rows from the emp table:
SELECT * FROM emp;
Select the salary column for employee number 7788:
SELECT sal
FROM emp
WHERE empno = 7788;
Sort rows and return them in order:
SELECT ename, sal FROM emp ORDER BY sal;
Group rows together:
SELECT deptno,
COUNT(*) "Employees in department",
SUM(sal) "Total salaries for department",
AVG(sal) "Avarage salary for department"
FROM emp GROUP BY deptno;
| cs |
Update (udpate)
Give employees in department 10 an 10% raise:
UPDATE emp SET sal = sal*1.1 WHERE deptno = 10;
Give employee number 7844 a $100 commission:
UPDATE emp SET comm = 100 WHERE empno = 7844;
| cs |
Delete (delete)
Remove selective rows from a table - in this case only one:
DELETE FROM emp WHERE empno = 7844;
COMMIT;
Remove all rows from a table (also compare to TRUNCATE):
DELETE FROM emp;
ROLLBACK;
Remove records in a specific partition:
DELETE FROM emp_part PARTITION p1;
Remove rows from a remote database (via a database link):
DELETE FROM emp@remote_db;
Remove rows from a SELECT statement:
DELETE FROM (SELECT * FROM emp WHERE deptno = 10);
Delete with RETURNING clause (can only be used from PL/SQL):
DECLARE
v_rowid urowid;
BEGIN
DELETE FROM emp WHERE empno = 7844
RETURNING rowid INTO v_rowid;
| cs |
0 개의 댓글:
댓글 쓰기