Go: Oracle Stored procedure NOTE 1----basic syntax (and comparison with Informix)

Source: Internet
Author: User
Tags informix

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.