sqlserver2000函數如何轉換成oracle9i函數？

[Andy__Huang]

如題：
CREATE TABLE dbo.Dept(
id int identity(1,1),
Parent numeric(2),
Name varchar(1),
Type numeric(1)
)

INSERT INTO Dept(Parent,Name,Type)
select 0,'A',1
union all select 1,'B',2
union all select 1,'C',2
union all select 2,'D',3
union all select 2,'E',3
union all select 2,'F',3
union all select 3,'G',2
union all select 7,'H',4
union all select 8,'I',4

SELECT * FROM Dept

-- drop function dbo.fn_string
create function dbo.fn_string(@id numeric(2))
returns varchar(100)
as
begin
declare @s1 varchar(100)
declare @parent1 numeric(2),@name1 varchar(1),@type1 numeric(2)
set @s1=''
select @parent1=parent,@name1=name,@type1=type from Dept where id=@id
if @type1<=2
begin
select @s1=@name1
end
else
begin
select @s1=dbo.fn_string(@parent1)+'\'+@name1
end
return(@s1)
end

SELECT ID,Name,FullPath=dbo.fn_string(id) FROM Dept
ORDER BY ID

這段sqlserver2000函數如何轉換成oracle9i函數？並且如何調用？

謝謝!

[Andy__Huang]

修一點點

create or replace function fn_string(p_id integer)
return varchar2
is
......

這樣就OK了
謝謝!

[icedut]

CREATE TABLE Dept(
id integer,
Parentid integer,
Name varchar2(1),
Type integer
)


identity(1,1), 在oracle要用序列


zt
Oracle里没有标识列这种说法的，只有序列；

在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。
1、Create Sequence
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限，
CREATE SEQUENCE emp_sequence
INCREMENT BY 1　 -- 每次加几个
START WITH 1　　 -- 从1开始计数
NOMAXVALUE　　　 -- 不设置最大值
NOCYCLE　　　　　-- 一直累加，不循环
CACHE 10; --缓存序列个数，有助于提高效率，但可能造成跳号。

一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL
CURRVAL=返回 sequence的当前值
NEXTVAL=增加sequence的值，然后返回 sequence 值
比如：
emp_sequence.CURRVAL
emp_sequence.NEXTVAL

可以使用sequence的地方：
- 不包含子查询、snapshot、VIEW的 SELECT 语句
- INSERT语句的子查询中
- NSERT语句的VALUES中
- UPDATE 的 SET中

可以看如下例子：
INSERT INTO emp VALUES
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);

SELECT empseq.currval FROM DUAL;

但是要注意的是：
- 第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白？

- 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。

2、Alter Sequence
你或者是该sequence的owner，或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须 drop sequence 再 re-create .
Alter sequence 的例子
ALTER SEQUENCE emp_sequence
　　INCREMENT BY 10
　　MAXVALUE 10000
　　CYCLE 　　-- 到10000后从头开始
　　NOCACHE ;


影响Sequence的初始化参数：
SEQUENCE_CACHE_ENTRIES =设置能同时被cache的sequence数目。

可以很简单的Drop Sequence
DROP SEQUENCE order_seq;


3 变通方法设置序列的初始值（通过设置INCREMENT BY参数 ）
--设置pickup_no的初始值
CREATE SEQUENCE S_T_PICKUP_NO START WITH 1 MAXVALUE 999999
MINVALUE 1 NOCYCLE NOCACHE NOORDER;
SELECT S_T_PICKUP_NO.NEXTVAL FROM dual;
--修改序列的初始值为0
PROCEDURE restart_pickup_no
IS
v_seq VARCHAR2 (200);
v_lastvalue INTEGER;
v_nextvalue INTEGER;
v_sql VARCHAR2 (200);
BEGIN
v_seq := 's_t_pickup_no';

EXECUTE IMMEDIATE 'select ' || v_seq || '.nextval from dual'
INTO v_nextvalue;

--DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_nextvalue));
v_sql :=
'ALTER SEQUENCE '
|| v_seq
|| ' INCREMENT BY -'
|| TO_CHAR (v_nextvalue)
|| ' MINVALUE -999999 NOCACHE';

--DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_sql));
EXECUTE IMMEDIATE v_sql;

--EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||v_seq|| ' INCREMENT BY -' ||TO_CHAR(v_nextvalue) ||' MINVALUE -999999 NOCACHE';
EXECUTE IMMEDIATE 'select ' || v_seq || '.nextval from dual'
INTO v_nextvalue;

--DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_nextvalue));
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || v_seq
|| ' INCREMENT BY 1 NOCACHE';

LOOP
EXECUTE IMMEDIATE 'select ' || v_seq || '.CURRVAL from dual'
INTO v_lastvalue;

--DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_lastvalue));
EXIT WHEN v_lastvalue >= 0;

EXECUTE IMMEDIATE 'select ' || v_seq || '.NEXTVAL from dual'
INTO v_lastvalue;
END LOOP;
END restart_pickup_no;

[icedut]

select 0,'A',1
---select 0,'A',1 from dual --oracle用法


create or replace function fn_string(p_id integer)
returns varchar2(100)
as
v_s1 varchar(100);
v_parent1 integer;
v_name1 varchar2(1);
v_type1 integer;
begin

v_s1:='';
for rec in (select * from Dept where id=p_id)
loop
v_parent1:=rec.parent;
v_name1:=rec.name;
v_type1:=rec.type ;
end loop;
if v_type1<=2 then
v_s1:=v_name1;
else
select fn_string(v_parent1)||'\'||v_name1 into v_s1 from dual;
end if;
return v_s1;
end;

[icedut]

oracle中不用@
oracle中句子结束一般用;

if then
...
else
...
end if;

[Andy__Huang]

先谢谢icedut(冰),

让我试试看!

[Andy__Huang]

TO:icedut(冰)

SqlServer我用下面语句可以调用函数：
SELECT ID,Name,FullPath=dbo.fn_string(id) FROM Dept
ORDER BY ID

在Oracle9i中，我想得到上面结果，如果调用函数？

[icedut]

SELECT ID,Name,fn_string(id) FullPath
FROM Dept
ORDER BY ID
--oracle中可以这样调用

[sdkinger]

学习了

[某鸟]

佩服 icedut 的认真……