掌握包、类、触发器、函数、存储过程的 创建以及应用，掌握 DMSQL 程序的基本操作：语句块操作、 游标访问，动态sql执行

包
包创建
create table tab_new_package (id int, name varchar(100), city varchar(100));
insert into tab_new_package values(1,‘wuhan’,‘武汉’);

create or replace package new_package is
exception1 exception;
count int;
pcur cursor;
procedure addcs(pname varchar(100), pcity varchar(100));
procedure removecs(pname varchar(100), pcity varchar(100));
procedure removecs2(pcount int);
function getcount return int;
procedure cslist;
end new_package;
/

create or replace package body new_package as
procedure addcs(pname varchar(100), pcity varchar(100)) as
begin
insert into tab_package_cs values(11, pname, pcity);
count = count + sql%rowcount;
end addcs;
procedure removecs(pname varchar(100), pcity varchar(100)) as
begin
delete from tab_package_cs where name like pname and city like pcity;
count = count - sql%rowcount;
end removecs;
procedure removecs2(pcount int) as
begin
delete from tab_package_cs where id = pcount;
count = count - sql%rowcount;
end removecs2;
function getcount return int as
begin
return count;
end getcount;
procedure cslist as
declare
v_count int;
v_name varchar(100);
v_city varchar(100);
begin
if count = 0 then
raise exception1;
end if;
open pcur for select count, name, city from tab_package_cs;
loop
fetch pcur into v_count,v_name,v_city;
exit when pcur%notfound;
print (‘no.’ + (cast (v_count as varchar(100))) + ’ ’ + v_name + ‘???’ + v_city );
end loop;
close pcur;
end cslist;
begin
select count(*) into count from tab_package_cs;
end new_package;
/

创建表tab_new_package
创建包头，定义数据库对象，创建包体
调用存储过程addcs，插入一条数据，count+1，removecs删除一条指定数据，count-1,removecs根据id删除数据,count-1

包调用
Call 包名。定义对象，例如存储过程
alter package new_package compile;
包的重编译
包删除
drop package body new_package;
drop package new_package;

类
实现类
create class mycls
as
type rec_type is record (c1 int, c2 int);
id int;
r rec_type;
function f1(a int, b int) return rec_type;
function mycls(id int , r_c1 int, r_c2 int) return mycls;
end;
/

create or replace class body mycls
as
function f1(a int, b int) return rec_type
as
begin
r.c1 = a;
r.c2 = b;
return r;
end;
function mycls(id int, r_c1 int, r_c2 int) return mycls
as
begin
this.id = id;
r.c1 = r_c1;
r.c2 = r_c2;
return this;
end;
end;
/
创建类头和类体
函数f1返回rec_type类型，把a值赋给对象r.c1,b值赋给r.c2，函数mycls把r对象值覆盖
Alter class sysdba.mycls compile
重编译

Drop class if exists sysdba.mycls
删除类

java类
create or replace java class jac2{
int a;
public static int a1(int a, int b)
{
return ab;
}
public int a2(int a,int b,int c){
return ab*c;
}
}

create table tab2(c1 int,c2 jac2 );
insert into tab2 values(jac2.a1(1,2),jac2(1));
insert into tab2 VALUES(jac2.a2(1,2,3),jac2(13));//出现报错
insert into tab2 VALUES(jac2().a2(1,2,3),jac2(23));
成员方法调用之前必须实例化
Insert into tab2 values(jac2().a2(12,33,12),jac2(13));

函数
创建函数
CREATE OR REPLACE FUNCTION dd(a INT) RETURN VARCHAR(10) AS
BEGIN
IF (a<0) THEN
RETURN ’ a<0’;
ELSE
PRINT ‘没有 RUTURN’;
END IF;
END;
创建一个输入a为int类型，返回值为varchar类型的函数

存储过程
CREATE OR REPLACE PROCEDURE SYSDBA.SS(a IN OUT INT) AS
b INT:=10;
BEGIN
a:=a+b;
PRINT a;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
创建SYSDBA模式下名为SS的存储过程
a为输入输出类型为int类型，输出a为a+b的值

语句块操作
创建语句块
CREATE OR REPLACE PROCEDURE BLOCK AS
X INT := 0;
COUNTER INT := 0;
BEGIN
FOR I IN 1 … 4 LOOP
X := X + 1000;
COUNTER := COUNTER + 1;
PRINT CAST(X AS CHAR(10)) || CAST(COUNTER AS CHAR(10)) || ‘OUTER LOOP’;
DECLARE
X INT := 0;
BEGIN
FOR I IN 1 … 4 LOOP
X := X + 1;
COUNTER := COUNTER + 1;
PRINT CAST(X AS CHAR(10)) || CAST(COUNTER AS CHAR(10)) || ‘INNER LOOP’;
END LOOP;
END;
END LOOP;
END;
创建语句块 BLOCK 设置全局变量X和COUNTER,在外层循环x=x+1000， counter=counter+1
内部嵌套循环局部变量先赋值x为0再每次都使x=x+1， counter=counter+1

