Oracle PL/SQL Tutorial/Cursor/Cursor Attributes
Содержание
- 1 An example of cursorValue cursor using %ISOPEN
- 2 An example of cursor variable using %ROWCOUNT
- 3 An example to illustrate parameterized cursors and cursorValue%NOTFOUND
- 4 Check sql%rowcount
- 5 Cursor not found
- 6 sql%notfound
- 7 using %ROWCOUNT as an incremental rowcount
- 8 using SQL%BULK_ROWCOUNT and SQL%ROWCOUNT
- 9 While cursorVariable%found loop
An example of cursorValue cursor using %ISOPEN
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> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
2 CURSOR cursorValue IS
3 SELECT h.product_description,o.rupany_short_name
4 FROM company o,product h
5 WHERE o.product_id =h.product_id
6 ORDER by 2;
7 v_company_rec cursorValue%ROWTYPE;
8 BEGIN
9 IF (NOT cursorValue%ISOPEN) THEN
10 OPEN cursorValue;
11 END IF;
12
13 FETCH cursorValue INTO v_company_rec;
14
15 WHILE (cursorValue%FOUND)LOOP
16 dbms_output.put_line(rpad(v_company_rec.product_description,20," ")||" "||
17 rpad(v_company_rec.rupany_short_name,30," "));
18 FETCH cursorValue INTO v_company_rec;
19 END LOOP;
20 IF (cursorValue%ISOPEN)THEN
21 CLOSE cursorValue;
22 END IF;
23 END;
24 /
Java A Inc.
Java B Inc.
Java C Inc.
Oracle D Inc.
Oracle E Inc.
Oracle F Inc.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table company;
Table dropped.
SQL>
An example of cursor variable using %ROWCOUNT
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> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 CURSOR cursorValue IS
3 SELECT h.product_description,o.rupany_short_name FROM company o,product h
4 WHERE o.product_id =h.product_id
5 ORDER by 2;
6 num_total_rows NUMBER;
7 BEGIN
8
9 FOR idx IN cursorValue LOOP
10 dbms_output.put_line(rpad(idx.product_description,20," ")||" "||
11 rpad(idx.rupany_short_name,30," "));
12
13 num_total_rows :=cursorValue%ROWCOUNT;
14 END LOOP;
15 IF num_total_rows >0 THEN
16 dbms_output.new_line;
17 dbms_output.put_line("Total Organizations = "||to_char(num_total_rows));
18 END IF;
19 END;
20 /
Java A Inc.
Java B Inc.
Java C Inc.
Oracle D Inc.
Oracle E Inc.
Oracle F Inc.
Total Organizations = 6
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL>
An example to illustrate parameterized cursors and cursorValue%NOTFOUND
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> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
2 CURSOR cursorValue(p_product_id NUMBER) IS
3 SELECT h.product_description,o.rupany_short_name
4 FROM company o,product h
5 WHERE o.product_id =h.product_id
6 AND h.product_id =p_product_id
7 ORDER by 2;
8 v_company_rec cursorValue%ROWTYPE;
9 BEGIN
10 OPEN cursorValue(1);
11
12 LOOP
13 FETCH cursorValue INTO v_company_rec;
14 EXIT WHEN cursorValue%NOTFOUND;
15 dbms_output.put_line(rpad(v_company_rec.product_description,20," ")||" "||
16 rpad(v_company_rec.rupany_short_name,30," "));
17 END LOOP;
18 CLOSE cursorValue;
19 OPEN cursorValue(2);
20
21 LOOP
22 FETCH cursorValue INTO v_company_rec;
23 EXIT WHEN cursorValue%NOTFOUND;
24 dbms_output.put_line(rpad(v_company_rec.product_description,20," ")||" "||
25 rpad(v_company_rec.rupany_short_name,30," "));
26 END LOOP;
27 CLOSE cursorValue;
28 END;
29 /
Java A Inc.
Java B Inc.
Java C Inc.
Oracle D Inc.
Oracle E Inc.
Oracle F Inc.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table company;
Table dropped.
Check sql%rowcount
SQL>
SQL>
SQL>
SQL> create table employee (
2 id number,
3 employee_type_id number,
4 external_id varchar2(30),
5 first_name varchar2(30),
6 middle_name varchar2(30),
7 last_name varchar2(30),
8 name varchar2(100),
9 birth_date date ,
10 gender_id number );
Table created.
SQL>
SQL>
SQL>
SQL> create table gender (
2 id number,
3 code varchar2(30),
4 description varchar2(80),
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL>
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, "F", "Female" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 2, "M", "Male" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 3, "U", "Unknown" );
1 row created.
SQL>
SQL>
SQL> create table employee_type (
2 id number,
3 code varchar2(30),
4 description varchar2(80),
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL> insert into employee_type(id,code,description)values(1,"C","Contractor" );
1 row created.
SQL> insert into employee_type(id,code,description)values(2,"E","Employee" );
1 row created.
SQL> insert into employee_type(id,code,description)values(3,"U","Unknown" );
1 row created.
SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
2 v_first_name employee.first_name%TYPE;
3 v_middle_name employee.middle_name%TYPE;
4 v_last_name employee.last_name%TYPE;
5 v_name employee.name%TYPE;
6 d_birth_date employee.birth_date%TYPE;
7 n_count number;
8
9 begin
10 v_first_name := "JOHN";
11 v_middle_name := "J.";
12 v_last_name := "DOE";
13 v_name := rtrim(v_last_name||", "||v_first_name||" "||v_middle_name);
14 d_birth_date := to_date("19800101", "YYYYMMDD");
15
16 begin
17 insert into employee (
18 id,
19 employee_type_id,
20 external_id,
21 first_name,
22 middle_name,
23 last_name,
24 name,
25 birth_date,
26 gender_id )
27 select 12,
28 myCursor.id,
29 lpad("12", 9, "0"),
30 v_first_name,
31 v_middle_name,
32 v_last_name,
33 v_name,
34 d_birth_date,
35 c2.id
36 from employee_type myCursor,
37 gender c2
38 where myCursor.code = "C"
39 and c2.code = "M"
40 and not exists (
41 select 1
42 from employee x
43 where x.name = v_name
44 and x.birth_date = d_birth_date
45 and x.gender_id = c2.id );
46
47 n_count := sql%rowcount;
48 exception
49 when OTHERS then
50 raise_application_error(-20006, SQLERRM||" on insert employee");
51 end;
52
53 DBMS_OUTPUT.PUT_LINE(to_char(n_count)||" row(s) inserted.");
54 end;
55 /
1 row(s) inserted.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table gender;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> drop table employee_type;
Table dropped.
SQL>
SQL>
Cursor not found
SQL>
SQL>
SQL>
SQL>
SQL> create table employee
2 (
3 empl_no integer primary key
4 ,lastname varchar2(20) not null
5 ,firstname varchar2(15) not null
6 ,midinit varchar2(1)
7 ,street varchar2(30)
8 ,city varchar2(20)
9 ,state varchar2(2)
10 ,zip varchar2(5)
11 ,zip_4 varchar2(4)
12 ,area_code varchar2(3)
13 ,phone varchar2(8)
14 ,company_name varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created.
SQL>
SQL>
SQL> create table ord(
2 order_no integer primary key
3 ,empl_no integer
4 ,order_date date not null
5 ,total_order_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment_method varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,gift_message varchar2(100)
12 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,add_months(sysdate, -1), 235.00, "14-Feb-1999", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,add_months(sysdate, -2), 50.98, "14-feb-1999", "1 pm", "CA",7, "Rose", "Happy Valentines Day to Mother");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,add_months(sysdate, -3), 35.99, "14-feb-1999", "1 pm", "VS",2, "Ruby", "Happy Valentines Day to Mother");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,add_months(sysdate, -4), 19.95, "14-feb-1999", "5 pm", "CA",2, "Coy", "Happy Valentines Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,add_months(sysdate, -7), 35.95, "21-jun-1999", "12 noon", "VS", 2, "Fill", "Happy Birthday from Joe");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, add_months(sysdate, -8), 35.95, "1-jan-2000", "12 noon", "DI",3, "Laura", "Happy New Year""s from Lawrence");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, add_months(sysdate, -9), 75.95, "2-jan-2000", "12 noon", "CA",7, "Sara", "Happy Birthday from Lawrence" );
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, add_months(sysdate, -10), 19.95, sysdate, "2:30 pm", "VG",2, "OK", "Happy Valentines Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, add_months(sysdate, -11), 30.00, sysdate+2, "1:30 pm", "VG",2, "Hi", "Happy Birthday Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, add_months(sysdate, -12), 21.95, sysdate-2, "3:30 pm", "CA",2, "Jack", "Happy Birthday Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, add_months(sysdate, -1), 21.95, sysdate, "3:30 pm", "CA",2, "Jay", "Thanks for giving 100%!");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> alter table employee add(discount number);
Table altered.
SQL>
SQL> create or replace procedure employee_discount1 as
2 cursor cust_cur is
3 select empl_no, sum(total_order_price) as sales
4 from ord group by empl_no;
5 cust_rec cust_cur%rowtype;
6 v_discount employee.discount%type;
7 myStart number := dbms_utility.get_time;
8 begin
9 open cust_cur;
10 loop
11 fetch cust_cur into cust_rec;
12 if cust_cur%notfound then exit; end if;
13 case
14 when cust_rec.sales <= 10 then
15 v_discount := .00;
16
17 when cust_rec.sales <= 20 then
18 v_discount := .10;
19
20 when cust_rec.sales <= 50 then
21 v_discount := .15;
22
23 else v_discount := .20;
24 end case;
25 update employee
26 set discount = v_discount
27 where empl_no = cust_rec.empl_no;
28 end loop;
29 close cust_cur;
30 commit;
31 dbms_output.put_line(
32 round( (dbms_utility.get_time - myStart) / 100
33 ,2) || " seconds");
34 end;
35 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> exec employee_discount1
PL/SQL procedure successfully completed.
SQL>
SQL> drop table ord;
Table dropped.
SQL> drop table employee;
Table dropped.
SQL>
SQL>
SQL>
SQL>
sql%notfound
SQL>
SQL> create table ord
2 (
3 order_no integer primary key
4 ,empl_no integer
5 ,order_date date not null
6 ,total_order_price number(7,2)
7 ,deliver_date date
8 ,deliver_time varchar2(7)
9 ,payment_method varchar2(2)
10 ,emp_no number(3,0)
11 ,deliver_name varchar2(35)
12 ,gift_message varchar2(100)
13 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,add_months(sysdate, -1), 235.00, "14-Feb-1999", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,add_months(sysdate, -2), 50.98, "14-feb-1999", "1 pm", "CA",7, "Rose", "Happy Valentines Day to Mother");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,add_months(sysdate, -3), 35.99, "14-feb-1999", "1 pm", "VS",2, "Ruby", "Happy Valentines Day to Mother");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,add_months(sysdate, -4), 19.95, "14-feb-1999", "5 pm", "CA",2, "Coy", "Happy Valentines Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,add_months(sysdate, -7), 35.95, "21-jun-1999", "12 noon", "VS", 2, "Fill", "Happy Birthday from Joe");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, add_months(sysdate, -8), 35.95, "1-jan-2000", "12 noon", "DI",3, "Laura", "Happy New Year""s from Lawrence");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, add_months(sysdate, -9), 75.95, "2-jan-2000", "12 noon", "CA",7, "Sara", "Happy Birthday from Lawrence" );
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, add_months(sysdate, -10), 19.95, sysdate, "2:30 pm", "VG",2, "OK", "Happy Valentines Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, add_months(sysdate, -11), 30.00, sysdate+2, "1:30 pm", "VG",2, "Hi", "Happy Birthday Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, add_months(sysdate, -12), 21.95, sysdate-2, "3:30 pm", "CA",2, "Jack", "Happy Birthday Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, add_months(sysdate, -1), 21.95, sysdate, "3:30 pm", "CA",2, "Jay", "Thanks for giving 100%!");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> create table ord_history
2 (
3 order_no integer primary key
4 ,empl_no integer
5 ,order_date date not null
6 ,total_order_price number(7,2)
7 ,deliver_date date
8 ,deliver_time varchar2(7)
9 ,payment_method varchar2(2)
10 ,emp_no number(3,0)
11 ,deliver_name varchar2(35)
12 ,gift_message varchar2(100)
13 );
Table created.
SQL> create or replace procedure delete_orders(p_days in number)
2 is
3 begin
4 insert into ord_history select * from ord where order_date < sysdate - p_days;
5 if sql%notfound then
6 dbms_output.put_line("No orders old than " || p_days || " days");
7 else
8 delete from ord
9 where order_date < sysdate - p_days;
10 dbms_output.put_line(sql%rowcount || " rows deleted that were older than " || p_days || " days");
11 end if;
12 end;
13 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> drop table ord;
Table dropped.
SQL> drop table ord_history;
Table dropped.
using %ROWCOUNT as an incremental rowcount
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> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
2 CURSOR cursorValue IS
3 SELECT h.product_description,o.rupany_short_name
4 FROM company o,product h
5 WHERE o.product_id =h.product_id
6 ORDER by 2;
7 num_total_rows NUMBER;
8 BEGIN
9 FOR idx IN cursorValue LOOP
10 IF cursorValue%ROWCOUNT =1 THEN
11 dbms_output.put_line(rpad("-",20,"-")||" "||rpad("-",30,"-"));
12 END IF;
13 dbms_output.put_line(rpad(idx.product_description,20," ")||" "||
14 rpad(idx.rupany_short_name,30," "));
15 num_total_rows :=cursorValue%ROWCOUNT;
16 END LOOP;
17 IF num_total_rows >0 THEN
18 dbms_output.new_line;
19 dbms_output.put_line("Total Organizations = "||to_char(num_total_rows));
20 END IF;
21 END;
22 /
-------------------- ------------------------------
Java A Inc.
Java B Inc.
Java C Inc.
Oracle D Inc.
Oracle E Inc.
Oracle F Inc.
Total Organizations = 6
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table company;
Table dropped.
using SQL%BULK_ROWCOUNT and SQL%ROWCOUNT
SQL>
SQL>
SQL> CREATE TABLE customer_region(
2 region_id NUMBER(4) PRIMARY KEY,
3 region_name VARCHAR2(11) NOT NULL
4 );
Table created.
SQL>
SQL>
SQL> INSERT INTO customer_region VALUES (1,"REGION1");
1 row created.
SQL> INSERT INTO customer_region VALUES (2,"REGION2");
1 row created.
SQL> INSERT INTO customer_region VALUES (3,"REGION3");
1 row created.
SQL> INSERT INTO customer_region VALUES (4,"REGION4");
1 row created.
SQL>
SQL>
SQL> DECLARE
2 Type regionIdTableCollection IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
3 Type regionNameTableCollection IS TABLE of VARCHAR2(20)INDEX BY BINARY_INTEGER;
4 region_ids regionIdTableCollection;
5 region_names regionNameTableCollection;
6 returnCode NUMBER;
7 errorMessage VARCHAR2(1000);
8 Procedure load_regions_bulk_bind
9 (region_ids IN regionIdTableCollection,
10 region_names IN regionNameTableCollection,
11 returnCode OUT NUMBER,
12 errorMessage OUT VARCHAR2)
13 Is
14 BEGIN
15 FORALL i IN region_ids.FIRST..region_ids.LAST
16 INSERT INTO customer_region values (region_ids(i),region_names(i));
17 FOR i in 1..region_ids.COUNT LOOP
18 IF SQL%BULK_ROWCOUNT(i)>0 THEN
19 dbms_output.put_line(to_char(sql%bulk_rowcount(i)));
20 NULL;
21 END IF;
22 END LOOP;
23 IF SQL%ROWCOUNT =0 THEN
24 DBMS_OUTPUT.PUT_LINE("No Rows inserted overall");
25 ELSE
26 COMMIT;
27 END IF;
28 EXCEPTION WHEN OTHERS THEN
29 COMMIT;
30 returnCode :=SQLCODE;
31 errorMessage :=SQLERRM;
32 END;
33 BEGIN
34 region_ids(1):=6;
35 region_names(1):="region6";
36 load_regions_bulk_bind(region_ids,region_names,returnCode,errorMessage);
37 END;
38 /
1
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table customer_region;
Table dropped.
SQL>
While cursorVariable%found loop
SQL>
SQL>
SQL> create table ord_item
2 (
3 order_no integer
4 ,product_id integer
5 ,quantity number(4,0)
6 ,item_price number(7,2)
7 ,total_order_item_price number(9,2)
8 ,primary key (order_no ,product_id)
9 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(1, 2, 10, 23.00 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(2, 1, 1, 23.00 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(2, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(2, 8, 1, 17.48 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(3, 8, 1, 35.99 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(4, 7, 1, 19.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(5, 5, 1, 10.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(6, 8, 1, 22.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(7, 1, 6, 15.00 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(7, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(7, 8, 1, 10.45 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(8, 8, 1, 35.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(9, 8, 1, 65.45 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(9, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(10, 3, 1, 19.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(11, 8, 1, 30.00);
1 row created.
SQL>
SQL>
SQL>
SQL> create table ord
2 (
3 order_no integer primary key
4 ,empl_no integer
5 ,order_date date not null
6 ,total_order_price number(7,2)
7 ,deliver_date date
8 ,deliver_time varchar2(7)
9 ,payment_method varchar2(2)
10 ,emp_no number(3,0)
11 ,deliver_name varchar2(35)
12 ,gift_message varchar2(100)
13 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,add_months(sysdate, -1), 235.00, "14-Feb-1999", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,add_months(sysdate, -2), 50.98, "14-feb-1999", "1 pm", "CA",7, "Rose Red", "Happy Valentines Day to Mother");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,add_months(sysdate, -3), 35.99, "14-feb-1999", "1 pm", "VS",2, "Ruby Forest", "Happy Valentines Day to Mother");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,add_months(sysdate, -4), 19.95, "14-feb-1999", "5 pm", "CA",2, "W. Coyote", "Happy Valentines Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,add_months(sysdate, -7), 35.95, "21-jun-1999", "12 noon", "VS", 2, "Jessica Rabbit", "Happy Birthday from Joe");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, add_months(sysdate, -8), 35.95, "1-jan-2000", "12 noon", "DI",3, "Laura", "Happy New Year""s from Lawrence");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, add_months(sysdate, -9), 75.95, "2-jan-2000", "12 noon", "CA",7, "Sara", "Happy Birthday from Lawrence" );
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, add_months(sysdate, -10), 19.95, sysdate, "2:30 pm", "VG",2, "W. Coyote", "Happy Valentines Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, add_months(sysdate, -11), 30.00, sysdate+2, "1:30 pm", "VG",2, "W. Coyote", "Happy Birthday Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, add_months(sysdate, -12), 21.95, sysdate-2, "3:30 pm", "CA",2, "W. Coyote", "Happy Birthday Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, add_months(sysdate, -1), 21.95, sysdate, "3:30 pm", "CA",2, "W. Coyote", "Thanks for giving 100%!");
1 row created.
SQL>
SQL>
SQL>
SQL> --set termout on
SQL>
SQL> DECLARE
2
3 v_order_no ord_item.order_no%type;
4 v_tot_order_price ord_item.item_price%type;
5
6 cursor c1 is
7 select order_no, sum(item_price * quantity)
8 from ord_item
9 group by order_no;
10
11 begin
12 open c1;
13
14 fetch c1 into v_order_no, v_tot_order_price;
15
16 while c1%found loop
17
18 update ord
19 set total_order_price = v_tot_order_price
20 where order_no = v_order_no;
21
22 fetch c1 into v_order_no, v_tot_order_price;
23
24 end loop;
25
26 close c1;
27 end;
28 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table ord;
Table dropped.
SQL>
SQL> drop table ord_item;
Table dropped.