建表和插入数据：
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程序能够再中输出结果 在编写时，有时会用 dbms_output.put_line将必要的信息输出，以便对进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。
CLear Screen :清屏
游标使用：
create or replace procedure select888
ascursor a is select * from emp ;osname emp%rowtype;beginopen 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)
aspsal 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 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) asbegin 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;**************************************************