Example Using the INSERT Statement
This section consists of an example using the INSERT
statement.
Using the INSERT Statement
TimesTen supports the TimesTen DML statements INSERT
, UPDATE
, DELETE
, and MERGE
.
This example uses the AS SELECT
query clause to create table emp_copy
, sets AUTOCOMMIT
off, creates a sequence to increment employee_id
, and uses the INSERT
statement in PL/SQL to insert a row of data in table emp_copy
.
Command> CREATE TABLE emp_copy AS SELECT * FROM employees;
107 rows inserted.
Command> SET AUTOCOMMIT OFF;
Command> CREATE SEQUENCE emp_copy_seq
START WITH 207
INCREMENT BY 1;
Command> BEGIN
INSERT INTO emp_copy
(employee_id, first_name, last_name, email, hire_date, job_id,
salary)
VALUES (emp_copy_seq.NEXTVAL, 'Parker', 'Cores', 'PCORES', SYSDATE,
'AD_ASST', 4000);
END;
/
PL/SQL procedure successfully completed.
Continuing, the example confirms the row was inserted, then rolls back the transaction.
Command> SELECT * FROM EMP_COPY WHERE first_name = 'Parker';
< 207, Parker, Cores, PCORES, <NULL>, 2008-07-19 21:49:55, AD_ASST, 4000,
<NULL>, <NULL>, <NULL> >
1 row found.
Command> ROLLBACK;
Command> SELECT * FROM emp_copy WHERE first_name = 'Parker';
0 rows found.
Now INSERT
is executed again, then the transaction is rolled back in PL/SQL. Finally, the example verifies that TimesTen did not insert the row.
Command> BEGIN
INSERT INTO emp_copy
(employee_id, first_name, last_name, email, hire_date, job_id,
salary)
VALUES (emp_copy_seq.NEXTVAL, 'Parker', 'Cores', 'PCORES', SYSDATE,
'AD_ASST',4000);
ROLLBACK;
END;
/
PL/SQL procedure successfully completed.
Command> SELECT * FROM emp_copy WHERE first_name = 'Parker';
0 rows found.