Oracle(培训班带出来)

1、.：.；PL/SQL简介 Oracle公司在规范SQL言语的根底上开展出了PL/SQL(Procedural Language/SQL,即过程化SQL言语)言语，将变量、控制构造、过程和函数等构造化程序设计的要素引入SQL言语中，这样就可以编写出较复杂的SQL程序了。以bdyd的身份登录，创建一个用户表testtablecreate table testtable( recordNumber number(4) not null, currentDate date not null);向上述数据库表中插入100条记录 set serveroutput ondeclare maxrecords

2、constant int:=100; i number :=1;begin for i in 1.maxrecords loop insert into testtable(recordnumber,currentdate) values(i,sysdate); end loop;dbms_output.put_line(胜利录入数据！);commit;end;其中，dbms_output为系统默许的程序包，put_line是包中定义的方法，功能是输出信息。PL/SQL程序构造结合上述实例进展分析，一个完好的PL/SQL程序的构造可以分成以下3部分：1定义部分：以declare开场，定义在程序

3、中所要运用的常量、变量等等。它不像高级言语那样可以在程序执行过程中进展定义，一切用到的内容都必需在declare中进展定义。2执行部分：以begin开场，以end终了，中间部分是对数据库的操作语句和各种流程控制语句。3异常处置部分：该部分包含在执行部分里面，以exception标识，该部分用来对运用程序产生的例外进展处置。一个完好的PL/SQL的构造 declare 定义部分 begin 执行部分 exception 异常处置部分 end在上述3部分中，只需begin.end部分是不可短少的1)begin.end部分set serveroutput onbegin dbms_output.pu

4、t_line(helloworld);end;2)declare 部分set serveroutput ondeclare v_stuName varchar2(20);begin v_stuName := zhangsan; dbms_output.put_line(v_stuName);end;declare v_stuName varchar2(20) not null :=lisi;begin commit;end;3)exception 异常处置部分set serveroutput ondeclare v_num number:=0;begin v_num := 9/v_num; d

5、bms_output.put_line(v_num);exception when others then dbms_output.put_line(error);end;常量常量的定义： 常量名 constant 数据类型 := 值例如： declare PI constant number(9) := 3.1415926; begin commit; end;变量1根本数据类型变量的定义： 变量名 数据类型 not null := 初始值例如: declare age number(3) :=25; begin commit; end;2复合数据类型变量：法一：运用%type定义变量 为了

6、让变量的类型和数据库表中字段的数据类型一致，Oracle9i提供了%type的定义方法。这样当数据库表中字段的类型修正后，PL/SQL程序中相应变量的类型也自动修正。 例如:declare mydate testtable.currentDate%type;begin commit;end;法二：记录类型变量很多构造化程序设计言语都提供了记录类型的数据类型，在PL/SQL中，也支持将多个根本数据类型捆绑在一同的记录数据类型。例如：declare type stuInfo is record( stuName varchar2(20), stuAge number, birthday date)

7、; myInfo stuInfo;begin myInfo.stuName := zhangsan;myInfo.stuAge := 25;myInfo.birthday := sysdate;dbms_output.put_line(myInfo.birthday);end;法三：运用%rowtype定义变量运用%type可以使得变量的类型和数据库表中字段的类型一致，而运用%rowtype可以使得变量的类型和整个记录的类型一致。declare mytable testtable%rowtype;begin select * into mytable from testtable where

8、recordnumber=99; dbms_output.put_line(mytable.currentdate);end;declare mydept dept%rowtype;beginselect * into mydept from dept where deptno=10;dbms_output.put_line(mydept.loc);end;在上述例如中，定义了一个变量mytable，它的类型和数据库表testtable的构造一样。表达式常量,变量经常要组合成表达式来进展各种运算，下面引见在PL/SQL中常见表达式的运算规那么。1算术表达式:算术运算符：+ - * / *例如：

