How to use Oracle cursor

The following articles mainly introduce the specific operation methods of Oracle cursor. If you are a beginner in the practical application of Oracle cursor, you can use the following articles to better understand how to use Oracle cursor correctly. The following is a detailed description of the article.

Oracle cursor usage

We will discuss various DDL and TCL statements used to access the Oracle database.

Query

The SELECT statement is used to query data from the database. When the SELECT statement is used in PL/SQL, it must be used with the INTO clause. The return value of the query is assigned to the variable in the INTO clause, variable declaration is in DELCARE. The select into syntax is as follows:

 
 

  
  
SELECT [DISTICT|ALL]{*|column[,column,...]}  
  
  
INTO (variable[,variable,...] |record)  
  
  
FROM {table|(sub-query)}[alias]  
  
  
WHERE............  
 
 

When Oracle cursor is used, only one row of data is returned by the SELECT statement in PL/SQL. If there is more than one row of data, we will discuss the cursor using an explicit cursor later). The INTO clause must have a variable with the same number of columns as the SELECT clause. The INTO clause can also be a record variable.

% TYPE attribute

In PL/SQL, you can declare variables and constants as built-in or user-defined data types to reference a column name and inherit its data types and sizes. This dynamic value assignment method is very useful. For example, the data TYPE and size of the columns referenced by variables have changed. If % TYPE is used, you do not need to modify the code, otherwise, you must modify the code.

Example:

 
 

  
  
v_empno SCOTT.EMP.EMPNO%TYPE;  
  
  
v_salary EMP.SALARY%TYPE;  
 
 

Not only can the column name use % TYPE, but also variables, cursors, records, or declared constants can use % TYPE. This is useful for defining variables of the same data type.

 
 

  
  
DELCARE  
  
  
V_A NUMBER(5):=10;  
  
  
V_B V_A%TYPE:=15;  
  
  
V_C V_A%TYPE;  
  
  
BEGIN  
  
  
DBMS_OUTPUT.PUT_LINE  
  
  
('V_A='||V_A||'V_B='||V_B||'V_C='||V_C);  
  
  
END  
  
  
SQL>/  
  
  
V_A=10 V_B=15 V_C=  
  
  
PL/SQL procedure successfully completed.  
  
  
SQL>  
 
 

Other DML statements

The DML statements for other data operations are INSERT, UPDATE, DELETE, and lock table. the syntax of these statements in PL/SQL is the same as that in SQL. We have discussed the usage of DML statements before. In DML statements, you can use any variable declared in the DECLARE section. If it is a nested block, pay attention to the scope of the variable.

Example:

 
 

  
  
CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)  
  
  
AS  
  
  
v_ename EMP.ENAME%TYPE;  
  
  
BEGIN  
  
  
SELECT ename INTO v_ename  
  
  
FROM emp  
  
  
WHERE empno=p_empno;  
  
  
INSERT INTO FORMER_EMP(EMPNO,ENAME)  
  
  
VALUES (p_empno,v_ename);  
  
  
DELETE FROM emp   
 
 

The above content is an introduction to the use of Oracle cursors. I hope you will gain some benefits.