Oracle Database PLSQL learning summary, oracleplsql

Oracle Database PLSQL learning summary, oracleplsql
PLSQL Introduction

PLSQL is an extension of SQL99 in Oracle., Basically every database will expand SQL. Oracle's extension to SQL is called PLSQL...

What is SQL99?

(1)Is the rule for operating all relational databases(2) is the fourth generation language (3)Is a structured query language.(4) The corresponding results are displayed only by issuing valid and reasonable commands.

SQL features

(1)Highly Interactive and non-procedural(2) Strong database operation capabilities. You only need to send commands and do not need to pay attention to how to implement (3) Multi-Table operations, automatic navigation is simple, for example:

select emp.empno,emp.sal,dept.dnamefrom emp,deptwhere emp.deptno = dept.deptno

(4) Easy to debug, error prompt, direct (5)Results highlighted by SQL

What is PLSQL?

Is dedicated to Oracle servers, based on SQL,Added some procedural control statements called PLSQLThe process includes: Type Definition, Judgment, loop, cursor, exception or exception handling...PLSQL stresses the process

Why PLSQL?

Because SQL is the fourth generation of imperative language,Unable to display and process the business, so we must use a procedural programming language to make up for the shortcomings of SQL, SQL and PLSQL are not alternative relationships, but complementary relationships

PLSQL syntax

Both declare and exception can be omitted, and begin and end;/cannot be omitted.

[Declare] variable Declaration; begin DML/TCL operations; [exception] exception handling; exception Handling; end ;/

In PLSQL:; Number indicates the end of each statement,/indicates the end of the entire PLSQL Program

What is the difference between PLSQL and SQL Execution:

(1) A single SQL statement is executed. (2) PLSQL statements are executed as a whole. A single SQL statement cannot be executed. The whole PLSQL statement ends with/, and each statement ends with a number.

PLSQL Variables

Since PLSQL focuses on the process, the program to write the process must have the basic syntax. First, let's introduce the PLSQL variables.

There are four types of PLSQL Variables

Number Varchar2 Same as column name TypeThe column type is the same as that of the entire table.

Write a PLSQL program and output the "hello world" string. Syntax: dbms_output.put_line ('output string'); begin -- output the string dbms_output.put_line ('Hello you') to the SQLPLUS client tool '); end;/Note: dbms_output is an output object in oracle. put_line is a method of the preceding object. It is used to output an automatic line feed setting of a string to display the execution result of the PLSQL program. By default, the execution result of PLSQL is not displayed. Syntax: set serveroutput on/off; set serveroutput on; use basic type variables, constants, and comments, evaluate the sum of 10 + 100 and declare -- Define the variable 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 the name and salary of employee No. 7369, in the following format: employee name No. 7369 is SMITH and salary is 800. Syntax: use the table name. field % typedeclare -- defines two variables: Name and wage pename emp. ename % type; psal emp. sal % type; begin -- SQL statement -- select ename, sal from emp where empno = 7369; -- PLSQL statement, put the value of ename into the pename variable, select ename, sal into pename, psal from emp where empno = 7369; -- output dbms_output.put_line ('employee name of employee No. 7369 is '| pename |', salary is '| psal); end;/output employee name and salary of employee No. 7788, the format is as follows: employee No. 7788 is SMITH and his salary is 3000. Syntax: Use table name % rowtypedeclare emp_record emp % rowtype; begin select * into emp_record from emp where empno = 7788; dbms_output.put_line ('employee name No. 7788 is '| emp_record.ename |', salary is '| emp_record.sal); end ;/

When to use % type and % rowtype?

When defining variables,The type of this variable is the same as the type of a field in the table. You can use % typeWhen defining a variable, % rowtype can be used when the variable is completely the same as the entire table structure.

Judgment body

Syntax:

It is worth noting that:EslifThere is no error, it is missing an e