9、set serveroutput ondeclare result number;begin result := 10+4*5+5*2; dbms_output.put_line(运算结果是|to_char(result);end;declare area number; radius number := 3;begin area := 3.14*radius*radius; dbms_output.put_line(area);end;还有关系表达式、逻辑表达式等等。条件控制1) ifelse if 条件 then 语句段1 else 语句段2 end if;例如： set serverou

10、tput ondeclare number1 number :=90; number2 number :=30;begin if number1=number2 then dbms_output.put_line(number1=number2); else dbms_output.put_line(number1=85 then dbms_output.put_line(good); else if score=75 and score=90 then dbms_output.put_line(A); when score=80 and score=89 then dbms_output.p

11、ut_line(B); when score between 70 and 79 then dbms_output.put_line(C); else dbms_output.put_line(below C); end case;end;循环控制1)loopexitend loop loop 循环体 if 条件语句 then exit; else 退出循环的语句处置 end if; end loop;例如：set serveroutput ondeclare number1 number :=80; number2 number :=90; i number := 0;begin loop

12、number1:=number1+1; if number1=number2 then exit; else i := i+1; end if; end loop; dbms_output.put_line(共循环次数:|to_char(i);end;2)loopexit when.end loop exit when 等价于 if 条件 then exit； end if；例如：set serveroutput ondeclare number1 number :=80; number2 number :=90; i number := 0;begin loop number1:=numbe

13、r1+1; i := i+1; exit when number1=number2; end loop; dbms_output.put_line(共循环次数:|to_char(i);end;3) whileloopend loop while 条件 loop 循环体 end loop; 例如：set serveroutput ondeclare number1 number :=80; number2 number :=90; i number := 0;begin while number120;insert into view1 values(15,aaa,aaa);select * f

14、rom view1;select * from dept;create or replace view view2 as select * from dept where deptno20 with check optioncreate or replace view view3 as (复杂的sql)当视图基于多个表时不让插入数据create or replace view view4 as select * from dept with read onlyselect text from user_views where where view_name=view4;同义词:以king登陆C

15、reate synonym dept for scott.dept;Select * from dept;用另一用户衔接时不可访问-私有同义词System/managerDrop synonym dept;Create public synonym dept from scott.dept;序列Create sequence myseqStart with 1Increment by 1OrderNocycleSelect myseq.nextval from dual;Select myseq.currval from dual;Insert into testable values(mys

16、eq.nextval,sysdate);Desc dba_sequences;Select sequence_name,sequence_owner from dba_sequence where sequence_name=myseq;Alter sequence myseq increment by 3;Select myseq.nextval from dual;游标什么是游标？游标是构建在PL/SQL中，用来查询数据，获取记录集合的指针。它可以让开发者一次访问结果集中的一行。游标可以以编程的方式访问数据， 从而完成需求分别在结果集中每个记录上执行的过程代码的义务。在Oracle中提供了

17、两种游标类型： 静态游标：在编译时知道其select语句的游标。静态游标又分成隐式游标和显示游标。 REF游标：游标运用的查询直到运转的时候才可以确定，实践上就是带参数的游标。隐式游标：PL/SQL为一切SQL数据支配语句隐式声明游标。之所以称为隐式游标，是由于用户不能直接命名和控制此类游标。当用户在PL/SQL中 运用数据支配语句时，Oracle预先定义一个名为SQL的隐式游标，经过检查隐式游标的属性可以获取最近执行的SQL语句的相关信息。在执行DML语句之后，隐式游标属性前往信息：%FOUND:只需在sql语句隐式一行或多行时，才前往true%NOTFOUND%ROWCOUNT:sql语句

