根据这个你稍做改动.我不太懂sqlserver CREATE FUNCTION F_HAVEBIDRIGHT(QUOTEID INTEGER, V_RIGHT VARCHAR2(4000)) RETURNS INTEGER IS TYPE V_CURSOR IS REF CURSOR; MYCURSOR V_CURSOR; STATUS INTEGER := 0; SMALLID INTEGER := 0;BEGIN IF RIGHT = 'admin' THEN RETURN 1; ELSE OPEN MYCURSOR FOR SELECT CATALOGSMALLID FROM TMATERIALINFO WHERE MIID IN (SELECT MIID FROM TBIDQUOTE A, TBIDMATERIAL B WHERE A.QUOTEID = B.QUOTEID AND A.QUOTEID = QUOTEID) LOOP FETCH MYCURSOR INTO SMALLID; EXIT WHEN MYCURSOR%NOTFOUND; END LOOP; STATUS := CHARINDEX(',' || SMALLID || ',', ',' || V_RIGHT || ',') --sqlserver里面这里的Charindex是什么意思? IF STATUS > 0 THEN RETURN 1; END IF; END IF; CLOSE MYCURSOR RETURN 0 END F_HAVEBIDRIGHT;
谢谢了, sqlserver里面这里的Charindex是什么意思? 就是oracle中的inStr()函数,只是里面的参数换个位置就行了, 这是改后的可以的版本:CREATE OR REPLACE FUNCTION F_HAVEBIDRIGHT(pQUOTEID number, V_RIGHT VARCHAR2) RETURN number IS TYPE V_CURSOR IS REF CURSOR; MYCURSOR V_CURSOR; STATUS number(10) := 0; SMALLID number(10) := 0; BEGIN IF V_RIGHT = 'admin' THEN RETURN 1; ELSE OPEN MYCURSOR FOR SELECT CATALOGSMALLID FROM TMATERIALINFO WHERE MIID IN (SELECT MIID FROM TBIDQUOTE A, TBIDMATERIAL B WHERE A.QUOTEID = B.QUOTEID AND A.QUOTEID = pQUOTEID); LOOP FETCH MYCURSOR INTO SMALLID; EXIT WHEN MYCURSOR%NOTFOUND; END LOOP; CLOSE MYCURSOR; STATUS := instr(',' || V_RIGHT || ',',',' || SMALLID || ','); IF STATUS > 0 THEN RETURN 1; END IF; END IF; RETURN 0; END F_HAVEBIDRIGHT;
CREATE FUNCTION F_HAVEBIDRIGHT(QUOTEID INTEGER, V_RIGHT VARCHAR2(4000)) RETURNS INTEGER IS TYPE V_CURSOR IS REF CURSOR;
MYCURSOR V_CURSOR;
STATUS INTEGER := 0;
SMALLID INTEGER := 0;BEGIN
IF RIGHT = 'admin' THEN
RETURN 1;
ELSE
OPEN MYCURSOR FOR
SELECT CATALOGSMALLID
FROM TMATERIALINFO
WHERE MIID IN (SELECT MIID
FROM TBIDQUOTE A, TBIDMATERIAL B
WHERE A.QUOTEID = B.QUOTEID
AND A.QUOTEID = QUOTEID) LOOP FETCH MYCURSOR
INTO SMALLID;
EXIT WHEN MYCURSOR%NOTFOUND;
END LOOP;
STATUS := CHARINDEX(',' || SMALLID || ',', ',' || V_RIGHT || ',') --sqlserver里面这里的Charindex是什么意思?
IF STATUS > 0 THEN RETURN 1;
END IF;
END IF; CLOSE MYCURSOR RETURN 0
END F_HAVEBIDRIGHT;
sqlserver里面这里的Charindex是什么意思?
就是oracle中的inStr()函数,只是里面的参数换个位置就行了,
这是改后的可以的版本:CREATE OR REPLACE FUNCTION F_HAVEBIDRIGHT(pQUOTEID number, V_RIGHT VARCHAR2)
RETURN number
IS
TYPE V_CURSOR IS REF CURSOR;
MYCURSOR V_CURSOR;
STATUS number(10) := 0;
SMALLID number(10) := 0;
BEGIN
IF V_RIGHT = 'admin' THEN
RETURN 1;
ELSE
OPEN MYCURSOR FOR
SELECT CATALOGSMALLID FROM TMATERIALINFO
WHERE MIID IN (SELECT MIID
FROM TBIDQUOTE A, TBIDMATERIAL B
WHERE A.QUOTEID = B.QUOTEID
AND A.QUOTEID = pQUOTEID);
LOOP
FETCH MYCURSOR INTO SMALLID;
EXIT WHEN MYCURSOR%NOTFOUND;
END LOOP;
CLOSE MYCURSOR;
STATUS := instr(',' || V_RIGHT || ',',',' || SMALLID || ',');
IF STATUS > 0 THEN
RETURN 1;
END IF;
END IF;
RETURN 0;
END F_HAVEBIDRIGHT;