Shulou(Shulou.com)06/01 Report--

1. Brief introduction of PLSQL tool PL/SQL Developer: PL/SQL Developer is not only a tool dedicated to developing, testing, debugging and optimizing Oracle PL/SQL, but also a very easy-to-use Oracle management tool. Official website download address: https://www.allroundautomations.com/bodyplsqldevreg.html (like ordinary software installation) 2, the introduction of PLSQL a, what is PL/SQL? PL/SQL is a programming language called procedural SQL language (Procedural Language/SQL). PL/SQL is an extension of SQL statements to Oracle databases. The characteristics of programming language are added to the use of ordinary SQL statements. PL/SQL is a process-oriented language and PL/SQL is an extension of the SQL language.

-extension of SQL language to different databases:

Oracle:PL/SQL; SQL Server:Transac-SQL (T-SQL). B, what are the characteristics of PLSQL? PL/SQL is the core language of Oracle system, and now many parts of Oracle are written by PL/SQL. PLSQL is simple, efficient, flexible and practical. 3. The syntax structure of PLSQL, PL/SQL block (block): it is the basic program unit of PL/SQL. The PL/SQL block consists of three parts: the declaration part, the execution part and the exception handling part. Declaration part (DECLARE): declare variables, constants, complex data types, cursors, etc.; execution part (BEGIN): PL/SQL statements and SQL statements; exception handling part (EXCEPTION): handle execution errors. END;-- the block end tag. (the execution part is required, and the declaration section and exception handling section are optional. ) b, the first example of a PL/SQL program: print hello everyone!

1. Use the Command Window window: in the PLSQL Developer tool, use New-> Command Window in the menu bar to open the command line:

Enter the following on the open command line:

Note: when using the DBMS_OUPUT package to output information, you need to set the value of the SQL*Plus environment serveroutput to ON. (after using /, type enter to execute the PL/SQL program)

SQL > begin dbms_output.put_line ('hello everyone'); end; / PL/SQL procedure successfully completedSQL > set serveroutput onSQL > begin dbms_output.put_line ('hello everyone'); end; / hello everyone PL _ SQL procedure successfully completed

2. Use the SQL Window window: in the PLSQL Developer tool, use New-> SQL Window in the menu bar to open the command line:

