5.3.2 PLSQL

PLSQL

Day01

一.常见的访问oracle的技术

plsql 过程化(procedure)sql 存储过程

proc/proc++ 使用c或者c++ 来访问oracle

odbc/ado vc中访问数据库的技术

oci oracle提供的底层接口

sqlj/jdbc java 访问数据库的技术

二.plsql 的特点

结构化 模块化编程

良好的可维护性

良好的可移植性 (同类型)

不便于向异构数据库移植

提升系统性能

三.plsql 扩展了sql

变量和类型

控制语句

函数和过程

对象和方法

四.plsql 程序结构

declare

​ /* 申明区

​ 声明变量 定义类型 */

begin

​ /* 执行区

​ 执行sql 语句 或者 plsql语句 */

exception

​ /* 异常处理区

​ 处理异常 */

end;

/

五.第一个plsql程序

begin

​ dbms_output.put_line(‘hello plsql!’);

end;

/

设置输出为打开状态

set serveroutput on

开发工具:

命令行:sqlplus

图形化:windows plsqldeveloper

​ sqlprogramer

标示符:给变量 类型 游标 过程 函数 等命名的

六 变量

​ 6.1 如何声明变量

​ declare

​ 变量名 类型;

​ begin

​ / 如果一个变量没有赋值 则值为NULL /

​ declare

​ var_id number;

​ var_name varchar2(30);

​ begin

​ DBMS_OUTPUT.PUT_LINE(var_id||’:’

​ ||var_name);

​ end;

​ /

6.2 plsql如何给变量赋值 和 初始化都是

​ 用 :=

​ declare

​ var_id number:=0;

​ var_name varchar2(30):=’a’;

​ begin

​ var_id:=100;

​ var_name:=’test’;

​ DBMS_OUTPUT.PUT_LINE(var_id||’:’

​ ||var_name);

​ end;

​ /

6.3 变量的数据类型

​ 标量类型: number char varchar2 date

​ binary_integer boolean

​ 组合类型:record table cursor

​ 参考类型:ref cursor

​ 大类型:BLOB CLOB BFILE

​ (存储数据的位置 varchar2)

6.4 binary_integer boolean

​ declare

​ var_ind binary_integer;

​ var_f boolean;

​ begin

​ var_ind:=1000;

​ / var_f:=false; /

​ var_f:=true;

​ if var_f then

​ dbms_output.put_line(var_ind);

​ end if;

​ end;

​ /

6.5 变量的修饰

​ constant 类型 not null

​ declare

​ var_name varchar2(30) not null:=’gg’;

​ var_salary constant number:=10000;

​ begin

​ var_name:=’hello’;

​ — var_salary:=10001;

​ dbms_output.put_line(var_name||’:’||

​ var_salary);

​ end;

​ /

6.6 写一个plsql 定义两个变量 分别和s_emp

表中 first_name 和salary字段的类型相同。然后

把id=1 的first_name 和 salary 分别赋值给这

两个变量。打印这两个变量的值。

declare

​ var_name varchar2(25);

​ var_sal number(11,2);

begin

​ var_name:=’Carmen’;

​ var_sal:=1600;

​ dbms_output.put_line(var_name||’:’

​ ||var_sal);

end;

/

6.7 表名.字段名%type 可以直接获取表的字段

​ 对应的类型。

​ 可以使用sql 语句 对变量进行赋值。

declare

​ var_name s_emp.first_name%type;

​ var_sal s_emp.salary%type;

begin

​ select first_name,salary

​ into var_name,var_sal

​ from s_emp

​ where id=25;

​ dbms_output.put_line(var_name||’:’

​ ||var_sal);

end;

/

6.8 类似于c语言 结构体的类型 record类型

​ 6.8.1 语法

​ type 类型名 is record(

​ 字段名 类型,

​ 字段名 类型,

​ 字段名 类型

​ );

​ 变量名 类型名;

​ 6.8.2 把s_emp 表的id,first_name,salary

​ 包装成一个记录类型。然后把id=2 相关数据

​ 放入这个记录类型的变量中。然后输出这个

​ 变量的值。

​ declare

​ / 定义一个记录类型 /

​ type emptype is record(

​ id s_emp.id%type,

​ name s_emp.first_name%type,

​ salary s_emp.salary%type

​ );

