100分求一些小的，正确的存储过程（insert,update和delete)

[rocker1218]

100分求一些小的，正确的存储过程（insert,update和delete)
写者即有分！

[jiezhi]

DECLARE
v_StudentID students.id%TYPE;
BEGIN
-- Retrieve a new student ID number
SELECT student_sequence.NEXTVAL
INTO v_StudentID
FROM dual;

-- Add a row to the students table
INSERT INTO students (id, first_name, last_name)
VALUES (v_StudentID, 'Timothy', 'Taller');

-- Add a second row, but use the sequence number directly
-- in the INSERT statement.
INSERT INTO students (id, first_name, last_name)
VALUES (student_sequence.NEXTVAL, 'Patrick', 'Poll');
END;
/

------------------
DECLARE
v_Major students.major%TYPE;
v_CreditIncrease NUMBER := 3;
BEGIN
-- This UPDATE statement will add 3 to the current_credits
-- field of all students who are majoring in History.
v_Major := 'History';
UPDATE students
SET current_credits = current_credits + v_CreditIncrease
WHERE major = V_Major;
END;
/

-----------------------------
DECLARE
v_StudentCutoff NUMBER;
BEGIN
v_StudentCutoff := 10;
-- Delete any classes which don't have enough students registered.
DELETE FROM classes
WHERE current_students < v_StudentCutoff;

-- Delete any Economics students who don't have any credits yet.
DELETE FROM students
WHERE current_credits = 0
AND major = 'Economics';
END;
/

[jiezhi]

DECLARE
v_StudentRecord students%ROWTYPE;
v_Department classes.department%TYPE;
v_Course classes.course%TYPE;
BEGIN
-- Retrieve one record from the students table, and store it
-- in v_StudentRecord. Note that the WHERE clause will only
-- match one row in the table.
-- Note also that the query is returning all of the fields in
-- the students table (since we are selecting *). Thus the
-- record into which we fetch is defined as students%ROWTYPE.
SELECT *
INTO v_StudentRecord
FROM students
WHERE id = 10000;

-- Retrieve two fields from the classes table, and store them
-- in v_Department and v_Course. Again, the WHERE clause will
-- only match one row in the table.
SELECT department, course
INTO v_Department, v_Course
FROM classes
WHERE room_id = 99997;
END;
/

[dinya2003]

create or replace procedure pro_name(yourname varchar2(20)) as
begin
insert into table_user(id,name) values(table_user.nextval,'yourname');
commit;
end;

create or replace procedure pro_name(yourname varchar2(20)) as
begin
update table_user a set a.user_name=yourname where a.user_id=1;
commit;
end;

create or replace procedure pro_name(yourname varchar2(20)) as
begin
delete from table_user a where a.user_name=yourname;
commit;
end;

--上面三个是最简单的存储过程了,

[txlicenhe]

Oracle9i测试通过。

create or replace procedure sp_inv_sl(v_sl in out numeric, v_flag in numeric :=0)
as
begin
if v_flag = -1 then
v_sl := -v_sl;
else
v_sl := v_sl;
end if;
update erp.inv_m set inv_kcsl = inv_kcsl + v_sl;
end;

--调用:
declare sl numeric(10) ;
begin
sl :=10;
sp_inv_sl(sl,-1);
end;

[rocker1218]

原来oracle和sql server 竟然有那么大的差别。

[rocker1218]

各位能给解释一下吗/我有的地方不是太明白！

[rocker1218]

楼上的各位老大们，实在是让小弟不得不佩服，
可是我是一名新手，只是想简单了解一下ORACLE的存储过程是怎么写的，
我这里有个表，请各位针对我的这个小型表结构，写下他的存储过程。
名称 空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(19)
SEX VARCHAR2(3)
ADDRESS VARCHAR2(12)

[leborety]

写过程？你要说想干什么才写吧~

[rocker1218]

我只是用来学习 。

[bzszp]

随编写了一个
sql*plus里面执行

