Oracle stored procedure-cursor

Stored Procedures, learned how to use cursors, and learned how to process data. Implicit cursor select into from

All implicit cursors are assumed to return only one record.

You do not need to declare, open, or close an implicit cursor. PL/SQL implicitly opens, processes, and closes the cursor.

 

Create or replace procedure pro_two (p_id in integer) is -- declare v_description varchar2 (132): = ''; v_code lsy_codebook.codecode % type; v_codeorder number: = 0; v_temp varchar2 (132 ): = ''; begin -- select into from obtain the select codecode, description into v_code, v_description from lsy_codebook where id = p_id; If v_code is null then raise no_data_found; elsif v_code = 'qw' then begin update lsy_codebo OK set description = v_description | 'changed 'Where id = p_id; commit; dbms_output.put_line (p_id |': Update successful '); end; else begin -- execute immediate is similar to the eval method in JS. Here is a separate example. -- It uses the string as an SQL command for execution. Note that single quotes are escaped. -- In a string, writing two single quotes in a row will be converted into single quotes, for example, ''is '. -- Similar to Java, escape v_description: = v_description | 'else changed '; v_temp: = 'in in update lsy_codebook set description = ''' | v_description | ''' where id = '| p_id |'; commit; end ;'; dbms_output.put_line (v_temp); execute immediate 'in in update lsy_codebook set description = ''' | v_description | '''where id = '| p_id |'; commit; end; '; dbms_output.put_line (p_id |': Update succeeded '); end if; -- Exception capture exception when no_data_found then begin dbms_output.put_line ('no _ data_found. '); end; When too_many_rows then begin dbms_output.put_line ('too _ many_rows. '); end; when others then begin rollback; end pro_two;

In the preceding statement, pay attention to the execute immediate usage. Its function is similar to eval in Js. The advantage is self-evident. PLSQL commands can be dynamically passed in Java programs.

Note the escape of single quotes. The two adjacent single quotes are escaped into a single quotes character in the string.

 

Cursor loop exit when

Create or replace procedure pro_three (p_code in varchar2) is cCode lsy_codebook.codecode % type; cname lsy_codebook.codename % type; cnum number: = 0; cursor mycur is select codecode, codename from lsy_codebook where codecode like '%' | p_code | '%'; begin select count (*) into cnum from lsy_codebook where codecode like '%' | p_code | '%'; dbms_output.put_line ('result set: '| cnum | 'barri'); open mycur; -- Open Loop fetch mycur into cCode, cname; dbms_output.put_line ('field information: '| cCode | ''| cname); exit when mycur % notfound; end loop; close mycur; -- disable dbms_output.put_line ('over'); -- Exception capture exception when no_data_found then dbms_output.put_line ('no _ data_found. '); When too_many_rows then dbms_output.put_line ('too _ many_rows. '); when others then rollback; end pro_three;

 

 

Cursor for loop for v_temp in mycur Loop

Create or replace procedure pro_four (p_code in varchar2) is cnum number: = 0; cursor mycur is select codecode, codename from lsy_codebook where codecode like '%' | p_code | '%'; begin select count (*) into cnum from lsy_codebook where codecode like '%' | p_code | '%'; dbms_output.put_line ('result set: '| cnum | 'barri'); -- open mycur; -- note that the cursor cannot be opened here, or the temporary variable v_temp will be skipped directly. Do not declare for v_temp in mycur loop dbms_output.put_line ('field information: '| v_temp.codecode | ''| v_temp.codename); End loop; -- close mycur; -- cannot write close -- Test for loop for v_temp2 in 1 .. 5 loop dbms_output.put_line ('test for loop: '| v_temp2); End loop; terminate ('over'); -- Exception capture exception when no_data_found then dbms_output.put_line ('no _ data_found. '); When too_many_rows then dbms_output.put_line ('too _ many_rows. '); when others then rollback; end pro_four;

 

Print:

Result set: 3

Field Information: Gd Guangdong

Field Information: GX Guangxi

Field Information: Gz Guizhou

Test for loop: 1

Test for loop: 2

Test for loop: 3

Test for loop: 4

Test for loop: 5

Over

 

Cursor while loop while mycur % found Loop

Create or replace procedure pro_five (p_code in varchar2) is cCode lsy_codebook.codecode % type; cname encoding % type; cnum number: = 0; CINT number: = 0; cursor mycur is select codecode, codename from lsy_codebook where codecode like '%' | p_code | '%'; begin select count (*) into cnum from lsy_codebook where codecode like '%' | p_code | '%'; dbms_output.put_line ('result set: '| cnum | 'barri'); open mycur; -- The cursor/* While should be written in the cursor. In addition to while, you must first execute a fetch mycur into statement. Because the condition is mycur % found, if you do not execute this sentence first, this condition will inevitably fail, and the loop will be skipped directly. */Fetch mycur into cCode, cname; while mycur % found loop fetch mycur into cCode, cname; dbms_output.put_line ('field info: '| cCode | ''| cname ); end loop; close mycur; -- close -- test while loop while CINT <5 loop CINT: = CINT + 1; dbms_output.put_line ('test while loop: '| CINT); End loop; dbms_output.put_line ('over'); -- Exception capture exception when no_data_found then dbms_output.put_line ('no _ data_found. '); When too_many_rows then dbms_output.put_line ('too _ many_rows. '); when others then rollback; end pro_five;

 

