Oracle的存储过程基本写法
1，Oracle存储过程简介：
  存储过程是事先经过编译并存储在数据库中的⼀段SQL语句的集合，调⽤存储过程可以简化应⽤开发⼈员的很多⼯作，
减少数据在数据库和应⽤服务器之间的传输，对于提⾼数据处理的效率是有好处的。
优点：
允许模块化程序设计，就是说只需要创建⼀次过程，以后在程序中就可以调⽤该过程任意次。
允许更快执⾏，如果某操作需要执⾏⼤量SQL语句或重复执⾏，存储过程⽐SQL语句执⾏的要快。
减少⽹络流量，例如⼀个需要数百⾏的SQL代码的操作有⼀条执⾏语句完成，不需要在⽹络中发送数百⾏代码。
更好的安全机制，对于没有权限执⾏存储过程的⽤户，也可授权他们执⾏存储过程。
1.2，创建存储过程的语法：
1create[or replace]procedure存储过程名（param1 in type，param2 out type）
2as
3变量1 类型（值范围）;
4变量2 类型（值范围）;
5begin
6select count(*) into变量1 from表A where列名=param1；
7if (判断条件) then
8select列名into变量2 from表A where列名=param1；
9        dbms_output.Put_line('打印信息');
10    elsif (判断条件) then
11        dbms_output.Put_line('打印信息');
12else
13        raise 异常名（NO_DATA_FOUND）;
14end if;
15 exception
16when others then
17rollback;
18end;
参数的⼏种类型:
in 是参数的默认模式，这种模式就是在程序运⾏的时候已经具有值，在程序体中值不会改变。
out 模式定义的参数只能在过程体内部赋值，表⽰该参数可以将某个值传递回调⽤他的过程
in out 表⽰⾼参数可以向该过程中传递值，也可以将某个值传出去
1.3，常见存储过程实操:
  (下⾯⼀些存储过程的操作根据⾃⼰数据库中的表进⾏操作，只要显⽰内容能正常运⾏就正确，报错除外- -，还有存储过程尽量不要粘贴代码，很容易报错)
  不带参数的存储过程：
1CREATE OR REPLACE PROCEDURE MYDEMO02
2AS
3        name VARCHAR(10);
4        age NUMBER(10);
5BEGIN
6        name :='xiaoming';--:=则是对属性进⾏赋值
7        age :=18;
8        dbms_output.put_line ( 'name='|| name ||', age='|| age );--这条是输出语句
9END;
10--存储过程调⽤(下⾯只是调⽤存储过程语法)
11BEGIN
12    MYDEMO02();
13END;
  带参数的存储过程:
1CREATE OR REPLACE procedure MYDEMO03(name in varchar,age in int)
2AS
3BEGIN
4      dbms_output.put_line('name='||name||', age='||age);
5END;
6
7--存储过程调⽤
8BEGIN
9    MYDEMO03('姜煜',18);
10END;
  出现异常的输出存储过程:
1CREATE OR REPLACE PROCEDURE MYDEMO04
2AS
3    age INT;
4BEGIN
5    age:=10/0;
6    dbms_output.put_line(age);
7 EXCEPTION when others then  --处理异常
8    dbms_output.put_line('error');
9END;
11BEGIN
12      MYDEMO04;
13END;
Oracle常见的三⼤异常分类[没有详细陈述，有兴趣的同学可以⾃⾏查下]
预定义异常：由PL/SQL定义的异常。由于它们已在standard包中预定义了，因此，这些预定义异常可以直接在程序中使⽤，⽽不必再定义部分声明。
⾮预定义异常：⽤于处理预定义异常所不能处理的Oracle错误。
⾃定义异常：⽤户⾃定义的异常，需要在定义部分声明后才能在可执⾏部分使⽤。⽤户⾃定义异常对应的错误不⼀定是Oracle错误，例如它可能是⼀个数据错误。
  获取当前时间和总⼈数:
1CREATE OR REPLACE PROCEDURE TEST_COUNT01
2IS
3    v_total int;
4    v_date varchar(20);
5BEGIN
6select count(*) into v_total from EMP_TEST WHERE ENAME ='燕⼩六';  --into是赋值的关键字
7select 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);
10END;
11
12--调⽤存储过程
13BEGIN
14    TEST_COUNT01();
15END;
  带输⼊参数和输出参数的存储过程：
1CREATE OR REPLACE PROCEDURE TEST_COUNT04(v_id in int,v_name out varchar2)
2IS
3BEGIN
4SELECT ENAME into v_name FROM EMP_TEST WHERE EMPNO = v_id;
5        dbms_output.put_line(v_name);
6 EXCEPTION
7when no_data_found then dbms_output.put_line('no_data_found');
8END;
9
10--调⽤存储过程
11DECLARE
12        v_name varchar(200);
13BEGIN
14        TEST_COUNT04('1002',v_name);
15END;
  查询存储过程以及其他:
