SQL> set serveroutput on
SQL> edit
SP2-0107: 无须保存。
SQL> set serveroutput on;
SQL> create or replace function max_salary
2 return employees.salary%type
3 is
4 v_salary employees.salary%type;
5 begin
6 select MAX(salary) into v_salary
7 from employees;
8 return v_salary;
9 end;
10 /
函数已创建。
SQL> select first_name,last_name
2 from employees
3 where salary=max_salary
4 /
FIRST_NAME LAST_NAME
-------------------- -------------------------
Steven King
SQL> declare
2 sm_salary employees.salary%type;
3 begin
4 sm_salary:=max_salary;
5 dbms_output.put_line('雇员的最高工资为:'||sm_salary);
6 end;
7 /
雇员的最高工资为:24000
PL/SQL 过程已成功完成。
SQL> create or replace function get_departmant
2 (v_employee_id employees.employee_id%type)
3 return employees.department_id%type
4 is
5 v_department_id employees.department_id%type;
6 begin
7 select department_id into v_department_id
8 from employees
9 where employee_id=v_employee_id;
10 return v_department_id;
11 end get_department;
12 /
警告: 创建的函数带有编译错误。
SQL> edit
已写入 file afiedt.buf
1 create or replace function get_departmant
2 (v_employee_id employees.employee_id%type)
3 return employees.department_id%type
4 is
5 v_department_id employees.department_id%type;
6 begin
7 select department_id into v_department_id
8 from employees
9 where employee_id=v_employee_id;
10 return v_department_id;
11* end get_department;
SQL> /
警告: 创建的函数带有编译错误。
SQL> show error
FUNCTION GET_DEPARTMANT 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
11/5 PLS-00113: END 标识符 'GET_DEPARTMENT' 必须同 'GET_DEPARTMANT'
匹配 (在第 1 行, 第 10 列)
SQL> edit
已写入 file afiedt.buf
1 create or replace function get_departmant
2 (v_employee_id employees.employee_id%type)
3 return employees.department_id%type
4 is
5 v_department_id employees.department_id%type;
6 begin
7 select department_id into v_department_id
8 from employees
9 where employee_id=v_employee_id;
10 return v_department_id;
11* end;
12 /
函数已创建。
SQL> declare
2 s_employee_id employees.employee_id%type:=&p_employee_id;
3 s_department_id employee.department_id%type;
4 begin
5 s_department_id:=get_department(s_employee_id);
6 dbms_output.put_line('雇员所在部门号为:'||s_department_id);
7 end;
8 /
输入 p_employee_id 的值: 110
原值 2: s_employee_id employees.employee_id%type:=&p_employee_id;
新值 2: s_employee_id employees.employee_id%type:=110;
s_department_id employee.department_id%type;
*
第 3 行出现错误:
ORA-06550: 第 3 行, 第 1
7 列:
PLS-00201: 必须声明标识符 'EMPLOYEE.DEPARTMENT_ID'
ORA-06550: 第 3 行, 第 17 列:
PL/SQL: Item ignored
ORA-06550: 第 5 行, 第 1 列:
PLS-00320: 此表达式的类型声明不完整或格式不正确
ORA-06550: 第 5 行, 第 1 列:
PL/SQL: Statement ignored
ORA-06550: 第 6 行, 第 43 列:
PLS-00320: 此表达式的类型声明不完整或格式不正确
ORA-06550: 第 6 行, 第 1 列:
PL/SQL: Statement ignored
SQL> show error
没有错误。
SQL> edit
已写入 file afiedt.buf
1 declare
2 s_employee_id employees.employee_id%type:=&p_employee_id;
3 s_department_id employees.department_id%type;
4 begin
5 s_department_id:=get_department(s_employee_id);
6 dbms_output.put_line('雇员所在部门号为:'||s_department_id);
7* end;
SQL> /
输入 p_employee_id 的值: 110
原值 2: s_employee_id employees.employee_id%type:=&p_employee_id;
新值 2: s_employee_id employees.employee_id%type:=110;
s_department_id:=get_department(s_employee_id);
*
第 5 行出现错误:
ORA-06550: 第 5 行, 第 18 列:
PLS-00201: 必须声明标识符 'GET_DEPARTMENT'
ORA-06550: 第 5 行, 第 1 列:
PL/SQL: Statement ignored
SQL> edit
已写入 file afiedt.buf
1 declare
2 s_employee_id employees.employee_id%type:=&p_employee_id;
3 s_department_id employees.department_id%type;
4 begin
5 s_department_id:=get_departmant(s_employee_id);
6 dbms_output.put_line('雇员所在部门号为:'||s_department_id);
7* end;
SQL> /
输入 p_employee_id 的值: 110
原值 2: s_employee_id employees.employee_id%type:=&p_employee_id;
新值 2: s_employee_id employees.employee_id%type:=110;
雇员所在部门号为:100
PL/SQL 过程已成功完成。