数据库：PL/SQL

种一棵树001

已于 2024-09-28 19:48:00 修改

阅读量1.2k

点赞数 21

分类专栏： # SQL 文章标签： sql

于 2024-09-26 20:04:34 首次发布

本文链接：https://blog.csdn.net/weixin_58722381/article/details/142573469

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

变量

变量建议使用V开头
作用: 用来保存一个数据

普通变量

declare 
--定义一个变量（保存一个数据）
v_email varchar2(20);
--定义变量并且赋值
v_ename varchar2(20) :='张三';
begin
  v_email :='553215@qq.com';
  dbms_output.put_line(v_email);
  dbms_output.put_line(v_ename);
end;

引用型变量（保存一个数据）–引用某张表的某个字段类型来作为变量的类型

declare
ename emp.ename%type := 'smith';
sal emp.sal%type :='9000';
begin
  dbms_output.put_line(ename||':'||sal);
  end;

记录型变量（用来保存一条数据） --用某张表的一行字段的类型来作为变量的类型

  declare
  v_emp emp%rowtype;
  begin
  dbms_output.put_line(v_emp.empno);
  dbms_output.put_line(v_emp.ename);
  dbms_output.put_line(v_emp.job);
end;

游标用来保存多条数据

declare
cursor emp_cursor is select * from emp;
begin
  for i in emp_cursor loop
    dbms_output.put_line(i.empno||':'||i.ename);
    end loop;
   end;

常量：constant --定义常量时必须赋值，定义好的常量不能更改

declare
c_name constant varchar2(20):='tom';
begin
  c_name := 'jack';
  dbms_output.put_line(c_name);
end;

代码块

代码块就是（一段程序），一般用来处理数据

例  
 --查询SMITH的工资
declare
v_sal emp.sal%type;
v_ename varchar2(20):='SMITH';
begin
  -- v_sal:=sal
  select sal into v_sal from emp where ename = v_ename;
  dbms_output.put_line(v_sal);
end;

--把7788员工的工资+百分之10 并输出姓名和加百分之10后的工资
declare
v_ename emp.ename%type;
v_empno emp.empno%type:=7788;
v_sal number(9,2);
begin
  select ename,sal*1.1 into v_ename,v_sal
   from emp where empno = v_empno;
   dbms_output.put_line(v_ename||':'||v_sal);
end;

--查出SMITH所有的信息
declare
v_emp emp%rowtype;
begin
  select * into v_emp from emp where ename = 'SMITH';
  dbms_output.put_line(v_emp.empno||':'||v_emp.ename||':'||v_emp.sal);
end;

--输入名字查询 输出姓名和职位，工资
declare
v_ename emp.ename%type:='&输入姓名';
v_job emp.job%type;
v_sal emp.sal%type;
begin
  select job,sal into v_job,v_sal from emp where ename = v_ename;
  dbms_output.put('姓名：'||v_ename);
  dbms_output.put('职位：'||v_job);
  dbms_output.put_line('工资：'||v_sal);
end;

dbms_output.put 输出在内存而且不换行
dbms_output.put_line 把内存的数据进行输出，并且换行

练习
1 查出ALLEN的工资和职位
2 查出scott的入职时间
3 输入员工号 输出员工姓名
4 输入部门号 输出部门名
declare 
--1
v_sal emp.sal%type;
v_job emp.job%type;
--2
v_hiredate emp.hiredate%type;
--3
v_empno emp.empno%type := '&员工号';
v_ename emp.ename%type;
--4
v_deptno dept.deptno%type := '&部门号';
v_dname dept.dname%type;
begin
  select sal,job into v_sal,v_job from emp where ename ='ALLEN';
  dbms_output.put_line(v_sal);
  dbms_output.put_line(v_job);
  --2
  select hiredate into v_hiredate from emp where ename = upper('scott');
  dbms_output.put_line(v_hiredate);
  --3
  select ename into v_ename from emp where empno = v_empno;
  dbms_output.put_line('员工姓名'||v_ename);
  --4
  select dname into v_dname from dept where deptno =v_deptno;
  dbms_output.put_line('部门名'||v_dname);
