游标，存储过程

ＰＬ／ＳＱＬ程序有以下优点：
软件生产效率很高；系统性能良好；系统资源利用率高（如：节省内存）
Sql基本的语法结构
Declare 
    /*   声明部分: 在此  声明PL/SQL用到的变量,类型及光标 */
begin
    /*  执行部分:  过程及SQL 语句  , 即程序的主要部分  */
Exception
   /* 执行异常部分: 错误处理  */
End;
定义部分:    定义将在执行部分调用的所有变量,常量,游标,和用户自定义的异常处理. 这部分可以没有.
执行部分:    包括对数据库进行操作的ＳＱＬ语句,以及对语句进行组织,控制的ＰＬ/ＳＱＬ语句。这部分在ＰＬ／ＳＱＬ块中必须存在.   
例外处理部分:可执行部分中的语句,在执行过程中出错或出现非正常现象时,所应做的相应处理. 这部分可以没有
第一次使用的变量必须在定义部分定义,只有定义过的变量才能在执行部分引用.需要注意的是块可以嵌套使用.
变量类型
类型         子类            说 明            范围       ORACLE限制
--------------------   ------------    ------------   ------------------------
Char      character       定长字符串         032767      255
            Rowid    
Varchar2    varchar        可变字符串        032767      4000
Binary_integer       带符号整数,为整数计算优化性能
Number(p,s)           Dec           小数, Number 的子类型
Double precision    高精度实数
Integer             整数, Number 的子类型
Int                整数, Number 的子类型
Numeric                  与Number等价
Real                        与Number等价
Small int                  整数, 比 integer 小
Long                       变长字符串,一般在PL/SQL中只能用到 32,767字节
Date                       日期型
Boolean                  布尔型, TRUE, 或 FALSE
ROWID                   存放数据库行号
提示:  一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.
在 PL/SQL 语句中使用DBMS_OUTPUT.PUT_LINE 语句输出变量或字串.
Begin dbms_output.put_line(‘输出字符串');end;
在PL/SQL块中引用的所有标识符，都必须在PL/SQL块的定义部分中明确定义。在定义一个标识符时，首先要为其确定一个名字，其次是确定它是变量还是常量，然后是它的数据类型、是否允许为空值（NULL），以及如果有初始值，其初始值是什么。具体的语法形式如下所示：
<标识符>  [CONSTANT] <数据类型>  [NO TNULL][：= | DEFAULT <PL/SQL表达式>]；
同时,还要注意以下几点：
标识符的命名规则遵循所有SQL实体的命名规则。如果定义常量，必须加上关键字“CONSTANT”，而且定义常量时必须为其赋值。定义的变量不允许有空值出现时，必须使用关键字“NOT NULL”，并且为该变量赋初始值。为变量赋值时，使用赋值操作符
“：=”，或者，使用权用关键字“DEFAULT”。如果不为变量赋值，则其缺省的初始值为空值。
每行只能定义一个标识符。
DECLARE
n1 number(10,2);v1 varchar2(20) not null DEFAULT  ‘天津’;
v2 varchar2(20):=‘天津’;v3 CONSTANT varchar2(20):=‘天津’;
v4 CONSTANT varchar2(20) DEFAULT ‘天津’;
使用%TYPE
可以将变量类型定义为与数据库中表的字段相同的类型，当字段的数据类型变化时则变量的类型也相应的变化。
DECLARE  v1   xs_xx.xsid%TYPE;
使用%ROWTYPE可以将变量类型定义为与具有相同类型的数据库行。它将返回一个基于表定义的类型。DECLARE  record1 XS_XX%ROWTYPE;  
PL/SQL 程序中只能使用 DML 和 事务控制，不能使用 DDL 语句。在PL/SQL V2.1 以后的版本可以采用动态的方法来使用 DDL 语句。 
DECLARE
  V_empRecord emp%ROWTYPE;
  V_empno    emp.empno%TYPE;
Begin
  Select * into V_empRecord From emp where emp.empno = '7369';
  dbms_output.put_line(V_empRecord.empno||'|'||V_empRecord.ename);
  select empno into V_empno from emp where empno = '7369';
  dbms_output.put_line(V_empno);
END;
insert  语句可以包括 一个 select 语句，但要求选择列表要与插入列表相匹配。
DECLARE
  V_empno  emp.empno%TYPE;
BEGIN
   SELECT test_seq1.NEXTVAL INTO V_empno from dual;
  INSERT INTO emp(empno,ename)VALUES(V_empno,’Wang’);
End;
事务控制
事务(transaction)是一系列作为一个单元被成功或不成功执行的SQL语句。例如银行的事务：从一个帐号上汇出款到另一帐号上去（汇入）:
begin
  UPDATE accounts  SET balance = balance – transaction_amount
  WHERE  account_no= from_acct;
  UPDATE accounts  SET balance = balance + transaction_amount
  WHERE  account_no = to_acct;
  Commit;
Exception
   when others then
   rollback;End;
声明游标 CURSOR cursor_name IS select_statement;
DELCARE 
CURSOR C_EMP IS SELECT empno,ename,salary  FROM emp  WHERE salary>2000  
ORDER BY ename; 
    在游标定义中SELECT语句中不一定非要表，可以是视图，也可以从多个表或视图中选择列，甚至可以使用*来选择所有的列。使用游标中的值之前应该首先打开游标，打开游标初始化查询处理。打开游标的语法是：  OPEN cursor_name，cursor_name是在声明部分定义的游标名，如OPEN C_EMP;  关闭游标 CLOSE cursor_name  。
    从游标得到一行数据使用FETCH命令。每一次提取数据后，游标都指向结果集的下一行。语法如：FETCH cursor_name INTO variable[,variable,...] 。对于SELECT定义的游标的每一列，FETCH变量列表都应该有一个变量与之相对应，变量的类型也要相同
   SET SERVERIUTPUT ON 
   DECLARE 
     v_ename EMP.ENAME%TYPE;  v_salary EMP.SALARY%TYPE; 
     CURSOR c_emp IS SELECT ename,salary FROM emp; 
          FETCH c_emp INTO v_ename,v_salary; 
   DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary); 
          FETCH c_emp INTO v_ename,v_salary; 
   DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary); 
       CLOSE c_emp;  END；
