SQL>set serveroutput on;
SQL> create or replace PROCEDURE emp_pos( EMPLOYEE_NAME IN EMPLOYEE.EMP_NAME%TYPE,
POSITION_NAME OUT POSITION_EMPLOYEE.POSITION_NAME%TYPE )
AS
BEGIN
SELECT pos_emp.POSITION_NAME
INTO
POSITION_NAME
FROM EMPLOYEE emp
JOIN
POSITION_EMPLOYEE pos_emp
ON
emp.POSITION_EMPLOYEE_POSITION_ID
= pos_emp.POSITION_ID
WHERE emp.EMP_NAME
= EMPLOYEE_NAME
;
dbms_output.put_line(EMPLOYEE_NAME||'''s position is : '||POSITION_NAME);
END;
定义此过程后,您可以这样使用:
SQL>var v_emp_pos varchar2;
SQL>exec emp_pos('&employee_name',:v_emp_pos);
SQL>print v_emp_pos; -- just to print out this "out" parameter.