Oracle12 PL/SQL

pl/SQL Block Introduction  block isthe Basic program unit of PL/sql, the writing PL/SQL program is actually writing PL/SQL block, in order to complete the relatively simple application function, it may only need to write a PL/SQL block, However, if you want to implement complex functionality, you may want to nest additional pl/SQL blocks in a PL/SQL block.  block Structure PL//* Definition section--Define constants, variables, cursors, exceptions, complex data types */*  Execution section-the PL/SQL statement to be executed and the */* Exception Handling section--Various errors in handling the operation * /

instances of PL/SQL blocks (1) Example 1-Includes only the pl/of the execution partSQL block SQL code1.SetServeroutput on--Open Output Options2. Begin3. Dbms_output.put_line ('Hello'); 4. End; Description: Dbms_output is a package provided by Oracle (Java-like development package, Put_Line is a function), the package contains a number of procedures, put_line is a process of dbms_output package (below the package is a function or process). PLExample of a/sql block (2) Example 2-pl/that contains the definition section and the execution partSQL block SQL code1. Declare2. V_ename VARCHAR2 (5); --Defining string Variables3. Begin4.SelectEname into V_ename fromEmpwhereempno=&AA; 5. Dbms_output.put_line ('Employee Name:'||v_ename); 6. End; 7./

If the salary is to be shown, then the executive part should write this: SQL code declare v_ename varchar2 (5); v_sal number (7,  2)Select ename,sal to V_ename,v_sal  from emp where empno=&

instances of PL/SQL blocks (3) Example 3-contains the definition section, the execution section and the Exception handling section in order to avoid PL/sql program run error, improve pl/The robustness of SQL, it is necessary to deal with possible errors. 1For example, in instance 2, if you enter an employee number that does not exist, you should do the exception handling. 2Sometimes there is an exception and I want to handle it with another logic, [screen] Let's see how we can do 1 of the requirements. Description: Oracle has pre-defined some exceptions, No_data_found is the exception to the data that is not found. SQL code1. Declare2. --Defining Variables3. V_ename VARCHAR2 (5); 4. V_sal Number (7,2); 5. Begin6. --Executive Section7.SelectEname,sal into V_ename,v_sal fromEmpwhereempno=&AA; 8. --Display the user name in the console9. Dbms_output.put_line ('the user name is:'|| v_ename| |'Salary:'||v_sal); Ten. --Exception Handling One. Exception A. When No_data_found then -. Dbms_output.put_line ('my friend, you entered the wrong number! '); -. End;  the./

17: PL/SQL Classification--procedures, functions, packages, trigger process procedures are used to perform specific operations, and when the process is established, you can specify both the input parameters (inch), or you can specify an output parameter ( out), you can pass data to the execution section by using input parameters in the process, and you can pass the execution part of the data to the application environment (Java program) by using the output parameters. You can use the CREATE PROCEDURE command in Sqlplus to establish the process. Examples are as follows:1Please consider writing a procedure that allows you to enter an employee's name, a new salary, and modify the employee's salary.2There are two ways to invoke a procedure; EXEC call3How to call a stored procedure problem in a Java program: How do I use a procedure to return a value? Special note: For the process we will give you a detailed introduction in the future, now please everyone first has a concept. 1. Writing a process: CREATE PROCEDURE Sp_pro3 (spname varchar2, newsal number) is--Do not write number (3,2), indicating that the type is available and does not require a size. It's like a Java write method with the same parameters as the SQL code1. Begin2. --Execute section, modify salary according to user name3. Update empSetSal=newsalwhereEname=spname; 4. End; 5./  2. Calling procedure: Exec Sp_pro3 (' Scoot '4678);

3calling a stored procedure in a. Java program//Demo Java program to invoke Oracle's stored procedure caseJava Code1. Import java.sql.*; 2. Publicclasstestoraclepro{3. Public StaticvoidMain (string[] args) {5.Try{   6.//1. Load Driver7. Class.forName ("Oracle.jdbc.driver.OracleDriver"); 8.//2. Get Connected9. Connection ct = drivermanager.getconnection ("Jdbc:oracle:[email protected]:1521:myora1","Scott","m123");  One.//3. Create CallableStatement A. CallableStatement cs = Ct.preparecall ("{call Sp_pro3 (?,?)}");  -.//4. Assigning Values to -. Cs.setstring (1,"SMITH");  the. Cs.setint (2,Ten);  -.//5. Implementation -.   Cs.execute ();  -.//Close +.   Cs.close ();  -.   Ct.close ();  +. }Catch(Exception e) { A.   E.printstacktrace ();  at. }    -. }   } 

Oracle12 PL/SQL