数据编程plsql 小结

                   数据编程plsql --小结

PLSQL编程

1.plsql程序块
/*
declare
声明变量；--非必须
begin
  程序执行主体；--必须
exception
  异常处理；--非必须
end ；--必须
*/
例：
create table test_table(ncolumn number,vcolumn varchar2(20));

SELECT * FROM test_table;

declare
  v_num1 number:=1;
  v_num2 number:=2;
  v_str1 varchar2(20):='hello';
  v_str2 varchar2(20):='world';
  v_outputstr varchar2(20);
begin
   insert into test_table(ncolumn,vcolumn)   values (v_num1,v_str1);
   insert into test_table(ncolumn,vcolumn)   values (v_num2,v_str2);
   select vcolumn into v_outputstr from test_table where ncolumn = v_num1;
   dbms_output.put(v_outputstr||' ');
   select vcolumn into v_outputstr from test_table where  ncolumn = v_num2;
   dbms_output.put_line(v_outputstr);
end;
--变量：参与程序执行过程中的中间量，用于数据的传递或者临时存储
--：= 给变量赋值，可以在 declare 部分赋值，也可以在 begin 部分赋值
--变量可以重复赋值
-- := 赋值，= 比较运算符
--select ... into ... 使用查询语句给变量赋值
--dbms_output.put_line() 将括号中的内容打印输出到屏幕上，并且换行
--dbms_output.put() 将括号中的内容打印输出到屏幕上
--如果单独使用 dbms_output.put() 不会执行打印操作，直到使用 dbms_output.put_line() 之后
--才会执行所有的打印操作

--异常处理
declare
 v_nolumn number:=6;
 v_vcolumn varchar(20);
begin
  select vcolumn into v_vcolumn from test_table where ncolumn = v_nolumn;
  dbms_output.put_line(v_vcolumn);
Exception
 when no_data_found then   --还有很多种语句如下：

   1.1=【oracle  自带================================================

     ACCESS_INTO_NULL                     未定义对象  CASE_NOT_FOUND                       CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时  COLLECTION_IS_NULL                  集合元素未初始化  CURSER_ALREADY_OPEN              游标已经打开  DUP_VAL_ON_INDEX                    唯一索引对应的列上有重复的值  INVALID_CURSOR                        在不合法的游标上进行操作  INVALID_NUMBER                        内嵌的 SQL 语句不能将字符转换为数字  NO_DATA_FOUND                        使用 select into 未返回行，或应用索引表未初始化的元素时  TOO_MANY_ROWS                       执行 select into 时，结果集超过一行  ZERO_DIVIDE                              除数为 0  SUBSCRIPT_BEYOND_COUNT        元素下标超过嵌套表或 VARRAY 的最大值  SUBSCRIPT_OUTSIDE_LIMIT         使用嵌套表或 VARRAY 时，将下标指定为负数  VALUE_ERROR                             赋值时，变量长度不足以容纳实际数据  LOGIN_DENIED                            PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码  NOT_LOGGED_ON                        PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据  PROGRAM_ERROR                        PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包  ROWTYPE_MISMATCH                  宿主游标变量与 PL/SQL 游标变量的返回类型不兼容  SELF_IS_NULL                             使用对象类型时，在 null 对象上调用对象方法  STORAGE_ERROR                        运行 PL/SQL 时，超出内存空间  SYS_INVALID_ID                         无效的 ROWID 字符串  TIMEOUT_ON_RESOURCE          Oracle 在等待资源时超时 
 dbms_output.put_line('sorry,no data found');
end;

2.变量声明

--变量的作用域和可见性
例1：
declare
global_var varchar2(48);
begin
   global_var:='hello,boy';
   dbms_output.put_line(global_var);
   begin    --子程序块开始
      declare
      local_var1 varchar2(48);
      global_var varchar2(48);
      --子程序重新声明了变量 global_var ，子程序和主程序分别使用不同的变量
      begin
         local_var1:='This is internal var';
         global_var:='hello girl';
         dbms_output.put_line(local_var1);
         dbms_output.put_line(global_var);
      end;
   end;     --子程序块结束
        dbms_output.put_line(global_var);
end;

