--plsql基本结构
declare
v_Sex number;
v_AuthorCode char(6);
v_Arcount number;
begin
select author_code,sex
into v_AuthorCode,v_Sex from auths where name='阿牛';
if v_Sex=0 then
select count(article_code) into v_Arcount from article
where author_code='A0001';
end if;
end;
--
--记录类型
declare
type A_Record is record( Auhoor_code char(6),username varchar2(10),sex number(1));
v_Author A_Record;
--
--对象类型
create or replace type AuthorObj as object(
Author_code char(6),
Name varchar2(10),
Sex number(1),
birthdate date,
address varchar2(30)
);
---
--常量的定义
declare
sex_male constant int :=1
sex_female constant int :=0;
----
----循环结构
declare
v_loopCounter binary_integer:=1;
begin
loop
insert into table_a(num_col) values(v_loopCounter);
v_loopCounter:=v_loopCounter+1;
exit when v_loopCounter>100;
end loop;
end;
----
---for循环
declare
v_loopCounter binary_integer:=1;
begin v_loopCounter in 1..100
loop
insert into table_a(num_col) values(v_loopCounter);
end loop;
end;
---------------
---------游标
--游标用来查询数据库中的数据(例如select 语句返回的记录),并对 查询的结果进行处理。游标分为显式游标
--和隐式游标两种。通过游标,可以对 查询结果中的数据一条一条进行处理。
declare
v_name varchar2(10);
v_salary number(8,2);
--游标的定义。
cursor c_auths is
select name ,salary from auths;
begin
open c_auths;
loop
--检索一条记录
fetch c_auths into v_name ,v_salary;
--当前所有的记录都被检索出后退循环。
exit when c_auths%notfound;
--对检索的数据进行处理
end loop;
close c_auths;
end;
---------------
---------------无名块实例
set serveroutput on size 1000--设置存储缓冲区大小
declare
v_TypeCode1 varchar2(6):='cc';
v_TypeCode2 varchar2(6):='dd';
v_TypeRemark1 varchar2(15):='Computer';
v_typeRemark2 varchar2(15):='C++Language';
v_OutPut varchar2(15);
begin
insert into type
values(v_TypeCode1,v_TypeRemark1);
values(v_TypeCode2,v_TypeRemark2);
select type_remark
into v_OutPut from type
where type_code=v_TypeCode1;
Dbms_Output.put_line(v_OutPut);
select type_remark
into v_OutPut from type
where type_code=v_TypeCode2;
Dbms_Output.put_line(v_OutPut);
end inserttype;
-----------------------------------
---------------------命名块实例
<<inserttype>>
declare
v_TypeCode1 varchar2(6):='cc';
v_TypeCode2 varchar2(6):='dd';
v_TypeRemark1 varchar2(15):='Computer';
v_typeRemark2 varchar2(15):='C++Language';
v_OutPut varchar2(15);
begin
insert into type
values(v_TypeCode1,v_TypeRemark1);
values(v_TypeCode2,v_TypeRemark2);
select type_remark
into v_OutPut from type
where type_code=v_TypeCode1;
Dbms_Output.put_line(v_OutPut);
select type_remark
into v_OutPut from type
where type_code=v_TypeCode2;
Dbms_Output.put_line(v_OutPut);
end inserttype;--或者是 end <<inserttype>>
---------------------------------
-------定义子程序
--------创建存储过程inserttype
create or replace procedure inserttype as
-------声明在块中要用到的变量
v_TypeCode1 varchar2(6):='cc';
v_TypeCode2 varchar2(6):='dd';
v_TypeRemark1 varchar2(15):='Computer';
v_typeRemark2 varchar2(15):='C++Language';
v_OutPut varchar2(15);
begin
insert into type
values(v_TypeCode1,v_TypeRemark1);
values(v_TypeCode2,v_TypeRemark2);
select type_remark
into v_OutPut from type
where type_code=v_TypeCode1;
Dbms_Output.put_line(v_OutPut);
select type_remark into v_OutPut from type
where type_code=v_TypeCode2;
Dbms_Output.put_line(v_OutPut);
end inserttype;
-----将上例中的关键字declare 改变成关键字create or replace procedure 时候,无名块就变成了存储过程。注意,在end 关键字后必须加上存储过程名。
-------------下面是一个触发器的例子
--创建触发器salary_trigger
create or replace trigger salary_trigger
before insert or update of salary on suths for each row
begin
--如果插入值或者修改值大于10000时候,报告应插入的工资值,并产生异常,退出该触发器
if :new.salary>10000 then
raise_application_error(-200060,'插入的工资应小于10000!');
end if;
end salary_trigger;
--该触发器被创建在auths表上,该触发器确保只有小于10000的值才能插入到salary列中,当向表中插入或者修改一条记录时,触发该触发器。