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

Vernier concept

When you execute SELECT, INSERT, DELETE, and UPDATE statements in a PL/SQL block, ORACLE allocates a Context Area, or buffer, to it in memory. A cursor is a pointer to a region, either naming a Work Area, or a structured data type. It provides a method to deal with each row of data in the query result set of multi-row data separately for the application of equality view, and it is a common programming method for designing applications with embedded SQL statements.

In each user session, you can open multiple cursors at the same time, the number of which is defined by the OPEN_CURSORS parameter in the database initialization parameter file.

Cursors are used differently for different SQL statements:

SQL statement cursor

Non-query statement implicit

The result is implicit or displayed in a single-line query

The result is displayed by a multi-line query statement

Explicit cursor processing

Explicit cursor processing requires four PL/SQL steps:

Define / declare cursors: define a cursor name and its corresponding SELECT statement.

Format:

CURSOR cursor_name [(parameter [, parameter] …)]

[RETURN datatype]

IS

Select_statement

Cursor parameters can only be input parameters in the format:

Parameter_name [IN] datatype [{: = | DEFAULT} expression]

You cannot use a length constraint when specifying a data type. Such as NUMBER (4), CHAR (10) and so on are all wrong.

[RETURN datatype] is optional and represents the data returned by the cursor. If selected, it should strictly match the selection list in select_statement in order and data type. Typically record data types or data with "% ROWTYPE".

L Open the cursor: execute the SELECT statement corresponding to the cursor, put its query results into the workspace, and point the pointer to the head of the workspace to identify the cursor result set. If the cursor query statement has the FOR UPDATE option, the OPEN statement also locks the data row corresponding to the cursor result set in the database table.

Format:

OPEN cursor_name [([parameter = >] value [, [parameter = >] value] …)]

When passing parameters to the cursor, you can use the same method of passing values as the function parameters, that is, position representation and name representation. A PL/SQL program cannot repeatedly open a cursor with an OPEN statement.

L extract cursor data: retrieve the rows of data in the result set and put them in the specified output variable.

Format:

FETCH cursor_name INTO {variable_list | record_variable}

When you execute a FETCH statement, you return one row of data at a time, and then automatically move the cursor to the next row of data. When the last row of data is retrieved, if the FETCH statement is executed again, the operation fails and the cursor property% NOTFOUND is set to TRUE. So after each execution of the FETCH statement, checking the cursor property% NOTFOUND can determine whether the FETCH statement was executed successfully and return a row of data to determine whether the corresponding variable is assigned a value.

L process the record

L continue processing until there are no records in the active collection

Close the cursor: when the cursor result set data is extracted and processed, the cursor should be closed in time to release the system resources occupied by the cursor and invalidate the work area of the cursor, so that the FETCH statement can no longer be used to fetch the data. Closed cursors can be reopened using the OPEN statement.

Format:

CLOSE cursor_name

Note: a defined cursor cannot have an INTO clause.

Example 1. Query the information of the top 10 employees.

C:\ Users\ Administrator > sqlplus hr/hr@pdbtest

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 18 10:09:21 2017

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production

SQL > desc employees

Name Null? Type

EMPLOYEE_ID NOT NULL NUMBER (6)

FIRST_NAME VARCHAR2 (20)

LAST_NAME NOT NULL VARCHAR2 (25)

EMAIL NOT NULL VARCHAR2 (25)

PHONE_NUMBER VARCHAR2 (20)

HIRE_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2 (10)

SALARY NUMBER (8 dint 2)

COMMISSION_PCT NUMBER (2Jing 2)

MANAGER_ID NUMBER (6)

DEPARTMENT_ID NUMBER (4)

SQL > declare

2 cursor c_sursor

3 is select FIRST_NAME | | LAST_NAME,SALARY

4 from employees

5 where rownum list 5

5 where rownum c / 11/11

5 where rownum list 7

7 v_sal employees.FIRST_SALARY%type

SQL > c / FIRST_SALARY/SALARY

7 v_sal employees.SALARY%type

SQL > 13

13 fetch fetch c_sursor into v_ename,v_sal

SQL > c/fetch fetch/fetch

13 fetch c_sursor into v_ename,v_sal

SQL > run

1 declare

2 cursor c_sursor

3 is select FIRST_NAME | | LAST_NAME,SALARY

4 from employees

5 where rownum set serveroutput on

SQL > run

1 declare

2 cursor c_sursor

3 is select FIRST_NAME | | LAST_NAME,SALARY

4 from employees

