FUNCTION 생성및 활용

 

#####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;​
}