Printing Student Information with PL/SQL Block

Enable server output to view printed output before executing the block. The following code defines a custom record type for student data, a local printing procedure that acccepts the record as a parameter, assigns sample student data, and prints the result to the console:

SET SERVEROUTPUT ON;
DECLARE
  TYPE student_rec_type IS RECORD (
    student_id VARCHAR2(20),
    full_name VARCHAR2(20),
    gender VARCHAR2(10),
    place_of_origin VARCHAR2(50),
    academic_grade VARCHAR2(20),
    activity_grade VARCHAR2(20)
  );
  current_student student_rec_type;

  PROCEDURE print_student_details(p_student IN student_rec_type) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Student ID: ' || p_student.student_id);
    DBMS_OUTPUT.PUT_LINE('Name: ' || p_student.full_name);
    DBMS_OUTPUT.PUT_LINE('Gender: ' || p_student.gender);
    DBMS_OUTPUT.PUT_LINE('Place of Origin: ' || p_student.place_of_origin);
    DBMS_OUTPUT.PUT_LINE('Academic Grade: ' || p_student.academic_grade);
    DBMS_OUTPUT.PUT_LINE('Activity Grade: ' || p_student.activity_grade);
  END print_student_details;
BEGIN
  current_student.student_id := '2020xxxx';
  current_student.full_name := 'xxx';
  current_student.gender := 'Male';
  current_student.place_of_origin := 'Harbin, Heilongjiang Province';
  current_student.academic_grade := 'Excellent';
  current_student.activity_grade := 'Good';

  print_student_details(current_student);
END;
/

Automatic Book Statistics with DML Trigger

This example creates a trigger that automatically updates aggregate statistics whanever the book information table is modified by any DML operation.

Create bookinfo table

Create the table in the scott schema if it does not already exist:

CREATE TABLE scott.bookinfo (
  bookno VARCHAR2(36) PRIMARY KEY,
  bookname VARCHAR2(40) NOT NULL,
  authorname VARCHAR2(10) NOT NULL,
  publishtime DATE,
  bookprice FLOAT
);

Create statistics table

Create the major_stats table to store total book count and distinct author count:

CREATE TABLE scott.major_stats (
  total_books INTEGER,
  total_distinct_authors INTEGER
);

Create the trigger

Create an after-trigger that fires for all insert, delete, and update operations to refresh the statistics:

CREATE OR REPLACE TRIGGER UpdateMajorStats
AFTER INSERT OR DELETE OR UPDATE ON scott.bookinfo
BEGIN
  DELETE FROM major_stats;
  INSERT INTO major_stats (total_books, total_distinct_authors)
  SELECT COUNT(bookno), COUNT(DISTINCT authorname)
  FROM scott.bookinfo;
END;
/

Test the trigger

Validate the trigger behavior with these steps:

1. Query major_stats after creation to confirm it starts empty
2. Insert one or more test records in to bookinfo, then query major_stats to see updated counts
3. Delete an existing entry from bookinfo and recheck the statistics table to confirm the count is updated
4. Update the author name for an existing book entry to see the distinct author count adjust correctly