PLSql 强化-蒲公英云

PLSql 强化

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而累计：

参考文章

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.

参考文章

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;

函数（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;