Oracle Stored Procedures

The complete syntax structure for Oracle stored procedures is as follows:

　　

Create or Replace procedure  as     declaration statement segment; begin     execute statement segment; exception    exception handling statement segment; End;

As keyword replaces the declare of the nameless block

Call the stored procedure statement:

Call Procedure_name (); where () is essential, whether it has parameters or no parameters

Assignment statement:

Select XXX into ..... or direct variable: =xxx

Well, here's a simple example to better understand the stored procedure

Create a table student

Create TableStudent (No Number(6)Primary Key, namevarchar2( -), age Number(4)          );Insert  intoStudent (No,name,age)Values(1, ' Liu Yifei ', -);Insert  intoStudent (No,name,age)Values(2, ' Ivy Chen ', -);
Commit

Create a simple stored procedure and invoke the

Create or Replace procedure  as VARCHAR2 (+);         begin       Select  into  from where no=2;      Dbms_output.put_line (p_name); End ; call Stu_proc ();

You can see the output Ivy Chen

The stored procedure above is the simplest stored procedure with no parameters, and the following is a demonstration of a stored procedure with input parameters

Create or Replace procedureStu_proc1 (SnoinchStudent.no%Type asP_namevarchar2( -);begin        SelectName intoP_name fromStudentwhereNo=Sno; Dbms_output.put_line (p_name);End; call Stu_proc1 (1);

Call Stu_proc1 (1) to output Liu Yifei

-----------------------------------------------------

To improve the robustness of stored procedures and avoid running errors, the exception handling section should be included when establishing a stored procedure

An exception (EXCEPTION) is a PL/SQL identifier that includes predefined exceptions, non-pre-defined exceptions, and custom exceptions

Predefined exceptions are system exceptions provided by PL/SQL, non-predefined exceptions for Oracle errors unrelated to predefined exceptions, such as integrity constraints, and custom exceptions for handling other exceptions to Oracle errors

Raise_application_error is used to customize the error message, and the message number must be between -20000~-20999

Here's a simple example to demonstrate

Drop procedureStu_proc1;Create procedureStu_proc1 (SnoinchStudent.no%Type asP_namevarchar2( -);begin        SelectName intoP_name fromStudentwhereNo=Sno; Exception whenNo_data_found ThenRaise_application_error (-20011,'ERROR: not present!'); Dbms_output.put_line (p_name);End; call Stu_proc1 (3);

Oracle Stored Procedures