​ var_emp emptype;

​ begin

​ select id,first_name,salary into var_emp

​ from s_emp

​ where id=2;

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.name||’:’||var_emp.salary);

​ end;

​ /

6.8.3 记录类型之间的赋值

​ declare

​ / 定义一个记录类型 /

​ type emptype is record(

​ id s_emp.id%type,

​ name s_emp.first_name%type,

​ salary s_emp.salary%type

​ );

​ var_emp emptype;

​ var_emp1 emptype;

​ begin

​ select id,first_name,salary into var_emp1

​ from s_emp

​ where id=2;

​ /* 整体赋值

​ var_emp:=var_emp1; */

​ / 选择部分字段赋值 /

​ var_emp.name:=var_emp1.name;

​ var_emp.salary:=var_emp1.salary;

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.name||’:’||var_emp.salary);

​ end;

​ /

6.8.4 如果查询字段数据 少于记录类型字段数

​ declare

​ / 定义一个记录类型 /

​ type emptype is record(

​ id s_emp.id%type,

​ name s_emp.first_name%type,

​ salary s_emp.salary%type

​ );

​ var_emp emptype;

​ begin

​ select first_name,salary

​ into var_emp.name,var_emp.salary

​ from s_emp

​ where id=2;

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.name||’:’||var_emp.salary);

​ end;

​ /

6.9 思考如何把s_emp 表中 id=1 的所有信息放入

​ 一个记录类型的变量中?

​ 表名%rowtype 获得表的一行对应的类型

​ s_dept%rowtype

​ s_emp%rowtype

​ %rowtype 获取的类型 和 表头中字段名 和 字段

​ 的顺序完全相同。

​ declare

​ var_emp s_emp%rowtype;

​ begin

​ select * into var_emp from s_emp

​ where id=1;

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.first_name);

​ end;

​ /

6.10 类似于c数组的数据结构 table类型

​ 6.10.1 语法

​ type 数组类型名 is table of

​ 元素类型名 index by binary_integer;

​ 数组变量 数组类型;

​ 6.10.2 定义一个装 整数的数组类型 然后使用

​ 这个类型定义变量 把这些数(9,5,2,7)放入这

​ 个变量中。

​ 数组变量(下标):=值;

​ declare

​ type numstype is table of number

​ index by binary_integer;

​ var_nums numstype;

​ begin

​ var_nums(-1):=9;

​ var_nums(1):=5;

​ var_nums(0):=2;

​ var_nums(2):=7;

​ dbms_output.put_line(var_nums(2));

​ end;

​ /

6.10.3 知道下标从几开始

​ 知道元素个数

​ 知道下标是连续的

​ declare

​ type numstype is table of number

​ index by binary_integer;

​ var_nums numstype;

​ var_ind binary_integer;

​ begin

​ var_nums(-1):=9;

​ var_nums(1):=5;

​ var_nums(0):=2;

​ var_nums(2):=7;

​ var_ind:=-1;

​ dbms_output.put_line(var_nums(var_ind));

​ var_ind:=var_ind+1;

​ dbms_output.put_line(var_nums(var_ind));

​ var_ind:=var_ind+1;

​ dbms_output.put_line(var_nums(var_ind));

​ var_ind:=var_ind+1;

​ dbms_output.put_line(var_nums(var_ind));

​ end;

​ /

6.10.4 不知道下标从几开始

​ 不知道元素个数

​ 不知道下标是连续的

迭代器思想

first() 返回第一个元素对应的下标

next(n) 根据第一个元素的下标 返回 下一个

​ 元素对应的下标。

last() 返回最后一个元素对应的下标。

​ declare

​ type numstype is table of number

​ index by binary_integer;

​ var_nums numstype;

​ var_ind binary_integer;

​ begin

​ var_nums(-1):=9;

​ var_nums(1):=5;

​ var_nums(0):=2;

​ var_nums(3):=7;

​ var_ind:=var_nums.first();

​ dbms_output.put_line(var_nums(var_ind));

​ var_ind:=var_nums.next(var_ind);

​ dbms_output.put_line(var_nums(var_ind));

​ var_ind:=var_nums.next(var_ind);

​ dbms_output.put_line(var_nums(var_ind));