create or replace procedure p_demo as
v_addr varchar2(12);
begin
select address into v_addr from tbname where id='1';
dbms_output.put_line(v_addr);
exception when others then
dbms_output.put_line('sql error!');
end p_demo;
/
功能：找到id=1的address，显示出来
set serveroutput on;
exec p_demo;

[aegir]

混分
declare
begin
dbms_output.put_line('test');
end;

[bzszp]

声明变量的区别：
sql server:
declare
@v_str char(20);
...
oracle:
在create or replace procedure p_name as 后面声明
v_str varchar2(20);
...

[CodeMagic]

DECLARE
pe_ratio NUMBER(3,1);
BEGIN
SELECT price / earnings INTO pe_ratio FROM stocks
WHERE symbol = ’XYZ’; -- might cause division-by-zero error
INSERT INTO stats (symbol, ratio) VALUES (’XYZ’, pe_ratio);
COMMIT;
EXCEPTION -- exception handlers begin
WHEN ZERO_DIVIDE THEN -- handles ’division by zero’ error
INSERT INTO stats (symbol, ratio) VALUES (’XYZ’, NULL);
COMMIT;
...
WHEN OTHERS THEN -- handles all other errors
ROLLBACK;
END; -- exception handlers and block end here

[freddy2003]

CREATE OR REPLACE Procedure Cljs_Main(v_time In date) Is
/******************************************************************************
PURPOSE: 潮流计算主程序

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2003-10-17 Fred Zhang 1. Created this procedure.
******************************************************************************/
TGS1 Number;--线损
TGS2 Number; --线损
Type XLBH_type Is Table Of Tcalculate.XLBH%Type Index By Binary_Integer;
XLBH1 XLBH_type;--待计算的线路
max_layer Integer; --某一线路的最大层数
min_volt Number; --最小电压
cal_lp Integer; --计算的循环
Begin
/*读入一笔电表记录的单位电量(15分钟),并经过计算倍率折算*/
Update Tcalculate Set (SCYG,SCWG,JSDY,JSSJ)=
(Select 1000000*YGGL*JSBL,1000000*WGGL*JSBL,(UA+UB+UC)/decode((decode(UA,0,0,1)+decode(UB,0,0,1)+decode(UC,0,0,1)),0,3, (decode(UA,0,0,1)+decode(UB,0,0,1)+decode(UC,0,0,1))),CBRQ From Tameter_Rt Where Tcalculate.DBBH=Tameter_Rt.dbbh And Tameter_Rt.CBRQ=v_time);
Commit;
/*设定各元件的计算电压为出线开关的电压与计算时间*/
Select Distinct XLBH Bulk Collect Into XLBH1 From Tcalculate;
For i In 1..XLBH1.Count Loop
Update Tcalculate Set (JSDY,JSSJ)=(Select decode(JSDY,0,10500,JSDY),JSSJ From Tcalculate Where XLBH=XLBH1(i) And YJLX='出线开关')
Where XLBH=XLBH1(i);
Commit;
End Loop;
/*将高供低计的电压折算到高压侧*/
update tcalculate set JSDY=JSDY*JSBL where JLFS='高供低计' and JSDY<400;
/*将高供低计单位电量(15分钟)折算到高压侧*/
Update Tcalculate Set SCYG=SCYG+TGS+TBS*Power((Power(SCYG,2)+Power(SCWG,2)),1/2) Where JLFS='高供低计';
/*计算出各个用户的有功功率与无功功率*/
update tcalculate set SCYG=SCYG*4,SCWG=SCWG*4 where YJLX like '%变压器' or YJLX like '出线开关';
/*将此15分钟内功率为0且有两个联络开关的线路中的一个联络开关设为工作状态*/
Update Tcalculate Set kgzt=1 Where YJLX = '联络开关' And xlbh In
(Select tmp.xlbh From (Select xlbh,Count(*) As cnt From Tcalculate Where xlbh In (Select xlbh From Tcalculate Where Power(SCYG,2)+Power(SCWG,2)=0) And YJLX = '联络开关' Group By xlbh)tmp Where tmp.cnt=2);
Commit;
/*将其中的第二个联络开关设为非工作状态*/
Update Tcalculate Set kgzt=Null Where YJBH=
(Select tmp2.YJBH From(Select tmp.YJBH,tmp.rd From (Select Rowid As rd, Rownum As rm,YJBH From Tcalculate Where kgzt=1 Order By xlbh) tmp Where tmp.rm/2-Trunc(tmp.rm/2)=0) tmp2 Where Tcalculate.Rowid=tmp2.rd);
Commit;
Select XLBH Bulk Collect Into XLBH1 From Tcalculate Where KGZT=1;
For i In 1..XLBH1.Count Loop
/*计算线路的结构编码*/
Jgbm_Calc(XLBH1(i));
/*计算各层输入，输出有功功率与无功功率*/
Cljs_Curt(XLBH1(i));
/*计算此线路的线损*/
Update Tcalculate Set TGS=SRYG-SCYG Where YJLX Like '线路段' and xlbh=XLBH1(i);
commit;
/*将此线路的线损线保存*/
Select Sum(TGS) Into TGS1 From Tcalculate Where xlbh=XLBH1(i) And YJLX='线路段';
/*将其中的第一个联络开关设为非工作状态，另一个开关设为工作状态*/
Update Tcalculate Set kgzt=Decode(kgzt,Null,1,1,Null)
Where xlbh=XLBH1(i) And YJLX='联络开关' ;
Commit;
/*计算线路的结构编码*/
Jgbm_Calc(XLBH1(i));
/*计算各层输入，输出有功功率与无功功率*/
Cljs_Curt(XLBH1(i));
/*计算此线路的线损*/
Update Tcalculate Set TGS=SRYG-SCYG Where YJLX Like '线路段' and xlbh=XLBH1(i);
commit;
/*将此线路的线损线保存*/
Select Sum(TGS) Into TGS2 From Tcalculate Where xlbh=XLBH1(i) And YJLX='线路段';
If TGS2>TGS1 Then
Update Tcalculate Set kgzt=Decode(kgzt,Null,1,1,Null)
Where xlbh=XLBH1(i) And YJLX='联络开关' ;
Commit;
End If;
End Loop;
/*将此15分钟内功率为0且只有一个联络开关的线路的联络开关设为工作状态*/
Update Tcalculate Set kgzt=1 Where YJLX = '联络开关' And xlbh In
(Select tmp.xlbh From (Select xlbh,Count(*) As cnt From Tcalculate Where xlbh In (Select xlbh From Tcalculate Where Power(SCYG,2)+Power(SCWG,2)=0) And YJLX = '联络开关' Group By xlbh)tmp Where tmp.cnt=1);
Commit;
/*将此15分钟内功率不为0的出线开关的开关状态设为工作状态*/
Update Tcalculate Set kgzt=1 Where Power(SCYG,2)+Power(SCWG,2)<>0 And YJLX Like '出线开关';
Commit;
/*将所有的线路结构编码*/
Select XLBH Bulk Collect Into XLBH1 From Tcalculate Where KGZT=1;
For i In 1..XLBH1.Count Loop
Jgbm_Calc(XLBH1(i));
Commit;
End Loop;
cal_lp:=1;
/*设定计算电压*/
--Update Tcalculate Set JSDY=10000;
---Commit;
/*以上程序为工作开关的确定，以下程序为正式计算*/
Loop
Update Tcalculate Set SRYG=Null,SRWG=Null;
Update Tcalculate Set SCYG=Null,SCWG=Null Where YJLX='线路段';
Commit;
/*计算由联络开关工作的线路的功率与电流*/
Select XLBH Bulk Collect Into XLBH1 From Tcalculate Where YJLX='联络开关' And KGZT=1;
For i In 1..XLBH1.Count Loop
Cljs_Curt(XLBH1(i));
Update Tcalculate Set (SCYG,SCWG)=(Select SRYG,SRWG From Tcalculate Where xlbh=XLBH1(i) And KGZT=1)
Where YJLX='联络开关' And YJBH In (Select YJBH From Tcalculate Where xlbh=XLBH1(i) And KGZT=1);
Commit;
End Loop;
/*计算由出线开关工作的线路的功率与电流*/
Select XLBH Bulk Collect Into XLBH1 From Tcalculate Where YJLX='出线开关' And KGZT=1;
For i In 1..XLBH1.Count Loop
/*设定计算电压*/
Update Tcalculate Set JSDY=(Select JSDY From Tcalculate Where XLBH=XLBH1(i) And KGZT=1)
Where XLBH=XLBH1(i);
Commit;
Cljs_Curt(XLBH1(i));
End Loop;
/*计算由出线开关工作的线路的电压*/
Select XLBH Bulk Collect Into XLBH1 From Tcalculate Where YJLX='出线开关' And KGZT=1;
For v In 1..XLBH1.Count Loop
Cljs_Volt(XLBH1(v));
End Loop;
/*计算由联络开关工作的线路的电压*/
Select XLBH Bulk Collect Into XLBH1 From Tcalculate Where YJLX='联络开关' And KGZT=1;
For v In 1..XLBH1.Count Loop
Cljs_Volt(XLBH1(v));
End Loop;
Select Min(JSDY) Into min_volt From Tcalculate;
cal_lp:=cal_lp+1;
Exit When min_volt>9980 Or cal_lp>2;
End Loop;
/*计算所有线路的线损*/
Update Tcalculate Set TGS=SRYG-SCYG,
TBS=SRWG-SCWG
Where YJLX Like '线路段';
Commit;
/*线路运行数据表中数据不重复*/
Delete From Tcircuitry_Run
Where (JSSJ) In (Select Distinct JSSJ From Tcalculate);
/*将计算的结果放在运行表中*/
Insert Into Tcircuitry_Run (XLBH,YJLX, YJBH, SRYG, SRWG, SCYG, SCWG, JSDY, TGS, TBS, JSDL, JSSJ)
Select XLBH,YJLX, YJBH, SRYG, SRWG, SCYG, SCWG, JSDY, TGS, TBS, JSDL, JSSJ From Tcalculate
Where YJLX<>'虚节点' and XLBH in
(select tmp.XLBH from (select sum(SCYG) su,XLBH from tcalculate group by XLBH) tmp where tmp.su<>0);
Commit;

