Oracle Learning Summary 4

1: Three paradigms
A: A table must have a primary key, the column is not divided.
B: If a table contains two fields as the primary key, then the other fields cannot be partially dependent on the two fields.

2:PL Sql:procedural Language (Process language)

Write a small program that outputs Hello world.
Begin
Dbms.output.put_line (' Hello world! ');
End
/

Set serveroutput on; --Set server-side output

Write a declaration variable, then assign the value, and finally output the applet.

Declare
V_name varchar (20);
Begin
V_name: = ' Jack ';
Dbms_output.put_line (V_name);
End
/

Exception catch exception, if an error occurs during the execution of the program, the exception will be run to
Exception code block, others then is that the above exception is not captured when it snaps
All the exceptions.

Declare
V_num number;
Begin
V_num: = 0;
Dbms_output.put_line (2/v_num);
exception
When others then
Dbms_output.put_line (' error ');
End
/

3: Show Current user
Show user;

4: Declaration of variables
Declare
ID Number (4): = 1001;
Name varchar2: = ' Jack ';
Begin
Dbms_output.put_line (name);
End
/

The declared variables are used to put the data inside the table, but the table structure may change, in order to
As the table structure changes, the type of the declared variable also changes, and you can use the%type

Declare
ID Number (4): = 1001;
Name Emp.empno%type: = ' Jack ';
Begin
Dbms_output.put_line (name);
End
/

5: Declaring data type table represents array type
Declares a data type Type_table_name
Declare
Type Type_table_name is table of Emp.empno%type index by Binary_integer;
V_empnos Type_table_name;
Begin
V_empnos (0): = 1001;
V_empnos (1): = 1002;
V_empnos (-1): = 1003;
Dbms_output.put_line (V_empnos (-1));
End
/

6: Declaring data type record, equivalent to class in Java

Declare
Type type_record_dept is record
(
Deptno Dept.deptno%type,
Dname Dept.dname%type,
Loc Dept.loc%type
);
V_dept type_record_dept;
Begin
V_dept.deptno: = 1001;
V_dept.dname: = ' sales ';
V_dept.loc: = ' Shanghai ';
Dbms_output.put_line (V_dept.loc);
End
/

You can use%rowtype to declare a record so that, even if the data structure of the Dept table changes, the variable structure
Will change with it.
Declare
V_dept Dept%rowtype;
Begin
V_dept.deptno: = 1001;
V_dept.dname: = ' sales ';
V_dept.loc: = ' Shanghai ';
Dbms_output.put_line (V_dept.loc);
End
/

The SELECT statement in 7:PL

Select Empno to V_empno from emp where empno= ' 1001 '; The SELECT statement in--PL has and only one data can be found

Declare
V_empno Emp.empno%type;
V_ename Emp.ename%type;
Begin
Select Empno,ename to V_empno,v_ename from emp where empno= ' 1001 ';
Dbms_output.put_line (V_empno | | "| | V_ename);
End
/

Use%rowtype to put a record
Declare
V_emps Emp%rowtype;
Begin
SELECT * into V_emps from EMP where empno=1001;
Dbms_output.put_line (V_emps.empno | | "| | V_emps.ename | | ' ' | | V_emps.sal);
End
/

8:PL DML statements in SQL

To insert data into a table:
Declare
V_empno Emp.empno%type: = 1006;
V_ename emp.ename%type: = ' Markson ';
Begin
INSERT into EMP (empno,ename) values (v_empno,v_ename);
Commit
End
/

Updating data in a table
Declare
V_deptno Emp.deptno%type;
Begin
V_deptno: = 10;
Update emp set SAL=SAL/2 where Deptno=v_deptno;
Dbms_output.put_line (' Affected ' | | sql%rowcount| | ' Record! ');
Commit
End
/

Sql%rowcount:sql is a keyword that rowcount indicates how much effect the previous statement has.

9:PL the DDL language in SQL
Precede the EXECUTE statement with execute immediate, and the CREATE statement is enclosed in single quotation marks, if there is a single quotation mark inside the single quotation mark,
Then use double quotation marks instead of single quotes.
Begin
Execute immediate ' CREATE TABLE T (ID number (4), name varchar () not null) ';
End

10:PL branch structure in SQL if elsif else

Declare
V_sal Emp.sal%type;
Begin
Select Sal into V_sal from EMP where empno=1001;
if (v_sal<=1000) then
Dbms_output.put_line (' low ');
elsif (v_sal<=2000) Then
Dbms_output.put_line (' Middle ');
Else
Dbms_output.put_line (' High ');
End If;
End
/

11:PL loop structure in SQL
The loop in PL SQL must start with loop and end With End loop
Equivalent to do ... while structure in Java
First cycle and then judge:
Declare
I binary_integer: = 1;
Begin
Loop
Dbms_output.put_line (i);
I: =i+1;
Exit when (i>=11);
End Loop;
End
/

Equivalent to the while structure in Java, the first judgment, in the loop:
Declare
I binary_integer: = 1;
Begin
while (i<11) loop
Dbms_output.put_line (i);
I: =i+1;
End Loop;
End
/

For loop: equivalent to the forhance loop in Java, can be either positive or reverse:

Declare
I binary_integer: = 1;
Begin
For k in 1..10 Loop
Dbms_output.put_line (k);
End Loop;

For k in reverse 1..10 loop
Dbms_output.put_line (k);
End Loop;
End
/

Cursor: Cursors
Recursion: Recursion
Drigger: Trigger

Oracle Learning Summary 4