-
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 ............
-
In PL/SQL, the SELECT into statement returns only one row of data.. If a row of data is exceeded, an explicit cursor is used (we will discuss the cursor later). The INTO clause must contain variables 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 assignment method is very useful.
For example, the data TYPE and size of the column referenced by the variable 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
-
WHERE empno = p_empno;
-
UPDATE former_emp
-
SET date_deleted = SYSDATE
-
WHERE empno = p_empno;
-
-
EXCEPTION
-
WHEN NO_DATA_FOUND THEN
-
DBMS_OUTPUT.PUT_LINE ('employee Number Not Found! ');
-
END
-
-
DML statement results
-
After a DML statement is executed, the results of the DML statement are stored in four cursor attributes, which are used to control the program flow or understand the program status. When running a DML statement, PL/SQL opens a built-in cursor and processes the result. The cursor is an area in the memory that maintains the query result. The cursor is opened when running the DML statement and closed after completion.
Implicit cursor onlySQL % FOUND, SQL % NOTFOUND, SQL % ROWCOUNT three attributes. SQL % FOUND, SQL % NOTFOUND is a Boolean value, and SQL % ROWCOUNT is an integer.
-
-
SQL % FOUND and SQL % NOTFOUND
-
Before executing any DML statement, the values of SQL % FOUND and SQL % NOTFOUND are NULL. After executing the DML statement, the attribute values of SQL % FOUND will be:
-
-
. TRUE: INSERT
-
. TRUE: DELETE and UPDATE. At least one row is deleted or updated.
-
. TRUE: select into returns at least one row.
-
When SQL % FOUND is TRUE, SQL % NOTFOUND is FALSE.
-
-
SQL % ROWCOUNT
-
Before executing any DML statement, the SQL % ROWCOUNT value is NULL. For select into statements, if the execution is successful, the SQL % ROWCOUNT value is 1. If the execution fails, the SQL % ROWCOUNT value is 0, and an exception NO_DATA_FOUND is generated.
-
-
SQL % ISOPEN
-
SQL % ISOPEN is a Boolean value. If the cursor is opened, it is TRUE. If the cursor is closed, it is FALSE. for implicit cursors, SQL % ISOPEN is always FALSE. This is because the implicit cursors are opened when DML statements are executed and are immediately closed at the end.
-
-
Transaction control statement
-
A transaction is a logical unit of work that can contain one or more DML statements. Transaction Control helps you ensure data consistency. If any DML statement in the TRANSACTION control logic unit fails, the entire TRANSACTION will be rolled back. In PL/SQL, you can use COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION statements explicitly.
-
The COMMIT statement terminates the transaction, permanently stores changes to the database, releases all locks, and ROLLBACK terminates the current transaction to release all locks, but does not save any changes to the database. The SAVEPOINT is used to set the intermediate point, when a TRANSACTION calls too many database operations, the intermediate point is very useful. set transaction is used to set transaction attributes, such as read-write and isolation level.
-
-
Explicit cursor
-
When a query returns more than one row, an explicit cursor is required. You cannot use the select into statement. PL/SQL manages implicit cursors. When the query starts, the implicit cursor is opened. When the query ends, the implicit cursor is automatically closed. The explicit cursor is declared in the declaration part of the PL/SQL block. It is opened in the execution part or Exception Handling part, data is retrieved, and disabled.
-
-
Use cursor
-
Here we need to make a declaration. The cursor we refer to usually refers to an explicit cursor. Therefore, we have not specified any specific cursor from now on. To use a cursor in a program, you must first declare the cursor.
-
-
Declared cursor
-
Syntax:
-
CURSOR cursor_name IS select_statement;
-
-
In PL/SQL, the cursor name is an un-declared variable and cannot be assigned a value to the cursor name or used in an expression.
-
-
Example:
-
DELCARE
-
CURSOR C_EMP is select empno, ename, salary
-
FROM emp
-
WHERE salary> 2000
-
Order by ename;
-
........
-
BEGIN
-
In the definition of a cursor, a SELECT statement does not have to have a table that can be a view. You can also SELECT columns from multiple tables or views, or even use * to SELECT all columns.
-
-
Open cursor
-
Before using the values in the cursor, you should first open the cursor and open the cursor to initialize query processing. The syntax for opening a cursor is:
-
OPEN cursor_name
-
Cursor_name is the cursor name defined in the Declaration section.
-
-
Example:
-
OPEN C_EMP;
-
-
Close cursor
-
Syntax:
-
CLOSE cursor_name
-
-
Example:
-
CLOSE C_EMP;
-
-
Extract data from cursor
-
Use the FETCH Command to obtain a row of data from the cursor. After each data extraction, the cursor points to the next row of the result set. Syntax:
-
FETCH cursor_name INTO variable [, variable,...]
-
For each column of the cursor defined by SELECT, The FETCH Variable list should have a variable corresponding to it, and the variable type should be the same.
-
-
Example:
-
SET SERVERIUTPUT ON
-
DECLARE
-
V_ename EMP. ENAME % TYPE;
-
V_salary EMP. SALARY % TYPE;
-
CURSOR c_emp is select ename, salary FROM emp;
-
BEGIN
-
OPEN c_emp;
-
FETCH c_emp INTO v_ename, v_salary;
-
DBMS_OUTPUT.PUT_LINE ('salary of employee' | v_ename | 'ais '| v_salary );
-
FETCH c_emp INTO v_ename, v_salary;
-
DBMS_OUTPUT.PUT_LINE ('salary of employee' | v_ename | 'ais '| v_salary );
-
FETCH c_emp INTO v_ename, v_salary;
-
DBMS_OUTPUT.PUT_LINE ('salary of employee' | v_ename | 'ais '| v_salary );
-
CLOSE c_emp;
-
END
-
-
This code is undoubtedly very troublesome. If multiple rows return results, you can use the loop and use the cursor attribute as the condition for ending the loop to extract data in this way, the readability and conciseness of the program are greatly improved. Next we will re-write the following program in a loop:
-
SET SERVERIUTPUT ON
-
DECLARE
-
V_ename EMP. ENAME % TYPE;
-
V_salary EMP. SALARY % TYPE;
-
CURSOR c_emp is select ename, salary FROM emp;
-
BEGIN
-
OPEN c_emp;
-
LOOP
-
FETCH c_emp INTO v_ename, v_salary;
-
Exit when c_emp % NOTFOUND;
-
DBMS_OUTPUT.PUT_LINE ('salary of employee' | v_ename | 'ais '| v_salary );
-
END
-
-
Record variable
-
Define a record variable using the TYPE command and % ROWTYPE. For more information about % ROWsTYPE, see related materials.
-
Record variables are used to extract data rows from the cursor. When multiple columns are selected for the cursor, it is much easier to use records than to declare a variable for each column.
-
When % ROWTYPE is used in the table and the value retrieved from the cursor is put into the record, if you want to select all columns in the table, therefore, using * in a SELECT clause is much safer than listing all columns.
-
-
Example:
-
SET SERVERIUTPUT ON
-
DECLARE
-
R_emp EMP % ROWTYPE;
-
CURSOR c_emp is select * FROM emp;
-
BEGIN
-
OPEN c_emp;
-
LOOP
-
FETCH c_emp INTO r_emp;
-
Exit when c_emp % NOTFOUND;
-
DBMS_OUT.PUT.PUT_LINE ('salary of Employee '| r_emp.ename |' is '| r_emp.salary );
-
End loop;
-
CLOSE c_emp;
-
END;
-
-
% ROWTYPE can also be defined by the cursor name. In this way, the cursor must be declared first:
-
-
SET SERVERIUTPUT ON
-
DECLARE
-
CURSOR c_emp is select ename, salary FROM emp;
-
R_emp c_emp % ROWTYPE;
-
BEGIN
-
OPEN c_emp;
-
LOOP
-
FETCH c_emp INTO r_emp;
-
Exit when c_emp % NOTFOUND;
-
DBMS_OUT.PUT.PUT_LINE ('salary of Employee '| r_emp.ename |' is '| r_emp.salary );
-
End loop;
-
CLOSE c_emp;
-
END;
-
-
Parameter-based cursor
-
Similar to stored procedures and functions, you can pass parameters to the cursor and use them in the query. This is useful for processing the case where a cursor is opened under certain conditions. Its syntax is as follows:
-
-
CURSOR cursor_name [(parameter [, parameter],...)] IS select_statement;
-
-
Syntax for defining parameters:
-
Parameter_name [IN] data_type [{: = | DEFAULT} value]
-
-
Unlike stored procedures, a cursor can only accept passed values, but cannot return values. The parameter only defines the data type and has no size.
-
In addition, you can set a default value for the parameter. When no parameter value is passed to the cursor, the default value is used. The parameter defined in the cursor is just a placeholder. It is not necessarily reliable to reference it elsewhere.
-
-
Assign a value to the parameter when you open the cursor. The syntax is as follows:
-
-
OPEN cursor_name [value [, value]...];
-
The parameter value can be text or variable.
-
-
Example:
-
DECALRE
-
CURSOR c_dept is select * FROM dept order by deptno;
-
CURSOR c_emp (p_dept VARACHAR2) IS
-
SELECT ename, salary
-
FROM emp
-
WHERE deptno = p_dept
-
Order by ename
-
R_dept DEPT % ROWTYPE;
-
V_ename EMP. ENAME % TYPE;
-
V_salary EMP. SALARY % TYPE;
-
V_tot_salary EMP. SALARY % TYPE;
-
BEGIN
-
OPEN c_dept;
-
LOOP
-
FETCH c_dept INTO r_dept;
-
Exit when c_dept % NOTFOUND;
-
DBMS_OUTPUT.PUT_LINE ('department: '| r_dept.deptno |'-'| r_dept.dname );
-
V_tot_salary: = 0;
-
OPEN c_emp (r_dept.deptno );
-
LOOP
-
FETCH c_emp INTO v_ename, v_salary;
-
Exit when c_emp % NOTFOUND;
-
DBMS_OUTPUT.PUT_LINE ('name: '| v_ename | 'salary:' | v_salary );
-
V_tot_salary: = v_tot_salary + v_salary;
-
End loop;
-
CLOSE c_emp;
-
DBMS_OUTPUT.PUT_LINE ('toltal Salary for dept: '| v_tot_salary );
-
End loop;
-
CLOSE c_dept;
-
END;
-
-
-
-
Cursor FOR Loop
-
Most of the time we design a program, we follow the steps below:
-
1. Open the cursor
-
2. Start Loop
-
3. values from the cursor
-
4. Check that the row is returned.
-
5. Processing
-
6. close the loop
-
7. Close the cursor
-
You can simply call this type of code a cursor for a loop. However, there is another type of loop, which is a FOR loop,
The cursor used for the for loop is declared in the normal declaration mode. It has the advantages of not explicitly opening, closing, retrieving data, testing data existence, defining data storage variables, and so on.. The syntax of the cursor FOR loop is as follows:
-
-
FOR record_name IN
-
(Corsor_name [(parameter [, parameter]...)]
-
| (Query_difinition)
-
LOOP
-
Statements
-
End loop;
-
-
The example above is rewritten using a for Loop:
-
DECALRE
-
CURSOR c_dept is select deptno, dname FROM dept order by deptno;
-
CURSOR c_emp (p_dept VARACHAR2) IS
