Chapter 4: Intermediate SQL & Chapter 5: Advanced SQL¶

create type Dollars as numeric(12,2);  -- 定义美元类型为定点数
create table department(
    dept_name varchar(20),
    building varchar(15),
    budget Dollars  -- 直接使用自定义类型
);

create domain Dollars as numeric(12,2) not null;  -- 非空约束
create domain Pounds as numeric(12,2);
create table instructor(
    ID char(5) primary key,
    name varchar(20),
    dept_name varchar(20),
    salary Dollars,  -- 应用自定义域
    comm Pounds
);

CREATE TABLE instructor2(
    ID char(5) primary key,
    name varchar(20) not null,
    dept_name varchar(20),
    salary numeric(8,2) not null,
    check (salary >= 0)  -- 工资非负约束
);

create domain hourly-wage numeric(5,2)
    constraint value-test check(value >= 4.00);  -- 时薪不低于4.00

学生(学号,姓名,专业号) -- 参照关系，专业号为外键
专业(专业号,专业名称) -- 被参照关系，专业号为主键
instructor(dept_name) -- 参照关系
department(dept_name) -- 被参照关系

-- 方式1：表级定义，默认引用被参照表主键
create table instructor(
    ID char(5) primary key,
    dept_name varchar(20),
    foreign key (dept_name) references department
);

-- 方式2：列级简写，单列外键直接跟在属性后
create table instructor(
    ID char(5) primary key,
    dept_name varchar(20) references department
);

-- 方式3：显式指定被参照列（需是被参照表的主键/候选键，列名可不同）
create table instructor(
    ID char(5) primary key,
    dept_name varchar(20),
    foreign key (dept_name) references department (dept_name)
);

create table course(
    course_id varchar(8) primary key,
    dept_name varchar(20),
    foreign key (dept_name) references department
        on delete cascade  -- 删除院系时，级联删除该院系的所有课程
        on update cascade  -- 更新院系名时，级联更新课程的院系名
);

CREATE ASSERTION <断言名> CHECK <谓词>;

create assertion credits_earned_constraint check(
    not exists(
        select ID from student
        where tot_cred <> (
            select sum(credits)
            from takes natural join course
            where student.ID = takes.ID
                and grade is not null and grade <> 'F'
        )
    )
);

create assertion instructor_classroom_constraint check(
    not exists(
        select ID, semester, year, time_slot_id
        from instructor natural join teaches natural join section
        group by ID, semester, year, time_slot_id
        having count(distinct building, room_number) > 1
    )
);

create trigger <触发器名> <触发时机> <触发事件> on <表名>
    referencing <旧元组/新元组> as <别名>
    for each row/statement  -- 行级/语句级触发器
    when <触发条件>
begin
    <触发动作>  -- 如rollback/insert/update
end;

create trigger timeslot_check1 after insert on section
    referencing new row as nrow
    for each row
    when (nrow.time_slot_id not in (select time_slot_id from time_slot))
begin
    rollback;  -- 不存在则回滚插入操作
end;

create trigger timeslot_check2 after delete on time_slot
    referencing old row as orow
    for each row
    when (orow.time_slot_id not in (select time_slot_id from time_slot)
          and orow.time_slot_id in (select time_slot_id from section))
begin
    rollback;  -- 最后一个该时段被删除且被section引用，回滚
end;

-- 库存表/警戒线表/订货量表/订单表
create trigger reorder-trigger after update of level on inventory
    referencing old row as orow, new row as nrow
    for each row
    when (nrow.level <= (select level from minlevel where minlevel.item = nrow.item)
          and orow.level > (select level from minlevel where minlevel.item = orow.item))
begin
    insert into orders (select item, amount from reorder where reorder.item = orow.item);
end;

-- 定义视图，隐藏贷款敏感信息
create view cust-loan as
select branchname, customer-name
from borrower, loan
where borrower.loan-number = loan.loan-number;

-- 仅授予视图的SELECT权限，不授予基表权限
grant select on cust-loan to bank_clerk;

GRANT <权限列表> ON <表/视图名> TO <用户/角色/PUBLIC> [WITH GRANT OPTION];

-- 授予U1/U2/U3对instructor表的SELECT和INSERT权限
grant select, insert on instructor to U1, U2, U3;

-- 授予U1对department表的SELECT权限，且U1可转授该权限
grant select on department to U1 with grant option;

-- 1. 创建角色
create role instructor;
create role dean;

-- 2. 为角色授予权限
grant select on takes to instructor;

