oracle笔记7--存储过程触发器

--1．  创建用户kaifa（密码亦为kaifa），并分配connect,create table,resource权限
create user kaifa identified by kaifa;
grant connect ,create table ,resource to kaifa

--2
create table CCB_GYB
(
  ACCOUNTING_DATE DATE,
  RMB_YTD_BALANCE NUMBER,
  CNY_YTD_BALANCE NUMBER,
  USD_YTD_BALANCE NUMBER
)
create unique index CCB_GYB_IDX on CCB_GYB (ACCOUNTING_DATE)

create or replace function GetCurBal(grp_rq in varchar2,grp_code in varchar2)
return varchar2
as
res number;
begin
  if grp_code='RMB' then
    select RMB_YTD_BALANCE into res from CCB_GYB where ACCOUNTING_DATE=grp_rq;
    return res;
    elsif grp_code='CNY' then
    select CNY_YTD_BALANCE into res from CCB_GYB where ACCOUNTING_DATE=grp_rq;
    return res;
     elsif grp_code='USD' then
    select USD_YTD_BALANCE into res from CCB_GYB where ACCOUNTING_DATE=grp_rq;
    return res;
    else
      res :=0;
      return res;
      end if;
      end;
      
      declare
      a varchar2(20);
      begin
        a:=GetCurBal('sysdate','ere');
        dbms_output.put_line('余额为：'||a);
        end;
--3
   create table student(
sname varchar2(10),
scourse varchar2(10),
schengji number
);
insert into student values ('xiaowang','yuwen',90)
insert into student values ('xiaowang','shuxue',80)
insert into student values ('xiaowang','yingyu',70)
insert into student values ('xiaowang','huaxue',50)
insert into student values ('xiaowang','tiyu',65)
insert into student values ('xiaowang','lishi',95)
insert into student values ('xiaowang','lishi',55)
--查询统计分段分数
select (case when schengji between 0 and 60 then '0-60'
when schengji between 61 and 70 then '61-70'
else '81-100'  end) as cases, count(1)  as 人数
from student
group by case when schengji between 0 and 60 then '0-60'
when schengji between 61 and 70 then '61-70'
else '81-100' end
order by count(*)

select sname as 姓名,
max(case scourse when 'yuwen' then schengji else 0 end)  as 语文,
max(case scourse when 'shuxue' then schengji else 0 end)  as 数学,
max(case scourse when 'yingyu' then schengji else 0 end)  as 英语,
max(case scourse when 'lishi' then schengji else 0 end)  as 历史,
max(case scourse when 'huaxue' then schengji else 0 end)  as 化学,
max(case scourse when 'tiyu' then schengji else 0 end)  as 体育 from student group by sname;


--

create or replace procedure table_frame(v_partition_status varchar2 default 'Y')
is
   type column_type is table of  user_tab_columns.column_name%type;
   v_column column_type;
   type data_type is table of  user_tab_columns.data_type%type;
   v_type data_type;
   type length_type is table of  user_tab_columns.data_length%type;
   v_length length_type;
   type datapre_type is table of  user_tab_columns.DATA_PRECISION%type;
   v_ldatapre datapre_type;
   type datasca_type is table of  user_tab_columns.DATA_SCALE%type;
   v_dayasca datasca_type;
   v_str clob;
   file_name UTL_FILE.file_type;
   v_tables varchar2(50);
   partition_status varchar2(3);
   partition_keywords varchar2(30);
   TYPE part_cursor is ref CURSOR;
   part_name part_cursor;
   partition_name user_tab_partitions.partition_name%type;
   high_value user_tab_partitions.high_value%type;
begin
  file_name := UTL_FILE.FOPEN('DIR_DUMP','table.txt','w');
  --判断是否需要分区
  partition_status := v_partition_status;
  --按表循环
  for j in (select table_name  from user_tables  group by table_name ) loop
   v_tables :=upper(j.table_name);
   v_str := 'create table '||v_tables||'(';
   UTL_FILE.PUT_LINE(file_name,v_str);
   --提取表的字段信息
   select column_name,data_type,data_length,DATA_PRECISION,DATA_SCALE
    bulk collect into v_column,v_type,v_length,v_ldatapre,v_dayasca
      from user_tab_columns where table_name=v_tables;
    --按字段循环
    for i in 1..v_column.count loop
      if v_type(i)= 'DATE' or v_type(i) like 'TIMESTAMP%'  then
        v_str :=v_column(i)||' '||v_type(i)||',';
      elsif v_type(i)= 'NUMBER' and v_ldatapre(i) is not null  then
        v_str :=v_column(i)||' '||v_type(i)||'('||v_ldatapre(i)||','||v_dayasca(i)||'),';
      elsif v_type(i)= 'NUMBER' and v_ldatapre(i) is  null  then
        v_str :=v_column(i)||' '||v_type(i)||',';
      else
        v_str :=v_column(i)||' '||v_type(i)||'('||v_length(i)||'),';
      end if;     
      if i=v_column.count then
        v_str :=substr(v_str,1,length(v_str)-1);
      end if;
      UTL_FILE.PUT_LINE(file_name,v_str);      
    end loop;
    --判断是否添加分区
    if partition_status = 'Y' then          
     SELECT nvl(max(column_name),'0') into partition_keywords  FROM USER_PART_KEY_COLUMNS
      where object_type = 'TABLE'  and name=v_tables;
      if partition_keywords != '0' then
         UTL_FILE.PUT_LINE(file_name,')partition by range ('||partition_keywords||')(');        
         open part_name for select partition_name,high_value  from user_tab_partitions
         where table_name = v_tables;
         v_str := null;
         loop
           fetch part_name into partition_name,high_value;             
           if part_name%notfound then
             --去掉最后逗号
             v_str :=substr(v_str,1,length(v_str)-1);
             UTL_FILE.PUT_LINE(file_name,v_str);
             exit;
           end if;          
           UTL_FILE.PUT_LINE(file_name,v_str);
           v_str :='partition '||partition_name||' values less than ('||high_value||'),';
         end loop;
      end if;
    end if;
    UTL_FILE.PUT_LINE(file_name,');');
    UTL_FILE.PUT_LINE(file_name,'-------------------------------------------------------------');
    end loop;  
    UTL_FILE.fclose_all;

drop table call_book_info
select * from call_book_info
insert into call_book_info values('12355656565',sysdate,sysdate,1)

----------5
create or replace procedure deletephone
is
    v_exists number;   
begin--
select count(*) into v_exists from user_tables where table_name = 'TEST1';   
  if v_exists > 0 then   
  execute immediate 'drop table TEST1';   
  end if;   
execute immediate '  
create table test1(
mobile_id varchar2(12),
callin_time date,
callout_time date,
status char(1)
)';

insert into test1(mobile_id,callin_time,callout_time,
status)
select * from Call_book_info;
commit;
delete from Call_book_info where mobile_id
not in (select min(mobile_id) from call_book_info);
end;
/