Oracle Stored Procedures

--Stored procedure: A set of SQL statements that complete a specific function
--If the user new ID number is not enough 18 bit error
Create or replace procedure Pro_add_teacher
(
P_tno number,
P_tname VARCHAR2,
P_tid Char,
P_sal number
)
Is
E_tid_validate exception;

Begin
If Length (P_tid)!=18--judge the ID number is not enough 18 bit error
Then--Throws an exception
Raise E_tid_validate;
End If;
Insert into teacher (tno,tname,tid,sal)
Values (p_tno,p_tname,p_tid,p_sal);
--Auto COMMIT Transaction
Commit
--handling of exceptions
exception
When E_tid_validate Then
Dbms_output.put_line (' Please enter the correct ID number! ‘);
When others then
Dbms_output.put_line (' Other anomalies have appeared! ‘);
End Pro_add_teacher;

--Call the stored procedure

Call Pro_add_teacher (2088, ' Austrian Black Donkey ', ' 123456789123568947 ', 5000.5);

Oracle Stored Procedures