My work has a small database for our system. It's not maintained by a DBA but the programmer. So, it has issues.
I've done basic queries for years but it's been a long time since I've done more than a simple select/insert/update and that was on Oracle.
I'm a maintainer/supporter of the system that uses this DB. It's a backend to a web based application with menu's. In our system each table represents a menu level (more or less). Each subsequent selection depends on the previous.
We need a way to list different 'flows' from any selected entry in table 1. Normally, we use Toad. I can do all the selects but my boss was hoping to do a stored procedure that would list all result sets. Can we do that? In toad I can do multiple selects and it will show different result sets but I don't know if that can translate to a stored procedure.
I could do this in code. I'm an amateur coder and could loop through selects (using previous select results as variables) but I'm new to stored procedures and have only done basic ones. Do I need to use 'Cursors'?
I haven't used them and don't have a good understanding of them.
Level 1 selection (table 1 )
Level 2 selection (table 2)
Level 3 selection (table 3)
Level 4 selection (table 4) (optional)
Table 5 (optional, normally will have table 4 entry)
Table 6 (optional, normally will have table 4 entry)