So… let us agree that we return a record type for returning multiple output parameters instead of using OUT parameters in the function.\u00a0In this case, to make it easy, I will use a record structure based on the structure of a table (%ROWTYPE).<\/p>\n
CREATE OR REPLACE FUNCTION get_employee(p_employee_id IN hr.employees.employee_id%TYPE)\r\nRETURN employees%ROWTYPE\r\nIS\r\n\r\n CURSOR c_employees\r\n IS\r\n SELECT *\r\n FROM hr.employees\r\n WHERE employee_id = p_employee_id; \r\n\r\n r_employees c_employees%ROWTYPE;\r\n \r\nBEGIN\r\n\r\n OPEN c_employees;\r\n FETCH c_employees INTO r_employees;\r\n CLOSE c_employees;\r\n \r\n RETURN r_employees;\r\n\r\nEND;\r\n\/\r\n<\/pre>\nNow, we have all the values we need in different record fields and we can format the way we want to.<\/p>\n
DECLARE\r\n\r\n r_employee employees%ROWTYPE;\r\n\r\nBEGIN\r\n r_employee := get_employee(100);\r\n dbms_output.put_line(r_employee.last_name ||', '||r_employee.first_name);\r\n\r\nEND;\r\n\/\r\n\r\nKing, Steven\r\n<\/pre>\nFunctions returning a record structure, but only needing 1 value<\/h2>\n
“That record structure is nice, but I only need 1 value”.\u00a0When I use a record structure, I first need to assign the RETURN value to a local variable and then use the local variable.\u00a0A little bit of overhead?<\/p>\n
What a lot of people don’t know, is that you don’t have to assign the record to a local variable, you can use the fields of the record directly in your function.\u00a0Say what?\u00a0Yes, this is possible:<\/p>\n
BEGIN\r\n dbms_output.put_line(get_employee(100).last_name);\r\nEND;\r\n\/\r\n\r\nKing\r\n<\/pre>\nWhen you\u00a0only need one variable from a function returning a record, you can use “function_name().field_name”. Interesting, isn’t it?<\/p>\n
Of course you can also do this:<\/p>\n
BEGIN\r\n dbms_output.put_line(get_employee(100).last_name);\r\n dbms_output.put_line(get_employee(100).first_name);\r\n dbms_output.put_line(get_employee(100).last_name ||', '||get_employee(100).first_name);\r\n\r\nEND;\r\n\/\r\n\r\nKing\r\nSteven\r\nKing, Steven\r\n<\/pre>\nIndeed, no need to declare a variable based on the record structure.\u00a0But there’s a disadvantage! The function is executed several times\u00a0in the previous example.\u00a0We don’t want that!<\/p>\n
Functions returning a record structure combined with Result Cache<\/h2>\n
One of Oracle’s caching mechanisms is “Function Result Cache”. This means that Oracle caches the result of the function and when there’s a new call to the function with the same input, it returns the result that is stored in the cache.\u00a0Wouldn’t it be cool if we could use that?<\/p>\n
A little test case. I’ll write a line using dbms_output.put_line every time I enter the function. Now we can see how many times the function will be executed.<\/p>\n
CREATE OR REPLACE FUNCTION get_employee(p_employee_id IN hr.employees.employee_id%TYPE)\r\nRETURN employees%ROWTYPE\r\nIS\r\n\r\n CURSOR c_employees\r\n IS\r\n SELECT *\r\n FROM hr.employees\r\n WHERE employee_id = p_employee_id; \r\n\r\n r_employees c_employees%ROWTYPE;\r\n \r\nBEGIN\r\n\r\n dbms_output.put_line('execute get_employee');\r\n\r\n OPEN c_employees;\r\n FETCH c_employees INTO r_employees;\r\n CLOSE c_employees;\r\n \r\n RETURN r_employees;\r\n\r\nEND;\r\n\/\r\n<\/pre>\nAnd execute this block of code again:<\/p>\n
BEGIN\r\n\r\n dbms_output.put_line(get_employee(100).last_name ||', '||get_employee(100).first_name);\r\n\r\nEND;\r\n\/\r\n\r\nexecute get_employee\r\nexecute get_employee\r\nKing, Steven\r\n<\/pre>\nAs you can see, the function is executed twice.<\/p>\n
Now we add the result cache:<\/p>\n
CREATE OR REPLACE FUNCTION get_employee(p_employee_id IN hr.employees.employee_id%TYPE)\r\nRETURN employees%ROWTYPE RESULT_CACHE\r\nIS\r\n\r\n CURSOR c_employees\r\n IS\r\n SELECT *\r\n FROM hr.employees\r\n WHERE employee_id = p_employee_id; \r\n\r\n r_employees c_employees%ROWTYPE;\r\n \r\nBEGIN\r\n\r\n dbms_output.put_line('execute get_employee');\r\n\r\n OPEN c_employees;\r\n FETCH c_employees INTO r_employees;\r\n CLOSE c_employees;\r\n \r\n RETURN r_employees;\r\n\r\nEND;\r\n\/\r\n<\/pre>\nBEGIN\r\n\r\n dbms_output.put_line(get_employee(100).last_name ||', '||get_employee(100).first_name);\r\n\r\nEND;\r\n\/\r\n\r\n\r\nexecute get_employee\r\nKing, Steven\r\n<\/pre>\nAs you can see the function is executed only once!<\/p>\n
Conclusion<\/h2>\n
We created a nice API to get information from a table that is flexible(we can get one or more fields from a table) and that uses a caching mechanism to gain performance(the query isn’t executed every time I need the same information).<\/p>\n
This will certainly contribute to the maintainability of your application.<\/p>\n
But…<\/h2>\n
There’s a little downside in using record structures in a function – you can’t use it in SQL. Not even “get_employee(100).last_name” will work in SQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
This article is about functions and how to get more out of them if you combine them with record structures. Functions are the instrument in the Oracle Database to execute code and return a value.\u00a0Nothing new here, everybody who develops in PL\/SQL on the database knows that. In case you’re just starting with Oracle and want to know more about…<\/p>\n","protected":false},"author":316191,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[4658],"coauthors":[],"class_list":["post-73142","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-functions"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73142","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316191"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73142"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73142\/revisions"}],"predecessor-version":[{"id":91638,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73142\/revisions\/91638"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73142"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73142"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}