oracle - 存储过程学习笔记

最新推荐文章于 2024-07-26 09:40:31 发布

weixin_44611956

最新推荐文章于 2024-07-26 09:40:31 发布

阅读量722

点赞数 3

分类专栏： oracle数据库文章标签：数据库存储过程

本文链接：https://blog.csdn.net/weixin_44611956/article/details/88033332

版权

oracle数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.在存储过程/包中如何启用并行

如果需要执行alter session，则需要用动态语句执行，如：execute immediate ‘alter session enable parallel dml’;

2.TRUNCATE TABLE

1.在功能上与不带 WHERE 子句的 DELETE 语句相同；
2. 二者均删除表中的全部行；
3. 但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
4. DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项；
5. TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放；
6. TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变；
7.新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE；
8. 如果要删除表定义及其数据，请使用 DROP TABLE 语句；
9.对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句；
10.由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器；
11.TRUNCATE TABLE 不能用于参与了索引视图的表；

3.+parallel(t,n)

t代表表别名或者表名（没有起别名情况）；
n代表进程数量。

4.NVL函数

NVL函数的格式如下：NVL(expr1,expr2)；
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

5.数字式循环

FOR 循环计数器 IN [ REVERSE ] 下限 … 上限 LOOP
要执行的语句;
END LOOP;

每循环一次，循环变量自动加 1；使用关键字 REVERSE，循环变量自动减 1。

例题：

BEGIN
FOR int in 1…10 LOOP
DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int);
END LOOP;
END;

6.存储过程格式

CREATE [OR REPLACE] PROCEDURE Procedure_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
{ IS | AS }
<类型.变量的说明>
BEGIN
<执行部分>
EXCEPTION
<可选的异常错误处理程序>
END;

7. 遇到的问题

使用PL/SQL写存储过程，使用 dbms_output.PUT_LINE(bc_date_from);语句打印信息，但是看不到打印出来的信息在哪。如何查看呢？
答：如果是PL/SQL的工具的话，使用‘SQL窗口’，结果在你执行SQL的小窗体的左上角有一个‘OutPut’窗体里面；
如果使用的命令行的话，你得先设置允许显示输出类似dbms_output，命令如下：
SQL>set serveroutput on；

8.流程控制

declare
v_sal employees.salary%type;
begin
select salary into v_sal from employees where employee_id=150;
if v_sal >=10000 then dbms_output.put_line(‘salary>=10000’);
elsif v_sal >=5000 then dbms_output.put_line(‘10000>salary>=5000’);
else dbms_output.put_line(‘salary<5000’);
end if;
end;

declare
v_sal employees.salary%type;
v_temp varchar2(30);
begin
select salary into v_sal from employees where employee_id=150;
if v_sal >=10000 then v_temp := ‘salary>=10000’;
elsif v_sal >=5000 then v_temp := ‘10000>salary>=5000’;
else v_temp := ‘salary<5000’;
end if;
dbms_output.put_line(v_sal||’,’||v_temp);
end;

9.Oracle中的dual

1.dual是Oracle中的一个伪表，利用这个伪表可以设置或查看序列，或者是调用一些内置的函数，方便操作。
2.dual表就是oracle与数据字典自动创建的一张表，这张表是一个单行单列的表，这个表只有1列：DUMMY，数据类型为VERCHAR2(1)，dual表中只有一个数据’X’, Oracle有内部逻辑保证dual表中永远只有一条数据。dual表主要是用来选择系统变量或是求一个表达式的值。
比如：
求系统当前时间
SELECT sysdate FROM daul
按位数四舍五入
select round(1.25,1) from dual;------1.3

10.游标的使用

10.1 使用游标

打印出 80 部门的所有的员工的工资:salary: xxx

declare
–1. 定义游标
cursor salary_cursor is select salary from employees where department_id = 80;
v_salary employees.salary%type;
begin
–2. 打开游标
open salary_cursor;

–3. 提取游标
fetch salary_cursor into v_salary;

