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”来查看错误明细