Use if-else-end if to display the day of the week, whether it is "workday" or "Rest day" declare pday varchar2 (10); begin select to_char (sysdate, 'day ') into pday from dual; dbms_output.put_line ('Today is '| pday); if pday in ('satur', 'sunday') then dbms_output.put_line ('day '); else dbms_output.put_line ('workday'); end if; end;/receives the value from the keyboard. Use if-elsif-else-end if to display "age <16 ", "age <30", "age <60", "age <80" declare age number (3): = & age; begin if age <16 then dbms_output.put_line (' '); elsif age <30 then dbms_output.put_line (' '); elsif age <60 then dbms_output.put_line '); end if; end ;/

Loop

In PLSQL, there are three types of cyclic Syntax:

WHILE loop:

While is followed by a LOOP condition, similar to java, LOOP and end loop are keywords **

WHILE  total  <= 25000  LOOP    total : = total + salary;END  LOOP;

LOOP:

Exit the loop only after the exit condition is set]

Loop exit [when condition is true]; total: = total + salary; end loop;

FOR Loop:

The increment of a loop can only be 1, and the step size cannot be customized.

For I IN 1 .. 3 LOOP statement sequence; END LOOP;

Use loop to display 1-10declare I number (2): = 1; begin loop -- exit loop exit when I> 10 when I> 10; -- output I value dbms_output.put_line (I); -- variable auto-increment I: = I + 1; end loop; end;/use 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;/Use A while loop, insert 999 records into the emp table: declare I number (4): = 1; begin while (I <1000) loop insert into emp (empno, ename) values (I, 'haha '); I: = I + 1; end loop; end; // use the while loop to delete 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; // use the for loop to display 20-30declare I number (2 ): = 20; begin for I in 20 .. 30 loop dbms_output.put_line (I); end loop; end ;/

Cursor

The cursor in Oracle is similar to the resultSet in JDBC,Is a pointer Concept.

Since it is similar to resultSetThe cursor is only valid during query..

Syntax

CURSOR name [(Parameter Name Data Type [, parameter name data type]...)] is select statement;

Use cursor without parameters to query the names and salaries of all employees. If you need to traverse multiple records, use cursor with the cursor, no record found using cemp % notfound] declare -- defines the cursor cemp is select ename, sal from emp; -- defines the variable vename emp. ename % type; vsal emp. sal % type; begin -- open the cursor, then the cursor is located before the first record open cemp; -- loop -- move the cursor down one fetch cemp into vename, vsal; -- exit the loop, when the cursor moves down once and the record cannot be found, exit the loop exit when cemp % notfound; -- output result dbms_output.put_line (vename | '--------' | vsal); end loop; -- close the cursor close cemp; end;/use the parameter cursor to query the employee name and salary 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 (& deptno); loop fetch cemp into pename, psal; exit when cemp % notfound; dbms_output.put_line (pename | 'salary: '| psal); end loop; close cemp; end; // Use cursor without parameters to increase the salary of employees. ANALYST increases by 1000, MANAGER increases by 800, and other increases by 400. The 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; -- the exit condition must be set to exit when cemp % notfound; if pjob = 'analyst' then update emp set sal = sal + 1000 where empno = pempno; elsif pjob = 'manager' then update emp set sal = sal + 800 where empno = pempno; else update emp set sal = sal + 400 where empno = pempno; end if; end loop; commit; close cemp; end ;/

Exceptions

As we can see above, the syntax in PLSQL already knows that there is an exception, which is called an exception in Oracle. We can also simply think of it as an exception in Java...

Syntax:

Define the exception out_of exception in the declare section; throw the exception raise out_of In the feasible statement in the in section; process the exception when out_of then… In the exception Section...

With the built-in exceptions of the oracle system, this example shows [zero_pide] declare myresult number; begin myresult: = 1/0; dbms_output.put_line (myresult) except 0 ); exception when zero_pide then dbms_output.put_line ('divisor cannot be 0'); delete from emp; end; // use the oracle built-in exception to query the employee name of Department 100, no data [no_data_found] declare pename varchar2 (20); 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 ('Do not have employees in this department '); insert into emp (empno, ename) values (1111, 'error'); end ;/

