PLSql 强化

古城微笑少年丶 2021-12-05 14:53 378阅读 0赞

1 在使用游标的时候，游标会产生四个属性：%found、%notfound、%isopen 和%rowcount，下面以隐式游标为例来看下游标属性的使用方法。

BEGIN
      --update 语句会产生一个游标，我们会获取到相应的游标属性
      UPDATE cux_cursor_test t SET t.age = 30 WHERE t.person_name = 'Herry';
      IF SQL%FOUND THEN
        dbms_output.put_line('Herry 更新了几条记录：' || SQL%ROWCOUNT);
      ELSIF SQL%NOTFOUND THEN
        dbms_output.put_line('Herry 在表中不存在');
      END IF;
      --delete 语句会产生一个游标，我们会获取到相应的游标属性
      DELETE cux_cursor_test p WHERE p.person_name = 'Abc';
      IF SQL%FOUND THEN
        dbms_output.put_line('Abc 删除了几条记录' || SQL%ROWCOUNT);
      ELSIF SQL%NOTFOUND THEN
        dbms_output.put_line('Abc 在表中不存在');
      END IF;
      --insert 语句会产生一个游标，我们会获取到相应的游标属性
      INSERT INTO cux_cursor_test
      VALUES
        (103,
         'Peter',
         28);
      IF SQL%FOUND THEN
        dbms_output.put_line('Peter 插入了几条记录：' || SQL%ROWCOUNT);
      ELSIF SQL%NOTFOUND THEN
        dbms_output.put_line('无数据可以插入');
      END IF;
    END;

2 %rowcount r它随每次fetch而累计：

[参考文章][Link 1]

3 %notfound：It returns `TRUE` if an `INSERT`, `UPDATE`, or `DELETE` statement affected no rows, or a `SELECT` `INTO` statement returned no rows. Otherwise, it returns `FALSE`.

[参考文章][Link 2]

4 匿名plsql存储过程：

declare 
    MESSAGE VARCHAR2(35) := 'Tihs is my firet PL/SQL program';
    DATE_WRITTEN date := sysdate;
    begin 
      insert into temp(char_store,date_store) values(MESSAGE,DATE_WRITTEN);
      exception
        when others then
          dbms_output.put_line(sqlcode||'  '||sqlerrm);
    end;

5.存储过程（procedure）：给一个员工换工作，输入员工id，新工作id，新工作薪水为新工作最低工资加500。

CREATE OR REPLACE PROCEDURE add_job_hist
    (
      v_employee_id IN NUMBER,
      v_new_job_id  IN VARCHAR2
    ) IS
      v_hire_date     DATE;
      v_job_id        VARCHAR2(100);
      v_department_id NUMBER;
      v_salary        NUMBER;
    BEGIN
      -- 查询新工作最低薪水
      SELECT min_salary INTO v_salary FROM jobs WHERE job_id = v_new_job_id;
      -- 查询员工原始工作信息
      SELECT hire_date,
             job_id,
             department_id
        INTO v_hire_date,
             v_job_id,
             v_department_id
        FROM employees
       WHERE employee_id = v_employee_id;
      -- 工作记录表，添加一条历史记录
      INSERT INTO job_history
        (employee_id,
         start_date,
         end_date,
         job_id,
         department_id)
      VALUES
        (v_employee_id,
         v_hire_date,
         SYSDATE,
         v_job_id,
         v_department_id);
      -- 修改员工表job_id,salary,hire_date
      UPDATE employees
         SET hire_date = SYSDATE,
             job_id    = v_new_job_id,
             salary    = v_salary + 500
       WHERE employee_id = v_employee_id;
       -- 提交
       commit;
    END;

6. 函数（function）：查询员工已工作年数

create or replace function get_service_years(v_id number) return number is
    v_res number := 0;
    v_cnt number := 0;
    e_invalid_emp_id exception;
    cursor cur is select (end_date - start_date) day_s from job_history where employee_id = v_id;
    begin 
      select count(*) into v_cnt from job_history where employee_id = v_id;
      if v_cnt <= 0 then raise e_invalid_emp_id;
      end if;
      for c_row in cur 
        loop 
          v_res := v_res + c_row.day_s;
        end loop;
      v_res := v_res / 365;
      return v_res;
      exception 
        when e_invalid_emp_id then
          dbms_output.put_line('error: invalid emplloyee id');
          return null;
        when others then
          dbms_output.put_line(sqlerrm);
          return null;
    end;

[Link 1]: https://www.cnblogs.com/gaojian/archive/2012/11/14/2769385.html
[Link 2]: https://www.cnblogs.com/dingyingsi/p/3358682.html