1、判断如果表存在就删除
DECLARE YR CHAR(4);
num number;
tblname char(20);
begin
SELECT TO_CHAR(SYSDATE,'YYYY') INTO YR FROM DUAL;
tblname:='GDZCDB'||YR;
select count(1) into num from user_tables where table_name=tblname;
if num>0 then
execute immediate 'drop table'||tblname;
end if;
end;
2、游标的例子
declare
-- money cms3_simcard.card_fee%type :=0; --定义与表字段相同类型
cursor mycursor is --定义游
select tbyszq_zqnm, tbyszq_zqmc from tbyszq;
my_record mycursor%rowtype; --定义游标记录类型
Counter int :=0;
zqnm tbyszq.tbyszq_zqnm%type;
zqmc tbyszq.tbyszq_zqmc%type;
begin
open mycursor; --打开游标
if mycursor%isopen then --判断打开成功
loop --循环获取记录集
fetch mycursor into zqnm, zqmc; --获取游标中的记录
if mycursor%found then --游标的found属性判断是否有记录
dbms_output.put_line(zqnm||zqmc);
else
exit;
end if;
end loop;
else
dbms_output.put_line('游标没有打开');
end if;
close mycursor;
end;
3、 连接查询
左连接 显示左表employees 所有项信息
select employee_id, first_name||' '||last_name, department_name
from employees
left join departments
using(department_id)
右连接 显示右表departments 所有项信息
select employee_id, first_name||' '||last_name, department_name
from employees
right join departments
using(department_id)
连接查询,显示有信息的所有项信息,null不显示
select employee_id, first_name||' '||last_name, department_name
from employees
join departments
using(department_id)