|
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>
|