楼主也太懒了!!随便找本oracle的书都可以知道的问题!! create or replace procedure xiao as i integer; j integer; begin dbms_output.put_line('打印乘法表'); for i in 1..9 loop for j in 1..9 loop if i>=j then dbms_output.put(to_char(j)||'*'||to_char(i)||'='||to_char(i*j)||' '); end if; end loop; dbms_output.put_line(' '); end loop; end; /
这就是一个最简单的带名存储过程 CREATE OR REPLACE PROCEDURE TEST AS BEGIN NULL; END; /
--研究明白了这个基本上创建一个存储过程是不成问题了 create or replace package pkg is type cur is ref cursor; procedure bb( a in int,b out varchar2,Rst out cur); end pkg; / create or replace package body pkg is procedure bb( a in int,b out varchar2,Rst out cur) is begin b:=a; open Rst for select * from table1; end; end pkg;
用pl/sql工具点一下新建,就可以创建了。
我习惯用toad,很好用的,试试。如果网找不到,我可以给你发一个。
http://hexun.com/vbagcl/default.html给我留言,我给你发
create or replace procedure sp_Test(a varchar2,b out varchar2 ) is begin b:=a; end;
create or replace procedure procename( arg_1 in varchar2, arg_2 in varchar2, arg_3 out varchar2 ) is begin arg_3:=arg_1||arg_2; end;
很简单的,下面有个新增商品的小例子,不过在这之前你得自己建表哦CREATE OR REPLACE Package body Product as /* 新增商品关系类型信息 */ --PRODUCT PROCEDURE ProductRelationTypeInsert( v_PKID IN OUT NUMBER , v_FULLNAME VARCHAR2, v_RELATIONANAME VARCHAR2, v_RELATIONBNAME VARCHAR2, v_DESCRIPTION VARCHAR2, v_PARENTID NUMBER, v_VALID CHAR, v_LASTMODIFIER NUMBER, v_LASTMODIFYTIME CHAR, v_IMPORTTIME CHAR, v_EXPORTTIME CHAR ) IS BEGIN INSERT INTO TPRODUCTRELATIONTYPE (PKID,FULLNAME,RELATIONANAME,RELATIONBNAME,DESCRIPTION,PARENTID,VALID,LASTMODIFIER,LASTMODIFYTIME,IMPORTTIME,EXPORTTIME) VALUES ( TPRODUCTRELATIONTYPE_ID.NEXTVAL, v_FULLNAME, v_RELATIONANAME, v_RELATIONBNAME, v_DESCRIPTION, v_PARENTID, v_VALID, v_LASTMODIFIER , TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'), v_IMPORTTIME, v_EXPORTTIME) RETURNING PKID INTO v_PKID; END ProductRelationTypeInsert;
用PL/SQL developer工具创建比较存储过程,选择新建下的存储过程就可以创建了
想怎么创建都可以,弄个PL/sql,新建就有了
估计是出错不会分析,toad的话compile一下会有提示 sqlplus会直接提示的
sqlplus 中编译出错时,可以用SQL> show error显示编译错误。
create or replace PROCEDURE get_transposed( p_a in varchar2 ) return varchar2 is l_str varchar2(2000) default null; l_sep varchar2(1) default null; begin for x in ( select ou_number from super_ou_table where position_number = p_a ) loop l_str := l_str || l_sep || x.ou_number; l_sep := '.'; end loop; return l_str; end;
---------------------例子来了----------------------------------- create or replace procedure SP_GA_RESULT_SETTING ( idataid in varchar2, icoCodeList in varchar2, iruleCodeList in varchar2, excepIdS in varchar2, beginDate in varchar2, endDate in VARCHAR2 ) is vResSettleSqlStr varchar2(4000); replaceExcepIds varchar2(4000); BEGIN
replaceExcepIds := replace(excepIdS,',',''','''); vResSettleSqlStr := 'UPDATE GA_RESULT_LIST LIST SET LIST.STATUS = 5, LIST.MODIFY_TIME = SYSDATE, LIST.RESULT_ID = ' || idataid || ' WHERE LIST.UUID IN (SELECT A.UUID FROM GA_RESULT_LIST A, GL_VOU_HEAD B WHERE A.VOU_NO = B.VOU_NO AND A.VOU_CO_CODE = B.CO_CODE AND A.VOU_FISCAL = B.FISCAL AND A.VOU_ACCOUNT_ID = B.ACCOUNT_ID AND B.VOU_DATE >= TO_DATE(''' || beginDate || ''',''YYYY-MM-DD'') AND B.VOU_DATE <= TO_DATE(''' || endDate || ''',''YYYY-MM-DD'')'; if icoCodeList <> '*' then vResSettleSqlStr := vResSettleSqlStr || ' AND B.CO_CODE IN (''' || icoCodeList || ''')'; end if; if iruleCodeList <> '*' then vResSettleSqlStr := vResSettleSqlStr || ' AND A.RULE_CODE IN (''' || iruleCodeList || ''')'; end if; if excepIdS IS NOT NULL then vResSettleSqlStr := vResSettleSqlStr || ' AND A.RULE_CODE NOT IN(''' || replaceExcepIds || ''')'; end if; vResSettleSqlStr := vResSettleSqlStr || ' AND (SELECT COUNT(*) FROM GA_RESULT_TEMP K WHERE A.VOU_NO = K.VOU_NO AND A.VOU_CO_CODE = K.VOU_CO_CODE AND A.VOU_FISCAL = K.VOU_FISCAL AND A.VOU_ACCOUNT_ID = K.VOU_ACCOUNT_ID AND A.RULE_CODE = K.RULE_CODE AND A.VOU_SEQ = K.VOU_SEQ AND K.RESULT_ID = '|| idataid ||') = 0)'; execute immediate vResSettleSqlStr; commit; end SP_GA_RESULT_SETTING;
这就是一个最简单的带名存储过程 CREATE OR REPLACE PROCEDURE TEST AS BEGIN NULL; END;
pl/sql ->新建->程序窗口->procedure
在sqlserver里用的是 create procedure test as begin...end 在oracle里用的是 create procedure test is begin ...end; 就是把is 和 as 搞混了才创建不出来
create or replace procedure xiao as
i integer;
j integer;
begin
dbms_output.put_line('打印乘法表');
for i in 1..9 loop
for j in 1..9 loop
if i>=j then
dbms_output.put(to_char(j)||'*'||to_char(i)||'='||to_char(i*j)||' ');
end if;
end loop;
dbms_output.put_line(' ');
end loop;
end;
/
CREATE OR REPLACE PROCEDURE TEST
AS
BEGIN
NULL;
END;
/
create or replace package pkg
is
type cur is ref cursor;
procedure bb( a in int,b out varchar2,Rst out cur);
end pkg;
/
create or replace package body pkg
is
procedure bb( a in int,b out varchar2,Rst out cur)
is
begin
b:=a;
open Rst for select * from table1;
end;
end pkg;
begin
b:=a;
end;
arg_1 in varchar2,
arg_2 in varchar2,
arg_3 out varchar2
)
is
begin
arg_3:=arg_1||arg_2;
end;
as
/* 新增商品关系类型信息 */
--PRODUCT
PROCEDURE ProductRelationTypeInsert(
v_PKID IN OUT NUMBER ,
v_FULLNAME VARCHAR2,
v_RELATIONANAME VARCHAR2,
v_RELATIONBNAME VARCHAR2,
v_DESCRIPTION VARCHAR2,
v_PARENTID NUMBER,
v_VALID CHAR,
v_LASTMODIFIER NUMBER,
v_LASTMODIFYTIME CHAR,
v_IMPORTTIME CHAR,
v_EXPORTTIME CHAR
)
IS
BEGIN
INSERT INTO TPRODUCTRELATIONTYPE (PKID,FULLNAME,RELATIONANAME,RELATIONBNAME,DESCRIPTION,PARENTID,VALID,LASTMODIFIER,LASTMODIFYTIME,IMPORTTIME,EXPORTTIME)
VALUES (
TPRODUCTRELATIONTYPE_ID.NEXTVAL,
v_FULLNAME,
v_RELATIONANAME,
v_RELATIONBNAME,
v_DESCRIPTION,
v_PARENTID,
v_VALID,
v_LASTMODIFIER ,
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
v_IMPORTTIME,
v_EXPORTTIME)
RETURNING PKID INTO v_PKID;
END ProductRelationTypeInsert;
sqlplus会直接提示的
return varchar2
is
l_str varchar2(2000) default null;
l_sep varchar2(1) default null;
begin
for x in ( select ou_number from super_ou_table where position_number = p_a ) loop
l_str := l_str || l_sep || x.ou_number;
l_sep := '.';
end loop;
return l_str;
end;
create or replace procedure SP_GA_RESULT_SETTING
(
idataid in varchar2,
icoCodeList in varchar2,
iruleCodeList in varchar2,
excepIdS in varchar2,
beginDate in varchar2,
endDate in VARCHAR2
) is vResSettleSqlStr varchar2(4000);
replaceExcepIds varchar2(4000);
BEGIN
replaceExcepIds := replace(excepIdS,',',''',''');
vResSettleSqlStr := 'UPDATE GA_RESULT_LIST LIST
SET LIST.STATUS = 5, LIST.MODIFY_TIME = SYSDATE, LIST.RESULT_ID = ' || idataid || '
WHERE LIST.UUID IN
(SELECT A.UUID
FROM GA_RESULT_LIST A, GL_VOU_HEAD B
WHERE A.VOU_NO = B.VOU_NO
AND A.VOU_CO_CODE = B.CO_CODE
AND A.VOU_FISCAL = B.FISCAL
AND A.VOU_ACCOUNT_ID = B.ACCOUNT_ID
AND B.VOU_DATE >= TO_DATE(''' || beginDate || ''',''YYYY-MM-DD'')
AND B.VOU_DATE <= TO_DATE(''' || endDate || ''',''YYYY-MM-DD'')';
if icoCodeList <> '*' then
vResSettleSqlStr := vResSettleSqlStr || ' AND B.CO_CODE IN (''' || icoCodeList || ''')';
end if;
if iruleCodeList <> '*' then
vResSettleSqlStr := vResSettleSqlStr || ' AND A.RULE_CODE IN (''' || iruleCodeList || ''')';
end if;
if excepIdS IS NOT NULL then
vResSettleSqlStr := vResSettleSqlStr || ' AND A.RULE_CODE NOT IN(''' || replaceExcepIds || ''')';
end if;
vResSettleSqlStr := vResSettleSqlStr || ' AND (SELECT COUNT(*) FROM GA_RESULT_TEMP K
WHERE A.VOU_NO = K.VOU_NO
AND A.VOU_CO_CODE = K.VOU_CO_CODE
AND A.VOU_FISCAL = K.VOU_FISCAL
AND A.VOU_ACCOUNT_ID = K.VOU_ACCOUNT_ID
AND A.RULE_CODE = K.RULE_CODE
AND A.VOU_SEQ = K.VOU_SEQ
AND K.RESULT_ID = '|| idataid ||') = 0)';
execute immediate vResSettleSqlStr;
commit;
end SP_GA_RESULT_SETTING;
CREATE OR REPLACE PROCEDURE TEST
AS
BEGIN
NULL;
END;
在oracle里用的是 create procedure test is begin ...end;
就是把is 和 as 搞混了才创建不出来