Complete notes on oracle PLSQL

Complete notes on learning oracle plsql pl/SQL anonymous block declare -- Declaration part, optional begin -- execution part, required exception -- exception, optional end; -- the required stored procedure create procedure name (which can be written to the parameters to be received) is -- declares some beginexceptionend; uses the begin name (parameter) end; to use the stored procedure function create function name (variable in type) return value type is to define the variable begin select from return an end value to be returned; if the variable and constant are declared as constants, you must add the constant definition method after the variable name: variable name constant identifier variable data type [not null]: = (: = used to assign values )...... in addition, the constant value cannot be modified. Similarly, the final variable name in java must be less than 30 characters, and the first character must be the other letters. It can be a letter, digit, or underscore # v _ is used before a variable. c _ is used before a constant to identify a variable. The variable name and field name cannot be the same, and the variable name v_ename emp cannot be the same. ename % TYPE indicates that the defined TYPE is the same as that in the parameter list of stored procedures and functions. For example, number cannot be written as number (5, 2). In addition, in out and In out modes In the parameter list, in can be directly indicated by the begin name ('','', '') end; if the out and in out modes are used, you must assign values to the variables and put them in brackets to implement the real parameter declaremmm number (5, 3): = 100; begin name (mmm); end; otherwise, an error will be reported, because this 100 may need to be modified in the process or function, so a bucket is required, so a variable is required. When calling a stored procedure for insert, if the parameter list contains three a, B, c and a has a default value of 123. So when I pass a value to this function, I will use the decleremm number (5, 2): = 10 in the last two; nn number (5, 2): = 10; begininsert (B => mm, c => nn) end; at this time, we didn't input a, but he still defaults to 123, then B and c are assigned the values of mm and nn drop function name --- delete function exercises -- 1 create or replace procedure ADD_EMP_09 (v_empNo emp1.EMPNO % type, v_eName emp1.ENAME % type default 'unknown ', v_sal emp1.SAL % type default 2000, v_hireDate emp1.HIREDATE % Type) asbegin insert into emp1 (empno, ENAME, SAL, HIREDATE) values (v_empNO, v_eName, v_sal, v_hireDate); end; begin ADD_EMP_09 (7988, 'nnmmk ', 2332, sysdate); end; begin ADD_EMP_09 (v_empNo => 7548, v_hireDate => sysdate); end; -- 2 create or replace function UPD_EMP_09 (v_empNo in emp1.EMPNO % type, v_sal in emp1.SAL % type) return emp1.ENAME % type is v_ename emp1.ENAME % type; begin update emp1 set sal = v_sal where Empno = v_empNo; select ename into v_ename from emp1 where empno = v_empNo; return v_ename; end; begin dbms_output.put_line (UPD_EMP_09 (72.16,99999); end; -- 3 create or replace function GET_ENAME_09 (v_empNo in emp1.EMPNO % type) return emp1.ENAME % type is v_ename emp1.ENAME % type; begin select ename into v_ename from emp1 where empno = v_empNo; return v_ename; end;/begin dbms_output.put_line (GET_ENAME_09 (736 9); end; Use show erro to view the error message. This exercise contains the usage of the out mode. Take a closer look at the create or replace function DUTY (v_empno emp. empno % type, v_ename out emp. ename % type) return emp. sal % type is v_duty emp. sal % type; begin select ename, sal * 0.05 into v_ename, v_duty from emp where empno = v_empno; return v_duty; end; declare empno emp. empno % type: = 7499; ename emp. ename % type; begin dbms_output.put_line (ename | ''| DUTY (empno, ename); end; exception when no _ Data_found then v_empname: = 'no data' when too_many_rows then v_empname: = 'too define rows 'defines a type. The type name is record (ename varchar2 (30), job emp. job % type); this can be defined in the variable definition, and can be used as a type by new variables, for example: Name type name; defines the use of such a variable ..... select ename, job into type name from emp dbms_output.put_line (type name. ename | type name. job );..... keep in mind that the data type of the form parameter cannot have a length. For example, if you cannot write the data as number (30), you only need to write the number and then use % ROwTYPE to declare the method variable: dept_record dept. % Rowtype is consistent with the usage method defined by type, it only contains all the fields in the Table. SQL % ROWCOUNT can be used to obtain the number of rows affected by the last statement if then ..... elsif then ..... else ..... end if; case when v_aa = 'A' then .....; (used when a sentence is assigned or printed; ended) when v_bb = 'B' then .....; else ......; end case; loop ........ exit whenend loop; this is similar to while in java. It is executed once for I in 1 .. 10 loop increments ...... end loop; meaning of descending reverse for I in reverse 1 .. 10 decrease loop ...... end loop; while v_nu M <11 loop v_num: = v_num + 1; dbms_output.put_line (v_num); end loop; exit loop condition exit when ..... or if... then exit two points of attention: the division operation between integers is automatically rounded... Note This. There are also a few dbms_output.put (); after things are used together, you must add a last sentence dbms_output.put_line (''); otherwise, if no data is returned when the set function is not used, it is also possible to return a piece of data with a value of 0. For example, if you use count (*) to customize the cursor name is select from to open the open cursor name, the custom cursor must be opened. Close the cursor name fetch the cursor name into field, field, the field can define a table % rowtype and then into this definition of rowtype can also put data in. The cursor pointer does not move down when it knows the last data. If it continues printing, it only prints the last record. The cursor name % isopen determines whether the cursor is opened. Cursor name % notfound returns true if no data exists. cursor name % found returns true if data exists. cursor name % rowcount Returns number of rows in the result set cursor for loop cursor name is select frombegin for dept_c in cursor name loop ..... end loop; end; here, dept_c can not be defined if it is a full table structure; otherwise, a new one needs to be defined. This is called an implicit test cursor and you do not need to write it to open or close the cursor. The name of the cursor with parameters () is ......; when the cursor is opened, if the parameter is a for loop, the parameter is passed in the for loop to lock the record line: add a for update statement after the complete select statement so that these records are locked. As long as you have not submitted the statement, the record cannot be changed elsewhere. Selectfromorder byfor update: if both parties lock this record, an exception is thrown. First, locate the data, lock the data, and then update emp set sal = sal * 11 where exception handling during compilation exception running exception: -- two exceptions can be followed after when, use or to connect -- when others then to catch all other exceptions -- only one program block is allowed and placed at the bottom. -- No_data_found -- no data is returned -- too_many_rows -- returns multiple rows of data -- dup_val_on_index -- primary key repetition exception -- zero_divide -- attempts to divide by zero -- invalid_number -- fails to convert the string to a number: create or replace procedure DDDD (v_empno emp. empno % type) is aaa emp. empno % type; e_a exception; -- defines an exception begin select ename into aaa from emp where empno = v_empno; if aaa = 'ss' then raise e_a; -- raise is the exception-triggered statement end if; exception when e_a then .......; end; if there is no predefined name Only the error code can use the following method pragma exception_init (your own previously defined exception name, error code); -- when this error code occurs, go to the following when to capture the Exception name you defined. Sqlcode: Used to get the error code sqlerrm: Used to get the error message custom exception message: -- raise_app ........ now let's take out the in and end and nest it. If an exception occurs in the internal declaration part or an exception thrown in the internal exception, that is, the declare exception occurs, or the exception thrown by raise in the exception will only go to the outer layer to solve the exception and will not handle the exception in the internal layer: create or replace package name is declare function or variable etc procedure process name (...); end; the package name can be used outside. the process name/function name to call the header is public and can be used outside, but the package body does not work. Package body: create or repalce package body package name is the stored procedure function written here .. End package name; it is worth noting that the declared items in this package body are private packages that can be used to implement the overload function. The overload must have different parameter types or numbers. Note that char and varchar are of the same series and cannot be used as different types. drop package name Delete Baotou and package body drop package body package name Delete package body