5 where rownum set serveroutput on

SQL > run

1 DECLARE

two

3 DeptRec DEPARTMENTS%ROWTYPE

four

5 Dept_name DEPARTMENTS.DEPARTMENT_NAME%TYPE

six

7 Dept_loc DEPARTMENTS.LOCATION_ID%TYPE

eight

9 CURSOR c1 IS

ten

11 SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS

twelve

13 WHERE DEPARTMENT_ID DECLARE

two

3 v_f_name employees.first_name%TYPE

four

5 v_h_date employees.hire_date%TYPE

six

7 CURSOR c2 (dept_id NUMBER, j_id VARCHAR2)-declares cursors with parameters but no return value

eight

9 IS

ten

11 SELECT first_name, hire_date FROM employees

twelve

13 WHERE department_id = dept_id AND job_id = j_id

fourteen

15 BEGIN

sixteen

17 OPEN c2 (90, 'AD_VP');-- Open the cursor and pass parameter values

eighteen

19 LOOP

twenty

21 FETCH c2 INTO v_f_name, vastly hacked;-- extract cursors

twenty-two

23 IF c2%FOUND THEN

twenty-four

25 DBMS_OUTPUT.PUT_LINE (v_f_name | | 'date of employment is' | | v_h_date)

twenty-six

27 ELSE

twenty-eight

29 DBMS_OUTPUT.PUT_LINE ('finished processing result set')

thirty

31 EXIT

thirty-two

33 END IF

thirty-four

35 END LOOP

thirty-six

37 CLOSE c2;-- close the cursor

thirty-eight

39 END

40 /

The employment date for Neena is 2005-09-21 00:00:00.

The employment date for Lex is 2001-01-13 00:00:00.

The result set has been processed.

PL/SQL procedure successfully completed.

Example 6: a cursor with parameters and a return value.

SQL > DECLARE

two

3 TYPE emp_record_type IS RECORD (

four

5 f_name employees.first_name%TYPE

six

7 h_date employees.hire_date%TYPE)

eight

9 v_emp_record EMP_RECORD_TYPE

ten

eleven

twelve

13 CURSOR c3 (dept_id NUMBER, j_id VARCHAR2)-declare cursors with parameters and return values

fourteen

15 RETURN EMP_RECORD_TYPE

sixteen

17 IS

eighteen

19 SELECT first_name, hire_date FROM employees

twenty

21 WHERE department_id = dept_id AND job_id = j_id

twenty-two

23 BEGIN

twenty-four

25 OPEN c3 (j_id = > 'AD_VP', dept_id = > 90);-- Open the cursor and pass parameter values

twenty-six

27 LOOP

twenty-eight

29 FETCH c3 INTO vastly emptied record;-- extract cursors

thirty

31 IF c3%FOUND THEN

thirty-two

33 DBMS_OUTPUT.PUT_LINE (v_emp_record.f_name | | 'date of employment is'

thirty-four

35 | | v_emp_record.h_date)

thirty-six

37 ELSE

thirty-eight

39 DBMS_OUTPUT.PUT_LINE ('finished processing result set')

forty

41 EXIT

forty-two

43 END IF

forty-four

45 END LOOP

forty-six

47 CLOSE c3;-- close the cursor

forty-eight

49 END

50 /

The employment date for Neena is 2005-09-21 00:00:00.

The employment date for Lex is 2001-01-13 00:00:00.

The result set has been processed.

PL/SQL procedure successfully completed.

Example 7: define record variables based on cursors.

SQL > DECLARE

two

3 CURSOR c4 (dept_id NUMBER, j_id VARCHAR2)-declares cursors with parameters but no return value

four

5 IS

six

7 SELECT first_name f_name, hire_date FROM employees

eight

9 WHERE department_id = dept_id AND job_id = j_id

ten

11-defining record variables based on cursors is more convenient than declaring record type variables and is not error-prone.

twelve

13 v_emp_record c4%ROWTYPE

fourteen

15 BEGIN

sixteen

17 OPEN c4 (90, 'AD_VP');-- Open the cursor and pass parameter values

eighteen

19 LOOP

twenty

21 FETCH c4 INTO vastly emptied record;-- extract cursors

twenty-two

23 IF c4%FOUND THEN

twenty-four

25 DBMS_OUTPUT.PUT_LINE (v_emp_record.f_name | | 'date of employment is'

twenty-six

27 | | v_emp_record.hire_date)

twenty-eight

29 ELSE

thirty

