Oracle (3) Basic stored procedures, functions, packages, and triggers for pl/SQL programming

Oracle (3) pl/SQL programming basic stored procedures, functions, packages, triggers PLSQL programming (1) 1. Procedures, functions, and triggers are compiled by pl/SQL. 2. Procedures and function triggers are in oracle. 3. pl/SQL is a very powerful database process language. 4. Procedures, functions can call www.2cto.com PL/SQL in java programs. Advantages: a. Improve application performance B. modular design concept [paging process, order process, transfer process...] c reduce the amount of network transmission d improve security (for example, writing a password directly in java) Disadvantages: Poor portability (rewrite the process for changing databases) Case 1; 1. create table mytest (name varchar2 (30), passwd varchar2 (30 )); 2 create or replace procedure sp_pro1 isbegin during creation -- execute partial insert into mytest values ('kill', '20140901'); end; www.2cto.com replace: indicates how to check the error message show error 3 and call this process if sp_pro1 exists (two methods) a exec process name (parameter value 1, parameter value 2 ...); B call process name (parameter value 1, parameter value 2 ...); if there is no parameter value, it is the exec process name/call process name Case 2; create or replace procedure sp_pro2 isbegindelete from mytest where name = 'ker'; end; C. compile comments for a comment in a single line -- select * from emp where empno = 7788; -- obtain comments for multiple lines of employee information /*.... */to divide the naming rules of the B logo. 1) when defining a variable, we recommend that you use v _ as the prefix v_sal1. when defining a constant, we recommend that you use c _ as the prefix c_rate1) _ Cursor as the prefix emp_cursor1) when defining exceptions, we recommend that you use e _ as the prefix e_error www.2cto.com D. block introduction blocks are the basic program units of pl/SQL. Compiling pl/SQL programs is actually compiling pl/SQL blocks. To complete relatively simple application functions, you may only need to write one pl/SQL block. However, if you want to implement complex functions, you may need to embed a pl/SQL block in a pl/SQL block. E. The block structure pl/SQL block consists of three parts: the definition part, the execution part, and the exception handling part. As follows: declear/* definition section-defining constants, variables, cursors, exceptions, complex data types */begin/* Execution Section-pl/SQL statements to be executed and SQL statements */exception/ * Exception Handling part-handle various running errors */end; note: The definition part starts with declare. This part is optional and starts with begin. This part is required for exception handling and starts with exception, this part is optional case 2 -- simplest block begindbms_output.put_line ('hello, World'); end; www.2cto.com set serveroutput on -- enable the output option set serveroutput off -- disable the output option case 3 -- block with definition and execution -- Define the variable v_ename varchar2 (5 ); v_sal number (); begin -- execute part select en Ame, sal into v_ename, v_sal from emp where empno = & aa; -- display the user name dbms_output.put_line on the console ('user name is: '| v_ename |' salary: '| v_sal); -- Exception Handling exceptionwhen no_data_found thendbms_output.put_line ('Friend, your number is incorrect! '); End; * Description: & indicates that the variable F. simple classification | ---- process (Stored procedure) | ---- function block (programming) ----- | ---- trigger | ---- package process: Case 4 create procedure sp_pro3 (spName varchar2, newSal number) isbegin www.2cto.com -- execute part, modify the salary update emp set sal = newSal where ename = spName; end; exec sp_pro3 ('Scott ', 4678) according to the user name ); how to call a stored procedure public class TestOraclePro {public static void main (String [] args) {try {// 1. load the driver Class. forName ("oracle. jdbc. driver. O RacleDriver "); // 2. connect to Connection ct = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: hs", "system", "123456"); // 3. create CallableStatement cs = ct. prepareCall ("{call sp_pro3 (?,?)} "); // 4. assign a value to cs. setString (1, "kyle"); cs. setInt (); // execute cs.exe cute (); // disable cs. close (); ct. close ();} catch (Exception e) {e. printStackTrace ();} www.2cto.com} function: the function is used to return specific data. When a function is created, the return statement must be included in the function header, the function body must contain the data returned by the return Statement -- function case -- enter the employee's name and return the employee's annual salary create function sp_fun2 (spName varchar2) return number is yearSal number ); begin -- execute part select sal * 12 + nvl (comm, 0) * 12 into yearSal from emp where ename = SPNs Ame; return yearSal; end; call function SQL> var abc numbersql> call sp_fun2 ('Scott ') into: abc; SQL> print abc in sqlplus; call the select annual function in a java program. income ("SCOTT") from dual; you can use rs. getInt (1) returns the result package used to logically combine processes and functions. It consists of the package specification and package body. A. you can use the create package command to create a package: instance: www.2cto.com -- create package -- create a package sp_package -- declare that the package has a process update_sal -- declare that the package has a function annual_incomecreate package sp_package isprocedure update_sal (name varchar2, newsal number ); function annual_income (name varchar2) return number; end; the specification of the package only contains the description of the process and function, but there is no implementation code of the process and function. The package body is used to implement the process and functions in the package specification. B. you can run the create package body command to create or replace package body sp_package isprocedure update_sal (name varchar2, newsal number) for the sp_package ); isbeginupdate emp set sal = newsal where ename = name; end; function annual_income (name varchar2) return number isannual_salary number; begin www.2cto.com select sal * 12 + nvl (comm, 0) into annual_salary from empwhere ename = name; return annual_salary; e Nd; end; c. how to call the package process or function when calling the package process or function requires a package name before the process and function. If you want to access the packages of other solutions, you also need to add the solution name before the package name. SQL> call sp_package.update_sal ('Scott ', 1500); trigger refers to the stored procedure of implicit execution. When defining a trigger, you must specify the events and actions that are triggered. Common trigger events include the insert, update, and delete statements. The trigger operation is actually a pl/SQL block. You can use create trigger to create a trigger. Prepared by kyle8525_nsn