PL/SQL函数 [英] PL/SQL functions

问题描述

可能重复:
有关PL/SQL功能的作业
plsql函数

Possible Duplicate:
Homework on PL/SQL FUNCTIONS
plsql functions

功能:

1. Display_Employee_Name_In_Uppercase的功能，该功能接受Empoyees表中的Employee_ID，并以大写形式返回员工的名字和姓氏.
2. 编写一个小的PL/SQL程序以显示Employee_IDs分别为107、200和205的雇员的姓名.

1. function to Display_Employee_Name_In_Uppercase that accepts the Employee_ID from the Empoyees table and returns the first and the last name of the employee in uppercase.
2. Write a small PL/SQL program to display the names of the employees whose Employee_IDs are 107, 200 and 205.

这是我所做的，我不知道如何完成 可以帮忙吗?

this is what I have done I didnt know how to complete it can help ?

CREATE OR REPLACE FUNCTION disp (emp_id in  varchar20) return emp_name
select into emp_name 
fname||lname 
from employees 
where employee_id=emp_id
END disp ;

推荐答案

类似这样的事情...

Something like this...

CREATE OR REPLACE 
FUNCTION Fn_Display(p_empId IN VARCHAR2)
RETURN VARCHAR2

IS

empName VARCHAR2(100);    

BEGIN

 BEGIN
  SELECT UPPER(first_name || ' ' || last_name)
  INTO  empName
  FROM Employees
  WHERE employee_id = p_empId; 
 EXCEPTION 
    WHEN NO_DATA_FOUND THEN
       RAISE NO_DATA_FOUND                
 END;


 RETURN empName;

END Fn_Display;

您可以在任意位置调用此函数.这是一个示例...

You can call this function wherever you want. here is a sample...

  DECLARE

    empId VARCHAR2(100);
    empName VARCHAR2(100);

  BEGIN

    empId := &ENTER_EMPLOYEE_ID;
    empName := Fn_Display(empId);

    DBMS_OUTPUT.PUT_LINE('Employee Name: '||empName);

  END;

这篇关于PL/SQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！