1CREATE OR REPLACE PROCEDURE job_day04(de in varchar,name out varchar,App_Code out varchar,error_Msg out varchar)
2AS
3BEGIN
4SELECT ENAME into name FROM EMP_TEST WHERE ENAME=de;
5 EXCEPTION WHEN others THEN
6        error_Msg:='未到数据';
7END;
8--调⽤存储过程
9DECLARE
10    de varchar(10);
11    ab varchar(10);
12    appcode varchar(20);
13    ermg varchar(20);
14BEGIN
15    de:='张三丰';
16    JOB_DAY04(de,ab,appcode,ermg);
17    dbms_output.put_line(ermg);
18END;
  向数据库中添加数据的存储过程
1CREATE OR REPLACE PROCEDURE ZJMES.job_day05(do1 in varchar,dn1 in varchar,eo1 in number,en1 in varchar,dn2 in varchar,App_Code out varchar,error_Msg out varchar) 2AS
3BEGIN
4INSERT INTO STUDENT(NAME,CLASS)VALUES(do1,dn1);
5INSERT INTO COMPANY(EMPID,NAME,DEPARNAME)VALUES(eo1,en1,dn2);
6COMMIT;
7 EXCEPTION WHEN OTHERS THEN
8        App_Code:=-1;
9        error_Msg:='插⼊失败';
10END;
11
12--调⽤存储过程
13DECLARE
14    do1 varchar(10);
15    dn1 varchar(10);
16    eo1 number(20);
18    dn2 varchar(20);
19    App_Code varchar(20);
20    error_Msg varchar(20);
21BEGIN
22    do1:='张三丰';
23    dn1:='新桥';
24    eo1:=1001;
25    en1:='⽼⼦';
26    dn2:='道家办公室';
27    ZJMES.job_day05(do1,dn1,eo1,en1,dn2,App_Code,error_Msg);
28    dbms_output.put_line(error_Msg);
29END;
2，游标的使⽤，看到的⼀段解释很好的概念，如下：
游标是SQL的⼀个内存⼯作区，由系统或⽤户以变量的形式定义。游标的作⽤就是⽤于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放
在磁盘的表中调到计算机内存中进⾏处理，最后将处理结果显⽰出来或最终写回数据库。这样数据处理的速度才会提⾼，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。在前述程序中⽤到的查询语句，⼀次只能从数据库中提取⼀⾏数据，对于这种形式的查询和DML操作，
系统都会使⽤⼀个隐式游标。但是如果要提取多⾏数据，就要由程序员定义⼀个显式游标，并通过与游标有关的语句进⾏处理。显式游标对应⼀个返回结果为多
⾏多列的SELECT语句。
游标⼀旦打开，数据就从数据库中传送到游标变量中，然后应⽤程序再从游标变量中分解出需要的数据，并进⾏处理。
在我们进⾏insert、update、delete和select  value into  variable 的操作中，使⽤的是隐式游标
隐式游标的属性返回值类型意义
oracle手动调用存储过程SQL%ROWCOUNT    整型代表DML语句成功执⾏的数据⾏数
SQL%FOUND  布尔型值为TRUE代表插⼊、删除、更新或单⾏查询操作成功
SQL%NOTFOUND    布尔型与SQL%FOUND属性返回值相反
SQL%ISOPEN  布尔型 DML执⾏过程中为真，结束后为假
  修改雇员薪资：
1CREATE OR REPLACE PROCEDURE job_day06(epo in number)
2AS
3BEGIN
4UPDATE EMPS SET SAL=(SAL+100) WHERE empno = epo;
5IF SQL%FOUND  --SQL%FOUND是隐式游标作⽤：判断SQL语句是否成功执⾏，当有作⽤⾏时则成功执⾏为true，否则为false。 6THEN
7        DBMS_OUTPUT.PUT_LINE('成功修改雇员⼯资！');
8commit;
9else
10        DBMS_OUTPUT.PUT_LINE('修改雇员⼯资失败！');
11END IF;
12END;
13--调⽤存储过程
14declare
15    e_number number;
16begin
17    e_number:=1001;
18    job_day06(e_number);
19end;
  查询编号为1001信息
1CREATE OR REPLACE PROCEDURE job_day07
2IS
3BEGIN
4DECLARE
5cursor emp_sor is select name,sal FROM EMPS WHERE EMPNO ='1001'; --声明游标
6    cname EMPS.NAME%type; --%type 作⽤: 声明的变量ename与EMPS表的NAME列类型⼀样
7    csal EMPS.SAL%type;
8BEGIN
9open emp_sor; --打开游标
10    loop
11--    取游标值给变量
12FETCH emp_sor into cname,csal;
13    dbms_output.put_line('name:'||cname);
14exit when emp_sor%notfound;
15end loop;
16close emp_sor; --关闭游标
17end;
18end;
19--调⽤存储过程
20BEGIN
21    job_day07();
22END;
总结:
  存储过程通俗的理解就是就是⼀个执⾏过程，调⽤的时候给他所需要的需求就会对数据库进⾏操作，相当于我们⾃⼰⼿写Sql，只不过有了存储过程  只要调⽤⼀下传给他参数他就会帮我们写,⽐较⽅便，灵活的运⽤存储过程会让我们开发很⽅便

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。