Database Systems - Procedural SQL PL/SQL - CREATE PROCEDURE Statement - Cursors and Triggers
Procedural SQL (PL/SQL) is a combination of SQL along with the procedural features of programming languages. It was developed in the 90s by Oracle to enhance the capabilities of SQL.
SQL/Persistent Stored Modules (SQL/PSM) is a standard for procedural SQL that extends the core SQL standard. SQL/PSM is implemented in many relational databases with significant variations and different names. Many names incorporate the acronym 'PL', which stands for 'Procedural Language'.
A stored procedure is a prepared SQL code that you can store in the database, so the code can be reused or executed over and over again. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed. You can call a stored procedures from a host program written in another language, or the command line.
SQL/PSM (SQL/Persistent Stored Modules) is an ISO standard that defines an extension of SQL with a procedural language for use in stored procedures
CREATE PROCEDURE – creates a stored procedure.
The body of the procedure typically contains SQL statements like select and delete, or compound statements.
To execute the code of a stored procedure, you CALL it by specifying its name and arguments. Arguments are the actual data passed into the parameters of a stored procedure. Arguments can be literals, which are actual data values, or you pass a user-defined-variables as an argument which must begin with the @ character.
You can call a stored procedure from a programming language like Java or Python, the command line, or from another stored procedure.
Compound statements
In a stored procedure body, you will add a compound statement, where you can have a series of statements that start with BEGIN and end with END keywords. The statements are common programming concepts like variables and assignment, branching or looping structures.
Cursors
To handle a result set inside a stored procedure, you use a cursor, which allows you to iterate a set of rows returned by a query’s result set and process each row individually.
DECLARE CursorName CURSOR FOR Statement - creates a cursor named CursorName for the query Statement..
DECLARE CONTINUE HANDLER FOR NOT FOUND Statement - specifies a Statement to executes when the cursor has passed up the last row and there are no more rows to read.
OPEN CursorName executes the query associated with CursorName and moves the cursor before the first row of the result table.
FETCH FROM CursorName INTO variable [, variable, ... ] - moves CursorName to the next row of the result table and copies selected values into the variables.
CLOSE CursorName - releases the result table associated with the cursor.
Stored functions
A stored function is a special kind of function, similar to a stored procedure, but returns a single value. Stored procedures can return none, one or more values. Typically, you use stored functions to encapsulate common formulas or business rules that are reusable among SQL statements or stored programs. They are pretty similar to SQL aggregate functions like SUM(), COUNT(), and AVG(), but are created by the programmer.. Use the CREATE FUNCTION statement to create a stored function
Note that in a stored function, parameters are for input only and don’t use the IN keywords like a stored procedure. They are also called by SQL, and not by the CALL function. After the RETURNS keyword, you must have one of the listed keywords that describe the function :
NO SQL - no SQL statements in the function
READS SQL DATA - contains SQL statements that can only read table data
DETERMINISTIC - contains SQL statements that can write table data.
Trigger
trigger is a named database object that encapsulates and defines a set of actions that are to be performed in response to an insert, update, or delete operation against a table. It has no parameters and no return values, and cannot be called directly with a function call or CALL. Create a trigger with CREATE TRIGGER:
CREATE TRIGGER TriggerName
[ BEFORE | AFTER ] [ INSERT | UPDATE | DELETE ]
ON TableName
FOR EACH ROW
Body;
The CREATE TRIGGER statement specifies the TriggerName and one of these keywords after it:
ON TableName - specifies which table to activate the trigger on.
INSERT, UPDATE, or DELETE specifies the SQL operation for when to execute the trigger
BEFORE or AFTER time when to execute the trigger as before or after SQL operation.
FOR EACH ROW specifies the trigger repeats once for each row affected by the SQL operations.
Similarly to stored procedures, the trigger Body can be a simple or compound statement. Within the body, use keyword OLD to prefix the table values prior to an update or delete operation, and use NEW to prefix table values after an insert or update operation.
Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!