Oracle的存储过程基本写法

 

转：

Oracle的存储过程基本写法

 

1.1，Oracle存储过程简介：　

　　存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，
减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

优点：

  • 允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次。
  • 允许更快执行，如果某操作需要执行大量SQL语句或重复执行，存储过程比SQL语句执行的要快。
  • 减少网络流量，例如一个需要数百行的SQL代码的操作有一条执行语句完成，不需要在网络中发送数百行代码。
  • 更好的安全机制，对于没有权限执行存储过程的用户，也可授权他们执行存储过程。  

1.2，创建存储过程的语法：

复制代码
 1 CREATE OR REPLACE PROCEDURE 存储过程名( param1 IN TYPE， param2 OUT TYPE)
 2 AS 
 3      变量 1 类型(值范围);--a_dov varchar(100)
 4      变量 2 类型(值范围);
 5 BEGIN
 6 SELECT count( * ) INTO 变量 1 FROM 表 A WHERE  列名 = param1; --从表中取某列或某些列的值,要想使用肯定要赋值给变量的,那么select into就是为了赋值而营定义的语法
 7 IF (判断条件) THEN
 8 　　SELECT 列名 INTO 变量 2 FROM 表 A WHERE  列名 = param1;
 9 　　Dbms_output.Put_line ('打印信息');
10 ELSIF (判断条件) THEN
11 　　Dbms_output.Put_line ('打印信息');
12 　　ELSE Raise 异常名(NO_DATA_FOUND);
13 END IF;
14 　　EXCEPTION 
15 　　WHEN others THEN
16 　　Rollback；
17 END;
18 
19 --调用存储过程
20 DECLARE
21      变量 1 类型(值范围);--a_dov varchar(100)
22      变量 2 类型(值范围);
23 BEGIN
24     param1:=7788; --对参数进行赋值
25     存储过程名(param1);
26 END;
复制代码

参数的几种类型:
       in 是参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变。
       out 模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
       in out 表示高参数可以向该过程中传递值，也可以将某个值传出去

1.3，示范一些存储过程[下面一些存储过程的操作根据自己数据库中的内容进行内容显示，只要显示内容就正确，报错除外- -，还有存储过程尽量不要粘贴代码，很容易报错]:

　1.3.1，不带参数的存储过程：

复制代码
 1 CREATE OR REPLACE PROCEDURE MYDEMO02 
 2 AS 
 3         name VARCHAR (10);
 4         age int;
 5 BEGIN
 6         name := 'xiaoming';--:=则是对属性进行赋值
 7         age := 18;
 8         dbms_output.put_line ( 'name=' || name || ', age=' || age );--这条是输出语句
 9 END;
10 --存储过程调用(下面只是调用存储过程语法)
11 BEGIN 
12     MYDEMO02();
13 END;
复制代码

　1.3.2，带参数的存储过程:

复制代码
 1 CREATE OR REPLACE procedure MYDEMO03(name in varchar,age in int)
 2 AS
 3 BEGIN
 4       dbms_output.put_line('name='||name||', age='||age);
 5 END;
 6 
 7 --存储过程调用
 8 BEGIN 
 9     MYDEMO03('姜煜',18);
10 END;
复制代码

　1.3.3，出现异常的输出存储过程:

复制代码
 1 CREATE OR REPLACE PROCEDURE MYDEMO04
 2 AS
 3     age INT;
 4 BEGIN
 5     age:=10/0;
 6     dbms_output.put_line(age);
 7     exception when others then
 8     dbms_output.put_line('error');
 9 END;
10 --调用存储过程
11 BEGIN
12      MYDEMO04;
13 END;
复制代码

　1.3.4，获取当前时间和总人数:

复制代码
 1 CREATE OR REPLACE PROCEDURE TEST_COUNT01
 2 IS 
 3     v_total int;
 4     v_date varchar(20);
 5 BEGIN
 6     select count(*) into v_total from EMP_TEST WHERE ENAME ='燕小六'; 
 7     select to_char(sysdate,'yyyy-mm-dd')into v_date FROM EMP_TEST WHERE ENAME ='郭芙蓉';
 8     DBMS_OUTPUT.put_line('总人数:'||v_total);
 9     DBMS_OUTPUT.put_line('当前日期'||v_date);
10 END;
11 
12 --调用存储过程
13 BEGIN
14     TEST_COUNT01();
15 END;
复制代码

　1.3.5，带输入参数和输出参数的存储过程：

复制代码
 1 CREATE OR REPLACE PROCEDURE TEST_COUNT04(v_id in int,v_name out varchar2)
 2 IS
 3 BEGIN
 4         SELECT ENAME into v_name FROM EMP_TEST WHERE EMPNO = v_id;
 5         dbms_output.put_line(v_name);
 6 EXCEPTION
 7         when no_data_found then dbms_output.put_line('no_data_found');
 8 END;
 9 