例2：
declare
global_var varchar2(48);
begin
   global_var:='hello,boy';
   dbms_output.put_line(global_var);
   begin    --子程序块开始
      declare
      local_var1 varchar2(48);
      --子程序中没有重新声明变量，和主程序使用同一个变量
      begin
        dbms_output.put_line(global_var);
         local_var1:='This is internal var';
         global_var:='hello girl';
         dbms_output.put_line(local_var1);
         dbms_output.put_line(global_var);
      end;
   end;     --子程序块结束
        dbms_output.put_line(global_var);
        --dbms_output.put_line(local_var1);
end;

--变量的数据类型
例：
   declare
    mynum  number(10,2);
    mynum1 number(10);
    mynum2 number(4,-1);
  begin
    mynum:=1234.678;
    mynum1:=123.678;
    mynum2:=1239;
    dbms_output.put_line(mynum);
    dbms_output.put_line(mynum1);
    dbms_output.put_line(mynum2);
  end;

例：
declare
mychar1 char(8);
mychar2 char(24);
begin
  mychar1:='myoracle';
  mychar2:='myoracle';
  dbms_output.put_line(mychar1);
  dbms_output.put_line(mychar2);
end;

例：
declare
mychar1 varchar2(8);
mychar2 varchar2(24);
begin
  mychar1:='myoracle';
  mychar2:='myoracle';
  dbms_output.put_line(mychar1);
  dbms_output.put_line(mychar2);
end;

例：
declare
myvar1   boolean;
myvar2   boolean;
myvar3   boolean;
begin
  myvar1:= true;       
   myvar2:= false;
  if myvar1 = true then
  dbms_output.put_line('myvar1 is true');
  end if;
  if myvar2 = false then
  dbms_output.put_line('myvar2 is false');
  end if;
  if myvar3 is null then
  dbms_output.put_line('myvar3 is null');
  end if;
end;

例：
declare
mydate  date;
begin
  select hiredate  into mydate from emp where empno=7499;
  dbms_output.put_line('The hiredate for me is '||mydate);
   dbms_output.put_line(sysdate + 365);
end;

--表达式
declare
grade char:='A';
mystr  varchar2(20);
result varchar2(20);
mydate date;
begin
   mystr:='New Year';                     --字符表达式
   mydate:=sysdate;                       --日期表达式
   dbms_output.put_line(3**2);     --数值型表达式
   if ('Hello' < 'hello' )  then            --字符型表达式
   dbms_output.put_line('hello>Hello');
   end if;
   result:=                                          --case表达式
   case grade
      when 'A' then 'Excellent'
      when 'B' then 'Good'
      when 'C' then 'Pool'
    end ;
    dbms_output.put_line(result);
    dbms_output.put_line( mystr|| mydate); 
end;

练习1：
编写一个plsql块，要求能够对emp表中10部门的员工人数进行统计，
并在屏幕上打印出来。
declare
v_num number(2);
v_deptno number(2):=10;
begin
  SELECT COUNT(empno) INTO v_num FROM emp WHERE deptno = v_deptno;
  dbms_output.put_line('the employees in dept'||v_deptno||' is '||v_num);
end;

练习2：
编写一个plsql块，要求能够对emp表中的所有员工平均工资进行统
计，并在屏幕上打印出来。
declare
v_sal number(6,2);
begin
  SELECT AVG(sal) INTO v_sal FROM emp;
  dbms_output.put_line(v_sal);
end;

练习3：
编写一个plsql块，输入一个部门号，统计出该部门的平均工资，
并在屏幕上打印出来。
declare
v_sal emp.sal%type;
v_deptno emp.deptno%type := &deptno;
begin
  SELECT AVG(sal) INTO v_sal FROM emp WHERE deptno = v_deptno;
  dbms_output.put_line(v_sal);
end;
--& 表示允许手动输入数值，赋值给变量

第二章 表和记录

1.记录
--表中的一行，由各个列的属性组成，包括列名，数据类型，长度限制
--可以将记录理解成一张表，只是这张表只有一行
SELECT * FROM emp;
/*
记录类型的定义
type 记录类型名称 is record     --定义表的结构
(
列名1 数据类型，
列名2 数据类型，
……
列名n 数据类型
);
变量名称 记录类型名称；    --定义表名
*/
SELECT * FROM dept;

