Database Systems
Lab 08
PL SQL (Continue)
Cursor:
• Cursor are memory areas that allow you to allocate an area of
memory and access the information retrieved from SQL
statement.
• Example:
• You use a cursor to operate on all the rows of STUDENT table
for those students taking a particular course.
Types of Cursors:
• There are 2 types of cursors
1. Implicit Cursor
2. Explicit Cursor
Implicit Cursor:
• An implicit cursor is automatically declared by Oracle every time an
SQL statement is executed. The user will not be aware of this
happening and will not be able to control the process the
information in an implicit cursor.
• Example:
• SET SERVEROUTPUT ON;
• BEGIN
• UPDATE EMP SET JOB='Dr.' WHERE ENAME LIKE 'f%';
• DBMS_OUTPUT.PUT_LINE(SQL%rowcount);
• END;
• /
Explicit Cursor:
• You use cursor when you have a SELECT statement that
returns more than one row from the database. A cursor is
basically a set of rows that you can access one at a time.
Defining a Cursor
1. Declare variables to store column values from the SELECT
statement.
2. Declare the cursor, specifying your SELECT statement.
3. Open the Cursor.
4. Fetch the rows from the cursor.
5. Close the cursor.
STEP 1 : Declare variables to
store column values
These variables must be compatible with the column types.
DECLARE
v_Empno emp.empno%TYPE;
v_Ename emp.ename%TYPE;
v_Job emp.job%TYPE;
v_Deptno emp.deptno%TYPE;
v_Sal emp.sal%TYPE;
STEP 2: Declare the cursor
CURSOR cursor_name IS
SELECT_STATEMENT;
---
CURSOR cv_emp_cursor IS
SELECT empno,ename,sal
FROM emp
Order by empno;
STEP 3: Open the Cursor
• This step runs the SELECT statement. It must be placed in the
executable section of the block (BEGIN).
• OPEN cv_emp_cursor;
STEP 4: Fetch the rows from
the cursor
Syntax:
FETCH cursor_name
INTO variable[, variable. . . . ];
Example:
FETCH cv_emp_cursor
INTO v_Empno ,v_Ename , v_Job , v_Deptno ,v_Sal;
A cursor may have many rows; therefore, a loop is required to read each row in turn.
LOOP
FETCH CV_EMP_CURSOR
INTO V_EMPNO ,V_ENAME , V_JOB , V_DEPTNO ,V_SAL;
--exit the loop when there are no more rows, as indicated by
--the Boolean variable cv_emp_cursor%NOTFOUND (=true when
--there are no more rows)
EXIT WHEN cv_emp_cursor%NOTFOUND;
--use DBMS_OUTPUT.PUT_LINE () to display the variable
DBMS_OUTPUT.PUT_LINE(
‘v_Empno= ‘ || v_Empno ,’v_Ename=’ || v_ename ,’ v_Job= ‘ || v_job , ‘ v_Deptno= ‘
|| v_Deptno ,’v_Sal = ‘|| v_sal;
STEP 5: Close the Cursor
• Closing your cursors frees up system resources.
• CLOSE cv_emp_cursor;
Example 1
• SET SERVEROUTPUT ON;
• DECLARE
• c1 emp%rowtype;
• BEGIN
• SELECT * into c1 from emp where empno=7902;
• DBMS_OUTPUT.PUT_LINE(c1.empno || ' ' || c1.ename || ' '
|| c1.job);
• END;
•/
Example 2
• SET SERVEROUTPUT ON;
• DECLARE
• CURSOR c1 IS
• SELECT * from emp;
• cc1 c1%rowtype;
• BEGIN
• OPEN c1;
• LOOP
• FETCH c1 into cc1;
• exit when c1%NOTFOUND;
• DBMS_OUTPUT.PUT_LINE(cc1.empno || ' ' || cc1.ename || ' ' ||
cc1.job);
• END LOOP;
• CLOSE c1;
• END;
• /
Example 3
• SET SERVEROUTPUT ON;
• DECLARE
• CURSOR c1 IS
• SELECT * from emp ORDER BY SAL DESC;
• cc1 c1%rowtype;
• BEGIN
• OPEN c1;
• FOR i in 1 .. 5 LOOP
• FETCH c1 into cc1;
• exit when c1%NOTFOUND;
• DBMS_OUTPUT.PUT_LINE(cc1.empno || ' ' || cc1.ename || ' ' || cc1.job || '
'|| cc1.SAL );
• END LOOP;
• CLOSE c1;
• END;
• /
PL/SQL: Cursors and FOR
Loops
• You can use the power of FOR loop to access the rows in a
cursor
• When you use a FOR loop, you don’t have to explicitly open
and close the cursor------ the FOR loop does this automatically.
Example 2
DECLARE
CURSOR cv_emp_cursor2 IS
SELECT empno, ename,job
FROM emp WHERE deptno = 30;
BEGIN
FOR v_emp IN cv_emp_cursor2 LOOP
DBMS_OUTPUT.PUT_LINE(
‘EMPNO =’ || v_emp.empno || ‘,ename = ‘||
v_emp.ename || ‘,job = ‘|| v_emp.job );
END LOOP;
END;
Example 3
• DECLARE
• CURSOR EMP_CURSOR IS
• SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE
DEPARTMENT_ID = 30;
• EMP_RECORD EMP_CURSOR%ROWTYPE;
• BEGIN
• OPEN EMP_CURSOR;
• IF EMP_CURSOR%ISOPEN THEN
• LOOP
• FETCH EMP_CURSOR INTO EMP_RECORD;
• EXIT WHEN EMP_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_CURSOR%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.FIRST_NAME || ' ' ||
EMP_RECORD.LAST_NAME); END LOOP; END IF; CLOSE
EMP_CURSOR;
• END;
Views
• A view is a virtual table.
• A view contains rows and columns, just like a real table. The
fields in a view are fields from one or more real tables in the
database.
• You can add SQL functions, WHERE, and JOIN statements to a
view and present the data as if the data were coming from
one single table.
SQL CREATE VIEW Syntax
• CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
SQL CREATE VIEW Examples
• The view “emp_records" lists all active emp from the “emp"
table.
• The view is created with the following SQL:
• CREATE VIEW [emp_records] AS
SELECT *
FROM emp
WHERE deptno=30;
• SELECT * FROM [emp_records];
Another Example
create view abc AS
select * from emp where SAL > (select AVG(SAL) from emp);
Select * from abc;
create view abc1 AS
select DISTINCT JOB,SUM(SAL) AS total_sal FROM EMP
GROUP BY JOB;
Select * from abc1;
View from View
• CREATE VIEW abc2 AS
SELECT DISTINCT JOB , SUM(SAL) AS total_sal FROM abc
GROUP BY JOB;
SELECT * FROM abc2;
Procedures
• A procedure is a group of PL/SQL statements that you can call
by name.
Procedure: Syntax
• CREATE [OR REPLACE] PROCEDURE
procedure_name
• [(argument1 [mode1] datatype1,
• argument2 [mode2] datatype2,
• . . .)]
• IS|AS
• procedure_body;
Example 1
• CREATE OR REPLACE PROCEDURE greetings
• AS
• BEGIN
• dbms_output.put_line('Hello World!');
• END;
• /
• EXECUTE greetings;
• SET SERVEROUTPUT ON;
• BEGIN
• greetings;
• END;
• /
Procedure: Example
• create or replace procedure find_info(
• my_id IN emp.empno%type,
• my_name OUT emp.ename%type,
• my_job OUT emp.job%type ,
• my_sal OUT emp.sal%type
• )
• AS
• BEGIN
• select ename,job,sal into my_name,my_job,my_sal from emp
where empno=my_id;
• EXCEPTION
• WHEN OTHERS THEN
• dbms_output.put_line('Error ha yahan................');
• END find_info;
• /
Procedure compilation error.
• Show errors procedure [procedure_name];
Invoking the Procedure
• SET SERVEROUTPUT ON;
• DECLARE
• f_name emp.ename%type;
• f_job emp.job%type ;
• f_sal emp.sal%type;
• BEGIN
• find_info(7902,f_name,f_job,f_sal);
• dbms_output.put_line('student name is ' || f_name || ' job is
' || f_job || ' sal is ' || f_sal);
• END;
•/
Exercise
Views:
• Create a view, that stores information of only those
employees who belongs to Accounts department.
Cursors:
• Write a PL/SQL code to print out the employee information
who earns more than 2000 salary.
• Write a PL/SQL program displaying starting 10 employee
details
Procedures:
• To Write a PL / SQL program for creating a procedure for
calculating sum of two numbers. Execute it as well.
