Function or Procedure?
Somebody asked for a simple comparison between a PL/SQL pass-by-value function and pass-by-reference procedure, where the procedure uses only an OUT mode parameter to return the result. This provides examples of both, but please note that a pass-by-value function can be used in SQL or PL/SQL context while a pass-by-reference procedure can only be used in another anonymous of named block PL/SQL program.
The function and procedure let you calculate the value of a number raised to a power of an exponent. The third parameter lets you convert the exponent value to an inverse value, like 2 to 1/2.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE OR REPLACE FUNCTION find_root_function ( pv_number BINARY_DOUBLE , pv_power BINARY_DOUBLE , pv_inverse BINARY_INTEGER DEFAULT 0 ) RETURN BINARY_DOUBLE IS -- Declare local variable for return value. lv_result BINARY_DOUBLE; BEGIN -- If the inverse value is anything but zero calculate the inverse of the power. IF pv_inverse = 0 THEN lv_result := POWER(pv_number,pv_power); ELSE lv_result := POWER(pv_number,(1 / pv_power)); END IF; RETURN lv_result; END find_root_function; / |
You can test it with these to queries against the dual table:
SELECT TO_CHAR(find_root_function(4,3),'99,999.90') FROM dual; SELECT TO_CHAR(find_root_function(125,3,1),'99,999.90') FROM dual; |
The procedure does the same thing as the function. The difference is that the fourth parameter to the procedure returns the value rather than a formal return type like a function.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE OR REPLACE PROCEDURE find_root_procedure ( pv_number IN BINARY_DOUBLE , pv_power IN BINARY_DOUBLE , pv_inverse IN BINARY_INTEGER DEFAULT 0 , pv_return OUT BINARY_DOUBLE ) IS BEGIN -- If the inverse value is anything but zero calculate the inverse of the power. IF pv_inverse = 0 THEN pv_return := POWER(pv_number,pv_power); ELSE dbms_output.put_line('here'); pv_return := POWER(pv_number,(1 / pv_power)); END IF; END find_root_procedure; / |
You can test it inside an anonymous block PL/SQL program, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | DECLARE -- Declare input variables. lv_input BINARY_DOUBLE; lv_power BINARY_DOUBLE; lv_inverse BINARY_INTEGER; lv_output BINARY_DOUBLE; BEGIN -- Assign input values to variables. lv_input := '&1'; lv_power := '&2'; lv_inverse := '&3'; -- Test raising to a power. find_root_procedure(lv_input, lv_power, lv_inverse, lv_output); dbms_output.put_line(TO_CHAR(lv_output,'99,999.90')); -- Test raising to an inverse power. find_root_procedure(lv_input, lv_power, lv_inverse, lv_output); dbms_output.put_line(TO_CHAR(lv_output,'99,999.90')); END; / |
You can test it inside an anonymous block PL/SQL program, like the following example. For reference, the difference between PL/SQL and the SQL*Plus environment is large. The EXECUTE call is correct in SQL*Plus but would be incorrect inside a PL/SQL block for a Native Dynamic SQL (NDS) call. Inside a PL/SQL block you would use EXECUTE IMMEDIATE because it dispatches a call from the current running scope to a nested scope operation (see comment below).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | -- SQL*Plus Test. VARIABLE sv_input BINARY_DOUBLE VARIABLE sv_power BINARY_DOUBLE VARIABLE sv_inverse BINARY_DOUBLE VARIABLE sv_output BINARY_DOUBLE -- Verify the null value of the session variable. SELECT :sv_output AS ":sv_output" FROM dual; BEGIN -- Prompt for local assignments and initialize output variable. :sv_input := '&1'; :sv_power := '&2'; :sv_inverse := '&3'; :sv_output := 0; END; / -- Run the procedure in the SQL*Plus scope. EXECUTE find_root_procedure(:sv_input, :sv_power, :sv_inverse, :sv_output); -- Query the new value of the session variable. SELECT TO_CHAR(:sv_output,'99,999.90') AS ":output" FROM dual; |
As usual, I hope this helps folks beyond the one who asked. Comments are always welcome.
Subscribe via RSS