java从零开始，学习笔记之基础入门<Oracle_函数_触发器_游标_

---PL/SQL语言部分

--PL.SQL基本格式:

--declare --声明部分--一切变量和常量在此声明

--begin

-- --主体，执行语句

--end;

declare

i number(3);

begin

--给变量赋值

i:=1;

dbms_output.put_line(&＃39;i的值是:&＃39;||i);

end;

--声明常量

declare

i constant varchar2(20):=&＃39;我是摩纳哥鞑子&＃39;;

begin

dbms_output.put_line(i);

end;

select * from scott.emp;

--删除一条记录

declare

eno varchar2(5);

begin

eno:=7369;

delete scott.emp where empno=eno;

end;

--新增一条记录

declare

eno varchar2(5):=110;

ena varchar2(20):=&＃39;周星星&＃39;;

ejob varchar2(30):=&＃39;影帝&＃39;;

mgr number(4):=7369;

hir date:=&＃39;3-1月-2011&＃39;;

sals number(10):=10000;

com number(20):=100;

dep number:=10;

begin

insert into scott.emp values(eno,ena,ejob,mgr,hir,sals,com,dep);

end;

--查询数据

declare

eno number(3);

ena varchar2(10);

begin

eno:=110;

select ename into ena from scott.emp where empno=eno;

dbms_output.put_line(&＃39;ena的值是：&＃39;||ena);

end;

--显示所有的记录

declare

eno varchar2(5):=110;

ena varchar2(20);

ejob varchar2(30);

mgr number(10);

hir date;

sals number(10);

com number(20);

dep number(10);

begin

select empno,ename,job,mgr,hiredate,sal,comm,deptno

into eno,ena,ejob,mgr,hir,sals,com,dep

from scott.emp

where empno=eno;

dbms_output.put_line(eno||&＃39;,&＃39;||ena||&＃39;,&＃39;||ejob||&＃39;,&＃39;||mgr||&＃39;,&＃39;||hir||&＃39;,&＃39;||sals||&＃39;,&＃39;||com||&＃39;,&＃39;||dep);

end;

--使用一行简化我们的查询操作

--%rowtype,返回行的数据类型

declare

emps scott.emp% rowtype;

begin

select * into emps from scott.emp where empno=110;

dbms_output.put_line(emps.ename||&＃39;,&＃39;||emps.job);

end;

--%type

declare

enames scott.emp.ename% type;

begin

select ename into enames from scott.emp where empno=110;

dbms_output.put_line(enames);

end;

--条件控制语言

--if--then--else

--if--then--else if--else if --else--

--有多少个if就给多少end if 结束

declare

i number:=2;

begin

if(i>4)

then

dbms_output.put_line(&＃39;逻辑正确&＃39;);

else

dbms_output.put_line(&＃39;逻辑不正确&＃39;);

endif;

end;

--查询scott.emp 表中的数据，如果工资低于3000就加2000

--如果低于4000就只加500

--如果高于5000就扣除200

declare

emp number(5):=110;

esal number(10);

begin

select sal into esal from scott.emp where empno=emp;

if(esal<3000) then

update scott.emp set sal=sal+2000;

else if(esal>5000) then

update scott.emp set sal=sal-200;

end if;

end if;

select sal into esal from scott.emp where empno=emp;

dbms_output.put_line(esal);

end;

--case

declare

i number(2):=1;

begin

case i

when1 then

dbms_output.put_line(&＃39;i的值是1&＃39;);

when 2 then

dbms_output.put_line(&＃39;i的值是2&＃39;);

when 3 then

dbms_output.put_line(&＃39;i的值是3&＃39;);

else

dbms_output.put_line(&＃39;没有匹配的值&＃39;);

end case;

end;

--循环语句

--loop,for.while

--简单的loop循环

declare

i number(2):=1;

begin

loop

if(i>10) then

exit; --终止程序

end if;

dbms_output.put_line(i);

i:=i+1;

end loop;

end;

--for循环

declare

j number(2):=10;

begin

for i in1..j loop --for循环，不需要声明此处的变量i,范围采用".."

dbms_output.put_line(i);

end loop;

end;

--while 循环

declare

k number(2):=1;

begin

while (k<=10) loop