上段代码无疑是非常麻烦的，如果有多行返回结果，可以使用循环并用游标属性为结束循环的条件，以这种方式提取数据，程序的可读性和简洁性都大为提高，下面我们使用循环重新写上面的程序： 
DECLARE
  v_ename  EMP.ENAME%TYPE;v_salary EMP.SAL%TYPE;
  CURSOR c_emp IS SELECT ename, sal FROM emp;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_ename, v_salary;
   DBMS_OUTPUT.PUT_LINE('Salary of Employee ' || v_ename || ' is ' ||v_salary);
    EXIT WHEN c_emp%NOTFOUND;
    end loop;
END;
%ROWTYPE也可以用游标名来定义，这样的话就必须要首先声明游标： 
DECLARE
  CURSOR c_emp IS SELECT ename, sal FROM emp;
  r_emp c_emp%ROWTYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO r_emp;
    EXIT WHEN c_emp%NOTFOUND;
dbms_output.PUT_LINE('Salary of Employee '|| r_emp.ename || 'is'||r_emp.sal);
  END LOOP;
  CLOSE c_emp;
END;
   与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下：
CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型，没有大小。另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。
在打开游标时给参数赋值，语法如下：   
OPEN cursor_name[value[,value]....]; 
declare
  r_dept       DEPT%ROWTYPE;
  v_ename      EMP.ENAME%TYPE;
  v_salary     EMP.SAL%TYPE;
  v_tot_salary EMP.SAL%TYPE;
  cursor c_dept is select * from dept order by deptno;
  CURSOR c_emp(p_dept VARCHAR2) IS
    SELECT ename, sal FROM emp WHERE deptno = p_dept ORDER BY ename;
BEGIN
  OPEN c_dept;
  LOOP
    FETCH c_dept INTO r_dept;
    EXIT WHEN c_dept%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Department:' ||r_dept.deptno ||'-'||r_dept.dname);
    v_tot_salary := 0;
    OPEN c_emp(r_dept.deptno);
    LOOP
      FETCH c_emp INTO v_ename, v_salary;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Name:' || v_ename || ' salary:' || v_salary);
      v_tot_salary := v_tot_salary + v_salary;
    END LOOP;
    CLOSE c_emp;
    DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:' || v_tot_salary);
  END LOOP;
  CLOSE c_dept;
