-- 函数 increaseSalary()
- create or replace function increaseSalary(theIncome in number) return varchar2
- as
- theMessage varchar2(50);
- begin
- if theIncome <=4000 then
- theMessage := '收入太低, 工资增加 10%';
- elsif theIncome <=8000 then
- theMessage := '收入偏低, 工资增加 5%';
- elsif theIncome <=20000 then
- theMessage := '收入一般, 工资增加 2%';
- else
- theMessage := '收入很高, 工资增加 1%';
- end if;
- return theMessage;
- end;
-- 存储过程
- create or replace procedure getEmpInfo
- (theId in out employees.employee_id%type,
- theName out employees.first_name%type,
- theSalary out employees.salary%type,
- theMessage out varchar2)
- is
- begin
- select employee_id,first_name,salary,increaseSalary(salary)
- into theId,theName,theSalary,theMessage
- from employees
- where employee_id = theId;
- exception
- when NO_DATA_FOUND then
- dbms_output.put_line('没有该员工信息');
- end;
- set serveroutput on ;
- declare
- theId employees.employee_id%type:=&theId;
- theName employees.first_name%type;
- theSalary employees.salary%type;
- theMessage varchar2(50);
- begin
getEmpInfo(&theId,theName,theSalary,theMessage);-- 输入员工 id
- dbms_output.put_line('ID 为:'||theId||'的员工, 名字为'||theName
- ||', 收入为'||theSalary||','||theMessage);
- end;
- ---------------------
来源: http://www.bubuko.com/infodetail-3086938.html