Oracle 存储过程

Oracle存储过程

创建存储过程

CREATE OR REPLACE PROCEDURE UPDATE_EMPLOYEE_SALARY(
       p_employee_id IN NUMBER,
       p_employee_salary IN NUMBER
)AS
BEGIN
  UPDATE employees
  SET salary = p_employee_salary
  WHERE employee_id = p_employee_id;
  
  COMMIT;
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('不存在当前员工'||p_employee_id);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('AN ERROR'||SQLERRM);
END;

查询存储过程方法1

SELECT * FROM DBA_SOURCE
WHERE TYPE = 'PROCEDURE'
AND NAME = 'UPDATE_EMPLOYEE_SALARY'

查询存储过程方法2

SELECT * FROM User_Objects
WHERE OBJECT_TYPE = 'PROCEDURE'
AND OBJECT_NAME = 'UPDATE_EMPLOYEE_SALARY'

删除存储过程

DROP PROCEDURE UPDATE_EMPLOYEE_SALARY

执行存储过程

BEGIN 
  UPDATE_EMPLOYEE_SALARY(3,'7000');
END;

多条信息的存储过程

CREATE OR REPLACE PROCEDURE INCREASE_SALARY(
       P_PERCENTAGE IN NUMBER
)AS
       CURSOR EMP_CURSOR IS 
       SELECT EMPLOYEE_ID,SALARY FROM EMPLOYEES;
BEGIN
  FOR EMP IN EMP_CURSOR LOOP
    UPDATE EMPLOYEES
    SET SALARY = EMP.SALARY + (EMP.SALARY*P_PERCENTAGE/100)
    WHERE EMPLOYEE_ID = EMP.EMPLOYEE_ID;
  END LOOP;
  COMMIT;
END;

执行存储过程

BEGIN
  INCREASE_SALARY(10);
END;

用存储过程每年生成一张表

为了在 Oracle 数据库中每年生成一张表，您可以编写一个存储过程，该存储过程可以根据当前年份动态创建表。可以使用 DBMS_SCHEDULER 或 DBMS_JOB 定期调度该存储过程。

以下是如何实现这一目标的详细步骤和示例代码：

步骤 1: 创建存储过程

首先，编写一个存储过程，根据当前年份创建一张新表。假设表的命名格式为 employees_。

CREATE OR REPLACE PROCEDURE create_yearly_employee_table IS
    v_year VARCHAR2(4);
    v_sql  VARCHAR2(1000);
BEGIN
    -- 获取当前年份
    v_year := TO_CHAR(SYSDATE, 'YYYY');

    -- 动态生成创建表的 SQL 语句
    v_sql := 'CREATE TABLE employees_' || v_year || ' (
                 employee_id NUMBER PRIMARY KEY,
                 first_name  VARCHAR2(50),
                 last_name   VARCHAR2(50),
                 salary      NUMBER
             )';

    -- 执行创建表的 SQL 语句
    EXECUTE IMMEDIATE v_sql;

    DBMS_OUTPUT.PUT_LINE('Table employees_' || v_year || ' created successfully.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

步骤 2: 创建调度任务

使用 DBMS_SCHEDULER 包定期调度存储过程的执行。下面的示例每天执行一次存储过程，但您可以根据需要调整调度频率。

BEGIN
    DBMS_SCHEDULER.create_job (
        job_name        => 'create_yearly_employee_table_job',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN create_yearly_employee_table; END;',
        start_date      => TO_TIMESTAMP_TZ('2024-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR'),
        repeat_interval => 'FREQ=YEARLY;BYMONTH=1;BYMONTHDAY=1;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
        enabled         => TRUE
    );
END;
/

解释

job_name: 调度任务的名称。
job_type: 指定任务类型为 PL/SQL 块。
job_action: 指定要执行的 PL/SQL 块。
start_date: 指定任务开始执行的日期和时间。
repeat_interval: 指定任务的重复间隔。此示例表示每年1月1日的午夜执行一次。
enabled: 启用任务。

测试存储过程

在调度任务开始运行之前，您可以手动测试存储过程以确保其正常工作。

BEGIN
    create_yearly_employee_table;
END;
/

如果存储过程执行成功，您应该在数据库中看到一个新的表，例如 employees_2024。

注意事项

权限: 确保执行存储过程和调度任务的用户具有足够的权限来创建表和使用 DBMS_SCHEDULER。
错误处理: 在实际应用中，您可能需要更复杂的错误处理和日志记录机制。
表命名冲突: 如果表已经存在，存储过程会报错。可以在创建表之前检查表是否存在，并根据需要采取相应的操作。
通过上述步骤，您可以创建一个存储过程并定期调度它，以便每年自动生成一张新表。