pl\sql

minxinxin2010

浏览: 66135 次
性别:
来自: 成都

最近访客更多访客>>

xx5333

cao348922336

lhldyf

wssc198765

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

 --pl/sql输出 DECLARE i NUMBER; BEGIN i := 20; DBMS_OUTPUT.put_line('I的内容为：' || i); END; --pl/sql变量定义 DECLARE x emp.empno%type; y emp.job%type; z emp%rowtype; BEGIN select t.empno,t.job into x,y from emp t where t.empno = 7369; dbms_output.put_line(x); dbms_output.put_line(y); DBMS_OUTPUT.put_line(z.empno); END; --loop循环 declare cou number; begin cou := 1; loop dbms_output.put_line('数字='||cou); cou := cou + 1; exit when cou>10; end loop; end; --whlie使用 declare i number; begin i:=1; while(i<11) loop dbms_output.put_line('i='||i); i:=i+1; end loop; end; --for、loop使用 declare i number; begin for i in 1..10 loop dbms_output.put_line('i='||i); end loop; end; --if使用 declare i number; begin i := 10; if i>0 then dbms_output.put_line('i='||i); end if; end; --if...else使用 declare i number; begin i := 10; if i>=10 then dbms_output.put_line('i='||i); else dbms_output.put_line('i='||i); end if; end; --if...elsif...else使用 declare i number; begin i := 10; if i>10 then dbms_output.put_line('i='||i); elsif i > 10 then dbms_output.put_line('i='||i); else dbms_output.put_line('i='||i); end if; end; --goto语句 declare eno emp.empno%type; sal emp.sal%type; begin eno := &en; select t.sal into sal from emp t where t.empno=eno; if sal>3500 then goto po1; elsif sal>2000 then goto po2; else goto po3; end if; <<po1>> dbms_output.put_line('高工资...'); dbms_output.put_line(sal); <<po2>> dbms_output.put_line('中等工资...'); dbms_output.put_line(sal); <<po3>> dbms_output.put_line('低工资...'); dbms_output.put_line(sal); end; --rowtype使用 declare eno emp.empno%type; empinfo emp%rowtype; begin eno := &en; select * into empinfo from emp where empno = eno; dbms_output.put_line('雇员编号：' || empinfo.empno); dbms_output.put_line('雇员姓名：' || empinfo.ename); end; /* 1、声明游标 2、打开游标 3、取出结果，此时的结果取出的是一行数据 4、关闭游标 */ declare -- 声明游标 cursor mycur is select * from emp where empno = 7369; empinfo emp%rowtype; cou number; begin -- 游标操作使用循环，但是在操作之前必须先将游标打开 for empinfo in mycur loop cou := mycur%rowcount; dbms_output.put_line(cou || '雇员编号：' || empinfo.empno); dbms_output.put_line(cou || '雇员姓名：' || empinfo.ename); end loop; end; --编写游标输出表全部行 --方式一 declare -- 声明游标 cursor mycur is select * from emp; -- list (emppo) empinfo emp%rowtype; begin -- 游标操作使用循环，但是在操作之前必须先将游标打开 open mycur; -- 使游标向下一行 fetch mycur into empinfo; -- 判断此行是否有数据被发现 while (mycur%found) loop dbms_output.put_line('雇员编号：' || empinfo.empno); dbms_output.put_line('雇员姓名：' || empinfo.ename); -- 修改游标，继续向下 fetch mycur into empinfo; end loop; end; --方式二 declare -- 声明游标 cursor mycur is select * from emp; empinfo emp%rowtype; begin -- 游标操作使用循环，但是在操作之前必须先将游标打开 open mycur; loop -- 使游标向下一行 fetch mycur into empinfo; exit when mycur%notfound; dbms_output.put_line('雇员编号：' || empinfo.empno); dbms_output.put_line('雇员姓名：' || empinfo.ename); end loop; end; --判断游标是否打开%isopen使用 if mycur%isopen then null ; else open mycur ; end if ; --行数%rowcount declare -- 声明游标 cursor mycur is select * from emp; empinfo emp%rowtype; cou number; begin -- 游标操作使用循环，但是在操作之前必须先将游标打开 if mycur%isopen then null; else open mycur; end if; loop -- 使游标向下一行 fetch mycur into empinfo; exit when mycur%notfound; cou := mycur%rowcount; dbms_output.put_line(cou || '雇员编号：' || empinfo.empno); dbms_output.put_line(cou || '雇员姓名：' || empinfo.ename); end loop; /* 一次性上涨全部雇员的工资。根据它所在的部门涨工资， 规则： • 10部门上涨10% • 20部门上涨20% • 30部门上涨30% 所有部门的上涨工资，最不能超过5000，如果超过5000，则工资就为5000。 */ declare cursor mycur is select * from emp; empinfo emp%rowtype; s emp.sal%type; begin for empinfo in mycur loop if empinfo.deptno = 10 then s := empinfo.sal * 1.1; elsif empinfo.deptno = 20 then s := empinfo.sal * 1.2; elsif empinfo.deptno = 30 then s := empinfo.sal * 1.3; end if; if s > 5000 then s := 5000; end if; update emp set sal = s where empno = empinfo.empno; end loop; end; --函数即有返回值的过程 /*根据雇员编号查询雇员年薪*/ create or replace function myfun(eno emp.empno%type) return number as s number; begin select (sal + nvl(comm, 0)) * 12 into s from emp where empno = eno; return s; end; --直接sql调用myfun函数 select myfun(7369) from dual; 试比较下面两条SQL语句(emp 表的deptno列上建有ununique index)： 语句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); 语句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); 这两条查询语句实现的结果是相同的，但是执行语句A的时候，ORACLE会对整个emp表进行扫描， 没有使用建立在emp表上的deptno索引，执行语句B的时候， 由于在子查询中使用了联合查询，ORACLE只是对emp表进行的部分数据扫描，并利用了deptno列的索引， 所以语句B的效率要比语句A的效率高。

