I’m new to Toad and have a simple question; I previously wrote SAS macros and procedures and am entering this new Toad/Oracle environment. I’m trying to figure out how to do something similar to what I did in SAS where I could create functions where I used SAS macro language in my SQL statements.
For example, I’d like to make some sort of script where the user inputs some parameters to a function that are used in a SQL statement to return a table. For example, a function where the parameter is the parameter is later called as the parameter of a WHERE clause.
I don’t think procedures can return the results of a SQL query. Not sure where to start with this.
Thanks!
Procedures in Oracle’s PL/SQL can certainly be used to return results of a query. I’m not a PL/SQL expert, but here’s a simple example below, and one of many ways to execute SQL within PL/SQL. Example is set up as an anonymous block, but you can make a callable procedure out of it, with input parameters, etc.
Hope this helps.
DECLARE
CURSOR example_cursor is
select * from employee
where substr(emp_dept,1,1) in (‘W’,‘X’, ‘Y’, ‘Z’)
and job_id not like ‘%PRES’
and job_id not like ‘%VP’
and job_id not like ‘%MAN’
and job_id not like ‘%MGR’;
record_ex example_cursor%ROWTYPE;
BEGIN
FOR i IN 1 … 10000 LOOP
OPEN example_cursor;
LOOP
FETCH example_cursor INTO record_ex;
EXIT WHEN example_cursor%NOTFOUND;
END LOOP;
CLOSE example_cursor;
END LOOP;
END;