Oracle PL/SQL/PL SQL/Data Insert — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 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
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>
Bulk insert with insert ... select
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.
Data insert in a procedure
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;
Hard code value and insert
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.
Insert 100000 rows into a table with for loop
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>
Insert a specified number of suppliers and products per supplier
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.
Insert data in procedure
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>
Insert value passed in by parameter to a table
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>
Insert value to a table after calculation
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.
Insert value to product and productcategory with stored procedure
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;
Insert value to table with for loop
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>
Loop through all to do a bulk insert
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>
This script demonstrates returning clause
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>
Use in parameter to pass value and insert value to a table
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>