/* 高级操作*/
/*高级操作中，有主要涉及了JAVA在数据库中的使用，高级聚集函数，数据库中的函数和过程、trigger等等概念*/

/*1. 动态（JDBC AND ODBC）和嵌入式SQL*/
/* JDBC is a Java API for communicating with database systems supporting SQL.

   Model for communicating with the database:
   Open a connection
   Create a “statement” object
   Execute queries using the Statement object to send queries and fetch results
   Exception mechanism to handle errors*/
/*使用JAVA对数据库进行连接*/
public static void JDBCexample (String dbid , String userid , String passwd )
{
try (Connection conn = DriverManager.getConnection (
"jdbc:oracle:thin@db.yale.edu :2000:univdb", userid , passwd );
Statement stmt = conn.createStatement
)
{
   … Do Actual Work ….
   }
catch ( SQLException sqle ) {
System.out.println ("SQLException : " + sqle);
   }
}
/*具体的一些操作可以看我的选课系统*/


/*2. function与 Procedures*/
/*Compound statement: begin … end
returns indicates the variable type that is returned (e.g., integer)
return specifies the values that are to be returned as result of invoking the function
*/

/*Procedures can be invoked either from an SQL procedure or from embedded SQL, using the call
statement.
*/

/*3. 嵌入式EXEC SQL fetch, close等等，可以自行了解*/

/*4. Trriger 创建一个trigger， P183*/
/*A trigger is a statement that is executed automatically by the system as a side effect of a
modification to the database.*/
create trigger timeslot_check1 after insert on section
referencing new row as nrow /*old and new 的使用*/
for each row /*for each statement 为每一个受影响的语句执行*/
when (nrow.time_slot_id not in(
select time_slot_id
from time_slot )) /* time_slot_id not present in time_slot*/
begin
rollback
end

/*5. While and repeat statements :*/
declare n integer default 0;
while n < 10 do
set n = n + 1
end while
/* repeat 递归*/
repeat
set n = n 1
until n = 0
end repeat

/*6. rank(), over, partition by, limit子句, */
select ID , rank () over (order by GPA desc) as s_rank
from student_grades
order by s_rank

/*“Find the rank of students within each*/
select ID , dept_name
rank () over (partition by dept_name order by GPA desc )/*null first , null last 也可在order by...desc后边用*/
as dept_rank
from dept_grades
order by dept_name , dept_rank;

/*7. 其他的rank函数*/
percent_rank (within partition, if partitioning is done)
cume_dist (cumulative distribution)
row_number (non deterministic in presence of duplicates)

/*补充*/
/*over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。*/


/*8. rollup, cube, */
/*The cube operation computes union of group by ’s on every subset of the specified attributes*/
select item_name, color, size, sum (number)
from sales
group by cube (item_name, color, size)

/*The rollup construct generates union on every prefix of specified list of attributes*/
select item_name , color , size , sum (number)
from sales
group by rollup (item_name, color, size)

/* 其他聚集函数
Pivoting: changing the dimensions used in a cross tab is called
Slicing:creating a cross tab for fixed values only. Sometimes called dicing , particularly when values for multiple dimensions are fixed.
Rollup: moving from finer granularity data to a coarser granularity
Drill down: The opposite operation that of moving from coarser granularity data to finer granularity data*/