CREATE OR REPLACE PROCEDURE get_title_or_text (
2 title_like_in IN VARCHAR2
3,return_title_in IN BOOLEAN
4,joke_count_out OUT PLS_INTEGER
5,jokes_out OUT SYS_REFCURSOR
6 )
7 IS
8 c_from_whereVARCHAR2 (100) := ' FROM jokes WHERE title LIKE :your_title';
9 l_colname all_tab_columns.column_name%TYPE := 'TEXT';
10l_query
VARCHAR2 (32767);
11 BEGIN
12 IF return_title_in
13 THEN
14l_colname := 'TITLE';
15 END IF;
16
17
l_query := 'SELECT ' || l_colname || c_from_where;
18
19 OPEN jokes_out FOR l_query USING title_like_in;
20
21 EXECUTE IMMEDIATE 'SELECT COUNT(*)' || c_from_where
22
INTO joke_count_out
23
USING title_like_in;
24 END get_title_or_text;
哦，是这样啊，谢谢老大，还有个问题就是下面代码说是声明一个嵌套表来保存游标变量的数据， jokes_tt怎么可以做类型啊，jokes_tt()又是什么意思啊
TYPE jokes_tt IS TABLE OF jokes.text%TYPE;
l_joke_array jokes_tt:= jokes_tt ();
