oracle--stored procedures, cursors, functions, triggers

1.

Training requirements 1) Master Plsql Programming 2) Master the stored procedures,  Functions and Triggers 3) Learn some Oralcesql statement optimization scenarios-------------------------------------------------------------------------------------prepare COL Empno for 9999;col ename for A10;col job for A10;col Mgr for 9999;col hiredate for A12;col sal for 9999;col Comm for 9999 Col deptno for 99;col tname for A40;set pagesize;----------------------------------------------------------------- --------------------SQL vs. PLSQLSQL99 is what (1) is the rule that operates on all relational databases (2) is the fourth generation language (3) is a Structured Query Language (4) only need to issue legitimate and reasonable commands, There are corresponding results to show the characteristics of SQL (1) strong interactivity, non-process (2) database manipulation ability, just send commands, no need to pay attention to how to implement (3) multi-table operation, automatic navigation is simple, for example: select Emp.empno,emp.sal,dept.dname fro M emp,dept Where Emp.deptno = Dept.deptno (4) Easy to debug, error prompt, direct when (5) SQL emphasizes the result plsql is what is dedicated to the Oracle server, on the basis of SQL, added some procedural control statements,     Called Plsql process consists of: type definition, judgment, loop, cursor, exception or exception handling ... Plsql emphasizes why the process should be used plsql because SQL is the fourth-generation imperative language, it is not possible to display a process-oriented business, so a procedural programming language is used to compensate for the shortcomings of SQL, and SQL and plsql are not alternative relationships.    Is the complete composition of the Plsql program of the relationship: [DECLARE] variable declaration;     Variable declaration;  Begin DML/TCL Operation;     DML/TCL operation; [Exception] exception handling; exception handling;     End /Note: In the Plsql program, the number indicates the end of each statement,/means that the entire Plsql program finishes writing Plsql tools are: (1) sqlplus tools (2) Sqldeveloper tools (3) Third-party tools (Plsql & Other) What is the difference between plsql and SQL execution: (1) SQL is executed on a single line (2) Plsql is performed as a whole, cannot be executed in a single execution, the entire plsql ends with/, where each statement ends with , number------------------------------------------------------------------------------------plsql type write a plsql program, output " Hello World "string, Syntax: Dbms_output.put_line (' string to be output '); Begin--Output string Dbms_output.put_line to sqlplus client tool (' Hello hello '); end;/Note: Dbms_output is an output object in Oracle Put_Line is a method of the above object, used to output a string wrap setting to display the results of the PLSQL program, by default, does not show the results of Plsql program execution,    Syntax: Set serveroutput on/off;set serveroutput on; Use basic type variables, constants and annotations, 10+100 and declare--Define variables MySum number (3): = 0;    Tip VARCHAR2 (10): = ' result is '; Begin/* Business algorithm */mysum: = 10 + 100; /* Output to Controller */dbms_output.put_line (Tip | | mysum); end;/output No. 7369 employee name and salary, format as follows: Number No. 7369 The name of the employee is Smith, the salary is 800, syntax: Use the table name. Field%    Typedeclare-Define two variables, name and salary Pename Emp.ename%type respectively;    Psal emp.sal%type;begin--sql statement--select ename,sal from emp where empno = 7369; --plsql statement that puts the value of the enameInto the pename variable, the value of the SAL is placed in the PSAL variable in the Select Ename,sal into Pename,psal from emp where empno = 7369; --Output Dbms_output.put_line (' No. No. 7369 employee's name is ' | | pename| | ', Salary is ' | |    PSAL);  end;/Output number No. 7788 employee name and salary, in the following format: Number No. 7788 The name of the employee is Smith, the salary is 3000, syntax: Use the table name%rowtypedeclare emp_record Emp%rowtype;begin SELECT * into    Emp_record from emp where empno = 7788; Dbms_output.put_line (' The name of Employee No. No. 7788 is ' | | emp_record.ename| | ', Salary is ' | | Emp_record.sal); end;/when to use%type and when to use%rowtype? When you define a variable, the type of the variable is the same as the type of a field in the table, and you can use%type when you define a variable that is exactly the same as the entire table structure, you can use%rowtype, at which time the variable name is passed. Field name, which can be used to value the corresponding value in the variable item, commonly% Type------------------------------------------------------------------------------------Plsql to determine the use of if-else-end    If the day of the week is displayed, is "weekday" or "Rest day" declare pday varchar2; begin select To_char (sysdate, ' Day ') to pday from dual; Dbms_output.put_line (' Today is ' | |    Pday);    If Pday in (' Saturday ', ' Sunday ') thendbms_output.put_line (' Rest day ');    Elsedbms_output.put_line (' weekday ');    End if;end;/receive values from the keyboard, use If-elsif-else-end if to display "age<16", "age<30", "age<60", "age<80" declareAge Number (3): = &age;begin If < Dbms_output.put_line (' You are not an adult ');    Elsif Age < Dbms_output.put_line (' You young people ');    Elsif Age < Dbms_output.put_line (' You fight people ');    Elsif Age < Dbms_output.put_line (' you enjoy people ');    else Dbms_output.put_line (' not finished again '); End if;end;/-------------------------------------------------------------------------------------        Plsql Loop Displays 1-10declare I number (2) with loop loop: = 1;begin loop--when i>10 exits the loop exit when i>10;        --The value of the output I dbms_output.put_line (i);      --Variable self plus i: = i + 1;        End loop;end;/uses the while loop to display 1-10declare I number (2): = 1;begin while i<11 loop dbms_output.put_line (i);    I: = i + 1; End loop;end;/uses a while loop to insert 999 records into the EMP table declare i number (4): = 1;begin while (i <) loop Insert int        o emp (empno,ename) VALUES (i, ' haha ');    I: = i + 1;   End Loop; end;/using the While loop to remove 999 records from the EMP table declare I numbER (4): = 1;begin while i<1000 loop delete from emp where empno = i;    I: = i + 1; End loop;end;/uses a for loop to display 20-30declare I number (2): = 20;begin for i in 20.    Loop Dbms_output.put_line (i); End loop;end;/-------------------------------------------------------------------------------------plsql cursor What is a cursor /cursor/cursor similar to the functionality of the ResultSet object in JDBC, gets the contents of each record from top to bottom using the parameterless cursor, querying the names and wages of all employees "if you need to traverse multiple records, use cursor cursors, no records found using cemp%    NotFound "Declare--defines a cursor cemp is a select ename,sal from EMP;    --Define variable vename emp.ename%type;    Vsal Emp.sal%type;begin--Opens the cursor when the cursor is in the open Cemp before the first record;        -Loop Loop-moves the cursor down one fetch cemp into vename,vsal;       --Exit the loop, exit the loop when the cursor is not found when the record is moved down once cemp%notfound; --output result Dbms_output.put_line (vename| | ') --------' | |    Vsal);    End Loop; --Close the cursor closed cemp;end;/use the Cursors cursor to query the employee name and salary in department 10th DECLARE CURSOR cemp (pdeptno emp.deptno%type) is select Ename,sal from EMP where Deptno=pdeptno;    Pename Emp.ename%type; Psal Emp.sal%type;    Begin open Cemp (&AMP;DEPTNO);         Loop fetch cemp into pename,psal;        Exit when Cemp%notfound; Dbms_output.put_line (pename| | ' The salary is ' | |    PSAL);    End Loop; Close cemp;end;/cursors with no parameter cursor, real wage increases for employees, analyst 1000,manager up 800, others up 400, require display number, name, position, salary DECLARE cursor cemp is select    Empno,ename,job,sal from EMP;    Pempno Emp.empno%type;    Pename Emp.ename%type;    Pjob Emp.job%type;    Psal Emp.sal%type;begin Open cemp;        Loop fetch cemp into pempno,pename,pjob,psal;        --circular exit conditions must write exit when Cemp%notfound;        If pjob= ' ANALYST ' then update emp set sal = sal + empno = pempno;        elsif pjob= ' MANAGER ' then update emp set sal = Sal + where empno = Pempno;        else update emp Set sal = Sal + the WHERE empno = Pempno;    End If;    End Loop;    Commit Close cemp;end;/-------------------------------------------------------------------------------------PLSQL Exception using the Oracle system built-in exception, demonstrating the exception of 0 "Zero_divide" declare myresult number;begin myresult: = 1/0; Dbms_output.put_line (myresult); exception when Zero_divide then Dbms_output.put_line (' divisor cannot be 0 ');  Delete from EMP; end;/uses the Oracle system built-in exception to query the employee name in unit 100th, demonstrating no data found "No_data_found" declare pename varchar2; begin select Ename into Pename    From emp where deptno = 100; Dbms_output.put_line (pename); exception when No_data_found then Dbms_output.put_line (' No employee of the department '); INSERT into EMP (empno,ename) VALUES (1111, ' ERROR '); end;/use the user-defined exception, use the cursor cursors, query the employee name in the 10/20/30/100 department, show no data found " Nohave_emp_found "------------------------------------------------------------------------------------ Stored procedure Concepts What is a stored procedure "procedure"?    Why use stored procedures? (1) Plsql each execution to run the whole time, only the result (2) Plsql can not be encapsulated, long-term storage in the Oracle Server (3) Plsql can not be called by other applications, such as: what is the relationship between Java stored procedures and plsql? --------------------------------------------------------stored procedure to create a non-parametric stored procedure Hello, no return value, syntax: Create or Replace procedure procedure name As plsql program Delete stored procedure Hello, Syntax: drop procedure procedure name call stored procedure way one, EXEC stored procedure name calls stored procedure method two, PLSQL program calls stored procedure method Three, Java program creates the parameter stored procedure raisesalary (number), for No. 7369 employees up 10% of the wages, demonstration in the usage, default in, Case insensitive Create a parameter stored procedure findempnameandsalandjob (number), query number No. 7788 employee's name, position, monthly salary, return multiple values, demonstrate the use of out of what circumstances with the exec call, under what circumstances use Plsql call the stored procedure? With stored procedures, Write a function to calculate personal income tax-------------------------------------------------------------------------------------store function to create an getname stored function , there is a return value, syntax: Create or Replace function function name return returns type as Plsql program segment Delete stored function getname, Syntax: Drop function name Call storage function mode one, Plsql program calls stored function mode Two, Java program creates a parameter stored function findempincome (number), query No. 7369 Employee's annual income, demonstrate in usage, default in to create a parameter stored function findempnameandjobandsal (number) , check the name of Employee No. No. 7788 (return), Position (out), monthly salary (out), Returns multiple values-------------------------------------------------------------------------------------procedure functions are suitable for scenario declarations: suitable for not forcing you to use , is it just a priority to consider what is the "fit to use" stored procedure? What is the "fit to use" storage function? "Suitable for use" stored procedures: "Suitable for use" stored functions: What is "suitable for use" procedure function, what is "suitable for use" SQL? "Suitable for use" procedure function: "Need to be kept in the database for a long time" need to be repeated calls by multiple users "business logic is the same, but the parameters are not the same" batch operation of large amounts of data, such as: BULK insert a lot of data "suitable for use" SQL: " You can use SQL for tables, views, sequences, indexes, and so on, or SQL-------------------------------------------------------------------------------------trigger Oracle Common command: HOSTCLS clear screen rollback after rollback must perform commit commit show recyclebin; View Recycle Bin Flashback table EMP to before drop;--flash back drop table emp purge;--completely delete tables what is a trigger "Trigger"? Different DML (SELECT/UPDATE/DELETE/INSERT) operations, triggers can be intercepted and qualified to operate the base table, otherwise you cannot manipulate the base table, similar to the filter in Javaweb, Why do Struts2 's interceptor use triggers? If there is no trigger, then all DML operations can create a statement-level trigger Insertemptrigger without an unrestricted operation of the base table, and when the table "EMP" is added "before" before "insert", the "Hello World" create or Replace Trigger Insertemptriggerbefore Inserton empbegindbms_output.put_line (' Hello World '); end;/inserts a record using the INSERT statement, Causes Insertemptrigger trigger work INSERT INTO EMP (empno,ename,sal) VALUES (' 1111 ', ' Zhang San ', 7000),//insert triggers trigger, Print HelloWorld Using INSERT statement to insert n record, cause Insertemptrigger trigger work INSERT INTO EMP select * from xxx_emp; Delete Trigger Insertemptrigger, Syntax: Drop trigger Trigger Name drop trigger Insertemptrigger; Create statement-level trigger Deleteemptrigger, when "after" is deleted after "delete" operation on the table "EMP", display "world Hello "Create or replace trigger Deleteemptriggerafter deleteon empbegindbms_output.put_line (' Hello World '); end;/ Use the DELETE statement to delete a record, citingUp Deleteemptrigger trigger work delete from emp where empno = 1111;//Note It is supposed to show a deleted row first, and then show Hello World, the reality is reversed using the DELETE statement to delete N records, Cause Deleteemptrigger trigger work delete from EMP where 1=1; Monday to Friday, and 9-20 points can insert data into the Database EMP table, otherwise use function throws exception, syntax: Raise_application_error ('-20000 ', ' exception reason ') Create or replace trigger Securitytriggerbeforeinsertdeclarepday varchar2 (a);p hour number (2);-- The relational operation is best used in the digital Beginselect to_char (sysdate, ' Day ') into the pday from dual;  --Get the Week select To_char (sysdate, ' hh24 ') into the phour from dual;--get time if pday in (' Saturday ', ' Sunday ') or Phour not between 7 and then --Business Raise_application_error ('-20000 ', ' only working days and 7-23 points on weekdays to insert data ')--throw exception end flag test: INSERT INTO EMP (empno,ename , Sal) VALUES (' 2222 ', ' Zhang San ', 7000); --also prints HelloWorld because the insert trigger is still there. Create a row-level trigger Checksalarytrigger, the post-rise salary column, to ensure that it is greater than the pre-rise wage, syntax: for each row/:new.sal/:old.salcreate or replace trigger Checksalarytriggerafter update of Salon empfor each rowbeginif:new.sal <=: old.sal-If wages are not higher than before Throw exception Raise_application_error ('-20200 ', ' wages cannot rise and lower ')--throw exception end If;end;/update emp Set sal = Sal-1 wheRe empno = 7369; Delete trigger, is the table still there? Drop trigger Checksalarytrigger; The table is still dropping the table to the Recycle Bin, is the trigger still there?  drop table emp; Still in!show recyclebin; view Recycle Bin when the table is flashed back, will the trigger be in? Flashback table EMP to before drop;--Flash back, still in! Completely delete the table, will the trigger be there? drop table emp purge;--completely delete tables does not exist test: create a new table and deleted EMP with the same name CREATE TABLE EMP as SELECT * FROM Xxx_emp;select * from Emp;insert to E MP (Empno,ename,sal) VALUES (' 4545 ', ' small six sons ', 80000);----------------------------------------------------------------       ---------------------oraclesql Optimization Solution Why Oracle Optimization: As the actual project starts, Oracle has been running for a while, and the initial Oracle setup will be somewhat different from the actual Oracle performance, when we You need to make an optimization adjustment. Oracle Optimization This topic is large, can be divided into four categories: "host Performance" Memory Usage performance "Network transport performance" SQL statement execution Performance "programmer" listed below are some Oraclesql optimization scenarios: (01) Select the most efficient table name order (written Regular test) The Oracle parser processes the table names in the FROM clause in a right-to-left order, and the FROM clause is written in the last table that will be processed first, and in the case where the FROM clause contains more than one table, you must select the table with the fewest number of record bars to put in the last, if there are 3      The table above joins the query, then you need to select the table that is referenced by the other table at the end.      For example: Query employee's number, name, salary, salary level, department name select Emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname from Salgrade,dept,emp WHERE (Emp.deptno = Dept.deptno) and (Emp.sal between Salgrade.losal and salgrade.hisal) 1) If three tables are completely non-relational, write the table with the least number of records and columns, and then the last one, and then 2) if three tables are related, reference the most Many tables, placed at the end, and then so on the join order in the WHERE clause (written test) Oracle parses the WHERE clause from right to left, and according to this principle, the connection between tables must be written to the left of the other where conditions, which can filter out the maximum number of records        Must be written to the right of the WHERE clause. For example: Query employee's number, name, salary, department name select Emp.empno,emp.ename,emp.sal,dept.dname from emp,dept where (Emp.deptno = Dept      . Deptno) and (Emp.sal >) SELECT clause Avoid using the * number in the parsing process, the * will be converted to all column names, this work is done by querying the data dictionary, which means that it will take more time Select Empno,ename from emp; (04) Use the Decode function to reduce processing time using the Decode function to avoid duplicate scans of identical records or duplicate connections on the same table (05) consolidated simple, unrelated database access (06) replaced with truncate DELETE (07) Use Commit as much as possible because commit releases the rollback point (08) Replace the HAVING clause with a WHERE clause where executes first, having after having (09) using intrinsic functions to improve SQL efficiency (10) Use the alias of the table Salgrade s (11) Use the alias of the column ename E (12) using the index to improve efficiency in the query, use the index (13) string type, can use the = number, without the like because the = number means accurate comparison, lik E indicates that the fuzzy comparison (+) SQL statement is capitalized because the Oracle server always turns lowercase letters into uppercase before executing in eclipse, first lowercase letters, then ctrl+shift+x to uppercase; Ctrl+shift+y to lowercase (15) Avoid the index column OnWith not because the Oracle server encounters a not, he stops the current work and instead performs a full table scan (16) Avoid using the COMPUTE WHERE clause on an indexed column, and if the index column is part of a function, the optimizer will use a full table scan without an index, which will become slower      For example, the SAL column is indexed and inefficient: SELECT empno,ename from EMP WHERE sal*12 > 24000;  Efficient: Select Empno,ename from emp WHERE SAL > 24000/12; (17) Replace > Inefficient with >=: SELECT * from emp        where DEPTNO > 3 first navigates to deptno=3 records and scans to the first dept greater than 3 efficiency: SELECT * from EMP WHERE DEPTNO >= 4      Jump directly to the first dept equals 4 Record (18) with in Replace or select * from emp where Sal = $ or Sal = N or sal = 800; SELECT * from emp where Sal in (1500,3000,800); (19) Always use the first column of the index if the index is built on more than one column, the optimizer chooses to use the index only if its first column is referenced by a WHERE clause      When referencing the second column of an index, when the first column of the index is not referenced, the optimizer uses a full table scan and ignores the index create INDEX emp_sal_job_idex on EMP (sal,job);      ----------------------------------SELECT * from emp where job! = ' SALES '; (20) Avoid changing the type of indexed columns, display is more secure than implicit when comparing characters and numbers, Oracle takes precedence in converting numeric types to character types Select 123 | |          ' 123 ' from dual; In short, OraCLE optimization is not a day of the project, you have to work in the long-term practice, repeated testing and summary, I hope that students will be well understood in the future 

　　

oracle--stored procedures, cursors, functions, triggers