oracle proc例子,Oracle 示例4 存储过程

CREATE TABLE EMPS

AS

SELECT * FROM EMP;

CREATE TABLE DEPTS

AS

SELECT * FROM DEPT;

SELECT * FROM EMPS;

SELECT * FROM DEPTS;

--存储过程---------------------

--1.无参存储过程

CREATE OR REPLACE PROCEDURE PROC_DEMO1

AS --代替PL/SQL中的DECLARE

V_NAME EMPS.ENAME%TYPE;

V_SAL EMPS.SAL%TYPE;

V_DEPTNO EMPS.DEPTNO%TYPE;

V_AVGSAL NUMBER;

V_RESULT VARCHAR2(50);

CURSOR CUR_RESULT IS

SELECT ENAME,SAL,DEPTNO FROM EMPS;

BEGIN

OPEN CUR_RESULT;

LOOP

FETCH CUR_RESULT INTO V_NAME,V_SAL,V_DEPTNO;

EXIT WHEN CUR_RESULT%NOTFOUND; --退出循环的另一种方法

SELECT AVG(SAL) INTO V_AVGSAL FROM EMPS

WHERE DEPTNO = V_DEPTNO;

IF V_SAL>V_AVGSAL THEN

V_RESULT := '优秀';

ELSIF V_SAL = V_AVGSAL THEN

V_RESULT := '良好';

ELSE

V_RESULT := '请努力';

END IF;

DBMS_OUTPUT.PUT_LINE(V_NAME||':'||V_RESULT);

END LOOP;

CLOSE CUR_RESULT;

END PROC_DEMO1;

--测试存储过程1

--1).直接用PL/SQL测试

BEGIN

SCOTT.PROC_DEMO1;

END;

--2).使用工具测试

--切换用户给scott用户赋予debug的权限

GRANT DEBUG CONNECT SESSION TO SCOTT;

--2.有参存储过程

CREATE SEQUENCE SEQ_DEPTS

START WITH 50

INCREMENT BY 10

SELECT * FROM USER_SEQUENCES;

CREATE OR REPLACE PROCEDURE PROC_DEMO2

( --参数区 VARCAHR2 不要写多长

IN_NAME IN VARCHAR2,--注意用逗号

IN_LOC IN VARCHAR2 --结尾没有分号

)

AS

--变量区

BEGIN

--代码区

INSERT INTO DEPTS VALUES(SEQ_DEPTS.NEXTVAL,IN_NAME,IN_LOC);

COMMIT;

END PROC_DEMO2;

--3.有参存储过程 输出参数

CREATE OR REPLACE PROCEDURE PROC_DEMO3

(

IN_DEPTNO IN EMPS.DEPTNO%TYPE,

OUT_RESULT OUT NUMBER

)

AS

BEGIN

SELECT COUNT(*) INTO OUT_RESULT

FROM EMPS WHERE DEPTNO = IN_DEPTNO;

END PROC_DEMO3;

--4.存储过程使用 动态游标

CREATE OR REPLACE PROCEDURE PROC_DEMO4

(

IN_ID IN NUMBER,

OUT_RESULT OUT SYS_REFCURSOR --动态Cursor

--与 静态Cursor不一样的是 Cursor需要创建

--时 确定紧接着的查询语句

)

AS

BEGIN

OPEN OUT_RESULT FOR

SELECT E.ENAME,E.SAL FROM EMPS E

WHERE E.DEPTNO = IN_ID;

END PROC_DEMO4;

--测试PROC_DEMO4

DECLARE

V_ID NUMBER :=20;

V_RESULT SYS_REFCURSOR;

V_ENAME VARCHAR2(50);

V_SAL NUMBER;

BEGIN

SCOTT.PROC_DEMO4(V_ID,V_RESULT);

LOOP

FETCH V_RESULT INTO V_ENAME,V_SAL;

EXIT WHEN V_RESULT%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||V_SAL);

END LOOP;

CLOSE V_RESULT;

END;

--5.分页存储过程

CREATE OR REPLACE PROCEDURE PROC_DEMO5

(

IN_INDEX IN NUMBER, --当前页 currPageNo

IN_SIZE IN NUMBER, --页面大小pageSize

OUT_RESULT OUT SYS_REFCURSOR,--查询出来的结果

OUT_COUNT OUT NUMBER,--总行数 用来计算总页数

IN_NAME IN VARCHAR2,

IN_START IN VARCHAR2,

IN_END IN VARCHAR2,

IN_DEPT IN NUMBER

)

AS

V_SQL VARCHAR2(500); --SQL语句

V_WHERE_SQL VARCHAR2(300); --条件SQL语句

V_START_PAGE NUMBER;

V_END_PAGE NUMBER;

BEGIN

IF IN_NAME IS NOT NULL THEN

V_WHERE_SQL := ' AND ENAME LIKE ''%'||IN_NAME||'%'' ';

END IF;

IF IN_START IS NOT NULL AND IN_END IS NOT NULL THEN

V_WHERE_SQL :=V_WHERE_SQL||' AND HIREDATE BETWEEN TO_DATE('''||IN_START||''',''yyyy-mm-dd'') AND

TO_DATE('''||IN_END||''',''yyyy-mm-dd'') ';

END IF;

IF IN_DEPT > 0 THEN

V_WHERE_SQL := V_WHERE_SQL||' AND DEPTNO = '||IN_DEPT;

END IF;

V_SQL := 'SELECT COUNT(*) FROM EMPS WHERE 1=1 '|| V_WHERE_SQL;

EXECUTE IMMEDIATE V_SQL INTO OUT_COUNT; --动态执行的语句 可以这样赋值

V_START_PAGE := (IN_INDEX-1)*IN_SIZE;

V_END_PAGE := IN_INDEX*IN_SIZE;

V_SQL := 'SELECT * FROM ( '||

' SELECT E.*,ROWNUM AS R FROM EMPS E WHERE 1=1 '||V_WHERE_SQL||

' ) WHERE R<=:1 AND R>:2 ';

OPEN OUT_RESULT FOR V_SQL USING V_END_PAGE,V_START_PAGE; --动态游标可以执行字符串 同样也可以使用USING

END PROC_DEMO5;

SELECT * FROM (

SELECT E.*,ROWNUM AS R FROM EMPS E WHERE 1=1

)

WHERE R<=5 AND R>0;

SELECT * FROM USER_PROCEDURES;--查用户的存储过程

DROP PROCEDURE PROC_DEMO1;--删用户的存储过程

DROP PROCEDURE PROC_DEMO2;--删用户的存储过程

DROP PROCEDURE PROC_DEMO3;--删用户的存储过程

DROP PROCEDURE PROC_DEMO4;--删用户的存储过程

DROP PROCEDURE PROC_DEMO5;--删用户的存储过程