I have query that has a lot of columns (about 30) and I would like to place that query in stored procedure. The reason behind that is because this query is used in multiple places in the system. There are situations when I need to pull whole table result set and sometimes I need to look for specific record only (filtered by id). Here is example of the query:
SELECT
TRIM(t1.rec_id) AS rec_id,
TRIM(t1.date) AS date,
TRIM(t1.name) AS name,
TRIM(t1.address) AS address,
TRIM(t2.year) AS year,
CASE
WHEN LENGTH(TRIM(t3.percent)) <> 0 THEN CONCAT( ROUND( TRIM(t3.percent) * 100, 2) ,'% Whole' )
WHEN LENGTH(TRIM(t3.partial)) <> 0 THEN CONCAT( ROUND( TRIM(t3.partial) * 100, 2) ,'% Partial' )
END AS percent
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON TRIM(t1.rec_id) = TRIM(t2.rec_id)
LEFT OUTER JOIN table3 t3
ON TRIM(t1.rec_id) = TRIM(t3.rec_id)
-- Here I would like to add WHERE clause filter
-- If RecID argument is not NULL and is valid then query should filter
-- and return only one record. If not return all records.
ORDER BY t1.rec_id
Query above usually returns around 500-600
records. It's the main source of my application. In some situations I need all records and sometimes only specific record. So in my comment inside of the code above I explained the situation. The stored procedure should have one argument RecID
. If that argument is NULL
then stored procedure should return all records and columns. If ti's not NULL
then it should look for specific RecID
and return that row with all columns. I have done some work in SQL 2008+ where I used stored procedures. I'm wondering if someone can show me an example on how this would work in Oracle? I use Oracle 11g with ColdFusion 10. If someone have good example on how this can be completed please let me know. Thank you!