Procedures in CockroachDB
In this lesson, we learn to create Procedures in CockroachDB, including an explanation of input and output parameters. We’ll include:
- What? What do Cockroach database’s procedures do and what is the syntax?
- Why? Why use the procedure construct, otherwise known as Store Procedures in some other relational database systems?
- How? How and why do we use Cockroach Procedures, where do we use them in our SQL scripts, and how do we refer to them from within Python scripts?
What is a Cockroach Procedure?
Some features of Procedures:
- We can also refer to Procedures as “named programs” that are stored in the database, as opposed to being stored in our application’s code. In some ways, this can increase portability and modularity.
- Compiled object.
- Error checked.
- Procedures may or may not use argument values (parameters).
- Default for Cockroach Procedures is to return no values, differing here from Functions. Most likely, you will be giving your Procedure a value or values, but expecting no return value, unless there is an error.
- Execution can be explicit or implicit: Explicit execution. EXECUTE, along with specific SP name and optional parameters; and Implicit execution using only the name of the Procedure.
- Can not be run or called from within a SELECT SQL statement.
- You can call a Cockroach Procedure as often as you like.
Why Cockroach Procedures?
- Modularity. When you are writing duplicate-ish queries more than once within your code, if you want more efficiency, you can write just one stored procedure that – possibly with a parameter or more – can be run as many times as you like from your code, with far less coding, potential typos, and future maintenance. Not much different than the benefits of using functions.
- Performance. A Procedure’s code is compiled when created, meaning no need to compile at run-time, unless you change the program (Stored Procedure). This means much faster execution than interpreted SQL!
- Security. Procedures reduce potential for hacking, including one of the most popular, SQL injection.
Creating Cockroach Procedures
For much of this document, we will look at how to create a Cockroach Procedure specifically using CockroachDB’s structured query language (SQL). Let us start with the syntax.
Cockroach Procedure Syntax
CREATE OR REPLACE PROCEDURE [PROCEDURE name]([optional:params GO here])
[execution; your "program" code]
[optional: IF an error; error handling]
Cockroach Procedure Parameters
In terms of input and output to and from our Cockroach Procedure, there are 3 types of parameters:
- IN (only input).
- IN OUT (in/out mode – input and output).
- OUT (only output).
Cockroach Procedure Use
Let us start with a simple table called “tblTechnology” in our CockroachDB database system. We will use this table to track various technologies used at our firm.
Notice the following: We are assuming the idTech column is an auto-incrementing index.
CREATE OR REPLACE PROCEDURE ADD_A_USER
P_txtName IN tblTechnology.txtName%TYPE,
P_txtCategory IN tblTechnology.txtCategory%TYPE,
P_txtMsgError OUT VARCHAR2
-- No variables to declare in this procedure.
) VALUES (
WHEN OTHERS THEN
P_txtMsgError := SQLERRM;
Let’s do an in-depth examination of the above SQL:
- CREATE: CREATE is used to add objects (like tables and functions) to the database. Here, we recommend getting into the practice of adding “OR REPLACE” to account for overwrites. In other words, odds are high you will want to make changes to this Cockroach Procedure from time to time.
- ADD_A_USER: The naming of our Cockroach Procedure. This is important because we need a name to use for our Python (or whatever language) code to execute the Cockroach Procedure. Here we named the Cockroach Procedure “ADD_A_USER”.
- PARAMETERS: In parentheses immediately following the name of our Cockroach Procedure are the parameters we chose to use with our Cockroach Procedure, if any. Notice how we named all parameter variables with a “P_” appended? This is A MUST. Notice how 2 of our Procedure’s parameters are “IN” and one is “OUT”? The “IN” parameters are what are sent to the SP from our application and the “OUT” parameter is what our Cockroach Procedure sends back to our program, if anything. We’ll give an example of this later.
- %TYPE: If we don’t know the parameter variable type, we can use a placeholder called %TYPE to declare the type of the parameter to be dynamic in nature and thus, more flexible. This approach will reduce security but increase flexibility.
- IS: This can also be “AS”. No difference. They gave us this option for easier compatibility and ease of knowledge if we are coming from other databases like SQL Server, MySQL, etc., that use “AS”.
- DECLARE: The DECLARE here has no arguments in its block, which is because this is an optional area and for our purposes with this little Cockroach Procedure, we don’t need to declare any variables.
- BEGIN: This is where we tell Cockroach the starting of what we want executed on CockroachDB, all the way up to the END statement at the bottom.
- INSERT INTO: Here we are instructing the database engine, “We’re about to give you values to put into the following two columns. Following is the order they go in.
- VALUES: This is to instruct CockroachDB what values to place in those columns we defined above. Notice the “P” appending our two parameter values? Scroll up some in the Cockroach Procedure code to be reminded how, in the PARAMETERS area we named each parameter with a “P” before it.
- COMMIT: Instruct Cockroach to actually follow the commands we gave it above this place in the SQL.
- EXCEPTION: In this script block, we tell Cockroach what to do if an error is detected.
- WHEN OTHERS THEN: Here is the only part of this Cockroach Procedure that actually sends data back out to our calling code.
- END: Marks the end of the BEGIN block and end of execution.
Calling a Cockroach Procedure
Because Python works so well with Cockroach, we will use this language for the example below.
from flask import Flask # popular python library
import psycopg2 # for database connection and dependent on Flask
app = Flask(__name__)
crdbConn = psycopg2.connect(t_dsn)
crdbCursor = crdbConn.cursor()
txtResults = crdbCursor.fetchone()
print("Results: " + txtResults)
Here – if we assume the connection and cursor objects are not something we need to recreate and destroy over and over – we have three lines of code taking the place of Python, .Net, Java, etc. code that builds the following SQL statement:
) VALUES (
Please notice that the SQL statement above does not include the TRY…COMMIT…EXCEPT block of error-checking code that may look like this:
except psycopg2.Error as txtErrorMsg:
txtMessage = "Database error: " + txtErrorMsg + "/n SQL: " + s
Next, consider writing those INSERT INTO blocks of SQL over and over throughout your application but only changing what you send into those two parameters? Do you see how much time and potential for errors you can save by using Cockroach’s Procedure structure instead?
- The Procedure’s declarations can be empty.
- Procedures without parameters are called “static procedures”.
- CockroachDB Procedures that use parameters are called “dynamic”.
Here we learned why and how to create a Procedure in Cockroach to give us more control, efficiency, speed, modularity, ease, and security. We also looked at how to call a Procedure from Python.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started