First, basic syntax (and comparison with Informix) create [or Replace] procedure procedure_name (varible {in| Out| In Out} type) {Is|as}
[Varible {in | out | on out}] type;
[Varible {in | out | on out}] type;
...... [Cursor Curor_name is select subclause]
Begin body End [procedure_name];
For example:create or replace procedure Sp_login_error_count (User_name_var varchar2) is
--Create a stored procedure named Sp_login_error_count and define the parameters
--informix syntax (drop procedure Sp_login_error_count;create procedure Sp_login_error_count (User_name_var varchar (50))) Error_count_var integer;
--Variable definition
--informix syntax (define Error_count_var integer;) begin
--Program Start
--informix syntax like select Error_count into Error_count_var from users where User_name=user_name_var;
--select statement, the found value is placed in the variable
--informix syntax as Error_count_var: =error_count_var+1;
--Variable plus 1, which is the variable assignment operation of PL/SQL.
--informix syntax (let error_count_var=error_count_var+1;) update users set error_count= Error_count_var where user_name = User_name_var;
--Update STATISTICS
--informix syntax like commit;
--Submit
--informix does not need to submit end sp_login_error_count;
--End stored procedure
--informix syntax (end procedure;)
Oracle stored procedure executes as execute procedure_name or exec procedure_name
The Informix stored procedure executes as execute procedure procedure_name PL/SQL program consists of three blocks: Declaration part, execution part, exception handling part declare
/* Declaration section: The variables, types and cursors that are used in this declaration of PL/SQL */
Begin
/* Execute part: Process and SQL statement, which is the main part of the program */
Exception
/* Execute exception section: Error handling */
End; Where the execution part is required.
Go: Oracle Stored procedure NOTE 1----basic syntax (and comparison with Informix)