1、存储过程判断对象是否存在并执行DDL:
- DECLARE
- V_COUNT INTEGER := 0;
- BEGIN
- SELECT COUNT(*)
- INTO V_COUNT
- FROM USER_OBJECTS US
- WHERE US.OBJECT_NAME = 'GET_CHANGE_REC_ID'
- AND US.OBJECT_TYPE = 'PROCEDURE';
- IF V_COUNT > 0 THEN
- EXECUTE IMMEDIATE 'DROP PROCEDURE GET_CHANGE_REC_ID';
- END IF;
- END;
- /
DECLARE
V_COUNT INTEGER := 0;
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM USER_OBJECTS US
WHERE US.OBJECT_NAME = 'GET_CHANGE_REC_ID'
AND US.OBJECT_TYPE = 'PROCEDURE';
IF V_COUNT > 0 THEN
EXECUTE IMMEDIATE 'DROP PROCEDURE GET_CHANGE_REC_ID';
END IF;
END;
/
2、游标试用:
创建数据:
- CREATE TABLE TBL_TEST
- (
- U_ID NUMBER(10) PRIMARY KEY,
- U_NAME VARCHAR2(100)
- );
- BEGIN
- FOR I IN 1..20 LOOP
- INSERT INTO TBL_TEST VALUES(I,'NAME'||I);
- END LOOP;
- COMMIT;
- END;
- /
CREATE TABLE TBL_TEST
(
U_ID NUMBER(10) PRIMARY KEY,
U_NAME VARCHAR2(100)
);
BEGIN
FOR I IN 1..20 LOOP
INSERT INTO TBL_TEST VALUES(I,'NAME'||I);
END LOOP;
COMMIT;
END;
/
执行命令“SET SERVEROUTPUT ON”,打开输出
以游标的形式进行ID的累加:
- DECLARE
- V_TOTAL INTEGER:=0;
- BEGIN
- FOR R IN (SELECT * FROM TBL_TEST) LOOP
- V_TOTAL:=V_TOTAL+R.U_ID;
- END LOOP;
- DBMS_OUTPUT.put_line('V_TOTAL:'||V_TOTAL);
- END;
- /
DECLARE
V_TOTAL INTEGER:=0;
BEGIN
FOR R IN (SELECT * FROM TBL_TEST) LOOP
V_TOTAL:=V_TOTAL+R.U_ID;
END LOOP;
DBMS_OUTPUT.put_line('V_TOTAL:'||V_TOTAL);
END;
/
在游标中设定参数:
- DECLARE
- V_TOTAL INTEGER := 0;
- CURSOR C(V_ID INTEGER := 10) IS SELECT * FROM TBL_TEST WHERE U_ID < V_ID;
- BEGIN
- FOR R IN C LOOP
- V_TOTAL := V_TOTAL + R.U_ID;
- END LOOP;
- DBMS_OUTPUT.PUT_LINE('V_TOTAL:' || V_TOTAL);
- EXCEPTION
- WHEN OTHERS THEN
- IF C%ISOPEN THEN
- CLOSE C;
- END IF;
- END;
- /
DECLARE
V_TOTAL INTEGER := 0;
CURSOR C(V_ID INTEGER := 10) IS SELECT * FROM TBL_TEST WHERE U_ID < V_ID;
BEGIN
FOR R IN C LOOP
V_TOTAL := V_TOTAL + R.U_ID;
END LOOP;
DBMS_OUTPUT.PUT_LINE('V_TOTAL:' || V_TOTAL);
EXCEPTION
WHEN OTHERS THEN
IF C%ISOPEN THEN
CLOSE C;
END IF;
END;
/
输入输出参数:
- CREATE OR REPLACE PROCEDURE P_TEST(V1 IN INTEGER,
- V2 IN INTEGER,
- S OUT INTEGER) IS
- BEGIN
- S := V1 + V2;
- END;
- /
- -- 调用
- DECLARE
- S INTEGER := 0;
- BEGIN
- P_TEST(1, 2,S);
- DBMS_OUTPUT.PUT_LINE('SUM:' || S);
- END;
- /
CREATE OR REPLACE PROCEDURE P_TEST(V1 IN INTEGER,
V2 IN INTEGER,
S OUT INTEGER) IS
BEGIN
S := V1 + V2;
END;
/
-- 调用
DECLARE
S INTEGER := 0;
BEGIN
P_TEST(1, 2,S);
DBMS_OUTPUT.PUT_LINE('SUM:' || S);
END;
/
只有输入参数:
- CREATE OR REPLACE PROCEDURE P_TEST(V1 IN INTEGER, V2 IN INTEGER) IS
- BEGIN
- DBMS_OUTPUT.PUT_LINE('SUM:' || (V1 + V2));
- END;
- /
CREATE OR REPLACE PROCEDURE P_TEST(V1 IN INTEGER, V2 IN INTEGER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('SUM:' || (V1 + V2));
END;
/
- EXEC P_TEST(1,2);-- 脚本调用
EXEC P_TEST(1,2);-- 脚本调用
如果出现编译错误,可以执行“SHOW ERRORS”来查看错误明细