homework.txt

数据库综合编程实现产生车牌号 数据库综合编程实现产生车牌号

create table auto(auto_num varchar2(5) primary key);

insert into auto values('12345');
insert into auto values('12344');
insert into auto values('14565');
insert into auto values('44444');
insert into auto values('45654');
insert into auto values('12345');
insert into auto values('12355');
insert into auto values('12348');




set serveroutput on;

CREATE OR REPLACE PROCEDURE p_auto(num1 in char) 
IS
BEGIN
  declare
   p_auto_num auto.auto_num%type;

   cursor mycour1 is  
    select auto_num from auto;
  BEGIN 
      open mycour1;
       fetch mycour1 into p_auto_num;
    while mycour1%found
        loop
         if(p_auto_num like '44444')then 
           dbms_output.put_line('产生的车牌号是: 湘A_'||num1||'44444');
            elsif(p_auto_num like '%4')then
                   null;
               else 
                   dbms_output.put_line('产生的车牌号是: 湘A_'||num1||p_auto_num);
          end if;         
          fetch mycour1 into p_auto_num;           
       end loop;
     close mycour1;
  END;
END p_auto;
/


declare 
auto_num1 char(1);
auto_num2 char(1);
begin
  auto_num1:=&auto_num2;
  if(auto_num1 not between 'A' and 'Z')then
    raise_application_error(-20000,'输入的字母必须在A和Z之间!');
  end if;
     if(auto_num1='O')then
       raise_application_error(-20001,'输入字母不能以O开头!');
     end if;
p_auto(auto_num1);
end;
/








CREATE OR REPLACE PROCEDURE p_auto(num1 in char) 
IS
BEGIN
  declare
   p_auto_num auto.auto_num%type;

   cursor mycour1 is  
    select auto_num from auto;
  BEGIN 
      if(num1 not between 'A' and 'Z')then
    raise_application_error(-20000,'输入的字母必须在A和Z之间!');
  end if;
     if(num1='O')then
       raise_application_error(-20001,'输入字母不能以O开头!');
     end if;
      open mycour1;
       fetch mycour1 into p_auto_num;
    while mycour1%found
        loop
         if(p_auto_num like '44444')then 
           dbms_output.put_line('产生的车牌号是: 湘A_'||num1||'44444');
            elsif(p_auto_num like '%4')then
                   null;
               else 
                   dbms_output.put_line('产生的车牌号是: 湘A_'||num1||p_auto_num);
          end if;         
          fetch mycour1 into p_auto_num;           
       end loop;
     close mycour1;
  END;
END p_auto;
/




CREATE OR REPLACE PROCEDURE p_cph(num1 in char) 
IS
BEGIN
  declare
	cursor together is 
	select auto_num as num  from auto;
begin
dbms_output.enable;
if(num1 not between 'A' and 'Z')then
    raise_application_error(-20000,'输入的字母必须在A和Z之间!');
  end if;
     if(num1='O')then
       raise_application_error(-20001,'输入字母不能以O开头!');
     end if;
for together_1_rec in together loop
EXIT WHEN together%NOTFOUND;
if(together_1_rec.num like '44444')then 
           dbms_output.put_line('产生的车牌号是: 湘A_'||num1||'44444');
            elsif(together_1_rec.num like '%4')then
                   null;
               else 
                   dbms_output.put_line('产生的车牌号是: 湘A_'||num1||together_1_rec.num);
          end if;  
end loop;
end together;

END p_cph;
/