DROP DATABASE IF EXISTS sc1;
CREATE DATABASE sc1;

USE sc1;

CREATE SEQUENCE seq_only_source; 


DELIMITER $$

--
-- Create package `just_source_pack`
--
SET @saved_sql_mode = @@sql_mode
$$
SET @@sql_mode = 'ORACLE'
$$
CREATE PACKAGE just_source_pack
COMMENT 'just_source_pack comment'
SQL SECURITY DEFINER
AS
  FUNCTION test_function(eid INT) RETURN DECIMAL(10,2)
    COMMENT 'just_source_pack func declaration comment'
    LANGUAGE SQL CONTAINS SQL SQL SECURITY DEFINER;
  PROCEDURE test_proc(idn INT, str varchar(100))
    COMMENT 'just_source_pack proc declaration comment'
    LANGUAGE SQL MODIFIES SQL DATA SQL SECURITY INVOKER;
END
$$
SET @@sql_mode = @saved_sql_mode
$$

--
-- Create package body `just_source_pack`
--
SET @saved_sql_mode = @@sql_mode
$$
SET @@sql_mode = 'ORACLE'
$$
CREATE PACKAGE BODY just_source_pack
SQL SECURITY DEFINER
AS
  -- package body variables
  inner_variable varchar(150):='just_source test_package';

  -- private routines
  PROCEDURE log (eid INT, ecmnt TEXT) AS
    proc_inner_variable varchar(150):='just_source test_package private proc_var';
  BEGIN
    INSERT INTO message_log (id, descr) VALUES (eid, ecmnt);
  END;

  -- private function
  FUNCTION rec_amount(eid INT) RETURN int(11) AS
    amount int(11);
    func_inner_variable varchar(150):='just_source test_package private func_var';
  BEGIN
    SELECT count(*) INTO amount FROM test_table WHERE ID >eid;
    log(eid, concat('id=' || eid || ' amount=' || amount));
    RETURN amount;
  END;

  -- public routines
  PROCEDURE test_proc(idn int, str varchar(100)) AS
    eid INT;
    proc_inner_variable varchar(150):='just_source test_package proc_var';
    pmin int:=10;
    pmax int:=100;
    CURSOR cur (pmin INT, pmax INT) is SELECT tt.id FROM test_table tt WHERE tt.id BETWEEN pmin AND pmax;
    cond_name_cond CONDITION FOR 1002;
    proc_except EXCEPTION;

  BEGIN
    eid:= last_insert_id();
    INSERT INTO test_table (id, descr) VALUES (eid, str);
    log(eid, 'inserted ' || str);
   END;

  -- public function
  FUNCTION test_function(eid INT) RETURN DECIMAL(10,2) AS
    eid INT;
    retvalue DECIMAL(10,2);
    func_inner_variable varchar(150):='just_source test_package func_var';
    pmin int:=10;
    pmax int:=100;
    CURSOR cur (pmin INT, pmax INT) is SELECT tt.id FROM test_table tt WHERE tt.id BETWEEN pmin AND pmax;
    cond_name_cond CONDITION FOR 1003;
    proc_except EXCEPTION;

  BEGIN
    eid:= last_insert_id();
    SELECT id INTO retvalue FROM test_table WHERE id=eid;
    log(eid, 'func id=' || eid || ' scale=' || ID * 3.14159);
    RETURN retvalue * 3.14159;
  END;

BEGIN
  -- This code is executed when the current session
  -- accesses any of the package routines for the first time
  log(0, 'Session ' || connection_id() || ' ' || current_user || ' started for ' || inner_variable);
  EXCEPTION WHEN 1002, 1003 THEN SELECT 1;
END
$$
SET @@sql_mode = @saved_sql_mode
$$

DELIMITER ;

-- ===================================================================================

DROP DATABASE IF EXISTS sc2;
CREATE DATABASE sc2;

USE sc2;

-- EMPTY