Linux调Oracle存储过程,Oracle存储过程学习-CSDN博客

之前写过，后来很久不写，既然全部忘记了。这里重新学习Oracle的存储过程，希望看到的人不要见笑。

首先都知道存储过程的开头语句是CREATE OR REPLACE PROCEDURE (VAR in type,VAR out type, .....) AS | IS 但从变量申明开始变量类型有哪些？ BEGIN后逻辑语句有哪些，我就记不清楚了。

这里先给个简单的栗子CREATE OR REPLACE PROCEDURE OUTPUTINFO AS

I NUMBER(10); --在这里申明变量I

BEGIN

I := 0; --申明变量I的值为0

WHILE I <= 10 LOOP

--这里使用了WHILE循环

BEGIN

I := I + 1;

END;

DBMS_OUTPUT.PUT_LINE(I); --发现该句放在BEGIN,END结构内外，结果一样？

END LOOP;

END OUTPUTINFO;SQL> SET SERVEROUTPUT ON

SQL> EXEC OUTPUTINFO;

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

今天尝试继续写，发现当表名是变量时是过程是无法编译成功的。经过网上的查找和反复的测试才写成这么简单的一句CREATE OR REPLACE PROCEDURE TBS AS

TB VARCHAR2(15);

TBN NUMBER(10) DEFAULT 1;

V_SQL VARCHAR2(2000);

CNT NUMBER(10);

TBD DATE;

BEGIN

TBD := TO_DATE('2013/12/31 23:59:59', 'YYYY/MM/DD HH24:MI:SS');

WHILE (TBN <= 11) LOOP

TB := 'TBILLLOG' || TBN;

TBN := TBN + 1;

V_SQL := 'SELECT COUNT(*) FROM ' || TB;

EXECUTE IMMEDIATE V_SQL

INTO CNT;

IF CNT > 1 THEN

DBMS_OUTPUT.PUT_LINE(TBD || ' ' || TBN || ' ' || CNT);

END IF;

END LOOP;

END;

今天写作业，一个简单的过程，犯了个简单的逻辑错误，输出的时候使用的变量是V_SQL，导致结果总是SELECT语句而非语句执行的结果。CREATE OR REPLACE PROCEDURE MALE_AVG_SALARY AS

V_SQL VARCHAR2(2000);

RESULTS NUMBER(10);

BEGIN

V_SQL := 'SELECT AVG(SALARY) FROM EMPLOYEES WHERE EMPLOYEESEX=' ||

'''M''' || ' GROUP BY EMPLOYEESEX';

EXECUTE IMMEDIATE V_SQL;

DBMS_OUTPUT.PUT_LINE(V_SQL);

END;

今天的作业继续头大，不过参考了业务库上的过程，对过程调用有了进一步的认识：显示的开启游标

CREATE OR REPLACE PROCEDURE EMPLOYEE_SALARY(GENTEE IN VARCHAR2) AS

CURSOR CUR IS

SELECT FIRST_NAME || LAST_NAME EMPLOYEE_NAME, SALARY

FROM EMPLOYEES

WHERE EMPLOYEESEX = GENTEE;

R EMPLOYEES % ROWTYPE;

BEGIN

FOR R IN CUR LOOP

BEGIN

DBMS_OUTPUT.PUT_LINE('EMPLOYEE_NAME: ' || R.EMPLOYEE_NAME ||

' EMPLOYEE_SALARY: ' || R.SALARY);

END;

END LOOP;

END EMPLOYEE_SALARY;

显示的开启游标方法2：

CREATE OR REPLACE PROCEDURE EMPLOYEE_SALARY (GENTEE IN VARCHAR2)AS

EMPN VARCHAR2(30);

WAGE NUMBER(20);

C1 SYS_REFCURSOR;

BEGIN

OPEN C1 FOR SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEESEX=GENTEE;

LOOP

FETCH C1 INTO EMPN,WAGE;

DBMS_OUTPUT.PUT_LINE('EMPLOYEE_NAME: ' || EMPN || ' SALARY: ' || WAGE);

EXIT WHEN C1%NOTFOUND;

END LOOP;

END EMPLOYEE_SALARY;

该语句的隐式游标方式：

CREATE OR REPLACE PROCEDURE EMPLOYEE_SALARY(GENTEE IN VARCHAR2) AS

R EMPLOYEES % ROWTYPE;

BEGIN

FOR R IN (SELECT FIRST_NAME || LAST_NAME EMPLOYEE_NAME, SALARY

FROM EMPLOYEES

WHERE EMPLOYEESEX = GENTEE) LOOP

BEGIN

DBMS_OUTPUT.PUT_LINE('EMPLOYEE_NAME: ' || R.EMPLOYEE_NAME ||

' EMPLOYEE_SALARY: ' || R.SALARY);

END;

END LOOP;

END EMPLOYEE_SALARY;

今天写了一个带输入输出参数的过程，之前说查询语句不能直接运行，今天视乎打破了我之前的理解。并非不能直接运行查询。CREATE OR REPLACE PROCEDURE AVERAGE_AGE(LOW_AGE IN NUMBER,

HIGH_AGE IN NUMBER,

AVG_SALARY OUT NUMBER) AS

BEGIN

SELECT AVG(E_WAGE)

INTO AVG_SALARY

FROM EMPLOYEE

WHERE BIRTH

AND BITRH > SYSDATE - HIGH_AGE * 365;

RETURN;

END;

带输出的过程调用：DECLARE

AVG_WAGE NUMBER;

BEGIN

AVERAGE_AGE(20, 40, AVG_WAGE);

DBMS_OUTPUT.PUT_LINE(AVG_WAGE);

END;

exception

when no_data_found then -- 未找到数据

--处理

when too_many_rows then -- 找到多行数据

-- 处理

when others then -- 其他异常

-- 处理

1、过程中的表名直接用变量、或者常量和变量的组合。

---处理的方法可以将变量以字符的方式赋给其他变量，在通过游标的方式执行该语句。

2、查询语句不能直接运行，需要将结果赋给变量。

3、FOR...IN循环中的变量R 如果是取查询语句的结果，需要对其定义(R EMPLOYEES % ROWTYPE R 被定义为EMPLOYEES表中的行)，才能将SELECT语句的结果传入到变量R中。