-- 3. 角色嵌套：将instructor角色的权限授予dean角色
grant instructor to dean;

-- 4. 将角色分配给用户
grant dean to Amit, Satoshi;

REVOKE <权限列表> ON <表/视图名> FROM <用户/角色/PUBLIC> [RESTRICT | CASCADE];

-- 级联回收U1/U3对department表的SELECT权限（包括他们转授的权限）
revoke select on department from U1, U3 cascade;

-- 限制回收U1对instructor表的INSERT权限，若U1转授过则失败
revoke insert on instructor from U1 restrict;

-- 回收所有用户对course表的DELETE权限
revoke delete on course from PUBLIC;

DBA → U1 → U4
DBA → U2 → U5
DBA → U3

AUDIT <语句类型> [BY <用户>] [BY SESSION/ACCESS] [WHENEVER SUCCESSFUL/NOT SUCCESSFUL];

-- 审计用户scott每次成功执行的表相关语句（create/drop/alter）
audit table by scott by access whenever successful;

AUDIT <操作类型> ON <对象名/DEFAULT> [BY SESSION/ACCESS] [WHENEVER SUCCESSFUL/NOT SUCCESSFUL];

-- 审计所有用户对student表的delete和update操作（无论成功与否）
audit delete, update on student;

-- 取消对scott的表语句审计
noaudit table by scott;

-- 取消对student表的delete和update审计
noaudit delete, update on student;

create table Person(
    id char(10) primary key,
    name varchar(12) not null,
    age int,
    gender char(1),
    spouse char(10),
    foreign key(spouse) references Person
        on update cascade
        on delete set NULL,
    check(gender in ('f', 'm'))
);

delete from Person where name='Wang';  -- Wang的spouse为Ling
select count(spouse) from Person;

EXEC SQL BEGIN DECLARE SECTION;
char V_an[20], bn[20];  // 宿主变量：账号、支行名
float bal;               // 宿主变量：余额
EXEC SQL END DECLARE SECTION;

scanf("%s", V_an);  // 宿主语言读入账号
// 嵌入式SQL：根据账号查询支行名和余额，赋值给宿主变量
EXEC SQL SELECT branch_name, balance INTO :bn, :bal 
FROM account WHERE account_number = :V_an;
END_EXEC

printf("账号：%s，支行：%s，余额：%f", V_an, bn, bal);  // 宿主语言处理数据

EXEC SQL BEGIN DECLARE SECTION;
char cn[50], ccity[50];  // 宿主变量：客户名、城市
float v_amount = 1000;   // 宿主变量：指定余额
EXEC SQL END DECLARE SECTION;

// 步骤1：声明游标，关联查询语句
EXEC SQL DECLARE c CURSOR FOR
SELECT customer_name, customer_city
FROM depositor D, customer B, account A
WHERE D.customer_name = B.customer_name
  and D.account_number = A.account_number
  and A.balance > :v_amount;
END_EXEC

// 步骤2：打开游标，执行查询
EXEC SQL OPEN c END_EXEC;

// 步骤3：循环获取数据，直到结果集结束
while(1) {
    EXEC SQL FETCH c INTO :cn, :ccity END_EXEC;
    // SQLSTATE='02000'表示无更多数据
    if (strcmp(SQLSTATE, "02000") == 0) break;
    printf("客户名：%s，城市：%s\n", cn, ccity);
}

// 步骤4：关闭游标，释放资源
EXEC SQL CLOSE c END_EXEC;

EXEC SQL BEGIN DECLARE SECTION;
char an[20];
float bal;
EXEC SQL END DECLARE SECTION;

scanf("%s,%f", an, &bal);  // 读入账号和增加的余额
// 嵌入式SQL：更新账号余额
EXEC SQL UPDATE account SET balance = balance + :bal
WHERE account_number = :an;
END_EXEC

// 声明游标，指定可更新balance属性
EXEC SQL DECLARE csr CURSOR FOR
SELECT * FROM account WHERE branch_name = 'Perryridge'
FOR UPDATE OF balance;
END_EXEC

EXEC SQL OPEN csr;
while(1) {
    EXEC SQL FETCH csr INTO :an, :bn, :bal;
    if (sqlca.sqlcode != SUCCESS) break;
    // 定位更新：更新游标当前指向的行的余额
    EXEC SQL UPDATE account SET balance = balance + 100
    WHERE CURRENT OF csr;
    // 定位删除：删除游标当前指向的行
    // EXEC SQL DELETE FROM account WHERE CURRENT OF csr;
}
EXEC SQL CLOSE csr;

