SQL is required. Note that chapter 19th uses stored procedures. SQL is required.

Source: Internet
Author: User

SQL is required. Note that chapter 19th uses stored procedures. SQL is required.
19.1 stored procedures

Stored Procedure:A set of one or more SQL statements saved for future use. It can be considered as a batch file, although they are not limited to batch processing.

19.2 why use stored procedures

Reasons for using Stored Procedures
(1) simplify complicated operations by encapsulating processing in easy-to-use units.
(2) Data Consistency is ensured because a series of processing steps are not required.
(3) simplify the management of changes.
(4) Since stored procedures are usually stored in compiled form, DBMS does little work to process commands. Improved performance.
(5) There are some SQL elements and features that can only be used in a single request. stored procedures can be used to write code with stronger functionality and greater flexibility.
In other words, stored procedures have three main advantages:Simple, secure, and high-performance.
Storage Process Defects
(1) The Stored Procedure syntax varies with DBMS. In fact, writing a truly portable stored procedure is almost impossible. However, the self-calling of stored procedures can be relatively portable.
(2) In general, writing stored procedures is more complex than writing Basic SQL statements. Writing stored procedures requires higher skills and rich experience.
Most DBMS separates the security and access of stored procedures from the security and access of stored procedures. Even if you cannot write your own stored procedures, You can execute other stored procedures as appropriate.

19.3 execute the Stored Procedure

The SQL statement used to EXECUTE the stored procedure is simple, that is, EXECUTE. EXECUTE accepts the stored procedure name and any parameters that need to be passed to it.

EXECUTE AddNewProduct('JTS01',     'Stuffed Eiffel Tower',     6.49,     'Plush stuffed toy with the text La Tour Eiffel in red white and blue')

Stored Procedure:
(1) Check transmitted data to ensure that all parameters have values.
(2) generate a unique ID used as the primary key.
(3) Insert the new product into the Products table and store the generated primary key and transmitted data in the appropriate columns.

19.4 create a stored procedure
CREATE PROCEDURE MailingListCount(ListCount OUT NUMBER)ISBEGIN     SELECT * FROM Customers     WHERE NOT cust_email IS NULL;     ListCount := SQL%ROWCOUNT;END;

This stored procedure has a parameter named ListCount. This parameter returns a value from the stored procedure instead of passing a value to the stored procedure.
The Stored Procedure Code is included in the BEGIN and END statements. Here, a simple SELECT statement is executed to retrieve customers with email addresses. Then set ListCount (output parameter to be passed) with the retrieved number of rows ).
Annotation code:All codes should be commented out, and stored procedures are no exception. Adding annotations does not affect performance, so there are no defects. There are many benefits for commenting on code, including easier understanding and more secure code modification.

Related Article

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.