END;
在大多数时候我们在设计程序的时候都遵循下面的步骤： 
打开游标  开始循环  从游标中取值  检查那一行被返回  处理  关闭循环  关闭游标
可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同，这就是FOR循环，用于FOR循环的游标按照正常的声明方式声明，它的优点在于不需要显式的打开、关闭、取数据，测试数据的存在、定义存放数据的变量等等。游标FOR循环的语法如下：
FOR record_name IN 
(corsor_name[(parameter[,parameter]...)]  | (query_difinition) 
LOOP  statements END LOOP; 
下面我们用for循环重写上面的例子： 
DECLARE
  CURSOR c_dept IS SELECT deptno, dname FROM dept ORDER BY deptno;
  CURSOR c_emp(p_dept VARCHAR2) IS
    SELECT ename, sal FROM emp WHERE deptno = p_dept ORDER BY ename;
  v_tot_salary EMP.SAL%TYPE;
BEGIN
  FOR r_dept IN c_dept LOOP
DBMS_OUTPUT.PUT_LINE('Department: ' || r_dept.deptno || '-'||r_dept.dname);
    v_tot_salary := 0;
    FOR r_emp IN c_emp(r_dept.deptno) LOOP
      DBMS_OUTPUT.PUT_LINE('Name: ' || r_emp.ename || 'salary: '||r_emp.sal);
      v_tot_salary := v_tot_salary + r_emp.sal;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept: ' || v_tot_salary);
  END LOOP;
END;
      在游标FOR循环中可以定义查询，由于没有显式声明，所以游标没有名字，记录名通过游标查询来定义。 
DECLARE
  v_tot_salary EMP.SAL%TYPE;
BEGIN
  FOR r_dept（不定义）IN (SELECT deptno, dname FROM dept ORDER BY deptno) LOOP
    DBMS_OUTPUT.PUT_LINE('Department:' || r_dept.deptno || '-'||r_dept.dname);
    v_tot_salary := 0;
    FOR r_emp IN (SELECT ename, sal FROM emp WHERE deptno = r_dept.deptno ORDER BY ename) LOOP
DBMS_OUTPUT.PUT_LINE('Name:' || r_emp.ename || ' salary:'||r_emp.sal);
      v_tot_salary := v_tot_salary + r_emp.sal;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:' || v_tot_salary);
  END LOOP;
END;
DECLARE
n number(10);
BEGIN
n:=1;
insert into w_test1(bh,n) values('a',n);
   DECLARE
     n number(10);
   BEGIN
     insert into w_test1(bh,n) values('b',n);
     n:=100;
     insert into w_test1(bh,n) values('b',n);
   END;
insert into w_test1(bh,n) values('a',n);
END;
CREATE TABLE W_TEST1(
BH VARCHAR2(2),
N NUMBER(10));
BH           N
-- -----------
a            1
b 
b          100
a            1
运算符
符号                           用途
-------------    ------------------------------------------
!=                    不等于号
<>                   不等于号
:=                    赋值号
=>                   关系号
..                     范围运算符
||                     字符连接符
提示: ELSIF 不能写成 ELSEIF
简单循环
declare
    x  number;
begin
    x:= 0;
      loop
        x:=x+1;
        dbms_output.put_line(to_char(x));
        exit  when x=10;
      end loop;
  end;
DECLARE
  V_counter  BINARY_INTEGER  := 1;
Begin
  LOOP
     Inert into temp_table
     Values( v_counter, ‘loop index’ );
     V_counter  := v_counter  + 1;
     If v_counter > 50  then
        Exit;
     End if ;
  End loop;
End;
• WHILE 循环
declare
  x  number;
   begin
   x:= 1;
   while  x<10  loop
   dbms_output.put_line(to_char(x)||’还小于10’);
   x:= x+1;
  end loop;
end;
• 数字式循环
For  循环计数器  in  下限 ..  上限  .
begin
  for I in 1 .. 10 loop
    dbms_output.put_line('in=' || to_char(I));
  end loop;
end;
为提高应用程序的健壮性,开发人员必须考虑程序可能出现的各种错误,并进行相应的处理。
Oracle中异常分为预定义例外,非预定义例外和自定义例外三种
预定义异常是指由PL/SQL所提供的系统异常.当PL/SQL应用程序违反了Oralce规则或系统限制时,则会隐含的触发一个内部异常。
PL/SQL为开发人员提供了二十多个预定义异常
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
BEGIN
OPEN emp_cursor;
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.ename);
END LOOP;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE('游标已经打开.');
END;
BEGIN
  UPDATE DEPT SET DEPTNO = 20 WHERE DEPTNO = 10;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE(‘deptno列上不能出现重复值’);
