PL/SQL:存储过程的原理及实战应用

最新推荐文章于 2021-01-21 01:59:55 发布
阿桨
最新推荐文章于 2021-01-21 01:59:55 发布
阅读量262
点赞数
分类专栏：数据库应用文章标签： sql 数据库 oracle
本文链接：https://blog.csdn.net/weixin_42924611/article/details/103860248
版权
数据库应用专栏收录该内容
14 篇文章 0 订阅
订阅专栏
**-------------------存储过程的原理及实战应用--------------------
存储过程是用PL/SQL语言编写的能完成一定处理功能的存储在数据库字典中的程序。
命名的 PL/SQL 块，编译并存储在数据库中。
存储过程的各个部分：
声明部分
可执行部分
异常处理部分(可选)
优点：
模块化
将程序分解为逻辑模块
可重用性
可以被任意数目的程序调用
可维护性
简化维护操作
安全性
通过设置权限，使数据更安全
提高性能
创建过程的语法：
CREATE [OR REPLACE] PROCEDURE 
   <procedure name> [(<parameter list>)]
IS|AS 
   <local variable declaration>--声明变量(变量名 变量类型)
BEGIN
   <executable statements>--存储过程的执行体
[EXCEPTION
   <exception handlers>]
END;
过程参数的三种模式：
IN
属于默认参数
输入模式的参数，用于接收参数，在子程序内部，不能进行修改。
--定义测试in模式的存储过程
CREATE OR REPLACE PROCEDURE pro(a in int,b in int)--参数的个数、类型可以自定义，但是【参数不允许指定长度】
AS
BEGIN
  dbms_output.put_line(a);
  dbms_output.put_line(b);
  --b:=11; --in模式参数不能为其赋值、补充：赋值是":="  而不是单个"="，单个"="是判断是否相等意思
END;
--通过语句块调用存储过程
BEGIN
  pro(10,20);
END;
输出：
10
20

OUT
输出模式的参数，用于输出值，会忽略传入的值。在子程序内部可以对其进行修改。
输出：子程序执行完毕后，out模式参数最终的值会赋值给调用时对应的<实参变量>。
注意：out模式参数的调用，必须通过变量
--测试out模式的存储过程
CREATE OR REPLACE PROCEDURE pro(c out int)
AS
BEGIN
  dbms_output.put_line(c);--c会忽略传入的值
  c:=30;--设定存储过程调用后的值
END;
DECLARE
var3 int :=100;--声明一个变量用于设定存储过程调用前的值
BEGIN
-- pro(100); --error,100对应过程中out模式的参数，out会输出结果给调用的实参，但是100不能作为赋值目标
dbms_output.put_line('存储过程调用前的值：'||var3);
pro(var3);--调用pro存储过程重新赋值；调用过程，如果过程形参是out模式，必须采用变量实参
dbms_output.put_line('存储过程调用后的值：'||var3);
END;
输出：
存储过程调用前的值：100
存储过程调用后的值：30

IN OUT
输入输出模式：能接收传入的实参值；在子程序内部可以修改； 可以输出（必须用实参变量调用）
--测试in out模式的存储过程
CREATE OR REPLACE PROCEDURE pro(d in out int)
AS
BEGIN
  dbms_output.put_line(d);
  d:=99;--in out模式参数的值可以修改
END;
DECLARE
age int :=40;--声明一个变量用于设定存储过程调用前的值
BEGIN
dbms_output.put_line('存储过程调用前的值：'||age);
pro(age);--调用pro存储过程重新赋值
dbms_output.put_line('存储过程调用后的值：'||age);
END;
输出：
存储过程调用前的值：40
40
存储过程调用后的值：99

create or replace procedure  test
( v_in  in number;
  v_out out number;
  v_inout  in out number)
 is
   i number;
 begin
   i:=1234;
   v_in:=i;   --不合法
   v_out:=v_in;  --合法
   i:=v_out;  --不合法
   v_inout:=i;  --合法
   i:=v_inout;  --合法
end;

存储过程的分类：
不带参存储过程
create or replace procedure get_news
as
  sum_rows number;
  begin 
    select count(1) into sum_rows from emp;
    dbms_output.put_line('sum_rows=' || sum_rows);
  end;
begin
get_news;
end;
输出：
sum_rows=15

带参存储过程
create or replace procedure find_emp(emp_no number) 
as
  emp_name varchar2(20);
begin
  select ename into emp_name from emp where empno = emp_no;
  dbms_output.put_line('雇员姓名是:' || emp_name);