游标
创建游标
DECLARE
CURSOR SS IS SELECT DEPTINO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTION
BEGIN FOR S IN SS LOOP PRINT S.DEPTON|| ’ ’ ||S.AVGSAL; END LOOP; END;
显式游标，直接定义时定义语句
BEGIN FOR S IN SELECT DEPTINO,AVG(SAL) AS AVGSAL FROM EMP WHERE DEPTON=10
LOOP PRINT S.DEPTON|| ’ ’ ||S.AVGSAL; END LOOP; END;
隐式游标，在begin里定义
DECLARE
SS NUMBER(5);
DD emp%ROWTYPE;
CURSOR CC IS SELECT * FORM EMP WHERE DEPT=SS;
BEGIN
SS:=20
OPRN CC;
LOOP
FITCH CC INTO DD;
EXIT WHEN CC%NOTFOUND;
PRINT DD .EMPNO||’ ‘|| DD.ENAME||’ ‘||DD.SAL;
END LOOP;
CLOSE CC; END;
%TYPE - 引用变量和数据库列的数据类型
%ROWTYPE - 提供表示表中一行的记录类型
属性类型为行记录的DD
定义游标CC定义语句
打开cc 游标，对游标进行检索，当ss=20时把游标记录存到DD,
当没有找到时关闭游标，找到输出
.EMPNO||’ ‘|| DD.ENAME||’ '||DD.SAL;值
循环开启之前要开启游标，关闭循环后关闭游标
DECLARE
TYPE CURTYPE IS REL CURSOR
SS CURTYPE
SSS EMP%ROWTYPE
BEGIN OPEN SS FOR SELECT *FROM EMP;
LOOP
FETCH SS INTO SSS
EXIT WHEN SS%NOTFOUND;
PRINT SSS.EMPNO|| ’ ’ || SSS.ENAME
END LOOP
CLOSE SS;
END;
创建REF游标类型为CURTYPE
SS为CURTYPE类型，隐式定义ss

动态sql
动态SQL
-基本语法
EXECUTE IMMEDIATE <SQL动态语句文本> [USING <参数> {,<
参数>}];
参数绑定
指定参数有两种方式，一种是用“?”来表示；另一只是用
“:variable”来表示
execute immediate
选择结构——IF语句
基本语法
IF condition1 THEN statements1;
[ELSEIF condition2 THEN statements2;]
……
[ELSE else_statements];
END IF; - IF语句中的条件是一个布尔型变量或表达式，取值只能是TRUE，FALSE，NULL
选择结构——搜索CASE语句
基本语法
CASE
WHEN condition1 THEN statements1;
WHEN condition2 THEN statements2;
……
WHEN conditionn THEN statementsn;
[ELSE else_statements;]
END CASE;

循环结构——基本循环语句
基本语法
LOOP
sequence_of_statement;
EXIT [WHEN condition] ;
END LOOP;
注意：在循环体中一定要包含EXIT语句，否则程序进入死循环

循环结构——WHILE循环语句
基本语法
WHILE condition LOOP
sequence_of_statement;
END LOOP;

循环结构——FOR循环语句
基本语法
FOR loop_counter IN [REVERSE] low_bound…high_bound
LOOP
sequence_of_statement;
END LOOP; - 系统默认时，循环变量从下界往上界递增计数，如果使用REVERSE关
键字，则表示循环变量从上界向下界递减计数；
循环变量只能在循环体中使用，不能在循环体外使用

跳转结构
基本语法
《标号》
…
GOTO 标号； - 块内可以跳转，内层块可以跳到外层块，但外层块不能跳到内层。
IF语句不能跳入。不能从循环体外跳入循环体内。不能从子程序外
部跳到子程序中。
由于goto语句的缺点，建议尽量少用甚至不用goto语句

自定义异常
概念
异常处理机制

• 在存储模块的正常执行过程中，可能会出现未预料的事件，这就是
  异常(EXCEPTION)。
  异常事件会导致代码的不正确结束，因此用户需要在程序中对异常
  进行处理，以保证不可预知的错误不会终止DM PL/SQL模块。为此
  DM提供了异常处理机制，使用户可以在语句块的异常处理部分中
  处理异常。
  异常，可以是系统预定义异常，也可以是用户自定义异常

预定义异常
异常情况名 错误代码 描述
DUP_AVL_ON_INDEX -6602 违反唯一性约束
INVALID_CURSOR -4535 无效的游标操作
NO_DATA_FOUND -7065 数据未找到
TOO_MANY_ROWS -7046 SELECT INTO中包含多行数据
ZERO_DIVIDE -6103 除0错误

自定义异常
在声明部分为错误定义异常
<异常变量名> EXCEPTION;
PRAGMA EXCEPTION_INIT(<异常变量名>, <错误号>);
在执行过程中当错误产生时抛出与错误对应的异常
RAISE <异常名>;
在异常处理部分通过异常处理器捕获异常，并进行异常处理
EXCEPTION
WHEN exception1[OR excetpion2…]THEN statements1;
……
WHEN OTHERS THEN statementsn;
END;

OTHERS异常
OTHERS异常处理器是一个特殊的异常处理器，可以捕获所有的异常。
通常，OTHERS异常处理器总是作为异常处理部分的最后一个异常处理器
，负责处理那些没有被其他异常处理器捕获的异常。

获取错误信息
SQLCODE：返回当前错误代码
SQLERRM：返回当前错误的消息文本

创建自定义异常
DECLARE
AA NUMBER(5);
BB EXCIPTION;
PRAGMA EXCPTION_INIT CC=-13456;
BEGIN
UPDATE EMP SET SAL=SAL+100 WHERE EEE=1000 RETURNING SAL INTO AA;
IF AA>6000 THEN
RAISE BB;
END IF; COMMIT; PRINT ‘SAL:’||AA
EXCEPTION
WHEN BB THEN
PRINT ‘TOO LATGE’
WHEN OTHERS THEN
PRINT ‘SOME WRONG’;
ROLLBACK; END;
定义AA 和异常类型BB
定义错误号-13456
修改表参数并给定义的AA
判断AA是否大于6000
如果有则抛出异常BB,
PRINT ‘TOO LATGE’
如果有别的异常出现但没有被捕获
PRINT ‘SOME WRONG’;

没有则PRINT ‘SAL:’||AA
EXCEPTION

社区地址：https://eco.dameng.com