PLSQL代码

----内容1：PL/SQL块的使用---------------------------------------
--案例01：简单的变量使用
declare
eno varchar(10);
ename varchar(20);
begin
eno:='9527';
ename:='wind';
dbms_output.put_line('eno:' ||eno ||'ename:' ||ename);
end;

--案例02：定义并常规赋值变量
declare
eno varchar(20);
ename varchar(20);
 sid constant varchar(10):='110' ;
v_valid boolean  default false;
begin
eno:='9527';
ename:='wind';
dbms_output.put_line('eno:' ||eno ||', ename:' ||ename ||' ,sid'||sid);
end;

--案例03：通过查询表赋值变量
declare
eno varchar(20);
ename varchar(20);
 sid constant varchar(10):='110' ;
v_valid boolean  default false;
begin
select empno, ename into eno, ename from emp where empno=7788;  /*一定使用where指明条件*/
dbms_output.put_line('eno:' ||eno ||', ename:' ||ename ||' ,sid'||sid);
end;

 

--案例04：PL/SQL块

--基础01：%rowtype
declare
name emp%rowtype;  /*表示变量name和emp表的一行的所有列属性相同*/
begin
select * into name from emp where empno=7788;  /*select后只能使用*,把该行的所有列赋值给变量*/
dbms_output.put_line('name'||name.ename);  /*name.ename即：变量中的列名ename*/
end;

--基础02：%type
declare
nm emp.ename%type;   --表示与表emp表中的ename列属性相同
no emp.empno%type; 
begin
select ename, empno into nm, no from emp where empno=7788; /*select后使用部分字段列赋值给变量*/
dbms_output.put_line('name'||nm); 
end;

--完整案例：

declare
eno emp.empno%type;
name emp.ename%type;
emprow emp%rowtype;  --与emp表中的所有行属性相同
begin
 select empno, ename into eno, name from emp where empno=7369;
select * into emprow from emp  where empno=7788; /*将一行数据的所有列赋值给变量*/
dbms_output.put_line('tmpno:'||eno||'  '||'cname:' ||name);
dbms_output.put_line('empno:'||emprow.empno||'  '||'ename:' ||emprow.ename);
exception
 when no_data_found then
 dbms_output.put_line('没有数据');
end;

 

--案例05：日期转换
declare 
thisday date;
begin
 thisday:=sysdate;
 dbms_output.put_line('this is day:' ||to_char(thisday,'yyyy-mm-dd'));
exception
 when no_data_found then
 dbms_output.put_line('没有数据');
end;

--案例06：比较日期
declare 
thisday date;
begin
 thisday:=sysdate;
if thisday >'20-3月-2004' then --必须按照这样的日期格式。或案例07.
 dbms_output.put_line('this is day:' ||to_char(thisday,'yyyy-mm-dd'));
 else
 dbms_output.put_line('this is  no day');
 end if;
exception
 when no_data_found then
 dbms_output.put_line('没有数据');
end;

--案例07：
declare 
thisday date;
begin
 thisday:=sysdate;
if thisday <to_date('2006-03-12', 'yyyy-mm-dd' )then  /*通过格式转换，最好按照这样的转换*/
 dbms_output.put_line('this is day:' ||to_char(thisday,'yyyy-mm-dd'));
 else
 dbms_output.put_line('this is  no day');
 end if;
exception
 when no_data_found then
 dbms_output.put_line('没有数据');
end;

 

-----------内容2：数据类型的使用案例---------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
-----------1.标量数据类型案例
--案例02：使用标量变量
declare
   v_name varchar2(5);
   v_sal number(6,2);
   c_tax_rate constant number(3,2):=0.03;
   v_tax_sal number(6,2);
begin
select ename, sal into v_name, v_sal
from emp where empno=&eno;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line ('雇员姓名:'||v_name);
dbms_output.put_line ('雇员工资:'||v_sal);
dbms_output.put_line ('所得税:'||v_tax_sal);
end;

--案例03：使用%type属性
declare
v_name emp.ename%type;
v_sal emp.sal%type;
c_tax_rate constant number(3,2):=0.03;
v_tax_sal v_sal%type;
begin
select ename, sal into v_name, v_sal
from emp where empno=&eno;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line ('雇员姓名:'||v_name);
dbms_output.put_line ('雇员工资:'||v_sal);
dbms_output.put_line ('所得税:'||v_tax_sal);
end;

 

