清华大学数据库课件Lesson5-高级SQL语言Advanced SQL.ppt

ODBC Prepared Statements Prepared Statement SQL statement prepared: compiled at the database Can have placeholders: E.g. insert into account values(?,?,?) Repeatedly executed with actual values for the placeholders To prepare a statement SQLPrepare(stmt, SQL String); To bind parameters  SQLBindParameter(stmt, parameter#,  … type information and value omitted for simplicity..) To execute the statement retcode = SQLExecute( stmt); To avoid SQL injection security risk, do not create SQL strings directly using user input; instead use prepared statements to bind user inputs ADO.NET API designed for Visual Basic .NET and C#, providing database access facilities similar to JDBC/ODBC Partial example of ADO.NET code in C#using System, System.Data, System.Data.SqlClient; SqlConnection conn = new SqlConnection( “Data Source=IPaddr, Initial Catalog=Catalog”);conn.Open();SqlCommand cmd = new SqlCommand(“select * from students”,  conn);SqlDataReader rdr = cmd.ExecuteReader();while(rdr.Read()) { Console.WriteLine(rdr[0], rdr[1]); /* Prints result attributes 1 2 */}rdr.Close(); conn.Close(); Can also access non-relational data sources such as OLE-DB, XML data, Entity framework Procedural Constructs in SQL Triggers Trigger to Maintain credits_earned value create trigger credits_earned after update of takes on (grade)referencing new row as nrowreferencing old row as orowfor each rowwhen nrow.grade ’F’ and nrow.grade is not null and (orow.grade = ’F’ or orow.grade is null)begin atomic update student set tot_cred= tot_cred +  (select credits from course where course.course_id= nrow.course_id) where student.id = nrow.id;end; Recursive Queries Advanced Aggregation Features Ranking Ranking can be done using basic SQL aggregation, but resultant query is very inefficient select ID, (1 + (select count(*)