dbms_output.put_line(k);

k:=k+1;

end loop;

end;

--异常的处理

--预定义异常

--用户自定义异常

declare

invalied EXCEPTION;

categroy varchar2(10);

begin

categroy :=&＃39;tt&＃39;;

if categroy not in(&＃39;沈水&＃39;,&＃39;林下&＃39;,&＃39;石小孟&＃39;) then

raise invalied;

else

dbms_output.put_line(&＃39;你是：&＃39;||categroy);

end if;

exception

when invalied then

dbms_output.put_line(&＃39;你输入的不匹配&＃39;);

end;

declare

j number(2):=10;

begin

for i in1..j loop --for循环，不需要声明此处的变量i,范围采用".."

dbms_output.put_line(i);

end loop;

end;

/*

*

**

***

****

*****

*/

declare

begin

for i in0..5 loop

for j in0..i loop

dbms_output.put(&＃39;*&＃39;);

end loop;

dbms_output.new_line;

end loop;

end;

select * from scott.emp

--存储过程

create or replace procedure test_pro

as

begin

declare

i number(3):=1;

begin

dbms_output.put_line(i);

end;

end;

execute test_pro;--在sqlplus里执行

--在sql里执行存储过程

begin

test_pro;

end;

--带参数的存储过程

create or replace procedure get_par(i innumber)

as

begin

dbms_output.put_line(i);

end;

begin

get_par(2012);

end;

--根据学生id查询某学生名

create or replace procedure get_stu(stu_id innumber)

is

stuname varchar2(28);

begin

select s_name into stuname from student where s_id =stu_id;

dbms_output.put_line(stuname);

exception

when no_data_found then

dbms_output.put_line(&＃39;查无此人&＃39;);

end;

begin

get_stu(1);

end;

--带有输入输出参数的存储过程

--根据学生id来查询学生姓名

create or replace procedure get_stu(stu_id in number,stuname in out varchar2)

as

begin

select username into stuname from user_tb where userid =stu_id;

dbms_output.put_line(stuname);

exception

when no_data_found then

dbms_output.put_line(&＃39;查无此人&＃39;);

end;

declare

stuname varchar2(30);

begin

get_stu(1,stuname);

end;

*******************************************************

--模拟登录与注册

create table usertb(

userid number primary key,

username varchar2(30),

userpwd varchar2(30)

)

create or replace procedure login_pros(uname in out varchar2,pwd in out varchar2,islogin in out boolean)

as

begin

select username,userpwd into uname,pwd from usertb where username=uname and userpwd=pwd;

islogin:=true;

exception

when no_data_found then

dbms_output.put_line(&＃39;用户没有注册&＃39;);

islogin:=false;

end;

--执行登录的存储过程

declare

uname varchar2(30):=&＃39;李冰冰&＃39;;

pwd varchar2(30):=&＃39;abc&＃39;;

islogin boolean;

begin

login_pros(uname,pwd,islogin);

if(islogin) then

dbms_output.put_line(&＃39;登录成功,&＃39;||&＃39;登录的用户是&＃39;||uname);

else

dbms_output.put_line(&＃39;登录失败&＃39;||&＃39;请重新注册&＃39;);

end if;

end;

--注册

create or replace procedure regist_pros(uname in out varchar2,pwd in out varchar2,userid in number,isregist out boolean)

as

begin

insert into usertb values(userid,uname,pwd);

isregist:=true;

dbms_output.put_line(&＃39;注册成功&＃39;||&＃39;注册用户是:&＃39;||uname);

exception

when no_data_found then

dbms_output.put_line(&＃39;您输入的用户信息是否正确&＃39;);

isregist:=false;

end;

--注册的存储过程的调用

declare

uname varchar2(30):=&＃39;李冰冰&＃39;;

pwd varchar2(30):=&＃39;abc&＃39;;

userid number(10):=3;

isregist boolean;

begin

regist_pros(uname,pwd,userid,isregist);

if(isregist) then

dbms_output.put_line(&＃39;注册的用户是：&＃39;||uname);

else

dbms_output.put_line(&＃39;是否重新注册&＃39;);

end if;

end;

--视图

create view emp_view

as

select * from usertb;

--删除视图中的字段，会影响到住表中的数据

