• A row is inserted into a table by using INSERT command.
• Rows can insert into a
o Table
o Views Base Table.
o A partition of a Table.
o A Sub Partition of a Composite Partition Table.
o An Object Table.
o An Object Views Base Table.
Inserting of data into a table can be executed in two ways.
• Conventional INSERT.• Direct-Path INSERT.
In conventional Insert statement, Oracle reuses free space in the table into which the data in being Insert and Maintains Referential Integrity Constraints.
In Direct-Path Insert, Oracle append the insert data after existing data in the Table, the free space is not reused.
Syntax
Sql>INSERT INTO
[list of columns] VALUES(list of values);
Sql>INSERT Into Student_Det Values ( 1001, ‘THOMAS’, ‘SIEBEL’, ’30-DEC-81’, ’02-JAN-91’, 30000, ‘M’ );
• In this case the values should be provided to all the columns that exists inside the table.
• The order of values declared in the Values clause should follow the original order of the columns in the table.
• The Character and Date type data should be declared in Single Quotes.
• Number information can be applied normally.
Inserting Data into Required Columns
Sql>Insert Into Student_Det ( StudNo, Fname, Lname, DOJ ) Values ( 1002, ‘JAN’, ‘SMITH’, 12- JAN-09’ );
• In this case the Order of columns declared in insert need not be the same as that of the original table order.
• The data values in the values clause should match with that of INSERT list.
• The columns not supplied with data are filled with NULL values, Until the NOT NULL constraint is declared.
Inserting NULL values into Table
• Null represent information that is not available.
• Null Value is
o Unknown value
o Undefined value
o Not equal to 0 or blank space represented with ‘NULL’ keyword.
o Not allowed arithmetic, relational operation on Null, If performed it returns Null only.
o RDBMS must support NULL Values.
• NULL values can be inserted in two ways.
o Implicit –> Omit the columns from list oracle supplies NULL values.
o Explicit –> Specify the NULL keyword.
Sql>Insert Into Student_Det (StudNo,Fname,Lname,DOB,DOJ,Fees,Gender) Values(1003,'RAMA’,NULL,’12-JAN-81’,NULL,30000,‘M’);
Inserting special Values into TableSYSDATE FUNCTION
• SYSDATE is Pseudo-column.
• This function return the current date & time.
USER FUNCTION
This function returns the user name of the user who has logged in.
Example
Sql>CREATE TABLE Student ( Stuid Number(4), Name Clob, DOB Date, DOJ Date, Fees Number(8,2), Gender char );
Example
Sql>INSERT INTO student VALUES( 1001, user,’01-JAN-85’, sysdate, 2000, ‘M’);
Substitution Variables
• These variables are used to stored values temporarily
• The values can be stored temporarily through
o Single Ampersand(&)
o Double Ampersand(&&)
o Prompt and ACCEPT Commands
• The Single Ampersand substitution variable applies for each instance when the SQL statement is create or execute.
• The Double Ampersand substitution variable applies for all instances until that SQL statement is existing.
USING SINGLE AMPERSAND SUBSTITUTION VARIABLE
Sql> INSERT INTO dept VALUES(&Dno, ‘&Dname’, ‘&Loc’);
Sql> INSERT INTO emp(Empno, Ename, Hiredate, Deptno) VALUES(&Eno, ‘&Ename’, ‘&Hiredate’, &dno);
Note
To run command is used for executing a previous command…..
>run or >/
USING DOUBLE AMPERSAND SUBSTITUTION VARIABLE
Sql>INSERT INTO student VALUES(&Sid, ‘&Name’, ‘&Dob’, &Doj, &&fee, ‘&Gender’);
DEFINING CUSTOMIZED PROMPTS
Sql>ACCEPT Deptno prompt ‘Please Enter the deptnumber:’
Sql>ACCEPT Dname prompt ‘Please Enter the Deptname:’
Sql>ACCEPT Location prompt ‘Please Enter the Location:’
Sql>INSERT INTO dept VALUES(&Deptno, &Dname, &Location);
Default method Vs Multiple inheritance
Interface Default methods
Anonymous inner class Vs Lambda Expression"
Functional Interface
ATM Java Program
How CuncurrentHashMap Works Internally
Lambda Expression
Thanks for reading. If you like this post please follow us for more updates about technology related updates.
No comments:
Post a Comment