之前写过,后来很久不写,既然全部忘记了。这里重新学习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;
1
2
3
4
5
6
7
8
9
10
11
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 < SYSDATE - LOW_AGE * 365
     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中。