Wednesday, February 10, 2010

Oralce: Whats new in INSERT statment

RECORD INSERT

DECLARE
emprec emp%ROWTYPE;
BEGIN
--Add the new row to emprec
emprec.empno = 100;
emprec.name = 'James';
emprec.deptno = 20 ;
emprec.sal = 5000 ;

--INSERT the row into emp table
INSERT INTO emp
VALUES emprec ;
COMMIT;

END;

INSERT ALL
Using Insert All you can insert into multiple tables unconditionally with a single SQL statement.

Syntax:
INSERT ALL
INTO (table_name) VALUES (column_name_list)
INTO (table_name) VALUES (column_name_list)
(SELECT Statement);

Example:
INSERT ALL
INTO emp_10(empno, ename, deptno, sal)
VALUES (empno, ename, deptno, sal)
INTO emp_10_BONUS(empno, bonus)
VALUES (empno, sal+(sal* 10/100))
SELECT empno, ename, deptno, sal
FROM emp
WHERE dept_no = 10 ;

INSERT ALL WHEN
Using INSERT ALL WHEN, you can insert into multiple tables “conditionally” with a single SQL statement

Syntax:
INSERT ALL
WHEN (condition) THEN
INTO table_name(column_list)
VALUES (values_list)
WHEN (condition) THEN
INTO table_name(column_list)
VALUES (values_list)
ELSE
INTO table_name()
VALUES (values_list)
SELECT column_list FROM table_name;


Example:
INSERT ALL
WHEN (deptno = 10) THEN
INTO emp_10(empno, ename, deptno, sal)
VALUES (empno, ename, deptno, sal)
WHEN (deptno = 20) THEN
INTO emp_20(empno, ename, deptno, sal)
VALUES (empno, ename, deptno, sal)
SELECT empno, ename, deptno, sal
FROM emp
WHERE dept_no in (10,20) ;

INSERT With Returning Row

Syntax:
INSERT INTO (table_name)(column_list)
VALUES (values_list)
RETURNING (value_name)
INTO (variable_name);

Example:
DECLARE
vempno emp.empno%TYPE;
BEGIN
INSERT INTO emp(empno, ename,deptno, sal)
VALUES(1,'james',10, 5000)
RETURNING empno
INTO vempno ;
END;

No comments:

Post a Comment