数据库PPT课后习题 - 第六章

6.1 前置知识

PL/pgSQL 基本语法

1. 声明变量

DO $$
DECLARE
	Userid	 	INTEGER		:=1;
	Firstname	VARCHAR(20)	:='Tom';
	Payment		NUMERIC(6,2):=120.6;
BEGIN
	IF userid = 1 THEN
	RAISE NOTICE '% %  has been paid % USD',
		  Userid,Firstname,Payment;
	END IF;
END $$;

• 有关 %ROWTYPE：

  1. CREATE TABLE employees (
         id SERIAL PRIMARY KEY,
         first_name VARCHAR(50),
         last_name VARCHAR(50),
         email VARCHAR(100),
         salary NUMERIC(10, 2)
     );
     

  2. DO $$
     DECLARE
         employee_record employees%ROWTYPE;  -- 这里的employee_record 可以看作结构体
     BEGIN
         -- 将某个员工的数据赋值给 employee_record
         SELECT * INTO employee_record
         FROM employees
         WHERE id = 1;
         -- 输出该员工的数据
         RAISE NOTICE 'Employee: % % % %', 
             employee_record.id,
             employee_record.first_name,
             employee_record.last_name,
             employee_record.email,
             employee_record.salary;
     END $$;
     

• 有关 RECORD：

  DO $$
  DECLARE
      rec RECORD;
  BEGIN
      -- 执行任意查询，将结果存入 rec 变量
      FOR rec IN SELECT id, first_name, last_name, email FROM employees LOOP
          -- 输出结果
          RAISE NOTICE 'Employee: % % % %', 
              rec.id,
              rec.first_name,
              rec.last_name,
              rec.email;
      END LOOP;
  END $$;
  -- 与 %ROWTYPE 不同，RECORD 类型的变量在声明时不绑定到特定的表或视图。
  

• 有关 %TYPE

  DO $$
  DECLARE
      v_first_name employees.first_name%TYPE; -- 当表结构改变时（例如字段的数据类型改变），
      										-- 使用 %TYPE 的变量会自动匹配新类型
      										-- 还能避免输错类型、提高可读性
      v_salary employees.salary%TYPE;
  BEGIN
      -- 赋值给变量
      v_first_name := 'John';
      v_salary := 50000.00;
  
      -- 输出变量值
      RAISE NOTICE 'First Name: %', v_first_name;
      RAISE NOTICE 'Salary: %', v_salary;
  END $$;
  

2. 条件语句

IF ... THEN
	...
END IF;
------------
IF ... THEN
	...
ELSE
	...
END IF;
------------
IF ... THEN
	...
ELSIF ... THEN
	...
ELSE
	...
END IF;
------------
CASE
	WHEN ... THEN
		...
	WHEN ... THEN
		...
	ELSE
		...
END CASE;

IF age<18 THEN
	RETURN '未成年';
ELSIF age<60 THEN
	RETURN '成年';
ELSE RETURN '老年';
END IF;
-------------
CASE
	WHEN score >= 90 THEN
		grade := 'A';
	WHEN score >= 80 THEN
		grade := 'B';
	ELSE
		grade := 'C';
END CASE;

3. 循环语句

-- LOOP 基本循环, 用于构造死循环, label是用于标记循环的标签，可有可无
LOOP
	...
END LOOP [label];

DO $$
DECLARE
    counter INTEGER := 0;
BEGIN
    outer_loop: LOOP
        counter := counter + 1;
        RAISE NOTICE 'Counter: %', counter;
        IF counter >= 5 THEN
            EXIT outer_loop;
        END IF;
    END LOOP outer_loop;
END $$;

-- EXIT 循环退出
LOOP
	count := count+1;
	EXIT WHEN count>100;
END LOOP;

-- CONTINUE 循环继续
DO $$
DECLARE
    counter INTEGER := 0;
BEGIN
    LOOP
        counter := counter + 1;
        IF counter % 2 = 0 THEN
            CONTINUE;  -- 如果是偶数提前结束本次循环
        END IF;
        RAISE NOTICE 'Counter: %', counter;
        IF counter >= 10 THEN
            EXIT;
        END IF;
    END LOOP;
