Oracle PL/SQL Tutorial/PL SQL Data Types/ROWTYPE
Содержание
- 1 Declare ROWTYPE variable
- 2 Defining and using a cursor-oriented record
- 3 INSERT statement involving entire records
- 4 PLS-00382: expression is of wrong type
- 5 Reference attribute in a ROWTYPE variable
- 6 rowtype.iterations
- 7 Select * into table%rowtype
- 8 Select value into the %ROWTYPE type variable
- 9 UPDATE statement involving entire records
- 10 Use rowtype with object table
- 11 Use table column type as the record attribute type
- 12 Using a Weak REF CURSOR and %ROWTYPE
Declare ROWTYPE variable
<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> CREATE OR REPLACE PROCEDURE update_emp (emp_rec employee%ROWTYPE) IS
2 BEGIN 3 UPDATE employee 4 SET start_date = emp_rec.start_date + 100 5 WHERE id = emp_rec.id; 6 END update_emp; 7 /
Procedure created. SQL> SQL> SQL> DECLARE
2 a employee%ROWTYPE; 3 BEGIN 4 a.id := "01"; 5 update_emp(a); 6 END; 7 /
PL/SQL procedure successfully completed. SQL> SELECT * FROM employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 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> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
Defining and using a cursor-oriented record
<source lang="sql">
SQL> SQL> SQL> create table product(
2 product_id number(4) not null, 3 product_description varchar2(20) not null 4 );
Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> SQL> SQL> DECLARE
2 CURSOR csr_hrc IS 3 SELECT * FROM product ORDER BY 1; 4 myRecord csr_hrc%ROWTYPE; 5 BEGIN 6 OPEN csr_hrc; 7 8 LOOP 9 FETCH csr_hrc INTO myRecord; 10 EXIT WHEN csr_hrc%NOTFOUND; 11 dbms_output.put_line(to_char(myRecord.product_id)||" "||myRecord.product_description); 12 END LOOP; 13 CLOSE csr_hrc; 14 END; 15 /
1 Java 2 Oracle 3 C# 4 Javascript 5 Python PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop table product; Table dropped.</source>
INSERT statement involving entire records
<source lang="sql">
SQL> SQL> SQL> create table product(
2 product_id number(4) not null, 3 product_description varchar2(20) not null 4 );
Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> SQL> SQL> SQL> DECLARE
2 TYPE myRecord IS RECORD 3 (product_id NUMBER, 4 product_description VARCHAR2(20)); 5 v_example_rec myRecord; 6 BEGIN 7 v_example_rec.product_id :=99; 8 v_example_rec.product_description :="Web Analyst"; 9 INSERT INTO product VALUES v_example_rec; 10 COMMIT; 11 END; 12 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop table product; Table dropped. SQL></source>
PLS-00382: expression is of wrong type
<source lang="sql">
SQL> SQL> CREATE TABLE employee
2 (employee_id NUMBER(7), 3 last_name VARCHAR2(25), 4 first_name VARCHAR2(25), 5 userid VARCHAR2(8), 6 start_date DATE, 7 comments VARCHAR2(255), 8 manager_id NUMBER(7), 9 title VARCHAR2(25), 10 department_id NUMBER(7), 11 salary NUMBER(11, 2), 12 commission_pct NUMBER(4, 2) 13 );
Table created. SQL> SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL); 1 row created. SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL); 1 row created. SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL); 1 row created. SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL); 1 row created. SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL); 1 row created. SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL); 1 row created. SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL); 1 row created. SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL); 1 row created. SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL); 1 row created. SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL); 1 row created. SQL> SQL> DECLARE
2 TYPE type_emp_rec1 IS RECORD
3 (emp_last_name_txt employee.last_name%TYPE,
4 emp_first_name_txt employee.first_name%TYPE);
5 TYPE type_emp_rec2 IS RECORD
6 (emp_last_name_txt employee.last_name%TYPE,
7 emp_first_name_txt employee.first_name%TYPE);
8 lv_emp_type_rec1 type_emp_rec1;
9 lv_emp_type_rec2 type_emp_rec2;
10 lv_emp_type_rec3 type_emp_rec2;
11 BEGIN
12 lv_emp_type_rec1 := lv_emp_type_rec2;
13 lv_emp_type_rec1.emp_last_name_txt := lv_emp_type_rec2.emp_last_name_txt;
14 lv_emp_type_rec2 := lv_emp_type_rec3;
15 END;
16 /
lv_emp_type_rec1 := lv_emp_type_rec2;
*
ERROR at line 12: ORA-06550: line 12, column 24: PLS-00382: expression is of wrong type ORA-06550: line 12, column 4: PL/SQL: Statement ignored
SQL> SQL> drop table employee; Table dropped. SQL></source>
Reference attribute in a ROWTYPE variable
<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> CREATE OR REPLACE PROCEDURE update_emp (emp_rec employee%ROWTYPE) IS
2 BEGIN 3 UPDATE employee 4 SET start_date = emp_rec.start_date + 100 5 WHERE id = emp_rec.id; 6 END update_emp; 7 /
Procedure created. SQL> SQL> SQL> DECLARE
2 a employee%ROWTYPE; 3 BEGIN 4 a.id := "01"; 5 update_emp(a); 6 END; 7 /
PL/SQL procedure successfully completed. SQL> SELECT * FROM employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 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> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
rowtype.iterations
<source lang="sql">
SQL> SQL> create table job_parameters
2 ( jobid number primary key, 3 iterations number, 4 table_idx number );
Table created. SQL> SQL> SQL> create or replace procedure bind( p_job in number )
2 as 3 l_rec job_parameters%rowtype; 4 begin 5 select * into l_rec from job_parameters where jobid = p_job; 6 for i in 1 .. l_rec.iterations 7 loop 8 execute immediate 9 "insert into t" || l_rec.table_idx || " values ( :x )" using i; 10 commit; 11 end loop; 12 delete from job_parameters where jobid = p_job; 13 end; 14 /
Procedure created. SQL> drop table job_parameters; Table dropped. SQL> SQL></source>
Select * into table%rowtype
<source lang="sql">
SQL> SQL> create table T (
2 c1 number, 3 c2 number );
Table created. SQL> SQL> insert into T values (1,2); 1 row created. SQL> SQL> create or replace procedure WITH_ROWTYPE is
2 r T%ROWTYPE; 3 begin 4 select * into r from T where rownum = 1; 5 end; 6 /
Procedure created. SQL> SQL> SQL> drop table t; Table dropped.</source>
Select value into the %ROWTYPE type variable
<source lang="sql">
SQL> SQL> SQL> Using the %ROWTYPE Clause on the Table Name 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> SQL> declare
2 r_emp employee%ROWTYPE; 3 begin 4 select * into r_emp from employee where id = "01"; 5 DBMS_OUTPUT.put_line(r_emp.first_Name); 6 end; 7 /
Jason PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
UPDATE statement involving entire records
<source lang="sql">
SQL> SQL> SQL> SQL> create table product(
2 product_id number(4) not null, 3 product_description varchar2(20) not null 4 );
Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> SQL> DECLARE
2 TYPE myRecord IS RECORD 3 (product_id NUMBER, 4 product_description VARCHAR2(20)); 5 v_example_rec myRecord; 6 BEGIN 7 v_example_rec.product_id :=99; 8 v_example_rec.product_description :="Web"; 9 UPDATE product SET ROW =v_example_rec WHERE product_id =99; 10 COMMIT; 11 END; 12 /
PL/SQL procedure successfully completed. SQL> SQL> drop table product; Table dropped.</source>
Use rowtype with object table
<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 cursor c1 is select VALUE(a)from address_master a; 3 v_add address_master%ROWTYPE; 4 begin 5 open c1; 6 loop 7 fetch c1 into v_add; 8 exit when c1%notfound; 9 dbms_output.put_line(v_add.line1); 10 end loop; 11 close c1; 12 end; 13 /
19 J PL/SQL procedure successfully completed. SQL> SQL> drop table address_master; Table dropped. SQL> SQL></source>
Use table column type as the record attribute type
<source lang="sql">
SQL> SQL> CREATE TABLE employee
2 (employee_id NUMBER(7), 3 last_name VARCHAR2(25), 4 first_name VARCHAR2(25), 5 userid VARCHAR2(8), 6 start_date DATE, 7 comments VARCHAR2(255), 8 manager_id NUMBER(7), 9 title VARCHAR2(25), 10 department_id NUMBER(7), 11 salary NUMBER(11, 2), 12 commission_pct NUMBER(4, 2) 13 );
Table created. SQL> SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL); 1 row created. SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL); 1 row created. SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL); 1 row created. SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL); 1 row created. SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL); 1 row created. SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL); 1 row created. SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL); 1 row created. SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL); 1 row created. SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL); 1 row created. SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL); 1 row created. SQL> SQL> SQL> SQL> DECLARE
2 TYPE type_emp_comp_rec IS RECORD 3 (emp_last_name_txt employee.last_name%TYPE, 4 emp_first_name_txt employee.first_name%TYPE, 5 emp_salary_num employee.salary%TYPE, 6 emp_commission_pct_num employee.rumission_pct%TYPE ); 7 lv_emp_comp_rec1 type_emp_comp_rec; 8 lv_emp_comp_rec2 type_emp_comp_rec; 9 BEGIN 10 lv_emp_comp_rec1.emp_last_name_txt := "TREZZO"; 11 lv_emp_comp_rec2.emp_first_name_txt := "JOE"; 12 END; 13 /
PL/SQL procedure successfully completed. SQL> SQL> drop table employee; Table dropped. SQL></source>
Using a Weak REF CURSOR and %ROWTYPE
<source lang="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> SQL> SQL> create or replace procedure p_report(i_where_tx VARCHAR2)
2 is
3 c_emp_ref sys_refcursor;
4 v_emp_rec employee%ROWTYPE;
5 begin
6 DBMS_OUTPUT.put_line("-----------------------");
7 open c_emp_ref for "select * from employee where " ||i_where_tx;
8 loop
9 fetch c_emp_ref into v_emp_rec;
10 exit when c_emp_ref%notfound;
11 DBMS_OUTPUT.put_line(v_emp_rec.id||" "|| v_emp_rec.first_Name||" - "|| v_emp_rec.salary);
12 end loop;
13 DBMS_OUTPUT.put_line("-----------------------");
14 end p_report;
15 /
Procedure created. SQL> begin
2 p_report("salary > 2000");
3 end;
4 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
