文章标题

pl/sql

pl/sql叫做过程化SQL语言,是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言.

• PL/SQL完整的块结构
  HEADER IS ——\ \ 块头，可选部分，确定这个命名块或者程序的调用方式。
  DECLARE——\ \ 声明单元，可选部分，变量、常量、游标，以及子块。
  ……
BEGIN——\ \ 执行单元，必要部分，SQL语句和PL/SQL语句构成的执行程序
  ……
EXCEPTION —— \ \异常处理单元，可选部分，程序出现异常时，捕捉异常并处理异常
  ……
END；—— \ \结束标志，必须部分

• –常量和变量
  1.–引用变量,引用表中的字段的类型
  declare
pname emp.ename%type;
begin
select t.ename into pname from emp t where t.empno=7369;–使用into来赋值
  dbms_output.put_line(pname);
end;
  2.–记录型变量
  declare
Emprec emp%rowtype;
begin
select t.* into Emprec from emp t where t.empno=7369;
dbms_output.put_line(Emprec.empno || ' '||Emprec.ename);–字符串连接
  end;

• –if分支
  语法1：
  IF 条件 THEN 语句1;
语句2;
END IF;
  语法2：
  IF 条件 THEN 语句序列1；
ELSE 语句序列 2；
END IF；
  语法3：
  IF 条件 THEN 语句;
ELSIF 条件 THEN 语句;
ELSE 语句;
END IF;
  实例1：
  declare
pname number:=#
begin
if pname = 1 then
dbms_output.put_line('我是1');
else
dbms_output.put_line('我不是1');
end if;
end;
  实例2：
  declare
pname number := #
begin
if pname = 1 then
dbms_output.put_line('我是1');
elsif pname = 2 then
dbms_output.put_line('我是2');
else
dbms_output.put_line('我不是12');
end if;
end;
• –loop循环语句
  while(条件)
Loop
EXIT [when 条件];
……
End loop
• 实例1
  declare
pnum number(4):=0;
begin
while pnum < 10 loop
dbms_output.put_line(pnum);
pnum := pnum + 1;
end loop;
end;
• 实例2 (最常用的循环)
  declare
pnum number(4):=0;
begin
loop
exit when pnum=10;
pnum:=pnum+1;
dbms_output.put_line(pnum);
end loop;
end;
• 实例3
  declare
pnum number(4);
begin
for pnum in 1 .. 10 loop
dbms_output.put_line(pnum);
end loop;
end;

---

游标

• 语法：
  CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,…)] IS SELECT 语句;
  例如：cursor c1 is select ename from emp;

declare
cursor c1 is
select * from emp;
emprec emp%rowtype;
begin
open c1;
loop
fetch c1
into emprec;
exit when c1%notfound;
dbms_output.put_line(emprec.empno || ’ ’ || emprec.ename);
end loop;
close c1; –要记得关闭游标
end;

• ———-例外
  –异常，用来增强程序的健壮性和容错性
  – no_data_found (没有找到数据)
  –too_many_rows (select …into语句匹配多个行)
  –zero_divide ( 被零除)
  –value_error (算术或转换错误)
  –timeout_on_resource (在等待资源时发生超时)

• —-写出被0除的例外程序
  declare
  pnum number(4) := 10;
  begin
  pnum := pnum / 0;
  exception
  when zero_divide then
  dbms_output.put_line(‘被0除了’);
  when value_error then
  dbms_output.put_line(‘算术或转换错误’);
  when others then
  dbms_output.put_line(‘其他异常’);
  end;

• —-自定义异常
  –No_data exception;
  –要抛出raise no_data;

declare
cursor c1 is
select * from emp t where t.deptno = 20;
no_data exception;
emprec emp%rowtype;
begin
open c1;
loop
fetch c1
into emprec;
if c1%notfound then
raise no_data;
else
dbms_output.put_line(emprec.empno || ’ ’ || emprec.ename);
end if;
end loop;
close c1;

exception
when no_data then
dbms_output.put_line(‘无员工’);
when others then
dbms_output.put_line(‘其他异常’);
end;

• —-存储过程
  语法：
  create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
  AS
  begin
  PLSQL子程序体；
  End;

或者

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
is
begin
PLSQL子程序体；
End 过程名;

—–创建一个存储过程helloworld
create or replace procedure helloworld is
begin
dbms_output.put_line(‘hello world’);
end helloworld;

——创建一个涨工资的
create or replace procedure addsal(eno in emp.empno%type) is
emprec emp%rowtype;
begin
select * into emprec from emp t where t.empno = eno;

update emp t set t.sal = t.sal + 100 where t.empno = eno;
dbms_output.put_line(‘涨工资前是’ || emprec.sal || ‘,涨工资后是’ ||
(emprec.sal + 100));
end addsal;

---

–java代码调用存储过程和函数

–存储过程

create or replace procedure acc_yealsal(eno in emp.empno%type,yearsal out number) is
pcomm emp.comm%type;
psal emp.sal%type;
begin
select t.sal,t.comm into psal,pcomm from emp t where t.empno=eno;
yearsal :=psal*12 +nvl(pcomm,0);
end;

—-存储函数
create or replace function 函数名(Name in type, Name in type, .. .)
return 数据类型 is
结果变量 数据类型;
begin

return(结果变量);
end函数名;
–存储函数计算年薪
create or replace function accf_yearsal(eno in emp.empno%type)
return number is
Result number;
psal emp.sal%type;
pcomm emp.comm%type;
begin
select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;
Result := psal * 12 + nvl(pcomm, 0);
return(Result);
end accf_yearsal;

---

—触发器
–触发语句：增删改：
语法：
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
begin
PLSQL 块
End 触发器名

—插入一个新员工则触发
create or replace trigger insert_person
after insert on emp
begin
dbms_output.put_line(‘插入新员工’);
end;

select *from emp;
insert into emp values(1001,’李四’,’管理’,7902,sysdate,100,100,20);

–raise_application_error(-20001, ‘不能在非法时间插入员工’)

–==============================================================================
SQL> @ E:\powerDesigner\A_脚本\user.sql –导入脚本文件

select *from H_USER ;

insert into h_user valuer(sequserid.nextval,’a’,’a’,sysdate,’北京’,1);

---

–数据库建模
–一对多：多的一端是2，箭头指向的是表1，即少的一端
–在实体类中一的一端的实体类有多的一端的实体类的集合属性
–使用powerDesiger进行数据库建模，然后将数据导入，导入到plsql中进行使用

——————–连接远程数据库
–方法1，修改localhost的地址
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.lan)
)
)
–方法2
–或者直接在登陆界面在database中输入远程数据库的ip地址和端口号进行远程登陆