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;
/
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;
/
执行命令“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;
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) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('SUM:' || (V1 + V2));
END;
/
EXEC P_TEST(1,2);-- 脚本调用
如果出现编译错误,可以执行“SHOW ERRORS”来查看错误明细。