plsql.txt

来自「这是一个网上订票系统」· 文本 代码 · 共 44 行

--查看passenger中是否有未出现在ticket表中的id--
SQL> select  passenger.id from passenger where passenger.id not in
(select distinct ticket.id from ticket);

未选定行

--由于passenger中的id，在ticket中都存在，所以我们向passenger中插入以下数据来测试--
SQL>insert into passenger values('8888','王彪','男','广州市天河区','510642','GZ3102');

已创建 1 行。

--查看passenger中是否有未出现在ticket表中的id--
SQL> select  passenger.id from passenger where passenger.id not in
(select distinct ticket.id from ticket);

ID
------------------
8888

已选择 1 行。

SQL>@D:\schoolwork\数据库\第二次实验\plsql.sql;
declare
   iden passenger.id%TYPE;
   cursor T1Cursor is
   select  passenger.id from passenger where passenger.id not in
   (select distinct ticket.id from ticket);
   begin
     open T1Cursor;
     loop
        fetch T1Cursor into iden;
        exit when T1Cursor %NOTFOUND;
        delete from  passenger where id=iden;
     end loop;
     close T1Cursor;
   end;

PL/SQL 过程已成功完成。

--验证--
SQL> select  passenger.id from passenger where passenger.id not in
(select distinct ticket.id from ticket);

未选定行