【Persistence Tier using Oracle and JPA】【Assignment】【Day1】

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;
/