%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