PL/SQL

Sql代码

SQL>set serveroutput on;  --显示输出

-------------------------------------------------
--LOOP循环
declare
  i number:=1;
begin
  loop
    i:=i+1;
    exit when i=100;
    dbms_output.put_line(i);
  end loop;

end;

-------------------------------------------------
-- FOR LOOP循环50次
declare
  i number :=10;
begin
  for idx in 1..50 loop  --次数：50-1+1=50
    dbms_output.put_line(i);
  end loop;
end;

-------------------------------------------------
-- WHILE LOOP循环
declare
  i number :=100;
  j number :=1;
begin
  while(j
    dbms_output.put_line(j);
    j:=j+1;
  end loop;
end;

-------------------------------------------------
--IF语句
declare
  i number :=40;
  j number :=10;
begin
  if(ithen
    dbms_output.put_line('OK');
  elsif (i=j) then
    dbms_output.put_line('OK1');
  else
    dbms_output.put_line('OK2');
  end if;
end;


=================================================
游标操作
=================================================

--显示游标
declare
  --建立显示游标
  cursor csr_1 is
     select name from stu1;
  cursor csr_2 is
     select id from stu1;
  --定义变量
  str varchar2(10);
  str2 varchar2(10);
begin
  --打开游标
  open csr_1;
  open csr_2;
  --附值
  fetch csr_1 into str;
  fetch csr_2 into str2;
  --输出
  dbms_output.put_line(str);
  dbms_output.put_line(str2);
end;

-------------------------------------------------
--取表某列全部记录(适合控制)
declare
  --建立显示游标
  cursor csr_1 is
     select name,note from stu1;
  --定义变量
  str  varchar2(10);
  str2 varchar2(10);
begin
  --打开游标
  open csr_1;
  loop
    fetch csr_1 into str,str2;
    exit when csr_1%notfound;  --到达记录尾
    dbms_output.put_line(str || str2);
  end loop;

  --关闭游标
  close csr_1;
end;

-------------------------------------------------
--FOR LOOP查询表全部记录(简单--打开、附值、关闭隐式完成)
declare
  --建立显示游标
  cursor csr_1 is
     select name,note from stu1;
begin
  for idx in csr_1 loop
    dbms_output.put_line(idx.name || idx.note);
  end loop;
end;


-------------------------------------------------
--利用游标复制表(效率不高)
declare
  cursor csr_1 is
     select ks_xm,ks_zkz,bk_cj from ahzk_all_cj;
  i number;
begin
  for idx in csr_1 loop
    insert into tmp values(idx.ks_xm,idx.ks_zkz,idx.bk_cj);
    i:=csr_1%rowcount;  --获取当前记录数
    if(i mod 1000=0) then
      dbms_output.put_line(i);
      commit;
    end if;
  end loop;
  commit;
end;

--附：高效复制表
declare
begin
    insert into tmp
       select ks_xm,ks_zkz,bk_cj from ahzk_all_cj;
    if(sql%found) then
      dbms_output.put_line('插入' || sql%rowcount || '行记录');
    end if;
    commit;
end;
------------

ORACLE 游标的4种应用方法
Sql代码 
--1.普通cursor
set serveroutput on;
declare
   num1 number;
   num2 number;
   cursor SEQ is select SEQ,JCXM from test;
begin
    OPEN SEQ;
    loop
        fetch SEQ into num1, num2;
         dbms_output.put_line(num1||','||num2);
        exit when SEQ%NOTFOUND;
    end loop;
    CLOSE SEQ;
    Exception
    When others then
       Rollback;
end;
--2.使用FOR循环的游标1;

 declare
   num1 number;
   num2 number;
   cursor SEQ is select SEQ,JCXM from test;
begin

    for seq_r in SEQ loop
     dbms_output.put_line(seq_r.seq||','||seq_r.jcxm);

    end loop;
    Exception
    When others then
       Rollback;
end;
--3.使用FOR循环的游标2;

 declare
   num1 number;
   num2 number;
   cursor SEQ is select SEQ,JCXM from test;
begin
    for seq_r in (select SEQ,JCXM from test ) loop
    dbms_output.put_line(seq_r.seq||','||seq_r.jcxm);
    END loop;
    Exception
    When others then
       Rollback;
end;
--4.带参数的游标

 declare
   num1 number;
   num2 number;
   cursor SEQ(seqNum number ) is select SEQ,JCXM from test where seq = seqNum;
begin

    for seq_r in SEQ(11) loop
    dbms_output.put_line(seq_r.seq||','||seq_r.jcxm);
    END loop;
    Exception
    When others then
       Rollback;
end;

  游标for循环的优点是：用户不需要打开游标，去数据，测试数据的存在，关闭游标或者定义存放数据的变量。

 缺点就不知道了，会不会影响速度呢？

====================

PL/SQL入门教程

1.1 PL/SQL简介

PL/SQL是ORACLE的过程化语言，包括一整套的数据类型、条件结构、循环结构和异常处理结构，PL/SQL可以执行SQL语句，SQL语句中也可以使用PL/SQL函数。


1.2 创建PL/SQL程序块

DECLARE
…
BEGIN
…
EXCEPTION
END;


1.3 PL/SQL数据类型

名称 类型 说明
NUMBER 数字型 能存放整数值和实数值，并且可以定义精度和取值范围
BINARY_INTEGER 数字型 可存储带符号整数，为整数计算优化性能
DEC 数字型 NUMBER的子类型，小数
DOUBLE PRECISION 数字型 NUMBER的子类型，高精度实数
INTEGER 数字型 NUMBER的子类型，整数
INT 数字型 NUMBER的子类型，整数
NUMERIC 数字型 NUMBER的子类型，与NUMBER等价
REAL 数字型 NUMBER的子类型，与NUMBER等价
SMALLINT 数字型 NUMBER的子类型，取值范围比INTEGER小
VARCHAR2 字符型 存放可变长字符串，有最大长度
CHAR 字符型 定长字符串
LONG 字符型 变长字符串，最大长度可达32,767
DATE 日期型 以数据库相同的格式存放日期值
BOOLEAN 布尔型 TRUE OR FALSE
ROWID ROWID 存放数据库的行号

例子：
DECLARE
ORDER_NO NUMBER(3);
CUST_NAME VARCHAR2(20);
ORDER_DATE DATE;
EMP_NO INTEGER:=25;
PI CONSTANT NUMBER:=3.1416;
BEGIN
NULL;
END;


1.4 处理PL/SQL的异常

1.4.1 PL/SQL的异常

例如：
DECLARE
X NUMBER;
BEGIN
X:= 'yyyy';--Error Here
EXCEPTION WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDED');
END;

实现技术：
EXCEPTION WHEN first_exception THEN
…
WHEN second_exception THEN
…
WHEN OTHERS THEN
/*THERS异常处理器必须排在最后，它处理所有没有明确列出的异常。*/
…
END;

1.4.2 预定义异常

异常名称 ORACLE错误 说明
CURSOR_ALREADY_OPEN ORA-6511 试图打开一个已打开的游标
DUP_VAL_ON_INDEX ORA-0001 试图破坏一个唯一性限制
INVALID_CURSOR ORA-1001 试图使用一个无效的游标
INVALID_NUMBER ORA-1722 试图对非数字值进行数字操作
LOGIN_DENIED ORA-1017 无效的用户名或者口令
NO_DATA_FOUND ORA-1403 查询未找到数据
NOT_LOGGED_ON ORA-1012 还未连接就试图数据库操作
PROGRAM_ERROR ORA-6501 内部错误
ROWTYPE_MISMATCH ORA-6504 主变量和游标的类型不兼容
STORAGE_ERROR ORA-6500 内部错误
TIMEOUT_ON_RESOURCE ORA-0051 发生超时
TOO_MANY_ROWS ORA-1422 SELECT INTD命令返回的多行
TRANSACTION_BACKED_OUT ORA-00 由于死锁提交被退回
VALUE_ERROR ORA-6502 转换或者裁剪错误
ZERO_DIVIDE ORA-1476 试图被零除

1.4.3 自定义异常处理

DECLARE
BAD_ROWID EXCEPTION;
X ROWID;
PRAGMA EXCEPTION_INIT(BAD_ROWID,-01445);
BEGIN
SELECT ROWID INTO X FROM TAB
WHERE ROWNUM=1;
EXCEPTION WHEN BAD_ROWID THEN
DBMS_OUTPUT.PUT_LINE('CANNOT QUERY ROWID FROM THIS VIEW');
END;

注意：-01445 因为PRAGMA EXCEPTION_INIT命令把这个变量(-01455)连接到
这个ORACLE错误，该语句的语法如下：
PRAGMA EXCEPTION_INIT(exception_name, error_number);
其中error_number是负数，因为错误号被认为负数，当定义错误时记住使用负号

1.4.4 自定义异常

异常不一定必须是oracle返回的系统错误，用户可以在自己的应用程序中创
建可触发及可处理的自定义异常
DECLARE
SALARY_CODE VARCHAR2(1);
INVALID_SALARY_CODE EXCEPTION;
BEGIN
SALARY_CODE:='X';
IF SALARY_CODE NOT IN('A', 'B', 'C') THEN
RAISE INVALID_SALARY_CODE;
END IF;
EXCEPTION WHEN INVALID_SALARY_CODE THEN
DBMS_OUTPUT.PUT_LINE('INVALID SALARY CODE');
END;


1.5 在PL/SQL中单条记录的查询

在PL/SQL内，有时在没有定义显式游标的情况下需要查询单条记录，并把记录的数据赋给变量。
DECLARE
ln_dno NUMBER;
lvs_dname VARCHAR2(40);
BEGIN
SELECT DEPT_NO,DEPT_NAME
INTO ln_dno,lvs_dname
FROM dept
WHERE DEPT_NO=1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ln_dno)||'.'||lvs_dname);
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA_FOUND');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;