SQL优化

尽量少用in操作符：根据主子表数据量大小具体考虑使用in还是exits
尽量用not exists或者外连接替代not in操作符：not in不能应用表的索引
尽量不用“<>”或者“!=”操作符：不是说操作符不会用到索引，是由于会产生全表扫描。例如：a<>0改为a>0 or a<0
在设计表的时候，把索引列设置为not null：判断字段是否为空一般不会应用索引，因为B树索引是不索引空值的
尽量不用通配符“%”或者“_”作为查询字符串的第一个字符
where子句中避免在索引列上使用计算：在where子句中对索引列使用函数的时候。索引不再起作用
用“>=”替代“>”
where后面的条件顺序要求：where后面的条件，表连接语句写在最前，可以过滤掉最大数据记录的条件居后
使用表的别名，并将之作为每列的前缀：当sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀，可以减少解析时间
进行显式或隐式的运算的字段不能进行索引
用union all代替union

触发器的主要用途

控制数据安全：例如在非工作时间不能对表进行操作
实现数据统计：例如记载员工的工资变化
实现数据的完整性：如果只是限制员工的工资不能低于800，可以选用check约束
Alter table emp add constraint ck_sal check(sal>=800);
但如果是限定新工资不能低于其原来工资，也不能高于20%。则通过约束是无法实现的。这时可通过触发器来实现
```
Create or replace trigger tr_check_sal
Before update of sal on emp
For each row
When(new.sal<old.sal or new.sal>1.2*old.sal)
Begin
Raise_application_error(-20931,‟ddd‟);
End;
```
实现参照完整性：约束可实现级联删除，不能实现级联更新，可通过触发器实现级联更新
```
Create or replace trigger tr_update_cascade
after update of deptno on dept
for each row
begin
update emp set deptno=:new.deptno where deptno=:old.deptno;
end;
```
declare
v_i number:=0;
v_sum number:=0;
begin
loop
    v_i:=v_i+1;
    v_sum:=v_sum+v_i;
    exit when(v_i=100);
end loop;
dbms_output.put_line(v_sum);
end;
declare
v_i number:=0;
v_sum number:=0;
begin
while(v_i<100)
loop
    v_i:=v_i+1;
    v_sum:=v_sum+v_i;
   end loop;
   dbms_output.put_line(v_sum);
end;
declare
v_i number:=0;
v_sum number:=0;
begin
for v_i in 1..100
loop
v_sum := v_i+v_sum;
end loop;
dbms_output.put_line(v_sum);
end;
declare
v_i number:=0;
begin
for v_i in 1..10
loop
    if(v_i>4) then
     goto endloop;
    elsif(v_i<4) then
     dbms_output.put_line(v_i);
    end if;
end loop;
<<endloop>>
null;
end;