oracle 存储过程、java对象、包
--oracle不可用if exists，mysql可以
--DROP TABLE IF EXISTS qmcb_ls_2019_3_25;
-- 匿名存储过程
-- declare
create or replace procedure qmcb_km_data
as
num number;
--create_sql varchar2(20000);
v_sql varchar2(1000);
begin
--判断所有的表中是否已经存在这个表
select count(1) into num from all_tables where TABLE_NAME = upper('qmcb_km_2019_3_14') and OWNER=upper('qmcbrt');
if num=1 then
--如果存在，则执行drop table
execute immediate 'drop table qmcbrt.qmcb_km_2019_3_14';
end if;
--新建table，实现全表更新
execute immediate '
create table qmcb_km_2019_3_14 as
select
rownum as row_num,
t1.AAC147,
nvl(t1.AAC004, t2.AAC004) as AAC004,
nvl(t1.AAB301, substr(t2.AAC300, 1, 6)) as AAB301,
t1.AAC060 as AAC060_ZCA1,
case when ((t1.AAC060 is null or t1.AAC060=''8'') AND t1.AAZ299 is null)
AND (t2.AAC060=''1'' AND t2.AAE138 is null) then ''1'' else ''0'' end is_normal,
case when t5.sf is not null then ''1'' else ''0'' end as is_bzsw,
nvl2(t6.AAC002, ''1'', ''0'') as is_dc,
from
(
SELECT
AAC147,
AAC004
FROM
ZCA1 @BEIK_RT
WHERE
AAE100 = ''1''
) t1 left join (
select
sf
from
ZCA2 @BEIK_RT
where
add_months(liur.stringToDate(nvl(sxrq, pjrq)), pxn * 12 + pxy) > sysdate
group by
sf
) t7 on t1.AAC147 = t7.sf
left join(
SELECT
b.AAC002,
b.BAE185
FROM
(
SELECT
a.AAC002,
a.BAE185,
row_number() OVER(PARTITION BY a.AAC002 ORDER BY a.BAZ052 DESC) row_num
FROM
BC12 @BEIK_RT a
where
a.BAZ060 is null or a.BAZ060=''1''
) b
WHERE
b.row_num = 1
) t8 on t1.AAC147 = t8.AAC002';
--execute immediate create_sql;
-- Add comments to the columns
v_sql := 'comment on column QMCB_KM_2019_3_14.row_num is ''行号''';
execute immediate v_sql;
/*v_sql := 'comment on column QMCB_KM_2019_3_14.random_num is ''随机数''';
execute immediate v_sql;*/
-- Create/Recreate indexes
v_sql := 'create index INDEX_QMCBKM_ROW_NUM on QMCB_KM_2019_3_14 (ROW_NUM)
tablespace QMCBRTSJGL_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)';
execute immediate v_sql;
end;
执行存储过程
begin exec 包名.过程名(); end;
insert
create table bigtab (mycol varchar2(20));
begin
for i in 1..20000 loop
insert into bigtab (mycol) values (dbms_random.string('A',20));
end loop;
end;
/
show errors
commit;
execute immediate 拼接/动态传参
v_sql:='select * from tables t where t.c_date='''||v_date||''''; EXECUTE IMMEDIATE v_sql; v_sql:='select * from tables t where t.c_date=:1 and t.name=:2'; EXECUTE IMMEDIATE v_sql USING '20130304','xiaoming';
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
commit;
Returning Into简介
在进行insert、update和delete操作的时候，都可以在末尾加入returning into字句。这字句的作用是将进行DML操作影响到数据行的列值，保存进指定的PL/SQL变量中。使用该字句的效果，与进行insert、update之后执行select into，以及在delete之前进行select into的效果相同。在Oracle官方的PL/SQL指导书中，推荐这种方法进行变量保存，能够最大限度的保证数据的一致。
DML（Data Manipulation Language） :（insert, delete, update, select）
DDL（data definition language）:（create, alter, drop）
参考链接：
