• day13_存储过程小记


    1.基本结构 
    CREATE OR REPLACE PROCEDURE 存储过程名字
    (
        参数1 IN NUMBER,
        参数2 OUT NUMBER
    ) IS
    变量1 INTEGER :=0;
    变量2 DATE;
    BEGIN

    END 存储过程名字



    2.SELECT INTO STATEMENT
      将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条
      记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
      例子: 
      BEGIN
      SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
          xxxx;
      END;
      ...

    3.IF 判断
      IF V_TEST=1 THEN
        BEGIN 
           do something
        END;
      END IF;

    4.while 循环
      WHILE V_TEST=1 LOOP
      BEGIN
     XXXX
      END;
      END LOOP;

    5.变量赋值
      V_TEST := 123;

    6.用for in 使用cursor
      ...
      IS
      CURSOR cur IS SELECT * FROM xxx;
      BEGIN
     FOR cur_result in cur LOOP
      BEGIN
       V_SUM :=cur_result.列名1+cur_result.列名2
      END;
     END LOOP;
      END;

    7.带参数的cursor
      CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
      OPEN C_USER(变量值);
      LOOP
     FETCH C_USER INTO V_NAME;
     EXIT FETCH C_USER%NOTFOUND;
        do something
      END LOOP;
      CLOSE C_USER;

    8.用pl/sql developer debug
      连接数据库后建立一个Test WINDOW
      在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

     

    简单实例,通过DBMS_OUTPUT来看结果

    CREATE OR REPLACE PROCEDURE bb (lic_para IN VARCHAR2,out_para OUT VARCHAR2)
    AS
       temp   VARCHAR2 (100);
    BEGIN
       SELECT lic_no
         INTO temp
         FROM t_vehicle_info
        WHERE lic_no = lic_para;
        out_para:=temp;
        DBMS_OUTPUT.put_line (out_para);
    END bb;

    下面是调用:

    begin

      -- Call the procedure

      bb(lic_para => :lic_para,

         out_para => :out_para);

    end;

















    1.编写。编写一个最最简单的存储过程,给它起个名字叫做proc_helloworld
    CREATE OR REPLACE PROCEDURE proc_helloworld
    IS
    BEGIN
       DBMS_OUTPUT.put_line ('Hello World!');
    END;
    /

    2.创建。在sqlplus命令行界面创建该存储过程
    sys@ora10g> conn sec/sec
    Connected.
    sec@ora10g> CREATE OR REPLACE PROCEDURE proc_helloworld
      2  IS
      3  BEGIN
      4     DBMS_OUTPUT.put_line ('Hello World!');
      5  END;
      6  /

    Procedure created.

    3.运行。两种方法运行存储过程
    1).需要在屏幕上显示出"DBMS_OUTPUT.put_line"的输出字符串,需要做一个小小的设置
    sec@ora10g> show serveroutput
    serveroutput OFF
    sec@ora10g> set serveroutput on
    sec@ora10g> show serveroutput
    serveroutput ON SIZE 10000 FORMAT WORD_WRAPPED

    2).在sqlplus命令行界面使用"EXECUTE"命令(简写做"EXEC")执行
    sec@ora10g> exec proc_helloworld
    Hello World!

    PL/SQL procedure successfully completed.

    3).在一个无名PL/SQL块内运行存储过程
    BEGIN
       proc_helloworld;
    END;
    /

    sec@ora10g> BEGIN
      2     proc_helloworld;
      3  END;
      4  /
    Hello World!

    PL/SQL procedure successfully completed.

    4.修改。修改一个存储过程只需要将修改好的存储过程在sqlplus界面先重新执行一下即可,因为在创建过程中使用的是"CREATE OR REPLACE PROCEDURE",也就是说如果没有就创建,如果已经存在了这个存储过程,就替换它
    CREATE OR REPLACE PROCEDURE proc_helloworld
    IS
    BEGIN
       DBMS_OUTPUT.put_line ('Hello World! '||chr(10)||'I am a Happy DBA Secooler!');
    END;
    /
    或者
    CREATE OR REPLACE PROCEDURE proc_helloworld
    IS
    BEGIN
       DBMS_OUTPUT.put_line ('Hello World! 
    I am a Happy DBA Secooler!');
    END;
    /

    sec@ora10g> CREATE OR REPLACE PROCEDURE proc_helloworld
      2  IS
      3  BEGIN
      4     DBMS_OUTPUT.put_line ('Hello World! '||chr(10)||'I am a Happy DBA Secooler!');
      5  END;
      6  /

    Procedure created.

    看一下执行效果:
    sec@ora10g> exec proc_helloworld;
    Hello World!
    I am a Happy DBA Secooler!

    PL/SQL procedure successfully completed.

    5.调试。对于非常复杂的存储过程的调试是真正体现个人魅力和能力的地方,往往需要很多的经验,这个急不得,只能慢慢来 Take it easy.
    在sqlplus下调试存储过程时,如果出现错误,时刻使用"show errors"命令查看哪里出错了,例如:
    sec@ora10g> CREATE OR REPLACE PROCEDURE proc_helloworld
      2  IS
      3  BEGIN
      4     DBMS_OUTPUT.put_line ('Hello World!');
      5  END
      6  /

    Warning: Procedure created with compilation errors.

    sec@ora10g> show errors
    Errors for PROCEDURE PROC_HELLOWORLD:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/3      PLS-00103: Encountered the symbol "end-of-file" when expecting
             one of the following:
             ; <an identifier> <a double-quoted delimited-identifier>
             delete exists prior <a single-quoted SQL string>
             The symbol ";" was substituted for "end-of-file" to continue.

    通过提示,问题出现在END后面没有分号结束符号,修改后问题得到处理
    sec@ora10g> CREATE OR REPLACE PROCEDURE proc_helloworld
      2  IS
      3  BEGIN
      4     DBMS_OUTPUT.put_line ('Hello World!');
      5  END;
      6  /

    Procedure created.

    6.删除。使用drop语句删除存储过程
    sec@ora10g> select object_name,object_type,status from user_objects where OBJECT_TYPE='PROCEDURE';

    OBJECT_NAME                    OBJECT_TYPE         STATUS
    ------------------------------ ------------------- -------
    PROC_HELLOWORLD                PROCEDURE           VALID

    sec@ora10g> DROP PROCEDURE proc_helloworld;

    Procedure dropped.

    sec@ora10g> select object_name,object_type,status from user_objects where OBJECT_TYPE='PROCEDURE';

    no rows selected

    7.获取。在维护存储过程的过程中往往需要快速的获取存储过程的SQL创建语句,我经常使用的有如下两种方法
    1).使用DBMS_METADATA包获得
    sec@ora10g> SELECT DBMS_METADATA.get_ddl ('PROCEDURE', 'PROC_HELLOWORLD') from dual;

    DBMS_METADATA.GET_DDL('PROCEDURE','PROC_HELLOWORLD')
    --------------------------------------------------------------------------------

      CREATE OR REPLACE PROCEDURE "SEC"."PROC_HELLOWORLD"
    IS
    BEGIN
       DBMS_OUTPUT.put_line ('Hello World!');
    END;

    2).使用"USER_SOURCE"视图获得,本人推荐使用这种方法查看,格式标准,内容也全面
    sec@ora10g> SELECT text FROM user_source WHERE NAME = 'PROC_HELLOWORLD';

    TEXT
    ------------------------------------------------------------------------------------------------------------------------------
    PROCEDURE proc_helloworld
    IS
    BEGIN
       DBMS_OUTPUT.put_line ('Hello World!');
    END;

    8.小结
    上面的实验,我通过编写、创建、运行、修改、调试、删除和获取七个内容描述了一个存储过程的开发和维护过程。
    试验,实验还有实践,技术工作者永远不变的途径!
    Goodluck Friends!

















    一、过程 (存储过程)

    过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。
    示例1:声明存储过程,该过程返回dept表行数

    DECLARE
    PROCEDURE getDeptCount
    AS
    deptCount INT;
    BEGIN
    SELECT COUNT(*) INTO deptCount FROM DEPT;
    DBMS_OUTPUT.PUT_LINE('DEPT表的共有记录数:'||deptCount);
    END getDeptCount;
    BEGIN
    getDeptCount[()];
    END;
     

    注意:此存储过程getDeptCount只在块运行时有效。
    示例2:创建不带参数的存储过程,该过程返回dept表行数

    CREATE OR REPLACE PROCEDURE getDeptCount
    AS | IS
    deptCount int;
    BEGIN
    SELECT COUNT(*) INTO deptCount FROM dept;
    DBMS_OUTPUT.PUT_LINE('dept表共有'||deptCount||'行记录');
    END [getDeptCount];
     

    当我们创建的存储过程没有参数时,在存储过程名字后面不能有括号。在AS或者IS后至BEGIN之前是声明部分,存储过程中的声明不使用DECLARE关键字。同匿名PL/SQL块一样,EXCEPTION和声明部分都是可选的。
    当我们创建的过程带有错误时,我们可以通过SELECT * FROM USER_ERRORS查看,或者使用SHOW ERRORS [ PROCEDURE Proc_Name]查看。
    使用以下代码可以执行存储过程:

    BEGIN
    getDeptCount;
    END;
    以上存储过程还可以通过以下代码来简化调用:
    EXEC getDeptCount[;] 
    CALL  getDeptCount();
     

    注意:

    并不是所有的存储过程都可以用这种方式来调用
    定义无参存储过程时,存储过程名后不能加()
    在块中或是通过EXEC调用存储过程时可以省略()
    通过CALL调用无参存储过程必须加上()
    示例3:创建带有输入参数的存储过程,该过程通过员工编号打印工资额

    CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo NUMBER)  --参数的数据类型不能指定长度
    AS
    salary emp.sal%TYPE;
    BEGIN
    SELECT SAL INTO salary  FROM EMP WHERE EMPNO=eNo;
    DBMS_OUTPUT.PUT_LINE(eNo||'号员工的工资为'||salary);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
    END;
     

    当定义的存储过程含有参数时,参数的数据类型不能指定长度。参数还有输入和输出之分,本例中没有指定,默认情况为输入参数,也可显示的指定某个参数是输入参数,如(eNo IN NUMBER)。同示例1不同,该例中加入了异常处理。同示例1类似可以使用下面的两种方式调用存储过程:
    BEGIN
    getSalaryByEmpNo(7788);
    END;
    或者
    EXEC getSalaryByEmpNo(7788);  或者
    CALL getSalaryByEmpNo(7788);
    但是如果传给一个存储过程的参数是变量时,必须使用BEGIN  END块,如下:

    DECLARE
    no emp.empNo%TYPE;
    BEGIN
    no:=7788;
    getSalaryByEmpNo(no);
    END;
     

    如果某个包中含有常量,也可以通过如下的方式调用:
    EXEC getSalaryByEmpNo(ConstantPackage.no);
    但这种方式不能再使用CALL调用。
    示例4:创建含有输入和输出参数的存储过程,该过程通过员工编号查找工资额,工资额以输出参数返回

    CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo IN NUMBER,salary OUT NUMBER)
    AS
    BEGIN
    SELECT SAL INTO salary  FROM EMP WHERE EMPNO=eNo;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
    END;
     

    当过程中含有输出参数时,调用时必须通过BEGIN  END块,不能通过EXEC或CALL调用。如:

    DECLARE
    salary NUMBER(7,2);
    BEGIN
    getSalaryByEmpNo(7788,salary);
    DBMS_OUTPUT.PUT_LINE(salary);
    END;
     

    示例5:创建参数类型既是输入参数也是输出参数的过程

    CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(noSalary IN OUT NUMBER)
    AS
    BEGIN
    SELECT SAL INTO noSalary FROM EMP WHERE EMPNO=noSalary;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
    END;
     

    调用如下:

    DECLARE
    no NUMBER(7,2);
    BEGIN
    no:=7788;
    getSalaryByEmpNo(no);
    DBMS_OUTPUT.PUT_LINE(no);
    END;
     

    示例6:创建带有默认值的过程

    CREATE OR REPLACE PROCEDURE addEmp
    (
    empNo NUMBER,
    eName VARCHAR2,
    job  VARCHAR2 :='CLERK',
    mgr  NUMBER,
    hiredate DATE  DEFAULT SYSDATE,
    sal  NUMBER  DEFAULT 1000,
    comm  NUMBER  DEFAULT 0,
    deptNo NUMBER  DEFAULT 30
    )
    AS
    BEGIN
    INSERT INTO emp VALUES(empNo,eName,job,mgr,hiredate,sal,comm,deptNo);
    END;
     

    调用如下:

    EXEC addEmp(7776,'zhangsan','CODER',7788,'06-1月-2000',2000,0,10);  --没有使用默认值
    EXEC addEmp(7777,'lisi','CODER',7788,'06-1月-2000',2000,NULL,10);  --可以使用NULL值
    EXEC addEmp(7778,'wangwu',mgr=>7788);  --使用默认值
    EXEC addEmp(mgr=>7788,empNo=>7779,eName=>'sunliu');  --更改参数顺序
     

    示例7:使用NOCOPY编译提示
    当参数是大型数据结构时,如集合、记录和对象实例,把它们的内容全部拷贝给形参会降低执行速度,消耗大量内存。为了防止这样的情况发生,我们可以使用 NOCOPY提示来让编译器按引用传递方式给IN OUT模式的参数。

    DECLARE
    TYPE DeptList IS TABLE OF VARCHAR2(10);
    dList  DeptList:=DeptList('CORESUN','CORESUN','CORESUN','CORESUN');
    PROCEDURE My_Proc(d IN OUT NOCOPY DeptList)
    AS...
     

    注意:NOCOPY只是一个提示,而不是指令。即使有时候我们使用了NOCOPY,但编译器有可能仍然会进行值拷贝。通常情况下NOCOPY是可以成功的。

    二、维护过程

    1、删除存储过程

    DROP PROCEDURE Proc_Name;
    2、查看过程状态

    SELECT object_name,status  FROM USER_OBJECTS WHERE object_type='PROCEDURE';
     

    3、重新编译过程

    ALTER PROCEDURE Proc_Name COMPILE;
     

    4、查看过程代码

    SELECT * FROM USER_SOURCE WHERE TYPE='PROCEDURE';
     

    三、参数的理解

    -- 输出参数不可以修改解决的方法有两种
    --1 把参数改成输入参数 
    --2 就是参数改成 可输入输出的参数;
    调用过程的 三个方式
    1 就是使用call
    在只用call方式调用函数的时候,必须加要括号,有参数,还要加参数值

    这个方式在命令窗口,调用过程,将不会出现输入的数据.
    2 就是使用exec 命令,进行命令调用过程, 使用命令,就必须在命令行里面输入
    过程名,这个命令窗口中,可加可不加() ,如果有参数的,就一定要加,还有参数值,参数值的类型要与
    变量类型相同.
    3 在语句块中进行调用过程,这个方式和命令模式类似,他们都是可要可不要(),
    -- 在2 和 3 中的 没有括号的情况是,过程没有参数 ,如果有,就必须要有()
    输出参数的特点
    1 一个过程中,如果有输出参数(OUT 参数),在调用过程的使用,也要传入一个参数, 这个参数可以不用在调用的地方
    进行赋值,就直接传入一个声明好的一个变量,用来接受存储过程中的输出参数的值(OUT 参数)
    2 输入参数 值不可以改变在过程中,
    注意: 在存储过程中,他的参数类型不可以设置它的大小 ; 
    例如;

    CREATE OR REPLACE PROCEDURE hello(
    p_name IN VARCHAR2(12),
    p_age OUT NUMBER(10,2)
    )
    IS
    BEGIN 
    如果有输出参数就必须有有一个参数进行接收 ;

    CREATE OR REPLACE PROCEDURE hello(
    p_name IN VARCHAR2,
    p_age OUT emp.sal%TYPE
    )
    IS
    BEGIN
    SELECT emp.sal + 3131 INTO p_age FROM emp WHERE empno = 7788 ;
    dbms_output.put_line( p_age);
    END ;
    --------- 块中调用方法

    DECLARE
    v_nanme varchar2(12);
    v_age NUMBER (12,2);
    BEGIN
    hello (v_nanme,v_age);
    dbms_output.put_line(v_age);
    END ;
    -- 在这个过程中 传入的v_age 就是接受 存储过程输出参数的值 ; 类似于Java的中的返回值

    -- 理解 in out 参数

    CREATE OR REPLACE PROCEDURE hello1 (
    p_name IN OUT emp.ename%TYPE
    )
    IS
    BEGIN
    -- SELECT emp.ename INTO p_name FROM emp ;
    p_name:='a;sk , ' || p_name ;
    END ;
    --------------------------------------------------------------------------
    DECLARE
    v_nanme varchar2(12);
    BEGIN 
    v_nanme:='12312';
    hello1(v_nanme);
    补充:sqlplus中执行含有输出参数为游标的存储过程

    实例6:

    sqlplus创建存储过程,使用如下:

    SQL>create or replace procedure test1(rcursor out sys_refcursor) as
    begin
      open rcursor for
      select decode(row_number() over(partition by deptno order by ename),
                      1,
                       deptno,
                       null) deptno,
                 t.ename
            from scott.emp t;
    end;
    /
    --使用sqlplus执行上面创建的带有游标输出参数的存储过程

    SQL> var cur refcursor
    SQL> exec test1(:cur);
    PL/SQL procedure successfully completed.
    SQL> print cur;
        DEPTNO ENAME
    ---------- ----------
            10 CLARK
               KING
               MILLER
            20 ADAMS
               FORD
               JONES
               SCOTT
               SMITH
            30 ALLEN
               BLAKE
               JAMES

        DEPTNO ENAME
    ---------- ----------
               MARTIN
               TURNER
               WARD

    14 rows selected.














    简要记录存储过程语法与Java程序的调用方式

      一 存储过程

        首先,我们建立一个简单的表进行存储过程的测试

    create table 
    xuesheng(id integer, xing_ming varchar2(25), yu_wen number, shu_xue number);

    insert into xuesheng values(1,'zhangsan',80,90)
    insert into xuesheng values(2,'lisi',85,87)
    1)无返回值的存储过程

    create or replace procedure xs_proc_no is
    begin
      insert into xuesheng values (3, 'wangwu', 90, 90);
      commit;
    end xs_proc_no;
    2)有单个数据值返回的存储过程

    复制代码
    create or replace procedure xs_proc(temp_name in varchar2,
                                        temp_num  out number) is
      num_1 number;
      num_2 number;
    begin
      select yu_wen, shu_xue
        into num_1, num_2
        from xuesheng
       where xing_ming = temp_name;
      --dbms_output.put_line(num_1 + num_2);
      temp_num := num_1 + num_2;
    end;
    复制代码
    其中,以上两种与sql server基本类似,而对于返回数据集时,上述方法则不能满足我们的要求。在Oracle中,一般使用ref cursor来返回数据集。示例代码如下:

    3)有返回值的存储过程(列表返回)

    首先,建立我们自己的包。并定义包中的一个自定义ref cursor

    create or replace package mypackage as
      type my_cursor is ref cursor;
    end mypackage;
    在定义了ref cursor后,可以书写我们的程序代码

    create or replace procedure xs_proc_list(shuxue   in number,
                                             p_cursor out mypackage.my_cursor) is
    begin
      open p_cursor for
        select * from xuesheng where shu_xue > shuxue;
    end xs_proc_list;
     二、程序调用

    在本节中,我们使用java语言调用存储过程。其中,关键是使用CallableStatement这个对象,代码如下:

    String oracleDriverName = "oracle.jdbc.driver.OracleDriver";
     
            // 以下使用的Test就是Oracle里的表空间
            String oracleUrlToConnect = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
            Connection myConnection = null;
            try {
                Class.forName(oracleDriverName);
            } catch (ClassNotFoundException ex) {
                ex.printStackTrace();
            }
            try {
                myConnection = DriverManager.getConnection(oracleUrlToConnect,
                        "xxxx", "xxxx");//此处为数据库用户名与密码
     
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            try {
                 
                CallableStatement proc=null;
                proc=myConnection.prepareCall("{call xs_proc(?,?)}");
                proc.setString(1, "zhangsan");
                proc.registerOutParameter(2, Types.NUMERIC);
                proc.execute();
                String teststring=proc.getString(2);
                System.out.println(teststring);
     
            } catch (Exception ex) {
                ex.printStackTrace();
            }
    对于列表返回值的存储过程,在上述代码中做简单修改。如下

    复制代码
    CallableStatement proc=null;
                proc=myConnection.prepareCall("{call getdcsj(?,?,?,?,?)}");
                proc.setString(1, strDate);
                proc.setString(2, jzbh);
                proc.registerOutParameter(3, Types.NUMERIC);
                proc.registerOutParameter(4, OracleTypes.CURSOR);
                proc.registerOutParameter(5, OracleTypes.CURSOR);
                proc.execute();
                ResultSet rs=null;
                int total_number=proc.getInt(3);
                rs=(ResultSet)proc.getObject(4);
    复制代码
    上述存储过程修改完毕。另外,一个复杂的工程项目中的例子:查询一段数据中间隔不超过十分钟且连续超过100条的数据。即上述代码所调用的getdcsj存储过程

    create or replace procedure getDcsj(var_flag     in varchar2,
                                        var_jzbh     in varchar2,
                                        number_total out number,
                                        var_cursor_a out mypackage.my_cursor,
                                        var_cursor_b out mypackage.my_cursor) is
      total number;
      cursor cur is
        select sj, flag
          from d_dcsj
         where jzbh = var_jzbh
         order by sj desc
           for update;
      last_time date;
    begin
      for cur1 in cur loop
        if last_time is null or cur1.sj >= last_time - 10 / 60 / 24 then
          update d_dcsj set flag = var_flag where current of cur;
          last_time := cur1.sj;
        else
          select count(*) into total from d_dcsj where flag = var_flag;
          dbms_output.put_line(total);
          if total < 100 then
            update d_dcsj set flag = null where flag = var_flag;
            last_time := null;
            update d_dcsj set flag = var_flag where current of cur;
          else
            open var_cursor_a for
              select *
                from d_dcsj
               where flag = var_flag
                 and jzbh = var_jzbh
                 and zh = 'A'
               order by sj desc;
            number_total := total;
            open var_cursor_b for
              select *
                from d_dcsj
               where flag = var_flag
                 and jzbh = var_jzbh
                 and zh = 'B'
               order by sj desc;
            number_total := total;
            exit;
          end if;
        end if;
      end loop;
      select count(*) into total from d_dcsj where flag = var_flag;
      dbms_output.put_line(total);
      if total < 100 then
        open var_cursor_a for
          select * from d_dcsj where zh = 'C';
        open var_cursor_b for
          select * from d_dcsj where zh = 'C';
      else
        open var_cursor_a for
          select *
            from d_dcsj
           where flag = var_flag
             and jzbh = var_jzbh
             and zh = 'A'
           order by sj desc;
        number_total := total;
        open var_cursor_b for
          select *
            from d_dcsj
           where flag = var_flag
             and jzbh = var_jzbh
             and zh = 'B'
           order by sj desc;
        number_total := total;
      end if;
      commit;
    end;
    /











    关于 游标 if,for 的例子 
    create or replace procedure peace_if 
    is 
    cursor var_c is select * from grade; 
    begin 
    for temp in var_c loop 
    if temp.course_name = 'OS' then 
    dbms_output.put_line('Stu_name = '||temp.stu_name); 
    elsif temp.course_name = 'DB' then 
    dbms_output.put_line('DB'); 
    else 
    dbms_output.put_line('feng la feng la '); 
    end if; 
    end loop; 
    end; 
    ---关于游标 for,case 的例子1 
    create or replace procedure peace_case1 
    is 
    cursor var_c is select * from test_case; 
    begin 
    for temp in var_c loop 
    case temp.vol 
    when 1 then 
    dbms_output.put_line('haha1'); 
    when 2 then 
    dbms_output.put_line('haha2'); 
    when 3 then 
    dbms_output.put_line('haha3'); 
    when 4 then 
    dbms_output.put_line('haha4'); 
    else 
    dbms_output.put_line('qita'); 
    end case ; 
    end loop; 
    end; 
    ---关于游标 for,case 的例子2 
    create or replace procedure peace_case2 
    is 
    cursor var_c is select * from test_case; 
    begin 
    for temp in var_c loop 
    case 
    when temp.vol=1 then 
    dbms_output.put_line('haha1'); 
    when temp.vol=2 then 
    dbms_output.put_line('haha2'); 
    when temp.vol=3 then 
    dbms_output.put_line('haha3'); 
    when temp.vol=4 then 
    dbms_output.put_line('haha4'); 
    else 
    dbms_output.put_line('qita'); 
    end case ; 
    end loop; 
    end; 
    ---关于for 循环的例子 
    create or replace procedure peace_for 
    is 
    sum1 number :=0; 
    temp varchar2(500); 
    begin 
    for i in 1..9 loop 
    temp := ''; 
    for j in 1 .. i 
    loop 
    sum1 := i * j; 
    temp := temp||to_char(i) || ' * ' ||to_char(j) ||' = ' ||to_char(sum1) ||' '; 
    end loop; 
    dbms_output.put_line(temp ); 
    end loop; 
    end; 
    ---关于 loop循环的例子 
    create or replace procedure peace_loop 
    is 
    sum1 number := 0; 
    temp number :=0 ; 
    begin 
    loop 
    exit when temp >= 10 ; 
    sum1 := sum1+temp; 
    temp := temp +1; 
    end loop; 
    dbms_output.put_line(sum1 ); 
    end; 

    ---关于游标和loop循环的例子 
    create or replace procedure loop_cur 
    is 
    stu_name varchar2(100); 
    course_name varchar2(100); 
    cursor var_cur is select * from grade ; 
    begin 
    open var_cur; 
    loop 
    fetch var_cur into stu_name,course_name; 
    exit when var_cur%notfound; 
    dbms_output.put_line(stu_name|| course_name); 
    end loop; 
    close var_cur; 
    end; 
    ---关于异常处理的例子 
    create or replace procedure peace_exp(in1 in varchar2) 
    is 
    c_n varchar2(100); 
    begin 
    select course_name into c_n from grade where stu_name = in1; 
    dbms_output.put_line(c_n); 
    exception 
    when no_data_found 
    then 
    dbms_output.put_line('try'); 
    when TOO_MANY_ROWS 
    then 
    dbms_output.put_line('more'); 
    end; 

    ---关于异常处理的例子2 
    create or replace procedure peace_insert ( c_n in varchar2) 
    is 
    error EXCEPTION; 
    begin 
    if c_n = 'OK' 
    then 
    insert into course (course_name) values (c_n); 
    elsif c_n = 'NG' then 
    insert into course (course_name) values (c_n); 
    raise error; 
    else 
    Dbms_Output.put_line('c_n' || c_n); 
    end if; 
    commit; 
    exception 
    when error then 
    rollback; 
    Dbms_Output.put_line('ERRO'); 
    end; 
    ---关于包的例子 定义包 
    create or replace package peace_pkg 
    as 
    function test1(in1 in varchar2) 
    return number; 
    procedure test2 (in2 in varchar2); 
    end peace_pkg; 
    ---关于包的例子 定义包体 
    create or replace package body peace_pkg 
    as 
    function test1(in1 in varchar2) 
    return number 
    as 
    temp number; 
    begin 
    temp := 0; 
    return temp; 
    end; 
    procedure test2 (in2 in varchar2) 
    is 
    begin 
    dbms_output.put_line(in2); 
    end; 
    end peace_pkg; 
















    用了两年Oracle还没写过存储过程,真是十分惭愧,从今天开始学习Oracle存储过程,完全零起点,争取每日一篇学习笔记,可能开始认识的不全面甚至有错误,但坚持下来一定会有收获。
    1. 建立一个存储过程
             create or replace PROCEDURE firstPro
             IS
             BEGIN
             DBMS_OUTPUT.PUT_LINE('Hello World!');
             END;
    其中IS关键字替换为AS关键字结果不会出现任何变化,大多认为他们是等同的,但也有一种说法解释为:一般PACKAGE 或者单独的FUNCTION, PROCEDURE 都用AS,PACKAGE 中的FUNCTION, PROCEDURE 用IS。
             DBMS_OUTPUT.PUT_LINE('Hello World!'); 是一个输出语句。
    2. 执行存储过程
    Oracle返回结果需要使用包,那么存储过程似乎只能在数据库中执行或被其他调用,编程语言似乎并不能直接调用存储过程返回数据,是否能执行他有待研究。那么首先在数库中执行上面的存储过程。
             BEGIN
             FirstPro();//注意有括号
             END;
           运行后输出Hello World。
    3. 下面写一个稍复杂的存储过程,他定义了变量,进行了运算,输出一个count操作所用的时间。
           CREATE OR REPLACE procedure testtime  
          is  
          n_start   number;  
          n_end   number; 
          samplenum number;
          use_time number;
          begin  
                 n_start:=dbms_utility.get_time;  
                 select count(*) into samplenum from emp; 
                 n_end:=dbms_utility.get_time;  
                 use_time:=   n_end   -   n_start;  
                 dbms_output.put_line('This   statement   cost   '|| use_time ||'   miliseconds');  
          end;  
    4. 下面试验下怎么能给存储过程赋值
           CREATE OR REPLACE procedure test(num in number) is
           begin
                  dbms_output.put_line('The input numer is:' || num);
           end ;
           今天的就到这,明天将调用这个存储过程,并试验一写对表的操作。
    1. 首先把昨天带参的存储过程执行一下
           declare
           n number;
           begin
                  n:=1;
                  test(num=>n);
           end;
           注;在调用存储过程时,=>前面的变量为存储过程的形参且必须于存储过程中定义的一致,而=>后的参数为实际参数。当然也不可以不定义变量保存实参,可写成如下形式:
           Begin
                  test(num=>1);
           end;
           这样我们就能更清楚得看到给存储过程赋值的格式了。后面打算用存储过程操作一些表,按照增删改查的顺序依次建立存储过程。
    2. 插入
           CREATE OR REPLACE
           procedure proc_test_Insert_Single(e_no in number,e_name in varchar ,s in varchar,d in               varchar)
           as
           begin
                  insert into emp (emp_id,emp_name,salary,birthday) values (e_no,e_name,s,d);
           end;
           调用:
           DECLARE
           i NUMBER;
           n varchar(5);
                 s varchar(11);
          d varchar(10);
           BEGIN
                 i:=10;
                 n := 'text11';
                 s:='3998';
                 d:='1998-02-02';
                 PROc_TEST_Insert_single(e_no => i,e_name=>n,s=>s,d=>d);
           END;
           注:调用存储过程声明varchar时,必须限定长度,即斜体的部分不能少。同时如果给变量赋值时大于限定的长度了,则会提示ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小。
     
    3. 更新
           create or replace procedure proc_test_update_Single(e_no in number,s in varchar)
           as
           begin
                  UPDATE emp set salary =s where emp_id=e_no;
           end;
           调用:
           DECLARE
                 n NUMBER;
                 s varchar(11);
           BEGIN
                 n := 2;
                 s:=3998;
                 PROc_TEST_UPdate_single(e_no => n,s=>s);
           END;
    4. 号外,今天在开发过程中正好有个数据库更新操作可用存储过程实现,顺便练习一下,需求是将一个表中的ID字段,查出来更新到另一个表中,两个表通过b_bs和b_kgh关联。存储过程如下:
           create or replace procedure update_yygzdbid
           as
                 bs varchar(20);
                 kgh varchar(20);
                 bid number;
                 cursor c_db is select b_id,b_bs,b_kgh from pmdcdb;
           begin
                 for temp in c_db loop
              update yygz_db set b_id= temp.b_id where g_bs=temp.b_bs and g_bh=temp.b_kgh;
                 end loop;
           end;
           运行这个存储过程:
           Begin
                  update_yygzdbid();
           end;
           说明:
           (1).在没有参数的存储过程定义时存储过程的名称不需要括号,写成update_yygzdbid()是错误的,
           (2). cursor c_db是定义一个游标,获得查询语句的结果集,
           (3). For temp in c_bd loop
                         Begin
                         End;
                  End loop
                  是循环游标,其形式类似于C#中的foreach,  获得字段:temp.b_id。
    5. 查询
           最后我们做一个查询的存储过程,能够返回一个值,注意不是结果集,结果集是明天的目标。
           CREATE OR REPLACE
           procedure proc_test_Select_Single(t in varchar,r out varchar )
           as
           begin
                  select salary into r from emp where emp_name=t;
           end;
           这个存储过程使用了2个参数,并分别出现了IN和OUT,in代表输入,out用于输出,从下面的语句也可以看到salary写入到变量r中了,这个r我们可以在调用存储过程后得到。
           这时编译后会出现一个Warning(1,48): PLW-07203: 使用 NOCOPY 编译器提示可能对参数 'R' 有所帮助,那么nocopy是什么呢,nocopy主要是针对in|out record/index-by table/varray/varchar2提高效率使用的, 对于number使用nocopy与否基本没有影响.所以在'enable:performance'情况下不会对number提示warning.
           我们把第一行改为:procedure proc_test_Select_Single(t in varchar,r out nocopy varchar )
    现在即使对in的varchar没有使用nocopy也不会提示警告,
           DECLARE
                 T varchar2(4);
                 R VARCHAR2(4);
           BEGIN
                 T := 'zz';
                 PROC_TEST_SELECT_SINGLE(T => T,R => R );
                 DBMS_OUTPUT.PUT_LINE('R = ' || R);
           END;
           运行后即可在输出中看到结果了。
    三、
    1. 今天我们首先写一个涨工资的存储过程,给每个低于5k工资的人涨点钱。
           CREATE OR REPLACE PROCEDURE p_test(forRaise in number)
           as
        begin
          for v_emp in (select * from emp) loop
              if(v_emp.salary<'5000') then
            update emp set salary =(v_emp.salary+forRaise) where emp_id=v_emp.emp_id;
            end if;
          end loop;
           end;
           调用:
           DECLARE
                 FORRAISE NUMBER;
           BEGIN
                 FORRAISE :=1;
                  P_TEST(FORRAISE => FORRAISE);
           END;
           这里要注意两个地方:
    (1)       循环中begin和end不是必须的
    (2)       这里增加了if语句,其格式比较简单就不细说了。
    (3)       这里没有定义游标,在游标的位置直接用select语句代替了。
           2. 这里顺便介绍下另外一种循环,while循环,实现同样的功能
                  CREATE OR REPLACE PROCEDURE p_test(forRaise in number)
                  as
                         cursor c is select * from emp;
                         v_row emp%rowtype;
                  begin
                 open c;
                 fetch c into v_row;
                 while  c%found Loop
                      if(v_row.salary<'5000') then
                            update emp set salary =(v_row.salary+forRaise) where emp_id=v_row.emp_id;
                      end if;
               fetch c into v_row;
                 end loop;
                 close c;
                  end;
                  说明:
    (1)       这里需要定义一个游标,还要定义一个emp%rowtype类型的变量,%前面是表名,后面表示这个表的一行,
    (2)       在使用游标前还要显示的打开游标,并将其赋值到row中,使用后关闭游标。
    (3)       C%found表示只有row中有值的时候才会进行循环。
    (4)       经过对比发现于while循环相比,for循环更像是C#中的foreach,使用起来方便很多。
    (5)       另从9i开始提供的动态游标类型sys_refcursor,以前的版本必须要先创建一个ref cursor的类型,现在多个
    3.        现在我们使用程序调用下涨工资的存储过程,这个存储过程是没有返回值的。
    OracleConnection conn = new OracleConnection();   //创建一个新连接
    conn.ConnectionString = "Data Source='ds';user id='id ';password='pwd';"; OracleCommand cmd = new OracleCommand("P_TEST", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    OracleParameter p1 = new OracleParameter("forRaise", OracleType.UInt32);
    p1.Value = 1;
    p1.Direction = System.Data.ParameterDirection.Input;
    cmd.Parameters.Add(p1);
    conn.Open();
    int r=cmd.ExecuteNonQuery();
        conn.Close();
        这样我们就可以给员工涨工资了,说明:
    (1)         虽然给多个人涨了公司,但r的值是1,他只调用了1个存储过程,或者说受影响的只是1个。
    (2)         参数P1的名字必须和存储过程中的一样否则会提示:调用 'P_TEST' 时参数个数或类型错误。
    4.         现在我们试着从存储过程中得到点结果吧,我先看看我给几个人涨了工资,我每个月一共要多付多少钱了。
             改动存储过程:
             CREATE OR REPLACE PROCEDURE p_test(forRaise in number,res out number)
             is
        begin
          res:=0;
          for v_emp in (select * from emp) loop
                 if(v_emp.salary<'4000') then
              update emp set salary =(v_emp.salary+forRaise) where emp_id=v_emp.emp_id;
              res:=res+1;
            end if;
          end loop;
             end;
             增加了一个out 的number型,记录改动的次数。然后相应的调整C#程序,获得这个改动的次数。
    OracleCommand cmd = new OracleCommand("P_TEST", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    OracleParameter p1 = new OracleParameter("forRaise", OracleType.UInt32);
    p1.Value = 4;
    p1.Direction = System.Data.ParameterDirection.Input;
    OracleParameter p2 = new OracleParameter("res", OracleType.UInt32);
    p2.Value = 10;
    p2.Direction = System.Data.ParameterDirection.Output;
    cmd.Parameters.Add(p1);
    cmd.Parameters.Add(p2);
    conn.Open();
    int r=cmd.ExecuteNonQuery();
     conn.Close();
    MessageBox.Show(“你已经给:”+p2.Value.ToString()+“人涨了工资”);
    好了,今天就到这,下次返回数据集。
    Oracle使用存储过程返回结果集必须使用包,包包括包头和包体两部分,包头是定义部分包体是具体的实现
        包头:
        CREATE OR REPLACE
        PACKAGE pkg_test_select_mul
        AS
        TYPE myrctype IS REF CURSOR; 
        PROCEDURE proc(s number, res OUT myrctype);
    END pkg_test_select_mul;
        这里定义了个一个游标和一个存储过程。
        包体:
        CREATE OR REPLACE
        PACKAGE BODY "PKG_TEST_SELECT_MUL" AS
            PROCEDURE proc(s in number,res OUT myrctype)
            IS       
            BEGIN       
              OPEN res FOR Select  emp_id,emp_Name, salary,birthday From            emp where salary> s;
            END proc;   
        END PKG_TEST_SELECT_MUL;
        这里实现里包头中定义的存储过程,实现了查询工资超过一定数额的人的信息,而游标则不用重新定义了,且存储过程中的参数名必须和定义中的一致。下面我们看一下C#的调用部分。
        OracleConnection conn = new OracleConnection();   //创建一个新连接
                conn.ConnectionString = "Data Source='" + "MyTest" + "';user id='" + "azkaser" + "';password='" + "sti" + "';";   //写连接串 
                OracleCommand cmd = new OracleCommand("PKG_TEST_SELECT_MUL.proc", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter p1 = new OracleParameter("s", OracleType.Number);
                p1.Value = 4000;
                p1.Direction = ParameterDirection.Input;
                OracleParameter p2 = new OracleParameter("res", OracleType.Cursor);
                p2.Direction = ParameterDirection.Output;
     
                cmd.Parameters.Add(p1);
                cmd.Parameters.Add(p2);
                conn.Open();
                OracleDataReader myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    MessageBox.Show(myReader.GetString(1));
                }
                conn.Close();
        程序将得到的结果存放在OracleDataReader的对象中。
        到此简单的Oracle存储过程操作就此就全部完成了,程序写的很随便,目的就是实现功能,将来有时间会进一步
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
    语句块定义:
    Sql代码  收藏代码
    decalre  
    -- 变量声明  
    var1 number(2);                -- 仅声明  
    var2 char(2) := '11';          -- 在声明的同时初始化  
      
    begin  
            -- 语句  
    end; -- 语句块结束  
     
    if 语句
    Sql代码  收藏代码
    if a = 1 or b = 2 then  
      
    elsif c = 3 then  
      
    else  
      
    end if;  
     
     
    case 语句
    case语句如果作为分支控制语句,最后结束语句是end case,如果是作为select语句里的控制语句则只需要end。
    Sql代码  收藏代码
    declare  
    num number(10) := 1;  
    begin  
        case   
            when num = 0 then dbms_output.put_line( 'zero');  
            when num = 1 then dbms_output.put_line( 'one');  
            else  dbms_output.put_line( 'default');  
        end case;  
          
        case num  
            when 0 then dbms_output.put_line( 'zero');  
            when 1 then dbms_output.put_line( 'one');  
            else  dbms_output.put_line( 'default');  
        end case;  
    end;  
     
    for 循环
    for循环主要有两个用处。

    1、 循环一个范围
    格式:for i in [start .. end] loop ... end loop;
    Sql代码  收藏代码
    for i in 0..9 loop  
        dbms_output.put_line('i:' || i);  
    end loop;  
     

    2、遍历隐式游标

    隐式游标的好处是不需要手动关闭,方便
    Sql代码  收藏代码
    for currow in (  
       select t.col1, t.col2  
       from tableName t  
       where ...  
    ) loop  
        if currow.col1 = 0 then  
           return;    -- 中止sp,返回  
       end if;  
    end loop;  
     
    while 循环
    Sql代码  收藏代码
    isok := 9;  
    while isok >= 0 loop  
          isok := isok - 1;  
           
          if isok = 8 then  
             continue;                -- 与编程语言的 continue 语义一样,跳过当前循环的剩余语句,回到循环开始  
          end if;  
           
          if isok = 4 then  
             exit;                    -- 与编程语言的 break 语义一样,跳出循环  
          end if;  
      
          dbms_output.put_line('isok:' || isok);  
    end loop;  
      
    dbms_output.put_line('outside while loop .');  
     
    存储过程定义
    Sql代码  收藏代码
    create or replace procedure sp_name (  
            -- 入参、出参列表, 逗号分隔。  
            uid in varchar2,                          -- 不能带长度信息  
            startDate in date,                        -- 第二个输入参数  
            defaultVar in varchar2 default "",        -- 默认参数,如果不传,要注意参数的顺序  
            isok out number,                          -- 输出参数  
            result out varchar2                       -- 第二个输出参数  
    )  
    as  
    -- 变量声明,每个声明用分号结束。可以在声明的同时初始化  
    var1 varchar2(11);  
    var2 number(2) := 123;  
      
    begin  
            -- 字符串拼接用 ||  
            dbms_output.put_line('isok:' || 'abc');  
             
            -- 调用其他存储过程  
            sub_sp_name(param1, prarm2, outParam1, outParam2);  
      
    end;        -- 存储过程结束  
     
    函数定义
    Sql代码  收藏代码
    create or replace function func  (  
            -- 入参、出参列表, 逗号分隔。  
            uid in varchar2,                          -- 不能带长度信息  
            startDate in date,                        -- 第二个输入参数  
            defaultVar in varchar2 default "",        -- 默认参数,如果不传,要注意参数的顺序  
            isok out number,                          -- 输出参数  
            result out varchar2                       -- 第二个输出参数  
    )  
    return number      -- 定义返回类型  
    as  
    -- 变量声明,每个声明用分号结束。可以在声明的同时初始化  
    var1 varchar2(11);  
    var2 number(2) := 123;  
      
    begin  
            -- 字符串拼接用 ||  
            dbms_output.put_line('isok:' || 'abc');  
             
      
            return ret_val;  
    end;  
     
    存储过程与函数异同
    1、两者定义类似,都可以带输入输出参数。
    2、函数有返回值,存储过程没有。
    3、函数的调用要在select语句里;而存储过程不用,可以独立调用。
    游标
    隐式游标 
    隐式游标的好处是不需要手动关闭,方便
    Sql代码  收藏代码
    for currow in (  
       select t.col1, t.col2  
       from tableName t  
       where ...  
    ) loop  
        if currow.col1 = 0 then  
           return;    -- 中止sp,返回  
       end if;  
    end loop;  
     
    显式游标
    Sql代码  收藏代码
    declare  
    isok integer;  
    v_event_id number(10);  
    v_isagain number(2);  
    v_rate number(2);  
      
    v_sender char(11) := '13800138000';  
      
    cursor cursorVar is select event_id, isagain, rate from call_event where sender = v_sender; -- 声明游标  
      
      
    begin  
        open cursorVar;    -- 打开游标  
        loop  
             fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值  
             exit when cursorVar%notfound;                             --当没有记录时退出循环  
             dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);  
        end loop;  
         
        close cursorVar;   -- 关闭游标  
         
        --游标的属性有:%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT;   
        --%FOUND:已检索到记录时,返回true   
        --%NOTFOUNRD:检索不到记录时,返回true   
        --%ISOPEN:游标已打开时返回true   
        --%ROWCOUNT:代表检索的记录数,从1开始   
    end;  
     
    带参数游标
    Sql代码  收藏代码
    declare  
    isok integer;  
    v_event_id number(10);  
    v_isagain number(2);  
    v_rate number(2);  
      
    v_sender char(11) := '13800138000';  
      
    cursor cursorVar(p_sender varchar2) is select event_id, isagain, rate from call_event where sender = p_sender; -- 声明游标  
      
    begin  
        open cursorVar(v_sender);    -- 打开游标,在括号里传参。  
        loop  
             fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值  
             exit when cursorVar%notfound;                             --当没有记录时退出循环  
             dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);  
        end loop;  
         
        close cursorVar;   -- 关闭游标  
    end;  




  • 相关阅读:
    tabbar 旋转指定的页面
    GDAL中文路径不能打开&Shp文件字段属性值中文乱码
    Project : error PRJ0019: 工具从"Moc'ing xxx.h..."
    详解Android中的屏幕方向
    qt中获取文件路径和文件名
    vs2005下Qt项目中修改exe图标的方法
    Qt & C/C++统计运行时间
    Qt 中Treewidget添加右键菜单
    QT 中文乱码解决方案
    Qt多线程应用QRunnable显示进度条示例
  • 原文地址:https://www.cnblogs.com/xiaoxiao5ya/p/760aa0f4516356e837940ad6c759ac46.html
Copyright © 2020-2023  润新知