How to correctly write Oracle stored procedures

Introduction: This article mainly describes the correct writing of Oracle stored procedures. Here we mainly write the stored procedures for inserting, deleting, and modifying records.

1. insert:

Code

Create or replace Procedure p_insert_t_stu

Stored Procedure name

(
P_stuid in Number,
P_stuname in Nvarchar2,
P_stusex in Nvarchar2,
P_stuadd in Nvarchar2
)
As
BEGIN
Insert into t_stu
Values
(P_stuid, p_stuname, p_stusex, p_stuadd );
Commit;
End;

Ii. Delete:

Code

Create or replace Procedure p_delete_t_stu

Stored Procedure name

(P_stuid in Number, p_msg Out Nvarchar2) Is flag Integer: = 1; v_stuid Number; Begin Select flag Into v_stuid From t_stu Where stuid = p_stuid; Delete t_stu Where stuid = p_stuid; commit; if flag = 1 Then Begin
P_msg: = 'deleted successfully ';
End; End If; Exception When Others Then
P_msg: = Sqlerrm | ',' | 'deletion failed ';
END;

3. modification:

Code

Create or replace Procedure p_update_t_stu

Stored Procedure name

(
P_stuid in Number,
P_stuname in Nvarchar2,
P_stusex in Nvarchar2,
P_stuadd in Nvarchar2
)
As
BEGIN
Update t_stu Set stuname = p_stuname, stusex = p_stusex, stuadd = p_stuadd
Where
Stuid = p_stuid;
Commit;
End;

Code writing is a very important part of the database operation process. Everyone must pay great attention to the details. Once there is a small error, you will be able to discard your efforts. Be careful.