Oracle PL/SQL/PL SQL/Data Insert
Содержание
- 1 An anonymous block program to write the record to a row
- 2 Bulk insert with insert ... select
- 3 Data insert in a procedure
- 4 Hard code value and insert
- 5 Insert 100000 rows into a table with for loop
- 6 Insert a specified number of suppliers and products per supplier
- 7 Insert data in procedure
- 8 Insert value passed in by parameter to a table
- 9 Insert value to a table after calculation
- 10 Insert value to product and productcategory with stored procedure
- 11 Insert value to table with for loop
- 12 Loop through all to do a bulk insert
- 13 This script demonstrates returning clause
- 14 Use in parameter to pass value and insert value to a table
An anonymous block program to write the record to a row
<source lang="sql">
SQL> SQL> CREATE TABLE emp
2 (emp_id INTEGER NOT NULL 3 ,fname VARCHAR2(30 CHAR) NOT NULL 4 ,mid_name VARCHAR2(1 CHAR) 5 ,lname VARCHAR2(30 CHAR) NOT NULL 6 ,CONSTRAINT emp_pk PRIMARY KEY (emp_id));
Table created. SQL> SQL> SQL> SQL> DECLARE
2 3 4 emp1 emp%ROWTYPE; 5 6 BEGIN 7 8 9 emp1.emp_id := 1; 10 emp1.fname := "John"; 11 emp1.mid_name := "D"; 12 emp1.lname := "R"; 13 14 15 INSERT INTO emp VALUES (emp1.emp_id ,emp1.fname ,emp1.mid_name,emp1.lname); 16 17 18 COMMIT; 19 20 END; 21 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Bulk insert with insert ... select
<source lang="sql">
SQL> SQL> create table EMP (
2 EMPNO NUMBER(8), 3 ENAME VARCHAR2(20), 4 HIREDATE DATE, 5 SAL NUMBER(7,2), 6 DEPTNO NUMBER(6) );
Table created. SQL> SQL> SQL> create table DEPT (
2 DEPTNO NUMBER(6), 3 DNAME VARCHAR2(20) );
Table created. SQL> SQL> SQL> alter table EMP add constraint EMP_PK primary key (EMPNO); Table altered. SQL> SQL> SQL> alter table DEPT add constraint DEPT_PK primary key (DEPTNO); Table altered. SQL> SQL> create table myTable ( x varchar2(10)); Table created. SQL> SQL> begin
2 for i in 1 .. 200 loop
3 insert into myTable values ("x");
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> SQL> insert into EMP select rownum, "Name"||rownum, sysdate+rownum/100, dbms_random.value(7500,10000), dbms_random.value(1,10) from myTable
2 where rownum <= 500;
200 rows created. SQL> SQL> insert into DEPT select rownum, "Dept"||rownum from myTable where rownum <= 10; 10 rows created. SQL> SQL> drop table EMP; Table dropped. SQL> drop table DEPT; Table dropped. SQL> drop table myTable; Table dropped.
</source>
Data insert in a procedure
<source lang="sql">
SQL> SQL> SQL> create table t(
2 n number 3 ) 4 /
Table created. SQL> SQL> create or replace
2 procedure insert_into_t( p_parm in number ) is 3 begin 4 insert into t values ( p_parm ); 5 end insert_into_t; 6 /
Procedure created. SQL> SQL> SQL> select * from t; no rows selected SQL> SQL> exec insert_into_t( p_parm => 100 ); PL/SQL procedure successfully completed. SQL> SQL> select * from t;
N
100
SQL> SQL> SQL> drop table t;
</source>
Hard code value and insert
<source lang="sql">
SQL> CREATE TABLE book (
2 isbn CHAR(10) PRIMARY KEY, 3 category VARCHAR2(20), 4 title VARCHAR2(100), 5 num_pages NUMBER, 6 price NUMBER, 7 copyright NUMBER(4), 8 emp1 NUMBER, 9 emp2 NUMBER, 10 emp3 NUMBER 11 );
Table created. SQL> SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("1", "Database", "Oracle", 563, 39.99, 2009, 1, 2, 3);
1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("2", "Database", "MySQL", 765, 44.99, 2009, 4, 5);
1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created. SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> DECLARE
2 v_isbn book.ISBN%TYPE := "12345678"; 3 v_category book.CATEGORY%TYPE := "Oracle Server"; 4 v_title book.TITLE%TYPE := "Oracle Information Retrieval"; 5 BEGIN 6 INSERT INTO book (ISBN,CATEGORY,TITLE,NUM_PAGES,PRICE,COPYRIGHT,emp1) VALUES (v_isbn, v_category, v_title, 4, 3.5,2005, 44); 7 COMMIT; 8 EXCEPTION 9 WHEN OTHERS 10 THEN 11 DBMS_OUTPUT.PUT_LINE(SQLERRM); 12 ROLLBACK; 13 END; 14 /
PL/SQL procedure successfully completed. SQL> SQL> drop table book; Table dropped.
</source>
Insert 100000 rows into a table with for loop
<source lang="sql">
SQL> create table t ( x int, y char(50) ); Table created. SQL> SQL> begin
2 for i in 1 .. 100000 3 loop 4 insert into t values ( i, "x" ); 5 end loop; 6 commit; 7 end; 8 /
PL/SQL procedure successfully completed. SQL> SQL> select count(*) from t;
COUNT(*)
100000
SQL> SQL> drop table t; Table dropped. SQL>
</source>
Insert a specified number of suppliers and products per supplier
<source lang="sql">
SQL> SQL> create table supplier(
2 supplier_no integer primary key 3 ,supplier_name varchar2(50) 4 ,address varchar(30) 5 ,city varchar(20) 6 ,state varchar2(2) 7 ,area_code varchar2(3) 8 ,phone varchar2(8) 9 );
Table created. SQL> -- supplier table inserts SQL> insert into supplier(supplier_no, supplier_name)values(10,"ABC Gift Supply Co."); 1 row created. SQL> insert into supplier(supplier_no, supplier_name)values(12,"Z Gift Supply Co."); 1 row created. SQL> insert into supplier(supplier_no, supplier_name)values(13,"XYZ Gift Supply Co."); 1 row created. SQL> insert into supplier(supplier_no, supplier_name)values(14,"R and R Gift Supply Co."); 1 row created. SQL> insert into supplier(supplier_no, supplier_name)values(17,"Z Gift Supply Co."); 1 row created. SQL> SQL> SQL> ACCEPT p_add PROMPT "Enter the number of suppliers to add " Enter the number of suppliers to add ACCEPT p_prod PROMPT "Enter the number of products to add per supplier " SQL> SQL> declare
2 v_ctr number := &p_add; 3 v_ctr number := &p_prod; 4 v_loop number := 1; 5 6 v_curr_supplier supplier.supplier_no%TYPE ; 7 8 begin 9 10 WHILE v_loop <= v_ctr LOOP 11 INSERT INTO supplier (SUPPLIER_NO, SUPPLIER_NAME) 12 VALUES (supplier_seq.NEXTVAL, "Acme Supply #"||supplier_seq.CURRVAL); 13 14 SELECT supplier_seq.CURRVAL INTO v_curr_supplier FROM dual ; 15 16 p_add_prod(v_curr_supplier, v_ctr); 17 18 v_loop := v_loop + 1 ; 19 END LOOP; 20 COMMIT; 21 end; 22 /
SQL> drop table supplier; Table dropped.
</source>
Insert data in procedure
<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","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> SQL> -- handle dup value on index SQL> SQL> declare
2 myEmployee employee%rowtype;
3 begin
4 myEmployee.id := 100;
5 myEmployee.first_name := "Tech";
6 insert into employee ( id, first_name )
7 values( myEmployee.id, myEmployee.first_name );
8 exception
9 when DUP_VAL_ON_INDEX then
10 dbms_output.put_line("DUP_VAL_ON_INDEX exception.");
11 dbms_output.put_line("This is where we""d write out own handler code.");
12 end;
13 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> select * from employee; 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 100 Tech 9 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL> SQL> SQL> SQL>
</source>
Insert value passed in by parameter to a table
<source lang="sql">
SQL> CREATE TABLE myTable2 (
2 a INTEGER, 3 b CHAR(10) 4 );
Table created. SQL> SQL> CREATE or replace PROCEDURE addtuple1(i IN NUMBER) AS
2 BEGIN 3 INSERT INTO myTable2 VALUES(i, "xxx"); 4 END addtuple1; 5 /
Procedure created. SQL> SQL> drop table myTable2; Table dropped. SQL>
</source>
Insert value to a table after calculation
<source lang="sql">
SQL> create table sales(
2 gift_id number(5), 3 order_total number(11,2) 4 );
Table created. SQL> SQL> declare
2 v_order_total sales.order_total%type; 3 begin 4 for i in 1..100 loop 5 v_order_total := i * 1.10; 6 insert into sales values (i, v_order_total); 7 end loop; 8 end; 9 /
PL/SQL procedure successfully completed. SQL> SQL> drop table sales; Table dropped.
</source>
Insert value to product and productcategory with stored procedure
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL, 4 Description VARCHAR(1000) NOT NULL, 5 Price NUMBER NULL, 6 ImagePath VARCHAR(50) NULL, 7 soldout NUMBER(1,0) NULL, 8 Promotion NUMBER(1,0) NULL);
SQL> SQL> CREATE SEQUENCE ProductIDSeq; SQL> SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product 3 FOR EACH ROW 4 BEGIN 5 SELECT ProductIDSeq.NEXTVAL 6 INTO :NEW.ProductID FROM DUAL; 7 END; 8 /
SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Ruler", "Long",14.99, "ruler.jpg", 0, 0);
SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Keyboard", "keyboard",3.75, "keyboard.jpg", 0, 0);
SQL> SQL> SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL, 3 CategoryID INT NOT NULL, 4 PRIMARY KEY (ProductID, CategoryID) 5 );
SQL> SQL> SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5); SQL> SQL> CREATE PROCEDURE CreateProductToCategory
2 (CategoryID integer, 3 ProductName IN varchar2, 4 ProductDescription IN varchar2) 5 AS 6 BEGIN 7 INSERT INTO Product (Name, Description) 8 VALUES (ProductName, ProductDescription); 9 10 INSERT INTO ProductCategory (ProductID, CategoryID) 11 VALUES (ProductID.CurrVal, CategoryID); 12 END; 13 /
SQL> SQL> SQL> drop sequence ProductIDSeq; SQL> drop table product; SQL> drop table ProductCategory;
</source>
Insert value to table with for loop
<source lang="sql">
SQL> CREATE TABLE myTable(
2 e INTEGER, 3 f INTEGER 4 );
Table created. SQL> SQL> begin
2 for i in 1..1000 3 loop 4 insert into myTable values(i, 56); 5 end loop; 6 end; 7 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
Loop through all to do a bulk insert
<source lang="sql">
SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> CREATE TABLE myTable
2 (id NUMBER NOT NULL 3 ,CONSTRAINT id_pk PRIMARY KEY (id));
Table created. SQL> SQL> SQL> DECLARE
2 3 TYPE number_table IS TABLE OF myTable.id%TYPE INDEX BY BINARY_INTEGER; 4 5 6 number_list NUMBER_TABLE; 7 8 BEGIN 9 10 11 FOR i IN 1..10000 LOOP 12 13 number_list(i) := i; 14 15 END LOOP; 16 17 18 FORALL i IN 1..number_list.COUNT 19 INSERT INTO myTable VALUES (number_list(i)); 20 21 COMMIT; 22 23 END; 24 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
This script demonstrates returning clause
<source lang="sql">
SQL> CREATE TABLE book (
2 id NUMBER (10) PRIMARY KEY, 3 isbn CHAR(10 CHAR), 4 description CLOB, 5 descr NCLOB, 6 book_cover BLOB, 7 chapter_title VARCHAR2(30 CHAR), 8 chapter BFILE 9 ) ;
Table created. SQL> SQL> SQL> SQL> SET SERVEROUTPUT ON LONG 64000 SQL> SQL> DECLARE
2 v_clob CLOB;
3 BEGIN
4 INSERT INTO book (id,isbn,description,descr,book_cover,chapter)VALUES (1,"3", "A",EMPTY_CLOB(),EMPTY_BLOB(),BFILENAME("book_LOC", "3.jpg"))RETURNING description INTO v_clob;
5 COMMIT;
6
7 DBMS_OUTPUT.PUT_LINE(v_clob);
8 EXCEPTION
9 WHEN OTHERS
10 THEN
11 DBMS_OUTPUT.PUT_LINE(SQLERRM);
12 END;
13 /
A PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> drop table book; Table dropped. SQL> SQL> SQL>
</source>
Use in parameter to pass value and insert value to a table
<source lang="sql">
SQL> SQL> create table t(
2 n number 3 ) 4 /
Table created. SQL> SQL> create or replace
2 procedure insert_into_t( p_parm1 in number, p_parm2 in number ) is 3 begin 4 insert into t values ( p_parm1 ); 5 insert into t values ( p_parm2 ); 6 end insert_into_t; 7 /
Procedure created. SQL> SQL> SQL> exec insert_into_t( p_parm1 => 101, p_parm2 => 102 ); PL/SQL procedure successfully completed. SQL> SQL> select * from t;
N
101
102
SQL> SQL> SQL> drop table t; Table dropped. SQL> SQL>
</source>
