SQL> create or replace procedure update_sql
2 (namenew varchar2,passnew varchar2,useraddnew out varchar2)
3 IS
4 begin
5 update sunping set name=namenew where pass=passnew
6 returning useradd into useraddnew
7 end;
8 /Warning: Procedure created with compilation errors请问这个是什么毛病啊
2 (namenew varchar2,passnew varchar2,useraddnew out varchar2)
3 IS
4 begin
5 update sunping set name=namenew where pass=passnew
6 returning useradd into useraddnew
7 end;
8 /Warning: Procedure created with compilation errors请问这个是什么毛病啊
INSERT INTO SUNPING VALUES('222','22','MANTIS');
INSERT INTO SUNPING VALUES('333','33','ORACLE');
INSERT INTO SUNPING VALUES('444','44','DWH');
INSERT INTO SUNPING VALUES('555','55','DI');COMMIT;
SQL> SELECT * FROM SUNPING;NAME PASS USERADD
---------- ---------- ----------
111 11 XF
222 22 MANTIS
333 33 ORACLE
444 44 DWH
555 55 DI-- "BULK COLLECT INTO" WAY:
CREATE OR REPLACE PROCEDURE UPDATE_SQL_TEST (NAMENEW VARCHAR2,
PASSNEW VARCHAR2/*,
USERADDNEW OUT VARCHAR2*/) IS
TYPE SUN_TYPE IS TABLE OF SUNPING.USERADD%TYPE;
ROW_SUN SUN_TYPE;
BEGIN
UPDATE SUNPING SET NAME = NAMENEW WHERE PASS = PASSNEW
RETURNING USERADD BULK COLLECT INTO ROW_SUN;
COMMIT;
FOR I IN 1..ROW_SUN.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('UPDATED USERADD : '||ROW_SUN(I));
END LOOP;
END;
SQL> EXEC UPDATE_SQL_TEST('AAA','11');UPDATED USERADD : XFPL/SQL procedure successfully completed
SQL> SELECT * FROM SUNPING;NAME PASS USERADD
---------- ---------- ----------
AAA 11 XF
222 22 MANTIS
333 33 ORACLE
444 44 DWH
555 55 DI
-- RETURN 1 RECORD:
CREATE OR REPLACE PROCEDURE UPDATE_SQL_TEST2 (NAMENEW VARCHAR2,
PASSNEW VARCHAR2,
USERADDNEW OUT VARCHAR2) IS
BEGIN
UPDATE SUNPING SET NAME = NAMENEW WHERE PASS = PASSNEW
RETURNING USERADD INTO USERADDNEW;
COMMIT;
END;
SQL> DECLARE
2 RE_VALUE VARCHAR2(100);
3 BEGIN
4 UPDATE_SQL_TEST2('BBB','22',RE_VALUE);
5 DBMS_OUTPUT.PUT_LINE('USERADD VALUE: '||RE_VALUE);
6 END;
7 /USERADD VALUE: MANTISPL/SQL procedure successfully completedSQL> SELECT * FROM SUNPING;NAME PASS USERADD
---------- ---------- ----------
AAA 11 XF
BBB 22 MANTIS
333 33 ORACLE
444 44 DWH
555 55 DISQL>
后面少了个分号吧! returning useradd into useraddnew;
namenew VARCHAR2,
passnew VARCHAR2,
useraddnew OUT VARCHAR2
)
IS
BEGIN
UPDATE sunping
SET NAME = namenew
WHERE pass = passnew
RETURN useradd
INTO useraddnew;
END;
/
試一下,看行不行
CREATE OR REPLACE PROCEDURE update_sql (
namenew VARCHAR2,
passnew VARCHAR2,
useraddnew OUT VARCHAR2
)
IS
BEGIN
UPDATE sunping
SET NAME = namenew
WHERE pass = passnew
RETURN useradd
INTO useraddnew;
END;
/