This procedure will insert a new book into the book table. : Insert « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Insert

This procedure will insert a new book into the book table.

   

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> CREATE OR REPLACE PROCEDURE AddNewBook(
  2    p_ISBN IN book.ISBN%TYPE,
  3    p_Category IN book.category%TYPE := 'Oracle Server',
  4    p_Title IN book.title%TYPE,
  5    p_NumPages IN book.num_pages%TYPE,
  6    p_Price IN book.price%TYPE,
  7    p_Copyright IN book.copyright%TYPE DEFAULT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')),
  8    p_emp1 IN book.emp1%TYPE,
  9    p_emp2 IN book.emp2%TYPE := NULL,
 10    p_emp3 IN book.emp3%TYPE := NULL) AS
 11
 12  BEGIN
 13    INSERT INTO book (isbn, category, title, num_pages, price,copyright, emp1, emp2, emp3)
 14    VALUES (p_ISBN, p_Category, p_Title, p_NumPages, p_Price,p_Copyright, p_emp1, p_emp2, p_emp3);
 15  END AddNewBook;
 16  /

Procedure created.

SQL>
SQL> BEGIN
  2    AddNewBook(p_ISBN => '0000000000',
  3               p_Category => 'Database',
  4               p_Title => 'A Book',
  5               p_NumPages => 500,
  6               p_Price => 34.99,
  7               p_Copyright => 2004,
  8               p_emp1 => 1);
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table book;

Table dropped.

SQL>

   
    
    
  

Related examples in the same category