END $$;

-- WHILE
WHILE counter <= 5 LOOP
	RAISE NOTICE 'Counter: %', counter;
	counter := counter + 1;
END LOOP;

-- FOR
-- FOR name IN [REVERSE] expressions..
DO $$
DECLARE
    emp RECORD;
BEGIN
    FOR emp IN EXECUTE 'SELECT id, first_name, last_name FROM employees WHERE salary > 50000' LOOP
        -- 打印当前记录的 id 和姓名; 默认正序, 加上REVERSE 就反序
        -- EXECUTE 用于执行一个动态 SQL 查询，查询语句根据条件 "salary > 50000" 动态生成
        RAISE NOTICE 'ID: %, Name: % %', emp.id, emp.first_name, emp.last_name;
    END LOOP;
END $$;

4. 函数

1. 创建一个名为 countRecords() 的程序函数，实现STUDENT表中人数统计。

   CREATE OR REPLACE FUNCTION countRecords()
   RETURN integer AS $$
   DECLARE
   	count integer;
   BEGIN
   	SELECT count(*) into count FROM STUDENT;
   	RETURN count;
   END;
   $$ LANGUAGE plpgsql;
   

2. 定义带输入参数"销售价格"的函数,返回折扣价格。

   CREATE FUNCTION discount(salePrice real) RETURNS real AS $$
   -- real 是实数类型, 注意不要和 C 的语法搞混了, 然后函数的返回值也是 real类型
   -- RETURNS 用于声明函数的返回类型
   BEGIN
   	RETURN salePrice*0.85;
   END;
   $$ LANGUAGE plpgsql;
   

3. -- 如果想要在函数 or 存储过程中调用数据库函数, 格式：
   SELECT INTO 自定义变量 from 函数名(参数)
   -- 例子:
   CREATE OR REPLACE FUNCTION testExec() RETURNS integer AS $$
   DECLARE
   	rec integer;
   BEGIN
   	SELECT INTO rec countRecords();
   	return rec;
   END;
   $$ LANGUAGE plpgsql;
   

5. 游标

1. DECLARE
   	curs refcursor; -- 定义 非绑定游标
   	curStudent CURSOR FOR SELECT * FROM student; -- 不带参数的游标定义
   	curStudentOne CURSOR(key interger) IS
   		SELECT * FROM student WHERE SID =key;    -- 带参数的游标定义(但是好像没有这种方式，游标一般是静态定义的)
   

2. OPEN curVars1 FOR SELECT * FROM student WHERE SID = mykey;
   OPEN unbounder_cursor FOR EXECUTE 'SELECT * FROM' || quote_ident($1); -- $1指由存储过程传递的第一个参数
   
   OPEN curStudent;
   OPEN curStudentOne('20160230302001');
   

3. FETCH curStudent INTO SID,Sname,Sgender;
   -- FETCH 语句从 curStudent 游标中提取下一行数据，并将这三列的值分别赋给相应的变量 SID、Sname 和 Sgender。
   

   FETCH 语句只能在游标已经打开（即使用 OPEN 语句）且游标指向一个有效的结果集时才能使用。FETCH 语句每次执行都会从结果集中提取一行数据，并将游标位置移动到下一行。如果游标已经到达结果集的末尾，那么 FETCH 语句将不再提取数据，并且游标将保持在最后一行。

4. CLOSE curStudent;
   

