------------------------------------
--用途:添加新数据
-------------------------------------
create or replace procedure jobs_Create(job_desc in VARCHAR2,min_lvl in smallint,max_lvl in smallint) is
begin
insert into jobs (job_desc,min_lvl,max_lvl) values(job_desc,min_lvl,max_lvl);
COMMIT;
EXCEPTION WHEN OTHERS
ROLLBACK;
end jobs_Create;
--用途:添加新数据
-------------------------------------
create or replace procedure jobs_Create(job_desc in VARCHAR2,min_lvl in smallint,max_lvl in smallint) is
begin
insert into jobs (job_desc,min_lvl,max_lvl) values(job_desc,min_lvl,max_lvl);
COMMIT;
EXCEPTION WHEN OTHERS
ROLLBACK;
end jobs_Create;
解决方案 »
- If a single DML statement fails during execution, only that statement is rolled
- Oracle出现异常,报:ORA-07445: 出现异常错误: 核心转储
- update语句
- oracle双机热备坏了,该如何修复,点上系统,兄弟们邦帮下?
- 测试存储过程报错,哪位高手看一下,是什么原因?
- Oracle的Java Stored Procedure
- oracle自动生成ID问题
- oracle的sys用户导致的ORA-12526受限模式的问题
- 为什么我的 Oracle9i里面没发现RMAN该咋办?
- 关于oracle的imp问题(100分)
- 怎样能备份一个orcale方案的所有中的数据和表的结构?
- 如何看Execution Plan
insert into jobs (job_desc,min_lvl,max_lvl) values(:job_desc,:min_lvl,:max_lvl);
--用途:添加新数据
-------------------------------------
CREATE or replace PROCEDURE dbo.jobs_Create(job_desc in varchar2,min_lvl in number,max_lvl in number)
AS
begin
insert into jobs (job_desc,min_lvl,max_lvl) values(job_desc,min_lvl,max_lvl)
commit;
exception
when others then
rollback;
end;
按照这种方式就可以。
--用途:添加新数据
-------------------------------------CREATE OR REPLACE PROCEDURE jobs_Create(job_desc in varchar2,min_lvl in number,max_lvl in number)
as
begin
insert into jobs(job_desc,min_lvl,max_lvl) values(job_desc,min_lvl,max_lvl);
commit;
exception
when others then
rollback;
end jobs_Create;
--用途:添加新数据
-------------------------------------
CREATE PROCEDURE jobs_Create(
job_desc Varchar2,
min_lvl Integer,
max_lvl Integer,
v_result Out Integer
)
AS
begin
insert into jobs (job_desc,min_lvl,max_lvl) values(job_desc,min_lvl,max_lvl);
Commit;
v_result := 1;
Except
When Others ThenRollback;
v_result:=0;
End jobs_Create;------------------------------------
--用途:更新数据
-------------------------------------
CREATE PROCEDURE jobs_Modify(
job_id Integer,
job_desc Varchar2,
min_lvl Integer,
max_lvl Integer,
v_result Out Integer)begin
update jobs set job_desc=job_desc,min_lvl=min_lvl,max_lvl=max_lvl where job_id=job_id;
Commit;
v_result:= 0;
Except
When Others ThenRollback;
v_result:=0;
End jobs_Modify;------------------------------------
--用途:从表中删除数据
-------------------------------------
CREATE PROCEDURE jobs_Delete(
job_id Integer,
v_result Out Integer)
AS
begin
delete from jobs where job_id=job_id;
Commit;
v_result:= 0;
Except
When Others Then
Rollback;
v_result:=0;
End jobs_Delete;
--用途:从表中选择数据
-------------------------------------
CREATE OR REPLACE PACKAGE pack_test
AS
TYPE mycur IS REF CURSOR;
END pack_test;CREATE OR REPLACE PROCEDURE obs_SelectInf
(
job_id IN VARCHAR2,
p_rc OUT pack_test.mycur
) Is
Begin
OPEN p_rc For select * from jobs where job_id=job_id;
End obs_SelectInf;
--用途:从表中检查是否存在符合条件的记录。
-------------------------------------Create Procedure Jobs_Checkexistofjob_Id(Job_Id Integer v_Result Out Integer)
v_temp Integer:=0;
Begin
Select Count(1) Into v_temp From Jobs Where Job_Id = Job_Id;
If v_temp>0 Then
v_Result:=1;
Else
v_Result :=0;
End If;
End Jobs_Checkexistofjob_Id;
------------------------------------
--用途:从表中检查是否存在符合条件的记录。
-------------------------------------Create Procedure Jobs_Checkexistofjob_Id(Job_Id Integer, v_Result Out Integer)
v_temp Integer:=0;
Begin
Select Count(1) Into v_temp From Jobs Where Job_Id = Job_Id;
If v_temp>0 Then
v_Result:=1;
Else
v_Result :=0;
End If;
End Jobs_Checkexistofjob_Id;