Category Archives: DataBase

MySQLストアドプロシージャにwhileループを使用するサンプル

SQLコード:
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc()
-> BEGIN
->
-> DECLARE i int;
-> SET i=1;
-> loop1: WHILE i<=10 DO
-> IF MOD(i,2)<>0 THEN /*偶数 – 再試行*/
-> SELECT CONCAT(i,” 古い番号です”);
-> END IF;
-> SET i=i+1;
-> END WHILE loop1;
-> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call myProc();

MySQLのトランザクションのロールバックのサンプルコード

SQLコード:
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc(
-> in_from INTEGER,
-> in_to INTEGER,
-> in_amount DECIMAL(8,2))
-> BEGIN
->
-> DECLARE txn_error INTEGER DEFAULT 0 ;
->
-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
-> SET txn_error=1;
-> END;
->
-> SAVEPOINT savepoint_tfer;
->
-> UPDATE employee
-> SET salary=in_amount
-> WHERE id=in_from;
->
-> IF txn_error THEN
-> ROLLBACK TO savepoint_tfer;
-> SELECT ‘Transfer aborted ‘;
-> ELSE
-> UPDATE employee
-> SET salary=in_amount
-> WHERE id=in_to;
->
-> IF txn_error THEN
-> ROLLBACK TO savepoint_tfer;
-> SELECT ‘Transfer aborted ‘;
-> END IF;
-> END IF;
->
-> END$$
Query OK, 0 rows affected (0.00 sec)

MySQLのストアドプロシージャに一つのロックオブジェクトを要求する

SQLコード:
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc()
-> BEGIN
-> DECLARE lock_result INT;
-> IF get_lock(‘sp_critical_section_lock’,60) THEN
-> /* このブロックはユーザーが一回実行する*/
-> SELECT ‘got lock’;
-> /* ここに重要なコード*/
-> SET lock_result=release_lock(‘sp_critical_section_lock’);
-> ELSE
-> SELECT ‘failed to acquire lock’;
-> /* ここにエラー処理*/
-> END IF;
-> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call myProc();
+———-+
| got lock |
+———-+
| got lock |
+———-+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>

MySQLストアドプロシージャにREPEATでカーソルをオープンしてデータを読み取る

SQLコード:
mysql>
mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc (in_customer_id INT)
-> BEGIN
->
-> DECLARE l_first_name VARCHAR(30);
-> DECLARE l_id INT;
-> DECLARE l_city VARCHAR(30);
-> DECLARE l_department_count INT;
-> DECLARE no_more_departments INT;
->
-> DECLARE dept_csr CURSOR FOR
-> SELECT id,first_name, city
-> FROM employee;
->
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
->
-> SET no_more_departments=0;
-> OPEN dept_csr;
-> dept_loop:REPEAT
-> FETCH dept_csr INTO l_id,l_first_name,l_city;
-> IF no_more_departments=0 THEN
-> SET l_department_count=l_department_count+1;
-> END IF;
-> select l_id,l_first_name,l_city;
-> UNTIL no_more_departments
-> END REPEAT dept_loop;
-> CLOSE dept_csr;
-> SET no_more_departments=0;
->
->
-> END$$
Query OK, 0 rows affected (0.00 sec)

MySQLストアドプロシージャ例外処理方法

