CREATE PROCEDURE UpdateCourseInfo (
  IN course_id INT,  -- Required parameter for identifying the course to update
  IN new_dept_code VARCHAR(10) = NULL,  -- Optional parameter for updating department code
  IN new_course_number VARCHAR(10) = NULL,  -- Optional parameter for updating course number
  IN new_course_title VARCHAR(100) = NULL, -- Optional parameter for updating course title
  IN new_credits INT = NULL  -- Optional parameter for updating credits
)
BEGIN
  DECLARE original_dept_code VARCHAR(10);

  -- Enforce mandatory course ID for update
  IF course_id IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Course ID is required for update!';
  END IF;

  -- Retrieve original department code for foreign key check (optional)
  SELECT dept_code INTO original_dept_code FROM Courses WHERE course_id = course_id;

  -- Update course information based on provided parameters
  UPDATE Courses
  SET dept_code = COALESCE(new_dept_code, dept_code),  -- Update dept_code if provided
      course_number = COALESCE(new_course_number, course_number), -- Update course number if provided
      course_title = COALESCE(new_course_title, course_title), -- Update course title if provided
      credits = COALESCE(new_credits, credits) -- Update credits if provided
  WHERE course_id = course_id;

  -- Check foreign key constraint after update (optional)
  IF new_dept_code IS NOT NULL THEN
    SELECT 1 FROM Departments WHERE dept_code = new_dept_code;

    IF @@ROWCOUNT = 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid department code provided!';
    END IF;
  END IF;

  SET MESSAGE_TEXT = 'Course information updated successfully!';
END;
by

PL/SQL Online Compiler

Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle PL/SQL running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.

About PL/SQL

PL/SQL is procedural extension for SQL created by Oracle. It is by default embedded into the Oracle Database. PL/SQL program units are compiled and stored inside the Oracle Database which results in optimal execution times as the PL/SQL and SQL run within the same server process.

Syntax help

Following is the syntax structure for the PL/SQL code blocks

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

Example

DECLARE 
   message  varchar2(100):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Named procedures

CREATE OR REPLACE FUNCTION 
hello_user
   (user_name IN VARCHAR2) 
    RETURN VARCHAR2
IS
BEGIN
   RETURN 'Hello ' || user_name;
END hello_user;
/

BEGIN
   dbms_output.put_line(hello_user('Peter'));
END;
/

Exception handling

BEGIN
  DBMS_OUTPUT.put_line (1/0);
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;