---------------------PL/SQL中的重点cursor(游标)和指针的概念差不多----------------------
declare
cursorc is
select * from emp; //此处的语句不会立刻执行,而是当下面的open c的时候,才会真正去数据库中取数据
v_empc%rowtype;
begin
openc;
fetchc into v_emp;
dbms_output.put_line(v_emp.ename); //这样会只输出一条数据 134将使用循环的方法输出每一条记录
close c;
end;
----------------------使用do while 循环遍历游标中的每一个数据---------------------
declare
cursorc is
select* from emp;
v_empc%rowtype;
begin
openc;
loop
fetchc into v_emp;
(1) exit when(c%notfound); //notfound是oracle中的关键字,作用是判断是否还有下一条数据
(2) dbms_output.put_line(v_emp.ename); //(1)(2)的顺序不能颠倒,否则会把最后一条结果再多打印一次。
end loop;
close c;
end;
------------------------使用while循环,遍历游标---------------------
declare
cursorc is
select* from emp;
v_empemp%rowtype;
begin
openc;
fetchc into v_emp;
while(c%found)loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;
--------------------------使用for 循环,遍历游标(最方便快捷的方法!)---------------------
declare
cursorc is
select * from emp;
begin
forv_emp in c loop
dbms_output.put_line(v_emp.ename);
endloop;
end;
---------------------------带参数的游标(相当于函数)---------------------
declare
cursorc(v_deptno emp.deptno%type, v_job emp.job%type)
is
select ename, sal from emp wheredeptno=v_deptno and job=v_job;
begin
forv_temp in c(30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
endloop;
end;
-------------------------可更新的游标-----------------------------
declare
cursorc
is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.sal< 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_temp.sal =5000) then
deletefrom emp2 where current of c;
end if;
end loop;
commit;
end;
49、------------------------------store procedure存储过程(带有名字的程序块) -------------------
createor replace procedure p
is--除了这两句替代declare,下面的语句全部都一样
cursor c is
select* from emp2 for update;
begin
forv_emp in c loop
if(v_emp.deptno= 10) then
updateemp2 set sal = sal +10 where current of c;
elseif(v_emp.deptno =20) then
updateemp2 set sal = sal + 20 where current ofc;
else
updateemp2 set sal = sal + 50 where current of c;
endif;
end loop;
commit;
end;
执行存储过程的两种方法:
(1)exec p;(p是存储过程的名称)
(2)begin
p;
end;
/
-------------------------------带参数的存储过程
先创建存储过程:(in标识传入参数,out标识传出参数,默认为传入参数)
createor replace procedure p
(v_ain number, v_b number, v_ret outnumber, v_temp inout number)
is
begin
if(v_a> v_b) then
v_ret:= v_a;
else
v_ret:= v_b;
endif;
v_temp:= v_temp + 1;
end;
再调用:
declare
v_a number := 3;
v_b number := 4;
v_retnumber;
v_tempnumber := 5;
begin
p(v_a,v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
------------------删除存储过程---------------------------
dropprocedure p;
50、------------------------创建函数计算个人所得税的税率-------------------------------------
createor replace function sal_tax
(v_sal number)
returnnumber
is
begin
if(v_sal< 2000) then
return0.10;
elsif(v_sal<2750) then
return0.15;
else
return0.20;
endif;
end;
-----------------------------创建触发器(trigger) 触发器不能单独的存在,必须依附在某一张表上
写主语 谓语 宾语 游戏
创建触发器的依附表:
createtable emp2_log
(
enamevarchar2(30) ,
eactionvarchar2(20),
etimedate
);
create or replace trigger trig
after insert or delete orupdate on emp2 for each row --加上此句,每更新一行,触发一次,不加入则值触发一次
begin
ifinserting then
insertinto emp2_log values(USER, 'insert', sysdate);
elsifupdating then
insertinto emp2_log values(USER, 'update', sysdate);
elsifdeleting then
insertinto emp2_log values(USER, 'delete', sysdate);
endif;
end;
51、--------------------触发器用法之一:通过触发器更新约束的相关数据-------------------
createor replace trigger trig
afterupdate on dept
foreach row
begin
updateemp set deptno =:NEW.deptno where deptno =: OLD.deptno;
end;
//只编译不显示的解决办法 set serveroutput on;
52、-------------------------------通过创建存储过程完成递归
createor replace procedure p(v_pid article.pid%type,v_level binary_integer) is
cursorc is select * from article where pid = v_pid;
v_preStrvarchar2(1024) := '';
begin
for i in 0..v_leave loop
v_preStr:= v_preStr || '****';
end loop;
for v_article in c loop
dbms_output.put_line(v_article.cont);
if(v_article.isleaf= 0) then
p(v_article.id);
endif;
endloop;
end;
-------------------------------查看当前用户下有哪些表---
首先,用这个用户登录然后使用语句:
select* from tab;
-----------------------------用Oracle进行分页!--------------
因为Oracle中的隐含字段rownum不支持'>'所以:
select* from (
selectrownum rn, t.* from (
select* from t_user where user_id <> 'root'
)t where rownum <6
)where rn >3
------------------------Oracle下面的清屏命令----------------
clearscreen; 或者 cle scr;
-----------将创建好的guohailong的这个用户的密码改为abc--------------
alter user guohailong identified by abc
当密码使用的是数字的时候可能会不行