Back in 2013 when i started with PL/SQL, i was given an assignment to write a function which would return the row type for a given table, that would be used as a input parameter. Now with the advent oracle advance features and PFT we have ways to write it real quick but back then, it consumed a lot of my time and ended up writing a messy dynamic query.
Why would anyone give such requirement. Working on projects we often Encounter some task which is kind of repetitive. One of the ways to combat such Encounter, is write code which would write code for you.
In this post I would list 3 generic codes which is more or less used by most of us in PL/SQL programming.
NUMBER OF RECORDS IN DIFFERENT TABLES: Not often you would be using row counts of different tables in your project but it cab be very handy in Data migration related projects wherein you would match the no of records in all the tables of two schema.
CREATE OR REPLACE FUNCTION tabCount (
tab IN VARCHAR2, whr IN VARCHAR2 := NULL)
RETURN INTEGER
IS
retval INTEGER;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*)
FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(tab) ||
' WHERE ' || NVL (whr, '1=1')
INTO retval;
RETURN retval;
END;
The above code creates a function for me to count the records.
FOR instance if i want to have the number of records in employee table having department =30
SELECT tabcount('employees', 'department_id =30') from dual;
--------------------------------------------------------------
This returns 6 This code can also be very handy in data migration projects or any such which requires a records comparison between two schema.
You can use the below to get the counts of all the tables in the current schema
DECLARE
result number;
BEGIN
FOR i in (SELECT TABLE_NAME FROM USER_TABLES)
LOOP
result := tabCount(i.table_name);
DBMS_OUTPUT.put_line(result || ' ' || i.table_name);
end loop;
end;Coming back to where i started lets Suppose you are asked to write functions which take the primary key of a table as input
i/p and returns row type as o/p.
for this you have to write as many functions as is the tables in your DB
FUNCTION returning rowtypes of table being passed
Let's try to do it in a more generalized way.
create or replace procedure genlookup(tab_in in varchar2,col_in in varchar2)
is
begin
dbms_output.put_line('create or replace function ' || tab_in || '_row_for(');
dbms_output.put_line(' ' || col_in||'_in IN ' || tab_in || '.'||col_in||'%type)');
dbms_output.put_line('return '||tab_in||'%rowtype');
dbms_output.put_line('is');
dbms_output.put_line('l_return '||tab_in||'%rowtype;');
dbms_output.put_line('begin');
dbms_output.put_line('select * into l_return from ' ||tab_in);
dbms_output.put_line('where ' || col_in ||'='||col_in||'_in;');
dbms_output.put_line('return l_return;');
dbms_output.put_line('end;');
end;
begin
for i in (select ccol.table_name,ccol.column_name pkycol_name
from user_constraints cons,user_cons_columns ccol
where cons.owner = ccol.owner
and cons.constraint_name = ccol.constraint_name
and cons.table_name like '%EMP%' and cons.CONSTRAINT_TYPE = 'P')
loop
genlookup(i.table_name,i.pkycol_name);
end loop;
end;
Above code will automatically generate PLSQL FUNCTION For every table in your database. I have used TABLE like EMP to restrict the function to generate code for all the tables carrying EMP in my DATABASE. Try running it.
Earlier this week, i saw a generic code generation on oracle live posted by Steven Feuerstein. A man of great fame in this domain and a source of inspiration for developers like us.
He showed some ways to generate code to move to string indexed collection. I am copying his code below but for details you can visit his codes.
Generate code to move rows into a collection
CREATE OR REPLACE PROCEDURE gen_select_into_index_by2 (query_in IN VARCHAR2,
table_in IN VARCHAR2,
column_in IN VARCHAR2)
IS
c_template VARCHAR2(32767)
:= q'[DECLARE
CURSOR c IS #QUERY#;
TYPE vct IS TABLE OF c%ROWTYPE INDEX BY VARCHAR2(32767);
l_rows_by_varchar2 vct;
l_index VARCHAR2(32767);
BEGIN
FOR rec IN (#QUERY#)
LOOP
l_rows_by_varchar2 (rec.#COLUMN#) := rec;
END LOOP;
CLOSE c;
/* Iterate through the string-indexed collection */
l_index := l_rows_by_varchar2.FIRST;
WHILE l_index IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE (l_rows_by_varchar2(l_index).#COLUMN#);
l_index := l_rows_by_varchar2.NEXT (l_index);
END LOOP;
END;]';
BEGIN
DBMS_OUTPUT.put_line (
REPLACE (
REPLACE (
REPLACE (c_template, '#QUERY#', query_in),
'#TABLE#', table_in),
'#COLUMN#', column_in));
END;
begin
gen_select_into_index_by2 (
'select last_name, first_name, employee_id
from employees where department_id = 30', 'employees', 'last_name');
end;
-----------------------------------------------------------------
This would generate code for fetching the result into a string indexed collection.All of the three codes above are beautiful examples of automating the code writing and reducing much of our efforts in re- writing similar codes, gosh these days i am also kind of overwhelmed with repetitive. I must find a way to do it from a code rather than creating scripts every time.
QUIZ:
I encountered something similar years ago. Would you try ?
BEGIN
WHILE sysdate = sysdate LOOP
NULL;
END LOOP;
END;
/
DECLARE
x DATE;
BEGIN
LOOP
BEGIN
SELECT null INTO x FROM dual WHERE sysdate =
sysdate;
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
END;
END LOOP;
END;
QUESTION : Are the loops above both finite, both infinite or is it so that one of them is finite
and the other one infinite?
ANSWER :
The first is finite while the other is infinite.
Follow this link for Polymorphic table functions(PFT) , which can really reduce a lot of your efforts while writing these types of subprograms:
Comments