Oracle PL/SQL Tutorial/Insert Update Delete/Insert — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10: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
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.
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
INSERT into table_name [column_name] values (values)
Combine three tables with "insert into statement"
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.
Conditional INSERT Statement
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>
Including Single and Double Quotes in a Column Value
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>
Insert default value
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>
Insert double quote
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>
Insert value for specific columns
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>
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.
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>
Specifying a Null Value for a Column
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>
to_date() and insert statement
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>
To insert records into a table using a subquery:
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.
Truncate sysdate in insert statement
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>
Use trunc in insert statement
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.