In oracle-day04

(vi) Circulation
1. Unconditional loop
Grammatical structure

loop--循环语句end loop;

Example: output 100 numbers starting from 1

declarev_num number:=1;beginloopdbms_output.put_line(v_num);v_num:=v_num+1;exit when v_num>100;end loop;end ;

2. Condition Cycle
Grammatical structure

while 条件loopend loop;

Example: output 100 numbers starting from 1

declarev_num number:=1;beginwhile v_num<=100loopdbms_output.put_line(v_num);v_num:=v_num+1;end loop;end ;

3. For loop
Basic syntax

for 变量 in 起始值..终止值loopend loop;

Example: output 100 numbers starting from 1

beginfor v_num in 1..100loopdbms_output.put_line(v_num);end loop;end;

(vii) Cursors
1. What is a cursor
A cursor is a data buffer opened by the system for the user to hold the results of the SQL statement execution. We
You can interpret a cursor as a result set in PL/SQL.

2. Syntax structure and examples
To declare a cursor in the declaration area, the syntax is as follows:

cursor 游标名称 is SQL 语句;
Using the Tour banner method

open 游标名称loopfetch 游标名称 into 变量exit when 游标名称%notfoundend loop;close 游标名称

Requirements: Print the price list of the owner type 1
Code:

declarev_pricetable T_PRICETABLE%rowtype;--价格行对象cursor cur_pricetable is select * from T_PRICETABLE whereownertypeid=1;--定义游标beginopen cur_pricetable;--打开游标loopfetch cur_pricetable into v_pricetable;--提取游标到变量exit when cur_pricetable%notfound;--当游标到最后一行下面退出循环dbms_output.put_line( ‘价格:‘||v_pricetable.price ||‘吨位：‘||v_pricetable.minnum||‘-‘||v_pricetable.maxnum );end loop;close cur_pricetable;--关闭游标end ;

The results of the operation are as follows:

3. Cursors with parameters
The condition values of our query statements are likely to be determined at runtime, such as the sex owner type,
It may be the runtime to decide, how can it be implemented? We'll then learn about cursors with parameters, modifying
Above case

declarev_pricetable T_PRICETABLE%rowtype;--价格行对象cursor cur_pricetable(v_ownertypeid number) is select *from T_PRICETABLE where ownertypeid=v_ownertypeid;--定义游标beginopen cur_pricetable(2);--打开游标loopfetch cur_pricetable into v_pricetable;--提取游标到变量exit when cur_pricetable%notfound;--当游标到最后一行下面退出循环dbms_output.put_line(‘价格:‘||v_pricetable.price ||‘吨位：‘||v_pricetable.minnum||‘-‘||v_pricetable.maxnum );end loop;close cur_pricetable;--关闭游标end ;

4.FOR loop Fetch CURSOR Value
Each time we fetch the cursor, we need to open the cursor to close the cursor loop cursor to extract the cursor control loop
Quit and so on, good trouble! Is there a simpler wording? Yes! With a For loop everything is so simple,
The code of the example above can be transformed into the following form

declarecursor cur_pricetable(v_ownertypeid number) is select *from T_PRICETABLE where ownertypeid=v_ownertypeid;--定义游标beginfor v_pricetable in cur_pricetable(3)loopdbms_output.put_line(‘价格:‘||v_pricetable.price ||‘吨位：‘||v_pricetable.minnum||‘-‘||v_pricetable.maxnum );end loop;end ;

Second, storage function
(a) What is a storage function
A storage function is also called a custom function. You can receive one or more parameters, returning a result.
In the function we can use P/SQL to handle the logic.
(ii) Storage function syntax structure
The syntax for creating or modifying a stored procedure is as follows:

CREATE [ OR REPLACE ] FUNCTION 函数名称（参数名称 参数类型, 参数名称 参数类型, ...）RETURN 结果变量数据类型IS变量声明部分;BEGIN逻辑部分;RETURN 结果变量;[EXCEPTION异常处理部分]END;

(c) Case
Requirements: Create a storage function to query the address name based on the address ID.
Statement:

create function fn_getaddress(v_id number)return varchar2isv_name varchar2(30);beginselect name into v_name from t_address where id=v_id;return v_name;end;

To test this function:

select fn_getaddress(3) from dual
Output content

Requirements: Query owner ID, owner's name, owner address, owner address use the function we created just now
To achieve.

select id 编号,name 业主名称,fn_getaddress(addressid) 地址from t_owners

The query results are as follows:

In oracle-day04