PLSQL编程实例
-------------------------PL/SQL-------------------------------------------------CREATE PROCEDURE------------------------CREATE OR REPLACE PROCEDURE PRO_003(PARAMS)
IS
--内部使用变量或自定义错误异常
EXCEPTION EXP_NAME;
BEGIN
EXCEPTION
--出现异常会跳到此处
END PRO_003;
--------------------CREATE FUNCTION-------------------------CREATE OR REPLACE FUNCTION FUN_003(PARAMS) RETURN NUMBER
IS
--内部使用变量
BEGIN
RETUNE
EXCEPTION
--出现异常会跳到此处
RETUNE
END PRO_003;
SET SERVEROUTPUT ON; --开启DBMS_OUTPUT
--------------------调用示例--------------------------------DECLARE
R NUMBER(1);
BEGIN
R := USER_ADD('baskball') ;
DBMS_OUTPUT.PUT_LINE(R);
END;
--------------------CREATE SEQUENCE-------------------------CREATE SEQUENCE DATA_SEQ_003
START WITH0
INCREMENT BY1
MAXVALUE99
MINVALUE0
NOCACHE
NOCYCLE;
--------------------CREATE FUNCTION-------------------------
CREATE OR REPLACE FUNCTION FUN003(para_interest IN U100.TESTPLSQL.INTEREST%TYPE) RETURN NUMBER
IS
v_re number(2);
BEGIN
INSERT INTO U100.TESTPLSQL(ID,DATA_SEQ,INTEREST)
VALUES(03,DATA_SEQ_003.nextval,para_interest);
COMMIT;
v_re :=0;
DBMS_OUTPUT.PUT_LINE('INSERT SECCEDDED.');
RETURN v_re;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
DBMS_OUTPUT.PUT_LINE('ERROR:'||SQLCODE);
END FUN_INSERT;
--------------------CREATE PROCEDURE------------------------
--NO_DATA_FOUND只有SELECT INTO语句才会触发
create or replace PROCEDURE PRO003(user_id NUMBER)
IS
CURSOR CUR IS SELECT ID,DATA_SEQ,INTEREST FROM U100.TESTPLSQL WHERE ID = user_id ORDER BY DATA_SEQ;
REC CUR%ROWTYPE;
EXC_01 EXCEPTION;
BEGIN
IF CUR%ISOPEN =FALSE
THEN
OPEN CUR;
END IF;
LOOP
FETCH CUR INTO REC;
EXIT WHEN CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(REC.ID||' '||REC.DATA_SEQ||'
'||REC.INTEREST);
END LOOP;
IF CUR%ROWCOUNT =0
THEN
RAISE EXC_01;
END IF;
IF CUR%ISOPEN
THEN
CLOSE CUR;
END IF;
EXCEPTION
WHEN EXC_01
THEN
DBMS_OUTPUT.PUT_LINE('No.'||user_id||':NO DATA FOUND!'); END PRO003;