best book for pl sql programming

can have multiple return values through an out-type parameter implementation--Requirements: Define a function: Gets the sum of the payroll for a given department and the total number of employees in that department (defined as an out type parameter).Requirement: The department number is defined as a parameter and the payroll is defined as the return value.Run:You can use the default keyword to set the defaults for input parameters when declaring a function parameter in the Create OR replacefunc

Exception handling:Even if a good Pl-sql program encounters errors or unforeseen events, a good program should be able to handle all kinds of error situations and recover as much as possible from the error. An error that occurs while the program is running becomes an exception. After an exception occurs, the statement terminates execution, and Plsql immediately gives control to the Plsql exception handling

Tags: Comment Query Select Bin control cannot else Eve index1. PL/SQL combines the language of the process language and the query language, which is to add the features of the programming language to SQL, and to realize complex functions or computations through the operation of logic judgment and loop.[declare] -- decl

ondeclare vnum number;begin vnum:=1/0; --handling Exception exception when zero_divide then Dbms_output.put_line (' exception: by 0 '); end;/Operation Result:10. Custom ExceptionsSet Serveroutput on;--Custom Exception declare -declares an exception type my_exception exception; begin -Throws an exception raise My_ Exception; Exception --Exception handling when My_exception and then Dbms_output.put_line (' Custom exception '); end;Operation Result: Copyright NOTICE: This arti

PL/SQL Basic structurePL/SQL data type Numeric type: Number (p,s), Pls_integer, Binary_integerCharacter types: CHAR, NCHAR, VARCHAR2, NVARCHAR2, LONGDay Type: DateBoolean Type: BooleanDefine data type: typeType Data type: Oracle allows you to define Recode, TABLEPL/SQL Constants and variablesvariable is initialized to

(tablenameinch varchar2, Curpageinch Number, Pagerownuminch Number, ORDERFLGinch varchar2, Totalrownum out Number, Totalpagenum out Number, res_cursor out Sp_pack_pagebycursor.p_cursor) is--defining SQL statement variablesV_sqlvarchar2( +);--define the query start subscriptV_begin_rownum Number:=(Curpage-1)*Pagerownum+1;--The following table defines the end of the queryV_end_rownum Number:=Curpage*Pagerownum;--Executionbegin--define database query

=spno;End-----3. How to call in Java---1. Create CallableStatement CS =ct.preparecall ([Call Sp_pro9 (?,?)]);----Cs.setint (1,10);----Cs.registeroutparameter (2,oracle.jdbc.oracletypes.cursor);--run--cs.execute ();--Get the result set/*resultset rs= (ResultSet) Cs.getobject (2);while (Rs.next ()) {....}*/---------------------Exception Handling---------Case_not_foundData_not_foundCursor_already_openDup_val_on_index Unique index repeatedInvaild_cursor run operation on an illegal cursor for example

Loop structure:1, Loop loop structureGrammar:LOOPThe statement to execute;Exit when END LOOP;Example: An integer that loops out 1-10DECLARE Number: = 1; BEGIN LOOP dbms_output.put_line (v_num); V_num:=+1; EXIT when Ten ; END LOOP; END;The drawback of loop structure: The structure is not clear enough, exit easy to write.2. While loop structureGrammar:While The statement to execute;END LOOP;Example: Displaying an integer of 1-10;DECLARE Number: = 1; B

Label: 1Create or replace procedure Pr_mytest is 2 3 4 5V_test Number (8) :=123; 6V_char VARCHAR2 (Ten):='Database'; 7C_CHANGL Constant Number ( A,2) :=3.14; 8V_BM t_hq_ryxx.bum%type; 9Type Ty_ry isRecord--a record type in a composite type that defines a new record typeTen ( OneV_XINGM t_hq_ryxx.xingm%type, AV_XINGB t_hq_ryxx.xingb%type, -V_gongz Number (Ten,2) - ); the -V_ry Ty_ry; --variables that declare this type of record - -V_ryrow T_hq_ryxx%rowtype; --all fields of the table +

match.Then I wrote an example:CREATE OR REPLACE PROCEDURE give_bonus (emp_no_id in number, bonus in number) AUTHID Current_userisbegin dbms_output.Put_Line (emp_no_id); Dbms_output. Put_Line (bonus); END give_bonus;/This is followed by the use of the case expressiondeclarel_salnumber; beginselectsalintol_salfromemp whereempno=7839;dbms_output. Put_Line (l_sal); give_bonus (7839, CASE whenl_sal>=1000andl_salSo it's actually an expression, and then the expression gets a result.After the introduct