SQL Statements
CREATE PROCEDURE
Use the CREATE PROCEDURE statement to register an external procedure or to write and register an SPL procedure.
Syntax
Usage
A procedure is a user-defined routine that can accept arguments but does not return a value. INFORMIX-Universal Server supports procedures written in the following languages:
The entire length of a CREATE PROCEDURE statement must be less than 64 kilobytes. This length is the literal length of the statement, including blank space and tabs.
Routines, Functions, and Procedures
In INFORMIX-Universal Server, routine is a generic term that includes both procedures and functions. A procedure is a routine that can accept arguments but does not return any values. A function is routine that can accept arguments and returns one or more values. Universal Server treats any routine that includes a Return clause as a function.
Legacy Procedures
In earlier Informix products, the term stored procedure was used for both SPL procedures and SPL functions. As a result, you may have created functions with CREATE PROCEDURE in the past. For backward compatibility with earlier products, you can continue to create SPL functions with CREATE PROCEDURE. However, with Universal Server, Informix recommends that you use CREATE PROCEDURE only with procedures and CREATE FUNCTION only with functions.
For more information on CREATE FUNCTION, see page 1-125. 
SPL Procedures
SPL procedures are routines written in Stored Procedure Language (SPL) that do not return a value.
Use one CREATE PROCEDURE statement, with SQL and SPL statements embedded between CREATE PROCEDURE and END PROCEDURE, to write and register an SPL procedure. Unlike external procedures, you do not need to write the procedure and register it in separate steps.
SPL procedures are parsed, optimized (as far as possible), and stored in the system catalog tables in executable format. The body of an SPL procedure is stored in the sysprocbody system catalog table. Other information about the procedure is stored in other system catalog tables, including sysprocedures, sysprocplan, and sysprocauth. For more information about these system catalog tables, see Chapter 1, "System Catalog," in the Informix Guide to SQL: Syntax.
You must use the END PROCEDURE keywords with an SPL procedure.
If you use a Return clause or a Specific Name clause, place a semicolon after the clause immediately before the SPL statement block. If you do not use a Return clause or a Specific Name clause, do not place a semicolon after the CREATE PROCEDURE statement. Always place a semicolon at the end of the entire statement, after the END PROCEDURE, DOCUMENT, or WITH LISTING IN clause.
Example
The following example creates a SPL procedure:
For more information on writing SPL procedures, see Chapter 14, "Creating and Using SPL Routines," in the Informix Guide to SQL: Tutorial.
External Procedures
External procedures are procedures you write in an external language that the Universal Server supports. To create external procedures, follow these steps:
1. Write the procedure in an external language, such as C, that Universal Server supports.
2. Compile the procedure and store the compiled code in a shared library.
3. Register the procedure in the database server with the CREATE PROCEDURE statement.
When Universal Server executes an external procedure, the database server invokes the external object code.
Universal Server does not store the body of an external procedure directly in the database, as it does for SPL procedures. Instead, the database server stores only a pathname to the compiled version of the procedure. You specify this pathname in the External Routine Reference clause.
The database server does store information about an external procedure in several system catalog tables, including sysprocbody and sysprocauth. For more information on these system catalog tables, see Chapter 1, "System Catalog," in the Informix Guide to SQL: Syntax.
With external procedures, the END PROCEDURE keywords are optional.
Example
The following example registers an external C procedure named check_owner() in the database. This procedure takes one argument of the type lvarchar. The external routine reference specifies the path to the C shared library where the procedure object code is stored. This library contains a function unix_owner(), which is invoked during execution of the check_owner() procedure.
Using the DBA Keyword
The level of privilege necessary to execute a routine depends on whether the routine is created with the DBA keyword. The DBA keyword limits execution of the procedure to those users who have the DBA privilege.
You need the DBA privilege to create a procedure using the DBA keyword. You need the DBA privilege to execute a procedure that is created with the DBA keyword.
If you do not use the DBA option, the procedure is known as an owner-privileged procedure. If the procedure is owner privileged, and if the database is ANSI compliant, anyone can execute the procedure.
If you create an owner-privileged routine in a database that is not ANSI-compliant, the NODEFDAC environment variable prevents privileges on that routine from being granted to PUBLIC. See the Informix Guide to SQL: Syntax for further information on the NODEFDAC environment variable.
Procedure Name
Because Universal Server offers routine overloading, you can define more than one procedure with the same name but different parameter lists. You may want to overload procedures if you are defining a type hierarchy or a system of distinct types or casts. When you overload procedures, you can create a procedure for the new data types you define.
The process of overloading routines and the routine resolution rules are described briefly in "Routine Resolution".
The syntax of the Procedure Name segment is described in "Procedure Name".
Parameter List
To define the parameters for an SPL procedure, specify a parameter name and a data type for each parameter. For more information about defining parameters, see "Routine Parameter List".
To define the parameters for an external routine, you can specify a name, and you must specify a data type for each parameter. For more information on the syntax of the parameter list, see "Routine Parameter List".
Return Clause
The database server considers any routine that is created with a Return clause to be a function. Informix recommends that you use the CREATE FUNCTION statement, not CREATE PROCEDURE, to create functions. For external routines, this rule is strictly enforced.
The syntax of the Return clause is described in "Return Clause".
In SPL, you can use CREATE PROCEDURE to write and register a routine that returns one or more values (that is, a function). However, this feature is offered only for backward compatibility with earlier Informix products. Informix recommends that you do not use CREATE PROCEDURE to create functions.
You cannot specify a Return clause for an external procedure. An external procedure does not return a value.
Specific Name
You can specify a specific name for an SPL procedure or an external procedure. A specific name is a name that is unique in the database. A specific name is useful, because due to routine overloading, more than one procedure can have the same name.
The syntax of the Specific Name is described in "Specific Name".
Procedure Modifier
When you write an SPL procedure, you cannot specify a procedure modifier in the CREATE PROCEDURE statement.
In the CREATE PROCEDURE statement, you can specify any of a list of procedure modifiers with a WITH clause. For more information on the procedure modifiers, see "Routine Modifier".
Statement Block
In an SPL routine, you must specify an SPL statement block instead of an external routine reference. The syntax of the statement block is described in "Statement Block".
External Routine Reference
When you register an external procedure, you must specify an External Routine Reference clause. The External Routine Reference clause specifies the pathname to the procedure object code, which is stored in a shared library. The External Routine Reference Clause also specifies the name of the language in which the procedure is written. For more information on the External Routine Reference clause, see "External Routine Reference".
DOCUMENT Clause
The quoted string in the DOCUMENT clause provides a synopsis and description of the routine. The string is stored in the sysprocbody system catalog table and is intended for the user of the routine.
To find the description of the SPL procedure raise_prices, shown in "SPL Procedures", enter a query such as the following:
The preceding query returns the following text:
An SPL routine, external routine, or application program can query the system catalog tables to fetch the DOCUMENT clause and display it for a user.
You can use a DOCUMENT clause at the end of the CREATE PROCEDURE statement, whether or not you use END PROCEDURE.
WITH LISTING IN Clause
The WITH LISTING IN option specifies a filename where compile-time warnings are sent. This listing file is created on the database server when you compile an SPL or external routine.
If you specify a filename but not a directory in the WITH LISTING IN clause, Universal Server uses the home directory on the database server as the default directory. If you do not have a home directory on the server, the file is created in the root directory.
If you do not use the WITH LISTING IN option, the compiler does not generate a list of warnings.
Privileges Necessary for Using CREATE PROCEDURE
You must have the Resource privilege on a database to create a procedure within that database. The owner of a procedure grants the Execution privilege to on that procedure to other users.
Routine Resolution
In Universal Server, you can have more than one instance of a routine with the same name but different parameter lists, as in the following situations:
Routine resolution is the process of determining which instance of a function to execute, given the name of a routine and a list of arguments. For more information on routine resolution, refer to the Extending INFORMIX-Universal Server: User-Defined Routines manual.
PREPARE Statement
You can use a CREATE PROCEDURE statement only within a PREPARE statement. If you want to create a procedure for which the text is known at compile time, you must put the text in a file and specify this file with the CREATE PROCEDURE FROM statement. For more information, see the CREATE PROCEDURE FROM statement on page 1-191.
References
See the CREATE FUNCTION, CREATE PROCEDURE FROM, DROP FUNCTION, DROP PROCEDURE, DROP ROUTINE, EXECUTE FUNCTION, EXECUTE PROCEDURE, GRANT, PREPARE, UPDATE STATISTICS, and REVOKE statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of how to create and execute SPL routines on page 14-1.
In the Extending INFORMIX-Universal Server: User-Defined Routines manual, see the discussion of how to create and use external procedures
CREATE PROCEDURE FROM
Use the CREATE PROCEDURE FROM statement to create a procedure. The actual text of the CREATE PROCEDURE statement resides in a separate file.
Syntax
Usage
An INFORMIX-ESQL/C program cannot directly create a stored procedure or external procedure. That is, it cannot contain the CREATE PROCEDURE statement. However, you can create these functions within an ESQL/C program with the following steps:
1. Create a source file with the CREATE PROCEDURE statement.
2. Use the CREATE PROCEDURE FROM statement to send the contents of this source file to the database server for execution.
For example, suppose that the following CREATE PROCEDURE statement is in a separate file, called raise_pr.sql:
In the ESQL/C program, you can create the raise_prices() stored procedure with the following CREATE PROCEDURE FROM statement:
The filename that you provide is relative; if you provide a simple filename (as in the preceding example), the client application looks for the file in the current directory.
References
See the CREATE FUNCTION, CREATE FUNCTION FROM, CREATE PROCEDURE, and CREATE ROUTINE FROM statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of how to create and use SPL procedures in Chapter 14.
CREATE ROLE
Use the CREATE ROLE statement to create a new role.
Syntax
Usage
The database administrator (DBA) uses the CREATE ROLE statement to create a new role. A role can be considered as a classification, with privileges on database objects granted to the role. The DBA can assign the privileges of a related work task, such as engineer, to a role and then grant that role to users, instead of granting the same set of privileges to every user.
After a role is created, the DBA can use the GRANT statement to grant the role to users or to other roles. When a role is granted to a user, the user must use the SET ROLE statement to enable the role. Only then can the user use the privileges of the role.
The CREATE ROLE statement, when used with the GRANT and SET ROLE statements, allows a DBA to create one set of privileges for a role and then grant the role to many users, instead of granting the same set of privileges to many users.
A role exists until it is dropped either by the DBA or by a user to whom the role was granted with the WITH GRANT OPTION. Use the DROP ROLE statement to drop a role.
To create the role engineer, enter the following statement:
References
See the DROP ROLE, GRANT, REVOKE, and SET ROLE statements in this manual.
CREATE ROUTINE FROM
Use the CREATE ROUTINE FROM statement to create a routine. The actual text of the CREATE FUNCTION or CREATE PROCEDURE statement resides in a separate file.
Syntax
Usage
An INFORMIX-ESQL/C program cannot directly define a routine. That is, it cannot contain the CREATE FUNCTION or CREATE PROCEDURE statement. However, you can create these functions within an ESQL/C program with the following steps:
1. Create a source file with the CREATE FUNCTION or CREATE PROCEDURE statement.
2. Use the CREATE ROUTINE FROM statement to send the contents of this source file to the database server for execution.
The filename that you provide is relative. If you provide a simple filename (as in the preceding example), the client application looks for the file in the current directory.
If you know at compile time whether the routine in the file is a function or a procedure, use the CREATE ROUTINE FROM statement in the ESQL/C program. However, if you do know whether the routine is a function or procedure, Informix recommends that you use the matching statement to create the file:
Use of the matching statements improves the readability of the code.
References
See the CREATE FUNCTION, CREATE FUNCTION FROM, CREATE PROCEDURE, and CREATE PROCEDURE FROM statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of creating and using stored procedures in Chapter 14.
CREATE ROW TYPE
Use the CREATE ROW TYPE statement to create a named row type.
Syntax
Usage
The CREATE ROW TYPE statement creates a named row type. You can assign a named row type to a table or view to create a typed table or typed view. You can also assign a named row type to a column. Although you can assign a row type to a table to define the structure of the table, row types are not the same as table rows. Table rows consist of one or more columns; row types consist of one or more fields, which are defined using the Extended Field Definition syntax. For a full discussion of named row types and typed tables, see Chapter 10, "Understanding Complex Data Types," in the Informix Guide to SQL: Tutorial.
You can use a named row type anywhere you can use any other type. Named row types are strongly typed. Any two named row types are not considered equivalent even if they are structurally equivalent. Row types without names are called unnamed row types. Any two unnamed row types are considered equivalent if they are structurally equivalent. For more information on named row types and unnamed row types, see the section "Complex Data Type" of this manual and Chapter 10, "Understanding Complex Data Types" in the Informix Guide to SQL: Tutorial.
Privileges on Named Row Types
The following table indicates which privileges you must have to create a row type.
To find out what privileges you have on a particular data type, check the sysxtdtypes system catalog table. This table is described in Chapter 1 of the Informix Guide to SQL: Syntax.
See the reference pages for GRANT, beginning on page 1-461, for information about the RESOURCE, UNDER, and ALL privileges.
Privileges on a typed table (a table that is assigned a named row type) are the same as privileges on any table. Refer to the CREATE TABLE statement on page 1-211 and the "Table-Level Privileges" section of the GRANT statement on page 1-461.
To find out what privileges you have on a particular table, check the systabauth system catalog table. This table is described in Chapter 1 of the Informix Guide to SQL: Syntax.
Privileges on Named Row Type Columns
Privileges on named row type columns are the same as privileges on any column. For more information, see the "Table-Level Privileges" section of the GRANT statement on page 1-461.
To find out what privileges you have on a particular column, check the syscolauth system catalog table. This table is described in Chapter 1 of the Informix Guide to SQL: Syntax.
Inheritance and Named Row Types
A named row type can belong to an inheritance hierarchy, as either a subtype or a supertype. You use the UNDER clause in the CREATE ROW TYPE statement to create a named row type as a subtype. The supertype must also be a named row type.
When you create a named row type as a subtype, the subtype inherits the following properties:
In addition, you can add new fields to the subtype that you create and define functions on the subtype. The new fields and functions are specific to the subtype alone.
You cannot substitute a row type in an inheritance hierarchy for its supertype or its subtype. For example, suppose you define a type hierarchy in which person_t is the supertype and employee_t is the subtype. If a column is of type person_t, the column can only contain person_t data. It cannot contain employee_t data. Likewise, if a column is of type employee_t, the column can only contain employee_t data. It cannot contain person_t data.
Creating a Subtype
In most cases, you add new fields when you create a named row type as a subtype of a another named row type (supertype). To create the fields of a named row type, you use the field definition clause that is shown on page 1-203.
When you create a subtype, you must use the UNDER keyword to associate the supertype with the named row type that you want to create. The following statement creates the employee_t type under the person_t type:
The employee_t type inherits all the fields of person_t and has two additional fields: salary and bonus. However, the person_t type is not altered.
Type Hierarchies
When you create a subtype, you create a type hierarchy. In a type hierarchy, each subtype that you create inherits its properties from a single supertype. If you create a named row type customer_t under person_t, customer_t inherits all the fields and functions of person_t. If you create another named row type, salesrep_t under customer_t, salesrep_t inherits all the fields and functions of customer_t. More specifically, salesrep_t inherits all the fields and functions that customer_t inherited from person_t as well as all the fields and functions defined specifically for customer_t. For a full discussion of type inheritance, refer to Chapter 10 of the Informix Guide to SQL: Tutorial.
Procedure for Creating a Subtype
Before you create a named row type as a subtype in an inheritance hierarchy, do the following:
You must have the Resource privilege on the database. You can find this information in the sysusers system catalog table.
You can find this information in the sysxtdtypes system catalog table.
You must have the Under privilege on the supertype.You can find this information in the sysusers system catalog table.
To verify whether the name you want to assign to a new data type is unique within the schema, check the sysxtdtypes system catalog table. The name you want to use must not be the name of an existing data type.
Constraints on Named Row Types
You cannot apply constraints to named row types directly. Specify the constraints for the tables that use named row types when you create or alter the table.
Extended Field Definition
Use the extended field definition to define new fields in a named row type.
Each field has its own field definition, as described in the "Field Definition" section.
.
Field Definition
To define a field, you must specify a name and a data type for each field.
References
See the DROP ROW TYPE, CREATE TABLE, CREATE CAST, GRANT, and REVOKE statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of named row types in Chapter 10, "Understanding Complex Data Types." In the Informix Guide to SQL: Syntax, see Chapter 2, "Data Types."
CREATE SCHEMA
Use the CREATE SCHEMA statement to issue a block of CREATE and GRANT statements as a unit. This statement allows you to specify an owner of your choice for all objects that the CREATE SCHEMA statement creates.
Syntax
Usage
You cannot issue the CREATE SCHEMA statement until you create the affected database.
Users with the Resource privilege can create a schema for themselves. In this case, user name must be the name of the person with the Resource privilege who is running the CREATE SCHEMA statement. Anyone with the DBA privilege can also create a schema for someone else. In this case, user name can identify a user other than the person who is running the CREATE SCHEMA statement.
You can put CREATE and GRANT statements in any logical order within the statement, as the following example shows. Statements are considered part of the CREATE SCHEMA statement until a semicolon or an end-of-file symbol is reached.
Creating Objects Within CREATE SCHEMA
All objects that a CREATE SCHEMA statement creates are owned by user name, even if you do not explicitly name each object. If you are the DBA, you can create objects for another user. If you are not the DBA, and you try to create an object for an owner other than yourself, you receive an error message.
Granting Privileges Within CREATE SCHEMA
You can only grant privileges with the CREATE SCHEMA statement; you cannot revoke or drop privileges.
Creating Objects or Granting Privileges Outside CREATE SCHEMA
If you create an object or use the GRANT statement outside a CREATE SCHEMA statement, you receive warnings if you use the -ansi flag or set DBANSIWARN.
References
See the CREATE INDEX, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, and GRANT statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of creating the database in Chapter 9.
CREATE SYNONYM
Use the CREATE SYNONYM statement to provide an alternative name, called a synonym, for a table or view.
Syntax
Usage
Users have the same privileges for a synonym that they have for the table to which the synonym applies.
The synonym name must be unique; that is, the synonym name cannot be the same as another database object, such as a table, view, or temporary table.
Once a synonym is created, it persists until the owner executes the DROP SYNONYM statement. This property distinguishes a synonym from an alias that you can use in the FROM clause of a SELECT statement. The alias persists for the existence of the SELECT statement. If a synonym refers to a table or view in the same database, the synonym is automatically dropped if you drop the referenced table or view.
You cannot create a synonym for a synonym in the same database.
The owner of the synonym (owner.synonym) qualifies the name of a synonym. The identifier owner.synonym must be unique among all the synonyms, tables, temporary tables, and views in the database. You must specify owner when you refer to a synonym that another user owns. The following example shows this convention:
You can create a synonym for any table or view in any database on your database server. Use the owner. convention if the table is part of an ANSI-compliant database. The following example shows a synonym for a table outside the current database. It assumes that you are working on the same database server that contains the payables database.
You can create a synonym for any table or view that exists on any networked database server as well as on the database server that contains your current database. The database server that holds the table must be on-line when you create the synonym. In a network, INFORMIX-Universal Server verifies that the object of the synonym exists when you create the synonym.
The following example shows how to create a synonym for an object that is not in the current database:
The identifier mysum now refers to the table jean.summary, which is in the payables database on the phoenix database server. Note that if the summary table is dropped from the payables database, the mysum synonym is left intact. Subsequent attempts to use mysum return the error Table not found.
PUBLIC and PRIVATE Synonyms
If you use the PUBLIC keyword (or no keyword at all), anyone who has access to the database can use your synonym. If a synonym is public, a user does not need to know the name of the owner of the synonym. Any synonym in a database that is not ANSI compliant and was created before Version 5.0 of the database server is a public synonym.
Synonyms are always private. If you use the PUBLIC or PRIVATE keywords, you receive a syntax error.
If you use the PRIVATE keyword, the synonym can be used only by the owner of the synonym or if the owner's name is specified explicitly with the synonym. More than one private synonym with the same name can exist in the same database. However, a different user must own each synonym with that name.
You can own only one synonym with a given name; you cannot create both private and public synonyms with the same name. For example, the following code generates an error:
Synonyms with the Same Name
If you own a private synonym, and a public synonym exists with the same name, when you use the synonym by its unqualified name, the private synonym is used.
If you use DROP SYNONYM with a synonym, and multiple synonyms exist with the same name, the private synonym is dropped. If you issue the DROP SYNONYM statement again, the public synonym is dropped.
Chaining Synonyms
If you create a synonym for a table that is not in the current database, and this table is dropped, the synonym stays in place. You can create a new synonym for the dropped table, with the name of the dropped table as the synonym name, which points to another external or remote table. In this way, you can move a table to a new location and chain synonyms together so that the original synonyms remain valid. (You can chain as many as 16 synonyms in this manner.)
The following steps chain two synonyms together for the customer table, which will ultimately reside on the zoo database server (the CREATE TABLE statements are not complete):
1. In the stores7 database on the database server that is called training, issue the following statement:
2. On the database server called accntg, issue the following statement:
3. On the database server called zoo, issue the following statement:
4. On the database server called training, issue the following statement:
The synonym cust on the accntg database server now points to the customer table on the zoo database server.
The following steps show an example of chaining two synonyms together and changing the table to which a synonym points:
1. On the database server called training, issue the following statement:
2. On the database server called accntg, issue the following statement:
3. On the database server called training, issue the following statement:
The synonym cust on the accntg database server now points to a new version of the customer table on the training database server.
References
See the DROP SYNONYM statement in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of synonyms in Chapter 5.
|