end;

循环结构

for循环

语法： for 循环变量 in 范围 loop（开始循环） end loop;（结束循环）

--输出10个大数据
declare 
begin 
  for i in 1..10 loop
    dbms_output.put('大数据 ');
    end loop;
    dbms_output.put_line(' ');
end;

--输出所有员工的姓名
begin
  for i in (select ename from emp) loop
    dbms_output.put_line(i.ename);
    end loop;
end;

--计算1-100之间所有单数和
declare
v_sum number := 0;
begin
  for i in 1..100 loop
    if mod(i,2) = 1 then
    v_sum := v_sum+i;
    end if;
  end loop;
  dbms_output.put_line(v_sum);
end; 

--找到101-999之间的所有水仙花数
declare
g number;
s number;
b number; 
begin
  for i in 101..999 loop
    g := substr(i,3,1); -- g = mod(i,10)
    s := substr(i,2,1); -- s = mod(floor(i/10),10)   s = floor(mod(i,100)/10)
    b := substr(i,1,1); -- b = floor(i/100)
    if power(g,3)+power(s,3)+power(b,3) = i then
     dbms_output.put_line(i);
     end if;
   end loop;
end; 

--查出10部门 工资大于1500的人数， 和总工资； 不使用函数
declare
v_sum number := 0;
v_number number := 0;
begin 
  for i in (select sal from emp where deptno = 10) loop
    v_sum := v_sum+i.sal;
    if  i.sal>1500 then
      v_number :=  v_number + 1;
      end if;
     end loop;
     dbms_output.put_line('部门总工资: '||v_sum);
     dbms_output.put_line('工资大于1500人数: '||v_number);
end;

while循环

语法:

while 条件 loop
      sql
end loop;

--有张纸，厚度是1mm，珠穆朗玛峰海拔8848m，请问，这张纸对折多少次，厚度会超过山的高度
declare
v_count number :=0;
v_houdu number := 1;
v_hight number := 8848000;
begin
  while v_houdu<=v_hight loop
    v_houdu := v_houdu*2;
    v_count:= v_count+1;
    dbms_output.put_line('对折次数：'||v_count||' 纸张厚度：'||v_houdu);
end loop;
end;

--假如从今天开始存1分钱，每天翻倍，明天2分，后天4分，大后天8分，请问要多少天才能存够100万元
declare
v_deposit number :=0; --存款
v_day number :=0; --存款天数
begin
  while v_deposit<100000000 loop
    v_deposit := v_deposit+power(2,v_day);
    v_day := v_day+1;
    dbms_output.put_line('存款天数：'||v_day||' 存款：'||v_deposit);
    end loop;
end;

loop循环

语法:

 loop exit when 条件  end loop;
--条件满足就退出循环

--用loop循环解决上述两个问题
declare
v_count number := 0; --对折次数
v_houdu number := 1; --纸张厚度
begin
  loop 
    exit when
    v_houdu > 8848000; 
    v_count:=v_count+1;
    v_houdu:=v_houdu*2;
    dbms_output.put_line('对折次数：'||v_count||' 纸张厚度：'||v_houdu);
    end loop;
    end;
    
declare 
v_deposit number := 0; --存款
v_day number := 0; --天数
begin 
  loop exit when v_deposit >= 100000000;
       v_deposit := v_deposit+power(2,v_day);
       v_day:=v_day+1;
       dbms_output.put_line('天数：'||v_day||' 存款：'||v_deposit);
       end loop;
       end;

动态sql

语法 execute immediate sql

–DDL 语句在plsql 只能使用动态SQL（create drop alter）
–DML 语句在plsql 可以直接执行（update，insert delete）
–DQL 语句在plsql 需要使用into （select）（把查询的结果通过into赋值给变量）

--输入表名 删除该表
declare 
v_table_name varchar2(20) :='&表名';

begin    
  execute immediate 'drop table '||v_table_name;
  end;