declare
--记录类型的声明过程都在 declare 部分
type deptrecord is record
(
v_no number(2),
v_name varchar2(20),
v_loc varchar2(20)
);
dept20 deptrecord;
begin
  SELECT * INTO dept20 FROM dept WHERE deptno = 20;
  dbms_output.put_line(dept20.v_no);
  dbms_output.put_line(dept20.v_name);
  dbms_output.put_line(dept20.v_loc);
  dept20.v_no := 60;
  dept20.v_name := 'TEACHING';
  dept20.v_loc := 'CHENGDU';
  INSERT INTO dept VALUES (dept20.v_no,dept20.v_name,dept20.v_loc);
end;

--%type 定义变量的数据类型与表中的某一列一致
declare
type deptrecord is record
(
v_no dept.deptno%type,
v_name dept.dname%type,
v_loc dept.loc%type
);
dept20 deptrecord;
begin
  SELECT * INTO dept20 FROM dept WHERE deptno = 20;
  dbms_output.put_line(dept20.v_no);
  dbms_output.put_line(dept20.v_name);
  dbms_output.put_line(dept20.v_loc);
  dept20.v_no := 70;
  dept20.v_name := 'TEACHING';
  dept20.v_loc := 'CHENGDU';
  INSERT INTO dept VALUES (dept20.v_no,dept20.v_name,dept20.v_loc);
end;

--%rowtype 定义一个记录类型的变量，让记录类型与某张表的结构一致
--变量名 表名%rowtype
declare
dept20 dept%rowtype;
begin
  SELECT * INTO dept20 FROM dept WHERE deptno = 20;
  dbms_output.put_line(dept20.deptno);
  dbms_output.put_line(dept20.dname);
  dbms_output.put_line(dept20.loc);
  dept20.deptno := 70;
  dept20.dname := 'TEACHING';
  dept20.loc := 'CHENGDU';
  INSERT INTO dept VALUES (dept20.deptno,dept20.dname,dept20.loc);
end;

练习：
1.编写一个plsql块，要求按照dept表，定义一个记录类型，
记录类型中的成员使用%type与dept表中的各个字段类型保持一致,输出20号部门名称
declare
type deptrecord is record
(
v_no dept.deptno%type,
v_name dept.dname%type,
v_loc dept.loc%type
);
dept20 deptrecord;
begin
  SELECT * INTO dept20 FROM dept WHERE deptno = 20;
  dbms_output.put_line(dept20.v_name);
end;

2.编写一个plsql块，要求按照dept表，定义一个记录类型，
记录类型中的成员使用%rowtype与dept表中的各个字段类型保持一致,
给这个记录类型的各个成员赋值，并将其插入到dept表中。
declare
dept60 dept%rowtype;
--记录类型只能在 begin 部分赋值，不能在 declare 部分赋值
begin
  dept60.dname := 'develop';
  dept60.deptno := 60;
  dept60.loc := 'HUAYANG';
  INSERT INTO dept VALUES (dept60.deptno,dept60.dname,dept60.loc);
end;
SELECT * FROM dept;

第三章 控制结构

1.条件控制

--if …… then ……
/*
if 条件判断 then
  执行语句；
end if ；
*/
例：
 declare
    v_hours number:=30;
    v_overtime number:=0;
begin
  if  v_hours>40 then
  v_overtime:=v_hours -40;
   dbms_output.put_line('Hours overtime worked ='||v_overtime);
  end if;
 End;

--输入一个部门编号，计算该部门的平均工资，如果平均工资低于1800 则输出“该涨工资了！”
declare
v_deptno emp.deptno%type := &deptno;
v_sal emp.sal%type;
begin
  SELECT AVG(sal) INTO v_sal FROM emp WHERE deptno = v_deptno;
  dbms_output.put_line(v_sal);
  if v_sal < 1800 then
    dbms_output.put_line('It is time to raise!');
  end if;
end;

--if ... then ... else ...
/*
if 条件判断 then
  执行语句 1；
  else 执行语句2 ；
end if ；
*/
例：
declare
    v_hours number:=30;
    v_overtime number:=0;
begin
  if v_hours>40 then
  v_overtime:=v_hours - 40;
   dbms_output.put_line('Hours overtime worked ='||v_overtime);
 else
 dbms_output.put_line('Hours are not overtime ');
  end if;
 End;
 
