例如:
procedure red_i1 is
begin
fetch c1 into c1rec;
if c1%found then
cnt_i1 := cnt_i1 + 1;
if cnt_i1 = 1 then
keyset1;
red_i1;
end if;
else
red_i1_flg := 1;
end if;
end;
procedure red_i1 is
begin
fetch c1 into c1rec;
if c1%found then
cnt_i1 := cnt_i1 + 1;
if cnt_i1 = 1 then
keyset1;
red_i1;
end if;
else
red_i1_flg := 1;
end if;
end;
解决方案 »
- Oracle数据导入SQLSERVER报错
- redhat 下 oracle proc 预处理总是留下 tpXXXXXX 的临时文件
- 权限 select any dictionary
- 谁能说一下redhat 企业版上安装oracle 客户端的步骤?
- 关于Oracle别名的问题
- 我有一些sql脚本,怎样才能放到我的oracle sever运行起来呢
- 求一个存储过程的写法(读取值并返回)
- !!!!!!!求一条SQL语句!!!!!!!在线等各位达人!!!!!!!!
- 请教:Oracle数据库在什么情况下,会自动加出来些莫名其妙的表?
- 怎么插入自定义类型?
- 求助 执行过程中出错
- 请问在select 后面可不可以进行逻辑操作,详情请进入~~~
17.8.7 Recursive Processing in a SQL Statement
The SQL language does not support recursion, yet this powerful programming method is at times crucial to solving a problem. PL/SQL does allow for recursive execution of function calls, however, so you can put it to use inside a SQL statement where recursion is needed. Suppose your application needs to print checks. A check contains both the numeric version of the amount of the check (say, $99.70) and the written version of the amount of the check (ninety-nine dollars and seventy cents). You will not be able to use SQL to convert the numeric check amount (stored in the database) into the written version. You can, however, put PL/SQL to use, along with some of its more interesting features like recursion, global data, and PL/SQL tables, to provide a very elegant solution. The package below offers a checks package to provide this conversion capability.[2] [2] This implementation of "number-to-words" was first presented by Mike Burnside of Oracle Australia at the International Oracle User's Week in San Francisco in September 1994. I have made some minor modifications./* Filename on companion disk: checks.spp */
PACKAGE checks
IS
/* Convert number to words */
FUNCTION num2words (number_in IN NUMBER) RETURN VARCHAR2;
/* Since a package, must assert my purity level. */
PRAGMA RESTRICT_REFERENCES (num2words, WNDS);
END checks;
PACKAGE BODY checks
IS
/* Table structure to hold names of numeric components. */
TYPE numwords_tabtype IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER;
words_table numwords_tabtype;
/* Used in initialization section. */
v_date DATE;
FUNCTION num2words (number_in IN NUMBER) RETURN VARCHAR2
IS
my_number NUMBER;
BEGIN
/* Sorry, I don't do cents in this program! */
my_number := FLOOR (number_in);
/* $1000+ */
IF my_number >= 1000
THEN
/* Break up into two recursive calls to num2words. */
RETURN num2words (my_number/1000) ||
' Thousand ' ||
num2words (MOD (my_number, 1000));
END IF;
/* $100-999 */
IF my_number >= 100
THEN
/* Break up into two recursive calls to num2words. */
RETURN num2words (my_number/100) ||
' Hundred ' ||
num2words (MOD (my_number, 100));
END IF;
/* $20-$99 */
IF my_number >= 20
THEN
/* Break up into tens word and then final dollar amount. */
RETURN words_table (FLOOR (my_number/10)) ||
' ' ||
num2words (MOD (my_number, 10));
END IF;
/* Down to 19 or less. Get word from "upper register" of table. */
RETURN words_table (my_number + 10);
END num2words;
BEGIN
/* Initialization section of package. Run just once per session. */
/* Manually construct the tens names. */
words_table (1) := 'Ten';
words_table (2) := 'Twenty';
words_table (3) := 'Thirty';
words_table (4) := 'Forty';
words_table (5) := 'Fifty';
words_table (6) := 'Sixty';
words_table (7) := 'Seventy';
words_table (8) := 'Eighty';
words_table (9) := 'Ninety';
/* Return NULL for zero. */
words_table (10) := NULL;
/* Construct number names for one through nineteen. */
FOR day_index IN 1 .. 19
LOOP
v_date := TO_DATE (to_char(day_index) || '-JAN-94');
words_table (day_index+10) :=
INITCAP (TO_CHAR (v_date, 'DDSP'));
END LOOP;
END checks;
Here are some examples of conversion from whole numbers to words:checks.num2words (99) ==> Ninety Nine
checks.num2words (12345) ==> Twelve Thousand Three Hundred Forty Five
checks.num2words (5) ==> Five
I can also put this packaged function to use inside my SQL statement to query up all the information I need to print a check: SELECT TO_CHAR (SYSDATE, 'Month DD, YYYY'),
payee,
amount,
checks.num2words (amount),
comment
FROM bill
SQL> declare
2 num number:=5;
3 procedure pro(n in out number)
4 as
5 m number:=n;
6 begin
7 dbms_output.put_line(m);
8 if m=1 then
9 return;
10 end if;
11 m:=m-1;
12 pro(m);
13 end;
14 begin
15 pro(num);
16 end;
17 /
5
4
3
2
1PL/SQL procedure successfully completedoracle不论函数,过程都可以嵌套;
以下是函数:
create or replace function f (n positive) return integer is
begin
if n = 1 then
return 1;
else
return n * f(n - 1);
end if;
end f;
/Function createdSQL> select f(10) from dual; F(10)
----------
362880010的阶乘
我已经把递归调用改成用GOTO red_i1_beg这样的语句来完成了
祝大家圣诞快乐!