Oracle PL/SQL Tutorial/PL SQL Data Types/TYPE

Column type parameters

   <source lang="sql">

SQL> SQL> CREATE TABLE employee_locker (

 2    emp_id        NUMBER        NOT NULL  PRIMARY KEY,
 3    name          VARCHAR2(30)  NOT NULL,
 4    room_number   VARCHAR2(30)  NOT NULL,
 5    occupied_dt   DATE,
 6    checkout_date DATE );

Table created. SQL> SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt, checkout_date )VALUES( 1, "Java", "10A", TRUNC(SYSDATE), TRUNC(SYSDATE) ); 1 row created. SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 2, "SQL", "12A", TRUNC(SYSDATE) ); 1 row created. SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt, checkout_date )VALUES( 3, "Oracle", "12B", TRUNC(SYSDATE), TRUNC(SYSDATE) ); 1 row created. SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 4, "PC", "10A", TRUNC(SYSDATE+1) ); 1 row created. SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 5, "JavaScript", "12A", TRUNC(SYSDATE+1) ); 1 row created. SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 6, "C", "12B", TRUNC(SYSDATE+1) ); 1 row created. SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 7, "C++", "10A", TRUNC(SYSDATE+2) ); 1 row created. SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 8, "Python", "12A", TRUNC(SYSDATE+2) ); 1 row created. SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 9, "C#", "12B", TRUNC(SYSDATE+2) ); 1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE update_bill(

 2    emp_id_in  employee_locker.emp_id%TYPE,
 3    room_number_in  employee_locker.room_number%TYPE )
 4  IS
 5  BEGIN
 6    dbms_output.put_line("bill updated for emp_id = "||emp_id_in||", room_number = "||room_number_in);
 7  END update_bill;
 8  /

Procedure created. SQL> SQL> set serveroutput on size 500000 SQL> SQL> DECLARE

 2     CURSOR employee_locker_cur IS
 3        SELECT emp_id, room_number
 4        FROM employee_locker WHERE occupied_dt = TRUNC(SYSDATE);
 5     employee_locker_rec employee_locker_cur%ROWTYPE;
 6  BEGIN
 7     OPEN employee_locker_cur;
 8     LOOP
 9        FETCH employee_locker_cur INTO employee_locker_rec;
10        EXIT WHEN employee_locker_cur%NOTFOUND;
11        update_bill (employee_locker_rec.emp_id, employee_locker_rec.room_number);
12     END LOOP;
13     CLOSE employee_locker_cur;
14  END;
15  /

bill updated for emp_id = 1, room_number = 10A bill updated for emp_id = 2, room_number = 12A bill updated for emp_id = 3, room_number = 12B PL/SQL procedure successfully completed. SQL> SQL> SQL> DROP TABLE employee_locker; Table dropped. SQL></source>

PL/SQL allows you to use the %type attribute in a nesting variable declaration.

   <source lang="sql">

dept_sales INTEGER; area_sales dept_sales%type; group_sales area_sales%type; regional_sales area_sales%type; corporate_sales regional_sales%type;</source>

Select value into a column type variable

   <source lang="sql">

SQL> SQL> CREATE TABLE accounts(

 2    account_id  NUMBER  NOT NULL  PRIMARY KEY,
 3    balance  NUMBER );

Table created. SQL> SQL> INSERT INTO accounts(account_id,balance )VALUES(1,1000 ); 1 row created. SQL> INSERT INTO accounts(account_id,balance )VALUES(2,800 ); 1 row created. SQL> SQL> SQL> CREATE OR REPLACE FUNCTION account_balance(

 2    account_id_in IN accounts.account_id%TYPE)
 3  RETURN accounts.balance%TYPE
 4  IS
 5    l_balance  accounts.balance%TYPE;
 6  BEGIN
 7    SELECT balance
 8      INTO l_balance
 9      FROM accounts
10     WHERE account_id = account_id_in;
11
12    RETURN l_balance;
13
14  END account_balance;
15  /

Function created. SQL> SQL> CREATE OR REPLACE PROCEDURE apply_balance(

 2    account_id_in IN accounts.balance%TYPE,
 3    balance_in IN accounts.balance%TYPE)
 4  IS
 5  BEGIN
 6    UPDATE accounts
 7       SET balance = balance - balance_in
 8     WHERE account_id = account_id_in;
 9  END apply_balance;
10  /

Procedure created. SQL> SQL> SQL> SQL> SQL> DECLARE

 2     account_id  accounts.account_id%TYPE := 2;
 3     balance_remaining  accounts.balance%TYPE;
 4  BEGIN
 5     LOOP
 6
 7        balance_remaining := account_balance (account_id);
 8
 9