1.6 用游标查询多条记录

游标（CURSOR）是指向一个称为上下文相关区的区域的指针，这个区域在服务器的处理过程全局区（PGA）内，当服务器上执行了一个查询后，查询返回的记录集存放在上下文相关区，通过游标上的操作可以把这些记录检索到客户端的应用程序。

1.6.1 使用游标的基本方法

DECLARE
CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS
WHERE ROWNUM<=10
ORDER BY VIEW_NAME;
VNAME VARCHAR2(40);
BEGIN
OPEN C1;
FETCH C1 INTO VNAME;
WHILE C1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||' '||VNAME);
FETCH C1 INTO VNAME;
END LOOP;
END;

属性 含量
%FOUND 布尔型属性，当最近一次该记录时成功返回，则值为TRUE
%NOTFOUND 布尔型属性，它的值总与%FOUND属性的值相反
%ISOPEN 布尔型属性，当游标是打开时返回TRUE
%ROWCOUNT 数字型属性，返回已从游标中读取的记录数

1.6.2 使用游标FOR循环

DECLARE
CURSOR C1 IS
SELECT VIEW_NAME
FROM ALL_VIEWS
WHERE ROWNUM<=10
ORDER BY VIEW_NAME;
BEGIN
FOR I IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
END LOOP;
EXCEPTION WHEN OTHERS THEN
NULL;
END;

