Oracle Stored Procedures

Tag: T-SQL statement cannot execute under stored procedure call instead of Div Note command execution

What is a stored procedure? A stored procedure is a named PL/SQL program block, which is a block of code that consists of some T-SQL statements that implement functions like a method (to and from a single table or multiple tables), can have parameters, input and output parameters, and usually no return value. It cannot be invoked or executed directly by an SQL statement, but only by the execute command or within a PL/SQL program block. advantages of stored procedures1. A generic T-SQL statement is compiled once per execution, whereas a stored procedure is a compiled block of code that can be used without compiling a direct call, so the execution is more efficient than a T-SQL statement. 2. A stored procedure can replace a large number of T-SQL statements when the program interacts with the network, so it can reduce the traffic (traffic) of the network and improve the communication rate. 3. When a database is doing complex operations, such as when you update, Insert, Query, and delete multiple tables, you can encapsulate this complex-action stored procedure with the transactional processing provided by the database. 4. The stored procedure Code is streamlined and consistent and can be reused. 5. High security, can be set only one user has access to the specified stored procedure. Basic syntax for creating stored procedures: create [or Replace] procedure pro_name[(Parameter1[,parameter2] ...)] is|asbeginplsql_sentence; [Exception] [Dowith_sentence;] End [pro_name]; pro_name: Stored procedure name, if the data already exists for this name, you can specify the "or replace" keyword so that the new stored procedure overwrites the original stored procedure. Parameter1: The parameters of the stored procedure, the input parameters must be specified after the "in" keyword, the output parameter must be specified after the "out" keyword. The parameter type cannot specify a length. The Plsql_sentences:pl/sql statement, which is the main body of the stored procedure function implementation. Dowith_sentences: Exception handling statements, also PL/SQL statements, throws exceptions in code with the raise+ exception name, which is an optional item. Note: "Parameter1" is a parameter that is used when a stored procedure is called or executed, rather than an internal variable defined inside a stored procedure. The internal variable is defined after the "is|as" keyword and ends with a semicolon (;). calls to stored proceduresExecute procedure_name--stored procedure if there are parameters, the following parameter format is: @ parameter name =value, can also be directly the parameter value. deletion of stored proceduresdrop procedure procedure_name--can call another stored procedure in the stored procedure, but cannot delete another stored procedure

Oracle Stored Procedures