一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法（上）

DECLARE
  -- 声明部分
BEGIN
  -- 执行部分
EXCEPTION
  -- 异常处理部分
END;

DECLARE
  -- 声明部分
  v_name VARCHAR2(100);
BEGIN
  -- 执行部分
  SELECT first_name INTO v_name
  FROM employees
  WHERE employee_id = 100;
  
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
EXCEPTION
  -- 异常处理部分
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found for employee ID 100.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;
/

variable_name data_type [NOT NULL] [:= default_value];
constant_name CONSTANT data_type := value;

variable_name datatype [NOT NULL] [:= initial_value];

DECLARE
  v_age NUMBER := 30;
  v_salary NUMBER;
BEGIN
  v_salary := v_age * 1000;
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

constant_name CONSTANT datatype := value;

DECLARE
  c_tax_rate CONSTANT NUMBER := 0.15;
  v_income NUMBER := 10000;
  v_tax NUMBER;
BEGIN
  v_tax := v_income * c_tax_rate;
  DBMS_OUTPUT.PUT_LINE('Tax: ' || v_tax);
END;
/

IF condition THEN
  -- statements
ELSIF condition THEN
  -- statements
ELSE
  -- statements
END IF;

DECLARE
  v_age NUMBER := 25;
BEGIN
  IF v_age < 20 THEN
    DBMS_OUTPUT.PUT_LINE('Teenager');
  ELSIF v_age >= 20 AND v_age < 30 THEN
    DBMS_OUTPUT.PUT_LINE('Young adult');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Adult');
  END IF;
END;
/

CASE [expression]
  WHEN value1 THEN
    -- statements
  WHEN value2 THEN
    -- statements
  [ELSE
    -- statements]
END CASE;

DECLARE
  v_score NUMBER := 85;
  v_grade CHAR(1);
BEGIN
  CASE
    WHEN v_score >= 90 THEN
      v_grade := 'A';
    WHEN v_score >= 80 THEN
      v_grade := 'B';
    WHEN v_score >= 70 THEN
      v_grade := 'C';
    ELSE
      v_grade := 'F';
  END CASE;
  
  DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);
END;
/

FOR counter IN [REVERSE] lower_bound..upper_bound LOOP
  -- statements
END LOOP

DECLARE
BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
  END LOOP;
END;
/

WHILE condition LOOP
  -- statements
END LOOP;

DECLARE
  v_counter NUMBER := 1;
BEGIN
  WHILE v_counter <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter);
    v_counter := v_counter + 1;
  END LOOP;
END;
/

DECLARE
  v_number NUMBER := 5;
  v_factorial NUMBER := 1;
BEGIN
  FOR i IN 1..v_number LOOP
    v_factorial := v_factorial * i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Factorial of ' || v_number || ' is ' || v_factorial);
END;
/

DECLARE
  v_number NUMBER := 29;
  v_is_prime BOOLEAN := TRUE;
BEGIN
  FOR i IN 2..FLOOR(SQRT(v_number)) LOOP
    IF MOD(v_number, i) = 0 THEN
      v_is_prime := FALSE;
      EXIT;
    END IF;
  END LOOP;
  IF v_is_prime THEN
    DBMS_OUTPUT.PUT_LINE(v_number || ' is a prime number.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_number || ' is not a prime number.');
  END IF;
END;
/

sqlCopy code
DECLARE
  v_total_salary NUMBER := 0;
  v_avg_salary NUMBER;
  v_employee_count NUMBER := 0;
BEGIN
  -- 计算员工总工资
  SELECT SUM(salary) INTO v_total_salary
  FROM employees;
  -- 计算员工总数
  SELECT COUNT(*) INTO v_employee_count
  FROM employees;
  -- 计算平均工资
  IF v_employee_count > 0 THEN
    v_avg_salary := v_total_salary / v_employee_count;
    DBMS_OUTPUT.PUT_LINE('平均工资为：' || TO_CHAR(v_avg_salary));
  ELSE
    DBMS_OUTPUT.PUT_LINE('无员工数据');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生错误：' || SQLERRM);
END;
/