• 在函数中应用游标：

  1. 在函数中使用游标查询student表的学号、学生姓名和性别。

     CREATE OR REPLACE FUNCTION cursorDemo()
     returns boolean AS $$
     DECLARE
     	unbound_refcursor refcursor;
     	vsid 	 varchar;
     	vsname 	 varchar;
     	vsgender varchar;
     BEGIN
     	OPEN unbound_refcursor FOR EXECUTE 'SELECT studentID, studentName, studentGender FROM Student';
     	LOOP
     		FETCH unbound_refcursor INTO vsid, vsname, vsgender;
     		IF FOUND THEN -- 如果从游标中提取到数据
     			RAISE NOTICE '% % %',vsid, vsname, vsgender;
     		ELSE
     			exit;
     		END IF;
     	END LOOP
     	CLOSE unbound_refcursor;
     	RAISE NOTICE '取数据循环结束...';
     
     	EXCEPTION WHEN others THEN  -- 表示当捕获到除已明确定义的异常之外的所有其他异常时执行以下代码块
     		RAISE EXCEPTION 'error-(%)',sqlerrm; -- sqlerrm 错误代码变量
     		RETURN FALSE;
     	RETURN TURE;
     END;
     $$ LANGUAGE plpgsql
     

  2. 编写带参数的游标函数,从成绩表中查询分数大于某给定定值的学号和课程号。

     CREATE OR REPLACE FUNCTION cursorGrade(myscore int) returns void AS $$ -- 注: plsql中int和integer等价
     DECLARE
     	vstuscore Grade%ROWTYPE;
     	vstucursor cursor(invalue int) FOR SELECT courseID,studentID,grade FROM Grade 
     								   WHERE grade>=invalue ORDER BY studentID;
     BEGIN
     	OPEN vstucursor(myscore);
     	LOOP
     		FETCH vstucursor INTO vstuscore;
     		EXIT WHEN NOT FOUND; -- 没有找到就退出
     		RAISE NOTICE '%,%,%',vstuscore.studentID, vstuscore.courseID, vstuscore.grade;
     	END LOOP;
     	CLOSE vstucursor;
     END;
     $$ LANGUAGE plpgsql;
     

6.1 习题

6.1.1 在什么情况下使用游标？

1. 当需要 逐行处理查询 结果集时。
   • FETCH 语句
2. 需要在 多个位置重复访问 结果集。
   • 可以在需要的位置重复执行 OPEN 语句
3. 需要在处理过程中 **保存查询状态 **。
   • 游标在处理过程中会保存当前的状态信息，例如当前位置、是否已经到达结果集末尾等。
4. 需要在结果集中进行 更新 操作。
   • 通过 FETCH 和 UPDATE 语句可以实现对查询结果集的逐行更新操作

6.1.2 在PL/pgSQL中，函数中使用游标分为哪几个步骤，分别使用什么语句？

1. 声明游标：使用 DECLARE 语句声明游标变量。
2. 打开游标：使用 OPEN 语句打开游标，并执行查询。
3. 逐行获取数据：使用 FETCH 语句逐行获取数据。
4. 处理数据：对获取到的数据进行处理。
5. 关闭游标：使用 CLOSE 语句关闭游标，释放资源。

6.2 前置知识

• 存储过程：数据库中类似函数的一种程序对象，它由一组完成特定数据处理功能的SQL语句和过程语句组成。
  • 与函数的区别是：存储过程没有返回类型声明（返回值 void）；外部程序可以调用数据库的存储过程执行。

例题

1. 创建一个名为add_data(a,b,c)的存储过程实现 a+b 相加运算，并将结果放入c。

   CREATE OR REPLACE PROCEDURE add_data(a int,b int,c int) AS $$
   BEGIN
   	c=a+b;
   END;
   $$ LANGUAGE plpgsql;
   

2. 创建一个series_sum(upper,sumVal)的存储过程计算序列累加值。

   CREATE OR REPLACE PROCEDURE series_sum(upper int,inout sumVal int) AS $$
   DECLARE
   	i int := 1;
   BEGIN
   	WHILE i<=upper LOOP
   		sumVal:=sumVal+i;
   		i:=i+1;
   	END LOOP;
   	EXCEPTION WHEN others THEN
   		RAISE EXCEPTION 'error-(%)',sqlerrm;
   END;
   $$ LANGUAGE plpgsql;
   

3. 存储过程调用

   -- 在DB中通过SQL语句执行
   CALL serial_sum(10,0);
   

4. DROP PORCEDURE series_sum();
   

• 优点：执行速度快、减少网络通信量、降低业务实现和应用程序的耦合、降低前端开发的复杂性、保护DB元数据、增强DB安全性
• 缺点：存储过程的参数改变时需要修改存储过程的代码和主程序调用的代码；开发调试困难；可移植性差