–4. 对游标进行循环操作: 判断游标中是否有下一条记录
while salary_cursor%found loop
dbms_output.put_line('salary: ’ || v_salary);
fetch salary_cursor into v_salary;
end loop;

–5. 关闭游标
close salary_cursor;
end;

10.2 使用游标

-== 打印出 80 部门的所有的员工的工资: Xxx 's salary is: xxx==

declare
  cursor sal_cursor is select salary ,last_name from employees where department_id = 80;
  v_sal number(10);
  v_name varchar2(20);
begin
  open sal_cursor;
  
  fetch sal_cursor into v_sal,v_name;
  
  while sal_cursor%found loop
        dbms_output.put_line(v_name||'`s salary is '||v_sal);
        fetch sal_cursor into v_sal,v_name;
  end loop;
  
  close sal_cursor;
  
end;

10.3. 使用游标的练习:
打印出 manager_id 为 100 的员工的 last_name, email, salary 信息(使用游标, 记录类型)

declare
–声明游标
cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;

       --声明记录类型
       type emp_record is record(
            name employees.last_name%type,
            email employees.email%type,
            salary employees.salary%type
       );
       
       -- 声明记录类型的变量
       v_emp_record emp_record;

begin
–打开游标
open emp_cursor;

       --提取游标
       fetch emp_cursor into v_emp_record;
       
       --对游标进行循环操作
       while emp_cursor%found loop
              dbms_output.put_line(v_emp_record.name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary );                
              fetch emp_cursor into v_emp_record;
       end loop;
       
       --关闭游标
       close emp_cursor;

end;
(法二：使用for循环)
declare

  cursor emp_cursor is 
  select last_name,email,salary
  from employees
  where manager_id = 100;

begin

  for v_emp_record in emp_cursor loop
      dbms_output.put_line(v_emp_record.last_name||','||v_emp_record.email||','||v_emp_record.salary);
  end loop;

end;

利用游标, 调整公司中员工的工资:

工资范围调整基数
0 - 5000 5%
5000 - 10000 3%
10000 - 15000 2%
15000 - 1%

declare
–定义游标
cursor emp_sal_cursor is select salary, employee_id from employees;

--定义基数变量
temp number(4, 2);

--定义存放游标值的变量
v_sal employees.salary%type;
v_id employees.employee_id%type;

begin
–打开游标
open emp_sal_cursor;

--提取游标
fetch emp_sal_cursor into v_sal, v_id;

--处理游标的循环操作
while emp_sal_cursor%found loop
      --判断员工的工资, 执行 update 操作
      --dbms_output.put_line(v_id || ': ' || v_sal);
        
      if v_sal <= 5000 then
         temp := 0.05;
      elsif v_sal<= 10000 then
         temp := 0.03;   
      elsif v_sal <= 15000 then
         temp := 0.02;
      else
         temp := 0.01;
      end if;
      
      --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
      update employees set salary = salary * (1 + temp) where employee_id = v_id; 
              
      fetch emp_sal_cursor into v_sal, v_id;
end loop;
--关闭游标
close emp_sal_cursor;

end;

使用SQL中的 decode 函数

update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,
1, 0.03,
2, 0.02,
0.01)))

利用游标 for 循环完成 14.

declare
–定义游标
cursor emp_sal_cursor is select salary, employee_id id from employees;

--定义基数变量
temp number(4, 2);

begin
–处理游标的循环操作
for c in emp_sal_cursor loop
–判断员工的工资, 执行 update 操作
–dbms_output.put_line(v_id || ': ’ || v_sal);

      if c.salary <= 5000 then
         temp := 0.05;
      elsif c.salary <= 10000 then
         temp := 0.03;   
      elsif c.salary <= 15000 then
         temp := 0.02;
      else
         temp := 0.01;
      end if;
      
      --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
      update employees set salary = salary * (1 + temp) where employee_id = c.id;
end loop;

end;

16*. 带参数的游标

declare
–定义游标
cursor emp_sal_cursor(dept_id number, sal number) is
select salary + 1000 sal, employee_id id
from employees
where department_id = dept_id and salary > sal;

--定义基数变量
temp number(4, 2);

begin
–处理游标的循环操作
for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop
–判断员工的工资, 执行 update 操作
–dbms_output.put_line(c.id || ': ’ || c.sal);

      if c.sal <= 5000 then
         temp := 0.05;
      elsif c.sal <= 10000 then
         temp := 0.03;   
      elsif c.sal <= 15000 then
         temp := 0.02;
      else
         temp := 0.01;
      end if;
      
      dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);
      --update employees set salary = salary * (1 + temp) where employee_id = c.id;
end loop;

end;

隐式游标: 更新指定员工 salary(涨工资 10)，如果该员工没有找到，则打印”查无此人” 信息

begin
update employees set salary = salary + 10 where employee_id = 1005;

     if sql%notfound then
        dbms_output.put_line('查无此人!');
     end if;

end;

11.触发器

helloworld级别的触发器

create or replace trigger hello_trigger
 after 
update on employees
--for each row
begin 
 dbms_output.put_line('hello...');
 --dbms_output.put_line('old.salary:'|| :OLD.salary||',new.salary'||:NEW.salary);
end;

然后执行：

update employees set salary =  salary + 1000;

行级触发器: 每更新 employees 表中的一条记录, 都会导致触发器执行：

create or replace trigger employees_trigger
after 
update on employees
for each row
begin
       dbms_output.put_line('修改了一条记录!');
end;

语句级触发器: 一个 update/delete/insert 语句只使触发器执行一次：

create or replace trigger employees_trigger
after 
update on employees
begin
       dbms_output.put_line('修改了一条记录!');
end;

使用 :new, :old 修饰符：

create or replace trigger employees_trigger
after 
update on employees
for each row
begin
       dbms_output.put_line('old salary: ' || :old.salary || ', new salary: ' || :new.salary);
end;

编写一个触发器, 在对 my_emp 记录进行删除的时候, 在 my_emp_bak 表中备份对应的记录

1). 准备工作:

create table my_emp as select employee_id id, last_name name, salary sal from employees
create table my_emp_bak as select employee_id id, last_name name, salary sal from employees where 1 = 2

2).

create or replace trigger bak_emp_trigger
       before delete on my_emp
       for each row
       
begin
       insert into my_emp_bak values(:old.id, :old.name, :old.sal);
end;