exception
  when no_data_found then
    dbms_output.put_line('雇员编号没有找到');
end find_emp;
begin
find_emp(7369);
end;
输出：雇员姓名是:SMITH

----------------------------
CREATE OR REPLACE PROCEDURE
  find_emp(emp_no NUMBER)
AS
  emp_name VARCHAR2(20);
BEGIN
  SELECT ename INTO emp_name
  FROM EMP WHERE empno = emp_no;
  DBMS_OUTPUT.PUT_LINE('雇员姓名是 '|| emp_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('雇员编号未找到');
END find_emp;
执行过程的语法：
  EXECUTE procedure_name(parameters_list);
  
CREATE OR REPLACE PROCEDURE
  itemdesc(item_code IN VARCHAR2)
IS
  v_itemdesc VARCHAR2(5);
BEGIN
  SELECT itemdesc INTO v_itemdesc
  FROM itemfile
  WHERE itemcode = item_code;
  DBMS_OUTPUT.PUT_LINE(item_code||
          '项目的说明为'||v_itemdesc);
END;
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE itemdesc('i201');
CREATE OR REPLACE PROCEDURE 
  test( value1 IN  VARCHAR2, 
        value2 OUT NUMBER )
IS
 identity  NUMBER;
BEGIN
  SELECT ITEMRATE INTO identity 
  FROM itemFile 
  WHERE itemcode = value1;
  IF identity < 200 THEN
    value2:=100;
  END IF;
END;
DECLARE
  value1 VARCHAR2(5) := 'i202';
  value2 NUMBER;
BEGIN
  test (value1, value2);
  DBMS_OUTPUT.PUT_LINE('value2 的值为' || TO_CHAR(value2));
END;
DECLARE
   num1 NUMBER := 100;
   num2 NUMBER := 200;
BEGIN
   swap(num1, num2);
   DBMS_OUTPUT.PUT_LINE('num1 = ' || num1);
   DBMS_OUTPUT.PUT_LINE('num2 = ' || num2);
END;
将过程的执行权限授予其他用户
SQL> GRANT EXECUTE ON find_emp TO MARTIN;
SQL> GRANT EXECUTE ON swap TO PUBLIC;
删除过程
SQL> DROP PROCEDURE find_emp;
-------------------存储过程的原理及实战应用2--------------------
  1.创建包含以下列的salary表.
    empno      VARCHAR2(10),
    workdays   NUMBER,
    salary     NUMBER
   编写一个过程，根据empno计算雇员在扣除税款（税率为5%）后的净收入，并将净收入显示出来.
CREATE TABLE salary
( empno varchar2(10),
 workdays   NUMBER,
 salary     NUMBER
);
INSERT INTO salary VALUES ('E001', 21, 4000);
INSERT INTO salary VALUES ('E002', 19, 3000);
INSERT INTO salary VALUES ('E003', 20, 2500);
INSERT INTO salary VALUES ('E004', 18, 2000);
INSERT INTO salary VALUES ('E005', 15, 1800);
INSERT INTO salary VALUES ('E006',  7, 1500);
COMMIT;
select * from salary;
create or replace procedure salary_proc
    (emp_no varchar2)
    is
      emp_sal number;
      netsal number;
    begin
     select salary into emp_sal from salary
     where empno=emp_no;
     netsal:=emp_sal-emp_sal*5/100;
    dbms_output.put_line('职员'||emp_no||'的净收入为'||netsal);
   end;
   
begin 
  salary_proc('E001');
end;
输出：
职员E001的净收入为3800

create table test(A int, B int);
create or replace procedure insert_proc
    ( start_num  in number,
      end_num  in number)
    as
     begin
      declare i  number;
         begin
            for i in start_num .. end_num loop
              insert into test values(i,i);
           end loop;
        end;
   end;
begin 
  insert_proc(1,10);
end;
select * from test;
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
6	6	6
7	7	7
8	8	8
9	9	9
10	10	10
ps：看到已插入10条数据

create or replace procedure insert_stud
    ( stu_no  学生表.学号%type,
      stu_name 学生表.姓名%type,
      stu_sex  学生表.性别%type,
      stu_age  学生表.年龄%type)
    as
     begin
       insert into 学生表(学号,姓名,性别,年龄)
       values(stu_no,stu_name,stu_sex,stu_age);
    end;
begin
  insert_stud('95007','陈红','女',21);
end;
select * from 学生表;
create or replace procedure update_grade
    (stu_no  成绩表.学号%type,
     stu_cno 成绩表.课程号%type,
     stu_grade 成绩表.成绩%type)
    as
      begin
        update 成绩表
         set 成绩=stu_grade
         where 学号=stu_no and 课程号=stu_cno;
       if SQL%found then
         DBMS_output.put_line('该学生成绩已更新');
       else
        DBMS_output.put_line('该学生课程不存在');
       end if;
    end;
    
begin
  update_grade('95001','1',90);
end;
insert into 成绩表 values('95002','1',80);
insert into 成绩表 values('95003','2',95);
commit;
select * from 成绩表;
    
begin
  update_grade('95002','1',99);
end;
create  or replace procedure delete_stud
     (stu_no  学生表.学号%type)
     as
      begin
        delete 学生表
         where 学号=stu_no;
     if SQL%found then
          DBMS_OUTPUT.PUT_LINE('该学生已被删除了');
     else
        DBMS_output.put_line('该学生不存在');
    end if;
   end;
   
begin
  delete_stud('95001');
end;
-------------------存储过程的原理及实战应用3--------------------
存储过程的优化:
尽量避免大事务操作，慎用holdlock子句，提高系统并发能力
尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接 
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作 
注意存储过程中参数和数据类型的关系
注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁
select * from emp;
1	7369	SMITH	CLERK	7902	2018-12-17	2200		20
2	7499	ALLEN	SALESMAN	7698	2018-2-20	2000	1000	30
3	7521	WARD	SALESMAN	7698	2018-2-22	1450	1000	30
4	7566	JONES	MANAGER	7839	2018-4-2	2975		20
5	7654	MARTIN	SALESMAN	7698	2018-9-28	1450	1000	30
6	7698	BLAKE	SALESMAN	7839	2018-5-1	2850	1000	30
7	7782	CLARK	MANAGER	7839	2018-6-9	2450		20
8	7788	SCOTT	ANALYST	7566	1987-4-19	3000		20
9	7839	KING	PRESIDENT		2018-11-17	5000		10
10	7844	TURNER	SALESMAN	7698	2018-9-8	1700	1000	30
11	7876	ADAMS	CLERK	7788	1987-5-23	1300		20
12	7900	JAMES	CLERK	7698	2018-12-3	1150	1000	30
13	7902	FORD	ANALYST	7566	2018-12-3	3000		20
14	7934	MILLER	CLERK	7782	1982-1-23	1500		10
15	-10	Not found!		
				
create or replace procedure mytest_proc
  (value1 in number,value2 out number)
  is
  salary number;
  begin 
    select sal into salary from emp
     where empno=value1;
  if salary<2000 then
     value2:=salary+500;
     update emp
        set sal=value2
      where empno=value1;
  else
     value2:=salary;
  end if;
  end;
  
declare
  v1 number:=7369;
  v2 number;
  begin
  mytest_proc(v1,v2);
  dbms_output.put_line('V2的值的为' || to_char(v2));
  end;
输出：
V2的值的为2200
  
create or replace procedure myproc as
  cursor s_sno is
    select 学号 from 学生表;
  cursor grade_avg(stu_sno char) is
    select avg(成绩) from 成绩表 where 成绩表.学号 = stu_sno;
  stu_number   学生表.学号%type;
  stu_avggrade number;
begin
  open s_sno;
  loop
    fetch s_sno
      into stu_number;
    exit when s_sno%notfound;
    open grade_avg(stu_number);
    loop
      fetch grade_avg
        into stu_avggrade;
      exit when grade_avg%notfound;
      dbms_output.put_line(stu_number || ':' || stu_avggrade);
    end loop;
    close grade_avg;
  end loop;
  close s_sno;
end myproc;
 
 insert into 学生表 values('95002','刘晨','女',21);
 insert into 学生表 values('95003','刘成名','男',19);
 insert into 成绩表 values('95002','2',93);
 insert into 成绩表 values('95003','3',90);
 commit;
 select * from 学生表;
1	95007	陈红	女	21
2	95002	刘晨	女	21
3	95003	刘成名	男	18
4	95006	王成	男	19
 select * from 成绩表;
1	95002	1  	80
2	95003	2  	95
3	95002	2  	93
4	95003	3  	90
5	95001	001	95
6	95002	001	60
7	95003	002	70
begin 
  myproc;
end;
输出：
95002:77.66666666666666666666666666666666666667
95003:85
95006:
95007:
**