6.2 习题

6.2.1 在数据库应用编程中，哪些功能处理适合采用存储过程编程实现？

1. 复杂业务逻辑处理。
2. 数据处理和转换操作。
3. 定期任务执行。
4. 数据访问控制和权限验证。
5. 事务管理和数据一致性保证。
6. 数据库性能优化操作。

处理复杂业务逻辑、数据处理、定期任务和性能优化

6.2.2

针对一个成绩管理系统数据库,其中包括：

1. 学生表Student (StudentID, SName, Major, CompletedCredits)

2. 成绩表Grade (StudentID, CourseID, grade)

3. 课程表Course (CourseID, Cname,credit)。

采用PL/pgSQL语言，编写存储过程StudentCreditePro(SID)实现给定学号的学生总学分统计，其值保存在Student表中，并通过查询Student表获得该生总学分。

CREATE OR REPLACE PROCEDURE StudentCreditePro(SID char(13)) AS $$
DECLARE 
	sum_credit int :=0;
	vcredit Course.credit%TYPE;
	credit_cs refcursor;
BEGIN
	OPEN credit_cs FOR 
        SELECT C.credit
        FROM Course AS C
        JOIN Grade AS G ON C.CourseID = G.CourseID
        JOIN Student AS S ON G.StudentID = S.StudentID
        WHERE S.StudentID = SID;

	LOOP
		FETCH credit_cs INTO vcredit;
		EXIT WHEN NOT FOUND;
		sum_credit:=sum_credit+vcredit;
	END LOOP;
	CLOSE credit_cs;
	
	UPDATE Student -- 注意这里要用UPDATE
    SET CompletedCredits = sum_credit
    WHERE StudentID = SID;
END;
$$ LANGUAGE plpgsql;

6.3 触发器

• 触发器是特殊的存储过程，由事件（INSERT、UPDATE、DELETE等）触发而自动执行。

  • 可以实现比约束更复杂的数据完整性，用于加强数据的完整性约束和业务规则。

  • 特点：与DB对象相关，在 表 or 视图上执行 DML、DDL操作。

    1. DML 事件触发：INSERT、DELETE 、UPDATE
    2. DDL 事件触发：CREATE、ALTER 、DROP 、SELECT INTO

  • 分类：

    • 按执行次数：语句级（触发器作用的表上执行一条SQL语句时只执行一次，FOR EACH STATEMENT 为默认值）；
      行级（由 FOR , EACH , ROW 标记，当触发器所在表中数据发生变化时，每变一行触发一次）

      例：学生成绩表上定义了行级DELETE触发器。如果该表删除了20条记录,则将引发DELETE触发器程序被执行20次。

    • 按触发时间：BEFORE（触发事件之前触发）、AFTER（之后）、INSTEAD OF（执行触发器中指定的过程程
      序，而不是执行产生触发事件的SQL语句）

• 特殊变量

  • NEW：其数据类型是 RECORD。对于行级触发器，NEW中保存 INSERT 或 UPDATE 产生的新行记录数据；对于语句级触发器，NEW的值是 NULL。
  • OLD：其数据类型是 RECORD。对于行级触发器，NEW中保存 DELETE 或 UPDATE 产生的新行记录数据；对于语句级触发器，NEW的值是 NULL。
  • TG_OP：其数据类型是 TEXT。其值是 INSERT、UPDATE、DELETE 字符串之一。使用 TG_OP来获取触发器由哪类操作引发。

