Oracle_函数_触发器_游标_存储过程_视图 ---PL/SQL语言部分 --PL.SQL基本格式: --declare --声明部分--一切变量和常量在此声明 --begin -- --主体，执行语句 --end; declare i number(3); begin --给变量赋值 i:=1; dbms_output.put_line(i的值是:||i); end;
Oracle_函数_触发器_游标_存储过程_视图
---PL/SQL语言部分
--PL.SQL基本格式:
--declare --声明部分--一切变量和常量在此声明
--begin
-- --主体，执行语句
--end;
declare
i number(3);
begin
--给变量赋值
i:=1;
dbms_output.put_line('i的值是:'||i);
end;
--声明常量
declare
i constant varchar2(20):='我是摩纳哥鞑子';
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):='周星星';
ejob varchar2(30):='影帝';
mgr number(4):=7369;
hir date:='3-1月-2011';
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('ena的值是：'||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||','||ena||','||ejob||','||mgr||','||hir||','||sals||','||com||','||dep);
end;
--使用一行简化我们的查询操作
--%rowtype,返回行的数据类型
declare
emps scott.emp% rowtype;
begin
select * into emps from scott.emp where empno=110;
dbms_output.put_line(emps.ename||','||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('逻辑正确');
else
dbms_output.put_line('逻辑不正确');
endif;
end;
declare
emp number(5):=110;
esal number(10);
begin
select sal into esal from scott.emp where empno=emp;
if(esal
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('i的值是1');
when 2 then
dbms_output.put_line('i的值是2');
when 3 then
dbms_output.put_line('i的值是3');
else
dbms_output.put_line('没有匹配的值');
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
dbms_output.put_line(k);
k:=k+1;
end loop;
end;
--异常的处理
--预定义异常
--用户自定义异常
declare
invalied EXCEPTION;
categroy varchar2(10);
begin
categroy :='tt';
if categroy not in('沈水','林下','石小孟') then
raise invalied;
else
dbms_output.put_line('你是：'||categroy);
end if;
exception
when invalied then
dbms_output.put_line('你输入的不匹配');
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('*');
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('查无此人');
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('查无此人');
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('用户没有注册');
islogin:=false;
end;
--执行登录的存储过程
declare
uname varchar2(30):='李冰冰';
pwd varchar2(30):='abc';
islogin boolean;
begin
login_pros(uname,pwd,islogin);
if(islogin) then
dbms_output.put_line('登录成功,'||'登录的用户是'||uname);
else
dbms_output.put_line('登录失败'||'请重新注册');
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('注册成功'||'注册用户是:'||uname);
exception
when no_data_found then
dbms_output.put_line('您输入的用户信息是否正确');
isregist:=false;
end;
--注册的存储过程的调用
declare
uname varchar2(30):='李冰冰';
pwd varchar2(30):='abc';
userid number(10):=3;
isregist boolean;
begin
regist_pros(uname,pwd,userid,isregist);
if(isregist) then
dbms_output.put_line('注册的用户是：'||uname);
else
dbms_output.put_line('是否重新注册');
end if;
end;
视图
--视图
create view emp_view
as
select * from usertb;
--删除视图中的字段，会影响到住表中的数据
delete from emp_view where userid=2;
--对视图进行更新
update emp_view set username='李斯' where userid=2;
触发器
--触发器
--触发器对select不起作用
create or replace trigger delete_tir after delete on scott.emp
begin
dbms_output.put_line('删除一条语句');
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('更新一条语句');
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('即将删除的学生学号是：'||:old.stu_no);
dbms_output.put_line('即将删除的学生姓名是：'||: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,'OFFICELADY','SALE',7698,'1-5月-2011',29000,3000,20);
--对表进行删除操作后的记录
create or replace trigger old_tri after delete on scott.emp
for each row
begin
dbms_output.put_line('删除的用户是：'||: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||'-'||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;
查看更多关于java从零开始，学习笔记之基础入门<Oracle_函数_触发器_游标_的详细内容...