31 DBMS_OUTPUT.PUT_LINE ('finished processing result set')

thirty-two

33 EXIT

thirty-four

35 END IF

thirty-six

37 END LOOP

thirty-eight

39 CLOSE c4;-- close the cursor

forty

41 END

42 /

The employment date for Neena is 2005-09-21 00:00:00.

The employment date for Lex is 2001-01-13 00:00:00.

The result set has been processed.

PL/SQL procedure successfully completed.

FOR loop of cursor

The PL/SQL language provides the functions of cursor FOR loop statements, automatic execution of cursor OPEN, FETCH, CLOSE statements and loop statements. When entering the loop, the cursor FOR loop statement automatically opens the cursor and extracts the first row of cursor data. When the program processes the current extracted data and enters the next loop, the cursor FOR loop statement automatically extracts the next row of data for the program to process, ends the loop when all the data rows in the result set are extracted, and automatically closes the cursor.

Format:

FOR index_variable IN cursor_name [(value [, value] …)] LOOP

-- cursor data processing code

END LOOP

Where:

Index_variable is an index variable implicitly declared by a cursor FOR loop statement, which is a record variable with the same structure as the set of structures returned by the cursor query statement. The extracted cursor data can be read by referencing the index record variable element in the program, and the name of each element in index_variable is the same as the column name in the cursor query statement selection list. If there are calculated columns in the selection list of the cursor query statement, the calculated columns must be aliased before they can be accessed through the index variables in the cursor FOR loop statement.

Note: do not manually manipulate cursors in the program; do not define records in the program to control the FOR loop.

SQL > set serveroutput on

SQL > DECLARE

2 CURSOR c_sal IS SELECT employee_id, first_name | | last_name ename, salary

3 FROM employees

4 BEGIN

5-implicitly open cursors

6 FOR v_sal IN c_sal LOOP

7-implicitly execute a FETCH statement

8 DBMS_OUTPUT.PUT_LINE (to_char (v_sal.employee_id) | |'- -'| v_sal.ename | |'- -'| | to_char (v_sal.salary))

9-implied monitoring c_sal%NOTFOUND

10 END LOOP

11-implicitly close cursors

12 END

13 /

100---StevenKing---24000

101---NeenaKochhar---17000

102---LexDe Haan---17000

103---AlexanderHunold---9000

104---BruceErnst---6000

105---DavidAustin---4800

106---ValliPataballa---4800

107---DianaLorentz---4200

108---NancyGreenberg---12008

109---DanielFaviet---9000

110---JohnChen---8200

111---IsmaelSciarra---7700

112---Jose ManuelUrman---7800

113---LuisPopp---6900

114---DenRaphaely---11000

115---AlexanderKhoo---3100

116---ShelliBaida---2900

117---SigalTobias---2800

118---GuyHimuro---2600

119---KarenColmenares---2500

120---MatthewWeiss---8000

121---AdamFripp---8200

122---PayamKaufling---7900

123---ShantaVollman---6500

124---KevinMourgos---5800

125---JuliaNayer---3200

126---IreneMikkilineni---2700

127---JamesLandry---2400

128---StevenMarkle---2200

129---LauraBissot---3300

130---MozheAtkinson---2800

131---JamesMarlow---2500

132---TJOlson---2100

133---JasonMallin---3300

134---MichaelRogers---2900

135---KiGee---2400

136---HazelPhiltanker---2200

137---RenskeLadwig---3600

138---StephenStiles---3200

139---JohnSeo---2700

140---JoshuaPatel---2500

141---TrennaRajs---3500

142---CurtisDavies---3100

143---RandallMatos---2600

144---PeterVargas---2500

145---JohnRussell---14000

146---KarenPartners---13500

147---AlbertoErrazuriz---12000

148---GeraldCambrault---11000

149---EleniZlotkey---10500

150---PeterTucker---10000

151---DavidBernstein---9500

152---PeterHall---9000

153---ChristopherOlsen---8000

154---NanetteCambrault---7500

155---OliverTuvault---7000

156---JanetteKing---10000

157---PatrickSully---9500

158---AllanMcEwen---9000

159---LindseySmith---8000

160---LouiseDoran---7500

161---SarathSewall---7000

162---ClaraVishney---10500

163---DanielleGreene---9500

164---MatteaMarvins---7200

165---DavidLee---6800

166---SundarAnde---6400

167---AmitBanda---6200

168---LisaOzer---11500

169---HarrisonBloom---10000

170---TaylerFox---9600

171---WilliamSmith---7400