--输入一个部门编号，计算该部门的平均工资，如果平均工资低于1800 则输出“该涨工资了！”
--如果平均工资不低于1800 ，则输出“该部门工资还行”
declare
v_deptno emp.deptno%type := &deptno;
v_sal emp.sal%type;
begin
  SELECT AVG(sal) INTO v_sal FROM emp WHERE deptno = v_deptno;
  dbms_output.put_line(v_sal);
  if v_sal < 1800 then
    dbms_output.put_line('It is time to raise!');
    else dbms_output.put_line('The salary of dept'||v_deptno||' is OK!');
  end if;
end;

--if then elsif then ... else...
/*
if 条件判断1 then
  执行语句 1；
  elsif 条件判断2 then
    执行语句2 ；
    ……
    elsif 条件判断n then
      执行语句n；
      else 执行语句n+1；
end if ；
*/
例：
Declare
     v_hours number:=10;                    
     v_overtime number:=0;
begin
    if v_hours>40 then
    v_overtime:=v_hours - 40;
    dbms_output.put_line('Hours overtime worked ='||v_overtime);
    elsif v_hours <= 40 AND v_hours>20 then
    dbms_output.put_line('Hours are not overtime');
    else
    dbms_output.put_line('Hours are too few ');
    end if;
 End;

--输入一个部门编号，计算该部门的平均工资，如果平均工资低于1800 则输出“该涨工资了！”
--如果平均工资在 1800 到 2800 之间，则输出“该部门工资还行”
--如果平均工资高于 2800 则输出“该部门工资非常高”
declare
v_deptno emp.deptno%type := &deptno;
v_sal emp.sal%type;
begin
  SELECT AVG(sal) INTO v_sal FROM emp WHERE deptno = v_deptno;
  dbms_output.put_line(v_sal);
  if v_sal < 1800 then
    dbms_output.put_line('It is time to raise!');
    elsif v_sal BETWEEN 1800 AND 2800 then
      dbms_output.put_line('The salary of dept'||v_deptno||' is OK!');
    else dbms_output.put_line('The salary of dept'||v_deptno||' is VERY GOOD!');
  end if;
end;

--case 语句
/*
case
  when 条件判断1 then 执行语句1；
    when 条件判断2 then 执行语句2；
    ……
    when 条件判断n then 执行语句n；
      else 执行语句n+1；
end case ；

case 变量
  when value1 then 执行语句1；
    when value2 then 执行语句2；
      ……
      when valueN then 执行语句N；
        else 执行语句N+1；
end case ；
*/
declare
 grade char:='M';
Begin
  case grade
  when 'A' then dbms_output.put_line('Excellent');
  when 'B' then dbms_output.put_line('Very Good');
  when 'C' then dbms_output.put_line('Good');
  when 'D' then dbms_output.put_line('Fair');
  when 'E' then dbms_output.put_line('Poor');
  else dbms_output.put_line('No such grade');
  end case;
end;

--输入一个部门编号，计算该部门的平均工资，如果平均工资低于1800 则输出“该涨工资了！”
--如果平均工资在 1800 到 2800 之间，则输出“该部门工资还行”
--如果平均工资高于 2800 则输出“该部门工资非常高”
declare
v_deptno emp.deptno%type := &deptno;
v_sal emp.sal%type;
begin
  SELECT AVG(sal) INTO v_sal FROM emp WHERE deptno = v_deptno;
  dbms_output.put_line(v_sal);
  case
    when v_sal < 1800 then dbms_output.put_line('It is time to raise!');
    when v_sal BETWEEN 1800 AND 2800 then dbms_output.put_line('The salary of dept'||v_deptno||' is OK!');
    else dbms_output.put_line('The salary of dept'||v_deptno||' is VERY GOOD!');
  end case;
end;

2.循环控制

--loop
/*
loop
  执行语句；
  循环语句；
  exit when 退出循环的条件；
end loop;
*/
declare
    v1 number:=10;
begin
loop
   v1:=v1-1;
   if v1<3 then
   dbms_output.put_line('The v1 is: '||v1);
   end if;
   exit when v1<0;
end loop;
end;

declare
    v1 integer:=10;
begin
loop
   v1:=v1-1;
   if v1<3 then
   dbms_output.put_line('The v1 is: '||v1);
   end if;
  
end loop;
end;

--使用循环计算 1+2+3. ……+100 的结果
declare
v_no number(3) := 0;
v_result number(4) := 0;
begin
  loop
    v_no := v_no + 1;
    v_result := v_result + v_no;
    dbms_output.put_line(v_no||'     '||v_result);
    exit when v_no = 100;
  end loop;
