存储过程培训

存储过程：


conn scott/tiger;


1.基本结构 
CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN 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;


---------------


CREATE OR REPLACE PROCEDURE pro_test1
(
    c1 IN NUMBER
) IS
b1 number :=0;
b2 DATE;
BEGIN
     b2:=to_date('1990-11-20','yyyy-mm-dd');
     select comm INTO b1 from emp where 1>2;/*没数据*/
     select comm INTO b1 from emp where empno='7369';/*null值*/
     DBMS_OUTPUT.put_line( b1 || '有数据'|| b2 );/*null值也算有数据*/
EXCEPTION
  WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.put_line( b1 || '没有数据'|| b2 );
END pro_test1;


------------------


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




CREATE OR REPLACE PROCEDURE pro_test1
(
    c1 IN NUMBER
) IS
b1 number :=0;
BEGIN
     select comm INTO b1 from emp where empno=7369;
     IF b1 is null THEN
           BEGIN 
                 DBMS_OUTPUT.put_line( b1 || '存在数据'|| to_char(sysdate,'yyyy-mm-dd') );
           END;
     END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.put_line( b1 || '没有数据'|| to_char(sysdate,'yyyy-mm-dd') );
END pro_test1;




------------------


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




create table test_1 (id int ,name varchar(30));


CREATE OR REPLACE PROCEDURE pro_test1(c1 IN varchar2) IS
  V_TEST number := 0;
  V_FH   varchar(30);
BEGIN
     V_FH:=c1;
  WHILE V_TEST < 10 LOOP
    BEGIN
        INSERT INTO test_1 values (V_TEST,V_FH);
        V_TEST:=V_TEST+1;
          IF 0=mod(V_TEST,9) THEN
             BEGIN 
               V_FH:='CHINA';
             END;
          END IF;
    END;
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line(V_TEST || '没有数据' ||
                         to_char(sysdate, 'yyyy-mm-dd'));
END pro_test1;




----------------------




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;




CREATE OR REPLACE PROCEDURE pro_test1(k1 IN varchar2) IS
  cursor c1 is select empno,ename,job,sal from emp where deptno in (20,30);
  n1 number;
begin
     for v1 IN c1 loop
         dbms_output.put_line(k1 || v1.empno || ',' || v1.ename || v1.job || v1.sal );
         n1 := c1%rowcount;
     end loop;
     dbms_output.put_line(n1);
end  pro_test1 ;




-------------------------


9、简单实例，通过DBMS_OUTPUT来看结果


CREATE OR REPLACE PROCEDURE pro_hp (lic_para IN VARCHAR2,out_para OUT VARCHAR2)
AS
   temp   VARCHAR2 (100);
BEGIN
   SELECT ename
     INTO temp
     FROM emp
    WHERE empno = lic_para;
    out_para:=temp;
    DBMS_OUTPUT.put_line (out_para);
END pro_hp;


下面是调用：


  过程调用方式一
declare
  realempno emp.empno%type;
  realname  varchar(40);
begin
  realempno := 7369;
  realname  := '';
  pro_hp(realempno, realname); --必须按顺序
  DBMS_OUTPUT.PUT_LINE(realempno || '   ' || realname);
END;
 


  过程调用方式二
declare
  realempno emp.empno%type;
  realname  varchar(40);
begin
  realempno := 7369;
  realname  := '';
  pro_hp(lic_para => realempno, out_para => realname); --指定值对应变量顺序可变
  DBMS_OUTPUT.PUT_LINE(realempno || '   ' || realname);
END;






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








(step into )位置的按钮才是关键——单步执行，就是让代码一行一行的执行，(step over)位置的按钮是跳出单步执行，等待下一个指令。