6 : Advanced SQL

#sql iterator deptInfoIter(String dept_name, int avgSal); deptInfoIter iter = null; #sql iter = { select dept_name, avg(salary) from instructor group by dept name }; while (iter.next()) { String deptName = iter.dept_name(); int avgSal = iter.avgSal(); System.out.println(deptName + " " + avgSal); } iter.close();

int ODBCexample() { RETCODE error; HENV env; /* environment */ HDBC conn; /* database connection */ SQLAllocEnv(&env); //Environment 변수 공간 할당(안에 malloc 등등..) SQLAllocConnect(env, &conn); //연결관련 공간 할당 SQLConnect(conn, “localhost", SQL_NTS, “bnam", SQL_NTS, “changethis", SQL_NTS); // 연결 { …. Do actual work … } SQLDisconnect(conn);//접속종료 SQLFreeConnect(conn);//메모리 프리 SQLFreeEnv(env);//환경변수 프리 }

char deptname[80]; float salary; int lenOut1, lenOut2; HSTMT stmt; char * sqlquery = "select dept_name, sum (salary) from instructor group by dept_name"; SQLAllocStmt(conn, &stmt); error = SQLExecDirect(stmt, sqlquery, SQL_NTS); // if (error == SQL SUCCESS) { SQLBindCol(stmt, 1, SQL_C_CHAR, deptname, 80, &lenOut1); SQLBindCol(stmt, 2, SQL_C_FLOAT, &salary, 0 , &lenOut2); while (SQLFetch(stmt) == SQL_SUCCESS) { printf (" %s %g\n", deptname, salary); } } SQLFreeStmt(stmt, SQL_DROP);

SQLSetConnectOption(conn, SQL_AUTOCOMMIT,0)

SQLTransact(conn, SQL_COMMIT) or SQLTransact(conn, SQL_ROLLBACK)

MYSQL *conn = mysql_init(NULL);//SQL 초기화 mysql_real_connect(conn, "localhost", “id", "changethis", “db_name", 0, NULL, 0); char * sqlquery = "select dept_name, avg(salary)\ from instructor group by dept_name "; mysql_query(conn, sqlquery); MYSQL_RES *result = mysql_store_result(conn); int num_fields = mysql_num_fields(result); MYSQL_ROW row; while ((row = mysql_fetch_row(result))) { for(int i=0; i<num_fields; i++){ printf (" %s ", row[i]? row[i] : "NULL"); } printf("\n"); } mysql_free_result(result); mysql_close(conn);

void main () { EXEC SQL BEGIN DECLARE SECTION; int credit_amount; EXEC SQL END DECLARE SECTION; … EXEC SQL declare c cursor for select ID, name from student where tot_cred > :credit_amount END_EXEC … }

DELIMITER // create function dept_count (d_name varchar(20)) returns integer begin declare d_count integer; select count (* ) into d_count from instructor where instructor.dept_name = d_name; return d_count; end // DELIMITER ;

create function instructors_of (dept_name char(20)) returns table ( ID varchar(5), name varchar(20), dept_name varchar(20), salary numeric(8,2)) return table (select ID, name, dept_name, salary from instructor where instructor.dept_name = instructors_of.dept_name)

function <function-name> (param_spec1, …, param_speck) returns <return_type> [not] deterministic allow optimization if same output for the same input begin -- execution code end;

DELIMITER // create procedure instructors_of (dept_name char(20)) begin select ID, name, dept_name, salary from instructor where instructor.dept_name = dept_name; end // DELIMITER ;

Create Procedure <proc-name> (param_spec1, param_spec2, …, param_specn) begin -- execution code end;

IF <condition> then <statements> ELSEIF <condition> then <statements> ELSE <statements> END IF

DELIMITER// create trigger trigger name (before, after) (insert on 등등) for each row begin if new.dept_name is not null then update department set department.budget = department.budget + new.salary where department.dept_name = new.dept_name; END// delimiter;