​ var_ind:=var_nums.next(var_ind);

​ dbms_output.put_line(var_nums(var_ind));

​ end;

​ /

6.10.5 定义一个table类型 用来存放 s_emp 表

id=1 id=11 id=20 的所有信息。存放的下标就

是id对应值。输出这些信息的 id first_name.

declare

​ type empstype is table of s_emp%rowtype

​ index by binary_integer;

​ var_emps empstype;

​ var_id number:=1;

begin

​ select * into var_emps(var_id)

​ from s_emp

​ where id=var_id;

​ var_id:=11;

​ select * into var_emps(var_id)

​ from s_emp

​ where id=var_id;

​ var_id:=20;

​ select * into var_emps(var_id)

​ from s_emp

​ where id=var_id;

​ / 使用迭代器遍历 /

​ var_id:=var_emps.first();

​ dbms_output.put_line(var_emps(var_id).id

​ ||’:’||var_emps(var_id).first_name);

​ var_id:=var_emps.next(var_id);

​ dbms_output.put_line(var_emps(var_id).id

​ ||’:’||var_emps(var_id).first_name);

​ var_id:=var_emps.next(var_id);

​ dbms_output.put_line(var_emps(var_id).id

​ ||’:’||var_emps(var_id).first_name);

end;

/

七.变量的作用域 和 可见性

​ 局部区 可以访问全局区的数据

​ 全局区 不可以访问局部区的数据

​ / 这是plsql中的标签 /

​ <>

​ declare

​ var_n number:=1;

​ begin

​ declare

​ var_n number:=99;

​ begin

​ dbms_output.put_line(var_n);

​ dbms_output.put_line(abclab.var_n);

​ end;

​ end;

​ /

八.plsql 中的控制语句

​ 8.1 分支语句

​ 8.1.1 语法

​ if(a>b){ if a>b then

​ } end if;

​ if(a>b){ if a>b then

​ }else{ else

​ } end if;

​ if(a>b){ if a>b then

​ }else if(a>c){ elsif a>c then

​ }else if(a>d){ elsif a>d then

​ } end if;

​ if(a>b){ if a>b then

​ }else if(a>c){ elsif a>c then

​ }else if(a>d){ elsif a>d then

​ }else{ else

​ ; NULL;

​ } end if;

​ 8.1.2 定义三个整数变量 给这三个变量赋值

​ 然后输出这三个变量的最大值。

​ declare

​ var_a number:=9;

​ var_b number:=88;

​ var_c number:=0;

​ begin

​ if var_a<var_b then

​ if var_b<var_c then

​ dbms_output.put_line(var_c);

​ else

​ dbms_output.put_line(var_b);

​ end if;

​ else

​ if var_a < var_c then

​ dbms_output.put_line(var_c);

​ else

​ dbms_output.put_line(var_a);

​ end if;

​ end if;

​ end;

​ /

​ declare

​ var_a number:=9;

​ var_b number:=88;

​ var_c number:=0;

​ var_max number;

​ begin

​ var_max:=var_a;

​ if var_max<var_b then

​ var_max:=var_b;

​ end if;

​ if var_max<var_c then

​ var_max:=var_c;

​ end if;

​ dbms_output.put_line(var_max);

​ end;

​ /

​ 8.1.3 NULL 值的运算特点

​ declare

​ var_x number;

​ var_y number;

​ begin

​ if var_x < var_y then

​ dbms_output.put_line(‘var_x<var_y’);

​ elsif var_x is null

​ and var_y is null then

​ dbms_output.put_line(‘var_x is null

​ and var_y is null’);

​ elsif var_x > var_y then

​ dbms_output.put_line(‘var_x>var_y’);

​ elsif var_x = var_y then

​ dbms_output.put_line(‘var_x=var_y’);

​ end if;

​ end;

​ /

​ 8.2 循环语句

​ 8.2.1 简单循环的语法

​ loop

​ / 循环语句 /

​ end loop;

​ 8.2.2 如何退出循环

​ exit when 结束条件;

​ if 结束条件 then

​ exit;

​ end if;

​ 8.2.3 使用 简单循环 从1 输出到10

​ declare

​ var_i number:=1;

​ begin

​ loop

​ dbms_output.put_line(var_i);