delete from emp_view where userid=2;

--对视图进行更新

update emp_view set username=&＃39;李斯&＃39; where userid=2;

--触发器

--触发器对select不起作用

create or replace trigger delete_tir after delete on scott.emp

begin

dbms_output.put_line(&＃39;删除一条语句&＃39;);

end;

alter trigger delete_tir disable;

delete from scott.emp where empno=110;

create or replace trigger update_tri before update on scott.emp

begin

dbms_output.put_line(&＃39;更新一条语句&＃39;);

end;

update scott.emp set sal=9000 where empno=7499;

--对scott.emp表进行插入数据的时候，也同时将此数据插入到

create table emptest(

eno number(20) primary key,

enames varchar2(30),

jobs varchar2(30),

mgrs number(10),

hiretime date,

sals number(10),

comms number(10),

dept_no number(10)

)

--在删除之前执行，在删除之前打印即将删除学生的信息

--删除之前执行用":old"，更新之前执行用":new";

create or replace trigger stu_delete_prinStu

before deleteon tb_stu for each row

begin

dbms_output.put_line(&＃39;即将删除的学生学号是：&＃39;||:old.stu_no);

dbms_output.put_line(&＃39;即将删除的学生姓名是：&＃39;||:old.stu_name);

end;

delete from tb_stu where stu_no=6;

create or replace trigger new_tri before insert on scott.emp

for each row

begin

insert into emptest values(:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno);

end;

insert into scott.emp values(11,&＃39;OFFICELADY&＃39;,&＃39;SALE&＃39;,7698,&＃39;1-5月-2011&＃39;,29000,3000,20);

--对表进行删除操作后的记录

create or replace trigger old_tri after delete on scott.emp

for each row

begin

dbms_output.put_line(&＃39;删除的用户是：&＃39;||:old.ename);

end;

delete from scott.emp where empno=11;

--before和after

--行级触发器，语句级触发器 (for each row)

--行级触发器对DML语句影响每一行的操作，例如update语句，有多少条语句，触发器就会被执行多少次

--语句级触发器对我们的DML语句只执行一次操作，例如insert语句，即使有多条，触发器只被执行一次

--before表示在语句执行之前出发

--after表示在语句执行之后进行出发

--实际看到的效果没有什么区别

--禁用触发器，启用触发器

alter trigger new_tri disable;

alter trigger old_tri enable;

--禁用一个表中所有触发器

alter table tb_stu disable all triggers;

--删除触发器

drop trigger new_tri;

select * from scott.emp;

select * from lu.emptest

--定义一个函数实现加法运算

create or replace function myAdd(num1 number,num2 number)

return number--在规则说明中需要return

--只能够返回一个值（和声明的返回类型匹配）

--需要return关键字来返回值

--函数不能单独的被调用，只能作为sql代码的一部分来执行

as

num3 number;

begin

num3:=num1+num2;

return num3;

end;

--调用函数：函数不能单独的被调用，需要作为sql一部分来调用

declare

n number;

begin

n:=myAdd(12,34);

dbms_output.put_line(n);

end;

--定义一个函数，查询emp 将所有的信息打印到output

--并且计算出所有员工的工资总和，返回工资总和

--函数和存储过程一样，在定义的时候如果没有参数就不需要"()"

create or replace function getMsg

return number

as

cursor emp_msg is select * from scott.emp;

totalSal number;

begin

for e in emp_msg

loop

dbms_output.put_line(e.ename||&＃39;-&＃39;||e.sal);

end loop;

select sum(sal) into totalSal from scott.emp;

return totalSal;

end;

--调用函数

select getMsg() from dual;

--定义一个包的说明部分 （类似接口）

create or replace package my_package is

procedure myProc;

function myAdd(m number,n number)return number;

end my_package;

--定义一个人包的主题部分（类似程序体DAO）

create or replace package body my_package is

procedure myProc

is

cursor emp_msg isselect * from scott.emp;

begin

for e in emp_msg

loop

dbms_output.put_line(e.ename);

endloop;

end;

function myAdd(m number,n number)

return number

as

num number;

begin

num:=n+m;

return num;

end;

end my_package;

call my_package.myProc();

select my_package.myAdd(1,2) 结果 from dual;