开发笔记:ORACLE存储过程的创建和执行的简单示例和一些注意点

1 CREATE OR REPLACE PACKAGE pkg_query AS TYPE cur_query IS REF CURSOR;
2 END pkg_query;

1 create or replace procedure PRO_TestDemo(p_name in NVARCHAR2,
2 p_datacount out VARCHAR2,
3 p_cur out pkg_query.cur_query) is
4 v_sql VARCHAR2(1000) := \'\'; --用来存储SQL语句的变量
5 v_datacount number := 0; --用来存放受影响的条数
6 begin
7 IF p_name IS NOT NULL OR p_name <> \'\' THEN
8 v_sql := \'SELECT COUNT(*) FROM TEST1 a WHERE a.name=\' || p_name;
9 EXECUTE IMMEDIATE v_sql
10 INTO v_datacount;
11 IF (v_datacount > 0) THEN --这边的判断是为了避免不必要的语句执行
12 v_sql := \'SELECT * FROM TEST1 a WHERE a.name=\' || p_name;
13 OPEN p_cur FOR v_sql;
14 END IF;
15 END IF;
16 end PRO_TestDemo;