172---ElizabethBates---7300

173---SunditaKumar---6100

174---EllenAbel---11000

175---AlyssaHutton---8800

176---JonathonTaylor---8600

177---JackLivingston---8400

178---KimberelyGrant---7000

179---CharlesJohnson---6200

180---WinstonTaylor---3200

181---JeanFleaur---3100

182---MarthaSullivan---2500

183---GirardGeoni---2800

184---NanditaSarchand---4200

185---AlexisBull---4100

186---JuliaDellinger---3400

187---AnthonyCabrio---3000

188---KellyChung---3800

189---JenniferDilly---3600

190---TimothyGates---2900

191---RandallPerkins---2500

192---SarahBell---4000

193---BritneyEverett---3900

194---SamuelMcCain---3200

195---VanceJones---2800

196---AlanaWalsh---3100

197---KevinFeeney---3000

198---DonaldOConnell---2600

199---DouglasGrant---2600

200---JenniferWhalen---4400

201---MichaelHartstein---13000

202---PatFay---6000

203---SusanMavris---6500

204---HermannBaer---10000

205---ShelleyHiggins---12008

206---WilliamGietz---8300

PL/SQL procedure successfully completed.

Example 9: when a declared cursor has parameters, parameters are passed for the cursor through the cursor FOR loop statement.

SQL > DECLARE

two

3 CURSOR c_cursor (dept_no NUMBER DEFAULT 10)

four

5 IS

six

7 SELECT department_name, location_id FROM departments WHERE department_id BEGIN

2 FOR c1_rec IN (SELECT department_name, location_id FROM departments) LOOP

3 DBMS_OUTPUT.PUT_LINE (c1_rec.department_name | |'- -'| | c1_rec.location_id)

4 END LOOP

5 END

6 /

Administration---1700

Marketing---1800

Purchasing---1700

Human Resources---2400

Shipping---1500

IT---1400

Public Relations---2700

Sales---2500

Executive---1700

Finance---1700

Accounting---1700

Treasury---1700

Corporate Tax---1700

Control And Credit---1700

Shareholder Services---1700

Benefits---1700

Manufacturing---1700

Construction---1700

Contracting---1700

Operations---1700

IT Support---1700

NOC---1700

IT Helpdesk---1700

Government Sales---1700

Retail Sales---1700

Recruiting---1700

Payroll---1700

PL/SQL procedure successfully completed.

4.1.2 dealing with implicit cursors

Explicit cursors are mainly used to deal with query statements, especially when the query results are multiple records; while for non-query statements, such as modify and delete operations, the ORACLE system automatically sets cursors for these operations and creates its workspace. These cursors implicitly created by the system are called implicit cursors, and the name of the implicit cursor is SQL, which is defined by the ORACLE system. The operations of implicit cursors, such as defining, opening, valuing and closing, are done automatically by the ORACLE system without the need for users to deal with. The user can only complete the corresponding operation through the relevant properties of the implicit cursor. In the workspace of an implicit cursor, the data stored is the data contained in a newly processed SQL statement that is independent of the user-defined display cursor.

The format call is: SQL%

Note: cursors do not need to be explicitly defined in INSERT, UPDATE, DELETE, SELECT statements.

Example 11: delete all employees of a department in the EMPLOYEES table, or delete the department in the DEPARTMENT table if there are no employees in that department.

SQL > DECLARE

2 V_deptno employees.department_id%TYPE: = & p_deptno

3 BEGIN

4 DELETE FROM employees WHERE department_id=v_deptno

5 IF SQL%NOTFOUND THEN

6 DELETE FROM departments WHERE department_id=v_deptno

7 END IF

8 END

9 /

Enter value for p_deptno: 1000

Old 2: V_deptno employees.department_id%TYPE: = & p_deptno

New 2: V_deptno employees.department_id%TYPE: = 1000

PL/SQL procedure successfully completed.

Example 12: find out how many lines have been modified through the% ROWCOUNT attribute of the implicit cursor SQL.'

SQL > DECLARE

2 v_rows NUMBER

3 BEGIN

4-Update data

5 UPDATE employees SET salary = 30000

6 WHERE department_id = 90 AND job_id = 'AD_VP'

7-get the property value of the default cursor

8 v_rows: = SQL%ROWCOUNT

9 DBMS_OUTPUT.PUT_LINE ('updated' | | v_rows | | 'salary per employee')

10-roll back the update to keep the data in the database as it is

11 ROLLBACK

12 END

13 /

Updated the wages of two employees

