Skip to content
≡

Admin

Developer

Tools

External Tools

Preface#

About This Manual#

This manual explains how to use stored procedures with Altibase.

Audience#

This manual has been prepared for the following Altibase users:

  • Database administrators
  • Performance administrators
  • Database users
  • Application developers
  • Technical Supporters

It is recommended for those reading this manual possess the following background knowledge:

  • Basic knowledge in the use of computers, operating systems, and operating system utilities
  • Experience in using relational database and an understanding of database concepts
  • Computer programming experience
  • Experience in database server management, operating system management, or network administration

Organization#

This manual is organized as follows:

  • Chapter 1: Introduction to Stored Procedures
    This chapter explains the concept and structure of stored procedures, and the notes on using them.

  • Chapter 2: SQL Statements for Managing Stored Procedures This chapter explains the SQL statements that are used to manage stored procedures.

  • Chapter 3: Stored Procedure Blocks
    This chapter explains the concept of stored procedure blocks, how to define local variables within the body of stored procedures, and which statements can be used in stored procedures

  • Chapter 4: Control Flow Statements
    This chapter explains the control flow statements that can be used to author a procedural program within the body of a stored procedure.

  • Chapter 5: Using Cursors
    This chapter explains cursor-related statements, which are used to define and control cursors so that multiple records returned by a SELECT statement can be processed within a stored procedure.

  • Chapter 6: User-Defined Types
    This chapter explains how to define and use records and associative arrays, which are user-defined types that can be used within stored procedures and functions.

  • Chapter 7: Typesets
    This chapter explains how to define and use user-defined typesets.

  • Chapter 8: Dynamic SQL
    This chapter explains dynamic SQL, which enables queries to be created and executed as desired by the user at runtime.

  • Chapter 9: Exception Handlers
    This chapter explains the exception handler, which handles exceptions when an error occurs while a stored procedure is being executed.

  • Chapter 10: Pragma
    This chapter describes the pragma which has an impact on exection of stored procedure compile and how to use them.

  • Chapter 11: Stored Packages
    This chapter describes how to create and use stored packages.

  • Chapter 12: Altibase Stored Procedures and Built-in Function
    Altibase provides a variety of built-in stored procedures and functions. This chapter introduces these stored procedures and functions and explains their use.

  • Chapter 13: Altibase System-defined Stored Packages
    This chapter discusses system-defined stored packages provided by Altibase.

  • Appendix A. Examples
    This appendix explains the schema and sample program examples used in this manual.

Documentation Conventions#

This section describes the conventions used in this manual. Understanding these conventions will make it easier to find information in this manual and in the other manuals in the series.

There are two sets of conventions:

  • Syntax diagram convetions
  • Sample code conventions
Syntax Diagram Conventions#

This manual describes command syntax using diagrams composed of the following elements:

Elements Meaning
image1 Indicates the start of a command. If a syntactic element starts with an arrow, it is not a complete command.
image2 Indicates that the command continues to the next line. If a syntactic element ends with this symbol, it is not a complete command.
image3 Indicates taht the command continues from the previous line. If a syntactic element starts witht his symbol, it is not a complete command.
image4 Indicates the end of a statement.
image5 Indicates a manatory element.
image6 Indicates an optional element.
image7 Indicates a mandatory element comprised of options. One, and only one, option must be specified.
image8 Indicates an optional element comprised of options.
image9 Indicates an optional element in which multiple elements may be specified. A comman must precede all but the first element.
Sample Code Conventions#

The code examples explain SQL statements, stored procedures, iSQL statements, and other command line syntax.

The following table describes the printing conventions used in the code examples.

Rules Meaning Example
[ ] Indicates an optional item VARCHAR [(size)] [[FIXED |] VARIABLE]
{ } Indicates a mandatory field for which one or more items must be selected. { ENABLE | DISABLE | COMPILE }
| A delimiter between optional or mandatory arguments. { ENABLE | DISABLE | COMPILE } [ ENABLE | DISABLE | COMPILE ]
. . . Indicates that the previous argument is repeated, or that sample code has been omitted. SQL> SELECT ename FROM employee;
ENAME
-----------------------
SWNO
HJNO
HSCHOI
.
.
.
20 rows selected.
Other Symbols Symbols other than those shown above are part of the actual code. EXEC :p1 := 1; acc NUMBER(11,2)
Italics Statement elements in italics indicate variables and special values specified by the user. SELECT * FROM table_name;
CONNECT userID/password;
Lower case words Indicate program elements set by the user, such as table names, column names, file names, etc. SELECT ename FROM employee;
Upper case words Keywords and all elements provided by the system appear in upper case. DESC SYSTEM_.SYS_INDICES_;
Sample Schema#

Some of the examples in this manual are based on sample tables, including the employees, departments and orders tables. These tables can be created using the schema.sql file in the $ALTIBASE_HOME/sample/APRE/schema directory. For complete information on the sample schema, please refer to the Altibase General Reference.

For more detailed information, please refer to the following documents.

  • Installation Guide

  • Getting Started Guide

  • SQL Reference

  • iSQL User’s Manual

  • Error Message Reference

Altibase Welcomes Your Comments and Feedbacks#

Please let us know what you like or dislike about our manuals. To help us with better future versions of our manuals, please tell us if there is any corrections or classifications that you would find useful.

Include the following information:

  • The name and version of the manual that you are using
  • Any comments about the manual
  • Your name, address, and phone number

If you need immediate assistance regarding any errors, omissions, and other technical issues, please contact Altibase's Support Portal.

Thank you. We always welcome your feedbacks and suggestions.