------------2.符合变量（record、varry、nested table三种类型）--------
--案例01：PL/SQL记录(即record)
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
emp_record  emp_record_type;
begin
select ename, sal, job into emp_record
 from emp where empno=&eno;
dbms_output.put_line ('姓名:'||emp_record.name);
dbms_output.put_line ('工资:'||emp_record.salary);
dbms_output.put_line ('岗位:'||emp_record.title);
end;

--案例02：PL/SQL表
declare
type ename_table_type is table of emp.ename%type
index by binary_integer;
ename_table ename_table_type;
begin
select ename into ename_table(-1) from emp
where empno=&no;
dbms_output.put_line('雇员名'||ename_table(-1));
end;

--案例03：嵌套表(nested table)
create or replace type emp_type as object(
name varchar2(10),
salary number(6,2),
hiredate date);

create or replace type emp_arry is table of emp_type;

create table department(
deptno number(2),
dname varchar2(10),
employee emp_arry)
nested table employee store as employee;

--案例04：变长数组类型varray

create type article_type as object(
title varchar2(30),
pubdate date
);

create type article_array is varray(20) of article_type;

create table author
(id number(6),
name varchar2(10),
article article_array
);

-------------------3.参照变量(rf)------------------------------------
--案例01：使用REF CURSOR
declare
type c1 is ref cursor;  --申明游标类型
dyn_cursor c1;  --游标变量
col1 varchar(20);
col2 varchar(20);
begin
open dyn_cursor for select &col1, &col2 from &tab where &con;
fetch  dyn_cursor into col1,col2;
dbms_output.put_line('col1: '||col1);
dbms_output.put_line('col2: '||col2);
close dyn_cursor;
end;

--------------------------------内容3：编写控制结构语句--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

--1.简单条件分支语句
declare
v_sal number(6,2);
begin
select sal into v_sal from emp
where lower(ename)=lower('&&name');
if v_sal <2000 then
update emp set sal=v_sal+200
where lower(ename)=lower('&name');
end if;
dbms_output.put_line('你输入的员工存在，工资已经调整，请验证！');
end;

 

--2.二重分支语句

declare
v_comm number(6,2);
begin
select comm into v_comm from emp
where empno=&&no;
if v_comm <>0 then
update emp set comm=v_comm+100
where empno=&&no;
else
update emp set comm=200 where empno=&&no;
end if;
dbms_output.put_line('数据处理完毕，请验收');
end;

--3.elsif测试

declare
sal number:=500;
comm number;
begin
 if sal<100 then
 comm:=0;
 elsif sal<600 then
 comm:=sal*0.1;
 elsif sal<1000 then
 comm:=sal*0.15;
 else
 comm:=sal*0.2;
 end if;
 dbms_output.put_line(comm);
end;

--4.case测试

declare
v_sal number:=1000;
v_tax number;
begin
case
when v_sal<1500 then
v_tax:=v_sal*0.03;
when v_sal<2500 then
v_tax:=v_sal*0.04;
when v_sal<3500 then
v_tax:=v_sal*0.05;
when v_sal <8000 then
v_tax:=v_sal*0.04;
end case;
dbms_output.put_line(v_tax);
end;

--5.loop循环
/*案例01：将1到10插入到表temp表*/

create table temp(cola int);
declare
i int:=1;
begin
  loop
  insert into temp values (i);
  exit when i=10;
   i:=i+1;
end loop;
end;

/*案例02：计算1+2+3+...10的结果集*/
--方法1：先求和再加1（推荐）
declare
  res int:=0;
  i int:=0;
begin
  loop
  res:=res+i;
  exit when i=10;
  i:=i+1;
end loop;
dbms_output.put_line('结果是：'|| res);
end;

--或则--

declare
res int:=0;
i int:=1;
begin
loop
res:=res+i;
exit when i=10;
i:=i+1;
end loop;
dbms_output.put_line('结果是：'|| res);
end;

 

--方法2：先加1再求和
declare
res int:=0;
i int:=0;
begin
loop
i:=i+1;
exit when i>10;
res:=res+i;
end loop;
dbms_output.put_line('结果是：'|| res);
end;

--6.while循环
/*案例01：计算1+2+3+...10的结果集*/