1.6.3 带参数的游标

DECLARE
CURSOR C1(VIEW_PATTERN VARCHAR2) IS
SELECT VIEW_NAME
FROM ALL_VIEWS
WHERE VIEW_NAME LIKE VIEW_PATTERN||'%' AND
ROWNUM<=10
ORDER BY VIEW_NAME;
VNAME VARCHAR2(40);
BEGIN
FOR I IN C1('USER_AR') LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE(?);
FOR I IN C1('USER') LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('AAA');
END;

1.7 创建代表数据库记录和列的变量

变量名 基表名.列名%TYPE
DECLARE
D_NO DEPT.DEPT_NO%TYPE;
D_NAME DEPT.DEPT_NAME%TYPE;
BEGIN
SELECT DEPT_NO,DEPT_NAME INTO D_NO,D_NAME
FROM DEPT;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_NO));
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;

变量名 基表名%ROWTYPE
DECLARE
D VEQU12%ROWTYPE;
BEGIN
SELECT ASSET12ID,ASSET12NAME
INTO D.ASSET12ID, D.ASSET12NAME
FROM VEQU12;
DBMS_OUTPUT.PUT_LINE(D.ASSET12ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;
说明：
当用户要创建一个变量来表示一个基表列或者要创建多个变量来代表一整条记录时，可以实际使用%TYPE属性和%ROWTYPE属性，使用%TYPE属性和%ROWTYPE属性可以保证当基表的结构或者其中某列的数据类型改变了时，用户的PL/SQL代码仍可正常工作。

1.8 怎样用PL/SQL表实现数组功能

PL/SQL表与其他过程化语言(如C语言)的一维数组类似。实现PL/SQL表需要创建一个数据类型并另外进行变量说明。
Type <类型名> Is
Table Of <数据类型>
Index by Binary_Integer;
以下为一个例子：
Declare
Type Array_type is
Table Of Number
Index by Binary_Integer;
My_Array Array_type;
Begin
For I In 1..10 Loop
My_Array(I) := I*2;
End Loop;
For I In 1..10 Loop
Dbms_Output.Put_line(To_char(My_Array(I)));
End Loop;
End;