PLSQL.docx

1、PLSQL常见访问oracle的技术PLSQL 过程化sql procedure(存储过程)pro*c/c+ 使用c和c+访问oracle数据库odbc/ado vc访问数据库的技术sqlj/jdbc java访问数据库的技术PL/SQLPL/SQL(Procedural Language/SQL)是在标准SQL的基础上增加了过程化处理的语言。plsql扩展了sql 变量和数据类型 控制结构 if for goto 过程和函数 对象类型和方法PL/SQL程序结构QL/SQL块 声明部分， declare /声明区，定义变量和定义类型 执行部分， begin /执行区，执行sql语句和qlsql

2、语句 异常处理， exception /异常处理区，处理错误的区域 结束， end；sqlplus 命令行下的开发工具图形开发工具: plsqldeveloper sqlprogramer设置输出set serveroutput on /设置输出begin dbms_output.put_line(hello plsql);end;/1.变量的声明和定义declare var_id number:=100; /*变量定义和初始化*/ var_name varchar2(30):=test;begin dbms_output.put_line(var_id|:|var_name);end;2.变

3、量的赋值declare var_id number; /*变量定义和初始化*/ var_name varchar2(30);begin var_id:=1001; var_name:=test; dbms_output.put_line(var_id|:|var_name);end;3.变量的修饰 constant not null eg. var_id constant number:= 100; var_name varchar2(30) not null:=a;一个变量声明为not null必须赋初值，因为任何一个变量定义之后不赋值 值都是null。4变量数据类型1. 标量型(基本类型)

4、a) 数字型(number,binary_integer)b) 字符型(char,varchar2(0-4K)c) 布尔型(boolean : true,false,NULL)d) 日期型(date)2. 组合型a) RECORD,TABLE,3. 参考类型 引用类型a) ref cursor4. 大类型a) CLOB(0-4G)b) BLOB(0-4G)c) BFILE(0-4G) (一般不使用这种类型，因为存储数据的路径就可以解决问题)eg.NUMBER(4，3) 123.4567 /错误超出精度NUMBER(3，-3) 1234 /1000NUMBER(4，6) 0.0012345 /0

5、.0012355.定义两个变量 分别和s_emp 表中id和first_name的类型相同 把id=1的数据赋值给这个两个变量 然后输出 declare var_id number(7); var_name varchar2(25);begin select id,first_name into var_id,var_name from s_emp where id = 1; dbms_output.put_line(var_id|var_name);end;6. 使用%type得到表的字段对应的类型 表名.字段名%type declare var_id s_emp.id%type; var_

6、name s_emp.first_name%type;begin select id,first_name into var_id,var_name from s_emp where id = 2; dbms_output.put_line(var_id|:|var_name);end;7.把s_emp表中的id frist_name salary对应的类型取出来定义变量，然后把id=1的数据赋值给first_name salary输出这些信息8.记录类型 record 类型 /*定义一个类似于c语言结构体类型*/ type 类型名 is record( 字段名 类型， 字段名 类型， 字段名

7、 类型 ); 把s_emp表中的id frist_name salary对应的类型取出来定义变量，然后把id=1的数据赋值给first_name salary输出这些信息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_emp2 emptype;begin -使用sql给记录类型的变量赋值 select id,first_name,salary into

8、 var_emp2 from s_emp where id = 1; var_emp:=var_emp2; -整体赋值dbms_output.put_line(var_emp.id| |var_emp.name| |var_emp.salary);end; 当查询出的字段少于记录中的字段时，单独使用记录中的字段，没有赋值的字段是null：select first_name,salary into var_emp.name,var_emp.salary from s_emp where id = 1; 记录中的字段赋值var_emp.name:=var_emp2.name; -(c语言用strc

9、py,区别)var_emp.salary:=var_emp2.salary;表名%rowtype 取得表的一行对应的类型 s_dept%rowtype s_emp%rowtypeeg: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| |var_emp.salary);end;/%rowtype就是一个字段名和表头中的字段名和字段顺序完全相同