​ exit when var_i=10;

​ var_i:=var_i+1;

​ end loop;

​ end;

​ /

​ 8.2.4 while 循环

​ while 循环条件 loop

​ / 循环代码 /

​ end loop;

​ 使用while 循环从1 输出到 10

​ declare

​ var_i number:=1;

​ begin

​ while var_i<11 loop

​ dbms_output.put_line(var_i);

​ var_i:=var_i+1;

​ end loop;

​ end;

​ /

​ declare

​ var_i number:=10;

​ begin

​ while var_i>0 loop

​ dbms_output.put_line(var_i);

​ exit when var_i=5;

​ var_i:=var_i-1;

​ end loop;

​ end;

​ /

​ 8.2.5 for 循环 (智能循环)

​ for 变量 in a..b loop

​ / 循环代码 /

​ end loop;

​ 使用for 循环输出 1 到 10

​ begin

​ for var_a in 1..10 loop

​ dbms_output.put_line(var_a);

​ end loop;

​ end;

​ /

​ begin

​ for var_a in reverse 1..10 loop

​ dbms_output.put_line(var_a);

​ exit when var_a=4;

​ end loop;

​ end;

​ /

​ for 循环中的变量 不允许外界修改

​ begin

​ for var_a in reverse 1..10 loop

​ dbms_output.put_line(var_a);

​ var_a:=11;

​ end loop;

​ end;

​ /

8.3 goto 语句

​ 8.3.1 语法

​ goto 标签名;

​ 8.3.2 使用 goto 完成 输出1到10

​ declare

​ var_i number:=1;

​ begin

​ <>

​ if var_i < 11 then

​ dbms_output.put_line(var_i);

​ var_i:=var_i+1;

​ goto myloop;

​ end if;

​ end;

​ /

​ 8.3.3

​ 判断退出的是内层循环

​ 但是我想退出外层循环 ?

​ begin

​ <>

​ for x in 1..4 loop

​ for y in 1..4 loop

​ dbms_output.put_line(y);

​ if y=3 then

​ exit myouterloop;

​ end if;

​ end loop;

​ end loop;

​ end;

​ /

​ begin

​ for x in 1..4 loop

​ for y in 1..4 loop

​ dbms_output.put_line(y);

​ if y=3 then

​ goto myouterloop;

​ end if;

​ end loop;

​ end loop;

​ <>

​ NULL;

​ end;

​ /

Day02

一.plsql 中如何使用sql语句

1.1 如何使用select语句

​ select语句要和into 结合使用 把查询的

​ 数据放入变量中。

​ select first_name,salary into var_a,var_b

​ from s_emp

​ where id=1;

1.2 dml(insert delete update)

​ tcl(commit rollback savepoint)

​ 直接在plsql中使用

​ insert into testlog values(1,’test’);

​ commit;

1.3 ddl(creat table

​ drop table

​ alter table)

​ 不能直接在plsql中使用 需要使用动态sql

二.动态sql

​ 2.1 概念

​ sql语句在程序执行的过程中可以改变。

​ 2.2 实现

​ 把sql语句 做成字符串

​ 2.3 举例

​ declare

​ sqlstr varchar2(100);

​ begin

​ sqlstr:=’create table testdsql(

​ id number)’;

​ sqlstr:=substr(sqlstr,1,

​ length(sqlstr)-1);

​ sqlstr:=sqlstr||’, name varchar2(30))’;

​ dbms_output.put_line(sqlstr);

​ execute immediate sqlstr;

​ end;

​ /

2.4 dml 的动态sql

​ declare

​ sqlstr varchar2(100);

​ begin

​ sqlstr:=’insert into testdsql values(

​ 100,’’test100’’)’;

​ dbms_output.put_line(sqlstr);

​ execute immediate sqlstr;

​ commit;

​ end;

​ /

​ declare

​ sqlstr varchar2(100);

​ var_id number:=9527;

​ var_name varchar2(30):=’zhouxingxing’;

​ begin

​ sqlstr:=’insert into testdsql values(‘;

​ sqlstr:=sqlstr||var_id;

​ sqlstr:=sqlstr||’,’’’||var_name||’’’)’;

​ dbms_output.put_line(sqlstr);

​ end;

​ /

