Oracle PL/SQL Tutorial/Insert Update Delete/Insert — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 13:07, 26 мая 2010
Содержание
- 1 Adding Multiple Rows to a Table
- 2 Adding Rows Using the INSERT Statement
- 3 Combine three tables with "insert into statement"
- 4 Conditional INSERT Statement
- 5 Including Single and Double Quotes in a Column Value
- 6 Insert default value
- 7 Insert double quote
- 8 Insert value for specific columns
- 9 Omitting the Column List
- 10 Specifying a Null Value for a Column
- 11 to_date() and insert statement
- 12 To insert records into a table using a subquery:
- 13 Truncate sysdate in insert statement
- 14 Use trunc in insert statement
Adding Multiple Rows to a Table
<source lang="sql">
SQL> SQL> CREATE TABLE Student (
2 StudentID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL);
Table created. SQL> SQL> INSERT INTO Student (StudentID,Name) VALUES (1,"Tom"); 1 row created. SQL> INSERT INTO Student (StudentID,Name) VALUES (2,"Jack"); 1 row created. SQL> INSERT INTO Student (StudentID,Name) VALUES (3,"Mary"); 1 row created. SQL> INSERT INTO Student (StudentID,Name) VALUES (4,"Bill"); 1 row created. SQL> INSERT INTO Student (StudentID,Name) VALUES (5,"Cat"); 1 row created. SQL> SQL> SQL> CREATE TABLE Professor (
2 ProfessorID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL);
Table created. SQL> SQL> SQL> INSERT INTO Professor (ProfessorID, Name)
2 SELECT StudentID + 7, Name 3 FROM Student;
5 rows created. SQL> SQL> drop table Professor; Table dropped. SQL> drop table Student; Table dropped.</source>
Adding Rows Using the INSERT Statement
INSERT statement adds rows to a table.
You can specify the following information in an INSERT statement:
- The table into which the row is to be inserted.
- A list of columns for which you want to specify column values.
- A list of values to store in the specified columns.
- When adding a row, you typically need to supply a value for the primary key and all other NOT NULL columns.
- You don"t have to specify values for NULL columns if you don"t want to, by default they will be set to null.
- You can find out which columns are defined as NOT NULL using the SQL*Plus DESCRIBE command.
The Syntax for the INSERT Command
<source lang="sql">
INSERT into table_name [column_name] values (values)</source>
Combine three tables with "insert into statement"
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE person (
2 person_code VARCHAR2(3) PRIMARY KEY, 3 first_name VARCHAR2(15), 4 last_name VARCHAR2(20), 5 hire_date DATE 6 );
Table created. SQL> SQL> SQL> INSERT INTO person VALUES ("CA", "Chase", "At", "01-FEB-02"); 1 row created. SQL> INSERT INTO person VALUES ("GA", "Gary", "Talor", "15-FEB-02"); 1 row created. SQL> INSERT INTO person VALUES ("BB", "Bob", "Bark", "28-FEB-02"); 1 row created. SQL> INSERT INTO person VALUES ("LB", "Laren", "Baby", "01-MAR-02"); 1 row created. SQL> INSERT INTO person VALUES ("LN", "Linda", "Norman", "01-JUN-03"); 1 row created. SQL> SQL> CREATE TABLE product (
2 product_name VARCHAR2(25) PRIMARY KEY, 3 product_price NUMBER(4,2), 4 quantity_on_hand NUMBER(5,0), 5 last_stock_date DATE 6 );
Table created. SQL> SQL> CREATE TABLE product_order (
2 product_name VARCHAR2(25), 3 salesperson VARCHAR2(3), 4 order_date DATE, 5 quantity NUMBER(4,2) 6 );
Table created. SQL> SQL> SQL> INSERT INTO product_order VALUES ("Product 1", "CA", "14-JUL-03", 1); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 2", "BB", "14-JUL-03", 75); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 3", "GA", "14-JUL-03", 2); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 4", "GA", "15-JUL-03", 8); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 5", "LB", "15-JUL-03", 20); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 6", "CA", "16-JUL-03", 5); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 7", "CA", "17-JUL-03", 1); 1 row created. SQL> SQL> SQL> INSERT INTO product VALUES ("Product 1", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 3", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 4", 25, 10000, null); 1 row created. SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04"); 1 row created. SQL> INSERT INTO product VALUES ("Product 6", 45, 1, TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M.")); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE product_order_log (
2 order_date DATE, 3 product_name VARCHAR2(25), 4 product_price NUMBER(4,2), 5 quantity NUMBER(4,2), 6 sales_first_name VARCHAR2(15), 7 sales_last_name VARCHAR2(20) 8 );
Table created. SQL> SQL> INSERT INTO product_order_log (
2 SELECT purc.order_date, 3 prod.product_name, 4 prod.product_price, 5 purc.quantity, 6 pers.first_name, 7 pers.last_name 8 FROM product prod, 9 person pers, 10 product_order purc 11 WHERE prod.product_name = purc.product_name 12 AND 13 pers.person_code = purc.salesperson 14 );
6 rows created. SQL> SQL> SELECT * FROM product_order_log; ORDER_DAT PRODUCT_NAME PRODUCT_PRICE QUANTITY SALES_FIRST_NAM SALES_LAST_NAME
------------------------- ------------- ---------- --------------- --------------------
14-JUL-03 Product 1 99 1 Chase At 14-JUL-03 Product 2 75 75 Bob Bark 14-JUL-03 Product 3 50 2 Gary Talor 15-JUL-03 Product 4 25 8 Gary Talor 15-JUL-03 Product 5 9.95 20 Laren Baby 16-JUL-03 Product 6 45 5 Chase At 6 rows selected. SQL> SQL> drop table product_order_log; Table dropped. SQL> drop table person; Table dropped. SQL> drop table product; Table dropped. SQL> drop table product_order; Table dropped.</source>
Conditional INSERT Statement
<source lang="sql">
SQL> SQL> CREATE TABLE BookBorrow(
2 member CHAR(5) 3 ,book CHAR(5) 4 ,dueDate DATE 5 );
Table created. SQL> INSERT INTO BookBorrow VALUES ("bb01","bk002",DATE "2005-03-22"); 1 row created. SQL> INSERT INTO BookBorrow VALUES ("bb01","bk002",DATE "2005-09-21"); 1 row created. SQL> INSERT INTO BookBorrow VALUES ("bb02","bk005",DATE "2005-09-21"); 1 row created. SQL> INSERT INTO BookBorrow(member,book,dueDate)VALUES ("bb01","bk002",CURRENT_DATE + INTERVAL "14" DAY ); 1 row created. SQL> INSERT INTO BookBorrow(member,book,dueDate)VALUES ("bb02","bk005",CURRENT_DATE - INTERVAL "1" DAY ); 1 row created. SQL> SQL> SQL> CREATE TABLE BookReturn(
2 book CHAR(5) 3 ,member CHAR(5) 4 ,returnDate DATE 5 ,fine DECIMAL(10,2) 6 );
Table created. SQL> SQL> CREATE TABLE room(
2 who VARCHAR(20) 3 ,wht VARCHAR(20) NOT NULL 4 ,whn DATE NOT NULL 5 ,PRIMARY KEY (whn,wht) 6 );
Table created. SQL> SQL> INSERT INTO BookReturn(book,member,returnDate,fine)
2 VALUES ("bb01","bk002",CURRENT_DATE,(SELECT 0.20 FROM BookBorrow
3 WHERE member="bb01" AND book="bk002"
4 HAVING MAX(dueDate) < CURRENT_DATE));
1 row created. SQL> SQL> INSERT INTO BookReturn(book,member,returnDate,fine)
2 VALUES ("bb02","bk005",CURRENT_DATE,(SELECT 0.20 FROM BookBorrow
3 WHERE member="bb02" AND book="bk005"
4 HAVING MAX(dueDate) < CURRENT_DATE));
1 row created. SQL> SQL> SELECT * FROM BookReturn;
BOOK MEMBE RETURNDAT FINE
----- --------- ----------
bb01 bk002 26-OCT-09 bb02 bk005 26-OCT-09 .2
2 rows selected. SQL> SQL> INSERT INTO room(whn,wht,who)
2 SELECT DATE "2006-07-13","Room 1","Prof. Plum" 3 FROM dual 4 WHERE NOT EXISTS (SELECT who FROM room 5 WHERE whn = DATE "2006-07-13" 6 AND wht="Room 1");
1 row created. SQL> SQL> INSERT INTO room(whn,wht,who)
2 SELECT DATE "2006-07-13","Ballroom","Miss. Scarlet" 3 FROM dual 4 WHERE NOT EXISTS (SELECT who FROM room 5 WHERE whn = DATE "2006-07-13" 6 AND wht="Ballroom");
1 row created. SQL> SQL> SELECT * FROM room;
WHO WHT WHN
-------------------- ---------
Prof. Plum Room 1 13-JUL-06 Miss. Scarlet Ballroom 13-JUL-06 2 rows selected. SQL> SQL> DROP TABLE BookBorrow; Table dropped. SQL> DROP TABLE BookReturn; Table dropped. SQL> DROP TABLE room; Table dropped. SQL> SQL> SQL></source>
Including Single and Double Quotes in a Column Value
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name)
2 values ("01","Jason")
3 /
1 row created. SQL> insert into Employee(ID, First_Name)
2 values("08","O""James")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason 08 O"James SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
Insert default value
<source lang="sql">
SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> insert into employees(empno,ename,init,bdate,msal,deptno)
2 values (7001,"ZOMBIE","ZZ",trunc(sysdate),0, DEFAULT);
1 row created. SQL> SQL> select * from employees where empno = 7001;
EMPNO ENAME INIT JOB MGR BDATE MSAL COMM DEPTNO
-------- ----- -------- ---------- --------- ---------- ---------- ----------
7001 ZOMBIE ZZ 24-JUL-08 0
SQL> SQL> drop table employees; Table dropped. SQL></source>
Insert double quote
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name)
2 values ("01","Jason")
3 /
1 row created. SQL> insert into Employee(ID, First_Name)
2 values("08","J"a"mes")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason 08 J"a"mes SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Insert value for specific columns
<source lang="sql">
SQL> SQL> CREATE TABLE myTable (
2 name VARCHAR2(25) NOT NULL, 3 price NUMBER(4,2) NOT NULL, 4 start_date DATE);
Table created. SQL> SQL> SQL> SQL> INSERT INTO myTable (name, price) VALUES ("Product Name 2", 2.5); 1 row created. SQL> INSERT INTO myTable (name, price) VALUES ("Product Name 3", 50.75); 1 row created. SQL> INSERT INTO myTable (price, name) VALUES (99.99, "Product Name 4"); 1 row created. SQL> SQL> SQL> SELECT * FROM myTable;
NAME PRICE START_DAT
---------- ---------
Product Name 2 2.5 Product Name 3 50.75 Product Name 4 99.99 3 rows selected. SQL> drop table myTable; Table dropped. SQL> SQL> SQL></source>
Omitting the Column List
When omitting the column list, the order of the values must match the order listed in the output from the DESCRIBE command.
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Specifying a Null Value for a Column
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02",NULL, "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
to_date() and insert statement
<source lang="sql">
SQL> SQL> create sequence s_emp_id
2 start with 1000;
Sequence created. SQL> SQL> SQL> create table gift(
2 gift_id integer primary key 3 ,emp_id integer 4 ,register_date date not null 5 ,total_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,message varchar2(100) 12 );
Table created. SQL> SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created. SQL> SQL> SQL> insert into gift (gift_id, emp_id, register_date, payment)
2 values (s_emp_id.nextval, s_emp_id.currval,
3 to_date("01/15/2000", "mm/dd/yyyy"), "VS");
1 row created. SQL> SQL> SQL> drop table gift; Table dropped. SQL> SQL> drop sequence s_emp_id; Sequence dropped. SQL></source>
To insert records into a table using a subquery:
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created.
SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> INSERT INTO dept
2 SELECT * FROM dept;
4 rows created. SQL> SQL> drop table dept; Table dropped.</source>
Truncate sysdate in insert statement
<source lang="sql">
SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> insert into employees(empno,ename,init,bdate,msal,deptno)
2 values (7001,"ZOMBIE","ZZ",trunc(sysdate),0, DEFAULT);
1 row created. SQL> SQL> select * from employees where empno = 7001;
EMPNO ENAME INIT JOB MGR BDATE MSAL COMM DEPTNO
-------- ----- -------- ---------- --------- ---------- ---------- ----------
7001 ZOMBIE ZZ 24-JUL-08 0
SQL> SQL> drop table employees; Table dropped. SQL> SQL></source>
Use trunc in insert statement
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) , 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> INSERT INTO employee (start_date) VALUES (trunc(sysdate)); 1 row created. SQL> SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
06-JUN-07
9 rows selected. SQL> SQL> SQL> SQL> drop table Employee
2 /
Table dropped.</source>