10 --调用存储过程
11 DECLARE
12         v_name varchar(200);
13 BEGIN
14         TEST_COUNT04('1002',v_name);
15 END;
复制代码

　1.3.6，查询存储过程以及其他:

复制代码
 1 CREATE OR REPLACE PROCEDURE job_day04(de in varchar,name out varchar,App_Code out varchar,error_Msg out varchar)
 2 AS
 3 BEGIN
 4         SELECT ENAME into name FROM EMP_TEST WHERE ENAME=de;
 5 EXCEPTION WHEN others THEN
 6         error_Msg:='未找到数据';
 7 END;
 8 --调用存储过程
 9 DECLARE
10    de varchar(10);
11    ab varchar(10);
12    appcode varchar(20);
13    ermg varchar(20);
14 BEGIN
15    de:= '张三丰';
16    JOB_DAY04(de,ab,appcode,ermg);
17    dbms_output.put_line(ermg);
18 END;
复制代码

　1.3.7，向数据库中添加数据的存储过程

复制代码
 1 CREATE OR REPLACE PROCEDURE job_day05(do1 in varchar,dn1 in varchar,eo1 in number,en1 in varchar,App_Code out varchar,error_Msg out varchar)
 2 AS
 3 BEGIN
 4         INSERT INTO STUDENT(NAME,CLASS)VALUES(do1,dn1); 
 5         INSERT INTO COMPANY(EMPID,NAME,DEPARNAME)VALUES(eo1,en1,do1);
 6 COMMIT;
 7 EXCEPTION WHEN OTHERS THEN
 8         App_Code:=-1;
 9         error_Msg:='插入失败';
10 END;
11 --调用存储过程
12 DECLARE
13    do1 varchar(10);
14    dn1 varchar(10);
15    eo1 number(20);
16    App_Code varchar(20);
17    error_Msg varchar(20);
18 BEGIN
19     do1:= '张三丰';
20     dn1:='新桥';
21     eo1:=1001;
22     JOB_DAY04(do1,dn1,App_Code,error_Msg);
23     dbms_output.put_line(ermg);
24 END;
复制代码

这个比较麻烦，做的时候假如报错就别找了- -我找了好久也没找到，，，

2.0，游标的使用。看到的一段解释很好的概念，如下：

  •    游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放
  •    在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。 
  •    游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，
  •    系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多
  •    行多列的SELECT语句。 
  •    游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。
  •    在我们进行insert、update、delete和select   value into  variable 的操作中，使用的是隐式游标
  •    隐式游标的属性 返回值类型意义             
  •    SQL%ROWCOUNT    整型  代表DML语句成功执行的数据行数   
  •    SQL%FOUND   布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功   
  •    SQL%NOTFOUND    布尔型 与SQL%FOUND属性返回值相反   
  •    SQL%ISOPEN  布尔型 DML执行过程中为真，结束后为假  

　2.1，修改雇员薪资：

复制代码
 1 CREATE OR REPLACE PROCEDURE job_day06(epo in number)
 2     AS
 3     BEGIN
 4         UPDATE EMPS SET SAL=(SAL+100) WHERE empno = epo;
 5     IF SQL%FOUND
 6     THEN
 7         DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！');
 8     commit;
 9     else
10         DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');
11     END IF;
12 END;
13 --调用存储过程
14 declare
15     e_number number;
16 begin
17     e_number:=1001;
18     job_day06(e_number);
19 end;
复制代码

　2.2，查询编号为1001信息

复制代码
 1 CREATE OR REPLACE PROCEDURE job_day07
 2 IS
 3 BEGIN
 4 DECLARE
 5     cursor emp_sor is select name,sal FROM EMPS WHERE EMPNO = '1001';
 6     cname EMPS.NAME%type;
 7     csal EMPS.SAL%type;
 8 BEGIN
 9     open emp_sor;
10     loop
11 --     取游标值给变量
12     FETCH emp_sor into cname,csal;
13     dbms_output.put_line('name:'||cname);
14     exit when emp_sor%notfound;
15     end loop;
16     close emp_sor;
17 end;
18 end;
19 --调用存储过程
20 BEGIN
21     job_day07();
22 END;
复制代码

总结:
　　存储过程通俗的理解就是就是一个执行过程,调用的时候给他所需要的需求就会对数据库进行操作,相当于我们自己手写Sql,只不过有了存储过程
　　只要调用一下传给他参数他就会帮我们写,比较方便,灵活的运用存储过程会让我们开发很方便

学习交流群：540866202

作者： 如果耐，请真耐

出处： https://www.cnblogs.com/joeyJss/

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出,原文链接 如有问题， 可发送邮件咨询.

分类: 数据库
posted @ 2019-10-24 15:31  戈博折刀  阅读(1208)  评论(0)  编辑  收藏  举报