2.5 使用占位符号 简化字符串的拼接

​ declare

​ sqlstr varchar2(100);

​ var_id number:=9527;

​ var_name varchar2(30):=’zhouxingxing’;

​ begin

​ sqlstr:=

​ ‘insert into testdsql values(:b0,:b1)’;

​ execute immediate sqlstr using

​ var_id,var_name;

​ commit;

​ end;

​ /

2.6 select 语句也可以是字符串

​ 必须是普通的select 不能带into

​ 只能返回一个结果

三.游标

​ 1.1 概念

​ 是一个数据缓冲区

​ 1.2 使用步骤

​ 定义游标

​ declare

​ cursor 游标名 is select语句;

​ 打开游标

​ open 游标名;

​ 提取数据 处理数据

​ fetch 游标名 into 变量;

​ 关闭游标

​ close 游标名;

​ 1.3 把s_emp 表中所有的数据 放入一个游标

​ 中 然后提取前两条数据 并打印这些数据

​ 的id first_name salary。

​ declare

​ cursor empcursor is

​ select * from s_emp;

​ var_emp empcursor%rowtype;

​ begin

​ open empcursor;

​ fetch empcursor into var_emp;

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.first_name||’:’||var_emp.salary);

​ fetch empcursor into var_emp;

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.first_name||’:’||var_emp.salary);

​ close empcursor;

​ end;

​ /

​ 1.4 游标属性

​ 游标名%found 在提取游标数据时 如果提取到了

​ 新数据则found属性返回真。如果没有提取到

​ 新数据则返回假。游标必须处于打开状态否

​ 则返回非法游标。游标必须fetch 否则返回

​ NULL。

​ 游标名%notfound 在提取游标数据时 如果提取到了

​ 新数据则返回假。如果没有提取到

​ 新数据则返回真。游标必须处于打开状态否

​ 则返回非法游标。游标必须fetch 否则返回

​ NULL。

​ 1.5 使用简单循环 结合 notfound 属性遍历游标

​ declare

​ cursor empcursor is

​ select * from s_emp;

​ var_emp empcursor%rowtype;

​ begin

​ open empcursor;

​ loop

​ fetch empcursor into var_emp;

​ / 当发现不了新数据时 结束循环 /

​ exit when empcursor%notfound;

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.first_name||’:’||var_emp.salary);

​ end loop;

​ close empcursor;

​ end;

​ /

​ 1.6 使用 while循环 结合 found 属性

​ 遍历上面的游标。

​ declare

​ cursor empcursor is

​ select * from s_emp;

​ var_emp empcursor%rowtype;

​ begin

​ open empcursor;

​ fetch empcursor into var_emp;

​ while empcursor%found loop

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.first_name||’:’||var_emp.salary);

​ fetch empcursor into var_emp;

​ end loop;

​ close empcursor;

​ end;

​ /

1.7 游标的其它属性

​ 游标名%isopen 游标是否处于打开状态

​ 如果打开 则返回真

​ 否则返回假

​ 打开的游标不能再打开

​ 关闭的游标不能再关闭

​ 游标名%rowcount 游标指针偏移量

1.8 for 循环遍历游标

​ 智能循环 会自动定义变量 自动打开 提取和关闭

​ declare

​ cursor empcursor is

​ select * from s_emp;

​ begin

​ for var_emp in empcursor loop

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.first_name||’:’||var_emp.salary);

​ end loop;

​ end;

​ /

1.9 带参游标

​ plsql 中参数不能加任何长度修饰

​ 但是可以使用%type

​ 需要打开游标时传参

​ declare

​ cursor empcursor(var_id number) is

​ select * from s_emp where id>var_id;

​ var_emp empcursor%rowtype;

​ begin

​ open empcursor(15);

​ fetch empcursor into var_emp;

​ while empcursor%found loop

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.first_name||’:’||var_emp.salary);

​ fetch empcursor into var_emp;

​ end loop;

​ close empcursor;

​ end;

​ /

​ declare

​ cursor empcursor(var_id number) is

​ select * from s_emp where id>var_id;

​ begin

​ for var_emp in empcursor(20) loop

​ dbms_output.put_line(var_emp.id||’:’||

​ var_emp.first_name||’:’||var_emp.salary);