10        EXIT WHEN balance_remaining < 1000;
11
12
13        apply_balance (account_id, balance_remaining);
14     END LOOP;
15  END;
16  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table accounts; Table dropped. SQL></source>

The %TYPE Command Illustrated

   <source lang="sql">

SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
 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> declare

 2    v_ename employee.first_Name%TYPE;
 3    v_emp_rec employee%ROWTYPE;
 4  begin
 5    v_ename := "Smith";
 6    v_emp_rec.first_Name := "Chan";
 7  end;
 8  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped.</source>

Using %TYPE and %ROWTYPE on Row Objects

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE address AS OBJECT

 2              (line1 VARCHAR2(20),
 3               line2 VARCHAR2(20),
 4               city VARCHAR2(20),
 5               state_code VARCHAR2(2),
 6               zip VARCHAR2(13),
 7    MEMBER FUNCTION get_address RETURN VARCHAR2,
 8    MEMBER PROCEDURE set_address
 9              (addressLine1 VARCHAR2,
10               addressLine2 VARCHAR2,
11               address_city VARCHAR2,
12               address_state VARCHAR2,
13               address_zip VARCHAR2)
14  );
15  /

Type created. SQL> CREATE OR REPLACE TYPE BODY address AS

 2    MEMBER FUNCTION get_address RETURN VARCHAR2
 3    IS
 4    BEGIN
 5      RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||SELF.state_code||" "||SELF.zip);
 6    END get_address;
 7    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
 8                  addressLine2 VARCHAR2,
 9                  address_city VARCHAR2,
10                  address_state VARCHAR2,
11                  address_zip VARCHAR2)
12    IS
13    BEGIN
14      line1 :=addressLine1;
15      line2 :=addressLine2;
16      city :=address_city;
17      state_code :=address_state;
18      zip :=address_zip;
19    END set_address;
20  END;
21  /

Type body created. SQL> SQL> CREATE TABLE address_master OF address; Table created. SQL> SQL> INSERT INTO address_master VALUES (address("19 J","Reading Rd","Vancouver","NJ","00000")); 1 row created. SQL> SQL> select * from address_master; LINE1 LINE2 CITY ST

---

-------------------- -------------------- --

ZIP

---

19 J Reading Rd Vancouver NJ 00000

1 row selected. SQL> SQL> DECLARE

 2    v_line1 address_master.line1%TYPE;
 3  BEGIN
 4    SELECT line1 INTO v_line1 FROM address_master WHERE city ="Vancouver";
 5    DBMS_OUTPUT.PUT_LINE(v_line1);
 6  END;
 7  /

19 J PL/SQL procedure successfully completed. SQL> SQL> drop table address_master; Table dropped. SQL> SQL></source>

Variables Based on Database Columns

You base a declaration on a table column without having to know definitively what that column"s datatype is.

Your variables will automatically be changed as the table column.

You use the %type attribute in variable declarations to define variable based on table column.

   <source lang="sql">

avg_rate employee.pay_rate%type</th> dept_id INTEGER;</td> sub_dept_id dept_id%type; -- datatype based on a variable</td> area_id dept_id%type := 9141; -- used with an initialization clause</td></source>

You may also specify a variable"s type using the %TYPE keyword, which tells PL/SQL to use the same type as a specified column in a table.

   <source lang="sql">

SQL> SQL> 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> CREATE OR REPLACE PROCEDURE update_employee_salary(

 2    p_employee_id IN employee. id%TYPE,
 3    p_factor IN NUMBER
 4  ) AS
 5    v_employee_count INTEGER;
 6  BEGIN
 7    -- count the number of products with the
 8    -- supplied product_id (should be 1 if the product exists)
 9    SELECT COUNT(*)
10    INTO v_employee_count
11    FROM employee
12    WHERE id = p_employee_id;
13
14    -- if the employee exists (v_employee_count = 1) then
15    -- update that employee"s salary
16    IF v_employee_count = 1 THEN
17      UPDATE employee
18      SET salary = salary * p_factor
19      WHERE id = p_employee_id;
20      COMMIT;
21    END IF;
22  EXCEPTION
23    WHEN OTHERS THEN
24      ROLLBACK;
25  END update_employee_salary;
26  /

Procedure created. SQL> SQL> SQL> SQL> SELECT object_name, aggregate, parallel

 2  FROM user_procedures
 3  WHERE object_name = "UPDATE_EMPLOYEE_SALARY";

OBJECT_NAME AGG PAR

---

--- ---

UPDATE_EMPLOYEE_SALARY NO NO SQL> SQL> DROP PROCEDURE update_employee_salary; Procedure dropped. SQL> SQL> SELECT object_name, aggregate, parallel

 2  FROM user_procedures
 3  WHERE object_name = "UPDATE_EMPLOYEE_SALARY";

no rows selected SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>