Pro*C源程序（.pc）→ 预编译器（pcc）→ C源程序（.c）→ C编译器（cc）→ 可执行文件（.exe）

EXEC SQL PREPARE <动态语句名> FROM :<SQL字符串变量>;  -- 预编译动态SQL
EXEC SQL EXECUTE <动态语句名> [USING :<宿主变量列表>];  -- 执行动态SQL，传递参数

char *sqlprog = "update account set balance = balance * 1.05 where account_number = ?";
char v_account[10] = "A_101";

// 预编译动态SQL语句
EXEC SQL PREPARE dynprog FROM :sqlprog;
// 执行动态SQL，将v_account赋值给占位符?
EXEC SQL EXECUTE dynprog USING :v_account;

// 1. 分配环境句柄
HENV henv;
SQLAllocEnv(&henv);

// 2. 分配连接句柄
HDBC hdbc;
SQLAllocConnect(henv, &hdbc);

// 3. 连接数据源（DSN：数据源名，用户/密码）
SQLConnect(hdbc, "MySQLServer", SQL_NTS, "user", SQL_NTS, "password", SQL_NTS);

// 4. 分配语句句柄
HSTMT hstmt;
SQLAllocStmt(hdbc, &hstmt);

// 5. 执行SQL语句
// 5.1 直接执行（适合简单语句）
char *sql = "select dept_name, sum(salary) from instructor group by dept_name";
SQLExecDirect(hstmt, sql, SQL_NTS);

// 5.2 预编译执行（适合重复执行的语句，带占位符）
// SQLPrepare(hstmt, sql, SQL_NTS);
// SQLExecute(hstmt);

// 6. 绑定结果集到宿主变量（将查询结果赋值给C变量）
char deptname[80];
float salary;
SQLBindCol(hstmt, 1, SQL_C_CHAR, deptname, 80, NULL);
SQLBindCol(hstmt, 2, SQL_C_FLOAT, &salary, 0, NULL);

// 7. 遍历结果集
while (SQLFetch(hstmt) == SQL_SUCCESS) {
    printf("院系：%s，总工资：%f\n", deptname, salary);
}

// 8. 释放资源
SQLFreeStmt(hstmt, SQL_DROP);  // 释放语句句柄
SQLDisconnect(hdbc);           // 断开连接
SQLFreeConnect(hdbc);          // 释放连接句柄
SQLFreeEnv(henv);              // 释放环境句柄

import java.sql.*;

public class JDBCExample {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;

        try {
            // 1. 加载数据库驱动（Oracle驱动）
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // 2. 建立数据库连接（URL：jdbc:数据库类型://地址:端口/数据库名，用户/密码）
            conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@aura.bell_labs.com:2000:universitydb",
                "userid",
                "passwd"
            );

            // 3. 创建Statement对象，执行SQL语句
            stmt = conn.createStatement();

            // 4. 执行查询，获取结果集
            String sql = "select dept_name, avg(salary) from instructor group by dept_name";
            rset = stmt.executeQuery(sql);

            // 5. 遍历结果集
            while (rset.next()) {
                // 通过列名/列索引获取数据
                String deptName = rset.getString("dept_name");
                float avgSalary = rset.getFloat(2);
                System.out.println(deptName + "  " + avgSalary);
            }

            // 执行更新操作（插入/删除/更新）
            // stmt.executeUpdate("insert into instructor values('76766', 'Crick', 'Biology', 72000)");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();  // 驱动加载失败
        } catch (SQLException e) {
            e.printStackTrace();  // SQL执行失败
        } finally {
            // 6. 释放资源（逆序释放）
            try { if (rset != null) rset.close(); } catch (SQLException e) {}
            try { if (stmt != null) stmt.close(); } catch (SQLException e) {}
            try { if (conn != null) conn.close(); } catch (SQLException e) {}
        }
    }
}

// 预编译SQL语句，带4个占位符
String sql = "insert into instructor values(?, ?, ?, ?)";
PreparedStatement pStmt = conn.prepareStatement(sql);

// 为占位符赋值（索引从1开始）
pStmt.setString(1, "76766");
pStmt.setString(2, "Crick");
pStmt.setString(3, "Biology");
pStmt.setFloat(4, 72000);

// 执行更新
pStmt.executeUpdate();

// 重新赋值，执行第二次
pStmt.setString(1, "76767");
pStmt.executeUpdate();

int totCred = rset.getInt("tot_cred");
if (rset.wasNull()) {
    System.out.println("总学分为NULL");
}