Stored Procedures and functions

In Oracle, the concept of stored procedures is similar to that of stored functions. Generally, we can use them together. However, sometimes the use process is better, and sometimes the function is better. The following describes when to use the function.

First, before learning stored procedures and functions, we must understandWhy do you want to learn him?....

In fact, stored procedures and functions areSimilar to the concept of functions in Java....

So far, PLSQL has the following Disadvantages:

PLSQL cannot encapsulate it,Copy the entire piece of code to call each call.Sometimes, we want to save the PLSQL code,You can only manually save it to the hard disk.It is very troublesome for us to learn the database so that the program can be called,But PLSQL cannot allow the program (java) to call

Therefore, stored procedures and stored functions can solve the above problems. They can encapsulate the code and store it in the database so that the programming language can be called ....

Stored Procedure and function syntax

Process Syntax:

Create [or replace] procedure process name [(parameter list)] as PLSQL program body; [begin... End ;/]

Function Syntax:

CREATE [or replace] FUNCTION name [(parameter list)] RETURN Value Type ASPLSQL subroutine body; [begin... End ;/]

Whether it is a process or a function, the as keyword replaces the declare keyword.

The first process of creation:

CREATE OR REPLACE PROCEDURE helloAS  BEGIN    dbms_output.put_line('hello world');  END;

Three methods of calling process:

Exec process name [used in SQLPLUS] PLSQL program callJava call

PLSQL call

BEGIN  hello();END;

Creates a parameter Stored Procedure raiseSalary (number), which increases the salary by 7369 for employees on the 1949th. This shows the usage of in. The default value is in, which is case insensitive.

CREATE or REPLACE PROCEDURE bb(pempno in NUMBER)  AS  BEGIN    UPDATE EMP    SET sal = sal * 1.2    WHERE empno = pempno;  END;

Call:

  BEGIN    bb(7369);  END;

Create the findEmpNameAndSalAndJob (number) for the stored procedure, query the name, position, monthly salary of the employee No. 7788, and return multiple values to demonstrate the out usage.

Creation process:During the process, the default value is IN. If it is output, we need to specify it as OUT.

CREATE OR REPLACE PROCEDURE find(pempno IN NUMBER, psal OUT VARCHAR2, pename OUT VARCHAR2, pjob OUT VARCHAR2)AS  BEGIN    SELECT      ename,      sal,      job    INTO pename, psal, pjob    FROM emp    WHERE empno = pempno;  END;

Call: During the call, the psal, pname, and pjob used are not defined during the call. Therefore, weYou need to define the variable before using it!

DECLARE  psal   emp.sal%TYPE;  pename emp.ename%TYPE;  pjob   emp.job%TYPE;BEGIN  find(7369, psal, pename, pjob);  dbms_output.put_line(psal || pename || pjob);END;/

Create the parameter storage function findEmpIncome (number) to query the annual income of the 7369 employee. This shows the usage of in. The default value is in.

Create or replace function findEmpIncome (pempno in number) -- return numberas income NUMBER is specified here; begin select sal * 12 INTO income FROM emp WHERE empno = pempno; /* In PLSQL, you must have a return statement */RETURN income; END;

Call: In PLSQL, the value assignment statement is not "=", but: =

DECLARE  income number;BEGIN  income := findEmpIncome(7369);  dbms_output.put_line(income);END;/

If the number is =, the following error occurs:

