17,377
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure P_TEST(E_APPNO IN VARCHAR2) is
MAIL1 VARCHAR2(200);
MAIL2 VARCHAR2(2000);
CNT NUMBER;
begin
SELECT COUNT(A.PEMPLID)
INTO CNT
FROM APP_PASSENGER A
WHERE A.PEMPLID IS NOT NULL
AND A.APPNO = E_APPNO;
IF CNT > 0 THEN
FOR X IN (SELECT A.PEMPLID
FROM APP_PASSENGER A
WHERE A.PEMPLID IS NOT NULL
AND A.APPNO = E_APPNO) LOOP
SELECT F_GetEmpByWebService(X.PEMPLID) INTO MAIL1 FROM DUAL;
select MAIL1,
substr(MAIL1,
instr(MAIL1, ',', 1, 2) + 8,
instr(MAIL1, ',', 1, 3) - instr(MAIL1, ',', 1, 2) - 9)
into MAIL1
from dual;
IF MAIL1 <> 'null' THEN
MAIL2 := MAIL2 || ',' || MAIL1;
END IF;
END LOOP;
MAIL2 := SUBSTR(MAIL2, 1);
END IF;
end P_TEST;
CREATE OR REPLACE PROCEDURE P_TEST
(
E_APPNO IN VARCHAR2(200),
MAIL1 IN OUT VARCHAR2(200),
MAIL2 IN OUT VARCHAR2(2000)
)
IS
V_MAIL1 VARCHAR2(200);
BEGIN
FOR X IN (SELECT PEMPLID
FROM APP_PASSENGER
WHERE APPNO = E_APPNO
AND A.PEMPLID IS NOT NULL
)LOOP
SELECT MAIL1,
SUBSTR(MAIL1,
INSTR(MAIL1, ',', 1, 2) + 8,
INSTR(MAIL1, ',', 1, 3) - INSTR(MAIL1, ',', 1, 2) - 9)
INTO MAIL1
FROM DUAL;
IF MAIL1 IS NOT NULL THEN
MAIL2 := MAIL2 || ',' || V_MAIL1;
END IF;
END LOOP;
MAIL2 := SUBSTR(MAIL2, 1);
END P_TEST;
CREATE OR REPLACE PROCEDURE P_TEST
(
E_APPNO IN VARCHAR2(200),
MAIL1 IN OUT VARCHAR2(200),
MAIL2 IN OUT VARCHAR2(2000)
)
IS
V_MAIL1 VARCHAR2(200);
BEGIN
SELECT COUNT(A.PEMPLID)
INTO CNT
FROM APP_PASSENGER A
WHERE A.PEMPLID IS NOT NULL
AND A.APPNO = E_APPNO;
FOR X IN (SELECT PEMPLID
FROM APP_PASSENGER
WHERE APPNO = E_APPNO
AND A.PEMPLID IS NOT NULL
)LOOP
SELECT MAIL1,
SUBSTR(MAIL1,
INSTR(MAIL1, ',', 1, 2) + 8,
INSTR(MAIL1, ',', 1, 3) - INSTR(MAIL1, ',', 1, 2) - 9)
INTO MAIL1
FROM DUAL;
IF MAIL1 IS NOT NULL THEN
MAIL2 := MAIL2 || ',' || V_MAIL1;
END IF;
END LOOP;
MAIL2 := SUBSTR(MAIL2, 1);
END P_TEST;
create or replace procedure P_TEST(E_APPNO IN VARCHAR2(200),
MAIL1 VARCHAR2(200),
MAIL2 IN OUT VARCHAR2(2000),
CNT IN OUT NUMBER
)
is
V_MAIL1 varchar2(200);
begin
SELECT COUNT(A.PEMPLID)
INTO CNT
FROM APP_PASSENGER A
WHERE A.PEMPLID IS NOT NULL
AND A.APPNO = E_APPNO;
IF CNT > 0 THEN
FOR X IN (SELECT A.PEMPLID
FROM APP_PASSENGER A
WHERE A.PEMPLID IS NOT NULL
AND A.APPNO = E_APPNO) LOOP
SELECT F_GetEmpByWebService(X.PEMPLID) INTO MAIL1 FROM DUAL;
select MAIL1,
substr(MAIL1,
instr(MAIL1, ',', 1, 2) + 8,
instr(MAIL1, ',', 1, 3) - instr(MAIL1, ',', 1, 2) - 9)
into V_MAIL1
from dual;
IF v_MAIL1<> 'null' THEN
MAIL2 := MAIL2 || ',' || v_MAIL1;
END IF;
END LOOP;
MAIL2 := SUBSTR(MAIL2, 1);
END IF;
end P_TEST;
create or replace procedure P_TEST(E_APPNO IN VARCHAR2(200),
MAIL1 VARCHAR2(200),
MAIL2 IN OUT VARCHAR2(2000),
CNT NUMBER
)
is
V_MAIL1 varchar2(200);
V_MAIL2 varchar2(2000);
begin
SELECT COUNT(A.PEMPLID)
INTO CNT
FROM APP_PASSENGER A
WHERE A.PEMPLID IS NOT NULL
AND A.APPNO = E_APPNO;
IF CNT > 0 THEN
FOR X IN (SELECT A.PEMPLID
FROM APP_PASSENGER A
WHERE A.PEMPLID IS NOT NULL
AND A.APPNO = E_APPNO) LOOP
SELECT F_GetEmpByWebService(X.PEMPLID) INTO MAIL1 FROM DUAL;
select MAIL1,
substr(MAIL1,
instr(MAIL1, ',', 1, 2) + 8,
instr(MAIL1, ',', 1, 3) - instr(MAIL1, ',', 1, 2) - 9)
into V_MAIL1
from dual;
IF v_MAIL1<> 'null' THEN
MAIL2 := MAIL2 || ',' || v_MAIL1;
END IF;
END LOOP;
V_MAIL2 := SUBSTR(MAIL2, 1);
END IF;
end P_TEST;
create or replace procedure P_TEST(E_APPNO IN VARCHAR2) is
MAIL1 VARCHAR2(200);
MAIL2 VARCHAR2(2000);
CNT NUMBER;
begin
SELECT COUNT(A.PEMPLID)
INTO CNT
FROM APP_PASSENGER A
WHERE A.PEMPLID IS NOT NULL
AND A.APPNO = E_APPNO;
IF CNT > 0 THEN
FOR X IN (SELECT A.PEMPLID
FROM APP_PASSENGER A
WHERE A.PEMPLID IS NOT NULL
AND A.APPNO = E_APPNO) LOOP
SELECT F_GetEmpByWebService(X.PEMPLID) INTO MAIL1 FROM DUAL;
with tmp as
(select MAIL1 str from dual)
select substr(str,
instr(str, ',', 1, 2) + 8,
instr(str, ',', 1, 3) - instr(str, ',', 1, 2) - 9)
into MAIL1
from tmp
group by str;
IF MAIL1 <> 'null' THEN
MAIL2 := MAIL2 || ',' || MAIL1;
END IF;
END LOOP;
select substr(mail2, 1) into mail2 from dual;
END IF;
end P_TEST;
SELECT MAIL1,
SUBSTR(MAIL1,
INSTR(MAIL1, ',', 1, 2) + 8,
INSTR(MAIL1, ',', 1, 3) - INSTR(MAIL1, ',', 1, 2) - 9)
INTO MAIL1
FROM DUAL;
MAIL2 := SUBSTR(MAIL2, 1);
这两句报错了
Compilation errors for PROCEDURE SENDCAR.P_TEST
Error: PL/SQL: ORA-00947: not enough values
Line: 18
Text: FROM DUAL;
Error: PL/SQL: SQL Statement ignored
Line: 13
Text: SELECT MAIL1,
Error: Hint: Value assigned to 'MAIL2' never used in 'P_TEST'
Line: 26
Text: MAIL2 := SUBSTR(MAIL2, 1);