最近刚学oracle,才知道到有个PL/SQL,运行中这个语句运行的速度是最快的,所以肯定要掌握这个!
建表语句:
create table AB(
id number not null,
username varchar2(64),
password varchar2(64),
primary key(id)
)
pl/sql语句
简单的创建对象:
declare
pnumber number(7,2);
pname varchar2(20);
pdate date;
begin
pnumber:=1;
dbms_output.put_line(pnumber);
pname:='tom';
dbms_output.put_line(pname);
pdate:=sysdate;
dbms_output.put_line(pdate);
end;
/
引用sql语句,引用表中对象的属性:
declare
pusername AB.username%type;--表名.属性名
ppassword AB.password%type;
begin
--oracle字符串全是单引号!!!和mysql不一样,mysql是双引号!而且oracla没有关键字的顾及,随便用!
select password,username into ppassword,pusername from AB where username='111';
dbms_output.put_line(ppassword||'的对象是'||pusername);
end;
/
查找一列中的属性:
declare
AB_rec AB%rowtype;
begin
select * into AB_rec from AB where username='111';
dbms_output.put_line(AB_rec.username||'的密码的'||AB_rec.password);
end;
if判断语句
accept num1 prompt'请输入一个数字';--在我的PL/SQL中这句话不能执行
declare
pnum number:=&num1;
begin
if pnum=0 then dbms_output.put_line('您输入的是0');
elsif pnum=1 then dbms_output.put_line('您输入的是1');
elsif pnum=2 then dbms_output.put_line('您输入的是2');
else dbms_output.put_line('其他数字');
end if;
end;
/
--循环语句 while循环
declare
pnum number:=1 ;
begin
while pnum<=10 loop
dbms_output.put_line(pnum);
pnum:=pnum+1;
end loop;
end;
/
---循环语句 loop 循环
declare
pnum number:=1;
begin
loop
exit when pnum>10;
dbms_output.put_line(pnum);
pnum:=pnum+1;
end loop;
end;
/
--for循环
declare
pnum number:=1;
begin
for pnum in 1..10 loop
dbms_output.put_line(pnum);
end loop;
end;
/
光标的使用
declare
cursor cemp is select username,password from AB;
pusername AB.USERNAME%type;
ppassword AB.password%type;
begin
open cemp;
loop
fetch cemp into ppassword,pusername;
exit when cemp%notfound;
dbms_output.put_line(pusername||'的密码是'||ppassword);
end loop;
close cemp;
end;
/
光标使用修改,记得提交事务
declare
cursor cemp is select username,password from AB;
ppassword AB.password%type;
pusername AB.USERNAME%type;
begin
rollback;
open cemp;
loop
fetch cemp into pusername,ppassword;
exit when cemp%notfound;
if pusername='111'then update AB set password='1111' where username=pusername;
elsif pusername='112'then update AB set password='1112' where password=ppassword;
else update AB set password='110' where password=ppassword;
end if;
end loop;
close cemp;
dbms_output.put_line('更改成功');
commit;
end;
/
光标的属性:
%found %notfound %rowcount:影响的行数
oracle默认光标数为300,如果要修改,必须先用管理员身份登录,然后运行下面这段话
alter system set open_cursors=400 scope=both;
scope的取值:both(俩个都改),memory(只更改当前实例,不更改参数文件) ,spfile(不更改当前实例,只更改参数
文件,数据库需要重启);
--(带有类型参数的光标)密码叫111的帐号有。。。。
declare
cursor cemp( password varchar2) is select *from AB;
pusername AB.USERNAME%type;
begin
open cemp('110');
loop
fetch cemp into pusername;
exit when cemp%notfound;
dbms_output.put_line('密码为111的帐号有'||pusername);
end loop;
close cemp;
end;
--no_data_found 例外
declare
pusername AB.USERNAME%type;
begin
select username into pusername from AB where password='110';
exception
when no_data_found then dbms_output.put_line('没有找到数据');
when others then dbms_output.put_line('其他例外');
end;
--too_many_rows例外 行数超了
declare
pusername AB.Username%type;
begin
select username into pusername from AB where password='11111';
exception
when too_many_rows then dbms_output.put_line('你傻乎乎的把一个数组数据给一个基本类型的数据');
when others then dbms_output.put_line('其他例外');
end;
--zero_divide 0不能当除数
declare
pnum number;
begin
pnum:=1/0;
exception
when zero_divide then dbms_output.put_line('0不能当除数');
when others then dbms_output.put_line('其他例外');
end;
--value_error值类型赋值错误!!
declare
pnum number;
begin
pnum:='asdsdsd';
exception
when value_error then dbms_output.put_line('类型赋值出错啦');
when others then dbms_output.put_line('其他例外');
end;
--自定义例外
declare
cursor cemp is select username from AB where password='111111122';
pusername AB.username%type;
no_AB_found exception;
begin
open cemp;
fetch cemp into pusername;
if cemp %notfound them
raise no_AB_found;
end if;
close cemp;
exception
when no_AB_found then dbms_output.put_line('没有找到');
when others then dbms_output.put_line('其他例外');
end;