oracle pl(学习笔记-网).docx

oracle pl/sql 基础PL/SQL笔记PL/SQL块中只能直接嵌入SELECT,DML(INSERT,UPDATE,DELETE)以及事务控制语句(COMMIT,ROLLBACK,SAVEPOINT),而不能直接嵌入DDL语句(CREATE,ALTER,DROP)和DCL语句(GRANT,REVOKE) 1.检索单行数据 1.1使用标量变量接受数据 v_ename emp.ename%type; v_sal emp.sal%type; select ename,sal into v_ename,v_sal from emp where empno=&no;-从键盘取值 1.2使用记录变量接受数据 type emp_record_type is record( v_ename emp.ename%type,v_sal emp.sal%type); -定义一个类型，名字为emp_record_type ,该类型包括两个数据，v_ename和v_sal;这一个类型可以存放两个数据， emp_record emp_record_type;-定义一个变量，类型是新定义的记录类型的变量， begin select ename,sal into emp_record from emp where empno=&no;-取2个变量赋值到新的记录类型中。 Dbms_output.put_line(emp_record.v_name); 1.3嵌入SELECT语句注意事项: 使用SELECT INTO语句时,必须要返回一条数据,并且只能返回一条数据 no_date_found: select into没有返回数据 too_many_rows: select into返回多条数据 where子句使用注意事项: 使用的变量名不能与列名相同,否则触发TOO_MANY_ROWS例外. 2.操纵数据 2.1使用VALUES子句插入数据 v_deptno dept.deptno%type; v_dname dept.dname%type; v_deptno:=no; v_dname:='&name' insert into dept (deptno,dname) values(v_deptno,v_dname); 2.2使用子查询插入数据 v_deptno emp.deptno%type:=&no; insert into employee select * from emp where deptno=v_deptno; 2.3更新数据 使用表达式更新列值 v_deptno dept.deptno%type:=no; v_loc dept.loc%type:='&loc' update dept set loc=v_loc where deptno=v_deptno; 2.4使用子查询更新列值 v_ename emp.ename%type:='&name' update emp set (sal,comm) = (select sal,comm from emp where ename=v_ename) where job = (select job from emp where ename=v_ename) 2.5删除数据 使用变量删除数据 v_deptno dept.deptno%type:=&no; delete from dept where deptno=v_deptno; 2.6使用子查询删除数据 v_ename emp.ename%type:='&name' delete from emp where deptno=(select deptno from emp where ename=v_ename); 3.SQL游标 游标是指向上下文区的指针,包括隐含游标(SQL游标)和显式游标两种类型 SQL游标用于处理SELECT INTO ,INSERT,UPDATE以及DELETE语句. 显式游标用于处理多行的SELECT语句 SQL游标包括:SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT,SQL%ISOPEN等四种属性 3.1 SQL%ISOPEN:执行时,会隐含的打开和关闭游标.因此该属性的值永远都是FALSE 3.2 SQL%FOUND:用于确定SQL语句执行是否成功.当SQL有作用行时,为TRUE,否则为FALSE v_deptno emp.deptno%type:=&no; update emp set sal=sal*1.1 where deptno=v_deptno; if sql%found then dbms_output.put_line('执行成功'); else dbms_output.putline('失败'); endif 3.3 sql%notfound:确定SQL语句执行是否成功,当SQL有作用行时,为false,否则为true 3.4 sql%rowcount:返回SQL语句所作用的总计行数 v_deptno emp.deptno%type:=&no; update emp set sal=sal*1.1 where deptno=v_deptno; dbms_output.put_line('修改了'|sql%rowcount|'行');显示游标事例：declare v_empno emp.empno%type; v_ename emp.ename%type; v_depno emp.depno%type; cursor emp_cursor is select empno,ename,depno from emp where depno=10;-定义区不能有into子句，可用orderby 分组查询，指定游标存储一个select查询的结果集； begin open emp_cursor;-是在执行定义区的select语句查询结果存在游标工作区中，指针指向第一列 loop fetch emp_cursor into v_empno,v_ename,v_depno;-into的变量要和游标中定义相同； exit when emp_cursor%notfound;-跳出循环，当没有数据响应时 dbms_output.put_line(To_char(v_empno)|' '|(v_ename)|' '|(v_depno); end loop; close emp_cursor; end; -游标可以循环(1)Loop exit when(2)for in ().()loop End loop;For 循环省去游标声明、打开、提取、测试、关闭等语句DECLARECURSOR emp_cursor IS -SELECT last_name, department_idFROM employees;BEGINFOR emp_record IN emp_cursor LOOP-*_record为指定语法- implicit open and implicit fetch occurIF emp_record.department_id = 80 THEN.END LOOP; - implicit close occursEND;游标可以带参数DECLARECURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) ISSELECT employee_id, last_nameFROM employeesWHERE department_id = p_deptnoAND job_id = p_job;BEGINOPEN emp_cursor (80, 'SA_REP');. . .CLOSE emp_cursor;OPEN emp_cursor (60, 'IT_PROG');. . .END; 4.事务控制语句 事务控制语句包括COMMIT,ROLLBACK以及SAVEPOINT等三种语句 v_sal emp.sal%type:=&salary; v_ename emp.ename%type:='&name' update emp set sal=v_sal where ename=v_ename; commit; exception when others then rollback; insert into temp values(1); savepoint a1; insert into temp values(2); savepoint a2; insert into temp values(3); savepoint a3; rollback to a2; commit; 5.控制结构 条件分支语句 5.1简单条件判断 v_sal number(6,2); select sal into v_sal from emp where lower(ename)=lowe('&&name'); if v_sal<2000 then update emp set sal=v_sal+200 where lower(ename)=lower('&name') end if 5.2二重条件分支 v_comm number(6,2); select comm into v_comm from emp where empno=&&no; if v_comm<>0 then update emp set comm=v_comm+100 where empno=&no; else update emp set comm=200 where empno=&no; end if 5.3多重条件分支 v_job varchar2(10); v_sal number(6,2); select job,sal into v_job,v_sal from emp where empno=&&no; if v_job='president' then update emp set sal=v_sal+1000 where empno=&no; elseif v_job='manager' then update emp set sal=v_sal+500 where empno=&no; else update emp set sal=v_sal+200 where empno=&no; end if; 5.4 CASE语句: 在CASE语句中使用单一选择符进行等值比较 declare v_deptno emp deptno%type; begin v_deptno:=&no; case v_deptno when 10 then update emp set comm=100 where deptno=v_deptno; when 20 then update emp set comm=80 where deptno=v_deptno; when 30 then update emp set comm=50 where deptno=v_deptno; else dbms_output.put_line("不存在'); end case; end; 5.5 在CASE语句中使用多种条件比较 declare v_sal emp.sal%type; v_ename emp.ename%type; begin select ename,sal into v_ename,v_sal from emp where empno=&no; case when v_sal<1000 then update emp set comm=100 where ename=v_ename; when v_sal<2000 then update emp set comm=80 where ename=v_ename; when v_sal<6000 tehn update emp set comm=50 where ename=v_ename; end case; end; 5.6循环语句 有基本循环,WHILE循环,FOR循环 基本循环:一定要包含EXIT语句,定义循环控制变量 create table temp(cola int); declare i int:=1; begin loop insert into temp values(i); exit when i=10; i:=i+1; end loop; end; 5.7 WHILE循环:定义循环控制变量,并在循环体内改变循环控制变量的值 declare i int:=1; begin while i<=10 loop insert into temp values(i); i:=i+1; end loop; end; 5.8 for循环:使用FOR循环时,ORACLE会隐含定义循环控制变量. for counter inreverse lower_bound.upper_bound loop statement1; statement2; . end loop; 5.9 counter是循环控制变量,并且该变量由ORACLE隐含定义,不需要显示定义;lower_bound和upper_bound分别对应循环控制变量的上下界值.默认情况下,FOR循环,每次会自动增一,指定REVERSE选项时,每次循环控制变量会减一 begin for i in reverse 1.10 loop insert into temp values(i); end loop; end; 5.10嵌套循环和标号:通过在嵌套循环中使用标号,可以区分内层循环和外层循环,并且可以在内层循环中直接退出外层循环 declare result int; begin <<outer>> for i in 1.100 loop <<inter>> for j in 1.100 loop result:=i*j; exit outer when result=1000; exit when result=500; end loop inner; dbms_ouput.put_line(result); end loop outer; dbms_output.put_line(result); end; 6.顺序控制语句 PL/SQL不仅提供了条件分支语句和循环控制语句,而且还提供了顺序控制语句GOTO和NULL.一般情况下不使用 6.1 GOTO:用于跳转到特定标号处去执行语句. GOTO LABEL_NAME; declare i int :=1; begin loop insert into temp values(i); if i=10 then goto end_loop end if; i:=i+1; end loop; <<end_loop>> dbms_output.put_line('循环结束'); end; 6.2 null:不会执行任何操作,并且会直接将控制传递到下一条语句. declare v_sal emp.sal%type; v_ename emp.ename%type; begin select ename,sal into v_ename,v_sal from emp where empno=&no; if v_sal<3000 then update emp set comm=sal*0.1 where ename=v_ename; else null; end if; end; 7.复合数据类型 7.1定义PL/SQL记录 自定义PL/SQL记录:需要定义PL/SQL记录类型和记录变量,identifier用于指定记录变量名 type type_name is record( field_declaration, field_declaration. ); identifier type_name; declare type emp_record_type is record( name emp.ename%type, salary em.sal%type, dno emp.deptno%type); emp_record emp_record_type; 使用%rowtype属性定义记录变量:可以基于表或视图定义记录变量 当使用%ROWTYPE属性定义记录变量时,记录成员个数,名称,类型与表或视图列的个数，名称,类型完全相同. dept_record dept%rowtype; emp_record emp%rowtype; 在select into 语句中使用PL/SQL 记录 在select into 语句中使用记录变量 set serveroutput on declare type emp_record_type is record( name emp.ename%type, salary em.sal%type, dno emp.deptno%type); emp_record emp_record_type; begin select ename,sal,deptno into emp_record from emp where empno=&no; dbms_output.put_line(emp_record.name); end; 7.2在select into 语句中使用记录成员 declare type emp_record_type is record( name emp.ename%type, salary emp.sal%type, dna emp.deptno%type); emp_record emp_record_type; begin select ename,sal into emp_record.name,emp_record.salary from emp where empno=&no; dbms_output.put_line(emp_record.name); end; 7.3在insert语句中使用PL/SQL记录 在VALUES子句中使用记录变量 declare dept_record dept%rowtype; begin dept_record.deptno:=50; dept_record.dname:='administrator' dept_record.loc:='beijing' insert int dept values dept_record; end; 在VALUES子句中使用记录成员 declare dept_record dept%rowtype; begin dept_record.deptno:=60; dept_record.dname:='sales' insert into dept (deptno,dname) values (dept_record.deptno,dept_record.dname); end; 7.4在UPDATE语句中使用PL/SQL记录 在SET子句中使用记录变量 declare dept_record dept%rowtype; begin dept_record.deptno:=30; dept_record.dnama:='sales' dept_record.loc:='shanghai' update dept set row=dept_record where deptno=30; end; 在SET子句中使用记录成员 declare dept_record dept%rowtype; begin dept_record.loc:='guangzhou' update dept set loc=dept_record.loc where deptno=10; end; 7.5在DELETE语句中使用PL/SQL记录:只能在DELETE语句中的WHERE子句中使用记录成员 declare dept_record dept%rowtype; begin dept_record.deptno:=50; delete from dept where deptno=dept_record.deptno; end; 8.pl/sql集合 处理单行单列数据,可以使用标量变量,处理单行多列的数据，可以使用PL/SQL记录 处理单列多行数据,可以使用PL/SQL集合 PL/SQL集合类型类似于高级语言数组的一种复合数据类型 包括:索引表(PL/SQL表),嵌套表(NESTED TABLE),变长数组(VARRAY)三种 8.1 索引表:PL/SQL表 元素个数没有限制,并且下标可以是负值 定义索引表: type type_name is table of element_type not null index by key_type; identifier type_name; 在索引表中使用BINARY_INTEGER和PLS_INTEGER set serveroutput on declare type ename_table_type is table of emp.ename%type index by binary_integer; ename_table ename_table_type; begin select ename into ename_table(-1) from emp where empno=&no; dbms_output.put_line('雇员名:'|ename_table(-1); end; 在索引表中使用VARCHAR2 set serveroutput on declare type area_table_type is table of number index by varchar2(10); area_table area_table_type; begin area_table('北京'):=1; area_table('上海'):=2; area_table('广州'):=3; dbms_output.put_line('第一个元素:'|area_table.first); dbms_output.put_line('最后一个元素:'|area_table.last); end; 8.2 嵌套表:元素个数从1开始,并且元素个数没有限制 定义嵌套表: type type_name is table of element_type; identifier type_name; declare type ename_table_type is table of emp.ename%type; -定义一个sql的表类型，名字为type_name;,该类型是用于存放emp.ename%type（相当于varchar2）这种类型的数组 ename_table ename_table_type:=ename_table_type('A','A'); 在PL/SQL块中使用嵌套表:使用嵌套表变量时,必须首先使用构造方法初始化嵌套表变量,然后才能在块内引用嵌套表元素 declare type ename_talbe_type is table of emp.ename%type; ename_talbe ename_table_type;-定义一个ename_table的 变量，变量类型是ename_table_type; begin ename_table:=ename_table_type('mary','mary','mary'); select ename into ename_table(2) from emp where empno=&no;-查询结果放在下标为2的空间里 dbms_output.put_line('雇员名:'|ename_table(2); end; 在表列中使用嵌套表: 在表列中使用嵌套表类型,必须首先使用CREATE TYPE命令建立嵌套表类型. 当使用嵌套表类型作为表列的数据类型时,必须要为嵌套表列指定专门的存储表 create type phone_type is table of varchar2(20); / create table employee( id number(4),name varchar2(10),sal number(6,2), phone phone_type )nested table phone store as phone_table; 8.3 在PL/SQL块中为嵌套表列插入数据 当定义嵌套表类型时,ORACLE自动为该类型生成相应的构造方法.当为嵌套表列插入数据时,需要使用嵌套表的构造方法 begin insert into employee values(1,'scott',800,phone_type('0471-',''); end; 在PL/SQL块中检索嵌套表列的数据 当在PL/SQL块中检索嵌套表列的数据时,需要定义嵌套表类型的变量接受其数据. set serveroutput on declare phone_table phone_type; begin select phone into phone_table from employee where id=1; for i in 1.phone_table.count loop dbms_output.put_line('电话:'|phone_table(i); end loop; end; 8.4 在pl/sql块中更新嵌套表列的数据 更新嵌套表列的数据时,首先需要定义嵌套表变量,并使用构造方法初始化变量,然后才可在执行部分使用UPDATE语句更新数据 declare phone_table phone_type:=phone_type('0471-','','0471-',''); begin update employee set phone=phone_talbe where id=1; end; 8.5变长数组(varray) VARRAY也是一种用于处理PL/SQL数组的数据类型, 它也可以做为表列的数据类型使用. 元素下标以1开始,并且元素的最大个数是有限制的 定义VARRAY的语法: type type_name is varray(size_limite) of element_type not mull; identifier type_name; 当使用VARRAY元素时,必须要使用其构造方法初始化VARRAY元素. declare type ename_table_type is varrar(20) of emp.ename%type; ename_talbe ename_table_type:=ename_table_type('A','A'); 8.6 在PL/SQL块中使用VARRAY 必须首先使用其构造方法来初始化VARRAY变量,然后才能在PL/SQL块内引用VARRAY元素 declare type ename_table_type is varray(20) of emp.ename%type; ename_table ename_table_type:=ename_table_type('mary'); begin select ename into ename_table(1) from emp where empno=&no; dbms_output.put_line('雇员名:'|ename_table(1); end; 在表列中使用varray 要在表列中引用该数据类型,则必须使用CREATE TYPE命令建立VARRAY类型 create type phone_type is varray(20) of varchar2(20); / create table employee( id number(4),name varchar2(10), sal number(6,2),phone phone_type); 在PL/SQL块中操纵VARRAY列的方法与操纵嵌套表列的方法完全相同.嵌套表列元素个数没有限制,而VARRAY列的元素个数是有限制的. PL/SQL记录表 PL/SQL变量处理单行单列数据 PL/SQL记录处理单行多列数据 PL/SQL集合处理多行单列数据 PL/SQL记录表处理多行多列数据 8.7 PL/SQL记录表结合了PL/SQL记录和PL/SQL集合的优点 declare type emp_talbe_type is table of emp%rowtype index by binary_integer; emp_table emp_table_type; begin select * into emp_table(1) from emp where empno=&no; dbms_output.put_line('雇员姓名:'|emp_table(1).ename); dbms_output.put_line('雇员姓名:'|emp_table(1).sal); end; 8.8 多级集合 多级集合是指嵌套了集合类型的集合类型 在PL/SQL块中使用多级VARRAY:实现多维数组功能 定义二维VARRAY(10,10): declare type a1_varray_type is varray(10) of int;-定义一维VARRAY type na1_varray_type is varray(10) of a1_varray_type;-定义二维VARRAY集合