• 创建触发器

  • 步骤：

    1. 检查触发器所依附的表或视图是否存在，若不存在要先创建它。
    2. 创建触发器的执行过程（函数），该函数类型必须是 Trigger型，即触发器的执行函数。
    3. 创建触发器对象。包括：定义触发器依附的表、触发器被触发执行的时刻、触发器是行级触发器还是语句级触发器、触发器执行时需满足的条件。

  • 示例：

    -- 为了审计 Grade 表(courseID,studentID,grade)的课程成绩修改, 创建 audit_score 表记录 Grade 表的成绩变化
    CREATE TABLE Audit_score
    ( changeID serial	NOT NULL,
      userName varchar(20),
      sid      char(13),
      cid	   char(4),
      updatetime text,
      oldscore int,
      newscore int,
      CONSTRAINT changeID_PK PRIMARY KEY(changeID)
    );
    
    
    -- 创建触发器函数 score_audit()
    CREATE OR REPLACE FUNCTION score_audit returns TRIGGER AS $score_audit$
    BEGIN
    	IF (TG_OP='DELETE') THEN
    		INSERT INTO Audit_score(userName,sid,cid,updatetime,oldscore)
    		SELECT user,old.studentid,old.courseid,now(),old.grade;
    		RETURN OLD;
    	ELSIF (TG_OP='UPDATE') THEN
    		INSERT INTO Audit_score(username,sid,cid,updatetime,oldscore,newscore)
    		SELECT user,old.studentid,old.courseid,now(),old.grade,new.grade;
    		WHERE old.studentid=new.studentid and old.courseid=new.courseid;
    		RETURN NEW;
    	ELSIF (TG_OP='INSERT') THEN
    		INSERT INTO Audit_score(username,sid,cid,updatetime,oldscore,newscore)
    		SELECT user,new.studentid,new.courseid,now(),NULL,new.grade;
    		RETURN NEW;
    	END IF;
    	RETURN NULL;
    END;
    $score_audit$ LANGUAGE plpgsql;
    -- $name$ 是一种标记字符串的语法, 称为“定界符”
    -- 关键字是不区分大小写的，但标识符（例如表名、列名、函数名等）是区分大小写的
    

    在grade表上创建触发器 score_audit_trigger

    CREATE TRIGGER score_audit_trigger
    	AFTER INSERT OR UPDATE OR DELETE ON grade
    	FOR EACH ROW
    	EXECUTE PROCEDURE score_audit();
    -- 这个触发器会对每一行操作都调用 "score_audit()" 函数，这样就可以审计每一次对 "grade" 表的修改。
    

    验证触发器 score_audit_trigger 的作用：

• ALTER TRIGGER score_audit_trigger ON grade RENAME TO score_audit_trig;
  
  DROP TRIGGER IF EXISTS score_audit_trig ON grade CASCADE;
  -- 同时删除依赖触发器的对象
  

• 事件触发器：捕获数据库级别上的对象DDL事件，并执行触发器处理程序。

  • ddl_command_start：在DDL开始前执行

  • ddl_command_end：在DDL结束后执行

  • sql_drop：删除一个数据库对象前删除

  • 

  • 相关操作：

    CREATE EVENT TRIGGER ...
    ALTER EVENT TRIGGER ...
    DROP EVENT TRIGGER ...
    

6.3 习题

6.3.1 在数据库应用开发中，触发器主要应用于哪些场景？

1. 数据完整性约束： 确保数据满足特定的条件或约束。
2. 审计和日志记录： 记录数据库的变更，用于审计或跟踪数据变更历史。
3. 派生数据维护： 自动计算或更新相关的派生数据。
4. 数据复制和同步： 在数据库之间同步数据变更。
5. 业务规则实施： 实施业务规则和逻辑，执行相关的业务工作流程。
6. 安全性增强： 增强数据库的安全性，执行额外的安全检查或记录安全事件。
7. 数据转换和格式化： 在数据插入或更新时，自动对数据进行格式化或转换。

确保数据完整性、审计记录和执行业务规则。

6.3.2

​ 编写触发器程序实现如下业务功能操作的自动化处理。在雇员表employee中,每插入或更新一行数据记录时,触发函数程序将当前时间戳和当前用户名写入该记录,并且检查该雇员的姓名以及薪水数据是否为空。若为空,则输出相应提示信息。请输入样本数据,并验证触发器程序正确性。

CREATE TABLE employee (
 empID char(3) primary key,
 empName varchar(20),
 salary integer,
 last_date timestamp,
 last_user varchar(20)
);

-- 首先创建 Audit_emp 表来记录employee的变化
CREATE TABLE Audit_emp
(
    changeID serial NOT NULL,
    userName varchar(20),
    empName varchar(20),
    salary int,
    updatetime timestamp  -- 用text也行, 但是timestamp更好
);

