в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:21Z

1 версия

Новая страница

== An example of parameterized cursor using cursor FOR LOOP==



<source lang="sql">
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
11 FOR idx in cursorValue(1) LOOP
12 dbms_output.put_line(rpad(idx.product_description,20," ")||" "||
13 rpad(idx.rupany_short_name,30," "));
14 END LOOP;
15
16 FOR idx in cursorValue(2) LOOP
17 dbms_output.put_line(rpad(idx.product_description,20," ")||" "||
18 rpad(idx.rupany_short_name,30," "));
19 END LOOP;
20 END;
21 /
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.
SQL></source>

== Open cursor with parameter==



<source lang="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> set serveroutput on size 1000000;
SQL>
SQL> declare
2 cursor c_worker(aiv_last_name in employee.last_name%TYPE) is
3 select first_name
4 from employee
5 where last_name like aiv_last_name||"%"
6 order by id;
7
8 v_first_name employee.first_name%TYPE;
9
10 begin
11 open c_worker("DOE");
12 loop
13 fetch c_worker into v_first_name;
14
15 if c_worker%notfound then
16 close c_worker;
17 exit;
18 end if;
19
20 DBMS_OUTPUT.PUT_LINE(v_first_name);
21 end loop;
22 end;
23 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employee;
Table dropped.</source>

== Parameterized cursor==



<source lang="sql">
SQL>
SQL> CREATE TABLE books(
2 book_id NUMBER NOT NULL PRIMARY KEY,
3 title VARCHAR2(200),
4 author VARCHAR2(200) );
Table created.
SQL>
SQL> INSERT INTO books VALUES(1, "Oracle SQL*Plus", "GENNICK,JONATHAN");
1 row created.
SQL>
SQL> INSERT INTO books VALUES(2, "Oracle PL/SQL Programming", "FEUERSTEIN,STEVEN");
1 row created.
SQL>
SQL> INSERT INTO books VALUES(3, "Oracle Built-in Packages", "FEUERSTEIN,STEVEN");
1 row created.
SQL>
SQL> set serveroutput on size 500000
SQL>
SQL> DECLARE
2
3 CURSOR books_cur(author_in IN books.author%TYPE) IS
4 SELECT *
5 FROM books
6 WHERE author = author_in;
7
8 book_count PLS_INTEGER;
9
10 BEGIN
11
12 FOR book_rec IN books_cur (author_in => "FEUERSTEIN, STEVEN")
13 LOOP
14 -- ... process data ...
15 book_count := books_cur%ROWCOUNT;
16 END LOOP;
17
18 IF book_count > 10
19 THEN
20 dbms_output.put_line("Lotsa books, time for vacation.");
21 ELSE
22 dbms_output.put_line("Keep writing slacker.");
23 END IF;
24
25 END;
26 /
Keep writing slacker.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> DROP TABLE books;
Table dropped.</source>

== Passing parameters to cursors==

<p>Basic Syntax for Passing Parameters in a Cursor</p>
<p>You can pass a literal value, as in</p>



<source lang="sql">
open c_emp (10);</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Cursor/Cursor Parameter - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия