Creating and Editing SQL Procedures
Go Up to Developing the Physical Model
SQL procedures are reusable blocks of PL/SQL that you create, which are stored in the database. Applications can call procedures to streamline code development, debugging, and maintenance. Procedures can also enhance database security by letting you write procedures granting users execution privileges to tables rather than letting them access tables directly.
Stored procedures can contain program flow, logic, and database queries. They can accept and generate parameters, return single or multiple result sets, and return values.
In addition to doing anything that can be accomplished with SQL statements, stored procedures can also:
- Execute a series of SQL statements in a single stored procedure.
- Reference other stored procedures, which can simplify a series of complex statements.
- Execute faster than individual SQL statements because the stored procedure is compiled on the server when it is created.
- Be cached in memory, for faster execution.
There are several ways that you can implement SQL procedures:
- SQL that runs before or after creating an object. For more information, see Creating and Editing PreSQL and PostSQL for the CREATE TABLE Statement
- Scripted and templated procedures that can store application code into the database for all applications to used via the CALL statement. For more information, see Creating and Editing Procedures.
- Scripted and templated triggers that enforce referential integrity rules; enforce business rules on table data; synchronize information in another table when a table is updated; initiate an action external to the DBMS when an update occurs; and prevent some types of operations, such as UPDATEs. For more information, see Creating and Editing Triggers.
- Functions that can execute multiple SQL statements from within an SQL statement. For more information, see Creating and Editing Functions.
- Packages that can store together all the code necessary to process SQL statements from a single source file. You can use packages to process and call batches of SQL. For more information, see Creating and Editing Packages.
This section also covers the following:
- Creating and Editing PreSQL and PostSQL for the CREATE TABLE Statement
- Creating and Editing Procedures
- Create a Scripted Procedure
- Apply a Scripted Procedure to a Submodel or Model
- Create Reusable Procedure
- Create a Scripted User-Defined Procedure
- Create a Templated User-Defined Procedure
- Bind a Reusable Procedure to a Table
- Create an SQL Procedure
- Import an SQL Procedure
- Export an SQL Procedure