Print:

Result set: 3

Field Information: GX Guangxi

Field Information: Gz Guizhou

Field Information: Gz Guizhou

Test while loop: 1

Test while loop: 2

Test while loop: 3

Test while loop: 4

Test while loop: 5

Over

 

Cursor mycur (c_param varchar2) is

Create or replace procedure pro_six (p_code in varchar2) is -- cCode lsy_codebook.codecode % type; -- cname lsy_codebook.codename % type; cnum number: = 0; CINT number: = 0; -- The cursor mycur (c_param varchar2) with parameters is select codecode, codename from lsy_codebook where codecode like '%' | c_param | '%'; begin select count (*) into cnum from lsy_codebook where codecode like '%' | p_code | '%'; dbms_output.put_line ('result set: '| cnum |' bar '); -- open mycur (p_code); -- cursor, input parameter dbms_output.put_line ('cursor parameter: '| p_code); For v_temp in mycur (p_code) loop dbms_output.put_line (' field information: '| v_temp.codecode | ''| v_temp.codename); End loop; -- close mycur; -- disable dbms_output.put_line ('over '); -- Exception capture exception when no_data_found then dbms_output.put_line ('no _ data_found. '); When too_many_rows then dbms_output.put_line ('too _ many_rows. '); when others then rollback; end pro_six;

 

Print:

Result set: 3

Cursor parameter: G

Field Information: Gd Guangdong

Field Information: GX Guangxi

Field Information: Gz Guizhou

Over

Ref cursor

Create or replace procedure pro_seven (p_code in varchar2, p_method number) is cCode lsy_codebook.codecode % type; cname usage % type; cdescription lsy_codebook.description % type; Type type_mycursor is ref cursor; cnum number: = 0; -- ref cursor mycur type_mycursor; begin if p_method = 1 then begin --- Select count (*) into cnum from lsy_codebook where codecode like '%' | p_code | '%'; dbms_output.put_line ('P _ method: '| p_method |' result set: '| cnum | 'string'); open mycur for select codecode, codename from lsy_codebook where codecode like' % '| p_code |' % '; if mycur % isopen then begin loop fetch mycur into cCode, cname; exit when mycur % notfound; -- open mycur for open cursor cannot use for loop statement dbms_output.put_line ('field information: '| cCode | ''| cname); End loop; end if; --- end; else begin ----- select count (*) into cnum from lsy_codebook where codecode like '%' | p_code | '%'; dbms_output.put_line ('P _ method: '| p_method |' result set: '| cnum | 'bar'); open mycur for select codecode, description from lsy_codebook where codecode like' % '| p_code |' % '; if mycur % isopen then begin loop fetch mycur into cCode, cdescription; exit when mycur % notfound; -- open mycur for open cursor cannot use for loop statement dbms_output.put_line ('field information: '| cCode | ''| cdescription); End loop; end if; ----- end; end if; dbms_output.put_line ('over '); -- Exception capture exception when no_data_found then dbms_output.put_line ('no _ data_found. '); When too_many_rows then dbms_output.put_line ('too _ many_rows. '); when others then rollback; end pro_seven;

 

P_method: 1 result set: 3

Field Information: Gd Guangdong

Field Information: GX Guangxi

Field Information: Gz Guizhou

Over

P

P_method: 2 result set: 3

Field Information: The GD database does not have this record! Changed else changed

Field Information: GX test description 22

Field Information: Gz test description 11

Over

 

Attached test table data:

-- Create Table
Create Table lsy_codebook
(
ID number (19) not null,
Codetypecode varchar2 (20 ),
Codeorder number (10 ),
Codecode varchar2 (60 ),
Codename varchar2 (120 ),
Builddate date,
Description varchar2 (100)
)
Tablespace users
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64 K
Next 1 m
Minextents 1
Maxextents Unlimited
);

ID	Codetypecode	Codeorder	Codecode	Codename	Builddate	Description
500	SQ	1	QW	Network-wide	0:08:05	Changed
501	SQ	2	GD	Guangdong	9:52:10	The database does not have this record! Changed else changed
502	SQ	3	GX	Guangxi	17:54:03	Test Description 22
503	SQ	4	YN	Yunnan	8:03:02	Test Description 33
504	SQ	5	GZ	Guizhou	9:52:10	Test Description 11
505	SQ	6	Hn	Hainan	9:52:10	Test Description 5555
505	SQ	7	Hn	Hainan	9:52:10	Test Description 6666666