--输入旧表名 输入新表名 复制一张新表
declare 
v_old_name varchar2(20) := '&旧表名';
v_new_name varchar2(20) := '&新表名';
begin
  execute immediate 'create table '||v_new_name||' as select * from '||v_old_name;
end;

--动态sql 向部门表插入数据
declare 
v_sql varchar2(200) :='';
v_deptno number :='&部门编号';
v_dname varchar2(20) :='&部门名称';
v_loc varchar2(30) :='&部门地址';
begin
  v_sql :='insert into dept values('||v_deptno||','''||v_dname||''','''||v_loc||''')';
  dbms_output.put_line(v_sql);
  execute immediate v_sql;
  end;

游标

用来保存一个查询的结果集

静态游标游标的数据不能发生改变
- 显示游标（自己手动创建可以看到cursor关键字）
- 隐式游标（程序执行DML 或者select into 语句会自动创建看不到cursor关键字）
动态游标游标的数据可以发生改变

静态游标

显式游标

--使用while 循环操作游标
--使用游标显示每个人的姓名和工资
declare
--定义游标
cursor emp_cursor is select * from emp;
--定义记录型变量
v_emp emp%rowtype;
begin
  --打开游标
  open emp_cursor;
  --提取数据
  fetch emp_cursor into v_emp;
  while emp_cursor%found loop --判断是否提取到数据
    dbms_output.put_line(v_emp.ename||':'||v_emp.sal);
    fetch emp_cursor into v_emp;--继续提取游标中的数据，确保循环会终止
    end loop;
    --关闭游标
    close emp_cursor;
end;

--使用for循环操作游标   for循环会自动打开 提取 关闭 游标
declare 
cursor emp_cursor is select * from emp;
begin
  for i in emp_cursor loop
    dbms_output.put_line(i.ename||':'||i.sal);
    end loop;
    end;
    
    select * from dept;

练习
1 使用游标修改工资
    10部门的员工加百分之十 20部门的员工加百分之二十 30部门的员工加百分之三十
declare 
cursor emp_cursor is select * from emp;
begin
  for i in emp_cursor loop
    if i.deptno = 10 then 
      update emp set sal = i.sal*1.1 where  empno = i.empno ;
      elsif i.deptno = 20 then
        update emp set sal =  i.sal *1.2 where empno = i.empno ;
        elsif i.deptno = 30 then
          update emp  set sal = i.sal*1.3 where  empno = i.empno;
          end if;
          end loop;
          end;

2 使用游标 输出张三的总成绩，平均成绩 和姓名 （不使用函数）
declare
cursor sc_cursor is select a.cno,a.score from sc_a02 a，student_a02 b where sname = '张三' and a.sno = b.sno;
v_count number :=0; --记录科目数
v_sum_score number :=0; --总成绩
v_avg_score number;
begin
  for i in sc_cursor loop
   v_count:=v_count+1;
   v_sum_score := i.score+v_sum_score;
   end loop;
   v_avg_score := round(v_sum_score/v_count);
   dbms_output.put_line('总成绩：'||v_sum_score);
   dbms_output.put_line('平均成绩：'||v_avg_score);
   dbms_output.put_line('姓名：张三');
   end;

3 使用游标 把平均分大于80的学生信息插入到一张新表 （字段：姓名，年纪，性别，平均分 先创建该表）
begin
  execute immediate 'create table exc_stu_info(sname varchar2(20),sage number(3),sex varchar2(2),avg_sc number(5))';
end;
declare
cursor exc_cursor is
select sname,sage,ssex, 平均成绩 from student_a02 a,
(select sno,round(avg(score)) 平均成绩 from sc_a02 group by sno) b
where a.sno= b.sno and 平均成绩 >80;
begin
  for i in exc_cursor loop
   insert into exc_stu_info values(i.sname,i.sage,i.ssex,i.平均成绩);
  end loop;
end;
select * from exc_stu_info;

隐式游标

（程序执行DML 或者 select into 语句会自动创建 看不到cursor 关键字 默认游标名SQL）

begin 
  delete from emp where empno=7369;
  if SQL%found then
    dbms_output.put_line('删除成功');
    end if;
end;

–SQL%found 判断是否有数据有就返回True 没有返回False
–SQL%notfound 判断是否没有数据没有返回true 有就返回false
–SQL%rowcount
–显示游标代表行数
–隐式游标代表影响（删除，修改）的行数

begin
  update emp set sal = 10000 where deptno =10;
  if SQL%found then 
    dbms_output.put_line('成功修改'||SQL%rowcount||'条数据');
    end if;
end;

动态游标

游标的数据可以发生改变（游标可以改变指向） 但是不能用for循环，只能手动打开游标查询数据

语法 要先定义动态游标的类型 再定义动态游标变量

 declare
 type ref_cursor is ref cursor;
 data_cursor ref_cursor;
 --定义记录型变量
 v_dept dept%rowtype;
 begin
   open data_cursor for select * from emp;
   fetch date_cursor into v_emp; --提取游标数据赋值给记录型变量
   while data_cursor%found loop
     dbms_ouput.put_line(v_emp.ename);
     fetch data_cursor into v_emp;
     end loop;
     clos data_cursor;
     
     --修改动态游标指向
    open data_cursor for select * from dept;
     fetch data_cursor into v_dept;
   while data_cursor%found loop
     dbms_output.put_line(v_dept.dname);
     fetch data_cursor into v_dept;
   end loop;
   close data_cursor;
end;

–定义两个静态游标一样可以实现

declare 
cursor emp_cursor is select * from emp;
cursor dept_cursor is select * from dept;
begin
      for i in emp_cursor loop
        dbms_output.put_line(i.ename);
      end loop;
      
       for i in dept_cursor loop
        dbms_output.put_line(i.dname);
      end loop;
end;

练习
1 --输入部门号 --如果部门号不存在，输出 没有该部门  
--如果该部门没有员工 ，输出该部门没有员工  --如果该部门有员工 ，输出该部门所有员工姓名 
declare
v_deptno emp.deptno%type :='&部门号';
v_ename varchar2(100);
v_flag number :=0; --0 没有该部门  1 有该部门
v_empnum number :=0;
begin
  for i in (select deptno from dept) loop
    if v_deptno = i.deptno then
      V_flag :=1;
      select count(1) into v_empnum from emp where deptno =v_deptno; 
      if v_empnum=0 then
        dbms_output.put_line('该部门没有员工');
        else 
          select LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename)  into v_ename from emp where deptno = v_deptno group by deptno;
          dbms_output.put_line(v_ename);
       end if;
     end if;
   end loop;
   if v_flag = 0 then
     dbms_output.put_line('没有该部门');
     end if;
     end;  
2 --使用游标输出公司最高工资，（不使用函数）
declare
cursor emp_cursor is select * from emp;
v_maxsal number:=0;
begin
  for i in emp_cursor loop
    if i.sal > v_maxsal then v_maxsal := i.sal; end if;
    end loop;
    dbms_output.put_line(v_maxsal);
    end;
3 --使用动态SQL 把所有的分区表删除 （select * from user_tab_partitions ）
select * from user_tab_partitions;
declare
CURSOR c_part_tables IS  
        SELECT distinct table_name 
        FROM user_tab_partitions;  
begin
  for i in c_part_tables loop
      execute immediate 'drop table "'||i.table_name||'"';
       execute immediate 'PURGE TABLE "'|| i.table_name||'"';
  end loop;
  end;
4 --使用动态SQL 把所有E开头的表复制出来一个新表 （新表名为 原表名+当前时间）
    --如emp 复制出一张 emp20240911     
    --提示 查看所有表的语句 （select * from user_tables; ）
execute immediate 'create table '||v_new_name||' as select * from '||v_old_name;
    select * from user_tables;
declare
cursor tab_cursor is select * from user_tables;
begin
  for i in tab_cursor loop
    if substr(i.table_name,1,1) = 'E' then
      execute immediate 'create table '||i.table_name||to_char(sysdate,'yyyymmdd')||' as select * from '||i.table_name;
      end if;
    end loop; 
 end;

存储过程

有名字的代码块，为了完成特定的功能

语法 create or replace procedure 名字(参数) as 声明变量begin 代码块 end

--创建存储过程，添加部门
create or replace procedure add_dept(dno number,dname varchar2,dloc varchar2)
as
begin
  insert into dept values(dno,dname,dloc);
end;
--调用过程
call add_dept(3,'财务部','东莞');
select * from dept;

   
--创建一个过程 计算两个数之和
create or replace procedure plus(num1 number,num2 number, result out number)
as
begin
  result:=num1+num2;
end;

--带有返回值的过程，要在匿名代码块中调用 ，否则不能声明变量接收返回值
declare
r_sum number;
begin
  plus(100,200,r_sum);
  dbms_output.put_line(r_sum);
end;

–in 参数（是一个变量，用于接收传入的数据）调用者给它赋值
–out 参数（过程执行完毕得到的数据）理解为函数的返回值

--传入部门号返回部门的最高工资，最低工资
create or replace procedure get_max_min_sal(dno in number,maxsal out number,minsal out number)
as
begin
  select max(sal),min(sal) into maxsal,minsal from emp where deptno = dno;
end;
declare
ma number;
mi number;
begin
  get_max_min_sal(10,ma,mi);
  dbms_output.put_line('最高工资'||ma);
  dbms_output.put_line('最低工资'||mi);
end;

--创建一个过程 传入部门号进行删除数据 返回删除的行数
create or replace procedure delete_date(dno in number,d_row out number)
as
begin
  delete from emp where deptno = dno;
  d_row := SQL%rowcount;
end;

declare
row_d number;
begin
  delete_date(40,row_d);
  dbms_output.put_line('删除了'||row_d||'个员工');
end;


--作业
1 创建一个过程返回当前时间（年月日时分秒 都要）
create or replace procedure get_date(curr_time out timestamp)
as
begin
  curr_time := systimestamp;
end;
declare
c_time timestamp;
begin
  get_date(c_time);
  dbms_output.put_line('当前时间：'||to_char(c_time,'yyyy-mm-dd HH24:mi:ss'));
end;

2 创建一个过程实现和power函数相同功能，返回结果
create or replace procedure v_pow(dishu in number,zhishu in number,result out number)
as
begin
  if dishu = 0 then
    dbms_output.put_line('底数不能为0');
    elsif mod(zhishu,1) = 0 then --限定整数指数
      if zhishu >0  then
         result :=1;
         for i in 1..zhishu loop
         result := result * dishu;
         end loop;
         elsif zhishu = 0 then 
               result :=1;
               else --指数小于0
                 result :=1;
                 for i in 1..ABS(zhishu) loop
                 result := result * dishu;
                 end loop;
                 result := 1/result;
      end if; 
      else 
        result := power(dishu,zhishu);
  end if;                   
end;
declare
mi number(10,3);
begin
  v_pow(2,-2,mi);
  dbms_output.put_line(mi);
end;

3 创建一个过程删除没有员工的部门
create or replace procedure delete_01
as
begin
  delete from dept where deptno not in (select distinct deptno from emp);
end;

call delete_01(); 

create or replace procedure delete_dept
as 
v_count number;
begin
  for i in (select deptno from dept) loop
    select count(deptno) into v_count from emp where emp.deptno = i.deptno;
    if v_count = 0 then
       delete from dept where deptno = i.deptno;
    end if;
  end loop;
  dbms_output.put_line(sql%rowcount);
end;
select *from dept;
declare
begin
  delete_dept();
end;


4 创建一个过程把30号部门改为33，原来30号的员工 部门号也改为33 
create or replace procedure up_info(n out number)
as
v_dept dept%rowtype;
begin
  select * into v_dept from dept where deptno=30;
  --添加部门号33
  insert into dept values(33,v_dept.dname,v_dept.loc);
  --修改员工的部门号
  update emp set deptno = 33 where deptno =v_dept.deptno;
  n:=sql%rowcount;
  --删除30号部门
  delete from dept where deptno = 30;
end;
declare 
row_num number;
begin 
  up_info(row_num);
  dbms_output.put_line('修改了'||row_num||'名员工的部门号');
end;

存储函数

存储过程 procedure(DML)
- 可以不返回数据，要返回数据就用out参数
存储函数 function（计算）
- 必须要有一个返回值
- 如果要返回多个值，那么也可以用out参数
- 有out参数就不能使用select调用
- 可以用于DML操作，有了之后不能用select调用

--计算两个数之和，返回结果
create or replace function sum_01(num1 number,num2 number)
return number --定义返回类型
as
begin
  --返回数据
  return num1+num2;
end;
--select 调用
select sum_01(1,2) from dual;
--declare 调用
declare
n number;
begin
  n :=sum_01(3,4);
  dbms_output.put_line(n);
  dbms_output.put_line(sum_01(5,6));
 end;

 --使用函数 根据部门号返回部门人数和平均工资
 create or replace function get_sum_count(vno in number, v_sal out number)
 return number
 as
 v_count number;
 begin
   select count(1),round(avg(sal)) into v_count,v_sal from emp where deptno = vno;
   return v_count;
end;

declare
v_count number;
v_avgsal number;
begin
  v_avgsal:=get_sum_count(33,v_count);
  dbms_output.put_line(v_count);
    dbms_output.put_line(v_avgsal);
end;

触发器

--作用 
     --1.验证数据
     --2.数据备份
     --3.记录日志

--语法
--create or replace trigger 触发器名
--before --被触发的语句先执行    --after  --被触发的语句后执行
--操作（delete，update，insert）
--on 表名
--for each for --行级触发
--begin   被触发sql end；

----当员工表 有员工离职时，  自动把该员工的数据插入到离职表
--emp 在职员工表
--emp_back 离职员工表
--获取触发器 操作 的数据
--：old --用于删除，代表旧数据 代表修改之前的数据
--：new --用于添加，代表新数据 代表修改之后的数据
create table emp_back as  select * from emp where 1=2;
select * from emp_back;
select * from emp; 

create or replace trigger delete_emp_trigger
before delete
on emp 
for each row
begin
  insert into emp_back values(:old.empno,:old.ename,:old.job,:old.mgr,
                              :old.hiredate,:old.sal,:old.comm,:old.deptno);
end;

--触发sql
delete from emp where empno =7369;

--使用触发器，实现订单表和库存表之间的关系
--订单表增加数据，库存表自动减少数量
--库存表
create table stock(
sname varchar2(10),
scount number
);
--订单表
create table orders(
oname varchar2(10),
ocount number
);
insert into stock values('IphoneX',100);
insert into stock values('华为P60',100);

insert into orders values('IphoneX',0);
insert into orders values('华为P60',0);
select * from stock;
select * from orders;

create or replace trigger stock_order_trigger
after update on orders 
for each row
begin
  update stock set scount = scount-(:new.ocount-:old.ocount) where sname = :old.oname;
end;

update orders set ocount = ocount+5 where oname = 'IphoneX';  

数据校验
--禁止删除KING 新员工的工资不能超过3000 涨工资不能超过30%
create or replace trigger check_trigger 
before delete or update or insert
on emp
for each row
begin 
  if delete then
    if :old.ename ='KING' then
      --手动制造一个错误
      raise_application_error('-20002','不能删除老板');
    end if;
  end if;
  
  if insert then
    if :new.sal >3000 then
      raise_application_error('-20003','新员工工资不能超过3000');
      end if;
      end if;
  if update then 
    if :new.sal >:old.sal*1.3 then
      raise_application_error('-20003','工资涨幅不能超过百分之30');
    end if;
  end if;
end;

数据同步
  --使用触发器 备份 部门表
  --创建一个备份表
  create table dept_back as select  * from dept;
  select * from dept;
  select * from dept_back;
create or replace trigger backups_trigger
before insert or update or delete on dept 
for each row
begin
  if inserting then 
    insert into dept_back values(:new.deptno,:new.dname,:new.loc);
    end if;
  
  if updating then
    update dept set dname = :new.dname, loc = :new.loc where deptno = :old.deptno;
    end if;
   
  if deleting then
    delete from dept_back where deptno = :old.deptno;
    end if;
end;

记录日志
--(什么人 什么时候 对表做了什么操作）
--创建一个部门日志表
create table dept_log(
deptno number,
dname varchar2(20),
dloc varchar2(20),
ddate varchar2(20),--时间
duser varchar2(20),--操作人
dtype varchar2(20));--操作类型
  

select * from dept_log;

create or replace trigger dept_log_trigger
after insert or update or delete on dept
for each row
begin 
  if inserting then
    insert into dept_log 
    values(：new.deptno,:new.dname,:new.loc,
           to_char(sysdate,'yyyy-mm-dd HH24:mi:ss'),user,'insert');
  end if;
  
  if deleting then
    insert in dept_log 
    values(:old.deptno,:old.dname,:old.loc,
           to_char(sysdate,'yyyy-mm-dd HH24:mi:ss'),user,'delete');
  end if;
  
  if updating then
    insert in dept_log
    values(:old.deptno,:old.dname,:old.loc,
           to_char(sysdate,'yyyy-mm-dd HH24:mi:ss'),user,'update_before');
     
    insert in deot_log
    values(:new.deptno,:new.dname,:new.loc,
           to_char(sysdate,'yyyy-mm-dd HH24:mi:ss'),user,'update_after');
  end if;
end;

程序包

包
–可以减少名称的定义
–管理相同的表/业务
–不同的程序包，函数、过程的名称可以重复

--创建程序包
  --在包里面声明有哪些过程和函数（具体的功能不需要去实现）
  --过程写到参数部分结束        --函数写到返回值部分结束
--语法
create or replace package 包名
as
过程名（参数）
......
函数名（参数）返回值类型
end 包名

包体

    --创建包体
    --实现过程和函数 写具体代码
create or replace body 对应的包名
as
   procedure 过程名（参数）
     as
     begin
       代码
     end;
     
   function 函数名（参数）
    return 返回值类型
    as
    begin
      代码
      return ...;
    end;
end 包名;

create or replace procedure backup_all_tables
as
cursor back_cursor is select * from user_tables;
begin
  for i in back_cursor loop
    execute immediate 'create table '||i.table_name||tochar(sysdate,'mmdd')||' as select * from '||i.table_name;
  end loop;
 end;
 call backup_all_tables();

 --作业
 create or replace function my_random(P_LENGTH number,P_INCLUDE_CHAR number)
 return varchar2
 as
 result_char varchar2(4000) :='';
 begin
  if P_INCLUDE_CHAR = 0 then
    for i in 1..P_LENGTH loop
      result_char := result_char||floor(dbms_random.value(0,10));
    end loop;
  elsif P_INCLUDE_CHAR = 1 then
    for i in 1..P_LENGTH loop
      result_char := result_char||dbms_random.string('x',1);
    end loop;
   else
     raise_application_error('-20000','只能输入0或1');
   end if;
   return result_char;
end;

declare
begin
  dbms_output.put_line(my_random(5,1));
  end;
  
--3 有一个字符串abcdefg，需要将这个字符串拆分，每个字母占一行来显示
    --先创建一个表类型，这个 is table of 表示它是一个表格，这个表格在此处只能有一个字段
create type ty_string is table of varchar2(1);

--2创建一个自定义函数，这个函数返回的类型就是上边定义的表类型
create or replace function func_split_str(s varchar2)
return ty_string
as
--将表格类型赋值给一个变量，得到一个表格
t ty_string := ty_string(); --必须赋初值 赋值的过程就是在内存中创建一个空表的过程
begin
  for i in 1..length(s) loop
    --因为表格没有任何内容，甚至连行都没有，所有向表格添加数据前要先创建一个空行
    t.extend();
    t(i) := substr(s,i,1); --截取字符串
    end loop;
    return t;
end;
  
3.运行这个表类型函数
--在 from 后面 通过table（）的方式来调用这个函数，当成一个表格来输出结果
select * from table(func_split_str('abcdefg'));