-- 然后创建触发器函数 emp_audit()
CREATE OR REPLACE FUNCTION emp_audit() returns TRIGGER AS $emp_audit$
BEGIN
	IF(TG_OP='INSERT' OR TG_OP='UPDATE') THEN
		INSERT INTO Audit_emp(userName,empName,salary,updatetime)
        VALUES(user,NEW.empName,NEW.salary,now());
	END IF;
	IF NEW.empName IS NULL THEN
		RAISE EXCEPTION 'empName cannot be NULL';  -- 注意这里是EXCEPTION而不是NOTICE
	IF NEW.salary IS NULL THEN
		RAISE EXCEPTION 'Salary cannot be NULL!';
	END IF;
	RETURN NEW;   -- 注意要返回NEW或者OLD
END;
$$ LANGUAGE plpgsql;

-- 最后创建触发器emp_audit_trigger
CREATE TRIGGER emp_audit_trigger
	AFTER INSERT OR UPDATE ON employee
	FOR EACH ROW
	EXECUTE TRIGGER emp_audit();

6.4 前置知识

//建立连接
String URL="jdbc:postgresql://localhost:5432/testDB";
String userName="myuser";
String passWord="123456";
connection conn =
    DriverManager.getConnection(URL,userName,passWord);

//创建Statement对象
Statement stmt=conn.createStatement();

//执行SQL语句: 在创建对象后, 可以调用它的executeQuery(), executeUpdate() 方法
String sql = "INSERT INTO public.student(sid,sname,gender,birthday,major,phone)"
    		+"VALUES('20200126','坤坤','男','1998-08-02','唱跳篮球','13602810001')";
stmt.executeUpdate(sql);

//ResultSet 结果集
rs = stmt.executeQuery(sql);
while(rs.next()){
    String name = rs.getString("name"); // 取出name列的内容
    int age = rs.getInt("age");			// 取出age 列的内容
}

//关闭连接
rs.close();	   //关闭结果集对象
stmt.close();  //关闭执行对象
conn.close();  //关闭连接对象

• 在 JAVA、C/C++ 中嵌入SQL操作语句的高级语言称为 宿主语言，而 SQL 语句在应用程序中称为 嵌入式SQL（ESQL）

  • 在C语言中，采用"EXEC SQL"作为前缀，以"END EXEC"作为语句结束的结束标志。

    // 了解即可
    #include <stdio.h>
    #include <stdlib.h>
    #include <sqlca.h> // SQLCA 头文件包含了用于处理 SQLCA 结构的定义
    
    int main() {
        EXEC SQL BEGIN DECLARE SECTION; // 声明 SQL 语句的变量部分开始
            int employee_id;
            char employee_name[50];
        EXEC SQL END DECLARE SECTION; // 声明 SQL 语句的变量部分结束
    
        // 这里是 C 代码区域
    
        EXEC SQL SELECT emp_name INTO :employee_name FROM employees WHERE emp_id = :employee_id;
        if (sqlca.sqlcode != 0) {
            printf("Error fetching employee name\n");
            exit(1);
        }
    
        printf("Employee name: %s\n", employee_name);
    
        return 0;
    }
    

  • 由于 SQL 语句处理的是记录集合，而高级语言语句一次只能处理一条记录，因此需要使用 游标 把集合操作转换为单记录处理方式。

    EXEC SQL BEGIN DECLARE SECTION;
        // 在这里声明变量，如果需要的话
        int employee_id;
        char employee_name[50];
    EXEC SQL END DECLARE SECTION;
    
    EXEC SQL DECLARE emp_cursor CURSOR FOR
        SELECT emp_name INTO :employee_name FROM employees WHERE emp_id = :employee_id;
    END EXEC;
    