4.1.3 differences between NO_DATA_FOUND and% NOTFOUND

SELECT... INTO statement triggers NO_DATA_FOUND

Trigger% NOTFOUND when the WHERE clause of an explicit cursor is not found

Trigger SQL%NOTFOUND; when the WHERE clause of the UPDATE or DELETE statement is not found. In the extraction loop, use% NOTFOUND or% FOUND to determine the exit condition of the loop, not NO_DATA_FOUND.

4.1.4 use cursors to update and delete data

Cursor modification and deletion operations refer to the modification or deletion of specified data rows in the table under cursor positioning. At this point, it is required that the FOR UPDATE option be used in the cursor query statement to lock all and part of the columns of the cursor result set corresponding to the data rows in the table when the cursor is opened.

In order not to change the row being processed (queried) by another user, ORACLE provides a FOR UPDATE clause to lock the selected row. This requirement forces ORACLE to lock the rows of the cursor result set, preventing other transactions from updating or deleting the same rows until your transaction commits or rollback.

Syntax:

SELECT column_list FROM table_list FOR UPDATE [OF column [, column]...] [NOWAIT]

If another session has locked the rows in the active set, the SELECT FOR UPDATE operation waits until the other session releases the locks before continuing its own operation. In this case, when the NOWAIT clause is added, if the rows are really locked by another session, OPEN immediately returns and gives:

ORA-0054: resource busy and acquire with nowait specified.

If you use FOR UPDATE to declare cursors, you can use them in DELETE and UPDATE statements

The WHERE CURRENT OF cursor_ name clause modifies or deletes the rows of data in the database table corresponding to the current row of the cursor result set.

Example 13: query the employees of a department from the EMPLOYEES table and set their minimum salary at 1500

SQL > run

1 DECLARE

2 V_deptno employees.department_id%TYPE: = & p_deptno

3 CURSOR emp_cursor

4 IS

5 SELECT employees.employee_id, employees.salary

6 FROM employees WHERE employees.department_id=v_deptno

7 FOR UPDATE NOWAIT

8 BEGIN

9 FOR emp_record IN emp_cursor LOOP

10 IF emp_record.salary

< 1500 THEN 11 UPDATE employees SET salary=1500 12 WHERE CURRENT OF emp_cursor; 13 END IF; 14 END LOOP; 15 -- COMMIT; 16* END; Enter value for p_deptno: 100 old 2: V_deptno employees.department_id%TYPE :=&p_deptno; new 2: V_deptno employees.department_id%TYPE :=100; PL/SQL procedure successfully completed. 例14：将EMPLOYEES表中部门编码为90、岗位为AD_VP的雇员的工资都更新为2000元； SQL>

DECLARE

2 v_emp_record employees%ROWTYPE

3 CURSOR c1

4 IS

5 SELECT * FROM employees FOR UPDATE

6 BEGIN

7 OPEN c1

8 LOOP

9 FETCH c1 INTO v_emp_record

10 EXIT WHEN c1%NOTFOUND

11 IF v_emp_record.department_id = 90 AND

12 v_emp_record.job_id = 'AD_VP'

13 THEN

14 UPDATE employees SET salary = 20000

15 WHERE CURRENT OF C1;-- updates the data row corresponding to the current cursor row

16 END IF

17 END LOOP

18-COMMIT;-submit modified data

19 CLOSE c1

20 END

21 /

PL/SQL procedure successfully completed.

SQL > SELECT * FROM employees where department_id = 90 and job_id = 'AD_VP'

EMPLOYEE_ID FIRST_NAME LAST_NAME

EMAIL PHONE_NUMBER HIRE_DATE JOB_ID

SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID 101 Neena Kochhar

NKOCHHAR 515.123.4568 2005-09-21 00:00:00 AD_VP

20000 100 90

102 Lex De Haan

LDEHAAN 515.123.4569 2001-01-13 00:00:00 AD_VP

20000 100 90

EMPLOYEE_ID FIRST_NAME LAST_NAME

EMAIL PHONE_NUMBER HIRE_DATE JOB_ID

SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

4.2 Vernier variables

Like cursors, a cursor variable is a pointer to the current row of data in a multi-row query result set. But unlike cursors, cursor variables are dynamic and cursors are static. Cursors can only be connected to a specified query, that is, a fixed point to the memory processing area of a query, while cursor variables can be connected to different query statements. it can point to the memory processing regions of different query statements (but not multiple memory processing regions at the same time, and can only be connected to one query statement at a time), as long as the return types of these query statements are compatible.

