"Go" Oracle plsql Basic Learning

--oracle practice;
/**************************************************pl/sql Programming Basics ************************************************* **************/
--firstday
--"" "Data type
--Create table
CREATE TABLE T_cscustomer
(
Cust_no VARCHAR2 () primary key NOT NULL,
person_id VARCHAR2 (12),
Group_no VARCHAR2 (12),
cust_address_id VARCHAR2 (20),
Organiseid VARCHAR2 (10),
Cust_name VARCHAR2 (128),
Cust_type VARCHAR2 (2),
Serve_password VARCHAR2 (128),
Regioncode VARCHAR2 (4),
OPERATOR VARCHAR2 (16),
Opendate DATE,
REMARK VARCHAR2 (500),
ADDRESS VARCHAR2 (256),
Cust_kind VARCHAR2 (4),
Linkman VARCHAR2 (64),
Linkphone VARCHAR2 (64),
Linkmobile VARCHAR2 (64),
company_id VARCHAR2 (10),
Install_address VARCHAR2 (256)
)

Set Serveroutput on

--%type type
--sql>
Declare
Var_ename T_cscustomer.cust_name%type;
Var_phone T_cscustomer.linkphone%type;

Begin
SELECT Cust_name,linkphone
Into Var_ename,var_phone
From T_cscustomer
where cust_no= ' 0061121890 ';
Dbms_output.put_line (var_ename| | ' The phone number is: ' | | Var_phone);
End
/

--sql>
Declare
Var_ename VARCHAR2 (200);
Var_phone VARCHAR2 (200);

Begin
SELECT Cust_name,linkphone
Into Var_ename,var_phone
From T_cscustomer
where cust_no= ' 0061121890 ';
Dbms_output.put_line (var_ename| | ' The phone number is: ' | | Var_phone);
End
/

--record type
--sql>
Declare
Type Emp_type is record
(
Var_ename varchar2 (20),
Var_phone varchar2 (20),
Var_sal VARCHAR2 (200)
);
Empinfo Emp_type;
Begin
Select Cust_name,linkphone,address into Empinfo from T_cscustomer where cust_no= ' 0110542709 ';
Dbms_output.put_line (' Employees ' | | empinfo.var_ename| | ' The phone is ' | | empinfo.var_phone| | ', address is ' | | Empinfo.var_sal);
End
/

--%rowtype type
--sql>
Declare
Rowvar_emp T_cscustomer%rowtype;
Begin
SELECT * into Rowvar_emp from T_cscustomer where cust_no= ' 0110542709 ';
/* Output Information */
Dbms_output.put_line (' Employees ' | | rowvar_emp.cust_name| | ' The phone is ' | | rowvar_emp.linkphone| | ', address is ' | | rowvar_emp.address);
End
/

--variables, constants
Var_countryname VARCHAR2 (50): = ' China ';
CON_DAY constant integer:=365;