Enter the following in the open command line: begin dbms_output.put_line ('hello everyone'); after end; type F8 to execute, the output window displays the print line: (you don't need to set the value of serveroutput as in the Command Window window)

Note that SQL Window windows can only execute PL/SQL or SQL code. (SQLPlus command cannot be executed) classified anonymous blocks of c, PL/SQL blocks: dynamically constructed, can only be executed once. (such as the first PL/SQL program on) subroutine: stored procedures, functions, packages, etc., stored in a database. They can be called in other programs when they are established on the database. Trigger: when an operation occurs in the database, some events are triggered and the corresponding program is executed automatically. 4. Variable type an and identifier of PLSQL when writing PL/SQL blocks, variables and constants need to be defined in order to store data temporarily. Then the definitions of variables and constants need to meet the restrictions on identifiers:

(1) the identifier name cannot exceed 30 characters; (2) the first character must be a letter; (3) it is not case-sensitive; (4) you cannot use'-'

(minus sign). Note: try not to declare the variable name the same as the field name in the table. B, variable naming method in order to improve the readability of the code, it is recommended to follow the following coding rules: identifier command rule example program variable v_namev_sal program constant c_namec_pi cursor variable name_curroremp_curror exception identifies e_namee_integrity_error record type name_recordemp_recordc, variable type numerical type: NUMBER (p, s) (used to define fixed-length integers and floating-point numbers) and subtypes INT, FLOAT, etc. Character type: CHAR (n) (fixed length string), VARCHAR2 (n). (long string) date type: DATE. Boolean type: BOOLEAN. (the Boolean type values are false,true and nul, which are PLSQL data types, and the columns in the table cannot use this type.) d, the case rules of variables when writing sql statements and PL/SQL statements, you can use either uppercase format or lowercase format. But for the readability of the program, the following rules should be followed as far as possible: the SQL keyword is in uppercase format, such as SELECT,UPDATE. PL/SQL keywords are in uppercase format, such as DECLARE,BEGIN,END, etc. The data type is in uppercase format, such as INT, DATE, etc. Identifiers and parameters are in lowercase format, such as v_sal, etc. Database objects and columns are in lowercase format, such as emp,sal, etc. E, single-line comments in PLSQL:-- comment content multiline comment: / comment content / f, PL/SQL program synthesis example use the SQL Window window, type the following code in the window: DECLARE v_name VARCHAR2 (10); v_sal NUMBER (7Magin2); v_hiredate DATE; c_tax_rate CONSTANT NUMBER (3L2): = 0.02; v_tax_sal NUMBER (7L2); v_valid BOOLEAN DEFAULT TRUE BEGIN SELECT ename, sal, hiredate INTO v_name, v_sal, v_hiredate FROM emp WHERE empno = 7369;-- calculate income tax v_tax_sal: = v_sal * centering tax paid;-- printout DBMS_OUTPUT.put_line (v_name | | 'salary is:' | | v_sal | |', employee date is:'| v_hiredate |', income tax is:'| v_tax_sal) F v_valid THEN DBMS_OUTPUT.put_line ('verified'); END IF;END; needs to note that the assignment of variables and constants in the PL/SQL program needs to use the ": =" method, while the assignment of data in the database needs to use the INTO keyword. Boolean type is a unique data type of PLSQL, so the data retrieved from the database can not be assigned to Boolean type. It is mainly used for logical judgment. 5. PLSQL reference variables and record variables a, why to use reference variables and record variables in many cases, PL/SQL variables can be used to store data in database tables. In this case, the variable should have the same type as the table column. Example: use the SQL Window window. DECLARE v_name varchar2 (10); v_sal number (7Power2); BEGIN SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7788;-print name and salary DBMS_OUTPUT.PUT_LINE (v_name | | 'salary is:' | | v_sal); END; in this case, it is recommended to use referenced variables (using% TYPE) or record variables (using% ROWTYPE) instead of hard-coding the variable types. B. reference variable reference variable: it means that its data type is the same as that of a defined data variable, or the same data type as a column of a database table. For example: DECLARE-- define the referential variable v_name emp.ename%TYPE; v_sal emp.sal%TYPE;BEGIN-- assign the name and salary of 7788 to the defined referential variable SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7788;-- print the name and salary DBMS_OUTPUT.put_line (v_name | | 'salary is:' | | v_sal); END C. The record variable PL/SQL provides the% ROWTYPE operator and returns a record type whose data type is consistent with the data structure of the database table. Example: record variable, emp_record emp%ROWTYPE;, reference to record variable component: emp_record.ename: = 'ROSE'; example: DECLARE-- define record variable emp_record emp%ROWTYPE;BEGIN-- assign 7788 of employee information to record variable emp_record SELECT * INTO emp_record FROM emp WHERE empno = 7788 -print name and salary DBMS_OUTPUT.PUT_LINE (emp_record.ename | | 'salary is:' | | emp_record.sal); operator an of END;6 and PLSQL, arithmetic operator meaning + plus sign-minus sign * multiplication sign / division sign * * multiplier example: BEGIN dbms_output.put_line (10 + 2); dbms_output.put_line (10-2); dbms_output.put_line (10 * 2) Dbms_output.put_line (10 / 2); dbms_output.put_line (10 * * 2); END;b, relational operator meaning = equal to,! =, ~ =, ^ = equal to greater than equal to example: DECLARE v_num1 NUMBER (2): = & N1; v_num2 NUMBER (2): = & N2 BEGIN IF (v_num1 = v_num2) THEN DBMS_OUTPUT.put_line ('num1 equals num2'); ELSIF (v_num1

V_num2) THEN DBMS_OUTPUT.put_line ('num1 is greater than num2'); END IF; IF (v_num1 v_num2) THEN DBMS_OUTPUT.put_line (' num1 is not equal to num2'); END IF;END; Note: & N1, & N2 are keyboard input values. C. Comparison operator meaning IS NULL is a null value BETWEEN...AND between the two IN equals a value in the list

Example:

DECLARE v_num1 NUMBER (2): & N1 begin IF (v_num1 BETWEEN 5 AND 10) THEN DBMS_OUTPUT.put_line ('num1 is between 5 and 10'); ELSE DBMS_OUTPUT.put_line ('num1 is not between 5 and 10'); END IF IF (v_num1 IN (3,8,10) THEN DBMS_OUTPUT.put_line ('num1 equals one of 3,8,10'); ELSE DBMS_OUTPUT.put_line ('num1 is not equal to one of 3,8,10'); END IF IF (v_num1 IS NULL) THEN DBMS_OUTPUT.put_line ('num1 is empty'); ELSE DBMS_OUTPUT.put_line ('num1 is not empty'); END IF;END; d, logic operator logic meaning AND logic is opposed to NOT logic or OR, such as IS NOT NULL, NOT IN

Example:

DECLARE v_b1 BOOLEAN: & N1; v_b2 BOOLEAN: = & N2; begin IF (v_b1 AND v_b2) THEN DBMS_OUTPUT.put_line ('AND-- true'); END IF; IF (v_b1 OR v_b2) THEN DBMS_OUTPUT.put_line (' OR-- true'); END IF IF (NOT v_b1) THEN DBMS_OUTPUT.put_line ('b_v1 inverts to TRUE'); null values plus numbers are still null values for END IF;END;e, character, and number operations: NULL + = NULL. Null value plus (concatenation) character, the result is the character: NULL | = 7, conditional control statement of PLSQL

-additional note: classification of PL/SQL process control statements:

Conditional control statements (conditional branch statements): IF statements and CASE statements. Loop statement: LOOP statement. Sequential statements: GOTO statements, NULL statements. Conditional control statement: if statement is divided into three kinds of conditional branch statements: simple conditional judgment: IF-THEN; double conditional branch: IF-THEN-ELSE; multiple conditional branch: IF-THEN-ELSIF. The grammatical format of simple conditional judgment: the grammatical format of IF condition THEN statements; END IF; double conditional branches: the grammatical format of IF condition THEN statements; ELSE statements; END IF; multiple conditional branches: IF condition THEN statements; ELSIF condition THEN statements; ELSE statements; END IF;b, conditional control statements: CASE statements use cas statements to perform multiple conditional branching operations, the sentence is simpler and the execution efficiency is better. There are two ways to use a case statement to deal with multiple conditional branches: the first is to use a single selector for equivalence comparison, and the second is to use multiple conditions for non-equivalent comparison.

Syntax format for equivalence comparison using a single selector in a-CASE statement:

CASE selector WHEN expression1 THEN sequence_of_statements1; WHEN expression2 THEN sequence_of_statements2;. WHEN expressionN THEN sequence_of_statementsN; [ELSE sequence_of_statements;] END CASE; example: enter a grade, determine which level you belong to, and print out. DECLARE v_grade CHAR (1): ='& nmistress' begin CASE v_grade WHEN'A 'THEN DBMS_OUTPUT.put_line (' excellent'); WHEN'B' THEN DBMS_OUTPUT.put_line ('medium'); WHEN'C 'THEN Dbms_Output.put_line (' General'); ELSE DBMS_OUTPUT.put_line ('incorrect input') END CASE;END

A variety of conditional comparison syntax formats are used in the-CASE statement:

CASE WHEN condition1 THEN sequence_of_statements1; WHEN condition2 THEN sequence_of_statements2;. WHEN conditionN THEN sequence_of_statementsN; [ELSE sequence_of_statements;] END CASE; example: enter the employee number, get the employee's salary, judge the salary, if the salary is less than 1500, plus 100, if the salary is less than 2500, the subsidy plus 80, if the salary is less than 5000, the subsidy plus 50. DECLAREv_sal emp01.sal%TYPE;v_empno emp01.empno%TYPE: = & no;BEGINSELECT sal INTO v_sal FROM emp01 WHERE empno = vandalism case WHEN v_sal < 1500 THEN UPDATE emp01 SET comm = nvl (comm, 0) + 100 where empno = vested empnoff; WHEN v_sal < 2500 THEN UPDATE emp01 SET comm = nvl (comm, 0) + 80 where empno = v_empno WHEN v_sal < 5000 THEN UPDATE emp01 SET comm = nvl (comm, 0) + 50 where empno = vested empno, COMMIT;END CASE;END;c, loop statement: basic loop syntax format: LOOP statement1;. END LOOP; example: print the numbers 1 to 10:. DECLARE v_cnt INT: = 1 WHILE condition LOOP statement1; statement2; begin LOOP DBMS_OUTPUT.put_line (v_cnt); EXIT WHEN v_cnt = 10; v_cnt: = v_cnt + 1; END LOOP;END LOOP;d, loop statement: WHILE loop syntax format: WHILE condition LOOP statement1; statement2;. END LOOP;DECLARE v_cnt INT: = 1 * begin while v_cnt

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.