[13:58:14] [65000] [6550] ORA-06550: 4th rows, 10th columns: PLS-00103: sign "=" when you need the following: =. (@ %; ORA-06550: 4th rows, 31st columns: PLS-00103: sign ";" when you need the following :. (), * % &-+/at mod remainder rem and or | multisetORA-06550: 7th rows, 4th columns: PLS-00103: when the "end-of-file" symbol appears, end not pragma final instantiable order overriding static member constructor map

Create the parameter storage function findEmpNameAndJobAndSal (number). query the name (return), position (out), and monthly salary (out) of employee No. 7788, and return multiple values.

Create or replace function findEmpNameAndJobAndSal (pempno in number, pjob OUT VARCHAR2, income out number) -- The returned value type return varcharas/* is the same as the column name, you can use the same column name type. */Pename emp. ename % TYPE; begin select sal, ename, job INTO income, pename, pjob FROM emp WHERE empno = pempno;/* a return statement must exist in PLSQL */RETURN pename; END;

Call the function:

DECLARE/* the output field is of the same type as the column name. */Income emp. sal % TYPE; pjob emp. job % TYPE; pename emp. ename % TYPE; BEGIN pename: = findEmpNameAndJobAndSal (7369, pjob, income); dbms_output.put_line (pename | pjob | income); END ;/

Use Cases of processes and functions

We found that the difference between a process and a function is actually not big. Generally, we can use a function to implement a process ....

However, in some cases, it is better to use a function than to use a function. when to use a function and when to use it ???

It is not hard to find that a function must have a return value. When we call a function, accept the return value and obtain it directly.

That is to say

When the return value has only one parameter, the storage function is used! When no or more parameters are returned, use the process!Use Cases of SQL and process functions

[Applicable] process functions:

"Needs to be stored in the database for a long time" needs to be repeatedly called by multiple users "business logic is the same, but the parameters are different" batch operation a large amount of data, for example: Batch insert a lot of data

[Applicable] SQL:

SQL can be used on the opposite side of table, view, sequence, index, etc. Trigger

In PLSQL, a filter similar to that in Java Web is a trigger... The trigger idea is almost the same as the Filter idea ....

It is worth noting that the trigger is not used for query operations. That is to say:The trigger only applies to delete, modify, and insert operations!

Trigger syntax

CREATE [or REPLACE] TRIGGER name {BEFORE | AFTER} {INSERT | DELETE | ----- statement-level update of column name} ---- ROW-level ON table name -- traverse each row of records [for each row] PLSQL block [declare... Begin... End ;/]

Create a statement-Level Trigger insertEmpTrigger. When you add the insert operation to the table "emp", "hello world" is displayed"

CREATE OR REPLACE TRIGGER insertempTirigerBEFOREINSERT  ON EMP  BEGIN    dbms_output.put_line('helloword');  END;

Call:

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (1, '2', '3', 4, NULL, NULL, NULL, 10);

Result:

From Monday to Friday, and data can be inserted into the database emp table at. Otherwise, an exception is thrown using the function,
Syntax: raise_application_error ('-20000', 'exception reason ')

Create or replace trigger securityTriggerBEFOREINSERT on emp declare pday VARCHAR2 (10); ptime NUMBER; BEGIN/* Get the day of the week */SELECT to_char (sysdate, 'day') INTO pday FROM dual; /* obtain the time */SELECT to_char (sysdate, 'hh24') INTO ptime FROM dual; IF pday IN ('satur', 'sunday ') OR ptime not between 7 AND 23 THEN RAISE_APPLICATION_ERROR ('-20000', 'non-work event, please come back for work time! '); End if; END;

Insert data and response triggers:

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (3, '2', '3', 4, NULL, NULL, NULL, 10);

Create a row-Level Trigger checkSalaryTrigger. The post-increment salary column must be higher than the pre-increment salary. Syntax: for each row/: new. sal/: old. sal

You can use: new. sal/: old. sal to compare the values before and after insertion.

Create or replace trigger checkSalTriggerBEFOREUPDATE OF sal on empfor each row begin if: new. sal <=: old. sal THEN RAISE_APPLICATION_ERROR ('-20001', 'your salary is too low !!!! '); End if; END;

Call:

UPDATE empSET sal = sal - 1WHERE empno = 7369;