Link youtube to learn :
##
Oracle Advanced SQL Optimization Techniques
blogs.oracle.com
Cursor for loop :
SELECT-INTO
SELECT-INTO offers the fastest and simplest way to fetch a single row from a SELECT statement. The syntax of this statement is
SELECT select_list INTO variable_list FROM remainder_of_query;
where remainder_of_query contains the list of tables or views, the WHERE clause, and other clauses of the query. The number and types of elements in the variable_list must match those of the select_list.
If the SELECT statement identifies more than one row to be fetched, Oracle Database will raise the TOO_MANY_ROWS exception. If the statement doesn’t identify any rows to be fetched, Oracle Database will raise the NO_DATA_FOUND exception.
Here are some examples of using SELECT-INTO:
Get the last name for a specific employee ID (the primary key in the employees table):
DECLARE
l_last_name employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_last_name
FROM employees
WHERE employee_id = 138;
DBMS_OUTPUT.put_line (
l_last_name);
END;
If there is a row in the employees table with ID 138, this block will display the last name of that employee. If there is no such row, the block will fail with an unhandled NO_DATA_FOUND exception. Assuming that a unique index is defined on the employee_id column, this block will never raise the TOO_MANY_ROWS exception.
Fetch an entire row from the employees table for a specific employee ID:
DECLARE
l_employee employees%ROWTYPE;
BEGIN
SELECT *
INTO l_employee
FROM employees
WHERE employee_id = 138;
DBMS_OUTPUT.put_line (
l_employee.last_name);
END;
Again, if an employee exists for that ID, the last name will be displayed. In this case, I declare a record based on the employees table and fetch all columns (with a SELECT *) into that record for the specified row.
Fetch columns from different tables:
DECLARE
l_last_name
employees.last_name%TYPE;
l_department_name
departments.department_name%TYPE;
BEGIN
SELECT last_name, department_name
INTO l_last_name, l_department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id
AND e.employee_id=138;
DBMS_OUTPUT.put_line (
l_last_name ||
' in ' ||
l_department_name);
END;
In this case, I need more than one column value but not all the column values in either or both of the tables. So I declare two variables and fetch the two column values into those variables.
What happens if the list of variables in the INTO clause does not match the SELECT list of the query? You will see one of the error messages shown in Table 1.
| ORA-00947: not enough values | The INTO list contains fewer variables than the SELECT list. |
| ORA-00913: too many values | The INTO list contains more variables than the SELECT list. |
| ORA-06502: PL/SQL: numeric or value error | The number of variables in the INTO and SELECT lists matches, but the datatypes do not match and Oracle Database was unable to convert implicitly from one type to the other. |
Table 1: Possible error messages if INTO and SELECT lists do not match
Fetching from Explicit Cursors
A SELECT-INTO is also referred to as an implicit query, because Oracle Database implicitly opens a cursor for the SELECT statement, fetches the row, and then closes the cursor when it finishes doing that (or when an exception is raised).
You can, alternatively, explicitly declare a cursor and then perform the open, fetch, and close operations yourself.
Suppose I need to write a block that fetches employees in ascending salary order and gives them a bonus from a total pool of funds by calling the assign_bonus procedure, whose header is
PROCEDURE assign_bonus (
employee_id_in IN
employees.employee_id%TYPE,
bonus_pool_io IN OUT INTEGER)
Each time assign_bonus is called, the procedure subtracts the bonus given from the total and returns that reduced total. When that bonus pool is exhausted, it stops fetching and commits all changes.
Listing 1 includes a block that uses an explicit cursor to implement this logic, and it describes the operations in the block at specified line numbers.
Code Listing 1: Block and description of explicit cursor implementation
1 DECLARE
2 l_total INTEGER := 10000;
3
4 CURSOR employee_id_cur
5 IS
6 SELECT employee_id
7 FROM plch_employees
8 ORDER BY salary ASC;
9
10 l_employee_id employee_id_cur%ROWTYPE;
11 BEGIN
12 OPEN employee_id_cur;
13
14 LOOP
15 FETCH employee_id_cur INTO l_employee_id;
16 EXIT WHEN employee_id_cur%NOTFOUND;
17
18 assign_bonus (l_employee_id, l_total);
19 EXIT WHEN l_total <= 0;
20 END LOOP;
21
22 CLOSE employees_cur;
23 END;
| Line(s) | Description |
| 4–8 | The explicit cursor declaration. Move the query from the executable section (where the SELECT-INTO must reside), and use the CURSOR keyword to declare (give a name to) that query. |
| 10 | Declare a record based on the row of data returned by the query. In this case, there is just a single column value, so you could just as easily have declared l_employee_id as employees.employee_id%TYPE. But whenever you use an explicit cursor, it is best to declare a record by using %ROWTYPE, so that if the SELECT list of the cursor ever changes, that variable will change with it. |
| 12 | Open the cursor, so that rows can now be fetched from the query.
Note: This is a step Oracle Database performs with the SELECT-INTO statement. |
| 14 | Start a loop to fetch rows. |
| 15 | Fetch the next row for the cursor, and deposit that row’s information into the record specified in the INTO clause.
Note: This is a step Oracle Database performs with the SELECT-INTO statement. |
| 16 | If the FETCH does not find a row, exit the loop. |
| 18 | Call assign_bonus, which applies the bonus and also decrements the value of the l_total variable by that bonus amount. |
| 19 | Exit the loop if all the bonus funds have been exhausted. |
| 22 | Close the cursor.
Note: This is a step Oracle Database performs with the SELECT-INTO statement. |
Here are some things to keep in mind when working with explicit cursors:
If the query does not identify any rows, Oracle Database will not raise NO_DATA_FOUND. Instead, the cursor_name%NOTFOUND attribute will return TRUE.
Your query can return more than one row, and Oracle Database will not raise TOO_MANY_ROWS.
When you declare a cursor in a package (that is, not inside a subprogram of the package) and the cursor is opened, it will stay open until you explicitly close it or your session is terminated.
When the cursor is declared in a declaration section (and not in a package), Oracle Database will also automatically close it when the block in which it is declared terminates. It is still, however, a good idea to explicitly close the cursor yourself. If the cursor is moved to a package, you will have the now necessary CLOSE already in place. And if it is local, then including a CLOSE statement will also show other developers and your manager that you are paying attention.
Using the Cursor FOR Loop
The cursor FOR loop is an elegant and natural extension of the numeric FOR loop in PL/SQL. With a numeric FOR loop, the body of the loop executes once for every integer value between the low and high values specified in the range. With a cursor FOR loop, the body of the loop is executed for each row returned by the query.
The following block uses a cursor FOR loop to display the last names of all employees in department 10:
BEGIN
FOR employee_rec IN (
SELECT *
FROM employees
WHERE department_id = 10)
LOOP
DBMS_OUTPUT.put_line (
employee_rec.last_name);
END LOOP;
END;
You can also use a cursor FOR loop with an explicitly declared cursor:
DECLARE
CURSOR employees_in_10_cur
IS
SELECT *
FROM employees
WHERE department_id = 10;
BEGIN
FOR employee_rec
IN employees_in_10_cur
LOOP
DBMS_OUTPUT.put_line (
employee_rec.last_name);
END LOOP;
END;
The nice thing about the cursor FOR loop is that Oracle Database opens the cursor, declares a record by using %ROWTYPE against the cursor, fetches each row into a record, and then closes the loop when all the rows have been fetched (or the loop terminates for any other reason).
Best of all, Oracle Database automatically optimizes cursor FOR loops to perform similarly to BULK COLLECT queries (covered in “
Bulk Processing with BULK COLLECT and FORALL,” in the September/October 2012 issue of
Oracle Magazine). So even though your code looks as if you are fetching one row at a time, Oracle Database will actually fetch 100 rows at a time—and enable you to work with each row individually.
Dynamic SQL means that at the time you write (and then compile) your code, you do not have all the information you need for parsing a SQL statement. Instead, you must wait for runtime to complete the SQL statement and then parse and execute it.
Oracle Database makes it easy to execute SQL statements (and PL/SQL blocks) dynamically with the EXECUTE IMMEDIATE statement. And querying data is the easiest dynamic SQL operation of all!
You can fetch a single row or multiple rows. Here is a generic function that fetches the value of a numeric column in any table, for the specified WHERE clause:
CREATE OR REPLACE FUNCTION
single_number_value (
table_in IN VARCHAR2,
column_in IN VARCHAR2,
where_in IN VARCHAR2)
RETURN NUMBER
IS
l_return NUMBER;
BEGIN
EXECUTE IMMEDIATE
'SELECT '
|| column_in
|| ' FROM '
|| table_in
|| ' WHERE '
|| where_in
INTO l_return;
RETURN l_return;
END;
As you can see, instead of SELECT-INTO, I use EXECUTE IMMEDIATE-INTO and construct the SELECT statement from the arguments passed to the function. Here’s an example of calling the function:
BEGIN
DBMS_OUTPUT.put_line (
single_number_value (
'employees',
'salary',
'employee_id=138'));
END;
As with SELECT-INTO, EXECUTE IMMEDIATE-INTO will raise NO_DATA_FOUND if no rows are found and TOO_MANY_ROWS if more than one row is found.
You can also use EXECUTE IMMEDIATE to fetch multiple rows of data, which means that you will populate a collection, so you must use BULK COLLECT. The following is a procedure that will display the values of any numeric column for all rows specified in the WHERE clause:
CREATE OR REPLACE PROCEDURE
show_number_values (
table_in IN VARCHAR2,
column_in IN VARCHAR2,
where_in IN VARCHAR2)
IS
TYPE values_t IS TABLE OF NUMBER;
l_values values_t;
BEGIN
EXECUTE IMMEDIATE
'SELECT '
|| column_in
|| ' FROM '
|| table_in
|| ' WHERE '
|| where_in
BULK COLLECT INTO l_values;
FOR indx IN 1 .. l_values.COUNT
LOOP
DBMS_OUTPUT.put_line
(l_values (indx));
END LOOP;
END;
And when I call the procedure for the standard employees table
BEGIN
show_number_values (
'employees',
'salary',
'department_id = 10
order by salary desc');
END;
I see the following two rows of output:
4400
3200
------------
1. select distinct hpms_doctor.doctor_no, hpms_doctor.DOCTOR_NAME
from op_appointment,hpms_doctor
where hpms_doctor.DOCTOR_NO=op_appointment.DOCTOR_NO
and trunc(appoint_date) = trunc(sysdate)
and hpms_doctor.doctor_no in (select consult_by from opv_appexchange)
/
2. select distinct D.doctor_no, D.DOCTOR_NAME
from hpms_doctor D, op_appointment A
where D.DOCTOR_NO=A.DOCTOR_NO
and trunc(a.appoint_date) = trunc(sysdate)
and exists (select consult_by from opv_appexchange)
order by D.DOCTOR_NAME
ABOVE two query 2. is faster than one.
----20190206---
use with clause
WITH dept_count AS
(select identifyed_no,count(identifyed_no) total from eh_refadvice
where trunc(entry_timestamp)=trunc(sysdate)
group by identifyed_no
having
count(identifyed_no) >1)
select a.* from
(select identifyed_no,sl#,time,rdoctor_no,doctor,advice_by,advicer,operator,PRES_TIME,prescribed_by,item_name,rx_total_dpt,dispnc_qty
from
(select identifyed_no,sl#,to_char(ref_con_dt,'hh:mi') time,rdoctor_no,fnc_emp(rdoctor_no) doctor,advice_by,fnc_emp(advice_by) advicer,fnc_emp(entered_by) operator,null pres_time,null prescribed_by,null item_name,null rx_total_dpt,null dispnc_qty from eh_refadvice
union
(select identifyed_no,null sl#,null time,null rdoctor_no,null doctor,null advice_by,null advicer,null operator,to_char(START_DATE,'HH:MI') pres_time, fnc_emp(consult_by) prescribed_by,item_name,rx_total_dpt,dispnc_qty from pm_presmedicine
where trunc(entry_timestamp)=trunc(sysdate)
))
order by identifyed_no,sl#) a,dept_count b
where a.identifyed_no =b.identifyed_no
----------------EXECUTION PLAN SCRIPT.
explain plan for select * from product;
/
select * from table(dbms_xplan.display);
Time in Dhaka 
