Category Archives: DataBase

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

oracle 11gの制御ファイルがなくなったのでデータベースが起動することはきません

1.エラーメッセージ:
[oracle@centos ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 25 09:31:04 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 661209088 bytes
Fixed Size 1338560 bytes
Variable Size 494928704 bytes
Database Buffers 159383552 bytes
Redo Buffers 5558272 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>

2.解決方法:
[oracle@centos ~]$ ll app/oracle/oradata/orcl/control011.ctl //二つの制御ファイルを確認する
-rw-r—– 1 oracle oinstall 9748480 Jan 25 09:30 app/oracle/oradata/orcl/control011.ctl
[oracle@centos ~]$
[oracle@centos ~]$ ll app/oracle/flash_recovery_area/orcl/control02.ctl
-rw-r—–. 1 oracle oinstall 9748480 Jan 25 09:30 app/oracle/flash_recovery_area/orcl/control02.ctl
[oracle@centos ~]$ vim app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora //配置ファイルを開く、制御ファイル名を確認
*.control_files=’/home/oracle/app/oracle/oradata/orcl/control01.ctl’,’/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl’ //制御ファイル名を修正

3.テスト:
[oracle@centos ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 25 09:42:31 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

oracle 11gにoem制御画面を起動エラーの対策

1.エラーメッセージ:
[oracle@centos ~]$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://centos:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control …
… Stopped.
[oracle@centos ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://centos:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control
…………………………………………………………………………
……… failed.
——————————————————————
Logs are generated in directory /home/oracle/app/oracle/product/11.2.0/dbhome_1/centos_orcl/sysman/log
[oracle@centos ~]$ tailf -n 3 /home/oracle/app/oracle/product/11.2.0/dbhome_1/centos_orcl/sysman/log/emdctl.trc //ルートとホストエラーを表示する
2014-01-23 14:54:47,568 Thread-3032057536 WARN http: nmehl_connect_internal: connect failed to (centos:1158): No route to host (error = 113)
2014-01-23 15:02:50,071 Thread-3032803008 WARN http: nmehl_connect_internal: connect failed to (centos:3938): No route to host (error = 113)
2014-01-23 15:02:50,073 Thread-3032803008 ERROR main: nmectla_agentctl: Error connecting to https://centos:3938/emd/main. Returning status code 1

2.解決方法:
[root@centos ~]# ifconfig eth1 //サーバーのルーティングとホストIPアドレスをチェック
eth1 Link encap:Ethernet HWaddr 08:00:27:5C:4A:F6
inet addr:2.2.2.114 Bcast:2.2.2.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fe5c:4af6/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:88220 errors:0 dropped:0 overruns:0 frame:0
TX packets:43011 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:6611001 (6.3 MiB) TX bytes:29439365 (28.0 MiB)
[root@centos ~]# route -n
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
2.2.2.0 0.0.0.0 255.255.255.0 U 1 0 0 eth1
0.0.0.0 2.2.2.1 0.0.0.0 UG 0 0 0 eth1
[root@centos ~]#

3.テスト:
[oracle@centos ~]$ emctl start dbconsole //成功に起動
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://centos:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control …….. started.
——————————————————————
Logs are generated in directory /home/oracle/app/oracle/product/11.2.0/dbhome_1/centos_orcl/sysman/log
[oracle@centos ~]$

10 / 12« 先頭...89101112