Oracle SQL Plus自定义过程读写CSV文件——数据库课程作业求助

Alright, let's walk through how to build these custom PL/SQL procedures for your Oracle database assignment—since you can't use dedicated CSV tools, we'll handle file I/O with UTL_FILE and manually parse/generate CSV content. We'll cover both exporting the Students table to a CSV and importing data from a CSV back into the table.

First, a quick prerequisite: you'll need a database directory object to let Oracle access your filesystem. Ask your DBA to run these commands (or do it yourself if you have the privileges):

CREATE OR REPLACE DIRECTORY CSV_DIR AS '/path/to/your/local/csv/folder';
GRANT READ, WRITE ON DIRECTORY CSV_DIR TO your_username;

Replace /path/to/your/local/csv/folder with the actual folder path on your server, and your_username with your database user.

This procedure will query all records from Students, format them into CSV rows, and write them to a file. We'll handle date formatting to ensure consistency in the output.

CREATE OR REPLACE PROCEDURE export_students_to_csv(p_file_name IN VARCHAR2)
IS
  v_file UTL_FILE.FILE_TYPE;
  -- Cursor to fetch all student records
  CURSOR c_students IS
    SELECT id, name, birth_date FROM Students;
  -- Variables to hold each record's data
  v_id Students.id%TYPE;
  v_name Students.name%TYPE;
  v_birth_date Students.birth_date%TYPE;
BEGIN
  -- Open the file in write mode (32767 is the max line length)
  v_file := UTL_FILE.FOPEN('CSV_DIR', p_file_name, 'W', 32767);
  
  -- Optional: Write a header row to make the CSV more readable
  UTL_FILE.PUT_LINE(v_file, 'id,name,birth_date');
  
  -- Loop through every student record
  OPEN c_students;
  LOOP
    FETCH c_students INTO v_id, v_name, v_birth_date;
    EXIT WHEN c_students%NOTFOUND;
    
    -- Convert date to a standard string format (adjust the mask if you need a different format)
    UTL_FILE.PUT_LINE(v_file, 
      v_id || ',' || 
      v_name || ',' || 
      TO_CHAR(v_birth_date, 'YYYY-MM-DD')
    );
  END LOOP;
  CLOSE c_students;
  
  -- Clean up: close the file
  UTL_FILE.FCLOSE(v_file);
  
  DBMS_OUTPUT.PUT_LINE('Export done! File saved as: ' || p_file_name);
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('Error: The directory path is invalid or Oracle can''t access it.');
    RAISE;
  WHEN UTL_FILE.INVALID_MODE THEN
    DBMS_OUTPUT.PUT_LINE('Error: Invalid file open mode (should be ''W'' for write).');
    RAISE;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
    -- Make sure we close the file even if something goes wrong
    IF UTL_FILE.IS_OPEN(v_file) THEN
      UTL_FILE.FCLOSE(v_file);
    END IF;
    RAISE;
END;
/

To run this procedure:

SET SERVEROUTPUT ON;
EXEC export_students_to_csv('students_export.csv');

This procedure reads a CSV file, splits each line into individual fields, converts the date string back to a DATE type, and inserts the record into Students. We'll add error handling to skip problematic lines instead of failing the whole import.

CREATE OR REPLACE PROCEDURE import_csv_to_students(p_file_name IN VARCHAR2)
IS
  v_file UTL_FILE.FILE_TYPE;
  v_line VARCHAR2(32767); -- Holds a single line from the CSV
  -- Variables to store parsed fields
  v_id Students.id%TYPE;
  v_name Students.name%TYPE;
  v_birth_date_str VARCHAR2(20);
  v_birth_date Students.birth_date%TYPE;
  -- Track comma positions to split the line
  v_comma_pos1 NUMBER;
  v_comma_pos2 NUMBER;
BEGIN
  -- Open the file in read mode
  v_file := UTL_FILE.FOPEN('CSV_DIR', p_file_name, 'R', 32767);
  
  -- Optional: Skip the header row (remove this block if your CSV has no header)
  UTL_FILE.GET_LINE(v_file, v_line);
  
  -- Process each line in the CSV
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_file, v_line);
      
      -- Find the positions of the first two commas to split the line
      v_comma_pos1 := INSTR(v_line, ',', 1, 1);
      v_comma_pos2 := INSTR(v_line, ',', 1, 2);
      
      -- Extract each field using substring operations
      v_id := TO_NUMBER(SUBSTR(v_line, 1, v_comma_pos1 - 1));
      v_name := SUBSTR(v_line, v_comma_pos1 + 1, v_comma_pos2 - v_comma_pos1 - 1);
      v_birth_date_str := SUBSTR(v_line, v_comma_pos2 + 1);
      
      -- Convert the date string back to a DATE type (match the export format!)
      v_birth_date := TO_DATE(v_birth_date_str, 'YYYY-MM-DD');
      
      -- Insert the record into the Students table
      INSERT INTO Students(id, name, birth_date)
      VALUES(v_id, v_name, v_birth_date);
      
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT; -- We've reached the end of the file
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Skipping invalid line: ' || v_line || ' | Error: ' || SQLERRM);
        CONTINUE; -- Keep processing the rest of the file
    END;
  END LOOP;
  
  -- Save all changes to the database
  COMMIT;
  
  -- Clean up: close the file
  UTL_FILE.FCLOSE(v_file);
  
  DBMS_OUTPUT.PUT_LINE('Import completed successfully!');
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('Error: The directory path is invalid or Oracle can''t access it.');
    RAISE;
  WHEN UTL_FILE.INVALID_MODE THEN
    DBMS_OUTPUT.PUT_LINE('Error: Invalid file open mode (should be ''R'' for read).');
    RAISE;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
    IF UTL_FILE.IS_OPEN(v_file) THEN
      UTL_FILE.FCLOSE(v_file);
    END IF;
    RAISE;
END;
/

To run this procedure:

SET SERVEROUTPUT ON;
EXEC import_csv_to_students('students_import.csv');

Key Notes to Keep in Mind #

• Date Format Consistency: Make sure the TO_CHAR (export) and TO_DATE (import) format masks match. If your CSV uses a different date format (like DD-MON-YYYY), adjust the masks accordingly.
• Handling Special Characters: If your name field might contain commas or quotes, you'll need to modify the parsing logic to handle quoted fields. For example, using regular expressions to extract fields wrapped in double quotes.
• Permissions: Ensure the Oracle server process has read/write access to the directory you specified in CSV_DIR.
• Testing: Always test with a small dataset first, and consider backing up the Students table before running the import procedure.

内容的提问来源于stack exchange，提问作者Mihnea Bigu