• JAVA 嵌入式SQL编程

  package testConnDB;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  
  public class SQLinJava{
      public static void main(String[] args){
          Connection conn=NULL;
          String URL = "jdbc:postgresql://localhost:5432/testDB";
          String userName = "myuser";
          String passWord = "123456";
          String sid[] = {"14102","14103","14202","14301","14101","14201","14503"};
          String cid[] = {"1205","1208","1205","1208","1201","1201","1201"};
          int score[] = {90,78,89,68,86,96,83};
          try{
              Class.forName("org.postgresql.Driver");
              conn = DriverManager.getConnection(URL,userName,passWord);
              System.out.println("成功连接数据库");
              String insertSql = "INSERT INTO stu_score(sid,cid,score) VALUES(?,?,?)";
              String querySql = "SELECT sid,cid,score FROM stu_score where score>=?";
              // 定义动态执行SQL语句对象
              PreparedStatement psInsert = conn.preparedStatement(insertSql);
              PreparedStatement psQuery = conn.preparedStatement(querySql);
              
              for(int i=0;i<sid.length;i++){
                  psInsert.setString(1,sid[i]);
                  psInsert.setString(2,cid[i]);
                  psInsert.setInt(3,score[i]);
                  psInsert.addBatch(); // 添加批处理的记录
  			}
              psInsert.executeBatch(); // 批处理执行多条数据记录
              
              psQuery.setInt(1,80);
              ResultSet rs = psQuery.executeQuery();
              while(rs.next()){ // 判断是否还有下一个数据
                  System.out.println(rs.getString("sid")+" "+rs.getString("cid")+" "+rs.getInt("score"));
  			}
              psQuery.close();
              psInsert.close();
              conn.close();
          } catch(Exception e){
              System.err.println(e.getClass().getName()+" "+e.getMessage());
              System.exit(0);
  		}
  	}
  }
  

6.4 习题是C的，不写了

学堂在线补充

6.1.1 采用PL/pgSQL语言编写累加求和函数sum_series(upper integer)

CREATE OR REPLACE FUNCTION sum_series(upper INTEGER)
RETURNS INTEGER AS $$
DECLARE
    sum INTEGER := 0;
    i INTEGER;
BEGIN
    FOR i IN 1..upper LOOP
        sum := sum + i;
    END LOOP;
    RETURN sum;
END;
$$ LANGUAGE plpgsql;

6.2 针对雇员表Employees(empID,empName,salary),编写存储过程raise_salary( )，实现雇员涨工资10%，并输出雇员编号、姓名、涨前工资和涨后工资的信息列表输出。

CREATE TABLE Employees (
    empID SERIAL PRIMARY KEY,
    empName VARCHAR(50),
    salary NUMERIC
);

CREATE OR REPLACE PROCEDURE raise_salary()
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT empID, empName, salary FROM Employees
    LOOP
        -- 输出雇员编号、姓名、涨前工资
        RAISE NOTICE 'Employee ID: %, Name: %, Old Salary: %', rec.empID, rec.empName, rec.salary;

        -- 更新工资，增加 10%
        UPDATE Employees
        SET salary = salary * 1.10
        WHERE empID = rec.empID;

        -- 输出涨后工资
        RAISE NOTICE 'New Salary: %', rec.salary * 1.10;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

6.4 基于Java JDBC接口，对雇员表Employee（ID,name,agender,department,phone）进行数据库查询访问编程，实现雇员信息列表输出。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class EmployeeInfo {
    public static void main(String[] args) {
        String URL = "jdbc:postgresql://localhost:5432/testDB";
        String userName = "myUser";
        String passWord = "123456";

        String query = "SELECT ID, name, agender, department, phone FROM Employee";

        try (Connection conn = DriverManager.getConnection(URL, userName, passWord)) {
            Class.forName("org.postgresql.Driver");
            System.out.println("Successfully connected to the database");

            try (PreparedStatement psQuery = conn.prepareStatement(query);
                 ResultSet rs = psQuery.executeQuery()) {
                 while (rs.next()) {
                     int id = rs.getInt("ID");
                     String name = rs.getString("name");
                     String agender = rs.getString("agender");
                     String department = rs.getString("department");
                     String phone = rs.getString("phone");

                     System.out.println("ID: " + id + ", Name: " + name + ", Gender: " + agender + 
                                       ", Department: " + department + ", Phone: " + phone);
                }
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}