Database Tutorial

SQL> CREATE USER student IDENTIFIED BY student;
SQL> GRANT CONNECT, resource, CREATE any VIEW TO student;

-- Conditionally drop the objects to make this rerunnable.
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name IN ('A','B','TRANSACTION_ERROR_LOG')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('A_SEQ','B_SEQ','TEL_SEQ')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/  
 
-- Create the tables and sequences.
CREATE TABLE a
( a_id   NUMBER CONSTRAINT a_pk PRIMARY KEY
, a_text VARCHAR2(12));
 
CREATE SEQUENCE a_seq;
 
CREATE TABLE b
( b_id   NUMBER CONSTRAINT b_pk PRIMARY KEY
, a_id   NUMBER 
, b_text CHAR(12) CONSTRAINT u_b UNIQUE
, CONSTRAINT fk_a FOREIGN KEY(a_id) REFERENCES a (a_id));
 
CREATE SEQUENCE b_seq;
 
-- Create the error handling table.
CREATE TABLE transaction_error_log
( error_log_id   NUMBER  CONSTRAINT tel_pk PRIMARY KEY
, table_name     VARCHAR2(12)
, table_id       NUMBER
, table_text     VARCHAR2(12));
 
CREATE SEQUENCE tel_seq;
 
-- Create a stored procedure.
CREATE OR REPLACE PROCEDURE double_insert
( input_a VARCHAR2, input_b VARCHAR2) IS
BEGIN
  /* Set savepoint. */
  SAVEPOINT starting_point;
 
  /* Insert a row into two tables. */
  INSERT INTO a VALUES (a_seq.NEXTVAL, input_a);
  INSERT INTO b VALUES (b_seq.NEXTVAL, a_seq.CURRVAL, input_b);
 
  /* This commits the writes to table A and B when there's no error. */
  COMMIT;
 
EXCEPTION
  /* This acts as an exception handling block. */
  WHEN OTHERS THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO starting_point;
 
    /* While in the same transaction context, this occurs after prior work is undone. */
    INSERT INTO transaction_error_log VALUES
    ( tel_seq.NEXTVAL, 'B', b_seq.CURRVAL, input_b);
 
    /* This commits the write to error handling table. */
    COMMIT;
 
    /* This returns control back to the execution block, and suppresses an error back to the user. */
    RETURN;
END;
/
 
-- Define a couple local session variables.
VARIABLE text1 VARCHAR2(12)
VARIABLE text2 VARCHAR2(12)
 
-- Assign values to session variables.
BEGIN
  :text1 := 'This is one.';
  :text2 := 'This is two.';
END;
/
 
-- Call the local procedure.
EXECUTE double_insert(:text1,:text2);
EXECUTE double_insert(:text1,:text2);
EXECUTE double_insert(:text1,:text2);
EXECUTE double_insert(:text1,'This is new');
 
-- Select the data set. 
SELECT * FROM a;
SELECT * FROM b;
SELECT * FROM transaction_error_log;

SQL> CONNECT student/student@orcl

SQL> @create_oracle_procedure.sql

      A_ID A_TEXT
---------- ------------
         1 This IS one.
         4 This IS one.
 
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.
         4          4 This IS NEW
 
 
ERROR_LOG_ID TABLE_NAME     TABLE_ID TABLE_TEXT
------------ ------------ ---------- ------------
           1 B                     2 This IS two.
           2 B                     3 This IS two.

CREATE DATABASE storeddb;

mysql> CREATE USER 'student' IDENTIFIED BY 'student';
mysql> GRANT ALL ON storeddb.* TO 'student'@'localhost' IDENTIFIED BY 'student';

-- Select the database.
USE storeddb;
 
-- Conditionally drop the objects to make this rerunnable.
DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
DROP TABLE IF EXISTS transaction_error_log;
DROP PROCEDURE IF EXISTS double_insert;
 
-- Create the tables.
CREATE TABLE a
( a_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, a_text CHAR(12));
 
CREATE TABLE b
( b_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, a_id   INT UNSIGNED 
, b_text CHAR(12) UNIQUE KEY);
 
-- Create the tables.
CREATE TABLE transaction_error_log
( error_log_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, TABLE_NAME     CHAR(12)
, table_id       INT UNSIGNED
, table_text     CHAR(12));
 
-- Reset the execution delimiter to create a stored program.
-- ----------------------------------------------------------------------
--  Somebody told me that this line seemed unnecessary to a few folks following 
--  the example. They skipped it, and encounter a host of meaningless errors.
-- 
--  Those errors are triggered because the semicolon, \g, or \G are execute
--  instructions to the MySQL engine. The semicolon inside a stored procedure 
--  or function serves a different purpose. Inside the stored program unit the 
--  semicolon is a statement and block terminator. You must change the MySQL
--  DELIMITER to something other than a semicolon before you try to create a
--  stored programming unit. You must then change it back if you plan to use the
--  semicolon in the rest of your program. Also, you MUST have a white space 
--  between the DELIMITER keyword and the symbol (either a $$ or ; in the sample
--  programs that are provided). 
-- ----------------------------------------------------------------------
DELIMITER $$
 
-- Create a stored procedure, the MODIFIES SQL DATA phrase is optional in MySQL 5.1+.
CREATE PROCEDURE double_insert
( input_a CHAR(12), input_b CHAR(12)) MODIFIES SQL DATA
BEGIN
  /* Declare a locally scoped variable. */
  DECLARE duplicate_key INT DEFAULT 0;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT both_or_none;
 
  /* Insert a row into two tables. */
  INSERT INTO a VALUES (NULL, input_a);
  INSERT INTO b VALUES (NULL, last_insert_id(), input_b);
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT both_or_none;
 
    /* While in the same transaction context, this occurs after prior work is undone. */
    INSERT INTO transaction_error_log VALUES (NULL, 'B', last_insert_id(), input_b);
 
  END IF;
 
  /* This commits the writes to table A and B when the duplicate key constraint isn't violated. */
  COMMIT;
 
END;
$$
 
-- Reset the delimiter to the default.
-- ----------------------------------------------------------------------
--  Check the earlier note because this is a critical line in the sample.
-- ----------------------------------------------------------------------
DELIMITER ;
 
-- Declare a couple local session variables.
SET @text1 = 'This is one.';
SET @text2 = 'This is two.';
 
-- Call the local procedure.
CALL double_insert(@text1,@text2);
CALL double_insert(@text1,@text2);
CALL double_insert(@text1,@text2);
CALL double_insert(@text1,'New Text');
 
-- Query table contents. 
SELECT * FROM a;
SELECT * FROM b;
SELECT * FROM transaction_error_log;

mysql> QUIT;

C:\> mysql -ustudent -pstudent

mysql> SOURCE create_mysql_procedure.sql

mysql> \. create_mysql_procedure.sql

+------+--------------+
| a_id | a_text       |
+------+--------------+
|    1 | This IS one. |
|    4 | This IS one. |
+------+--------------+
2 ROWS IN SET (0.00 sec)
 
+------+------+--------------+
| b_id | a_id | b_text       |
+------+------+--------------+
|    1 |    1 | This IS two. |
|    4 |    4 | NEW Text     |
+------+------+--------------+
2 ROWS IN SET (0.00 sec)
 
+--------------+------------+----------+--------------+
| error_log_id | TABLE_NAME | table_id | table_text   |
+--------------+------------+----------+--------------+
|            1 | B          |        2 | This IS two. |
|            2 | B          |        3 | This IS two. |
+--------------+------------+----------+--------------+
2 ROWS IN SET (0.00 sec)