--方法1：（推荐）先求和再加1
declare
res int:=0;
i int:=0;
begin
while i<11 loop  --如果小于10则只是循环9次
res:=res+i;  --第一次0+0就相当于没有相加
i:=i+1;
end loop;
dbms_output.put_line('结果是：'|| res);
end;

 

--方法2：先加1再求和
declare
res int:=0;
i int:=0;
begin
while i<10 loop
i:=i+1;
res:=res+i;
end loop;
dbms_output.put_line('结果是：'|| res);
end;

 

/*案例02：*/
declare
i int:=1;
begin
while i<=10 loop
insert into temp values (i);
i:=i+1;
end loop;
end;

--7.for循环
declare
res int:=0;
begin
for i in 1..10 loop
res:=res+i;
end loop;
dbms_output.put_line('结果是：'|| res);
end;

--8.null的使用
declare 
thisday date;
begin
 thisday:=sysdate;
if thisday <to_date('2006-03-12', 'yyyy-mm-dd' )then  /*通过格式转换，最好按照这样的转换*/
 dbms_output.put_line('this is day:' ||to_char(thisday,'yyyy-mm-dd'));
 else
   null;
 end if;
exception
 when no_data_found then
 dbms_output.put_line('没有数据');
end;

-------------------------------内容4：错误处理--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--案例01：使用预定义错误data_not_found(单变量)

declare
nm emp.ename%type;
begin
select ename into nm  from emp where empno=7788;
dbms_output.put_line('name'||nm);
exception
when no_data_found then
dbms_output.put_line('不存在这个员工！');
end;

--案例02：预定义错误data_not_found（单变量）
declare
nm emp.ename%type;
no emp.empno%type;
begin
select ename into nm  from emp where empno=7788;
dbms_output.put_line('name:'||nm);
select empno into no from emp where empno=9999;   /*语句是不存在的，执行时会跳到异常部分*/
dbms_output.put_line('number:'||no);
exception
when no_data_found then
dbms_output.put_line('不存在这个员工！');
end;

/*如果没有异常部分，则整个语句块都不能进行*/

--案例03：使用多个预定义错误
declare
nm emp.ename%type;
no emp.empno%type;
begin
select ename into nm  from emp where empno=7788;
dbms_output.put_line('name:'||nm);
select empno into no from emp where sal<5000;   /*语句是不存在的，执行时会跳到异常部分*/
dbms_output.put_line('number:'||no);
exception
when no_data_found then
dbms_output.put_line('不存在这个员工！');
when too_many_rows  then
dbms_output.put_line('数据太多！');
end;

--案例04：未知的错误信息
declare
nm emp.ename%type;
no emp.empno%type;
begin
select ename into nm  from emp where empno=7788;
dbms_output.put_line('name:'||nm);
select empno into no from emp where sal<5000;  /*语句是不存在的，执行时会跳到异常部分*/
dbms_output.put_line('number:'||no);
exception
when no_data_found then
dbms_output.put_line('不存在这个员工！');
when too_many_rows  then
dbms_output.put_line('数据太多！');
when others then
dbms_output.put_line('没有遇到过的错误！');
end;

--案例05：自定义错误变量

declare
invalidcategory exception;
category varchar2(10);
begin
category:='&category';
if category not in ('附件', '顶盖', '备件') then
raise invalidcategory;
else
dbms_output.put_line(' 您输入的类别是：'|| category);
end if;
exception
when invalidcategory then
dbms_output.put_line('对不起，无法识别这个设备！');
end;

--案例06：自定义变量
/*
高考时需要选择学校专业，如果填报的是这些专业'语文', '数学', '物理','化学', '生物'，中间任意一个专业就提示：‘欢迎你填报我校的%专业，感谢你选这我校，祝你好运！’如果填报的不是我们学校的专业则提示错误：‘对不起，这个不是我们学校的专业！请查实后再填报

’*/

declare
notmajor exception;
work varchar2(10);
begin
work:='&wk';
if work in ('语文', '数学', '物理','化学', '生物' ) then
dbms_output.put_line(' 欢迎你填报我校的:'||work||'专业,感谢你选择我校,祝你好运！');
else
raise notmajor;
end if;
exception
when notmajor then
dbms_output.put_line('对不起，这个不是我们学校的专业！请查实后再填报！');
end;