end;
--循环语句和执行语句的顺序，以及退出条件都相互关联相互影响

--while loop 循环
/*
while 进入循环的条件 loop
  循环语句；
  执行语句；
end loop ；
*/
declare
    total number:=0;
begin
  while total<20 loop
  total:=total+1;
  dbms_output.put_line('The value of total is :'||total);
  end loop;
end;

--使用循环计算 1+2+3. ……+100 的结果
declare
v_no number(3) := 0;
v_result number(4) := 0;
begin
  while v_no < 100 loop
    v_no := v_no+1;
    v_result := v_result + v_no;
    dbms_output.put_line(v_no||'     '||v_result);
  end loop;
end;
--while loop 条件写在进入循环之前，如果不满足条件，可能一次循环都不会执行
--loop 条件写在进入循环之后，无论是否满足条件，都至少会执行一次循环

--for
/*
for i in [reverse] 最小值 .. 最大值 loop
  执行语句；
end loop;
让 i  从最小值 到 最大值递增
如果需要递减，使用 reverse 关键字
*/
declare
total integer:=0;
begin
  for i in 1..10 loop
  total:=total+1;
   dbms_output.put_line('The value of total is :'||total);
  end loop;
  dbms_output.put_line('The value of total is :'||total);
end;
--如果 执行语句与 i 无关，i 表示执行循环的次数

declare
begin
  for i in reverse 1..3 loop
   dbms_output.put_line('The value of i is :'||i);
  end loop;
end;

--使用循环计算 1+2+3. ……+100 的结果
declare
v_result number(4) := 0;
begin
  for i in 1..100 loop
    v_result := v_result + i;
    dbms_output.put_line(i||'     '||v_result);
  end loop;
end;

练习1：
编写一个 plsql块，要求输入一个部门编号，就可以将emp表中该部门的员工薪资进行统计。
要求对该部门最高薪水－最低薪水的之差进行判断，
如果差距大于等于2000,输出标记‘H’
如果差距小于2000，大于等于1000，输出标记‘M’
如果差距小于1000，输出标记‘L’
用if实现
declare
v_deptno emp.deptno%type := &deptno;
v_sal emp.sal%type;
begin
  SELECT MAX(sal)-MIN(sal) INTO v_sal FROM emp WHERE deptno = v_deptno;
  dbms_output.put_line(v_sal);
  if v_sal >= 2000 then
    dbms_output.put_line('H');
    elsif v_sal < 2000 AND v_sal >= 1000 then
       dbms_output.put_line('M');
       else  dbms_output.put_line('L');
  end if;
end;

练习2：
在1的基础上，用case实现，
如果标记为H，输出‘员工工资差距超过2000’
如果标记为M，输出‘员工工资差距在1000－2000之间’
如果标记为L，输出‘员工工资差距在1000以内’
declare
v_deptno emp.deptno%type := &deptno;
v_sal emp.sal%type;
v_mark char(1);
begin
  SELECT MAX(sal)-MIN(sal) INTO v_sal FROM emp WHERE deptno = v_deptno;
  dbms_output.put_line(v_sal);
  if v_sal >= 2000 then
    v_mark := 'H';
    elsif v_sal < 2000 AND v_sal >= 1000 then
       v_mark := 'M';
       else v_mark := 'L';
  end if;
  case v_mark
    when 'H' then  dbms_output.put_line('the distance of salary is more than 2000');
    when 'M' then dbms_output.put_line('the distance of salary is between 1000 and 2000');
    else dbms_output.put_line('the distance of salary is less than 1000');
   end case;
end;

练习3：
自定义一张表，字段（st_id(主键)，st_name（不能重复），phno）用循环向这张表中插入100行记录。
要求：st_id，st_name不能重复。
试着自己编写一个plsql 块，完成以上要求。
用loop，while loop，for循环其中一种来实现

CREATE TABLE st1
(
st_id number(4) PRIMARY KEY,
st_name varchar2(10) UNIQUE,
phno number(11)
);
SELECT * FROM st1;

declare
st st1%rowtype;
begin
  for i in 1..100 loop
    st.st_id := 1000+i;
    st.st_name := 'name'||i;
    st.phno := 13800138000+i;
    INSERT INTO st1 VALUES(st.st_id,st.st_name,st.phno);
  end loop;
end;