Shulou(Shulou.com)06/01 Report--

1 display cursor

Create table T1 (sex varchar2 (10), name varchar2 (20))

Insert into T1 values ('male', 'Xiao Liu')

Insert into T1 values ('female', 'Xiao Chen')

Insert into T1 values ('female', 'Xiaoyan')

Insert into T1 values ('female', 'Xiao Hong')

Commit

Select * from T1

DECLARE

CURSOR c_t1_cursor is select sex, name from T1 where sex= 'female'

V_sex t1. Sex type

V_name t1. Name type

Begin

Open c_t1_cursor

Loop

Fetch c_t1_cursor into v_sex, v_name

Exit when c_t1_cursor%notfound

Print (v_name | |'is'| | v_sex)

End loop

Close c_t1_cursor

End

Note: the definition of the cursor should be defined in the definition section of the anonymous block, and the cursor opening, extracting data, and closing are all in the execution part.

2-parameter cursor

Syntax:

CURSOR cursor_name

[(parameter_name datatype,...)]

IS

Select_statement

.

OPEN cursor_name (parameter_value,.)

DECLARE

CURSOR c_t1_cursor (c_sex varchar2 (10)) is select sex, name from T1 where sex=c_sex

V_sex t1. Sex%type

V_name t1. Name%type

Begin

Open c_t1_cursor ('male')

Loop

Fetch c_t1_cursor into v_sex, v_name

Exit when c_t1_cursor%notfound

Print (v_name | |'is'| | v_sex)

End loop

Close c_t1_cursor

End

Note: open c_t1_cursor ('male') can also be changed to open c_t1_cursor (& sex)

3 Vernier for loop

Syntax:

FOR record_name IN cursor_name | select_statement LOOP

Statement1

Statement2

.

END LOOP

Begin

For t1_record in (select sex, name from T1 where sex= 'female') loop

Print (t1_record. Name | |'is'| | t1_record. Sex)

End loop

End

4 Vernier expression

Syntax:

TYPE ref_type_name IS REF CURSOR [RETURN return_type]

Cursor_variable ref_type_name

Ref_type_name: used to specify a custom type name

RETURN: used to specify the data type to return the result

Cursor_variable: used to specify the cursor variable name

DECLARE

TYPE t1_cursor IS REF CURSOR

My_cursor t1_cursor

V_sex t1. Sex%type

V_name t1. Name%type

Begin

OPEN my_cursor FOR select sex, name from T1 where sex= 'female'

LOOP

FETCH my_cursor INTO v_sex, v_name

EXIT WHEN my_cursor%NOTFOUND

Print (v_name | |'is'| | v_sex)

End loop

Close my_cursor

End

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.