Define declare,%type%, ROWTYPE, plus loops

Source: Internet
Author: User

%TYPE:
You can use%type when you define a variable whose data type is the same as the type of a data variable that you have defined, or is the same as the data type of a column in a database table.

%rowtype

PL/SQL provides%rowtype Damn as a symbol, Returns a record type whose data type is consistent with the data structure of the database table.

The advantage of using the%rowtype feature is that:
1. The number of columns and data types in the referenced database can be unnecessary to know.
2. The number of columns and data types in the referenced database can be changed in real time.

Assignment statements

Select ... into ....

Declarev_name Teacher.tname%type; V_sal Teacher.sal%type; begin  SelectT.tname,t.sal intoV_name,v_sal fromTeacher TwhereT.tno=1004;//Assignment Statement dbms_output.put_line (V_name||'The salary is:'||v_sal);End;

Condition control

Note: Here's a then, and there's elsif there's no E

Change the capitalization of keywords--preferences

--Created on 2016/2/2 by ADMINISTRATORDeclare   --Local variables here define variablesV_sal Teacher.sal%type; V_name Teacher.tname%type; V_rankvarchar2(Ten);--What is storage ?begin  --Test Statements here  --Assigning values First  SelectT.tname,t.sal intoV_name,v_sal fromTeacher TwhereT.tno=1004; ifV_sal< -  ThenV_rank:= 'Temporary Workers'; elsif v_sal>= -  andV_sal<15000  ThenV_rank:= 'Blue Collar'; elsif v_sal>=15000  andV_sal<40000  ThenV_rank:= 'White Collar'; ElseV_rank:= 'Gold Collar'; End if; Dbms_output.put_line (V_name||':'||V_rank||', Salary:'||V_rank);End;

Case

You have to define a variable again.

  CaseV_result when 'Gold Collar'  ThenDbms_output.put_line (V_name||'Salary Range: over 40000');  when 'White Collar'  ThenDbms_output.put_line (V_name||'Salary Range: 15000 to 40000');  when 'Blue Collar'  ThenDbms_output.put_line (V_name||'Salary Range: 2000 to 15000'); ElseDbms_output.put_line (V_name||'Salary Range: below 2000'); End  Case;

or write like this.

Declarev_name Teacher.tname%type; V_sal Teacher.sal%type; V_resultvarchar2( -); V_sal_rankVARCHAR( -); begin  SelectT.tname,t.sal intoV_name,v_sal fromTeacher TwhereT.tno=1004;  CaseV_result when 'Temporary Workers'   ThenV_sal_rank:= '<2000';  when 'Blue Collar'  ThenV_sal_rank:= '[2000,15000]';  when 'White Collar'  ThenV_sal_rank:= '[15000,4000]'; ELSEV_sal_rank:= '>40000'; END  Case; Dbms_output.put_line (V_name||':'||V_result||', Salary:'||V_sal_rank);End;
Looping statements

When you create a new sequence,

New additions are good

Create TableDept_loop as Select *  fromDept--Copying TablesDelete  fromDept_loop; -- Delete data  --LoopsDeclareV_start Number; V_temp Number:=0;begin    Select Max(DEPTNO) intoV_start fromDept_loop; ifV_start is NULL  ThenV_start:= ";    End if; Loop V_start:=V_start+1; Insert  intoDept_loopValues(V_start,'Department'||V_start,'Address'||V_start); V_temp:=V_temp+1; Exit  whenV_temp>=Ten; EndLoop; Commit; Select Max(DEPTNO) intoV_start fromDept_loop; V_temp:=0;  whileV_temp<=TenLoop V_start:=V_start+1; Insert  intoDept_loopValues(V_start,'Department'||V_start,'Address'||V_start); V_temp:=V_temp+1; EndLoop; Commit; Select Max(DEPTNO) intoV_start fromDept_loop;  forV_tempinchV_start+1.. V_start+TenLoopInsert  intoDept_loopValues(V_start,'Department'||V_start,'Address'||V_start); EndLoop;Commit;End;

For explanation

V_start+1..v_start+10 means, add 1, 2, +3 、、、 to plus 10.

And V_temp is the thing that stands above.

The above is always select

 Select Max(DEPTNO) intoV_start fromDept_loop;  forV_tempinchV_start+1.. V_start+TenLoopInsert  intoDept_loopValues(V_temp,'Department'||V_temp,'Address'||v_temp); EndLoop;Commit;

Define declare,%type%, ROWTYPE, plus loops

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.