--secondday
--"" "Process Control
--if
--sql>
Declare
Var_name1 varchar2 (20);
Var_name2 varchar2 (20);
Begin
var_name1:= ' East ';
var_name2:= ' Xiaoke ';
If Length (var_name1) < Length (var_name2) Then
Dbms_output.put_line (' String ' | | var_name1| | ' " Longer than the string "' | | var_name2| | ' " The length of the small ');
End If;
End
/
--if ElseIf
--sql>
Declare
num_age int: = 55;
Begin
If Num_age>=56 Then
Dbms_output.put_line (' You can apply for retirement ');
Elsif num_age<56 Then
Dbms_output.put_line (' You are less than 56 years old, you can not apply for retirement! ‘);
Else
Dbms_output.put_line (' Sorry, the age is not legal! ‘);
End If;
End
/

--sql>
Declare
num_age int: = 55;
Aboutinfo VARCHAR2 (50);
Begin
If Num_age>=56 Then
Aboutinfo:= ' You can apply for retirement ';
Elsif num_age<56 Then
Aboutinfo:= ' You are less than 56 years old and can not apply for retirement! ‘;
Else
Aboutinfo:= ' Sorry, the age is not legal! ‘;
End If;
Dbms_output.put_line (Aboutinfo);
End
/

--case when
--sql>
declare
season int:=3;
Aboutinfo VARCHAR2 (50);
Begin
Case season
when 1 then
Aboutinfo: = season| | ' Quarterly includes 1、2、3月份 ';
When 2 then
Aboutinfo: = season| | ' Quarterly includes 4、5、6月份 ';
When 3 then
Aboutinfo: = season| | ' Quarterly includes 7、8、9月份 ';
When 4 then
Aboutinfo: = season| | ' Quarterly includes 10, 11, December ';
Else
Aboutinfo: = season| | ' The quarterly is illegal ';
End case;
Dbms_output.put_line (Aboutinfo);
End;
/

--"" Loop statement
--loop statement runs until exit when End_condition_exp is true exits
declare
sum_i int:=0;
I int:=0;
Begin
Loop
i:=i+1;
Sum_i: =sum_i + i;
Exit when i = +;
End Loop;
Dbms_output.put_line (The and of the first 100 natural numbers is: ' | | Sum_i);
End;
/
--while statement
declare
sum_i int: = 0;
I int: = 0;
Begin
While i<100 loop
i:=i+1;
Sum_i:=sum_i+i;
end loop;
Dbms_output.put_line (The and of the first 100 natural numbers are: ' | | Sum_i);
End;
/
--for statement
declare
sum_i int:=0;
Begin
For i in reverse 1..100 loop--reverse represents I descending from 100
sum_i:= sum_i+i;
End Loop;
Dbms_output.put_line (The and of the first 100 natural numbers is: ' | | Sum_i);
End;
/

--"" Cursor
/*
Cursor properties:
Cur_tmp%found At least one row of data is true;
Cur_tmp%notfound vs.%found
Cur_tmp%rowcount Returns the number of rows affected by the SQL statement
Cur_tmp%isopen The cursor is true when it is opened
*/
--Show cursor
Set serveroutput on
declare
Cursor cur_emp (var_name in varchar2:= ' Lili ')
is select Cust_no,cust_name,address
from T_cscustomer
where cust_name like var_name| | ' %‘;
Type record_emp is record
(
Var_empno t_cscustomer.cust_no%type,
Var_empname t_cscustomer.cust_name% Type,
var_empaddress t_cscustomer.address%type
);
Emp_row record_emp;
Begin
Dbms_output. ENABLE (buffer_size = null); --Indicates the output buffer is unrestricted
Open cur_emp (' Liu ');
fetch cur_emp into Emp_row;
while Cur_emp%found Loop
Dbms_output.put _line (emp_row.var_empname| | ' The number is ' | | emp_row.var_empno| | ', address is ' | | emp_row.var_empaddress);
fetch cur_emp into Emp_row;
End Loop;
Close cur_emp;
End;
/

--for using the cursor does not open cursors, read cursors, close cursors Oracle Internal Auto-complete
Declare
type Emp_type is record
(
Var_ename t_ Cscustomer.cust_name%type,
Var_phone t_cscustomer.linkphone%type,
var_sal t_cscustomer.address%type
);
Empinfo Emp_type;
Cursor cur_emp
is
Select Cust_name var_ename,linkphone var_phone,address var_sal from T_cscustomer where addr ESS like '% strokes South%0402 room% ';
Begin
Dbms_output. ENABLE (buffer_size = null); --Indicates the output buffer is unrestricted
--open cur_emp;
--fetch cur_emp into Empinfo;
--dbms_output.put_line (' Total Data ' | | cur_emp%rowcount| | ' Article ');
for Empinfo in Cur_emp loop
Dbms_output.put_line (' Employees ' | | empinfo.var_ename| | ' The phone is ' | | empinfo.var_phone| | ', address is ' | | Empinfo.var_sal);
End Loop;
End;
/

Declare
Cursor Cur_emp
Is
Select Cust_name var_ename,linkphone var_phone,address var_sal from T_cscustomer where address like '% strokes South%0402 room% ';
Begin
Dbms_output. ENABLE (buffer_size = null); --Indicates that the output buffer is unrestricted
For Empinfo in Cur_emp loop
Dbms_output.put_line (' Employees ' | | empinfo.var_ename| | ' The phone is ' | | empinfo.var_phone| | ', address is ' | | Empinfo.var_sal);
End Loop;
End
/

Begin
Dbms_output. ENABLE (buffer_size = null); --Indicates that the output buffer is unrestricted
For Empinfo in (select Cust_name var_ename,linkphone var_phone,address var_sal from T_cscustomer where address like '% strokes South%0 402 Room% ') loop
Dbms_output.put_line (' Employees ' | | empinfo.var_ename| | ' The phone is ' | | empinfo.var_phone| | ', address is ' | | Empinfo.var_sal);
End Loop;
End
/

--"Exception handling
/*
Predefined exceptions
Custom exceptions
*/
--pre-defined exceptions
declare
Var_empno t_cscustomer.cust_no%type;
Var_empname T_cscustomer.cust_name%type;
Begin
Select Cust_no,cust_name into Var_empno,var_empname from T_cscustomer where cust_no like ' 00% ';
If Sql%found then
Dbms_output.put_line (' Employee number: ' | | var_empno| | ', Name: ' | | Var_empname);
End If;
Exception
When Too_many_rows and then
Dbms_output.put_line (' return record more than one row ');
when No_data_found and then
Dbms_ Output.put_line (' No data record ');
End;
/
--Custom Exception
Declare
Primary_iterant exception;--defines an exception variable
pragma exception_init (primary_iterant,- 00001);--associated error number and exception variable name
Begin
INSERT INTO dept_tmp values (' 0 ', ' General Department ', ' Beijing ');
Dbms_output.put_line (' Insert Dept_tmp with default value success! ‘);
Exception
When Primary_iterant and then
Dbms_output.put_line (' primary key not allowed to repeat! ‘);
End;
/

/************************************************** stored procedures, functions, triggers, Packages ********************************************** *****************/
--"" Stored procedure
--Viewing errors
Show errors;

--Create or replace pro_inserttmp
drop table dept_tmp;
CREATE TABLE Dept_tmp (
Dept_no VARCHAR2 () primary key NOT NULL,
Dept_name VARCHAR2 (50),
Location VARCHAR2 (200)
);
Create or replace procedure pro_inserttmp is
Begin
INSERT into dept_tmp values (1, ' marketing department ', ' join ');
Commit
Dbms_output.put_line (' Insert dept_tmp new record successfully ');
End Pro_inserttmp;
/
--Executive Pro_inserttmp
--execurte pro_inserttmp;
exec pro_inserttmp;

--Call Pro_inserttmp in the program block
Set Serverout on
Begin
Pro_inserttmp
End
/

--third day2015-02-03
/** stored procedure parameter procedures include: in input parameters, out output parameters, in-out can be modified input parameters, and as an output parameter **/
-->>in
Create or Replace procedure pro_insertdept (V_deptno in Varchar2,v_deptname in Varchar2,v_loc on VARCHAR2) is
Begin
INSERT into dept_tmp values (V_DEPTNO,V_DEPTNAME,V_LOC);
Commit
Dbms_output.put_line (' Insert dept by in Parameter Success! ‘);
End Pro_insertdept;
/

--Do not pass in the parameters sequentially, specify parameter values
Begin
Pro_insertdept (v_deptname=> ' purchasing department ',v_loc=> ' Chengdu ',v_deptno=> ' 2 ');
End
/
--Incoming parameters sequentially
Begin
Pro_insertdept (' 3 ', ' marketing department ', ' Shenzhen ');
End
/
--Mixing incoming parameters
Begin
Pro_insertdept (' 4 ',v_loc=> ' Chengdu ',v_deptname=> ' engineering Department ');
End
/

-->>out
Create or Replace procedure pro_selectdept (V_deptno in Varchar2,v_deptname out Dept_tmp.dept_name%type,v_loc out Dept_ Tmp.location%type) is
Begin
Select Dept_name,location into V_deptname,v_loc from dept_tmp where Dept_no=v_deptno;
exception
When No_data_found Then
Dbms_output.put_line (' The numbered department does not exist! ‘);
End Pro_selectdept;
/

Set Serveroutput on
Declare
V_deptname Dept_tmp.dept_name%type;
V_loc Dept_tmp.location%type;
Begin
Pro_selectdept (' 1 ', v_deptname,v_loc);
--if V_deptname = "Then
Dbms_output.put_line (v_deptname| | ' Located in: ' | | V_LOC);
--end if;
End
/

-Execution
Variable v_deptname varchar2 (50);
Variable V_loc varchar2 (50);
exec pro_selectdept (' 2 ',: V_deptname,:v_loc);
Print V_deptname V_loc;
Select:v_deptname,:v_loc from dual;

-->> in Out
Create or Replace procedure Pro_square (num in Out Number,flag in Boolean) is
I int:=2;
Begin
If Flag then
num: = Power (num,i); --Calculate squared
Else
Num: = sqrt (num); --Calculate square root
End If;
End Pro_square;
/

Declare
N_number number;
N_TMP number;
B_flag Boolean;
Begin
B_flag:=false;
n_tmp:=3;
n_number:=n_tmp;
Pro_square (N_number,b_flag);
If B_flag Then
Dbms_output.put_line (n_tmp| | ' The square is: ' | | N_number);
Else
Dbms_output.put_line (n_tmp| | ' The square root is: ' | | N_number);
End If;
End
/

--in parameter Default value
Create or Replace procedure Pro_insertdeptdefault (V_deptno in Varchar2,v_deptname in varchar2 default ' fully Hopewell ', V_loc in varchar2 Default ' Beijing ') is
Primary_iterant exception;--defines an exception variable
pragma exception_init (primary_ ITERANT,-00001);--associated error number and exception variable name
Begin
INSERT INTO dept_tmp values (v_deptno,v_deptname,v_loc);
Commit;
Dbms_output.put_line (' Insert dept_tmp with default value succeeded! ‘);
Exception
When Primary_iterant and then
Dbms_output.put_line (' primary key not allowed to repeat! ‘);
End Pro_insertdeptdefault;
/
--Specify the name of the value
declare
row_dept dept_tmp%rowtype;
Begin
Pro_insertdeptdefault (' 5 ', V_loc = ' Taiyuan ');
Select * to Row_dept from dept_tmp where dept_no= ' 5 ';
dbm S_output.put_line (' Department Name: ' | | row_dept.dept_name| | ', located in: ' | | Row_dept.location);
Exception
When No_data_found and then
Dbms_output.put_line (' No related data found! ‘);
End;
/

drop table t_emp;
CREATE TABLE T_emp (
Emp_no number primary key is not NULL,
Emp_name varchar2 (20),
Age number,
Sal number,
Job Varchar2 (20),
Dept_no number,
Address VARCHAR2 (200),
HireDate Date
);
INSERT into t_emp values (1, ' Wang Li ', 22,9000, ' Accounting ', 3, ' Shenzhen Beijing Road Obama ', sysdate);
--"" "function
Create or Replace function Get_avg_pay (num_deptno number) return number is
Num_avg_pay number;
Begin
Select AVG (SAL) into Num_avg_pay from T_emp where Dept_no=num_deptno;
Return (round (num_avg_pay,2));
exception
When No_data_found Then
Dbms_output.put_line (' The employee of the department number does not exist ');
return (0);
End Get_avg_pay;
/

--calling a function in a program block
Declare
Avg_pay number;
Begin
Avg_pay:=get_avg_pay (3);
Dbms_output.put_line (' Department numbered 3, the average wage is: ' | | Avg_pay);
End
/

--delete function
Drop function Get_avg_pay;

--"" "Trigger
--Syntax format
Create or Replace Trigger Tri_name
[Before|after|instead of] Tri_event
On Table_name|view_name|user_name|db_name
[For each row] [When Tri_condition]
Begin
Plsql_sentences;
End Tri_name;
/

CREATE TABLE Dept_log (
Operate_tag VARCHAR2 (10),
Operate_time Date
);
--statement-level triggers
Create or Replace Trigger tri_dept
Before insert or UPDATE or delete
On dept_tmp
Declare
V_tag varchar2 (20);
Begin
If inserting then
v_tag:= ' Insert ';
elsif updating Then
v_tag:= ' modification ';
elsif deleting Then
v_tag:= ' delete ';
End If;
INSERT into Dept_log values (v_tag,sysdate);
End Tri_dept;
/
INSERT into dept_tmp values (6, ' Business Consulting department ', ' Changchun ');
Update dept_tmp set location= ' Shenyang ' where dept_no= ' 6 ';
Delete from dept_tmp where dept_no= ' 6 ';

--row-level triggers
CREATE TABLE T_goods (
ID int PRIMARY key NOT NULL,
Good_name VARCHAR2 (50)
);

Create sequence seq_goods_id;

The--:new.id--column identifier, which is used to identify the new value ": New.column_name" for a column in the current row, typically used in insert and UPDATE statements
--:old.id--column identifier, which is used to identify the original value ": New.column_name" for a column in the current row, typically used in delete and UPDATE statements
Create or Replace Trigger Tri_insert_goods
Before insert
On T_goods
For each row
Begin
Select Seq_goods_id.nextval into:new.id from dual;
End
/

Insert into T_goods (good_name) VALUES (' Apple ');
Insert into T_goods (id,good_name) VALUES (9, ' peach ');

--Replacement Trigger
--the replacement trigger is defined on the view (a database object), not on the table.
CREATE VIEW View_emp_dept
As select Emp_no,emp_name,dept_tmp.dept_no,dept_name,job,hiredate from T_emp,dept_tmp where T_emp.dept_no=dept_ Tmp.dept_no;

Create or replace trigger Tri_insert_view
instead of insert
on view_emp_dept
for each row
declare
row _dept Dept_tmp%rowtype;
Begin
Select * into Row_dept from dept_tmp where dept_no=:new.dept_no;
If Sql%notfound then
insert into de Pt_tmp (Dept_no,dept_name) VALUES (: New.dept_no,:new.dept_name);
End If;
INSERT INTO t_emp (emp_no,emp_name,dept_no,job,hiredate) VALUES (: New.emp_no,:new.emp_name,:new.dept_no,: New.job,:new.hiredate);
End Tri_insert_view;
/

--rollback can no longer be used in triggers
Create or Replace Trigger TRI_INSERT_VIEW2
Instead of insert
On view_emp_dept
For each row
Declare
Row_dept Dept_tmp%rowtype;
Begin
SELECT * into Row_dept from dept_tmp where dept_no=:new.dept_no;
If Sql%notfound Then
Insert into dept_tmp (Dept_no,dept_name) VALUES (: New.dept_no,:new.dept_name);
End If;
Insert into T_emp (emp_no,emp_name,dept_no,job,hiredate) VALUES (: New.emp_no,:new.emp_name,:new.dept_no,:new.job,: New.hiredate);
exception
--when No_data_found Then
--dbms_output.put_line (the corresponding department number is not found in the ' departmental table ');
--rollback;
End Tri_insert_view;
/

--If there is no number 10 in the department, an error will be
Insert into view_emp_dept (emp_no,emp_name,dept_no,dept_name,job,hiredate) VALUES (8888, ' Orient ', ten, ' ACCOUNTING ', ' Cashier ', sysdate);
Commit

SELECT * from view_emp_dept where emp_no=8888;

--User Event Trigger
CREATE TABLE T_ddl_oper_log (
Db_obj_name varchar2 (20),
Db_obj_type varchar2 (20),
Oper_action varchar2 (20),
Oper_user varchar2 (20),
Oper_date Date
);

Create or Replace Trigger Tri_ddl_oper
Before create or drop or alter
On Liulei.schema
Begin
INSERT into T_ddl_oper_log values (ora_dict_obj_name,ora_dict_obj_type,ora_sysevent,ora_login_user,sysdate);
End
/
CREATE TABLE T_test (ID number);
CREATE View view_test as select Emp_no,emp_name from T_emp;
Drop View view_test;
SELECT * from T_ddl_oper_log;

--"" "Package
--Grammar specification
Create or Replace package pack_name is
[Declare_variable];
[Declare_type];
[Declare_cursor];
[Declare_function];
[Declare_procedure];
end [Pack_name];

--Create a "specification" of a package without the body part of the declaration body
Create or Replace package pack_emp is
function Fun_avg_sal (num_deptno number) return number;--get the average salary for the specified department
Procedure Pro_regulate_sal (var_job varchar2,num_proportion number)--raising the salary of a specified position in a specified proportion
End Pack_emp;
/

--Grammar package body
Create or Replace package body Pack_name is
[Inner_variable]
[Cursor_body]
[Function_title]
{
Begin
Fun_plsql;
[Exception]
[Dowith_sentences;]
End [Fun_name]
}
[Procedure_title]
{
Begin
Pro_plsql;
[Exception]
[Dowith_sentences;]
End [Pro_name]
}
...
End [Pack_name]

Create or Replace package body pack_emp is
function Fun_avg_sal (num_deptno number) return number is
Num_avg_sal number;
Begin
Select AVG (SAL) into Num_avg_sal from T_emp where Dept_no=num_deptno;
return (num_avg_sal);
exception
When No_data_found Then
Dbms_output.put_line (' There is no employee record for this department number! ‘);
return 0;
End Fun_avg_sal;

Procedure Pro_regulate_sal (var_job varchar2,num_proportion number) is
Begin
Update t_emp set sal=sal* (1+num_proportion) where job=var_job;
End Pro_regulate_sal;
End Pack_emp;
/

--Call Package
Set Serveroutput on
Declare
Num_deptno T_emp.dept_no%type;
Var_job T_emp.job%type;
Num_avg_sal T_emp.sal%type;
Num_proportion number;
Begin
num_deptno:=5;
Num_avg_sal:=pack_emp.fun_avg_sal (NUM_DEPTNO);
Dbms_output.put_line (num_deptno| | ' Department's average salary is: ' | | Num_avg_sal);

Var_job: = ' Saleman ';
num_proportion:=0.1;
Pack_emp.pro_regulate_sal (var_job,num_proportion);
End
/

"Go" Oracle plsql Basic Learning