Application Operation instances and resolutions of PL/SQL

PL/SQL Application Operation instance and resolution [SQL] declare msg char (15); -- defines the variable begin msg: = 'hell '; -- assign the value dbms_output.put_line (msg); end; -- The declared type is emp. sal type variables are consistent with declare sal emp. sal % type; -- sal and emp tables have the same sal type as begin sal: = 900.44; dbms_output.put_line (sal); end; -- record a row declare -- declare emp_rec emp % rowtype; -- emp_rec is a row record of the variable emp table. emp % rowtype begin select * into emp_rec from emp where empno = 7499; -- When assigning values to emp_rec, you must assign values to dbms_output.put_line (emp_rec.sal) for an into statement record. -- emp_rec needs emp_rec.sal end; -- if Statement Form 1 declare grade number (5, 2); begin grade: = 80; if grade> 70 then dbms_output.put_line ('greate'); end if; end; -- if Statement Form 2 declare grade number (5, 2); begin grade: = 60; if grade> 70 then dbms_output.put_line ('greate'); else dbms_output.put_line ('bad '); end if; end; -- if Statement Form 3 declare grade number (5, 2); begin grade: = 60; if grade> 70 then dbms_output.put_line ('greate '); elsif grade> 50 then dbms_output.put_line ('bad'); else dbms_output.put_line ('bad2'); end if; end; accept num prompt 'enter the number '; -- When receiving the input value from the keyboard, the value of num is automatically saved. declare -- declare pnum number: = & num; -- & stands for the variable type begin dbms_output.put_line (pnum); end; declare pnum number: = 1; begin while pnum <10 -- while statement exit when pnum> 10 loop exit condition loop dbms_output.put_line (pnum); pnum: = pnum + 1; -- conditional end loop for loop change; end; declare begin for I in 1 .. 10 loop dbms_output.put_line (I); end loop; end; select * from emp; declare job_rec emp. job % type; begin select job into job_rec from emp; -- if a problem exists, multiple jobs are returned, job_rec can only accept one value if job_rec = 'manager' then update emp set sal = sal + 800; -- The where clause elsif job_rec = 'salesman' then update emp set sal = sal + 400; else update emp set sal = sal + 200; end if; end; -- declare the cursor declare cursor cl is select * from emp; -- Query all emp records stored in the cl cursor emp_rec emp % rowtype; begin open cl; -- open the cursor loop exit when cl % notfound; -- the condition cl % notfound for loop exit returns true fetch cl into emp_rec if no record exists; -- retrieve data dbms_output.put_line (emp_rec.empno ); end loop; close cl; end; declare emp_row emp % rowtype; begin select * into emp_row from emp; -- implicit cursor -- exception declaration exception when too_many_rows then -- the name of the when exception then the exception execution operation dbms_output.put_line ('overview'); end; declare no_data exception; emp_row emp % rowtype; cursor cl (pno number) is select * from emp where empno = pno; begin open cl (0000); fetch cl into emp_row; if cl % notfound then raise no_data; -- exception end if; close cl; exception -- catch the exception when no_data then dbms_output.put_line ('no data found '); end; -- declare: displays the employee information whose department number is 30. All emp records are queried and stored in the cl cursor. cursor cl (dno number) is select * from dept where deptno = dno; begin for dept_row in cl (30) -- open the cursor (automatic) to retrieve data close the cursor emp_row variable and do not need to define the loop dbms_output.put_line ('department name: '| dept_row.dname |' employee: '| dept_row.loc); -- dbms_output.put_line (dept_row.loc); end loop; end; -- add salary to employees declare -- Define the cursor, query job position and employee number cursor cr is select empno, job from emp; -- declare a variable. The variable type is the same as the job type in emp. job % type; epno emp. empno % type; -- start begin -- open the cursor open cr; loop -- retrieve the cursor fetch cr into epno, pjob; exit when cr % notfound; -- determine whether the cursor exists if pjob = 'cler' then update emp set sal = sal + 1000 where empno = epno; elsif pjob = 'manager' then update emp set sal = sal + 800 where empno = epno; else update emp set sal = sal + 400 where empno = epno; end if; end loop; close cr; end; select * from emp;