Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • Hour 7. Introduction to the Database Query
  • Hour 8. Using Operators to Categorize Data
  • Hour 9. Summarizing Data Results from a Query
  • Hour 10. Sorting and Grouping Data
  • Hour 11. Restructuring the Appearance of Data
  • Hour 12. Understanding Dates and Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
    • Advanced Topics
    • Cursors
    • Stored Procedures and Functions
    • Triggers
    • Dynamic SQL
    • Call-Level Interface
    • Using SQL to Generate SQL
    • Direct Versus Embedded SQL
    • Summary
    • Q&A
    • Workshop
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

Stored Procedures and Functions

Stored procedures are groupings of related SQL statements—commonly referred to as functions and subprograms—that allow ease and flexibility for a programmer. This ease and flexibility are derived from the fact that a stored procedure is often easier to execute than a number of individual SQL statements. Stored procedures can be nested within other stored procedures. That is, a stored procedure can call another stored procedure, which can call another stored procedure, and so on.

Stored procedures allow for procedural programming. The basic SQL DDL, DML, and DQL statements (CREATE TABLE, INSERT, UPDATE, SELECT, and so on) allow you the opportunity to tell the database what needs to be done, but not how to do it. By coding stored procedures, you tell the database engine how to go about processing the data.

A stored procedure is a group of one or more SQL statements or functions that are stored in the database, compiled, and are ready to be executed by a database user. A stored function is the same as a stored procedure, but a function is used to return a value.

Functions are called by procedures. When a function is called by a procedure, parameters can be passed into a function like a procedure, a value is computed, and then the value is passed back to the calling procedure for further processing.

When a stored procedure is created, the various subprograms and functions (that use SQL) that compose the stored procedure are actually stored in the database. These stored procedures are pre-parsed, and are immediately ready to execute when invoked by the user.

The Microsoft SQL Server syntax for creating a stored procedure is as follows:

   
CREATE PROCEDURE PROCEDURE_NAME
[ [(] @PARAMETER_NAME
DATATYPE [(LENGTH) | (PRECISION] [, SCALE ])
[ = DEFAULT ][ OUTPUT ]]
[, @PARAMETER_NAME
DATATYPE [(LENGTH) | (PRECISION [, SCALE ])
[ = DEFAULT ][ OUTPUT ]] [)]]
[ WITH RECOMPILE ]
AS SQL_STATEMENTS

The syntax for Oracle is as follows:

   
CREATE [ OR REPLACE ] PROCEDURE PROCEDURE_NAME
[ (ARGUMENT [{IN | OUT | IN OUT} ] TYPE,
ARGUMENT [{IN | OUT | IN OUT} ] TYPE) ] {IS | AS}
PROCEDURE_BODY

An example of a very simple stored procedure is as follows:

   
   

   CREATE PROCEDURE NEW_PRODUCT

   (PROD_ID IN VARCHAR2, PROD_DESC IN VARCHAR2, COST IN NUMBER)

   AS

   BEGIN
  
   INSERT INTO PRODUCTS_TBL
  
   VALUES (PROD_ID, PROD_DESC, COST);
  
   COMMIT;

   END;

   
Procedure created.

This procedure is used to insert new rows into the PRODUCTS_TBL table.

The syntax for executing a stored procedure in Microsoft SQL Server is as follows:

   
EXECUTE [ @RETURN_STATUS = ]
PROCEDURE_NAME
[[@PARAMETER_NAME = ] VALUE |
[@PARAMETER_NAME = ] @VARIABLE [ OUTPUT ]]
[WITH RECOMPLIE]

The syntax for Oracle is as follows:

   
EXECUTE [ @RETURN STATUS =] PROCEDURE NAME
[[ @PARAMETER NAME = ] VALUE | [ @PARAMETER NAME = ] @VARIABLE [ OUTPUT ]]]
[ WITH RECOMPLIE ]

Now execute the procedure you have created:

   

   EXECUTE NEW_PRODUCT ('9999','INDIAN CORN',1.99);

   
PL/SQL procedure successfully completed.

Advantages of Stored Procedures and Functions

Stored procedures provide several distinct advantages over individual SQL statements executed in the database. Some of these advantages include the following:

• The statements are already stored in the database.
• The statements are already parsed and in an executable format.
• Stored procedures support modular programming.
• Stored procedures can call other procedures and functions.
• Stored procedures can be called by other types of programs.
• Overall response time is typically better with stored procedures.
• Overall ease of use.

Share This