Column type parameter : Parameter IN « Stored Procedure Function « Oracle PL / SQL






Column type parameter

   
SQL>
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );

Table created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, 'Mar', 'Wells','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, 'Man', 'Kyte','Economics', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, 'Pat', 'Poll','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, 'Tim', 'Viper','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, 'Barbara', 'Blues','Economics', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, 'David', 'Large','Music', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, 'Rose', 'Bond','Music', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE AddNewStudent (
  2    p_FirstName  lecturer.first_name%TYPE,
  3    p_LastName   lecturer.last_name%TYPE,
  4    p_Major      lecturer.major%TYPE) AS
  5  BEGIN
  6    INSERT INTO lecturer (ID, first_name, last_name,
  7                          major, current_credits)
  8      VALUES (10018, p_FirstName, p_LastName,
  9              p_Major, 0);
 10  END AddNewStudent;
 11  /

Procedure created.

SQL>
SQL>
SQL> DECLARE
  2    v_NewFirstName  lecturer.first_name%TYPE := 'Cynthia';
  3    v_NewLastName   lecturer.last_name%TYPE := 'Camino';
  4    v_NewMajor      lecturer.major%TYPE := 'History';
  5  BEGIN
  6    AddNewStudent(v_NewFirstName, v_NewLastName, v_NewMajor);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from lecturer;

        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
MAJOR                          CURRENT_CREDITS
------------------------------ ---------------
     10001 Scott                Lawson
Computer Science                            11

     10002 Mar                  Wells
History                                      4

     10003 Jone                 Bliss
Computer Science                             8


        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
MAJOR                          CURRENT_CREDITS
------------------------------ ---------------
     10004 Man                  Kyte
Economics                                    8

     10005 Pat                  Poll
History                                      4

     10006 Tim                  Viper
History                                      4


        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
MAJOR                          CURRENT_CREDITS
------------------------------ ---------------
     10007 Barbara              Blues
Economics                                    7

     10008 David                Large
Music                                        4

     10009 Chris                Elegant
Nutrition                                    8


        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
MAJOR                          CURRENT_CREDITS
------------------------------ ---------------
     10010 Rose                 Bond
Music                                        7

     10011 Rita                 Johnson
Nutrition                                    8

     10012 Sharon               Clear
Computer Science                             3


        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
MAJOR                          CURRENT_CREDITS
------------------------------ ---------------
     10018 Cynthia              Camino
History                                      0


13 rows selected.

SQL>
SQL> drop table lecturer;

Table dropped.

SQL>
SQL>

   
    
  








Related examples in the same category

1.Use 'IN' parameters
2.Define in parameter
3.Demonstrate the behavior of IN, OUT, and IN OUT parameter modes.
4.This procedure will not compile, since it attempts to modify an IN parameter.