​ end loop;

​ end;

​ /

1.10 参考游标(引用游标)

​ 游标 + 动态sql

​ 1.定义参考游标类型

​ type 参考类型名 is ref cursor;

​ 2.使用参考游标类型 定义游标变量

​ 变量名 参考类型名;

​ 3.把字符串关联到游标变量

​ open 游标变量名 for sqlstr;

​ ‘select * from s_emp’

​ declare

​ type myrefcursor is ref cursor;

​ var_empcursor myrefcursor;

​ sqlstr varchar2(100);

​ var_emp s_emp%rowtype;

​ begin

​ sqlstr:=’select * from s_emp’;

​ / 把游标关联到字符串 /

​ open var_empcursor for sqlstr;

​ / 游标一旦打开就是普通的游标 /

​ loop

​ fetch var_empcursor into var_emp;

​ exit when var_empcursor%notfound;

​ dbms_output.put_line(var_emp.id||’:’

​ ||var_emp.salary);

​ end loop;

​ close var_empcursor;

​ end;

​ /

​ declare

​ type myrefcursor is ref cursor;

​ var_empcursor myrefcursor;

​ sqlstr varchar2(100);

​ var_emp s_emp%rowtype;

​ begin

​ sqlstr:=’select * from s_emp

​ where id>:b0’;

​ / 把游标关联到字符串 /

​ open var_empcursor for sqlstr using 10;

​ / 游标一旦打开就是普通的游标 /

​ loop

​ fetch var_empcursor into var_emp;

​ exit when var_empcursor%notfound;

​ dbms_output.put_line(var_emp.id||’:’

​ ||var_emp.salary);

​ end loop;

​ close var_empcursor;

​ end;

​ /

二.异常

系统定义的异常

自定义异常

2.1 当程序发生异常 plsql的默认处理方式

declare

​ var_name varchar2(30);

begin

​ dbms_output.put_line(‘app start!’);

​ select first_name into var_name

​ from s_emp where id>1;

​ dbms_output.put_line(‘var_name=’||

​ var_name);

​ dbms_output.put_line(‘app over!’);

exception

​ when no_data_found then

​ dbms_output.put_line(‘no emp ‘);

​ when too_many_rows then

​ dbms_output.put_line(‘too many emp ‘);

​ when others then

​ dbms_output.put_line(‘have exception!’);

end;

/

2.2 用户自定义异常

​ 1.定义异常

​ 异常变量 exception;

​ 2.根据条件 抛出异常

​ raise 异常变量;

​ 3.捕获异常

​ when 异常变量 then

​ 4.处理异常

declare

​ too_many_emp exception;

begin

​ if 1=1 then

​ raise too_many_emp;

​ end if;

exception

​ when too_many_emp then

​ dbms_output.put_line(‘have many emp’);

​ when others then

​ dbms_output.put_line(‘others’);

end;

/

三.存储过程

3.1 概念

匿名块

有名块(给具有业务含义的一组语句起一个名字

这个名字可以保存到数据库中)

3.2 存储过程的语法

create or replace procedure progetmax(

var_x number,var_y number:=10)

is

begin

​ if var_x < var_y then

​ dbms_output.put_line(var_y);

​ else

​ dbms_output.put_line(var_x);

​ end if;

end;

/

3.3 如何查看存储过程

​ desc 过程名;

​ desc progetmax;

​ select text from user_source

​ where name=’PROGETMAX’;

SQL> desc progetmax;

PROCEDURE progetmax

Argument Name Type In/Out Default?


VAR_X NUMBER IN

VAR_Y NUMBER IN DEFAULT

参数的名字

参数的类型

参数的模式 in 默认的模式

​ out

​ in out

参数的默认值 如果这个参数不传参 则使用

​ 默认值。

3.4 调用

begin

​ progetmax(1);

​ progetmax(9527,100);

​ progetmax(5,3);

end;

/

declare

​ x number:=1000;

​ y number:=200;

begin

​ progetmax(x,y);

end;

/

3.5 设计一个存储过程 传入一个整数参数

​ 这个参数的默认值是10。参数的作用代表

​ 从1输到的数。写出这个存储过程 并调用

​ 这个存储过程。1,2,9,11。

​ create or replace procedure printNnum10(

​ var_n in number:=10)

