NuoDB Docs
NuoDB
NuoDB

NuoDB Docs

      • NuoDB At a Glance
      • Samples Directory
      • Docker
      • Docker Compose
      • Kubernetes
        • RPM Package Install
        • TAR File Install
      • New Features
      • Changes
      • Resolved Issues
      • Preview Features
      • Known Issues
      • Deprecated Features
      • End of Life Features
      • Legal Notices
        • Admin Platform
        • Networking
        • Atoms
        • Data Access and Replication
        • Archive Synchronization
        • Data Partitioning
        • Archive
        • Indexing
      • Data Caching
        • Visibility
        • Commit Protocol
        • Vectorized Execution Engine
        • High-level SQL Pipeline
        • Data-To-Code Execution
        • Transactional DDL
        • Parallel Query Execution
          • Index Statistics (Stats v2)
        • System Requirements
          • Obtain and Install a Product License
            • RPM File Installation
            • TAR File Installation
            • Resolving Warnings About Previously Used NuoDB IDs
            • Post-Installation Tasks
            • EXE File Installation
            • ZIP File Installation
            • Prepare to Upgrade
              • Upgrade with Databases Down
              • Rolling Upgrade
            • Upgrade the Database Protocol
          • Downgrade to a Previous Release
            • Linux
            • Windows
          • Configuring NuoDB Admin
            • Generating Keystores and Truststores: Shared Admin Key
            • Generating Keystores and Truststores: Unique Admin Key
            • Using Certificates Signed by a Public Certificate Authority
            • Rotating Key Pair Certificates
            • Managing TLS Security
          • Creating the Domain and Adding Hosts
          • Starting and Stopping NuoDB Admin
            • Displaying Domain, Database, and Archive Status
            • Displaying Durable Domain Configuration Information
            • Displaying Database Process Information
            • Displaying Database Engine Attributes
          • Performing Health Checks on Domain and Database Status
          • Resetting the NuoDB Domain State
            • Determining if Admin Process (AP) Quorum Exists
            • Handling Unreachable Processes
            • Removing an Unreachable Admin Server from the Durable Domain
            • Re-establishing Admin Process (AP) Quorum
            • Creating an Archive
            • Creating a Database
            • Validating Database Archives
          • Confirming Domain and Database Status
          • Shutting down a Database
          • Restarting a Database
          • Starting a Storage Manager (SM)
          • Starting a Transaction Engine (TE)
          • Provision an Archive
          • Deprovision an Archive
          • Migrate to LSA
          • Extending the Database Across Multiple Hosts (Scaling Out)
          • Adding and Shutting Down a Redundant Storage Manager (SM)
          • Restoring a Database Using an Archive
          • Deprovisioning Hosts in a Multi-Host Configuration (Scaling In)
            • General Steps for Backing Up Databases
                • Copying the Archive and Journal Directory of the Shutdown Storage Manager (SM)
                • Using Hot Copy Successfully
                • Simple Hot Copy
                • Backup Sets
                • Full Hot Copy
                • Incremental Hot Copy
                • Journal Hot Copy
                • Hot Copy Command Formats
                  • Example of Hot Copy Running
                  • Example of Hot Copy Log Message
                  • Examples of Hotcopy Errors
                  • Example of Hotcopy Success
                  • Restore from a Single Backup Set - Examples
                  • Restore Data from Multiple Backup Sets - Examples
              • Hot Snap Backup
          • Deleting Databases
          • Database Management - A Working Example
          • Scripted Database Management Using pynuoadmin
        • System Requirements
        • Installing NuoDB
        • Install NuoDB License on Kubernetes
        • Enabling TLS in Containerized Environments
        • Automatic Management of NuoDB State
        • Scaling Down the Admin Layer without Kubernetes-Aware Admin
      • Durable Domain Configuration
      • Domain Credentials
      • Admin Process (AP)
      • Admin Process (AP) Quorum
      • Maintaining Admin Process (AP) Quorum
      • User Authentication and Authorization
        • Configure Transparent Data Encryption
        • Transparent Data Encryption Error Codes
        • Privileges of the Database Administrator
        • Connect to a database Using TLS and LDAP
        • User Authentication Using LDAP
        • Users and Roles
        • Schema Access Levels
        • Assign Database Access Levels
        • Examples of Assigning Database Access Levels
        • Manage Database Schemas
        • Manage Database Tables
        • Manage Other Database Objects
      • Database Journaling
        • Use Cases
        • Storage Group Rules
        • Table Partition Rules
          • Rules for Adding or Removing Storage Groups
          • Add a Storage Group
          • Remove a Storage Group
          • Delete a Storage Group
        • Manage Table Partitions
        • Example
      • Restoring a Database Using an Archive
        • Terminology
        • Pros and Cons
        • Setup
        • Normal Operation
        • Disaster Recovery
        • Disaster Recovery Using Handoff - Example
      • Reducing Memory Pressure on Transaction Engines (TEs)
      • Setting Client Connection Limits for Transaction Engines (TEs)
      • Installing pynuoadmin
      • Validating Database Archives
        • Communication Graphs
        • CPU Graphs
        • Disk Graphs
        • Memory Graphs
        • NuoDB Ops System Overview Graphs
        • NuoDB Overview Graphs
        • SQL Graphs
        • Case Studies
        • NuoDB Metrics Available from the Collector
        • Kubernetes Metrics
        • Use Kubernetes Dashboards
      • Useful SQL Metrics Related to Database Operation
      • Useful System Tables Related to Database Operation
      • Client Package
      • Load Balancer Policies
          • Database Operations
          • Enabling TLS
          • Notes for Using C++ Driver
          • Examples
          • API Reference
          • Release Notes
          • Database Operations
          • Enabling TLS
            • Specifying Properties on the Connection URL
            • Configuring a NuoDB DataSource
            • Third Party DataSources
            • Using try-with-resources
            • JDBC Transaction Isolation Levels Supported by NuoDB
            • Canceling a Statement
            • Working with Timeouts
            • Logging
            • Exception Handling
            • ResultSet Shelf Life
            • Setting Result Set Holdability
            • LOB Streaming
            • JDBC and XA Transactions Using NuoDB
            • Direct Transaction Engine Connection
            • Using Timestamps and Time Zones
          • Examples
          • API Reference
          • Hibernate Release Notes
          • Hibernate Dialect Considerations
          • Auto Generating Keys
          • Connect to a Database
          • Using JNDI
          • Example
        • NuoDB Flyway Driver
          • Use ADO.NET Driver Objects Directly
          • Database Operations
          • Notes
          • Enabling TLS
          • Database Operations
          • Notes
          • Example
          • API Reference
          • Release Notes
            • Connect Using a Single Connection
            • Connect Using a Connection Pool
          • Queries
          • Transactions
          • Data Types
          • Best Practices
          • pynuodb Release Notes
        • Sample NuoDB Programs on GitHub
      • Rules for Specifying NuoDB Identifier Names
          • Changing Default Transaction Behavior
          • Overriding Default Transaction Behavior
        • About Lock Behavior
          • About Implicit Transactions
          • About Explicit Transactions
        • Transaction Behavior in SQL Stored Procedures
        • Explicit Transactions and Rollback Modes
          • Description of NuoDB Transaction Isolation Levels
          • Examples of Reading Data After a Concurrent Transaction Updates Data
          • Examples of Selecting Data for Update After a Concurrent Transaction Updates Data
          • Examples of Updating a Row That is Being Updated by a Concurrent Transaction
          • Examples of Concurrent Updates That Do Not Overlap
          • JDBC Transaction Isolation Levels Supported by NuoDB
          • Behavior If Connection to a Database is Lost
          • Behavior If NuoDB Throws an Exception
        • INNER Join
        • LEFT OUTER Join
        • RIGHT OUTER Join
        • FULL OUTER Join
        • Cross Join Operations
        • Multiple Join Operations
        • Using Conditions to Control the Flow of Execution
        • Using Loops to Control the Flow of Execution
          • Variables
          • SQL Procedural Conditional Statements
          • Control Flow
          • Dynamic SQL
          • Comments
        • Creating Database Objects in Stored Procedures
          • DECLARE Cursor
          • OPEN Cursor
          • FETCH Cursor
          • FETCH_STATUS
          • Update and Delete Using WHERE CURRENT OF
          • CLOSE Cursor
          • DEALLOCATE Cursor
          • Examples Using Cursors
        • Using a Result Set as Input to a Stored Procedure
          • Returning a Result Set from a Stored Procedure
          • Returning Multiple Result Sets from a Stored Procedure
          • Using Embedded Java Stored Procedures
          • Using Embedded Java Triggers
          • Using Embedded Java User Defined Functions
          • Unsupported Methods in Embedded Java API
        • About the SELECT List of an Updatable View
        • About the FROM Clause of an Updatable View
        • About the WHERE Clause of an Updatable View
        • Examples of Creating and Updating Views
      • Investigating Hung Queries
      • Understanding EXPLAIN Command Output
        • Using SQL Observability Metrics to Identify Slow Query Performance
        • Identifying Historical Slow Queries Using System Tables
        • Identifying Currently Running Slow Queries Using System Tables
        • Obtaining SQL Elapsed Time Report
        • Running ANALYZE to Obtain Index Statistics
          • Identifying Index Usage (EXPLAIN)
          • Obtaining Index Use Statistics
        • Using Optimizer Hints
      • Internationalization and Localization
      • Tuning Tips for System Configuration
      • Tuning Tips for NuoDB Configuration
      • Tuning Tips for Database Design
      • Tuning Tips for Journal Performance
      • Troubleshooting with NuoDB Command
        • Connection Timed Out with Java IOException
        • No Such Database Name Error
        • No Available Nodes Error
      • Identifying and Killing Long-Running Queries
      • Messages Displayed in Relation to Connections Supported
        • Log Entry Format
        • Dynamic or On-Demand Logging
        • Default Logging
        • NuoDB Admin Logging
        • Logging Options
        • Cannot Start Transaction Engine (TE) Without a Storage Manager (SM)
        • Inconsistent Statistic for Index
        • Insufficient Storage Managers (SMs) to Start Transaction
        • Storage Manager (SM) Startup Failure: Attempt to Initialize Exisiting Archive
        • Transaction Engine (TE) or Storage Manager (SM) Quits Unexpectedly
      • Basic Regular Expression Constructs
        • Host Properties (nuoadmin.conf)
        • Data Type Mappings (nuodb-types.config)
        • Database Configuration for a Particular Host (nuodb.config)
        • JVM Options (jvm-options)
      • Connection Properties
          • nuoarchive check
          • nuoarchive recover
          • nuoarchive restore
          • NuoDB Command Reference
          • Docker-specific NuoDB Command Reference
        • NuoDB Dump
        • NuoDB Loader
          • Downloading NuoDB Migrator Source Code
          • NuoDB Migrator Properties File
          • About NuoDB Migrator Commands
            • NuoDB Migrator Mappings for DEFAULT Values
            • NuoDB Migrator Mappings for MySQL Unsigned Types
            • NuoDB Migrator Mappings for Unsupported Types
            • NuoDB Migrator Mappings for Supported Types
            • Dumping a Source Database to be Migrated
            • Loading Dumped Files into NuoDB Target Database
            • Generating DDL SQL Statement Scripts for Migrated Data
            • Example of Migrating From IBM DB2
            • Example of Migrating From MSSQL
            • Example of Migrating From MySQL
            • Example of Migrating From Oracle
            • Example of Migrating From PostgreSQL
          • Using NuoDB Migrator to Copy NuoDB Databases
            • Overriding NuoDB Migrator Type Mappings
            • Examples of NuoDB Migrator Schema Command
            • Troubleshooting Migration Metadata Error During Schema Generation
          • NuoDB Migrator Dump Command
          • NuoDB Migrator Load Command
            • Configuration File
            • History File
            • Init File
          • Configure NuoDB SQL
          • Batch Process using NuoDB SQL
          • Command-line Editing using NuoDB SQL
          • Display Output Vertically
          • Paginate Output
          • Display Command-line History
          • Examples
      • Database Options
      • Default Ports (NuoDB Admin)
      • Environment Variables
        • NuoDB Admin Log Description
        • NuoDB Admin Event Log
        • NuoDB Admin Audit Log
        • SQL Connections Log
        • Crash Directory Description
        • Engine Stop Log Description
      • Metrics Published by Database Processes
      • REST API Reference (NuoDB Admin)
        • SQL Internationalization and Localization
          • SQL String and Character Types
          • SQL Numeric Types
          • SQL Date and Time Types
          • SQL Boolean Types
          • SQL Binary Types
          • Implicit Conversions for INSERT and UPDATE Statements
        • SQL Error Codes
          • SQL Aggregate Functions
          • SQL Comparison Operators
          • SQL Conditional Expressions
          • SQL Data Type Conversion Functions
          • SQL Date and Time Functions and Operators
          • SQL JSON String Functions
          • SQL Logical Operators
          • SQL Mathematical Functions and Operators
          • SQL Pattern Matching
          • SQL String Functions and Operators
          • SQL System Information Functions
          • SQL Table Functions
          • SQL Window Functions
        • SQL Keywords
        • SQL Standard Compliance
          • ALTER FUNCTION
          • ALTER PROCEDURE
          • ALTER SEQUENCE
          • ALTER STATISTICS
          • ALTER TABLE
          • ALTER TRIGGER
          • ALTER USER
          • ALTER VIEW
          • ANALYZE
          • CALL
          • COMMIT
          • CREATE FUNCTION
          • CREATE INDEX
          • CREATE PROCEDURE
          • CREATE ROLE
          • CREATE SCHEMA
          • CREATE SEQUENCE
          • CREATE STATISTICS
          • CREATE TABLE
          • CREATE TRIGGER
          • CREATE USER
          • CREATE VIEW
          • DELETE
          • DROP FUNCTION
          • DROP INDEX
          • DROP PROCEDURE
          • DROP ROLE
          • DROP SCHEMA
          • DROP SEQUENCE
          • DROP STATISTICS
          • DROP TABLE
          • DROP TRIGGER
          • DROP USER
          • DROP VIEW
          • EXECUTE
          • EXPLAIN
          • GRANT
          • INSERT
          • LOCK
          • RELEASE SAVEPOINT
          • RENAME TABLE
          • REPLACE
          • REVOKE
          • ROLLBACK
          • SAVEPOINT
          • SELECT
          • START TRANSACTION
          • TRUNCATE
          • UPDATE
          • USE
          • WITH
          • ALTER DATABASE
          • HELP
          • KILL CONNECTION
          • KILL STATEMENT
          • SET
          • SHOW
        • SQL System Properties
          • ALLSYSTEMFIELDS
          • ALLSYSTEMTABLES
          • BUILTINFUNCTIONS
          • COLUMNGROUPS
          • COLUMNGROUPFIELDS
          • CONNECTIONS
          • CONNECTIONSTATEMENTS
          • DATATYPES
          • DUAL
          • FIELDS
          • FOREIGNKEYS
          • FORMATS
          • FUNCTIONCOLUMNS
          • FUNCTIONS
          • GLOBALATOMS
          • INDEXES
          • INDEXFIELDS
          • INDEXHISTOGRAMBUCKETS
          • INDEXHISTOGRAMS
          • INDEXPREFIXSTATISTICS
          • INDEXSTATISTICS
          • JAVACLASSES
          • LASTPARALLELSTATEMENT
          • LASTSTATEMENT
          • LOCALATOMS
          • LOCALATOMSEQUENCES
          • LOCALCONNECTIONS
          • LOCALEXECUTIONSTATS
          • LOCALINDEXATOMS
          • LOCALQUERYPERFORMANCEMETRICS
          • LOCALQUERYTIMEOUTS
          • LOCALSTATEMENTS
          • LOCALSTATSUPDATEJOBS
          • LOCALTABLEATOMFIELDS
          • LOCALTABLEATOMS
          • LOCALTRANSACTIONALLOCKS
          • LOCALTRANSACTIONS
          • MEMPROPERTIES
          • MULTIINDEXES
          • NODES
          • NODESTORAGEGROUPS
          • OPENRESULTS
          • PARTITIONCRITERIA
          • PARTITIONEDTABLES
          • PARTITIONIDS
          • PASSWORDS
          • PRIVILEGES
          • PRIVTYPES
          • PROCEDURECOLUMNS
          • PROCEDURES
          • PROPERTIES
          • PSEUDOTABLEFIELDS
          • PSEUDOTABLES
          • QUERYBUFFERSTATS
          • QUERYPERFORMANCEMETRICS
          • QUERYSTATS
          • ROLES
          • SCHEMAS
          • SEQUENCES
          • STATSCACHE
          • STORAGEGROUPS
          • TABLECONSTRAINTS
          • TABLES
          • TEMPFIELDS
          • TEMPINDEXES
          • TEMPINDEXFIELDS
          • TEMPTABLES
          • TRANSACTIONALLOCKS
          • TRANSACTIONS
          • TRIGGERCLASSES
          • TRIGGERS
          • USERROLES
          • USERS
          • VERSIONS
          • VIEW_TABLES
        • Using DbVisualizer
        • Using SQL Workbench/J
        • Using SQuirreL Universal SQL
        • Using MySQL mysqldump Utility
        • Logging
        • Privileges
        • Network Encryption
      • REST API
      • Java DataSource API
      • C Driver API
      • C++ Driver API
    • Glossary
            • SQL Enumerated Types
  • NuoDB Docs
    • 7.0.x
    • 6.0.2
    • 5.1.x
    • 5.0.x
  • SQL Development
  • SQL Procedures, Functions, and Triggers

SQL Procedures, Functions, and Triggers

This sections provides detailed information for SQL stored procedures, user defined functions (UDFs) and triggers. These can be written either in the NuoDB SQL procedural language (see NuoDB SQL Procedural Language) or in Java (see Using the Embedded Java API).

Using Loops to Control the Flow of Execution NuoDB SQL Procedural Language

All content © 2024 Dassault Systemes SE.

Site built with Antora and the Antora default ui.