Interacting with Oracle Server
- SQL Statements in PL/SQL
Use SQL Statements in PL/SQL to :
a. Retrieve a row from the database by using the SELECT Command
b. Make changes to rows in the database by using DML Commands
c. Control a Transaction with the COMMIT,ROLLBACK or SAVEPOINT Command.
- SELECT Statements in PL/SQL
Retrieve the Data from the Database with a SELECT Statement
Syntax :
SELECT
INTO {variable_name1,variable_name2}
FROM
WHERE condition
/
a. The INTO Clause is required and mandatory to select the records into the Variable
b. The SQL Statement must return only one row.
Ex.
— PL/SQL Block to display the Employee Name of Employee No. 7369 —
DECLARE
v_ename VARCHAR2(50);
BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE EMPNO = 7369;
DBMS_OUTPUT.PUT_LINE(‘Employee Name is :’||v_ename);
END;
/
— PL/SQL Block to display the Department Name of the Department No. 10 using an anonymous Block —
DECLARE
v_name VARCHAR2(30);
BEGIN
SELECT dname INTO v_name FROM dept
WHERE deptno = 10;
END;
/
The Anonymous Block gets the Name of the Department whose Department ID is 10 and stores it in a
variable v_name.
- Retrieving Data in PL/SQL
Ex.
— PL/SQL Block to display the Hiredate and Salary of Employee No. 7369 —
DECLARE
emp_hiredate EMP.HIREDATE%TYPE;
emp_sal EMP.SAL%TYPE;
BEGIN
SELECT hiredate,sal
INTO emp_hiredate,emp_sal
FROM EMP
WHERE empno = 7369;
DBMS_OUTPUT.PUT_LINE(‘Hiredate for Employee is : ‘||emp_hiredate||’ and Salary is :’||emp_sal);
END;
/
Ex.
— PL/SQL Block to return the sum of the salaries for all the Employees in the specified department —
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL:=20;
BEGIN
SELECT sum(sal)
INTO v_sum_sal
FROM emp
WHERE deptno = v_deptno;
DBMS_OUTPUT.PUT_LINE(‘Sum of Salary is : ‘||v_sum_sal);
END;
/
— PL/SQL Block to print the Employee Name who earns the Highest Salary in Department 10 —
DECLARE
v_ename emp.ename%TYPE;
BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp WHERE deptno = 10);
DBMS_OUTPUT.PUT_LINE(‘Employee ‘||v_ename||’ earns the highest Salary in Department 10′);
END;
/
- Naming Conventions
The following PL/SQL does not follow the naming conventions in PL/SQL.
DECLARE
v_sum_sal NUMBER(10,2);
deptno NUMBER NOT NULL:=20;
BEGIN
SELECT sum(sal) INTO v_sum_sal FROM emp WHERE deptno = deptno;
DBMS_OUTPUT.PUT_LINE(‘Sum of Salary is : ‘||v_sum_sal);
END;
/
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL:=20;
BEGIN
SELECT sum(sal) INTO v_sum_sal FROM emp WHERE deptno = v_deptno;
DBMS_OUTPUT.PUT_LINE(‘Sum of Salary is : ‘||v_sum_sal);
END;
/
In the above example the SQL Statement returns the Total Sum of Salary for all the departments even though in the WHERE Clause we have given the condition DEPTNO = DEPTNO (Here we are actually trying to retrive the Sum of Salary of Department 20)
Since the Variable Name is declared as DEPTNO which is an existing column name in EMP Table the Oracle Server takes precedence of the Database Table Column over the variable.
a. Use a naming convention to avoid ambiguity in the WHERE Clause.
b. Avoid using Database columns as identifiers i.e Variables Names.
c. Syntax errors can arise because PL/SQL checks the database first for a column in the table.
d. The names of local variables and formal parameters take precedence over the names of database tables.
e. The names of database table columns take precedence over the names of local variables
- Manipulating data using PL/SQL
We can make changes to a Database Tables using the following DML Commands :
a. INSERT
b. UPDATE
c. DELETE
d. MERGE
- Inserting Data
Ex.
— PL/SQL Block to add new Employee Information into the EMPLOYEES Table —
CREATE TABLE employees(id VARCHAR2(2))
/
SELECT * FROM employees
/
— Without the %TYPE —
DECLARE
v_empno VARCHAR2(2):=’&v’;
BEGIN
INSERT INTO employees(id)
VALUES(v_empno);
END;
/
UPDATE employees SET id = NULL
/
ALTER TABLE employees MODIFY(id VARCHAR2(1))
/
— Without the %TYPE —
DECLARE
v_empno VARCHAR2(2):=’&v’;
BEGIN
INSERT INTO employees(id)
VALUES(v_empno);
END;
/
— With the %TYPE —
DECLARE
v_empno employees.id%TYPE:=’&v’;
BEGIN
INSERT INTO employees(id)
VALUES(v_empno);
END;
/
SELECT * FROM employees
/
DROP TABLE employees PURGE
/
- Updating Data
Ex.
— PL/SQL Block to increase the Salary of all Employees who belong to Department 10 —
DECLARE
sal_increase employees.sal%TYPE:=800;
BEGIN
UPDATE employees
SET sal = sal + sal_increase
WHERE deptno = 10;
END;
/
SELECT * FROM EMPLOYEES
/
- Deleting Data
Ex.
— PL/SQL Block to delete rows that belong to department 10 from employees table —
DECLARE
v_deptno employees.deptno%TYPE:=10;
BEGIN
DELETE FROM employees
WHERE deptno = v_deptno;
END;
/
SELECT * FROM EMPLOYEES
/
DROP TABLE employees PURGE
/
- Merging Rows
Ex.
— PL/SQL Block to INSERT or UPDATE rows in the EMP13 Table to match the EMP Table —
SELECT * FROM emp
/
CREATE TABLE emp13 AS SELECT empno,ename FROM emp WHERE 1 = 2
/
INSERT INTO emp13(empno,ename)VALUES(7369,’Rahul’)
/
SELECT empno,ename FROM emp13
/
DECLARE
empno emp.empno%TYPE:=7369;
BEGIN
MERGE INTO emp13 C
USING emp E
ON (c.empno = e.empno)
WHEN MATCHED THEN
UPDATE SET
C.ENAME = E.ENAME
WHEN NOT MATCHED THEN
INSERT VALUES(E.EMPNO,E.ENAME);
END;
/
SELECT * FROM emp13
/
DROP TABLE emp13 PURGE
/
- SQL Cursor
a. An SQL Cursor is a pointer to the private memory area allocated by the Oracle Server.
b. A Cursor is a temporary work area created in the system memory when a SQL Statement is executed.
Cursor contains information on a SELECT Statement and the rows of data accessed by it.This temporary work area is used to store the data retrieved from the database and manipulate this data.
Cursor can hold more than one row but can process only one row at a time.
When the executable part of a PL/SQL Block issues a SQL Statement PL/SQL creates an implicit cursor which PL/SQL manages automatically.
The programmer explicitly declares and names an explicit cursor.
PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually.
c. There are two types of Cursors :
- Implicit – These are created and managed internally by the Oracle server to process SQL statements.
- Explicit – These are explicitly declared by the programmer.
- SQL Cursor attributes
Using SQL Cursor attributes we can test the outcome of our SQL Statements.
a. SQL%FOUND – Boolean attribute that evaluates to TRUE if the most recent SQL statement returned atleast one row.
If the DML statements like INSERT,DELETE,UPDATE affect one row this evaluates to TRUE.
b. SQL%NOTFOUND – Boolean attribute that evaluates to TRUE if the most recent SQL statement did not return even one row.
If the DML statements like INSERT,DELETE,UPDATE does not affect even one row this evaluates to TRUE.
c. SQL%ROWCOUNT – An integer value that represents the number of rows affected by the most recent SQL statement.
Returns the number of rows affected by the DML operations INSERT,DELETE,UPDATE.
Examples.
— PL/SQL Block to display the Total No.of Records from EMP Table using Cursors —
DECLARE
CURSOR c_count_emps IS
SELECT count(*)
FROM emp;
v_out_nr NUMBER;
BEGIN
OPEN c_count_emps;
FETCH c_count_emps INTO v_out_nr;
CLOSE c_count_emps;
DBMS_OUTPUT.PUT_LINE(‘Total No.of Records Retrieved is ‘||v_out_nr);
END;
/
DECLARE
CURSOR c1 IS SELECT empno,ename FROM emp;
v_empno NUMBER;
v_ename VARCHAR2(50);
BEGIN
OPEN c1;
FETCH c1 INTO v_empno,v_ename;
DBMS_OUTPUT.PUT_LINE(‘Employee Number : ‘||v_empno||’Employee Name : ‘||v_ename);
DBMS_OUTPUT.PUT_LINE(‘Total No.of Records Retrieved’||C1%ROWCOUNT);
CLOSE c1;
END;
/
— PL/SQL Block to display the Employee Name and his Salary using Cursor in PL/SQL —
DECLARE
v_ename VARCHAR2(40);
v_sal NUMBER;
CURSOR c1 IS SELECT ename,sal FROM EMP WHERE empno = 7369;
BEGIN
OPEN c1;
FETCH c1 INTO v_ename,v_sal;
DBMS_OUTPUT.PUT_LINE(‘Employee Name is ‘||v_ename);
DBMS_OUTPUT.PUT_LINE(‘Salary is ‘||v_sal);
DBMS_OUTPUT.PUT_LINE(‘No.of Records retrieved is : ‘||c1%ROWCOUNT);
CLOSE c1;
END;
/
— PL/SQL Block to display the Employee Name and his Salary using more than one Cursor in PL/SQL —
DECLARE
v_empno NUMBER;
v_ename VARCHAR2(40);
v_sal NUMBER;
CURSOR c1 IS SELECT ename,sal FROM EMP WHERE empno = 7369;
CURSOR c2 IS SELECT empno FROM EMP WHERE empno = 7369;
BEGIN
— Open the First Cursor —
OPEN c1;
FETCH c1 INTO v_ename,v_sal;
DBMS_OUTPUT.PUT_LINE(‘Employee Name is ‘||v_ename);
DBMS_OUTPUT.PUT_LINE(‘Salary is ‘||v_sal);
DBMS_OUTPUT.PUT_LINE(‘No.of Records retrieved is : ‘||c1%ROWCOUNT);
— Open the Second Cursor without Closing the First Cursor —
OPEN c1;
/*
FETCH c1 INTO v_ename;
DBMS_OUTPUT.PUT_LINE(‘Employee No is ‘||v_empno);
DBMS_OUTPUT.PUT_LINE(‘No.of Records retrieved is : ‘||c1%ROWCOUNT);
CLOSE c1;
*/
END;
/
— PL/SQL Block to display the Employee Name and his Salary using Cursor in PL/SQL — Use %FOUND and %NOTFOUND
DECLARE
v_ename VARCHAR2(40);
v_sal NUMBER;
CURSOR c1 IS SELECT ename,sal FROM EMP WHERE empno = 8000;
BEGIN
OPEN c1;
FETCH c1 INTO v_ename,v_sal;
IF c1%FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Records Retrieved Successfully’);
DBMS_OUTPUT.PUT_LINE(‘No.of Records retrieved is : ‘||c1%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(‘Employee Name is ‘||v_ename);
DBMS_OUTPUT.PUT_LINE(‘Salary is ‘||v_sal);
ELSIF c1%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Records are not Retrieved Successfully’);
END IF;
CLOSE c1;
END;
/
— PL/SQL Block to delete rows that have the specified Employee ID from the Employees Table. Print the no.of rows deleted —
DROP TABLE EMPLOYEES PURGE
/
CREATE TABLE EMPLOYEES AS SELECT * FROM EMP
/
VARIABLE ROWS_DELETED VARCHAR2(30)
SET AUTOPRINT ON
DECLARE
V_DEPTNO employees.deptno%TYPE:=10;
BEGIN
DELETE FROM employees
WHERE deptno = v_deptno;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(‘None of the Records were deleted’);
ELSIF SQL%FOUND THEN
:ROWS_DELETED:=(SQL%ROWCOUNT||’ Rows deleted.’);
END IF;
END;
/