10、的记录类型。类似于c数组的类型 table类型TYPE tabletype IS TABLE OF type INDEX BY BINARY_INTEGER;行的数目由BINARY_INTEGER的范围决定：-214743648-214743647Key没有必要是顺序的 可以用first last next方法遍历declare -定义一个table类型 type numstype is table of s_emp.id%type index by binary_integer; var_nums numstype; var_ind binary_integer:=1;begin var_n

11、ums(1):=100; var_nums(2):=200; var_nums(3):=600; select salary into var_nums(4) from s_emp where id=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_l

12、ine(var_nums(var_ind);end;-/*table 下标不连续 迭代器思想 first()得到第一个元素对应的下标 last() 最后一个元素对应的下标 next(n)根据一个元素的下标得到下一个元素的下标 */declare -定义一个table类型 type numstype is table of s_emp.id%type index by binary_integer; var_nums numstype; var_ind binary_integer:=1;begin var_nums(1):=100; var_nums(2):=200; var_nums(3):

13、=600; select salary into var_nums(4) from s_emp where id=1; 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.last(); d

14、bms_output.put_line(var_nums(var_ind);end;declare type tb is table of s_emp%rowtype index by binary_integer; tb1 tb; bid binary_integer:=1;begin select * into tb1(1) from s_emp where id = 3; -得到一条记录 select * into tb1(5) from s_emp where id = 7; bid:=tb1.first(); dbms_output.put_line(tb1(bid).id| |tb

15、1(bid).first_name); bid:=tb1.next(bid); dbms_output.put_line(tb1(bid).id| |tb1(bid).first_name);end;变量的作用域和 可见性 -指定标签abc declare -嵌套 var_m number:=1; begin declare var_m number:=100; var_n number:=100; begin /*局部可以访问全局的*/ /*同名则局部优先*/ dbms_output.put_line(var_m); -访问局部变量 dbms_output.put_line(abc.var_

16、m); -变量前面加全局标签访问 end; /*全局不能访问局部的*/ dbms_output.put_line(var_n); -错误 end;1分支语句 a. if ab then end if; b. if ab then else end if; c. if ab then elsif ac then elsif ad then end if;eg:declare a number:=10; b number:=20; c number:=5;begin if ab then a:=b; elsif ac then a:=c; else dbms_output.put_line(a i

17、s the max number); end if; dbms_output.put_line(the max number:|a);end;declare var_a number; var_b number; var_c number; var_d number; var_e number; var_max number;begin var_a := &var_a; var_b := &var_b; var_c := &var_c; var_d := &var_d; var_e := &var_e; var_max:=var_a; if var_maxvar_b then var_max:

18、=var_b; end if; if var_maxvar_c then var_max:=var_c; end if; if var_maxvar_d then var_max:=var_d; end if; if var_max 10 then exit; end if; var_i := var_i + 1; -exit when var_i 10; end loop; dbms_output.put_line(loop over);end; b.while 循环 1.while 循环的语法 while 循环条件 loop end loop; 2.while循环举例：输出1到10decl

19、are var_i number;begin var_i := 1; while var_i = 10 loop dbms_output.put_line(var_i); var_i := var_i + 1; end loop;end; 3.while循环的退出(同loop) exit when 退出条件 if 退出条件 then exit; end if; c. for循环-智能循环 1.for循环的语法 for 变量 in a.b loop end loop; 2.for 循环举例：输出1到10begin for var_i in 1.10 loop dbms_output.put_li

20、ne(var_i); end loop;end; 3.for循环中的变量不允许修改 4.如何反向输出 输出10到1begin for var_i in reverse 1.10 loop dbms_output.put_line(var_i); end loop;end; 5.for循环的退出，同while d.循环的嵌套 1.例子 3.使用类似于c语言的goto语句 4.plsql中可以把标签放前面 3.goto 语句 使用goto完成输出1到10 plsql 中的 sql使用 select 语句要和into 结合使用。 dml (insert delete update) tcl (com

21、mit rollback savepoint) 可以直接在plsql中使用 DDL(create drop alter)不能直接在plsql中使用，需要使用动态sql。-回顾：常见的访问oracle的技术plsqlproc/c+odbc/adosqlj/jdbcplsql的程序结构declare /*申明区 定义变量 和 类型*/begin /*执行区 执行sql语句和plsql语句*/exception /*异常处理区 处理程序错误的*/end;-plsql中定义变量1.使用sql中类型来定义变量 变量名 类型;2.binary_integer boolean3.变量的修饰 constant

22、 not null4.给变量手动赋值 使用sql语句给变量赋值5.表名.字段名%type 取得表的字段对应的类型6.记录类型 type 类型名 is record( 字段名 类型, 字段名 类型, 字段名 类型 ); 如何给记录类型赋值 当查询的字段数少于记录类型字段数 记录类型之间的赋值 整体 选择字段赋值 7.表的一行对应的类型 表名%rowtype 8.table 类型 类似c语言的数组 type 类型名 is table of 表中的类型 index by binary_integer; 变量名(下标):=值; 当下标连续时的遍历 当下标不连续 迭代器思想 first() last()

23、 next(n)-变量的作用域和可见性 局部优先 -plsql的控制语句1.分支语句 if 条件 then end if; if 条件 then else end if; if 条件 then elsif 条件 then elsif 条件 then else end if;2.循环语句 a.简单循环 loop end loop; b.while 循环 while 循环条件 loop end loop; c.for 循环 for 变量 in a.b loop end loop; - d.循环的退出 exit when 退出条件 if 退出条件 then exit; end if; f.循环的嵌套

24、 通过在内层循环中改变条件 退出外层循环 使用goto 可以直接在内层循环退出外层循环 使用exit 标签名- 3.goto 语句 使用goto 完成输出1 到 10 declare var_i number; begin var_i:=1; if var_i11 then dbms_output.put_line(var_i); var_i:=var_i+1; goto myloop; end if; dbms_output.put_line(myloop over!); end;-plsql 中的sql使用 1.select 语句要和 into 结合使用。 2.dml(insert del

25、ete update) tcl(commit rollback savepoint) 可以直接在plsql中使用 3.DDL(create drop alter)不能直接在plsql 中使用,需要使用动态sql。动态sql：把一个字符串对应的sql当做sql语句来执行 create table testsql(id number); /静态的，运行后不能修改 create table testsql(id number) 1.DDL:的动态sql -DDL必须要用动态的sql 2.DML的动态sql -为了解决字符串拼接的问题 使用占位符简化拼接占位符冒号开头 使用using替代占位符号。 3

26、. select 语句的动态sql 能且只能返回一个结果 游标：用来处理多行数据的一个数据类型 一条sql语句可以认为是匿名游标1. 游标 的语法a) 声明游标curson 游标名 is sql语句；b) 打开游标open 游标名；c) 提取数据i. 非滚动游标 提取数据必须是顺序的ii. fetch 游标名 into 变量名；d) 关闭游标close游标名;2. 例：把s_emp表中所有的数据放入一个游标中i. 输出前两天的id first_name salary 3.遍历游标中所有的数据 a) 游标的属性 %found 是否发现新数据 游标必须处于打开状态 如果没有打开游标使用则返回非法游标。 游标至少被fetch过一次 如果没fetch 则返回NULL值。 发现了新数据就返回true，否则返回false。 %notfound 是否没有发现新数据 游标必须处于打开状态 否则是非法游标 没有fetch 则返回NULL值 发现了新数据就返回 false,没有发现新数据就返回true. %isopen 游标是否打开 打开就返回true 否则返回false 打开的游标不能在打开，关闭的游标不能在关闭。 %r