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
<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> 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></source>
An example of cursor variable using %ROWCOUNT
<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> 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></source>
An example to illustrate parameterized cursors and cursorValue%NOTFOUND
<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> 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.</source>
Check sql%rowcount
<source lang="sql">
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></source>
Cursor not found
<source lang="sql">
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></source>
sql%notfound
<source lang="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", "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.</source>
using %ROWCOUNT as an incremental rowcount
<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> 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.</source>
using SQL%BULK_ROWCOUNT and SQL%ROWCOUNT
<source lang="sql">
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></source>
While cursorVariable%found loop
<source lang="sql">
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.</source>
