Assignment 1:

一、

Create table employee (

empNo number,

empName varchar2(20),

empSalary number(8,2),

grade char(1)

);

 

 

二、

CREATE OR REPLACE PROCEDURE sp_Get_Grade

(

p_eNo IN EMPLOYEE.EmpNo%TYPE:=0 ,

 p_eGrade OUT EMPLOYEE.Grade%TYPE

)

 IS

BEGIN

SELECT grade into p_eGrade FROM employee WHERE EmpNo = p_eNo;

EXCEPTION

WHEN NO_DATA_FOUND THEN

p_eGrade := 'Z';

WHEN OTHERS THEN

p_eGrade :='Z';

dbms_output.put_line('*** Error occurred ***');

dbms_output.put_line('SQLCODE: '||to_char(SQLCODE));

dbms_output.put_line('SQLERRM: '||SQLERRM);

END;

/

 

三、

DECLARE

v_employeeNo EMPLOYEE.EmpNo%TYPE;

v_employeeGrade EMPLOYEE.Grade%TYPE;

BEGIN

v_employeeNo := 1;

sp_Get_Grade(v_employeeNo, v_employeeGrade);

IF v_employeeGrade = 'Z' THEN

dbms_output.put_line('Employee No Not Found');

ELSE

dbms_output.put_line('Employee Grade is '||v_employeeGrade);

END IF;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('*** Error occurred ***');

dbms_output.put_line('SQLCODE: '||to_char(SQLCODE));

dbms_output.put_line('SQLERRM: '||SQLERRM);

END;

/