Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use stored procedures in oracle. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something from this article.

one。 Use for loop cursors: traverse all employees whose positions are managers

1. Define cursors (cursors are a small collection)

two。 Define Vernier variables

3. Use for Loop cursors

Declare-define cursors c_job cursor c_job is select empno, ename, job, sal from emp where job = 'MANAGER';-define cursor variables c_row c_row c_job%rowtype Begin-A circular cursor that uses the cursor variable c_row to store the looped value for c_row in c_job loop dbms_output.put_line (c_row.empno | |'-'| | c_row.ename | |'-'| | c_row.job | |'-'| | c_row.sal); end loop;end

two。 Fetch cursor: traverses all employees whose positions are managers

Must be clearly turned on and off when in use

Declare-- define the cursor c_job cursor c_job is select empno, ename, job, sal from emp where job = 'MANAGER';-- define the cursor variable c_row c_row cymbjb% rowtypeten begin open cantilever; loop-- extract a row of data to c_row fetch c_job into cymbal;-- determine whether the value is extracted, and exit the exit when c_job%notfound without getting the value. Dbms_output.put_line (c_row.empno | |'-'| | c_row.ename | |'-'| | c_row.job | |'-'| | c_row.sal); end loop;-- close the cursor close

three。 Use cursors and while loops: traverse the geographic locations of all departments

-- 3, use cursors and while loops to display the geographic locations of all departments (with the% found attribute) declare-declare cursor cursor csr_TestWhile is select loc from dept;-specify row pointer row_loc csr_TestWhile%rowtype;begin open csr_TestWhile;-give the first row of data fetch csr_TestWhile into row_loc -- Test whether data is available, and execute a loop while csr_TestWhile%found loop dbms_output.put_line ('Department location:' | | row_loc.LOC);-- give the next line of data fetch csr_TestWhile into row_loc; end loop; close csr_TestWhile;end

four。 Cursor with parameters: accepts the department number entered by the user

Declare-Vernier cursor c_dept (p_deptNo number) is select * from emp where emp.deptno with parameters: r_emp emp%rowtype;begin for r_emp in c_dept (20) loop dbms_output.put_line ('employee number:' | r_emp.EMPNO | | 'employee name:' | r_emp.ENAME | | 'salary:' | | r_emp.SAL); end loop;end

five。 Locked cursors: increases commission by 500 for all salesman

Declare-query data, for update of cursor csr_addComm (p_job nvarchar2) is select * from emp where job = p_job for update of comm; r_addComm emp%rowtype; commInfo emp.comm%type;begin for r_addComm in csr_addComm ('SALESMAN') loop commInfo: = r_addComm.comm + 500;-- Update data (where current of) update emp set comm = commInfo where current of csr_addComm; end loop;end

six。 Use counters: find the two employees who work the longest hours

Declare cursor crs_testComput is select * from emp order by hiredate asc;-- counter top_two number: = 2; r_testComput crs_testComput%rowtype;begin open crs_testComput; fetch crs_testComput into ritual computer; while top_two > 0 loop dbms_output.put_line ('employee name:' | r_testComput.ename | | 'working time:' | | r_testComput.hiredate) -- the speedometer minus 1 top_two: = top_two-1; fetch crs_testComput into ritual computer; end loop; close crs_testComput;end

seven。 If/else judgment: pay 20% of the basic salary for all employees, and cancel the raise if the increase is greater than 300

Declare cursor crs_upadateSal is select * from emp for update of sal; r_updateSal crs_upadateSal%rowtype; salAdd emp.sal%type; salInfo emp.sal%type;begin for r_updateSal in crs_upadateSal loop salAdd: = r_updateSal.sal * 0.2; if salAdd > 300Sal.sal.sal.dbms_output.put_line (r_updateSal.ename |': salary increase failed.' | 'salary is maintained at:' | r_updateSal.sal); else salInfo: = r_updateSal.sal + salAdd; dbms_output.put_line (r_updateSal.ENAME | |': salary increase succeeded.'| | 'salary becomes:' | | salInfo); end if; update emp set sal = salInfo where current of crs_upadateSal; end loop;end

eight。 Use case

When: pay increase by department

Declare cursor crs_caseTest is select * from emp for update of sal; r_caseTest crs_caseTest%rowtype; salInfo emp.sal%type;begin for r_caseTest in crs_caseTest loop case when r_caseTest.deptno = 10 THEN salInfo: = r_caseTest.sal * 1.05; when r_caseTest.deptno = 20 THEN salInfo: = r_caseTest.sal * 1.1; when r_caseTest.deptno = 30 THEN salInfo: = r_caseTest.sal * 1.15 When r_caseTest.deptno = 40 THEN salInfo: = r_caseTest.sal * 1.2; end case; update emp set sal = salInfo where current of crs_caseTest; end loop;end

nine。 Exception handling: data rollback

Set serveroutput on;declare d_name varchar2 (20); begin d_name: = 'developer'; savepoint A; insert into DEPT values (50, d_name,' beijing'); savepoint B; insert into DEPT values (40, d_name, 'shanghai'); savepoint C; exception when others then dbms_output.put_line (' error happens'); rollback to A; commit;end;/

ten。 Basic directives:

Set serveroutput on size 1000000 format wrapped;-- make DBMS_OUTPUT valid and set to the maximum buffer to prevent "eating" the first space set linesize 256;-- set the number of characters a line can hold set pagesize 50;-- set the number of lines per page set arraysize 5000;-- set the amount of data displayed back and forth, which will affect data such as consistent reading during autotrace-- there is no gap between the page and the page set long 5000 -- length displayed by LONG or CLOB set trimspool on;-- remove the extra spaces after each line in the SPOOL output set timing on;-- set the query time-consuming col plan_plus_exp format a120;-- explain plan output format after autotrace-- set termout off;-- do not display the output on the screen for the time being, prepare for the following setting sql-- set the time format

A little knowledge:

The following statement must be in Command Window to print the content.

Set serveroutput on;begin dbms_output.put_line; end;/ after reading the above, do you have any further understanding of how to use stored procedures in oracle? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.