END;
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
emp_record emp_cursor%ROWTYPE;
BEGIN
FETCH emp_cursor INTO emp_record;
CLOSE emp_cursor;
EXCEPTION
WHEN INVALID_CURSOR THEN
DBMS_OUTPUT.PUT_LINE('请检查游标是否已经打开.');
END;
DECLARE
  V_SAL EMP.SAL%TYPE;
BEGIN
  SELECT SAL INTO V_SAL FROM EMP WHERE LOWER(ENAME) = LOWER('simth');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('');
END;
DECLARE
TYPE emp_array_type IS VARRAY(20) OF VARCHAR2(10);
emp_array emp_array_type;
BEGIN
emp_array:=emp_array_type('SCOTT','MARY');
DBMS_OUTPUT.PUT_LINE(emp_array(3));
EXCEPTION
WHEN SUBSCRIPT_BEYOND_COUNT THEN
DBMS_OUTPUT.PUT_LINE('下标越界.');end；
自定义异常与Oracle错误没有任何关联,它是开发人员为特定情况所定义的异常.
自定义异常必须显式触发.使用步骤如下:
定义异常 -> 显式触发异常 -> 引用异常
首先要在定义部分(DECLARE)定义异常,然后在执行部分(BEGIN)触发异常(RAISE语句),
最后在异常处理部分(EXCEPTION)捕捉处理.
DECLARE
  E_NO_EMPLOYEE EXCEPTION;
BEGIN
  UPDATE EMP SET DEPTNO = 10 WHERE EMPNO = 7369;
  IF SQL%NOTFOUND THEN
    RAISE E_NO_EMPLOYEE;
  END IF;
EXCEPTION
   WHEN E_NO_EMPLOYEE THEN
    DBMS_OUTPUT.PUT_LINE(‘该雇员不存在！’);
END;
CREATE OR REPLACE  PROCEDURE raise_comm(eno NUMBER,commission NUMBER)
IS v_comm  emp.comm%TYPE;
BEGIN
SELECT comm INTO v_comm FROM emp WHERE empno=eno;
IF v_comm IS NULL THEN
RAISE_APPLICATION_ERROR(-20001,'该员工无补助.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该雇员不存在.');
END;
过程
用于执行动作的子程序。过程可以有多个入口及出口参数
CREATE OR REPLACE PROCEDURE 过程名 (入口参数1 [in|out|in out]类型，入口参数[in|out|in out] 类型) as
变量1 类型 ；变量2 类型 ；
begin
  语句；
exception
  when 例外 then语句; end ;
in –只读，不能修改out－只写，不能读值in out 读写
创建一个过程，指定人的工资加100
create or replace procedure Dp_add_gz(v_grid varchar2) as
  n_rec number;
begin
select count(1)  into n_rec from dual
   where exists (select 1 from emp where empno = v_grid);
  if n_rec > 0 then
    update emp set sal = sal + 100 where empno = v_grid;
  end if;
exception
  when others then
    null;
end;
  函数必须有RETURN子句，并且有一个函数返回值，其他和PROCEDUCE一样。
CREATE OR REPLACE FUNCTION 函数名 (入口参数1类型，入口参数 类型) return type is 
变量1 类型 ；变量2 类型 ；
Begin  语句； return ;
exception  when 例外 then  语句; end ;
return 语句
一个函数可以有多个return 语句，但是只有一个可以被执行。当在函数执行的过程中，有return出现时，其后面的语句就不会在被执行了。
return 的类型必须和定义的类型相一致。只能通过out参数返回多个数值，语法和PROCEDUCE一样。省缺参数：同PRECEDUCE一样