Update Tcalculate Set KGZT=Null, SRYG=Null,SCWG=Null,SRWG=Null,SCYG=Null,JSDY=Null,JSDL=Null,JGBM=Null,JSSJ=Null;
Commit;

End Cljs_Main;
/

[bzszp]

哪些地方不明白写出来

[cl_oracle]

mark

[qiaozhiwei]

设你的表名为tbname

create or replace procedure test
(
p_type in number, --操作类型 0:插入一条记录 1:选择 2:更新
p_result out number --执行结果 0 正确执行
)
as
v_name tbname.name%type; --定义一个变量类型和表中name相同
begin
if p_type = 0 then
insert into tbname (ID,NAME,SEX,ADDRESS) values (1,'john','f','china');
elsif p_type = 1 then
select NAME into v_name from tbname where ID = 1;
dbms_output.put_line('当id = 1 时,name ='||v_name); -- 打印出name的值
elsif p_type = 2 then
update tbname set name = 'jack' where ID = 1;
else
dbms_output.put_line('参数传递错误!');
end if;
p_result := 0;
dbms_output.put_line('正确执行!');
exception --异常处理
when others then
p_result := 1;
dbms_output.put_line(sqlerrm); --打印出错时的错误代码

end test;

[qiaozhiwei]

在sqlplus中调用:
set serverout on;
set timing on;
declare
result number;
begin
exec test(1,result);
end;