Oracle Basic Syntax

=========================oracle Database Simple Statement ============

--%type type--sql> declare var_name varchar2 (--zm_tree); Fname%type; Var_type varchar2 (+);--zm_tree.  Flinktype%type; Begin SELECT T.fname,t.flinktype to Var_name,var_type from Zm_tree T where flinktype= ' company ';--single record output dbms_output.put_  Line (var_name| | ' The type is: ' | | Var_type); End --record type--sql> declaretype Emp_type is record (Var_name varchar2 (a), Var_type varchar2 (+), Var_sort varchar2 (200) ); Empinfo emp_type;begin--The order of the query and record definition is the same as the order of select T.fname,t.flinktype,t.fsort into Empinfo from Zm_tree t where T.flinktype= ' company ';--single record output Dbms_output.put_line (' Name: ' | | empinfo.var_name| | ' --type: ' | | empinfo.var_type| | ' --Sort: ' | | Empinfo.var_sort) end;--%rowtype type--sql> declarerowvar_emp zm_tree%rowtype;beginselect * into RowVar_emp from ZM_ TREE T where t.flinktype= ' company ';/* output Information */--single record output Dbms_output.put_line (' Name: ' | | rowvar_emp.fname| | ' --type: ' | | Rowvar_emp. flinktype| | ' --Sort: ' | | Rowvar_emp. Fsort); end;--variable constant var_str varchar2 (30): = ' China '; var_num constant Number (8,4): =20;--secondday--"" Process Control--if--sql> DeclarE var_str1 varchar2 (+); var_str2 varchar2 (+); Begin var_str1:= ' 121321 ';  var_str2:= ' 21321321 '; if Length (VAR_STR1) >length (VAR_STR2) then Dbms_output.put_line (VAR_STR1); else Dbms_output.put_line (VAR_STR2); end if;end;--if ElseIf--sql> declarenum_age int:=& Please enter age; beginif NUM_AGE&G t;=56 thendbms_output.put_line (' You can apply for retirement '); elsif num_age<56 thendbms_output.put_line (' You are less than 56 years old and you cannot apply for retirement! '); Elsedbms_output.put_line (' Sorry, the age is not legal! End if;end;--sql> Declarenum_age int:=& Please enter age; aboutinfo varchar2; beginif num_age>=56 thenaboutinfo:= num_age| | ' You can apply for retirement '; elsif num_age<56 thenaboutinfo:=num_age| | ' You are less than 56 years old and can not apply for retirement! '; elseaboutinfo:=num_age| | ' Sorry, the age is not legal! '; end If;dbms_output.put_line (aboutinfo); End;--casedeclareseason int:=& Please enter the quarterly number; Aboutinfo varchar (50); Begin case season while 1 then aboutinfo:=season| | '     Quarterly inclusive Monthly '; When 2 then aboutinfo:=season| | '     Quarterly contains 4,5,6 monthly '; When 3 then aboutinfo:=season| | '     Quarterly contains 7,8,9 monthly '; When 4 then Aboutinfo:=season| | '      Quarterly contains 10,11,12 monthly ';        Else aboutinfo:= ' entered value is not valid ';         End case;   Dbms_output.put_line (Aboutinfo);   End  ---"" Loop statement--loop statement runs until exit when End_condition_exp is true exits declaresum_i int:=0;i Int:=0;begin loop i:=i+1; Sum_i: =sum_i + i; Exit when i = 100;end loop;dbms_output.put_line (' The first 100 natural number's and is: ' | |  sum_i); End;--while cycle declare sum_i int:=0;var_i int:=0;beginwhile var_i<100 loop var_i:=var_i+1;  Sum_i:=sum_i+var_i;  End Loop; Dbms_output.put_line (The and of the first 100 natural numbers is: ' | |  Sum_i); End;--for Statement declaresum_i Int:=0;begin for i in reverse 1..100 loop--Reverse denotes i descending from 100 sum_i:= sum_i+i; End Loop;dbms_output.put_line (the and of the first 100 natural numbers is: ' | | sum_i); end;--cursor//cursor property: Cur_tmp%found affects at least one row of data for True;cur_tmp%notfound and%found instead Cur_tmp%rowcount returns the number of rows affected by the SQL statement cur _tmp%isopen cursors Open with True*/declarecursor cur_emp (var_id in varchar2:= ' Lili ') are select T.flinkcode,t.fname, T.flinktypefrom zm_tree twhere flinkcode like var_id| | ' % '; Type record_emp is record (Var_name Zm_tree. Flinkcode%type,var_typeZm_tree. Fname%type,var_sort varchar2 ($)); Emp_row Record_emp;begin Dbms_output. ENABLE (buffer_size = null);--Indicates that the output buffer is unrestricted open cur_emp (' 2 ');--Get the cursor value fetch cur_emp into emp_row;--loop cursor while Cur_ Emp%found Loop Dbms_output.put_line (' Name: ' | | emp_row.var_name| | ' --type: ' | | emp_row.var_type| | ' --Sort: ' | |  Emp_row.var_sort); Fetch cur_emp into Emp_row; End Loop; Close cur_emp;--the cursor is used in close cursors end;--for using the cursor without opening the cursor, reading the cursor, closing the cursor Oracle internal Auto-completion declarevar_id varchar2 (a):=& input encoding; Cursor cur_emp isselect t.flinkcode var_name,t.fname,t.flinktype from Zm_tree t where flinkcode like var_id| | ' % '; Begin dbms_output.    ENABLE (buffer_size = null); --Indicates that the output buffer is unrestricted for emp_info in Cur_emp loop--Displays the same name as the column name Dbms_output.put_line (' Name: ' | | emp_info.var_name| | ' --type: ' | | emp_info.fname| | ' --Sort: ' | | Emp_info.flinktype); End Loop;end;begin Dbms_output.    ENABLE (buffer_size = null); --Indicates output buffer unrestricted for emp_info in (select T.flinkcode var_name,t.fname,t.flinktype from Zm_tree t where Flinkcode like ' 2% ' ) Loop Dbms_Output.put_line (' Name: ' | | emp_info.var_name| | ' --type: ' | | emp_info.fname| | ' --Sort: ' | | Emp_info.flinktype); End loop;end;--Exception Handling/* Predefined exception Custom exception */--pre-defined exception Declarevar_empno T_cscustomer.cust_no%type;var_empname t_ Cscustomer.cust_name%type;beginselect cust_no,cust_name to Var_empno,var_empname from T_cscustomer where cust_no Like ' 00% ', if Sql%found then Dbms_output.put_line (' Employee number: ' | | var_empno| | ', Name: ' | | Var_empname); end if;exception when Too_many_rows and then Dbms_output.put_line (' return record more than one line '); When No_data_found then Dbms_output.put_line (' No data record '); End;/*drop table dept_tmp;        CREATE TABLE dept_tmp (Dept_no VARCHAR2) primary key NOT NULL, Dept_name VARCHAR2 (+), location VARCHAR2); */create or replace procedure pro_inserttmp isbegin insert INTO dept_tmp values (1, ' Market development ', ' join '); Commit Dbms_output.put_line (' Insert dept_tmp new record succeeded '); end pro_inserttmp;--Execute Pro_inserttmp--execurte pro_inserttmp;exec pro_  Inserttmp;begin pro_inserttmp;  End /** stored procedure parameter procedures include: in input parameters, out output parameters, in out can be modified input parameters, and as an output parameter **/-->>increate or replace procedure pro_insertdept (V_deptno in Varchar2,v_deptname in Varchar2,v_loc in VARCHAR2) Isbegin insert into dept_tmp values (V_DEPTNO,V_DEPTNAME,V_LOC); Commit Dbms_output.put_line (' Insert dept by in Parameter Success!    '); end pro_insertdept; --Do not pass in the parameter in order, specify the parameter value declare i int:=1;begin while i<20 loop--pro_insertdept (v_deptname=> ' purchasing department ',v_loc=> ' Chengdu ', v_ Deptno=> ' 2 '); Pro_insertdept (V_deptno = i,v_deptname = ' purchasing Department ' | | I,v_loc = ' Chengdu ' | |   i); i:=i+2; End loop;end;-->>outcreate or replace procedure pro_selectdept (V_deptno in Varchar2,v_deptname out Dept_tmp.dept_ Name%type,v_loc out Dept_tmp.location%type) isbegin Select Dept_name,location to V_deptname,v_loc from Dept_tmp where D Ept_no=v_deptno;exception when No_data_found then Dbms_output.put_line (' the numbered department does not exist! ') End Pro_selectdept;declarevar_deptname Dept_tmp.dept_name%type;var_loc Dept_tmp.location%type;var_deptno Dept_ Tmp.dept_no%type:=&21;begin pro_selectdept (var_deptno,v_deptname =Var_deptname,v_loc = Var_loc); Dbms_output.put_line (var_deptname| | ' Located in: ' | |  VAR_LOC);  End Variable v_deptname varchar2 () variable v_loc varchar2 (' 1 ',: pro_selectdept);p v_deptname,:v_loc rint Deptname V_loc;select:v_deptname,:v_loc from dual;create or replace procedure Pro_square (num in out Number,flag in Boolea N) ISI int:=2;begin if flag then num: = Power (num,i); --Calculate the squared else num: = sqrt (num); --Calculate square root end if;end Pro_square;

　　

Oracle Basic Syntax