​ is

​ begin

​ for var_num in 1..var_n loop

​ dbms_output.put_line(var_num);

​ end loop;

​ end;

​ /

​ begin

​ printNnum10(1);

​ printNnum10(2);

​ printNnum10(9);

​ printNnum10(11);

​ end;

​ /

3.6 参数的模式

​ in 负责给存储过程传入参数

​ out 负责给存储过程传出参数

​ in out 既负责传入 又负责传入

​ void foo(int* x){

​ *x=1001;

​ }

​ int main(){

​ int x=10;

​ foo(&x);

​ x

​ }

​ 设计一个存储过程 传入三个整数参数

​ 然后把前两个参数的和 放入第三个参数中

​ create or replace procedure getSum(

​ x in number,y in number,z out number)

​ is

​ begin

​ z:=x+y;

​ end;

​ /

​ / 参数的位置赋值 /

​ declare

​ z number;

​ begin

​ getSum(1,2,z);

​ dbms_output.put_line(z);

​ end;

​ /

​ / 参数的名字赋值 /

​ declare

​ z number;

​ begin

​ getSum(x=>1,z=>z,y=>3);

​ dbms_output.put_line(z);

​ end;

​ /

3.6 设计一个存储过程 有两个整数参数

​ 打印这两个参数的最大值。并且把这两个

​ 参数的和放入第二个参数中。

​ 调用验证这个存储过程。

​ create or replace procedure getMaxAndSum

​ (x in number,y in out number)

​ is

​ begin

​ if x<y then

​ dbms_output.put_line(y);

​ else

​ dbms_output.put_line(x);

​ end if;

​ y:=x+y;

​ end;

​ /

​ declare

​ y number:=15;

​ begin

​ getMaxAndSum(100,y);

​ dbms_output.put_line(y);

​ end;

​ /

四.函数

​ 4.1 存储过程 和 函数的区别

​ 关键字不同 过程是procedure 函数是function

​ 函数有返回值类型 函数有返回值 过程没有

​ 调用方式不同 过程可以直接在plsql中调用

​ 函数必须组成表达式才能调用。

​ 4.2 设计一个函数 传入两个整数参数 返回最大值

​ create or replace function getmax(

​ x in number,y in number)return number

​ is

​ begin

​ if x<y then

​ return y;

​ end if;

​ return x;

​ end;

​ /

测试函数

select text from user_source

​ where name=’GETMAX’;

select getmax(1,100) from dual;

函数的调用

declare

​ var_z number:=1;

begin

​ var_z:=getmax(1,111);

​ dbms_output.put_line(var_z);

end;

/

4.3 设计一个函数 传入两个整数参数

返回两个参数的最大值。并且把两个参数

的和放入第二个参数中。

create or replace function getMaxSum(

​ x in number,y in out number)return number

​ is

​ var_temp number;

​ begin

​ var_temp:=y;

​ y:=x+y;

​ if x<var_temp then

​ return var_temp;

​ else

​ return x;

​ end if;

​ end;

​ /

​ declare

​ x number:=1;

​ y number:=1000;

​ z number:=0;

​ begin

​ z:=getMaxSum(x,y);

​ dbms_output.put_line(‘y=’||y);

​ dbms_output.put_line(‘z=’||z);

​ end;

​ /

create or replace procedure fpx11(

​ x in munber,y in out number)

is

begin

if x>y then

dbms_output.put_line(x);

else

dbms_output.put_line(y);

end if;

y:=x+y;

dbms_output.put_line(y);

end;

/

show errors

五.包 package

​ 5.1 概念

​ 把一组逻辑相关的数据 包装成一个逻辑名。

​ 5.2 系统提供的包

​ 使用包中的数据 需要在数据前 加包名。

​ 查看包 desc 包名;

​ dbms_output

​ dbms_output.put_line(‘hello world’);

​ dbms_random

​ select dbms_random.value(1,100)

​ from dual;

​ select trunc(dbms_random.value(1,100))

​ from dual;

​ dbms_job 定时执行存储过程

​ uc crontab

​ 1.建立一张表

​ create table test1402job(

​ id number primary key,

​ name varchar2(30)

​ );

​ 2.建立一个序列

​ create sequence test1402job_id;

