Oracle self-study (3)

Oracle self-study (3)

[SQL] pl/SQL language and programming: declare --- declare variable begin --- execute statement except10n end; for example: declare v_department char (3); v_course number; begin insert into classes (department, course) values (v_department, v_course); end;

 

Pl/SQL statement control structure 1. Select structure if condition expression then Statement 1 else if condition expression then Statement 2 else Statement 3 end if; this is the same as the shell syntax for explicit cursor

declare   teacher_id number(5);  teacher_name varchar(10);  teacher_sex varchar(2);  teacher_age number(3);  cursor teacher_cur is   select tid,tname,sex,age from teacher_teacher where age >30 and age <40;  begin     open teacher_cur;    fetch teacher_cur into teacher_id,teacher_name,teacher_sex,teacher_age；    loop      exit when not teacher_cur%found;      if teacher_sex='M'then         insert into man_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);      else         insert into female_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);      end if ;    end loop;    close teacher_cur;  end;  

 

// Note 1. use % isopen to check whether it is enabled before use. 2.% found % notFound must be used to check whether there are still operable rows when using the cursor. retrieve data from the cursor. The number of corresponding variables must be consistent with the data type. 4. close the cursor after use

Declare type teacher_record is record (teacher_id number (5); teacher_name varchar (10); teacher_sex varchar (2); teacher_age number (3); cursor is select tid, tname, sex, age from region where age> 30 and age <40; begin -- open teacher_cur; for teacher_record in teacher_cur loop if teacher_sex = 'M' then insert into man_teacher (....) values (teacher_id, teacher_name, teacher_sex, teacher_age); else insert into female_teacher (....) values (teacher_id, teacher_name, teacher_sex, teacher_age); end if; end loop; -- close teacher_cur; end; implicit cursor begin select tid, tname, sex, age into teacher_id, teacher_name, teacher_sex, teacher_age from teacher_table where tid = 13; end

 

// 1. note: For implicit cursors, there must be an into statement. Therefore, select statements using implicit cursors must select only one row of data or generate only one row of data. cursor attribute:

1. whether the cursor (% found % notfound) open teacher_cur; fetch teacher_cur into teacher_id, teacher_name, teacher_sex, teacher_age; loop exit when not teacher_cur % found; -- exit when teacher_cur % notfound if teacher_sex = 'M' then insert into man_teacher (....) values (teacher_id, teacher_name, teacher_sex, teacher_age); else insert into female_teacher (....) values (teacher_id, teacher_name, teacher_sex, teacher_age); end if; end loop; close rows; delete from teacher_table where tid = teacher_id if SQL % found then insert into success values (tid ); else insert into fail values (tid); end if; 2. number of rows of the cursor (% rowcount) open teacher_cur; fetch teacher_cur into teacher_id, teacher_name, teacher_sex, teacher_age; loop exit when teacher_cur % rowcount = 10; if teacher_sex = 'M' then insert into man_teacher (....) values (teacher_id, teacher_name, teacher_sex, teacher_age); else insert into female_teacher (....) values (teacher_id, teacher_name, teacher_sex, teacher_age); end if; end loop; close teacher_cur; -- open teacher_cur; for teacher_record in teacher_cur loop --- use for loop, the system implicitly defines a record whose data type is % rowcount as a cyclic counter and implicitly opens and closes the cursor, therefore, the preceding statement is redundant. if teacher_sex = 'M' then insert into man_teacher (....) values (teacher_id, teacher_name, teacher_sex, teacher_age); else insert into female_teacher (....) values (teacher_id, teacher_name, teacher_sex, teacher_age); end if; end loop; -- close teacher_cur; 3. whether the cursor is opened (% isopen) if fail % isopen then fetch teacher_cur into teacher_id, teacher_name, teacher_sex, teacher_age; else open cursor; end if; loop exit when limit % rowcount = 10; if teacher_sex = 'M' then insert into man_teacher (....) values (teacher_id, teacher_name, teacher_sex, teacher_age); else insert into female_teacher (....) values (teacher_id, teacher_name, teacher_sex, teacher_age); end if; end loop; close teacher_cur; // stored procedure: set serveroutput on format wrapped create or replace procedure drop_table (table_name varchar (20) as table_count: = 0; begin select count (*) into table_count from all_tables where table_name = upper (table_name); if table_count> 0 then drop table ni; end if; end ;/