'데이타베이스' 카테고리의 글 목록 (2 Page)

1. RB_OCUS_POS_TEST2테이블에 200,00개의 데이타를 넣는다.

2. 1초에 3,000개 이상의 데이타가 들어가는 횟수를 알고 싶다면?

3. 아래는 위의 문제를 구하기 위해서 구성한 쿼리이다.

mysql> show create table RB_OCUS_POS_TEST2;
+-------------------+-----------------------------------
| Table | Create

+-------------------+-----------------------------------
| RB_OCUS_POS_TEST2 | CREATE TABLE `RB_OCUS_POS_TEST2` (
`seqno` int(11) NOT NULL,
`account_no` varchar(10) NOT NULL,
`stock_symbol` varchar(20) NOT NULL,
`start_volume` varchar(10) DEFAULT NULL,
`start_amount` varchar(13) DEFAULT NULL,
`start_price` varchar(13) DEFAULT NULL,
`stock_type` varchar(2) DEFAULT NULL,
`trustee_id` varchar(2) DEFAULT NULL,
`regtime` varchar(30) DEFAULT NULL,
PRIMARY KEY (`seqno`,`account_no`,`stock_symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+-----------------------------------
1 row in set (0.00 sec)

mysql>

mysql>

mysql> select * from RB_OCUS_POS_TEST2 limit 10;
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
| seqno | account_no | stock_symbol         | start_volume | start_amount | start_price   | stock_type | trustee_id | regtime                |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
|     1 | 0016100    | BBL                  | 0000001500   | 0000266962.22 | 000177.974813 | 02         |            | 20190828 11:19:25(140) |
|     2 | 0016100    | BPP                  | 0000002000   | 0000042000.00 | 000021.000000 | 02         |            | 20190828 11:19:25(141) |
|     3 | 0016100    | CK                   | 0000015375   | 0000296816.51 | 000019.305139 | 02         |            | 20190828 11:19:25(142) |
|     4 | 0016100    | IEC                  | 0012286603   | 0000308909.54 | 000000.025142 | 02         |            | 20190828 11:19:25(142) |
|     5 | 0016100    | IVL                  | 0000011769   | 0000309729.03 | 000026.317362 | 02         |            | 20190828 11:19:25(142) |
|     6 | 0016100    | KTB                  | 0000005000   | 0000103785.51 | 000020.757102 | 02         |            | 20190828 11:19:25(142) |
|     7 | 0055920    | ASAP                 | 0000001000   | 0000003106.87 | 000003.106870 | 02         |            | 20190828 11:19:25(142) |
|     8 | 0055920    | BCP                  | 0000001000   | 0000033574.22 | 000033.574220 | 02         |            | 20190828 11:19:25(142) |
|     9 | 0055920    | CBG                  | 0000000800   | 0000042844.70 | 000053.555875 | 02         |            | 20190828 11:19:25(143) |
|    10 | 0055920    | CHAYO                | 0000002000   | 0000008739.30 | 000004.369650 | 02         |            | 20190828 11:19:25(143) |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
10 rows in set (0.00 sec)

mysql>

mysql> select T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 limit 10;
+-------+
| t_cnt |
+-------+
| 2149 |
| 2556 |
| 3462 |
| 2635 |
| 2780 |
| 3051 |
| 2450 |
| 2310 |
| 2695 |
| 2716 |
+-------+
10 rows in set (0.22 sec)

mysql>
mysql> select T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000;
+-------+
| t_cnt |
+-------+
| 3462 |
| 3051 |
| 3192 |
| 3761 |
| 3085 |
| 3418 |
| 3341 |
| 3066 |
| 3353 |
| 3287 |
| 3037 |
+-------+
11 rows in set (0.23 sec)

mysql>

mysql>

mysql> select T2.t_regtime,T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000;
+-------------------+-------+
| t_regtime | t_cnt |
+-------------------+-------+
| 20190828 11:19:27 | 3462 |
| 20190828 11:19:30 | 3051 |
| 20190828 11:19:38 | 3192 |
| 20190828 11:19:46 | 3761 |
| 20190828 11:19:47 | 3085 |
| 20190828 11:19:55 | 3418 |
| 20190828 11:20:00 | 3341 |
| 20190828 11:20:03 | 3066 |
| 20190828 11:20:12 | 3353 |
| 20190828 11:20:15 | 3287 |
| 20190828 11:20:20 | 3037 |
+-------------------+-------+
11 rows in set (0.19 sec)

mysql>

mysql> select T2.t_regtime,T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000 order by T2.t_cnt desc;
+-------------------+-------+
| t_regtime | t_cnt |
+-------------------+-------+
| 20190828 11:19:46 | 3761 |
| 20190828 11:19:27 | 3462 |
| 20190828 11:19:55 | 3418 |
| 20190828 11:20:12 | 3353 |
| 20190828 11:20:00 | 3341 |
| 20190828 11:20:15 | 3287 |
| 20190828 11:19:38 | 3192 |
| 20190828 11:19:47 | 3085 |
| 20190828 11:20:03 | 3066 |
| 20190828 11:19:30 | 3051 |
| 20190828 11:20:20 | 3037 |
+-------------------+-------+
11 rows in set (0.00 sec)

mysql>

'데이타베이스 > MySQL' 카테고리의 다른 글

MySQL ROWNUM(select @r:=@r+1 As rowNum) (0)	2019.10.31
TIP - SEQ증감 (0)	2019.10.31
FUNCTION 생성및 활용 (0)	2019.10.31
LINUX,프로그래밍을 통한 PROCEDURE 호출후 결과 (0)	2019.10.30
#LINUX,common.c(MySQL) (0)	2019.10.30

#####FUNCTION 생성

DROP FUNCTION IF EXISTS GETMASTER_SYMB;

DELIMITER $$
CREATE FUNCTION GETMASTER_SYMB(intput1 TEXT, intput2 TEXT) RETURNS TEXT
BEGIN

DECLARE p_result    TEXT;
    DECLARE p_input1    TEXT;
    DECLARE p_input2    TEXT;

SET p_input1 = intput1;
SET p_input2 = intput2;

SET p_result = (SELECT SYMB FROM fssmaster WHERE ( COD2 = p_input1 OR COD1 = p_input2 ))
;

RETURN p_result;
END $$
DELIMITER ;

#####FUNCTION 활용

C:\Users\kiwoom\Downloads>
C:\Users\kiwoom\Downloads>mysql -u root -p
Enter password: ***********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.35-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use fss;
Database changed
mysql> SELECT fss.GETMASTER_SYMB('4262', '4263');
+------------------------------------+
| fss.GETMASTER_SYMB('4262', '4263') |
+------------------------------------+
| AAV |
+------------------------------------+
1 row in set (0.02 sec)

mysql>
mysql>
mysql>

/*

* gcc -c sample.c

* gcc -o sample sample.o libmysql.lib

*/

#include "C:\Program Files\MySQL\MySQL Connector C 6.1\include\mysql.h"
#include <string.h>
#include <stdio.h>

#define DB_HOST "127.0.0.1"
#define DB_USER "root"
#define DB_PASS "eogksalsrnr"
#define DB_NAME "fss"

int main(void)
{
    MYSQL *connection=NULL, conn;
    MYSQL_RES *sql_result;
    MYSQL_ROW sql_row;
    int query_stat;

    mysql_init(&conn);

connection = mysql_real_connect(&conn, DB_HOST, DB_USER, DB_PASS, DB_NAME, 3306, (char *)NULL, 0);

    if (connection == NULL)
    {
        fprintf(stderr, "Mysql connection error : %s", mysql_error(&conn));
        return 1;
    }

    query_stat = mysql_query(connection, "SELECT fss.GETMASTER_SYMB('4262', '4263')");
    if (query_stat != 0)
    {
        fprintf(stderr, "Mysql query error : %s", mysql_error(&conn));
        return 1;
    }

    sql_result = mysql_store_result(connection);

    while ( (sql_row = mysql_fetch_row(sql_result)) != NULL )
    {
        printf(">>>>>>%s\n", sql_row[0]);
    }

mysql_free_result(sql_result);

mysql_close(connection);

return 0;
}

'데이타베이스 > MySQL' 카테고리의 다른 글

TIP - SEQ증감 (0)	2019.10.31
select T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000; (0)	2019.10.31
LINUX,프로그래밍을 통한 PROCEDURE 호출후 결과 (0)	2019.10.30
#LINUX,common.c(MySQL) (0)	2019.10.30
#LINUX,send_msg_to_queue.c (2번째) (0)	2019.10.30

/*

* Mysql 프로그래밍(6) - 프로그래밍을 통한 PROCEDURE 호출후 결과

* Porcedure의 경우는 보통 일괄처리에 사용되지만, 다중리턴값을 줄때도 사용되어진다.

* Function과 차이점을 알아야 한다.

*/

#include "mysql.h"
#include <string.h>
#include <stdio.h>

#define DB_HOST "127.0.0.1"
#define DB_USER "root"
#define DB_PASS "root"
#define DB_NAME "root"

/*--------------------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `fss`.`GET_TRADEEVENT3`(IN input1 VARCHAR(20), IN input2 VARCHAR(20))
READS SQL DATA
BEGIN
    SET @input1=input1;
    SET @input2=input2;

    prepare id from
    'SELECT timeofevent, timeoftrade, orderbook, orderqty, price, highprice, lowprice, totalturnover
    FROM fss.tradeevent
    WHERE ( orderbook = ? OR orderbook = ? )
    ORDER BY orderbook asc, timeofevent asc';

    execute id USING @input1,@input2;

DEALLOCATE PREPARE id;
END$$
----------------------------------------------------------------------------------*/
int main(void)
{
    MYSQL *connection=NULL, conn;
    MYSQL_RES *sql_result;
    MYSQL_ROW sql_row;
    int query_stat;
    char argument_rbuf[2048];

mysql_init(&conn);

connection = mysql_real_connect(&conn, DB_HOST, DB_USER, DB_PASS, DB_NAME, 3306, (char *)NULL, CLIENT_MULTI_RESULTS);

    if (connection == NULL)
    {
        fprintf(stderr, "Mysql connection error : %s", mysql_error(&conn));
        return 1;
    }

    memset(argument_rbuf, 0x00, sizeof(argument_rbuf));
    sprintf(argument_rbuf, "SET @orderbook1 = '%s', @orderbook2 = '%s'", "22658", "16568");

    query_stat = mysql_query(connection, argument_rbuf);
    if (query_stat != 0)
    {
        fprintf(stderr, "@@@@[1010]Mysql query error : %s", mysql_error(&conn));
        return 1;
    }
    query_stat = mysql_query(connection, "CALL GET_TRADEEVENT3(@orderbook1,@orderbook2)");
    if (query_stat != 0)
    {
        fprintf(stderr, "@@@@[2020]Mysql query error : %s", mysql_error(&conn));
        return 1;
    }

    sql_result = mysql_store_result(connection);

    while ( (sql_row = mysql_fetch_row(sql_result)) != NULL )
    {
        printf(">>>[%s],[%s],[%s],[%s],[%s],[%s],[%s]\n", sql_row[1],sql_row[2],sql_row[3],sql_row[4],sql_row[5],sql_row[6],sql_row[7]);
    }

mysql_free_result(sql_result);

mysql_close(connection);

return 0;
}

/*RESULT----------------------------------------------------------------------------------------
>>>[2017-04-03T10:53:06.661],[16568],[50000],[1040],[1040],[1040],[52000000]
>>>[2017-04-03T15:52:31.685],[16568],[4900],[1040],[1040],[1040],[57096000]
>>>[2017-04-03T15:52:45.007],[16568],[100],[1110],[1110],[1040],[57207000]
>>>[2017-04-03T15:53:07.334],[16568],[500],[1110],[1110],[1040],[57762000]
>>>[2017-04-03T15:53:13.430],[16568],[700],[1110],[1110],[1040],[58539000]
>>>[2017-04-03T15:53:16.995],[16568],[800],[1110],[1110],[1040],[59427000]
>>>[2017-04-03T15:54:44.687],[16568],[6800],[1040],[1110],[1040],[66499000]
>>>[2017-04-03T15:54:44.687],[16568],[3700],[1040],[1110],[1040],[70347000]
>>>[2017-04-03T15:54:57.614],[16568],[500],[1040],[1110],[1040],[70867000]
>>>[2017-04-03T15:57:17.670],[16568],[900],[1110],[1110],[1040],[71866000]
>>>[2017-04-03T15:57:17.858],[16568],[400],[1110],[1110],[1040],[72310000]
>>>[2017-04-03T15:57:17.858],[16568],[500],[1130],[1130],[1040],[72875000]
>>>[2017-04-03T15:57:18.063],[16568],[800],[1130],[1130],[1040],[73779000]
>>>[2017-04-03T15:57:18.063],[16568],[100],[1130],[1130],[1040],[73892000]
>>>[2017-04-03T15:57:18.583],[16568],[900],[1130],[1130],[1040],[74909000]
>>>[2017-04-03T15:57:21.882],[16568],[900],[1130],[1130],[1040],[75926000]
>>>[2017-04-03T15:57:41.366],[16568],[200],[1130],[1130],[1040],[76152000]
>>>[2017-04-03T15:57:41.366],[16568],[300],[1130],[1130],[1040],[76491000]
>>>[2017-04-03T15:57:41.533],[16568],[500],[1130],[1130],[1040],[77056000]
>>>[2017-04-03T15:57:41.721],[16568],[500],[1130],[1130],[1040],[77621000]
>>>[2017-04-03T15:57:41.912],[16568],[500],[1130],[1130],[1040],[78186000]
>>>[2017-04-03T15:57:42.996],[16568],[300],[1130],[1130],[1040],[78525000]
>>>[2017-04-03T15:57:42.996],[16568],[200],[1130],[1130],[1040],[78751000]
>>>[2017-04-03T15:57:43.904],[16568],[500],[1130],[1130],[1040],[79316000]
>>>[2017-04-03T15:57:47.634],[16568],[500],[1130],[1130],[1040],[79881000]
>>>[2017-04-03T15:59:57.163],[16568],[300],[1140],[1140],[1040],[81466000]
>>>[2017-04-03T15:59:57.163],[16568],[900],[1130],[1130],[1040],[81124000]
>>>[2017-04-03T15:59:57.163],[16568],[200],[1130],[1130],[1040],[80107000]
>>>[2017-04-03T16:00:00.440],[16568],[1400],[1140],[1140],[1040],[83062000]
>>>[2017-04-03T16:00:02.880],[16568],[1200],[1150],[1150],[1040],[84670000]
>>>[2017-04-03T16:00:02.880],[16568],[200],[1140],[1140],[1040],[83290000]
>>>[2017-04-03T16:00:05.983],[16568],[1100],[1150],[1150],[1040],[85935000]
>>>[2017-04-03T16:00:05.983],[16568],[300],[1150],[1150],[1040],[86280000]
>>>[2017-04-03T16:00:08.448],[16568],[1400],[1150],[1150],[1040],[87890000]
>>>[2017-04-03T16:02:31.378],[16568],[4500],[1040],[1150],[1040],[92570000]
>>>[2017-04-03T16:02:31.378],[16568],[2000],[1040],[1150],[1040],[94650000]
>>>[2017-04-03T16:02:31.378],[16568],[500],[1040],[1150],[1040],[95170000]
>>>[2017-04-03T16:02:55.979],[16568],[1500],[1150],[1150],[1040],[96895000]
>>>[2017-04-03T16:02:56.161],[16568],[100],[1160],[1160],[1040],[98621000]
>>>[2017-04-03T16:02:56.161],[16568],[1400],[1150],[1150],[1040],[98505000]
>>>[2017-04-03T16:03:51.826],[16568],[200],[1160],[1160],[1040],[98853000]
>>>[2017-04-03T16:03:57.872],[16568],[800],[1160],[1160],[1040],[99781000]
>>>[2017-04-03T16:04:04.512],[16568],[1200],[1160],[1160],[1040],[101173000]
>>>[2017-04-03T16:05:24.370],[16568],[500],[1160],[1160],[1040],[101753000]
>>>[2017-04-03T16:07:47.740],[16568],[500],[1160],[1160],[1040],[102333000]
>>>[2017-04-03T16:16:20.053],[16568],[500],[1160],[1160],[1040],[102913000]
>>>[2017-04-03T09:59:14.791],[22658],[60000],[57],[57],[57],[3420000]
>>>[2017-04-03T09:59:15.161],[22658],[20000],[57],[57],[57],[4560000]
>>>[2017-04-03T09:59:25.398],[22658],[40000],[57],[57],[57],[6840000]
>>>[2017-04-03T09:59:25.512],[22658],[20000],[57],[57],[57],[7980000]
>>>[2017-04-03T09:59:35.820],[22658],[40000],[57],[57],[57],[10260000]
>>>[2017-04-03T10:17:29.628],[22658],[40000],[56],[57],[56],[12500000]
>>>[2017-04-03T10:17:29.628],[22658],[20000],[57],[57],[56],[13640000]
>>>[2017-04-03T10:17:29.922],[22658],[20000],[57],[57],[56],[14780000]
>>>[2017-04-03T10:17:40.268],[22658],[40000],[57],[57],[56],[17060000]
>>>[2017-04-03T10:17:40.381],[22658],[20000],[57],[57],[56],[18200000]
>>>[2017-04-03T10:17:51.893],[22658],[30000],[57],[57],[56],[19910000]
>>>[2017-04-03T10:18:08.321],[22658],[40000],[56],[57],[56],[22150000]
>>>[2017-04-03T10:18:18.549],[22658],[40000],[56],[57],[56],[24390000]
>>>[2017-04-03T10:18:37.490],[22658],[40000],[56],[57],[56],[26630000]
>>>[2017-04-03T10:18:41.700],[22658],[40000],[56],[57],[56],[28870000]
>>>[2017-04-03T10:18:45.898],[22658],[10000],[56],[57],[56],[29430000]
>>>[2017-04-03T10:19:10.753],[22658],[40000],[56],[57],[56],[31670000]
>>>[2017-04-03T10:22:22.731],[22658],[40000],[56],[57],[56],[33910000]
>>>[2017-04-03T10:23:03.844],[22658],[40000],[53],[57],[53],[36030000]
>>>[2017-04-03T10:23:03.844],[22658],[20000],[52],[57],[52],[37070000]
>>>[2017-04-03T14:26:23.652],[22658],[10000],[59],[59],[52],[37660000]
>>>[2017-04-03T14:28:03.314],[22658],[10000],[44],[59],[44],[38100000]
>>>[2017-04-03T14:28:42.773],[22658],[200000],[44],[59],[44],[46900000]
>>>[2017-04-03T14:33:33.473],[22658],[40000],[45],[59],[44],[48700000]
>>>[2017-04-03T14:33:38.545],[22658],[40000],[45],[59],[44],[50500000]
>>>[2017-04-03T14:33:42.768],[22658],[40000],[45],[59],[44],[52300000]
>>>[2017-04-03T14:33:46.983],[22658],[40000],[45],[59],[44],[54100000]
>>>[2017-04-03T14:33:51.194],[22658],[20000],[45],[59],[44],[55000000]
>>>[2017-04-03T14:38:35.640],[22658],[40000],[45],[59],[44],[56800000]
>>>[2017-04-03T14:38:50.929],[22658],[35000],[44],[59],[44],[58340000]
----------------------------------------------------------------------------------------*/

'데이타베이스 > MySQL' 카테고리의 다른 글

select T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000; (0)	2019.10.31
FUNCTION 생성및 활용 (0)	2019.10.31
#LINUX,common.c(MySQL) (0)	2019.10.30
#LINUX,send_msg_to_queue.c (2번째) (0)	2019.10.30
#LINUX,recv_msg_from_queue.c (두번째) (0)	2019.10.30

#include <stdio.h>

#include <string.h>

#include <stdlib.h>

#include <time.h>

#include <errno.h>

#include <fcntl.h>

#include <signal.h>

#include <setjmp.h>

#include <unistd.h>

#include <ctype.h>

#include <sys/ipc.h>

#include <sys/msg.h>

#include <sys/stat.h>

#include <sys/types.h>

#include <sys/socket.h>

#include <netinet/in.h>

#include <arpa/inet.h>

#include <netdb.h>

#include <sys/shm.h>

#include <mysql.h>

extern MYSQL *STDCALL mysql_init(MYSQL *mysql);

extern MYSQL *STDCALL mysql_real_connect(MYSQL *mysql, const char *host,

const char *user,

const char *passwd,

const char *db,

unsigned int port,

const char *unix_socket,

unsigned long clientflag);

extern const char * STDCALL mysql_error(MYSQL *mysql);

extern int STDCALL mysql_select_db(MYSQL *mysql, const char *db);

extern unsigned int STDCALL mysql_errno(MYSQL *mysql);

extern void STDCALL mysql_close(MYSQL *sock);

extern int STDCALL mysql_query(MYSQL *mysql, const char *q);

#ifdef MYSQL_ERROR

#endif

MYSQL mysql;

char mysql_log_msg[SZ_MAX];

/*- ------------------------------------------------ -*

*- ConnectDB

*- ------------------------------------------------ -*/

int ConnectDB(char *database)

{

FILE *fp;

char conn[20], user[20], passwd[20];

char buff[100], mysql_file[200];

if((fp= fopen("MYSQLDATABASE.dat", "r")) == NULL)

{

return(-1);

}

while(fgets(buff,sizeof(buff),fp))

{

if(strncmp(buff, "#", 1) == 0 || strncmp(buff, "\n", 1) == 0) continue;

sscanf(buff, "%s %s %s", &conn, &user, &passwd);

}

fclose(fp);

mysql_init(&mysql);

if(!mysql_real_connect(&mysql, conn, user, passwd, database, 0, NULL, 0))

{

return(-2);

}

printf("CONNECT DATABASE\n");

if(mysql_select_db(&mysql, database) != 0)

{

return(-3);

}

return(0);

}

/*- ------------------------------------------------ -*

*- DATABASE 연결해제 함수

*- ------------------------------------------------ -*/

DisconnectDB()

{

mysql_close(&mysql);

printf("DISCONNECT DATABASE\n");

}

/*- ------------------------------------------------ -*

*- MYSQL Table Query function

*- ------------------------------------------------ -*/

int MysqlQuery(char *query)

{

FILE *fp=NULL;

char status_file[SZ_MAX], server_status[20];

mysql_query(&mysql, "Set names euckr;");

if((fp= fopen("SERVER.dat", "r")) == NULL)

{

return(-1);

}

while(fgets(server_status,sizeof(server_status),fp))

{

if(strncmp(server_status, "#", 1) == 0 || strncmp(server_status, "\n", 1) == 0) continue;

}

fclose(fp);

//[LIVE] is all ok(INSERT,TRUNCATE,UPDATE,REPLACE,DELETE,SELECT etc),

//[STANDBY] is SELECT ok

if( (strncmp(server_status, "LIVE", 4) == 0) || ((strncmp(server_status, "STANDBY", 7) == 0)

&& ((strncmp(query, "SELECT", 6) == 0) || (strncmp(query, "(SELECT", 7) == 0))))

{

if(mysql_query(&mysql, query) != 0)

{

sprintf(mysql_log_msg, "MYSQL ERROR[%s-%d]\n", mysql_error(&mysql), mysql_errno(&mysql));

if(mysql_errno(&mysql) != 1062)

{

#ifdef MYSQL_ERROR

printf("MYSQL ERROR[%s-%s]\n", query, mysql_log_msg);

#endif

printf("%s\n", mysql_log_msg);

}

return(mysql_errno(&mysql));

}

return(0);

}

'데이타베이스 > MySQL' 카테고리의 다른 글

FUNCTION 생성및 활용 (0)	2019.10.31
LINUX,프로그래밍을 통한 PROCEDURE 호출후 결과 (0)	2019.10.30
#LINUX,send_msg_to_queue.c (2번째) (0)	2019.10.30
#LINUX,recv_msg_from_queue.c (두번째) (0)	2019.10.30
#Queue를 이용한 MySQL입력 성능향상에 관한 글(1) (0)	2019.10.30

#include <stdio.h>
#include <sys/timeb.h>
#include <stdlib.h>
#include <stdio.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#include <time.h>
#include <stdlib.h>
#include <string.h>
#include <signal.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <netdb.h>
#include <sys/wait.h>
#include <sys/poll.h>
#include <stdbool.h>
#include <fcntl.h>
#include <sys/msg.h>
#include <stdlib.h>
#include <errno.h>

#include "isam.h"
#include "mysql.h"

#define SZ_QUEUETEXT 4096
#define ____MAX_QUEUE_MAX_NUMER 10
#define ____QUEUE_MAX_NUMER 10

int isfd=0, isrc=0, ismd=0;
static char *____get_semiconlon_date_time(void);
static int read_memory_from_file(void);
static int insert_into_rb_order_test(void);

char rb_memory_buf[200000][999];
int rb_memory_cnt=0;

int g_msg_id[____MAX_QUEUE_MAX_NUMER];

int main(void)
{
   int kk, rc, qkey;
    char begin_time[100],end_time[100];
    char queue_number[____MAX_QUEUE_MAX_NUMER][10] =    {"46560","46561","46562","46563","46564",

"46565","46566","46567","46568","46569"};

    for(kk=0; kk<____MAX_QUEUE_MAX_NUMER; kk++)
    {
        sscanf(queue_number[kk], "%x", &qkey);
        g_msg_id[kk] = msgget(qkey, IPC_CREAT | 0666 );

        if(g_msg_id[kk] == -1)
        {
            printf("%s queue ipc_creat fail\n", queue_number[kk]);
            return(-1);
        }
    }

memset(begin_time,0x00,sizeof(begin_time));
memset(end_time,0x00,sizeof(end_time));

rc = read_memory_from_file();

printf("send_msg_to_queue Insert>>[%s]\n", ____get_semiconlon_date_time());
sprintf(begin_time, "%s", ____get_semiconlon_date_time());

    rc = insert_into_rb_order_test();
    if(rc)
    {
        printf("insert_into_rb_order_test>>error(%d)\n", rc);
        return(-1);
    }
    printf("send_msg_to_queue Insert>>[%s]\n", ____get_semiconlon_date_time());
    printf("send_msg_to_queue Insert>>CNT[%d]\n",rb_memory_cnt);

sprintf(end_time, "%s", ____get_semiconlon_date_time());

printf("send_msg_to_queue>>begin[%s],end[%s]\n", begin_time,end_time);

return(0);
}

int read_memory_from_file(void)
{
    char jfile[40];
    char    command[7024];
    char buff[1024], tmpb[32];
    FILE *jfp=NULL;

memset(rb_memory_buf,0x00,sizeof(rb_memory_buf));

sprintf(jfile, "%s/%s", "/sdat/xdat/iFIS/dat", "ocus_pos.dat");

    jfp = fopen(jfile, "r");
    if (jfp == NULL)
    {
        return(-1);
    }

    for(;;)
    {
        memset(buff, 0x00, sizeof(buff));
        if(fgets(buff, sizeof(buff), jfp)==NULL) break;

        strcpy(rb_memory_buf[rb_memory_cnt], buff);
        rb_memory_cnt++;
    }
    if(jfp != NULL) fclose(jfp);
    return(0);
}

int insert_into_rb_order_test(void)
{
    int     divide_queue_number;
    int     kk, rc;
    struct ocus_pos_o ocus_pos_o;
    char    command[7024];
    char buff[1024], tmpb[32];

    struct queue_message_buffer {
        long mtype;
        char mtext[SZ_QUEUETEXT];
    };
    struct queue_message_buffer msg_buf;

    kk=0;
    for(;;)
    {
        memcpy(&ocus_pos_o, rb_memory_buf[kk], sizeof(struct ocus_pos_o));
        memset(command,0x00,sizeof(command));

       sprintf(command, "%d|"
                        "%.10s|"
                        "%.20s|"
                        "%.10s|"
                        "%.13s|"
                        "%.13s|"
                        "%.2s|"
                        "%.2s|",
                        kk + 1,
                        ocus_pos_o.account_no,
                        ocus_pos_o.stock_symbol,
                        ocus_pos_o.start_volume,
                        ocus_pos_o.start_amount,
                        ocus_pos_o.start_price,
                        ocus_pos_o.stock_type,
                        ocus_pos_o.trustee_id);

msg_buf.mtype = 1;
memcpy(&msg_buf.mtext[0], command, strlen(command));

divide_queue_number = kk % ____QUEUE_MAX_NUMER;

        if(msgsnd(g_msg_id[divide_queue_number], (struct queue_message_buffer *)&msg_buf, strlen(command), 0) < 0)
        {
            if(errno==EAGAIN)
            {
                printf(">>msg_push_thread>>[QueueDebug]queue is full, and IPC_NOWAIT was asserted\n");
            }
            else if(errno==EACCES)
            {
                printf(">>msg_push_thread>>[QueueDebug]permission denied, no write permission\n");
            }
            else if(errno==EFAULT)
            {
                printf(">>msg_push_thread>>[QueueDebug]msgp address isn't accessable - invalid\n");
            }
            else if(errno==EIDRM)
            {
                printf(">>msg_push_thread>>[QueueDebug]The message queue has been removed\n");
            }
            else if(errno==EINTR)
            {
                printf(">>msg_push_thread>>[QueueDebug]Received a signal while waiting to write\n");
            }
            else if(errno==EINVAL)
            {
                printf(">>msg_push_thread>>[QueueDebug]Invalid message queue identifier, nonpositive message type,

                        or invalid message size\n");
            }
            else if(errno==ENOMEM)
            {
                printf(">>msg_push_thread>>[QueueDebug]Not enough memory to copy message buffer\n");
            }
        }

kk++;
if(kk == rb_memory_cnt) break;

if(kk/100*100==kk) printf(">>QUEUE[%d]>>TOT[%.7d],NOW[%.7d],DATA[%s]\n", divide_queue_number, rb_memory_cnt,

kk, command);

usleep(50);
}

printf(">>QUEUE[%d]>>TOT[%.7d],NOW[%.7d],DATA[%s]\n", divide_queue_number, rb_memory_cnt, kk, command);

return(0);
}

char *____get_semiconlon_date_time(void)
{
    struct timeb itb;
    struct tm *lt;
    static char dt[100];

ftime(&itb);

lt = localtime(&itb.time);

memset(dt , 0x00 , sizeof(dt));

    sprintf(dt, "%04d%02d%02d %02d:%02d:%02d(%03d)"
             , lt->tm_year + 1900, lt->tm_mon + 1, lt->tm_mday
             , lt->tm_hour, lt->tm_min, lt->tm_sec
             , itb.millitm);
    return dt;
}

'데이타베이스 > MySQL' 카테고리의 다른 글

LINUX,프로그래밍을 통한 PROCEDURE 호출후 결과 (0)	2019.10.30
#LINUX,common.c(MySQL) (0)	2019.10.30
#LINUX,recv_msg_from_queue.c (두번째) (0)	2019.10.30
#Queue를 이용한 MySQL입력 성능향상에 관한 글(1) (0)	2019.10.30
1달전 자료 지우는 쿼리 (0)	2019.10.29

#include <stdio.h>
#include <sys/timeb.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#include <time.h>
#include <stdlib.h>
#include <string.h>
#include <signal.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <netdb.h>
#include <sys/wait.h>
#include <sys/poll.h>
#include <stdbool.h>
#include <fcntl.h>
#include <sys/msg.h>
#include <stdlib.h>
#include <pthread.h>
#include <errno.h>
#include "mysql.h"

#define SZ_QUEUETEXT 4096

struct __MESSAGE_QUEUE
{
long mtype;
char mtext[SZ_QUEUETEXT];
};

MYSQL *mysql=NULL;
MYSQL *connection=NULL;
char mesg[1024];

static int s_insert(char *buff);
static int s_commit(void);
static int ____l_myInsert(MYSQL *mysql, char *query, char *errmsg);
static int extract_message_from_strchr(char *resultstr, char msg[8][100]);
static int insert_into_rb_order_test(char *rbuf, int rlen, char *queue_name, int kk);
static int ____getmsgqueue(key_t qkey);
static int ____rcvmsgqueue(int q_id, struct __MESSAGE_QUEUE *msg_buf, int msg_type);
static char *____get_semiconlon_date_time(void);

char rb_memory_buf[200000][999];
int rb_memory_cnt=0;

int main(int argc, char *argv[])
{
    int kk, rc, rlen;
    struct __MESSAGE_QUEUE msg_buf;
    int qkey, q_id = -1, mtype=1;
    char rbuf[4096];

if(argc != 2) return(-100);

    sscanf(argv[1], "%x", &qkey);
    if((q_id = ____getmsgqueue(qkey)) < 0)
    {
        printf( "____getmsgqueue q_id[%d] error[%d]\n", q_id, errno);
        return(-1);
    }

    mysql = mysql_init(NULL);
    if(mysql == NULL) {
        return(-1);
    }

    connection = mysql_real_connect(mysql, "127.0.0.1", "heroG", "duddnd702!", "HEROG", 0, NULL, 0);
    if (connection == NULL)
    {
        return(-2);
    }

/*READ LOOP------------------------------------------*/

    kk=0;
    while(1)
    {
        rlen = 0;
        errno = 0;
        memset(&rbuf, 0x00, sizeof(rbuf));
        memset(&msg_buf, 0x00, sizeof(msg_buf));

        rlen = ____rcvmsgqueue(q_id, &msg_buf, mtype);
        if (errno > 0)
        {
            printf( "____rcvmsgqueue error[%d]\n", errno);
            break;
        }
        else
        {
            if(rlen == 0)
            {
                usleep(10000);
                continue;
            }
        }
        memcpy(&rbuf[0], (char *)&msg_buf.mtext[0], rlen);

        rc = insert_into_rb_order_test(rbuf, rlen, argv[1], kk);
        if(rc)
        {
            return(-1);
        }

        kk++;
    }
    mysql_close(connection);
    return(0);
}

int insert_into_rb_order_test(char *rbuf, int rlen, char *queue_name, int kk)
{
    char command[4096];
    char msg[8][100];
    int rc;

memset(command, 0x00, sizeof(command));
memcpy(command, rbuf, rlen);

#if(1)
    rc = extract_message_from_strchr(rbuf, msg);
    if(rc)
    {
        //ERROR
    }
    sprintf(command, "REPLACE INTO"

" RB_OCUS_POS_TEST2(seqno,account_no,stock_symbol,start_volume,start_amount,"

                     "start_price,stock_type,trustee_id,regtime) VALUES("
                     "%s,"
                     "'%s',"
                     "'%s',"
                     "'%s',"
                     "'%s',"
                     "'%s',"
                     "'%s',"
                     "'%s',"
                     "'%s');",
                     msg[0],
                     msg[1],
                     msg[2],
                     msg[3],
                     msg[4],
                     msg[5],
                     msg[6],
                     msg[7],
                     ____get_semiconlon_date_time());
    rc = s_insert(command);
    if(rc)
    {
        printf("(error)(%s)\n", command);
        return(-1);
    }
    else
    {
        s_commit();
    }
#endif

if(kk / 500 * 500 == kk) printf(">>QUEUE[%s],TIME(%s),SQ(%s)\n", queue_name, ____get_semiconlon_date_time(), command);

return(0);
}

int extract_message_from_strchr(char *resultstr, char msg[8][100])
{
    char tmp[100][1024];
    char tmpstr[1024];
    char *d1, *d2;
    int kk, rc;

memset(tmp,0x00,sizeof(tmp));

for(kk=0; kk<8; kk++) memset(&msg[kk],0x00,sizeof(msg[kk]));

    kk=0;
    d1 = strchr(resultstr, '|');
    memcpy(tmp[kk++], resultstr, d1 - resultstr);

    if(d1 != NULL)
    {
        for(;;)
        {
            d2 = strchr(d1+1, '|');
            if(d2==NULL) break;
            memcpy(tmp[kk++], d1+1, d2-d1-1);

            d1 = strchr(d2+1, '|');
            if(d1==NULL) break;
            memcpy(tmp[kk++], d2+1, d1-d2-1);
        }
    }
    rc=kk;

    for(kk=0; kk<rc; kk++)
    {
        memset(tmpstr,0x00,sizeof(tmpstr));
        strcpy(tmpstr, tmp[kk]);

        memcpy(msg[kk],tmpstr,strlen(tmpstr));
    }
    return(0);
}

int s_insert(char *buff)
{
    if (____l_myInsert(mysql, buff, mesg) < 0)
    {
        printf( "%s\n", mesg);
        return(-1);
    }

return(0);
}

int s_commit(void)
{
mysql_commit(mysql);

return(0);
}

int ____l_myInsert(MYSQL *mysql, char *query, char *errmsg)
{
int retn;

    retn = mysql_real_query(mysql, query, strlen(query));
    if (retn != 0)
    {
        sprintf(errmsg, "SQL insert Error[%d].",mysql_errno(mysql));
        return -1;
    }

return (mysql_affected_rows(mysql));
}

/*- ------------------------------------------------------------------------- -*
*- Message Queue Get
*- ------------------------------------------------------------------------ -*/
int ____getmsgqueue(key_t qkey)
{
int q_id;

    if((q_id = msgget(qkey,IPC_CREAT | 0666)) < 0)
    {
        return(-1);
    }
    return (q_id);
}

/*- ------------------------------------------------------------------------- -*
*- Message Queue Rcveive
*- ------------------------------------------------------------------------ -*/
int ____rcvmsgqueue(int q_id, struct __MESSAGE_QUEUE *msg_buf, int msg_type)
{
int msg_size;

    if((msg_size = msgrcv(q_id, msg_buf, SZ_QUEUETEXT , msg_type, MSG_NOERROR)) < 0)
    {
        if(errno == ENOMSG)
        {
            return(0);
        }
        return(-1);
    }
    return (msg_size);
}

char *____get_semiconlon_date_time(void)
{
    struct timeb itb;
    struct tm *lt;
    static char dt[100];

ftime(&itb);

lt = localtime(&itb.time);

memset(dt , 0x00 , sizeof(dt));

    sprintf(dt, "%04d%02d%02d %02d:%02d:%02d(%03d)"
             , lt->tm_year + 1900, lt->tm_mon + 1, lt->tm_mday
             , lt->tm_hour, lt->tm_min, lt->tm_sec
             , itb.millitm);
    return dt;
}

'데이타베이스 > MySQL' 카테고리의 다른 글

#LINUX,common.c(MySQL) (0)	2019.10.30
#LINUX,send_msg_to_queue.c (2번째) (0)	2019.10.30
#Queue를 이용한 MySQL입력 성능향상에 관한 글(1) (0)	2019.10.30
1달전 자료 지우는 쿼리 (0)	2019.10.29
MySQL LOCK 확인 및 해제(kill) 방법 (0)	2019.10.29

mysql> select * from RB_OCUS_POS_TEST2 limit 10;
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
| seqno | account_no | stock_symbol         | start_volume | start_amount | start_price   | stock_type | trustee_id | regtime                |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
|     1 | 0016100    | BANPU                | 0000045000   | 0000645569.57 | 000014.345990 | 02         |            | 20190829 15:31:55(410) |
|     2 | 0016100    | BBL                  | 0000001500   | 0000266962.22 | 000177.974813 | 02         |            | 20190829 15:31:55(410) |
|     3 | 0016100    | BPP                  | 0000002000   | 0000042000.00 | 000021.000000 | 02         |            | 20190829 15:31:55(410) |
|     4 | 0016100    | CK                   | 0000015375   | 0000296816.51 | 000019.305139 | 02         |            | 20190829 15:31:55(410) |
|     5 | 0016100    | IEC                  | 0012286603   | 0000308909.54 | 000000.025142 | 02         |            | 20190829 15:31:55(410) |
|     6 | 0016100    | IVL                  | 0000011769   | 0000309729.03 | 000026.317362 | 02         |            | 20190829 15:31:55(410) |
|     7 | 0016100    | KTB                  | 0000005000   | 0000103785.51 | 000020.757102 | 02         |            | 20190829 15:31:55(410) |
|     8 | 0055920    | ASAP                 | 0000001000   | 0000003106.87 | 000003.106870 | 02         |            | 20190829 15:31:55(411) |
|     9 | 0055920    | BCP                  | 0000001000   | 0000033574.22 | 000033.574220 | 02         |            | 20190829 15:31:55(411) |
|    10 | 0055920    | CBG                  | 0000000800   | 0000042844.70 | 000053.555875 | 02         |            | 20190829 15:31:55(411) |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
10 rows in set (0.00 sec)

mysql>

mysql>

mysql> select @r:=@r+1 As rowNum, T2.* from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2, (Select @r:=0) r limit 100;
+--------+-------------------+-------+
| rowNum | t_regtime         | t_cnt |
+--------+-------------------+-------+
|      1 | 20190829 15:31:55 | 2640 |
|      2 | 20190829 15:31:56 | 4744 |
|      3 | 20190829 15:31:57 | 5036 |
|      4 | 20190829 15:31:58 | 5270 |
|      5 | 20190829 15:31:59 | 2587 |
|      6 | 20190829 15:32:00 | 2909 |
|      7 | 20190829 15:32:01 | 3261 |
|      8 | 20190829 15:32:02 | 5019 |
|      9 | 20190829 15:32:03 | 5488 |
|     10 | 20190829 15:32:04 | 5853 |
|     11 | 20190829 15:32:05 | 4381 |
|     12 | 20190829 15:32:06 | 4307 |
|     13 | 20190829 15:32:07 | 2871 |
|     14 | 20190829 15:32:08 |   345 |
|     15 | 20190829 15:32:09 | 3368 |
|     16 | 20190829 15:32:10 | 3087 |

mysql> select @r:=@r+1 As rowNum, T2.* from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2, (Select @r:=0) r limit 100;
+--------+-------------------+-------+
| rowNum | t_regtime         | t_cnt |
+--------+-------------------+-------+
|      1 | 20190829 15:31:55 | 2640 |
|      2 | 20190829 15:31:56 | 4744 |
|      3 | 20190829 15:31:57 | 5036 |
|      4 | 20190829 15:31:58 | 5270 |
|      5 | 20190829 15:31:59 | 2587 |
|      6 | 20190829 15:32:00 | 2909 |
|      7 | 20190829 15:32:01 | 3261 |
|      8 | 20190829 15:32:02 | 5019 |
|      9 | 20190829 15:32:03 | 5488 |
|     10 | 20190829 15:32:04 | 5853 |
|     11 | 20190829 15:32:05 | 4381 |
|     12 | 20190829 15:32:06 | 4307 |

>>아래 쿼리의 내용은 큐를 이용해서 데이타를 입력했을때에 순서적으로 입력이 안되는 상태임을 보여주는 예입니다.

>>큐가 10개라면, SERIAL하게 데이타가 입력되는것에 대한 정합성을 보장하지 않습니다.

>>저장하는 용도의 입력이면 괜찮지만, 순서적으로 입력이 보장되어야 한다면, 다른 로직을 넣어서 이를 해결해야 합니다.

mysql> select T2.rowNum, T2.t_seqno, T2.rowNum - T2.t_seqno from DUMMY T1, (select @r:=@r+1 As rowNum, T1.seqno as t_seqno from RB_OCUS_POS_TEST2 T1, (Select @r:=0) r order by regtime asc) T2 order by T2.t_seqno asc limit 100;
+--------+---------+------------------------+
| rowNum | t_seqno | T2.rowNum - T2.t_seqno |
+--------+---------+------------------------+
|      1 |       1 |                      0 |
|      2 |       2 |                      0 |
|      3 |       3 |                      0 |
|      4 |       4 |                      0 |
|      5 |       5 |                      0 |
|      6 |       6 |                      0 |
|      7 |       7 |                      0 |
|      8 |       8 |                      0 |
|      9 |       9 |                      0 |
|     10 |      10 |                      0 |
|     12 |      11 |                      1 |
|     11 |      12 |                     -1 |
|     13 |      13 |                      0 |

mysql> select T2.rowNum, T2.t_seqno, T2.rowNum - T2.t_seqno from DUMMY T1, (select @r:=@r+1 As rowNum, T1.seqno as t_seqno from RB_OCUS_POS_TEST2 T1, (Select @r:=0) r order by regtime asc) T2 where T2.rowNum - T2.t_seqno <> 0 order by T2.t_seqno asc limit 100;
+--------+---------+------------------------+
| rowNum | t_seqno | T2.rowNum - T2.t_seqno |
+--------+---------+------------------------+
|     12 |      11 |                      1 |
|     11 |      12 |                     -1 |
|     32 |      31 |                      1 |
|     33 |      32 |                      1 |
|     40 |      33 |                      7 |
|     31 |      34 |                     -3 |
|     34 |      35 |                     -1 |
|     35 |      36 |                     -1 |
|     36 |      37 |                     -1 |
|     37 |      38 |                     -1 |
|     38 |      39 |                     -1 |
|     39 |      40 |                     -1 |
|     47 |      43 |                      4 |
|     43 |      44 |                     -1 |
|     44 |      45 |                     -1 |
|     45 |      46 |                     -1 |
|     46 |      47 |                     -1 |
|     60 |      53 |                      7 |

'데이타베이스 > MySQL' 카테고리의 다른 글

#LINUX,send_msg_to_queue.c (2번째) (0)	2019.10.30
#LINUX,recv_msg_from_queue.c (두번째) (0)	2019.10.30
1달전 자료 지우는 쿼리 (0)	2019.10.29
MySQL LOCK 확인 및 해제(kill) 방법 (0)	2019.10.29
#innodb_flush_log_at_trx_commit 에 따른 입력건수 성능비교 (0)	2019.10.29

//ORDR_DT = YYYYMMDD

DELETE FROM OPS_ORDER WHERE ORDR_DT <= date_format(date_add(now(), INTERVAL -1 MONTH), '%Y%m%d');

'데이타베이스 > MySQL' 카테고리의 다른 글

#LINUX,send_msg_to_queue.c (2번째) (0)	2019.10.30
#LINUX,recv_msg_from_queue.c (두번째) (0)	2019.10.30
#Queue를 이용한 MySQL입력 성능향상에 관한 글(1) (0)	2019.10.30
MySQL LOCK 확인 및 해제(kill) 방법 (0)	2019.10.29
#innodb_flush_log_at_trx_commit 에 따른 입력건수 성능비교 (0)	2019.10.29

석수코딩교육