4.2.1 declare cursor variables

The cursor variable is a pointer, which belongs to the reference type, so the cursor variable type must be defined before declaring the cursor variable type. In PL/SQL, cursor variable types can be defined within the declaration areas of blocks, subroutines, and packages.

The syntax format is:

TYPE ref_type_name IS REF CURSOR

[RETURN return_type]

Where: ref_type_name is the newly defined cursor variable type name

Return_type is the return type of a cursor variable, and it must be a record variable.

When defining cursor variable types, you can use both strong type definition and weak type definition. The strong type definition must specify the return type of the cursor variable, while the weak type definition does not specify the return type.

The two steps of declaring a cursor variable:

Step 1: define a REF CURSOU data type, such as:

TYPE ref_cursor_type IS REF CURSOR

Step 2: declare a cursor variable of this data type, such as:

Cv_ref REF_CURSOR_TYPE

Example: create two strongly typed definition cursor variables and one weakly typed cursor variable:

DECLARE

TYPE deptrecord IS RECORD (

Deptno departments.department_id%TYPE

Dname departments.department_name%TYPE

Loc departments.location_id%TYPE

);

TYPE deptcurtype IS REF CURSOR RETURN departments%ROWTYPE

TYPE deptcurtyp1 IS REF CURSOR RETURN deptrecord

TYPE curtype IS REF CURSOR

Dept_c1 deptcurtype

Dept_c2 deptcurtyp1

Cv curtype

4.2.2 Vernier variable operation

Like cursors, cursor variable operations include three steps: open, extract, and close.

1. Open the cursor variable

OPEN is used when opening cursor variables. FOR statement. The format is:

OPEN {cursor_variable_name |: host_cursor_variable_name}

FOR select_statement

Where cursor_variable_name is the cursor variable and host_cursor_variable_name is the cursor variable declared in the PL/SQL host environment (such as OCI: ORACLE Call Interface,Pro*c program, etc.).

OPEN... The FOR statement can reopen the cursor variable before closing the current cursor variable without causing a CURSOR_ALREAD_OPEN exception error. When a new cursor variable is opened, the memory processing area of the previous query is freed.

2. Extract cursor variable data

Use the fetch statement to extract data from the result set of cursor variables. The format is:

FETCH {cursor_variable_name |: host_cursor_variable_name}

INTO {variable [, variable]... | | record_variable} |

Where cursor_variable_name and host_cursor_variable_name are cursor variable and host cursor variable names, respectively; variable and record_variable are normal variable and record variable names, respectively.

3. Close the cursor variable

The CLOSE statement closes the cursor variable in the format:

CLOSE {cursor_variable_name |: host_cursor_variable_name}

Where: cursor_variable_name and host_cursor_variable_name are cursor variable and host cursor variable names, respectively, and if the application tries to close an unopened cursor variable, it will cause an INVALID_CURSOR exception error.

Example 15: strongly typed reference cursor variable type

SQL > DECLARE

2 TYPE emp_job_rec IS RECORD (

3 Employee_id employees.employee_id%TYPE

4 Employee_name employees.first_name%TYPE

5 Job_title employees.job_id%TYPE

6)

7 TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec

8 Emp_refcur emp_job_refcur_type

9 Emp_job emp_job_rec

10 BEGIN

11 OPEN emp_refcur FOR

12 SELECT employees.employee_id, employees.first_name | | employees.last_name, employees.job_id

13 FROM employees

14 ORDER BY employees.department_id

fifteen

16 FETCH emp_refcur INTO emp_job

17 WHILE emp_refcur%FOUND LOOP

18 DBMS_OUTPUT.PUT_LINE (emp_job.employee_id | |':'| | emp_job.employee_name | |'is a'| | emp_job.job_title)

19 FETCH emp_refcur INTO emp_job

20 END LOOP

21 END

22 /

200: JenniferWhalen is an AD_ASST

201: MichaelHartstein is a MK_MAN

202: PatFay is a MK_REP

114: DenRaphaely is a PU_MAN

115: AlexanderKhoo is a PU_CLERK

116: ShelliBaida is a PU_CLERK

117: SigalTobias is a PU_CLERK

118: GuyHimuro is a PU_CLERK

119: KarenColmenares is a PU_CLERK

203: SusanMavris is a HR_REP

120: MatthewWeiss is a ST_MAN

121: AdamFripp is a ST_MAN

122: PayamKaufling is a ST_MAN

