PL/SQL Practice

Error creating stored procedure, you can use show errors to see
Warning:procedure created with compilation errors

Sql> Show errors;
Errors for PROCEDURE SCOTT. Insert_dept:
Line/col ERROR
-------- ------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------
2/58 pls-00103:encountered the symbol "(" when expecting one of the following:: =.), @% default character The symbol ": =" is substituted for "(" to continue.
3/61 pls-00103:encountered the symbol "(" when expecting one of the following:: =.), @% default character The symbol ": =" is substituted for "(" to continue.

Find stored Procedures
Sql> select Object_name,procedure_name,object_type from User_procedures;
drop procedure Proc1;

Character parameter cannot be added length, that is v_dname in VARCHAR2 can, but v_dname in VARCHAR2 (10) will be error-
Create or Replace procedure insert_dept (V_deptno in number,
V_dname in Varchar2,
V_loc in VARCHAR2) is
E_dept_err exception;
pragma exception_init (e_dept_err,-0001);
Begin
INSERT INTO Dept values (V_deptno, V_dname, V_loc);
Commit
exception
When E_dept_err Then
Dbms_output.put_line (' You deptno are not unique,please input unique deptno number! ');
When others then
Dbms_output.put_line (' other error ');

End

sql> exec insert_dept ("Zhangsan", ' Beijing ')
PL/SQL procedure successfully completed
sql> exec insert_dept ("Zhangsan", ' Beijing ')
You deptno are not unique,please input unique Deptno number!
PL/SQL procedure successfully completed

A stored procedure is a PL/SQL block with a name
Contains two types: stored procedures with parameters and no parameters
Afternoon:

Stored procedure with out parameters
Create or replace procedure PROC2
(V_empno number,v_ename out varchar2,v_sal out number)
Is
Begin
Select Ename,sal to V_ename,v_sal from EMP where empno=v_empno;
Dbms_output.put_line (' Employee name is: ' | | v_ename| | ' Employee salary is: ' | | V_sal);
exception
When No_data_found Then
Dbms_output.put_line (' Employee not exist,please input currect number ');
When others then
Dbms_output.put_line (' other errors ');
End

Defines the value of the output parameter in the two-parameter receive block
sql> var v_name varchar2 (10);
sql> var v_salary number;
sql> exec proc2 (7369,:v_name,:v_salary); ----Call system parameters, add the front:
Employee Name Is:smith Employee Salary is:2600
PL/SQL procedure successfully completed
V_name
---------
SMITH
V_salary
---------
2600

sql> Print V_name
V_name
---------
SMITH

sql> Print V_salary
V_salary
---------
2600

Or use anonymous blocks to execute proc2.
Declare
V_name Emp.ename%type;
V_salary Emp.sal%type;
Begin
PROC2 (7369,:v_name,:v_salary);
End

Stored procedures with input and output type parameters
Create or Replace procedure proc3 (V_empno in Out number,
V_ename out VARCHAR2,
V_sal out number) is
Begin
Select Empno, ename, Sal
Into V_empno, V_ename, v_sal
From EMP
where empno = V_empno;
Dbms_output.put_line (V_empno);
Dbms_output.put_line (V_ename);
Dbms_output.put_line (v_sal);
exception
When No_data_found Then
Dbms_output.put_line (' Employee not exist,please input currect number ');
When others then
Dbms_output.put_line (' other errors ');
End

Declare
V_empno Emp.empno%type;
V_ename Emp.ename%type;
V_sal Emp.sal%type;
Begin
v_empno:=7369;
PROC3 (v_empno,v_ename,v_sal);
End

Function
function to return specific data
function is called as part of an expression
Functions can simplify the development of client applications
Improve the execution performance of your application.

Functions without any arguments
Create or Replace function fun1
return number
Is
V_sum_sal Emp.sal%type;
Begin
Select sum (SAL) into V_sum_sal from EMP where deptno=10;
return v_sum_sal;
End

Declare
V_sumsal Emp.sal%type;
Begin
V_sumsal: = fun1;
Dbms_output.put_line (v_sumsal);
End

Upgrade:
Create or Replace function fun2
(V_deptno Emp.deptno%type)
return number
Is
V_sum_sal Emp.sal%type;
Begin
Select sum (SAL) into V_sum_sal from EMP where Deptno=v_deptno;
return v_sum_sal;
End

Declare
V_sumsal Emp.sal%type;
Begin
V_sumsal: = fun2 (10);
Dbms_output.put_line (v_sumsal);
End

Query function condition
Select Name,text from User_source;

sql> Create or Replace function fun2
2
3 (v_empno number, v_ename out varchar2,v_sal out number)
4
5 return number
6
7 is
8
9 begin
10
One-select Ename,sal into V_ename,v_sal from EMP where empno=v_empno;
return v_sal;
The end;
14/
Function created

Sql> Declare
2
3 V_salary Emp.sal%type;
4 V_ename Emp.ename%type;
5 V_sal Emp.sal%type;
6
7 begin
8 V_salary: = Fun2 (7369,v_ename,v_sal);
9 Dbms_output.put_line (' Salary is: ' | | V_salary);
10
one end;
12/
Salary is:6800
PL/SQL procedure successfully completed

Create or Replace function fun_sum_sal
(V_empno in number)
return number
Is
V_sumsal Emp.sal%type;
Begin
Select (SAL+NVL (comm,0)) *12 into V_sumsal from EMP where empno=v_empno;
return v_sumsal;
End

Declare
V_sumsalary number;
Begin
V_sumsalary: = Fun_sum_sal (7369);
Dbms_output.put_line (v_sumsalary);
End

PL/SQL Practice