Oracle Database pl SQL study notes, oraclepl

Oracle Database pl SQL study notes, oraclepl

1. define basic variables:

2. referenced variable: set serveroutput on declarepename emp. ename % type; psal emp. sal % type; beginselect ename, sal into pename, psal from emp where empno = '000000'; dbms_output.put_line (pename | 'salary:' | psal); end; /3. Record-type variables: set serveroutput on declareemp_rec emp % rowtype; beginselect * into emp_rec from emp where empno = '2016 '; dbms_output.put_line (emp_rec.ename | 'salary: '| emp_rec.sal); end;/4. Use the if statement/* to determine the user input from the keyboard */set server Output on -- accept a keyboard input -- num: address value, meaning that the input value accept num prompt is saved on this address 'enter a number '; declare -- Define the variable input pnum number from the keyboard: = & num; beginif pnum = 0 then dbms_output.put_line ('your input is 0 '); elsif pnum = 1 then dbms_output.put_line ('your input is 1'); elsif pnum = 2 then dbms_output.put_line ('your input is 2'); else encode ('others '); end if; end;/5. while loop: set serveroutput on declare pnum number: = 1; begin while pnum <= 10 loop dbms_outpu T. put_line (pnum); pnum: = pnum + 1; end loop; end;/6. loop set serveroutput on declare pnum number: = 1; beginloop exit when pnum> 10; dbms_output.put_line (pnum); pnum: = pnum + 1; end loop; end;/7. for loop set serveroutput on declarepnum number: = 1; begin for pnum in 1 .. 10 loop dbms_output.put_line (pnum); end loop; end;/(loop is recommended) 8. Use of the cursor -- Query and print the employee's name and salary set serveroutput on/* attributes of the cursor % found: the cursor finds the record % notfound: The cursor cannot find the record * /Declare -- Define a cursor cemp is select ename, sal from emp; -- Define the corresponding variable pename emp for the cursor. ename % type; psal emp. sal % type; begin -- open the cursor open cemp; loop -- take a record fetch cemp into pename, psal; exit when cemp % notfound; dbms_output.put_line (pename | 'salary: '| psal); end loop; -- close the cursor and close cemp; end;/9. instance --- raise the employee's salary: set serveroutput on declare cursor cemp is select empno, perjob from emp; pempno emp. empno % type; pjob emp. perjob % ty Pe; beginopen cemp; loop fetch cemp into pempno, pjob; exit when cemp % notfound; if pjob = 'President 'then update emp set sal = sal + 1500 where empno = pempno; elsif pjob = 'analyst' then update emp set sal = sal + 1000 where empno = pempno; elsif pjob = 'salesman' then update emp set sal = sal + 500 where empno = pempno; else update emp set sal = sal + 300 where empno = pempno; end if; end loop; close cemp; commit; (if the upda If the data is te, you need to add commit) end;/10. other attributes of the cursor and Their Use instance ① % isopen: if cemp % isopen then dbms_output.put_line ('Open with a light '); ② % rowcount: (total number of affected rows) dbms_output.put_line ('number of rows: '| cemp % rowcount); 11. Number of cursors limit: by default, oracle databases only allow 300 cursors to be opened in the same session to view the cursor attributes: switch to the sys administrator user, conn sys/root as sysdba can switch to sys user to modify the default cursor count: alter system set open_cursors = 400 scope = both; (scope has three parameters: memory: modify only the current instance, do not change the system parameter file spfile: only change the parameter file, do not change the current instance, You need to restart the database to take effect both: Is the above two as shown in If an error is set, you can use the rollback command to roll back 11. set serveroutput on declare cursor cemp (dno number) is select ename from emp where deptno = dno; pename emp. ename % type; beginopen cemp (10); loop fetch cemp into pename; exit when cemp % notfound; dbms_output.put_line (pename); end loop; close cemp; end; /12. System exception: no_data_found (no data found) too_many_rows (select... the into statement matches multiple rows.) zero_divide (Division by zero) value_error (arithmetic or conversion error) timeout_on_resourc E (timeout while waiting for resources) 13. custom exception: set serveroutput on declare pename emp. ename % type; no_emp_found exception; cursor cemp is select ename from emp where empno = 12; beginopen cemp; fetch cemp into pename; if cemp % notfound then raise no_emp_found; end if; close cemp; exception when no_emp_found then dbms_output.put_line ('employee not found '); when others then dbms_output.put_line ('others'); end; /14. count the number of employees hired each year. set serveroutput on declare Cursor cemp is select to_char (hiredate, 'yyyy') from emp; pdate varchar2 (4); count80 number: = 0; count81 number: = 0; count82 number: = 0; count87 number: = 0; beginopen cemp; loopfetch cemp into pdate; exit when cemp % notfound; if pdate = '000000' then count80: = count80 + 1; elsif pdate = '000000' then count81: = count81 + 1; elsif pdate = '000000' then count82: = count82 + 1; else count87: = count87 + 1; end if; end loop; dbms_output. Put_line ('total: '| (count80 + count81 + count82 + count87); dbms_output.put_line ('1980:' | count80); dbms_output.put_line ('1981: '| count81); dbms_output.put_line ('2017:' | count82); dbms_output.put_line ('2017: '| count87); close cemp; end;/15. Case 2: analyze the SQL statement select empno, sal from emp order by sal asc; --> cursor --> loop --> exit condition: 1. total salary> 5 w 2.% notfound variable: 1. initial Value 2. how do I get the salary increase: countEmp number: = 0; total salary after the rise: salTotal number; s Elect sum (sal) into salTal from emp; total salary after Zhang = total salary before the increase + sal * 0.1 */set serveroutput ondeclare cursor cemp is select empno, sal from emp order by sal; pempno emp. empno % type; psal emp. sal % type; countEmp number: = 0; salTotal number; stop_sal exception; beginselect sum (sal) into salTotal from emp; open cemp; loopif salTotal <50000 then (the code below is executed only when the total wage limit is less than 5000) exit when salTotal> 50000; fetch cemp into pempno, Psal; exit when cemp % notfound; countEmp: = countEmp + 1; update emp set sal = sal * 1.1 where empno = pempno; salTotal: = salTotal + psal * 0.1; else raise stop_sal; end if; end loop; close cemp; commit; dbms_output.put_line ('salary increase: '| countEmp | 'total salary:' | salTotal ); exception when stop_sal then dbms_output.put_line ('end of salary increase '); when others then dbms_output.put_line ('others'); end;/16. Case 4 1. Because the final result is also a table, create table m Sg1 (coursename varchar2 (20), dname varchar2 (20), count1 number, count2 number, count3 number, avggrade number); SQL statement 1. select dno, dname from dep; --> cursor --> loop --> exit condition: notfound2: select grade fromm SC where cno = (select cno from course where cname = ??) And sno in (select sno from student where dno = ??); --> The cursor with parameters --> loop --> exit condition --> notfound variable: 1. Initial Value 2. How to obtain the number of people in each score segment count1 number, count2 number, count3 number avggrade number 1, arithmetic operation 2, SQL statement query set serveroutput on declare-system cursor cdept is select dno, dname from dep; pdno dep. dno % type; pdname dep. dname % type; -- result cursor cgrade (coursename varchar2, depno number) is select grade from SC where cno = (select cno from course where cname = coursena Me) and sno in (select sno from student where dno = depno); pgrade SC. grade % type; -- number of students in each score segment: count1 number; count2 number; count3 number; -- average avggrade number of students who have taken college physics; -- Course name: pcourseName varchar2 (20): = 'physically '; begin -- open the cursor of the system open cdept; loop -- get the information of a system fetch cdept into pdno, pdname; exit when cdept % notfound; -- initialization job count1: = 0; count2: = 0; count3: = 0; -- System average score select avg (grade) into avggrade from SC where cn O = (select cno from course where cname = pcourseName) and sno in (select sno from student where dno = pdno ); -- The system selects open cgrade (pcourseName, pdno) for college physics students; loop -- fetch cgrade into pgrade for a student; exit when cgrade % notfound; -- determine the score range if pgrade <60 then count1: = count1 + 1; elsif pgrade> = 60 and pgrade <85 then count2: = count2 + 1; else count3: = count3 + 1; end if; end loop; close cgrade; -- save the current structure insert into m Sg1 values (pcourseName, pdname, count1, count2, count3, avggrade); end loop; close cdept; dbms_output.put_line ('data query succeeded! '); End ;/