Oracle Control Structure

Oracle control structure (where the goto statement is known) a) Condition Statement if --- then/if -- then -- else/if -- then -- eslif -- then Example 1 ): write a process and enter the employee name. If the employee's salary is lower than 2000, increase the employee's salary by 10%; SQL> create or replace procedure p (pName varchar2) is v_sal emp. sal % type; -- the defined variable % type indicates that the sal type and v_sal are the same as the begin www.2cto.com select sal into v_sal from emp where ename = pName; if v_sal <2000 then upd Ate emp set sal = sal + sal * 10% where ename = pName; end if; end;/SQL> exec p ('Arthur '); Prepare Example 2): compile a process, enter an employee name. If the employee's subsidy comm is not zero, add 100 to the original base; otherwise, set it to 200 slave SQL> create or replace procedure p1 (pName varchar2) is v_comm emp. comm % type; -- the definition variable % type indicates the sal type and v _ Sal same begin select comm into v_comm from emp where ename = pName; if v_comm <> 0 then update emp set comm = comm + 100 where ename = pName; else update emp set comm = 200 where ename = pName; end if; -- always put at the end;/SQL> exec p ('Arthur '); example 3 at www.2cto.com ): you can enter an employee ID. If the employee's position is PRESIDENT, the employee's salary will be increased by 1000. If the employee's position is MANAGER, the salary will be increased by 50. 0. Salary increases by 200 for other positions --------------------------------------------------------------------------- SQL> create or replace procedure p2 (pNo number) is v_job emp. job % type; begin select job into v_job from emp where empno = pNo; if v_job = 'prerent' then update emp set sal = sal + 1000 where empno = pNo; elsif v_job = 'manager' then update emp set sal = sal + 500 where empno = pNo; else update emp set sal = sa L + 200 where empno = pNo; end if; -- always add end;/limit B) loop statement www.2cto.com I) loop... example 1): Write a process, enter the user name, and add 10 users to the users table, the User id starts from 1 and Adds explain SQL> create or replace procedure p3 (pName varchar2) is v_num number: = 1; -- variable that controls the loop. The initial value is 1 begin loop. Insert into users values (v_num, pName); -- judge whether to loop for ten times exit when v_num = 10; v_num: = v_num + 1; -- Auto increment 1 end loop end; /SQL> exec p3 ('Arthur '); ------------------------------------------------------------------------- ii): while LOOP, with whlie .... loop, end with end loop Example 1): Write a process, you can enter the user name, and add 10 users to the users table, user id from 11 to add www.2cto.com login SQL> CRES Ate or replace procedure p4 (pName varchar2) is v_num number: = 11; -- variable that controls the loop. The initial value is 1 begin while v_num <21 loop insert into users values (v_num, pName); v_num: = v_num + 1; -- Auto increment 1 end loop end;/SQL> exec p3 ('Arthur '); Limit 9) null, null statements do not execute any operations and pass control directly to the next statement. The main advantage is that the readability of PL/SQL is increased. 10) Compile paging process Example 1): Write a process, you can add a book to the book table. You must use a java program to call -------- ------------------------------------------------------------- -- In indicates that the bookID is the input variable. in fact, in is the default, -- out: indicates an output parameter -- this is a process without return values. SQL> create or replace procedure p5 (bookID in number, bookName in varchar2, publicHouse in varchar2) is begin insert into book values (bookID, bookName, publicHouse); end;/--------------------------------------------------------------------- call this process using java code ----------------------------- ---------------------------------------- Www.2cto.com // load the driver Class. forName ("oracle. jdbc. driver. oracleDriver "); Connection ct = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: MYORA1", "userName", "passWord") CallableStatement cs = ct. prepareCall ("{call p5 (?,?,?)} "); //? Assign a value to cs. setInt (1, 10); cs. setString (2, ""); cs. setString (3, "Xinhua Publishing House"); // execute cs.exe cute (); // close the resource operation. Example 2 is omitted here.): There is a returned stored procedure to compile a process, you can enter the employee ID and return the employee's name, salary, and position. -- Stored procedure SQL with input and output> create procedure p6 (bianHao in number, name out varchar2, pSal out number, pJob out varchar2) is begin select ename, sal, job into name, pSal, pJob from emp where empno = B IanHao; end;/--------------------------------------------------------------------- in java, obtain the name www.2cto.com Class in the above process. forName ("oracle. jdbc. driver. oracleDriver "); Connection ct = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: CallableStatement cs = ct. prepareCall ("{call p6 (?,?,?,?)} "); // Give the first? Assign cs. setInt (); // give the second? Assign a value to cs. registOutPrameter (2, oracle. jdbc. oracleTypes. VARCHAR); cs. registOutPrameter (3, oracle. jdbc. oracleTypes. DOUBLE); cs. registOutPrameter (4, oracle. jdbc. oracleTypes. VARCHAR); // execute cs.exe cute (); // retrieve the returned value, which indicates that the returned value is the second ?, So it is 2 String name = cs. getString (2); String job = cs. getString (4); // close related resources. Here, write a stored procedure with a returned value (list [result set, you can enter the Department number to return information about all employees of the Department. Because the oracle stored procedure does not return a value, all returned values are replaced by the out -- parameter, and the list is no exception, however, because it is a set, general parameters cannot be used. You must use package www.2cto.com plugin -- the first part to create a package and define a cursor type test_cursor. SQL> create or replace package testPackage as type test_cursor (cursor name) is ref cursor (cursor) end testPackage -- Part 2, creation process SQL> create or replace procedure p7 (bianHao in number, p_cursor out testPackage. test_cursor) is begin -- open a cursor open p_cursor for select * from emp where deptno = bianHao; end;/----------------------------------------------------- Class. forName ("oracle. jdbc. driver. oracle Driver "); Connection ct = DriverManager. getConnection (" jdbc: oracle: thin: @ 127.0.0.1: CallableStatement cs = ct. prepareCall ("{call p7 (?,?)} "); // Give the first? Assign cs. setInt (); // give the second? Assign a value to cs. registOutPrameter (2, oracle. jdbc. oracleTypes. CURSOR); www.2cto.com // execute cs.exe cute (); // obtain the result set ResultSet rs = (ResultSet) cs. getObject (2); while (rs. next () {System. out. println (rs. getInt (1) + "--" + rs. getString (2);} // close related resources. Write it here.
Author chunqiuwei