1.创建包
create or replace package packagename is
procedure prcedurename(vars varchar2);
end packagename;
2 创建包体
create or repalce package body packagename is
procedure prcedurename(vars in varchar2) is
begin
。。。。。。
end prcedurename;
end packagename ;
3基础语法
(1)if
if 【条件】 then
elseif 【条件】 then
else then
end if;
(2)for循环(也可以用游标 )
for i in 1..27 loop
.......
end loop;
(3) 表迁移
insert into tablename1 select from tablename2;
(4) merge into 语法,有就更新,没有就插入
merge into Tablename1 a using
Tablename2 b
on a.id=b.id
when matched then update set a.xx=b.xx
when not matched then insert (a.x,a.xx) values(b.x,b.xx);
语法
MERGE INTO [your table-name] [rename your table here] USING ( [write your query here] )[rename your query-sql and using just like a table] ON ([conditional expression here] AND [...]...) WHEN MATHED THEN [here you can execute some update sql or something else ] WHEN NOT MATHED THEN [execute something else here ! ]
(5) 给字段赋值
icount number;
select count(*) into icount from emp where sal>isal and job=sjob;
(6) in输入参数,out输出参数
(7) 游标的定义
CREATE OR REPLACE PROCEDURE EMPLIST
as
CURSOR emp_cursor IS
SELECT f_pid,f_pname,f_proce FROM t_part;
CURSOR emp_cursor2 IS
SELECT f_pid,f_pname,f_proce FROM t_part;
BEGIN
FOR Emp_record IN emp_cursor LOOP
FOR Emp_record2 IN emp_cursor2 LOOP
(8)调用存储过程
1、SQL>exec proc_emp('参数1','参数2');//无返回值过程调用
2、SQL>var vsal number
SQL> exec proc_emp ('参数1',:vsal);// 有返回值过程调用
或者:call proc_emp ('参数1',:vsal);// 有返回值过程调用
if(Emp_record.f_proce+Emp_record2.f_proce<25) then
DBMS_OUTPUT.PUT_LINE(Emp_record.f_proce||'|'||Emp_record2.f_proce);
end if;
END LOOP;
END LOOP;
END;
(9)异常
exception
when others then
raise_application_error(1111, sqlerrm);