18、影响的行数%ISOPEN:游标能否翻开，执行完sql语句后，Oracle自动封锁SQL游标，所以隐式游标的%ISOPEN属性一直为falsedeclare empid emp.empno%type; job emp.job%type;begin empid := &职员编号; select job into job from emp where empno=empid; if sql%rowcount0 then dbms_output.put_line(职员的头衔是|job); end if; exception when others then dbms_output.put_line(n

19、ot found);end;declare cursor c is select * from emp; firstemp c%rowtype;begin open c; fetch c into firstemp; dbms_output.put_line(firstemp.ename); close c;end;declare cursor c is select * from emp; firstemp c%rowtype; begin open c; loop fetch c into firstemp; exit when (c%notfound); dbms_output.put_

20、line(firstemp.ename); end loop; close c; end;declare cursor c is select * from emp; firstemp c%rowtype; begin open c; loop fetch c into firstemp; dbms_output.put_line(firstemp.ename); exit when (c%notfound); end loop; close c; end;declare cursor c is select * from emp; firstemp c%rowtype;begin open

21、c; fetch c into firstemp; while(c%found) loop dbms_output.put_line(firstemp.ename); fetch c into firstemp; end loop; close c;end;declare cursor c is select * from emp;begin for firstemp in c loop dbms_output.put_line(firstemp.ename); end loop;end;declare cursor c(v_deptno emp.deptno%type,v_job emp.j

22、ob%type) is select ename,sal from emp where deptno=v_deptno and job=v_job;begin for firstemp in c(30,CLERK) loop dbms_output.put_line(firstemp.ename); end loop;end;declare cursor c is select * from emp for update; begin for firstemp in c loop if (firstemp.salb) then ret := a; else ret := b; end if;

23、temp := temp+1;end;带参数存储过程的调用declare a number := 1; b number := 2; ret number; temp number := 3;begin p(a,b,ret,temp); dbms_output.put_line(ret); dbms_output.put_line(temp);end;关于show error;函数:与过程类似，也是数据库中存储的已命名的PL/SQL程序块，它的主要特征是必需前往一个值。函数的一些限制：1)函数只能带有in参数，不能带有out和in out参数2)形参只能运用数据库类型，不得运用PL/SQL类型

24、3)函数的前往类型也必需是数据库类型创建函数：create or replace function calculateTax (sal number) return numberisbegin if sal2000 then return 0.10; elsif sal=1); create table transInfo( cardId varchar2(15) not null, transType varchar2(10) not null, transMoney number not null); alter table transInfo add constraint ck2 che

25、ck(transType in(存入,取出); insert into bank values(zhangsan,1001,1000); insert into bank values(lisi,1002,1); insert into transInfo(cardId,transType,transMoney) values(1001,取出,200); select * from bank; select * from transInfo;当zhangsan取了200元时，虽然买卖信息表中保管了取款200元的信息，但帐户表中的余额依然是1000，并没有自动跟随修正。显然，我们应该 根据买卖类

26、型是“存入还是“取出，自动添加或减少帐户表中的余额。而且，它应该具有事务的特征：一旦买卖失败，对余额的修正也应 该自动取消。这个时候，就需求依托触发器的作用。触发器的分类：1)Insert触发器：当向表中插入数据时触发，自动执行触发器所定义的SQL语句。2) Update触发器：当更新表中某列、多列时触发，自动执行触发器所定义的SQL语句3)Delete触发器：当删除表中记录时触发，自动执行触发器所定义的SQL语句触发器的作用：强化约束，级联运转create table emp2_log(userName varchar2(20),action varchar2(20),actionTime

27、date)create or replace trigger mytrig after insert or delete or update on emp2 for each rowbegin if inserting then insert into emp2_log values(user,insert,sysdate); elsif updating then insert into emp2_log values(user,update,sysdate); elsif deleting then insert into emp2_log values(user,delete,sysda

28、te); end if;end;-create or replace trigger del_deptid after delete on dept for each rowbegin delete from emp where id=:old.id;end;delete from dept where deptno=10;rollback;-create or replace trigger insert_dept after insert on dept for each rowbegin insert into emp(empno,ename,deptno) values(111,111,:new.id);end;insert into dept val