How to select data and the total to cursors

oracleselect

I have a table like this:

 ID  | Name
------------
  1  | AA
  2  | AA
  3  | AA
  4  | BB

I want to select all rows where Name like 'AA' in a cursor and get the count from this result in another cursor. I also need to page the results.

Expected result (Skip = 0; Take = 2)

-- p_result

 ID  | Name
------------
  1  | AA
  2  | AA

-- p_count
   3

How can I do this?

Best Answer

An alternative to msi77's solution is the use of analytical functions. This might be a bit more efficient but you should run an explain plan to find out.

SELECT id,
       name, 
       count(*) over () as total_count
FROM your_table
WHERE name = 'AA'

Related Solutions

Mysql – Simple select statement not returning correct data

You are missing a JOIN between prices and items.

Your implicit join in the where clause only joins vendors and prices so you get a cartesian join for the result of that join and the items table. If you had used ANSI JOIN keywords instead, you would have noticed that immediately

You need something like this:

select distinct items.name, 
       vendors.name, 
       prices.url, 
       prices.price 
 FROM vendors
   JOIN prices ON vendors.vid = prices.vid 
   JOIN items ON items.iid = prices.iid ---  this part is missing
 WHERE items.iid='3' 
ORDER BY prices.price;

I don't know if the join condition is correct, because you didn't tell us which column in prices contains the foreign key to items.

MySQL Select Statement – How to Make a Select Statement Get Blocked

Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them

You can have SELECTs get deadlocked by UPDATEs and DELETEs
- Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
You can have UPDATEs and DELETEs blocked by SELECTs
- How are DB locks tied to connections and sessions?
- Is Oracle DB immune to the InnoDB deadlocks found in MySQL?
You can just lock the table before the SELECT and unlock it afterwards
- LOCK TABLES table READ;
- SELECT ... ;
- UNLOCK TABLES;
You could perform a large UPDATE in a transaction with the SERIALIZEABLE isoaltion level. The MySQL Documentation on SERIALIZEABLE isoaltion level says:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Cutting right to the chase, you could just use

SELECT ... FOR UPDATE, then try SELECT against those rows
SELECT ... LOCK IN SHARE MODE, then try to UPDATE or DELETE those rows
See the MySQL Documentation

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Simple select statement not returning correct data

MySQL Select Statement – How to Make a Select Statement Get Blocked

Related Question