存储过程和存储函数

建表和插入数据：

create table EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);

select * from EMP ;

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
commit;

******************************************************************

使用set serveroutput on 命令设置环境变量
serveroutput为打开状态，从而使得pl/sql程序能够再SQL*plus中输出结果
在编写存储过程时，有时会用
dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。

CLear Screen :清屏

游标使用：

create or replace procedure select888
as
cursor a is select * from emp ;
osname emp%rowtype;
begin
open a;
loop
fetch a into osname;
EXIT WHEN a%NOTFOUND;

dbms_output.put_line('=='||osname.empno||'==');

end loop;
close a;
end;

http://www.cnblogs.com/Rainbow-G/articles/4300706.html

 http://blog.csdn.net/yangzhawen/article/details/8617179

 **********************************************************************************

CREATE OR REPLACE PROCEDURE snakeLDP AS
CUR_CTGY HS_OPT_DELIVERY.SITE_ID%TYPE; --存放ID
CUR_CTGYNAME HS_BASIC_SITE.SITE_NAME%TYPE; --存放名称
CUR_PRTIFO HS_OPT_DELIVERY%ROWTYPE; --存放表HS_OPT_DELIVERY的行记录
CURSOR CUR_CATEGORY IS  --存放结果集，相当于集合
--SELECT SITE_ID FROM HS_OPT_DELIVERY where site_id <= 15036;
SELECT SITE_ID FROM HS_OPT_DELIVERY GROUP BY SITE_ID;
--SELECT SITE_ID FROM HS_OPT_DELIVERY where site_id = 15177;
BEGIN
OPEN CUR_CATEGORY;
LOOP
FETCH CUR_CATEGORY
INTO CUR_CTGY;
EXIT WHEN CUR_CATEGORY%NOTFOUND;

SELECT HS_BASIC_SITE.SITE_NAME
INTO CUR_CTGYNAME
FROM HS_BASIC_SITE
WHERE HS_BASIC_SITE.SITE_ID = CUR_CTGY; --根据id 编码得到名称

DBMS_OUTPUT.PUT_LINE('xxxxxxxxxxxxxxxxxxxx');
DBMS_OUTPUT.PUT_LINE('zaijiazaijianba====== '|| SQL%ROWCOUNT);

IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('------------');
DBMS_OUTPUT.PUT_LINE('papapapa: '||CUR_CTGYNAME || ':');
END IF;

FOR MY_PRDINFO_REC IN (SELECT *
FROM HS_OPT_DELIVERY
WHERE SITE_ID = CUR_CTGY)
LOOP
DBMS_OUTPUT.PUT_LINE('名称：' || MY_PRDINFO_REC.SITE_NAME || 'id:' ||
MY_PRDINFO_REC.SITE_ID);
END LOOP;
END LOOP;
CLOSE CUR_CATEGORY;
END;

--select * from HS_BASIC_SITE where site_id = '15177';

-- SELECT * FROM HS_OPT_DELIVERY WHERE SITE_ID = '15177';

*******************************************************************************

999999999999999999999999999999

CREATE OR REPLACE PROCEDURE snakeLDP AS
CUR_CTGY HS_OPT_DELIVERY.SITE_ID%TYPE; --存放产品类型编码
CUR_CTGYNAME HS_BASIC_SITE.SITE_NAME%TYPE; --存放产品类型名称
CUR_PRTIFO HS_OPT_DELIVERY%ROWTYPE; --存放表productinfo的行记录
CURSOR CUR_CATEGORY IS
SELECT SITE_ID FROM HS_OPT_DELIVERY GROUP BY SITE_ID;
BEGIN
OPEN CUR_CATEGORY;
LOOP
FETCH CUR_CATEGORY
INTO CUR_CTGY;
EXIT WHEN CUR_CATEGORY%NOTFOUND;
BEGIN
SELECT HS_BASIC_SITE.SITE_NAME
INTO CUR_CTGYNAME
FROM HS_BASIC_SITE
WHERE HS_BASIC_SITE.SITE_ID = CUR_CTGY; --根据id 编码得到名称
EXCEPTION WHEN NO_DATA_FOUND THEN CUR_CTGYNAME := '---空值---';
END;
DBMS_OUTPUT.PUT_LINE('网点编号:' || CUR_CTGY || ',网点名称:' || CUR_CTGYNAME);
FOR MY_PRDINFO_REC IN (SELECT *
FROM HS_OPT_DELIVERY d
WHERE d.SITE_ID = CUR_CTGY)
LOOP
DBMS_OUTPUT.PUT_LINE('派件扫描网点名称：' || MY_PRDINFO_REC.SITE_NAME ||
',派件扫描网点编号:' || MY_PRDINFO_REC.SITE_ID);
END LOOP;
END LOOP;
CLOSE CUR_CATEGORY;
END;

666666666666666666666666666

create or replace procedure raiseMoney(eno in number)
as

psal emp.sal%type;

begin

select sal into psal from emp where empno = eno ;

update emp set sal = sal + 100 where empno = eno ;

dbms_output.put_line('涨前：'||psal||'涨后：'||(psal+100));

end;

 ********************************************

存储函数

create or replace function queryIncome(eno in number) return number as
psal emp.sal%type;
pcomm emp.comm%type;
begin
select e.sal, e.comm into psal, pcomm from emp e where e.empno = eno;
return psal * 12 + nvl(pcomm,0);
end;

*9******************************************

 

简单理解就是：

 package 类似声明（接口）
 package body，类似实现  

  package   body和package 这两个是一体的，必须同时存在  都需要手工去写。  
  需要先创建package(也就是包的定义)，再创建body。增加包中的过程或者修改包中过程的输入参数个数等也是要先改package再改body。

 --包

create or replace package MyPackage as
type empcursor is ref cursor;
procedure queryEmpList(dno in number, empList out empcursor);
end MyPackage;

 --包体

create or replace package body MyPackage as

procedure queryEmpList(dno in number, empList out empcursor)
as
begin

open empList for select * from emp e where e.deptno = dno;

end queryEmpList;

end MyPackage;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

--out参数：如果只有一个返回值，用存储函数；否则，就用存储过程。
create or replace procedure queryEmp(eno in number,
pename out varchar2,
pjob out varchar2,
psal out number)
as
begin
select e.ename, e.job, e.sal
into pename, pjob, psal
from emp e
where e.empno = eno;

end;
/

*********************************************************

 --存储函数

create or replace function queryIncome(eno in number) return number as
psal emp.sal%type;
pcomm emp.comm%type;
begin
select e.sal, e.comm into psal, pcomm from emp e where e.empno = eno;
return psal * 12 + nvl(pcomm,0);
end;

测试：

 select queryincome(7844) from  dual; 

在PL/SQL中，FUNCTION 和 PROCEDURE都可以通过TEST来调试。
此外FUNCTION可以在SQL 窗口中，类似

select queryincome(7844) from dual; 调用
PROCEDURE 可以在命令窗口中，类似
SQL> exec queryincome;

**************************************************