123: ShantaVollman is a ST_MAN

124: KevinMourgos is a ST_MAN

125: JuliaNayer is a ST_CLERK

126: IreneMikkilineni is a ST_CLERK

127: JamesLandry is a ST_CLERK

128: StevenMarkle is a ST_CLERK

129: LauraBissot is a ST_CLERK

130: MozheAtkinson is a ST_CLERK

131: JamesMarlow is a ST_CLERK

132: TJOlson is a ST_CLERK

133: JasonMallin is a ST_CLERK

134: MichaelRogers is a ST_CLERK

135: KiGee is a ST_CLERK

136: HazelPhiltanker is a ST_CLERK

137: RenskeLadwig is a ST_CLERK

138: StephenStiles is a ST_CLERK

139: JohnSeo is a ST_CLERK

140: JoshuaPatel is a ST_CLERK

141: TrennaRajs is a ST_CLERK

142: CurtisDavies is a ST_CLERK

143: RandallMatos is a ST_CLERK

144: PeterVargas is a ST_CLERK

180: WinstonTaylor is a SH_CLERK

181: JeanFleaur is a SH_CLERK

182: MarthaSullivan is a SH_CLERK

183: GirardGeoni is a SH_CLERK

184: NanditaSarchand is a SH_CLERK

185: AlexisBull is a SH_CLERK

186: JuliaDellinger is a SH_CLERK

187: AnthonyCabrio is a SH_CLERK

188: KellyChung is a SH_CLERK

189: JenniferDilly is a SH_CLERK

190: TimothyGates is a SH_CLERK

191: RandallPerkins is a SH_CLERK

192: SarahBell is a SH_CLERK

193: BritneyEverett is a SH_CLERK

194: SamuelMcCain is a SH_CLERK

195: VanceJones is a SH_CLERK

196: AlanaWalsh is a SH_CLERK

197: KevinFeeney is a SH_CLERK

198: DonaldOConnell is a SH_CLERK

199: DouglasGrant is a SH_CLERK

103: AlexanderHunold is an IT_PROG

104: BruceErnst is an IT_PROG

105: DavidAustin is an IT_PROG

106: ValliPataballa is an IT_PROG

107: DianaLorentz is an IT_PROG

204: HermannBaer is a PR_REP

145: JohnRussell is a SA_MAN

146: KarenPartners is a SA_MAN

147: AlbertoErrazuriz is a SA_MAN

148: GeraldCambrault is a SA_MAN

149: EleniZlotkey is a SA_MAN

150: PeterTucker is a SA_REP

151: DavidBernstein is a SA_REP

152: PeterHall is a SA_REP

153: ChristopherOlsen is a SA_REP

154: NanetteCambrault is a SA_REP

155: OliverTuvault is a SA_REP

156: JanetteKing is a SA_REP

157: PatrickSully is a SA_REP

158: AllanMcEwen is a SA_REP

159: LindseySmith is a SA_REP

160: LouiseDoran is a SA_REP

161: SarathSewall is a SA_REP

162: ClaraVishney is a SA_REP

163: DanielleGreene is a SA_REP

164: MatteaMarvins is a SA_REP

165: DavidLee is a SA_REP

166: SundarAnde is a SA_REP

167: AmitBanda is a SA_REP

168: LisaOzer isa SA_REP

169: HarrisonBloom is a SA_REP

170: TaylerFox is a SA_REP

171: WilliamSmith is a SA_REP

172: ElizabethBates is a SA_REP

173: SunditaKumar is a SA_REP

174: EllenAbel is a SA_REP

175: AlyssaHutton is a SA_REP

176: JonathonTaylor is a SA_REP

177: JackLivingston is a SA_REP

179: CharlesJohnson is a SA_REP

100: StevenKing is an AD_PRES

101: NeenaKochhar is an AD_VP

102: LexDe Haan is an AD_VP

108: NancyGreenberg is a FI_MGR

109: DanielFaviet is a FI_ACCOUNT

110: JohnChen is a FI_ACCOUNT

111: IsmaelSciarra is a FI_ACCOUNT

112: Jose ManuelUrman is a FI_ACCOUNT

113: LuisPopp is a FI_ACCOUNT

205: ShelleyHiggins is an AC_MGR

206: WilliamGietz is an AC_ACCOUNT

178: KimberelyGrant is a SA_REP

PL/SQL procedure successfully completed.

Example 16: weak types refer to cursor variable types

SQL > PROMPT

SQL > PROMPT 'What table would you like to see?'

