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!