SQL Cursor Attributes Database Management System

SQL Cursor Attributes Database Management System

Using SQL cursor attributes, you can test the outcome of your SQL statements.

SQL%ROWCOUNT Number of rows affected by the most recent SQL statement (an integer value) SQL%FOUND Boolean attribute that evaluates to TRUE if the most recent

SQL statement affects one or more rows

SQL%NOTFOUND Boolean attribute that evaluates to TRUE if the most recent SOL statement does not affect any rows

SQL%ISOPEN Always evaluates to FALSE because

PL/SQL closes implicit cursors immediately after they are executed

SQL Cursor Attributes                                        

SQL CURSOR ATTRIBUTES Database Management System : SQL Cursor attributes allow you to evaluate what happened when an implicit cursor was last used. Use these attributes in PL/SQL statements, but not in SQL statements.

You can use the attributes SQL%ROWCOUNT, SQL%FOUND, SOL% NOTFOUND, and SQL%ISOPEN in the exception section of a block together information about the execution of a DML statement.

PL/SQL does not return an error if a DML statement does not affect any rows in the underlying table. However, if a SELECT statement does not retrieve any rows, PL/SQL returns an exception.

SQL Cursor Attributes

Delete rows that have the specified emp ID from

the EMP table. Print the number of rows

deleted.

Example:

VARIABLE rows_deleted VARCHAR2(30)

DECARE

Var_emp_id emp..emp_id%TYPE := 176;

BEGIN

DELETE FROM emp

WHERE emp_id= var_emp_id;

:rows_deleted := (SQL%ROWCOUNT || ‘row deleted.’);

END;

/

PRINT rows_deleted

 SQL Cursor Attributes (continued)

The example on the slide deletes the rows from the EMP table for EMP_ID 176. Using the SQL%ROWCOUNT attribute, you can print the number of rows deleted.

Writing Control Structures

Controlling PL/SQL Flow of Execution

You can change the logical flow of statements within the PL/SQL block with a number of control structures. This lesson addresses three types of PL/SQL Control structures: Conditional constructs with the IF statement, CASE expressions, and LOOP control structures (covered later in this lesson).

There are three forms of IF statements:

  • IF-THEN-END IF
  • IF-THEN-ELSE-END IF
  • IF-THEN-ELSIF-END IF

IF Statements

IF condition THEN

statements;

[ELSIF condition THEN statements;]

[ELSE statements;)

END IF;

Syntax:

if the emp name is Gietz, set the Manager ID to 102.

IF UPPER(var_last_name) = “GIET Z” THEN

Var_mgr := 102;

END IF;

IF Statements

The structure of the PL/SOLIF statement is similar to the structure of IF statements in other procedural languages. It allows PL/SQL to perform actions selectively based on conditions.

In the syntax:

condition is a Boolean variable or expression (TRUE, FALSE, or NULL). (It is associated with a sequence of statements, which is executed only if the expression yields TRUE).

THEN is a clause that associates the Boolean expression that precedes it with the Sequence of Statements that follows it.

statements can be one or more PL/SQL or SOL statements. They may include further IF statements containing several nested IF, ELSE, and ELSIF statements.)

ELSIF is a keyword that introduces a Boolean expression. (If the first condition yields FALSE or NULL then the ELSIF keyword introduces additional conditions.)

ELSE is a keyword that executes the sequence of statements that follow it if the control reaches it.

Simple IF Statements

if the last name is Vargas:

Set job ID to SA_REP

Set department number to 80

IF var ename = “Vargas’ THEN

var job := ‘SA REP’;

var deptno := 80;

END IF;

Simple IF Statements

In the example on the slide, PL/SQL assigns values to the following variables, only if the condition is TRUE.

var_job and var_deptno

If the condition is FALSE or NULL, PL/SQL ignores the statements in the IF block. In either case, control resumes at the next statement in the program following the END IF.

Guidelines

  • You can perform actions selectively based on conditions that are being met.
  • When writing code, remember the spelling of the keywords:
    • ELSIF is One Word.
    • END IF is two words.
  • if the controlling Boolean condition is TRUE, the associated sequence of statements is executed; if the Controlling Boolean Condition is FALSE or NULL, the associated sequence of statements is passed over. Any number of ELSIF clauses are permitted.
  • İndent the conditionally executed Statements for clarity.

Compound IF Statements

Compound IF Statements use logical operators like AND and NOT. In the example on the slide, the IF Statement has two conditions to evaluate:

  • Last name should be Vargas
  • Sal should be greater than 6500

Only if both the above conditions are evaluated as TRUE, war deptno is set to 60.

Consider the following example:

IF var_department = ’60’ OR var hiredate > ‘O1-Dec-1999’ THEN

Var_mgr :=101;

END IF;

In the above example, the IF statement has two conditions to evaluate:

  • Department ID should be 60
  • Hire date should be greater than 01-Dec-1999
  • if either of the above conditions are evaluated as TRUE, warmgr is set to 101.

IF-THEN-ELSE Statement Execution Flow

While writing an IF construct, if the condition is FALSE or NULL, you can use the ELSE clause to Carry Out other actions. As with the simple IF statement, control resumes in the program from the END

IF clause. For example:

IF Condition 1 THEN

statement1;

ELSE

Statement2,

END IF;

Nested IF Statements

Either set of actions of the result of the first IF statement can include further IF statements before specific actions are performed. The THEN and ELSE clauses can include IF Statements. Each nested IF statement must be terminated with a corresponding END IF clause.

IF Condition 1 THEN

Statement1;

ELSE

IF Condition2 THEN

statement2;

END IF;

END IF;

Consider the following example: Check the value in the var_ename variable. If the value is King, set the var_job variable to AD_PRES. Otherwise, set the var_job variable to ST_CLERK.

IF var_ename = “King’ THEN

Var_job :=”AD_PRES’;

ELSE

Var_job = ‘ST_CLERK’;

END IF;

IF-THEN-ELSIF Statement Execution Flow

Sometimes you want to select an action from several mutually exclusive alternatives. The third form of IF statement uses the keyword ELSF (not ELSEIF) to introduce additional conditions, as follows:

IF Condition 1 THEN

sequence_of _statements1;

ELSEIF condition2; THEN

sequence_of _statements2;

ELSE

sequence_of statements3;

END IF;

Consider the following example: Determine an emp’s bonus based upon the

emp’s department.

IF var_deptno = 10 THEN

Var_bonus := 5000;

ELSIF var_deptno = 80 THEN

Var_bonus := 7500;

ELSE

var bonus:= 2000;

END IF;