oracle-2 存储过程的写法

参考:
  http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html
   http://blog.csdn.net/liyong199012/article/details/8948952
SET SERVEROUTPUT ON  
        DECLARE    
         v_ename VARCHAR2(10);   
         v_job VARCHAR2(10);
         v_sal number;        
         CURSOR emp_cursor IS    
         SELECT ename,job,sal FROM emp order by sal desc;
         BEGIN  
     OPEN emp_cursor;
    for i in 1..10 loop    
    FETCH emp_cursor INTO v_ename,v_job,v_sal;   
        DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job||','||v_sal);   
        end loop;
        CLOSE emp_cursor;   
        END;  
        
        
        declare
 cursor emp_sur is
 select * from emp;
 begin
 for recod in emp_sur loop
  DBMS_OUTPUT.PUT_LINE(recod.empno||'#'||recod.ename||'#'||recod.job);
 end loop;
 end;
 
 
 declare
 cursor csr_dept
 is
 select job,ename from emp;
 begin
 for row_dept in csr_dept loop
 dbms_output.put_line('部门名称:'||row_dept.ename);
 end loop;
 end;
 
 1:
 CREATE OR REPLACE PROCEDURE test_oracle(p_id IN NUMBER)
IS
v_ename VARCHAR2(10);
v_job  VARCHAR2(10);
v_sal NUMBER(10);
CURSOR user_gz IS
SELECT ename,job,sal FROM emp WHERE empno = p_id;
BEGIN
     OPEN user_gz;
   --  {FOR i IN 1..3 LOOP
   --  FETCH user_gz  INTO v_ename,v_job,v_sal;
   --  dbms_output.put_line(v_ename||'#'||v_job||'#'||v_sal);
   --  END LOOP;}
   IF user_gz%ISOPEN THEN LOOP
      FETCH user_gz INTO v_ename,v_job,v_sal;
      dbms_output.put_line(v_ename||'#_#'||v_job||'#_#'||v_sal);
   END LOOP;
    ELSE
       dbms_output.put_line(v_ename||'#'||v_job||'#'||v_sal);
     END IF;
     CLOSE user_gz;  
END test_oracle;
2:
CREATE OR REPLACE PROCEDURE test_oracle(p_id IN NUMBER)
IS
v_ename VARCHAR2(10);
v_job  VARCHAR2(10);
v_sal NUMBER(10);
CURSOR user_gz IS
SELECT ename,job,sal FROM emp WHERE empno = p_id;
BEGIN
     OPEN user_gz;
   IF user_gz%ISOPEN THEN
     LOOP
      FETCH user_gz INTO v_ename,v_job,v_sal;
      EXIT WHEN user_gz%NOTFOUND;
      dbms_output.put_line(v_ename||'#_#'||v_job||'#_#'||v_sal);
   END LOOP;
    ELSE
       dbms_output.put_line(v_ename||'#'||v_job||'#'||v_sal);
     END IF;
     CLOSE user_gz;  
END test_oracle;








declare
v_ename VARCHAR2(10);
v_comm  NUMBER(10);
v_sal NUMBER(10);
CURSOR user_gz IS
SELECT distinct(a.sal),a.comm,b.ename FROM han a,emp b WHERE a.sal=b.sal;
BEGIN
    OPEN user_gz;
    IF user_gz%ISOPEN THEN
       LOOP
         FETCH user_gz INTO v_sal,v_comm,v_ename;
         EXIT WHEN user_gz%NOTFOUND;
         dbms_output.put_line(v_sal||'#'||v_comm||'#'||v_ename);
      END LOOP;
     ELSE
      dbms_output.put_line(v_sal||'#-'||v_comm||'#-'||v_ename);
     END IF;
     CLOSE user_gz;  
END test_oracle;