Simple use of Dameng Vernier
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
