Creation and invocation of--pl/sql stored procedures and functions in Oracle Database

1. Format the stored procedure establishes:

Create or replace procedure My_procedure is
Begin
--Execution part (function content);
End
/

Example: (Take HR table for example)

Create or replace procedure Insert_procedure is
Begin
INSERT into JOBS values (' my_job ', ' MY JOB ', 5000,10000);
End
/

2. Stored Procedure Calls

Begin
Insert_procedure ();
End
/

3. Form of function:

CREATE OR REPLACE FUNCTION function_name (arg1 type1)
RETURN Return_type
Is
--type. Declaration part of a variable
BEGIN
--Executive Section
RETURN expression
EXCEPTION
--Exception Handling section
END function_name;

Example: (Take HR table for example)

Create or Replace function update_min_salary return number
Is
n number;
Begin
Update JOBS set min_salary=5000 where min_salary=4000;
N:=sql%rowcount;
Dbms_output.put_line (' Number of rows affected: ' | | n);
return n;
End
/

4. Calling functions

Declare
M number;
Begin
M:=update_min_salary ();
Dbms_output. Put_Line (' function received from function to call this function is: ' | | m);
End
/

5.1

Note: If you want to display the server output information in the window, first execute this sentence: set serveroutput on;

5.2

Finally, be sure to commit with commit, otherwise the execution result will not update the database.

Creation and invocation of--pl/sql stored procedures and functions in Oracle Database