'What table would you like to see?'

SQL > ACCEPT tab PROMPT'(D) epartment, or (E) mployees:'

(d) epartment, or (E) mployees:

SQL > DECLARE

2 Type refcur_t IS REF CURSOR

3 Refcur refcur_t

4 TYPE sample_rec_type IS RECORD (

5 Id number

6 Description VARCHAR2 (30)

7)

8 sample sample_rec_type

9 selection varchar2 (1): = UPPER (SUBSTR ('& tab', 1,1))

10 BEGIN

11 IF selection='D' THEN

12 OPEN refcur FOR

13 SELECT departments.department_id, departments.department_name FROM departments

14 DBMS_OUTPUT.PUT_LINE ('Department data')

15 ELSIF selection='E' THEN

16 OPEN refcur FOR

17 SELECT employees.employee_id, employees.first_name | |'is a'| | employees.job_id FROM employees

18 DBMS_OUTPUT.PUT_LINE ('Employee data')

19 ELSE

20 DBMS_OUTPUT.PUT_LINE ('Please enter', 'dating','or 'and' estrangement')

21 RETURN

22 END IF

23 DBMS_OUTPUT.PUT_LINE ('-')

24 FETCH refcur INTO sample

25 WHILE refcur%FOUND LOOP

26 DBMS_OUTPUT.PUT_LINE (sample.id | |':'| | sample.description)

27 FETCH refcur INTO sample

28 END LOOP

29 CLOSE refcur

30 END

31 /

Old 9: selection varchar2 (1): = UPPER (SUBSTR ('& tab', 1,1))

New 9: selection varchar2 (1): = UPPER (SUBSTR (', 1,1))

Please enter'D' or'E'

Example 17: using cursor variables (no RETURN clause)

SQL > DECLARE

2-define a cursor data type

3 TYPE emp_cursor_type IS REF CURSOR

4-declare a cursor variable

5 c1 EMP_CURSOR_TYPE

6-declare two record variables

7 v_emp_record employees%ROWTYPE

8 v_reg_record regions%ROWTYPE

nine

10 BEGIN

11 OPEN C1 FOR SELECT FROM employees WHERE department_id = 20

12 LOOP

13 FETCH c1 INTO v_emp_record

14 EXIT WHEN c1%NOTFOUND

15 DBMS_OUTPUT.PUT_LINE (v_emp_record.first_name | | 'date of employment is' | | v_emp_record.hire_date)

16 END LOOP

17-- map the same cursor variable to another SELECT statement

18 OPEN C1 FOR SELECT FROM regions WHERE region_id in (1BI 2)

19 LOOP

20 FETCH c1 INTO v_reg_record

21 EXIT WHEN c1%NOTFOUND

22 DBMS_OUTPUT.PUT_LINE (v_reg_record.region_id | | 'means' | | v_reg_record.region_name)

23 END LOOP

24 CLOSE c1

25 END

26 /

The employment date for Michael is 2004-02-17 00:00:00.

The employment date for Pat is 2005-08-17 00:00:00.

1 means Europe

2 means Americas

PL/SQL procedure successfully completed.

Example 18: using cursor variables (with RETURN clause)

SQL > DECLARE

2-define a record data type that is the same as these columns in the employees table

3 TYPE emp_record_type IS RECORD (

4 f_name employees.first_name%TYPE

5 h_date employees.hire_date%TYPE

6 j_id employees.job_id%TYPE)

7-declare a record variable of this record data type

8 v_emp_record EMP_RECORD_TYPE

9-define a cursor data type

10 TYPE emp_cursor_type IS REF CURSOR

11 RETURN EMP_RECORD_TYPE

12-declare a cursor variable

13 c1 EMP_CURSOR_TYPE

14 BEGIN

15 OPEN c1 FOR SELECT first_name, hire_date, job_id

16 FROM employees WHERE department_id = 20

17 LOOP

18 FETCH c1 INTO v_emp_record

19 EXIT WHEN c1%NOTFOUND

20 DBMS_OUTPUT.PUT_LINE ('employee name:' | | v_emp_record.f_name

21 | | 'date of employment:' | | v_emp_record.h_date

22 | | 'position:' | | v_emp_record.j_id)

23 END LOOP

24 CLOSE c1

25 END

26 /

Employee name: Michael Employment date: 2004-02-17 00:00:00 Job: MK_MAN

Employee name: Pat Employment date: 2005-08-17 00:00:00 Job: MK_REP

PL/SQL procedure successfully completed.

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.