Select statement
SET SERVEROUTPUT ON
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) — group function
INTO v_sum_sal
FROM employees
WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE (’The sum salary is ‘ ||
TO_CHAR(v_sum_sal));
END;
SQL Cursor
• A cursor is a private SQL work area.
• There are two types of cursors:
– Implicit cursors
– Explicit cursors
• The Oracle server uses implicit cursors to parse and execute your SQL statements.
• Explicit cursors are explicitly declared by the programmer.
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 oneor more rows
SQL%NOTFOUND ====== Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any rows
SQL%ISOPEN ====== Always evaluates to FALSEbecause PL/SQL closes implicit cursors immediately after they are executed
SQL Cursor Attributes
Delete rows that have the specified employee ID from the EMPLOYEEStable. Print the number of rows deleted.
Example:
VARIABLE rows_deleted VARCHAR2(30)
DECLARE
v_employee_id employees.employee_id%TYPE := 176;
BEGIN
DELETE FROM employees
WHERE employee_id = v_employee_id;
:rows_deleted := (SQL%ROWCOUNT ||
‘ row deleted.’);
END;
/
PRINT rows_deleted
Transaction Control Statements
• Initiate a transaction with the first DML command to follow a COMMITor ROLLBACK.
• Use COMMITand ROLLBACKSQL statements to terminate a transaction explicitly.
Embed SQL in the PL/SQL block using SELECT, INSERT, UPDATE, DELETE, and MERGE
• Embed transaction control statements in a PL/SQL block COMMIT, ROLLBACK, and SAVEPOINT