​ 3.写一个存储过程 向表中放入一条数据

​ create or replace procedure

​ insert1402_job

​ is

​ begin

​ insert into test1402job values(

​ test1402job_id.nextval,

​ ‘test’||test1402job_id.currval);

​ commit;

​ end;

​ /

​ 4.dbms_job.submit(

​ JOB BINARY_INTEGER

​ WHAT VARCHAR2

​ NEXT_DATE DATE

​ INTERVAL VARCHAR2

​ );

​ 把定时任务提交给系统 系统会分配一个

​ 任务编号,把编号的值放入第一个参数中。

​ 第二个参数 是调用的存储过程名

​ 第三个参数 第一次调用的时间

​ 第四个参数 第二次调用的时间 第四个

​ 参数和 第三个参数的差就是周期。

​ dbms_job.run(JOB BINARY_INTEGER);

​ 把任务编号是job的任务执行起来。

​ \5. 周期性调用 存储过程

​ declare

​ jobno binary_integer;

​ begin

​ dbms_job.submit(jobno,

​ ‘insert1402_job;’,

​ sysdate,’sysdate+1/(24*60)’);

​ dbms_output.put_line(‘jobno=’||jobno);

​ dbms_job.run(jobno);

​ end;

​ /

​ select * from test1402job;

​ select job from user_jobs;

​ 6.删除定时任务

​ begin

​ dbms_job.remove(1422);

​ end;

​ /

​ 5.3 用户自定义包

​ 在包中的数据前 加包名。

​ 相当于c语言的头文件

​ create or replace package mypack

​ is

​ procedure packgetmax(x number,

​ y number);

​ function packgetmin(x number,

​ y number) return number;

​ end;

​ /

​ 写一个包的实现 相当于c中的实现文件

​ create or replace package body mypack

​ is

​ procedure packgetmax(x number,

​ y number)

​ is

​ begin

​ if x<y then

​ dbms_output.put_line(y);

​ else

​ dbms_output.put_line(x);

​ end if;

​ end;

​ function packgetmin(x number,

​ y number) return number

​ is

​ begin

​ if x<y then

​ return x;

​ end if;

​ return y;

​ end;

​ end;

​ /

​ 调用

​ begin

​ mypack.packgetmax(1,100);

​ end;

​ /

六.触发器 trigger

​ 6.1 在进行dml(insert delete update)操作时

​ 系统可以根据我们的操作 做出相应的响应。

​ 6.2 如何在一张表上建立触发器

​ create table testemp1021 as select

​ id,first_name,salary from s_emp;

​ create or replace trigger 触发器名

​ before|after insert|delete|update

​ on 表名

​ declare

​ begin

​ end;

​ /

​ create or replace trigger testemp1021_update

​ after update on testemp1021

​ declare

​ begin

​ dbms_output.put_line(‘update testemp1021’);

​ end;

​ /

​ update testemp1021 set salary=salary+100

​ where id=1;

​ update testemp1021 set salary=salary+100

​ where id>1;

​ update testemp1021 set salary=salary+100

​ where id<1;

​ 上面的触发器 无论sql语句影响多少行 只触发

​ 一次 这叫语句级触发器。

​ 希望针对影响没一行都做触发,这样就可以拿到

​ 具体影响的数据。行级触发器。

​ create or replace trigger testemp1021_update

​ after update on testemp1021 for each row

​ declare

​ begin

​ dbms_output.put_line(:old.id||’:’

​ ||:old.salary);

​ dbms_output.put_line(‘update testemp1021’);

​ dbms_output.put_line(:new.id||’:’

​ ||:new.salary);

​ end;

​ /

​ update testemp1021 set salary=salary+100

​ where id=1;

​ update testemp1021 set salary=salary+100

​ where id>1;

​ update testemp1021 set salary=salary+100

​ where id<1;

​ update :old :new

​ delete :old

​ insert :new

​ 触发器中要注意不能使用事务控制语句。


打赏

微信支付 支付宝支付

License

本作品由Simonhttp://www.uusystem.com)创作,采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。 欢迎转载,但任何转载必须保留完整文章,在显要地方显示此声明以及原文链接。如您有任何疑问或者授权方面的协商,请邮件:postmaster@uusystem.com。