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

Preparation article

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 80

1. SQL vs. PLSQL

What is SQL99?

(1) is the rule for operating all relational databases

(2) it is the fourth generation language.

(3) it is a structured query language

(4) as long as a legal and reasonable order is issued, the corresponding results will show

Characteristics of SQL

(1) highly interactive and non-procedural

(2) strong database manipulation ability, only need to send commands, do not need to pay attention to how to implement

(3) when operating with multiple tables, automatic navigation is simple, for example:

Select emp.empno,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno

(4) easy to debug, error prompt and straightforward

(5) SQL emphasizes results.

What is PLSQL?

Is dedicated to the Oracle server, on the basis of SQL, added some procedural control statements, called PLSQL

Process includes: type definition, judgment, loop, cursor, exception or exception handling.

PLSQL emphasizes process

Why use PLSQL?

Because SQL is the fourth generation imperative language, it can not show the business that handles the process, so it is necessary to use a process programming language to make up for the deficiency of SQL.

SQL and PLSQL are not a substitute relationship, but a remedial relationship.

The complete structure of the PLSQL program is as follows:

[declare] variable declaration; variable declaration; begin DML/TCL operation; DML/TCL operation; [exception] exception handling; exception handling; end;/

Note: in PLSQL programs, the; sign indicates the end of each statement, and / indicates the end of the entire PLSQL program.

The tools for writing PLSQL are:

(1) SQLPLUS tool

(2) SQLDeveloper tool

(3) third-party tools (PLSQL & others)

What is the difference between PLSQL and SQL execution:

(1) SQL is executed in a single way

(2) PLSQL is executed as a whole and cannot be executed individually. The whole PLSQL ends with /, and each statement ends with the; sign.

2. PLSQL type

Write a PLSQL program that outputs the "hello world" string, syntax: dbms_output.put_line ('string to be output')

Begin-- outputs the string dbms_output.put_line ('hello Hello') to the SQLPLUS client tool; end;/

Note:

Dbms_output is an output object in oracle

Put_line is a method of the above object, which is used to output a string with automatic line wrapping

Set to display the execution result of PLSQL program. By default, the execution result of PLSQL program is not displayed. Syntax: set serveroutput on/off

Set serveroutput on

Use primitive type variables, constants, and comments to find the sum of 10 percent 100

Declare-define variable mysum number (3): = 0; tip varchar2 (10): = 'result'; begin / * business algorithm * / mysum: = 10 + 100; / * output to controller * / dbms_output.put_line (tip | | mysum); end;/

Output employee 7369's name and salary in the following format: employee 7369's name is SMITH, salary is 800. syntax: use table name. Field type

Declare-define two variables: name and salary 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, and the value of sal into the psal variable select ename,sal into pename,psal from emp where empno = 7369 -- output dbms_output.put_line (the name of employee '7369 is' | pename | |', salary is'| | psal); end;/

Output the name and salary of employee 7788 in the following format: employee 7788 is named SMITH, salary is 3000, syntax: use table name% rowtype

Declare emp_record emp%rowtype;begin select * into emp_record from emp where empno = 7788; dbms_output.put_line (the name of employee '7788 is' | emp_record.ename | |', salary is'| | emp_record.sal); end;/

When to use type and when to use rowtype?

When defining a variable with the same type as a field in the table, you can use% type

When defining a variable, which is exactly the same as the entire table structure, you can use% rowtype, in this case through the variable name. Field name, you can take the corresponding value in the value variable

% type is commonly used in projects

3. PLSQL judgment

Use if-else-end if to show what day it is today, is it a "working day" or a "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 ('Saturday', 'Sunday') then dbms_output.put_line ('rest day'); else dbms_output.put_line ('working day'); end if;end;/

Receive values from the keyboard and use if-elsif-else-end if to display "age"

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.