SQL例外処理:
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc
-> (p_first_name VARCHAR(30),
-> p_last_name VARCHAR(30),
-> p_city VARCHAR(30),
-> p_description VARCHAR(30),
-> OUT p_sqlcode INT,
-> OUT p_status_message VARCHAR(100))
-> BEGIN
->
-> /*条件を宣言開始*/
->
-> DECLARE duplicate_key CONDITION FOR 1062;
-> DECLARE foreign_key_violated CONDITION FOR 1216;
->
-> /* 条件を宣言終了*/
->
-> /* 変数やカーソルを宣言開始 */
->
-> DECLARE l_manager_id INT;
->
-> DECLARE csr_mgr_id CURSOR FOR
-> SELECT id
-> FROM employee
-> WHERE first_name=p_first_name
-> AND last_name=p_last_name;
->
-> /* END Declare variables and cursors */
->
-> /* START Declare Exception Handlers */
->
-> DECLARE CONTINUE HANDLER FOR duplicate_key
-> BEGIN
-> SET p_sqlcode=1052;
-> SET p_status_message=’Duplicate key error’;
-> END;
->
-> DECLARE CONTINUE HANDLER FOR foreign_key_violated
-> BEGIN
-> SET p_sqlcode=1216;
-> SET p_status_message=’Foreign key violated’;
-> END;
->
-> DECLARE CONTINUE HANDLER FOR not FOUND
-> BEGIN
-> SET p_sqlcode=1329;
-> SET p_status_message=’No record found’;
-> END;
->
-> /* END Declare Exception Handlers */
->
-> /* START Execution */
->
-> SET p_sqlcode=0;
-> OPEN csr_mgr_id;
-> FETCH csr_mgr_id INTO l_manager_id;
->
-> IF p_sqlcode<>0 THEN /* Failed to get manager id*/
-> SET p_status_message=CONCAT(p_status_message,’ when fetching manager id’);
-> ELSE
-> INSERT INTO employee (first_name,id,city)
-> VALUES(p_first_name,l_manager_id,p_city);
->
-> IF p_sqlcode<>0 THEN /* Failed to insert new department */
-> SET p_status_message=CONCAT(p_status_message,
-> ‘ when inserting new department’);
-> END IF;
-> END IF;
->
-> CLOSE csr_mgr_id;
->
-> /* END Execution */
->
-> END$$
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> delimiter ;
mysql> set @myCode = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @myMessage = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> call myProc(‘Jason’,’Martin’,’New City’,’New Description’,@myCode,@myMessage);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select @myCode, @myMessage;
+———+————+
| @myCode | @myMessage |
+———+————+
| 0 | NULL |
+———+————+
1 row in set (0.00 sec)

mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)

MySQLのLEAVE文法の使い方

MySQLコード下記:

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc()
-> BEGIN
-> DECLARE i,j INT DEFAULT 1;
-> outer_loop: LOOP
-> SET j=1;
-> inner_loop: LOOP
-> SELECT CONCAT(i,” times “, j,” is “,i*j);
-> SET j=j+1;
-> IF j>12 THEN
-> LEAVE inner_loop;
-> END IF;
-> END LOOP inner_loop;
-> SET i=i+1;
-> IF i>12 THEN
-> LEAVE outer_loop;
-> END IF;
-> END LOOP outer_loop;
->
->
->
-> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call myProc();

「MySQL」LOOPでIterate文法サンプルコード

MySQLコード:

mysql>
mysql>
mysql> DELIMITER //
mysql> CREATE PROCEDURE myProc (IN in_count INT)
-> BEGIN
-> DECLARE count INT default 0;
->
-> increment: LOOP
-> SET count = count + 1;
-> IF count < 20 THEN ITERATE increment; END IF;
-> IF count > in_count THEN LEAVE increment;
-> END IF;
-> END LOOP increment;
->
-> SELECT count;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> call myProc(5);
+——-+
| count |
+——-+
| 20 |
+——-+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)

mysql>

MySQLデータベースのユーザーに権限を与える

データベースユーザーは読み取りと書き取り権限:

GRANT SELECT, INSERT, UPDATE, DELETE ON doreader.* TO ‘doreader_rw’@’%’ IDENTIFIED BY ‘doreader_rw_11’;

データベースユーザは読み取り権限:

GRANT SELECT ON doreader.* TO ‘doreader_r’@’%’ IDENTIFIED BY ‘doreader_r_11’;

 

MySQLエラー:Another MySQL daemon already running with the same unix socket

エラーメッセージ:
Another MySQL daemon already running with the same unix socket.
原因:
複数のMySQLプロセスが同じソケットを使用する理由
解決対策:
1.shutdown -h now

2.mysql.sockをリネームする
mysqlが起動

3.海外記事:
To prevent the problem from occurring, you must perform a graceful shutdown of the server from the command line rather than powering off the server.

# shutdown -h now
This will stop the running services before powering down the machine.

Based on Centos, an additional method for getting it back up again when you run into this problem is to move mysql.sock:

# mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak

# service mysqld start
Restarting the service creates a new entry called mqsql.sock

 

oracle 11gにoemコントロールエラーメッセージ:Environment variable ORACLE_SID not defined. Please define it.

エラーメッセージ:
C:\Documents and Settings\Administrator>emctl start dbconsole
Environment variable ORACLE_SID not defined. Please define it.

解決対策:
C:\Documents and Settings\Administrator>set oracle_sid=orcl
C:\Documents and Settings\Administrator>emca -config dbcontrol db -repos recreate