Oracle PL/SQL/PL SQL/Data Insert — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 09:59, 26 мая 2010

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>