6 Examples Using Standalone Subprograms

Example 6-1 Using IN and OUT parameters

This example creates a procedure query_emp to retrieve information about an employee, passes the employee_id 171 to the procedure, and retrieves the name and salary into two OUT parameters.

Command> CREATE OR REPLACE PROCEDURE query_emp
       >   (p_id IN employees.employee_id%TYPE,
       >    p_name  OUT employees.last_name%TYPE,
       >    p_salary OUT employees.salary%TYPE) IS
       > BEGIN
       >   SELECT last_name, salary INTO p_name, p_salary
       >   FROM employees
       >   WHERE employee_id = p_id;
       > END query_emp;
       > /
 
Procedure created.

Command> -- Execute the procedure
       > DECLARE
       >  v_emp_name employees.last_name%TYPE;
       >  v_emp_sal  employees.salary%TYPE;
       > BEGIN
       >  query_emp (171, v_emp_name, v_emp_sal);
       >  DBMS_OUTPUT.PUT_LINE (v_emp_name || ' earns ' ||
       >   TO_CHAR (v_emp_sal, '$999,999.00'));
       > END;
       > /
Smith earns    $7,400.00
 
PL/SQL procedure successfully completed.

Example 6-2 Using bind variables to execute a procedure

This example uses bind variables to execute procedure query_emp from Example 6-1. (You must create that procedure before completing this example.) Remember to check that data types are compatible.

Command> VARIABLE b_name VARCHAR2 (25);
Command> VARIABLE b_sal  NUMBER;
Command> BEGIN
       >  query_emp (171, :b_name, :b_sal);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_name
B_NAME               : Smith
Command> PRINT b_sal
B_SAL                : 7400

Example 6-3 Using IN OUT parameters and bind variables

Consider a situation where you want to format a phone number. You decide to use an IN OUT parameter to pass the unformatted phone number to a procedure. After the procedure is executed, the IN OUT parameter contains the formatted phone number value. Procedure FORMAT_PHONE in this example accomplishes that, accepting a 10 character string containing digits for a phone number. Bind variable b_phone_no first provides the input value passed to FORMAT_PHONE, then after execution is used as an output value returning the updated string.

Command> CREATE OR REPLACE PROCEDURE format_phone
       >   (p_phone_no IN OUT VARCHAR2 ) IS
       > BEGIN
       >  p_phone_no := '('  || SUBSTR (p_phone_no,1,3) ||
       >                ') ' || SUBSTR (p_phone_no,4,3) ||
       >                '-'  || SUBSTR (p_phone_no,7);
       > END format_phone;
       > /
 
Procedure created.

Create the bind variable, execute the procedure, and verify the results.

Command> VARIABLE b_phone_no VARCHAR2 (15);
Command> EXECUTE :b_phone_no := '8006330575';
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_phone_no;
B_PHONE_NO           : 8006330575
Command> BEGIN
       >   format_phone (:b_phone_no);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_phone_no
B_PHONE_NO           : (800) 633-0575

Example 6-4 Querying system view USER_SOURCE

This example queries the USER_SOURCE system view to examine the source code of procedure query_emp from Example 6-1. (You must create that procedure before completing this example.)

Command> SELECT SUBSTR (text, 1, LENGTH(text)-1)
       > FROM user_source
       > WHERE name = 'QUERY_EMP' AND type = 'PROCEDURE';

This produces the following output:

< PROCEDURE query_emp >
< (p_id IN employees.employee_id%TYPE, >
<  p_name OUT employees.last_name%TYPE, >
<  p_salary OUT employees.salary%TYPE) IS >
< BEGIN >
< SELECT last_name, salary INTO p_name, p_salary >
< FROM employees >
< WHERE employee_id = p_id; >
< END query_emp; >
9 rows found.

Example 6-5 Creating and invoking a standalone function

This example creates and invokes the function get_sal, which as one input parameter and returns salary as type NUMBER.

Command> CREATE OR REPLACE FUNCTION get_sal
       >   (p_id employees.employee_id%TYPE) RETURN NUMBER IS
       >   v_sal employees.salary%TYPE := 0;
       > BEGIN
       >   SELECT salary INTO v_sal FROM employees
       >     WHERE employee_id = p_id;
       >   RETURN v_sal;
       > END get_sal;
       > /
 
Function created.
Command> BEGIN
       >   DBMS_OUTPUT.PUT_LINE (get_sal (100));
       > END;
       > /
24000
 
PL/SQL procedure successfully completed.