游标，存储过程

ＰＬ／ＳＱＬ程序有以下优点：
软件生产效率很高；系统性能良好；系统资源利用率高（如：节省内存）
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一样