Embedding SQL in Program Code

A MySQL reference for this information.

As we have seen SQL is a table oriented system
And most programming languages are not.
So you need to step across a table.
But we saw how to deal with this in C/C++
And it would be even easier in a language like Python.

SQL/Persistent Stored Modules (SQL/PSM)

Each DBMS has it's own extensions to the SQL standard
This appears to be one of those places
The standard refers to Persistent Stored Modules (PSM).
PSM provides
- Provides storage for a table and the way to step through one.
- Flow of control
  - Begin/end blocks
  - If/Then
  - Loops
- Variables
A PSM procedure is stored as part of the database.
- This is true for veiws as well.
- They are fast (because they can take advantage of DBMS caching, no network involvement, optimization for environment, ...)
- The logic is in the DB, not the application
- They should be more portable than the application logic.
- They should (perhaps) be able to migrate between DBMS
General format in MYSQL
- A name,
- A parameter list (but this can be empty)
- SQL statements
  - Local variable declaration
  - Code
  - Return

A quick tour of procedures in MYSQL

Changing the delimiter
- Since procedures need to store SQL statements.
- And these statements need to end with a ;
- So we need to change the delimiter for the command line.
- DELIMITER //
- It can be anything but the backslash (\), which is used for escaping things.
CREATE PROCEDURE name (parameter list) body;
- Man page
- ```
CREATE PROCEDURE foo()
	SELECT  * from BUYER;
       
```
- Procedure names are not case sensitive. (Foo, FoO, fOo, foo, ...)
- Overloading is not allowed in mysql.
- Probably best not to use existing names.
- Procedures are in the scope of a database.
Other Procedure Quick-Start functions
- CALL PROCEDURE proc_name
- SHOW PROCEDURE STATUS
- DROP PROCEDURE proc_name
- SHOW CREATE PROCEDURE proc_name
- SHOW PROCEDURE CODE proc_name but this only works if db is build in debug mode, which our's is not.
- SELECT * FROM mysql.proc WHERE name='proc-name'
- SELECT BODY FROM mysql.proc WHERE name='proc-name'
User Defined Variables
- Session specific, only last for a given sessions, can not be shared across sessions
- @varname, alpha,digit, _, and other things using quoted strings.
- Not case sensitive
- set var=value
- SET @x=4;
- SELECT @x;
- Simple Math: SELECT @x*@x;
- They are typeless, but you can cast them.
- SET @name="Dale Rogers";
- Then use them in sql statements:
- SELECT * from WAREHOUSE WHERE MANAGER = @name;

parameters

([in]|out|inout) name data-type
create procedure bar(in param1 int) set @x=param1;

CREATE PROCEDURE foobar(IN param1 INT, OUT param2 INT) 
BEGIN
   SET param2 = param1;
END

Compound statements
- BEGIN ... END : marks a code block.
- DECLARE var_name [, var_name] TYPE [DEFAULT value]
  - Only valid within BEGIN .. END block.
  - Lifetime and scope seem to be a single call.
- Flow of Control Statements
- IF
  - IF condition THEN statement list
  - ELSEIF condition THEN statement list
  - ELSE statement list
  - END IF
- WHILE condition DO statements END WHILE

An example;

CREATE TABLE t (a INT);

SELECT * FROM t;

CREATE PROCEDURE populate(myLimit INT)
    BEGIN
      DECLARE i INT;
      DECLARE j INT;
      SET i = 0;
      WHILE i < myLimit DO
         IF MOD(i,2) = 0 THEN
	    set j = i;
	 ELSE
            set j = i*10;
	 END IF;
         INSERT INTO  t VALUE (j);
	 SET i = i + 1;
      END WHILE;
    END;

SELECT * FROM t;