Demonstrates default parameters. : Default Parameter Value « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Default Parameter Value

Demonstrates default parameters.

    

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('0', 'Database', 'A Book',5, 34.99, 2004, 1);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table book;

Table dropped.

   
    
    
    
  

Related examples in the same category