I. Creating an instance of a package specification:Create or Replace package pack_op is
Procedurepro_print_ename (ID number,value2 out number);
Procedurepro_print_sal (ID number);
Functionfun_re_date (ID number) return date;
End Pack_op;
To create a package body:
Create or Replace package body Pack_op is
Procedure Pro_print_ename (ID number,value2 out number) Is/as
Name Emp.ename%type;
Begin
Select ename into name from EMP Whereempno=id;
Dbms_output.put_line (' Employee Name: ' | | name);
Value2:= 500;
Exception
Whenothers Then
Rollback;
End Pro_print_ename;
Procedure Pro_print_sal (ID number) Is/as
Salaryemp.sal%type;
Begin
Selectsal into salary from EMP where empno=id;
Dbms_output.put_line (' Staff salary: ' | | Salary);
Exception
When others then
Rollback;
End Pro_print_sal;
function fun_re_date (ID number) return date Is/as
Bedateemp.hiredate%type;
Begin
Select HireDate into Bedate from EMP where empno=id;
Returnbedate;
End Fun_re_date;
End Pack_op;
Procedures and functions created in the calling package
Declarevalue2 number; (
if the assignment value2 number: =100; value2 is the in out type)
Begin
Pack_op.pro_print_ename (7900,value2);
Dbms_output.put_line (value2);
End
EXEC pack_op.pro_print_sal (7900);
Select Pack_op.fun_re_date (7900) from dual;
To query information about procedures, functions, and packages:
user_objects Data dictionary view
Column object_name format A18
Select Object_name,object_type fromuser_objects
where object_type in (' PROCEDURE ', ' FUNCTION ', ' PACKAGE ', ' PACKAGE body ');
Two. Create a process with out parameters (default is in, input output in out)Create or Replace procedure test (value1varchar2,value2 out number) is
Identity number;
Begin
Select Sal into identity from EMP where empno=value1;
If identity<2000 then value2:=1000;
else value2:=500;
End If;
End test;
Call the process with out parameters: (value2 number: = 100--equivalent in)
declare value2 number;
Begin
Test (7900,value2);
Dbms_output.put_line (value2);
End
to grant the execution permission of a procedure to another user
GRANT EXECUTE on Find_emp to Scott; GRANT EXECUTE on swap to public;
Grant the Execute permission for the find_emp procedure to user Scott and grant all database users the right to execute the swap procedure.
Delete procedure syntax: Drop PROCEDURE procudure_name;
Precautions:
1.
stored procedure parameter does not take value range, in represents the incoming, out representation output, in out both (incoming maximum 4000)
2. Variable with a value range, followed by a semicolon---Define variable maximum value 32767
3. It is best to use the count (*) function to determine whether an action record exists before judging the statement
4. Use SELECT ... Into ... Assigning values to variables
5. Throw exception in code with raise+ exception name
three. Create a function1 has a return value without parameters
Create or Replace function Fun_hello
return VARCHAR2
Is/as
Begin
Return ' friend, hello ';
End Fun_hello;
Call function: Select Fun_hellofrom dual;
2) default in type parameter
Create or Replace function get_sal (dept_idnumber)
Return number//returns number type
As
V_sumsal Number (10): = 0;
Cursor Salary_cursor is select salary fromemployees where department_id = dept_id;
Begin
For C in Salary_cursor Loop
V_sumsal: = V_sumsal +c.salary;
End Loop;
return v_sumsal;
End
Call Way One
Begin
Dbms_output.put_line (Get_sal (80));
End
Call Mode Two
Select Get_sal from dual;
3) with out type parameters
Create or Replace function get_salsum (dept_id number,total_count out number)
Total_count as Out type parameter
return number
As
V_sumsal Number (10): = 0;
Cursor Salary_cursor is select salary fromemployees where department_id = dept_id;
Begin
//
the initial value of the Total_count is 0;
Total_count: = 0;
For C in Salary_cursor Loop
V_sumsal: = V_sumsal +c.salary;
Total_count: = Total_count + 1;
End Loop;
return v_sumsal;
End
Call mode
Declare
V_num (a);(
v_num Number: = 100--equivalent in)
Begin
Dbms_output.put_line (Get_salsum (80,v_num));
Dbms_output.put_line (V_num);
End
The formal argument must use only the database type, not the Pl/sql type. The return type of the function must also be a database type
Delete function: Drop functionfunction_name;
Small problem:SELECT INTO problem
The SELECT INTO statement stores the results of a select query into a variable, and you can store multiple columns in multiple variables at the same time.
must have a record or throw an exception (If no record is thrown no_data_found)
Solution:
BEGIN
SELECT Name
Into V_name
From T_student
WHERE id = ' 101 '
EXCEPTION
When No_data_found THEN
V_name: = ';
When others THEN
V_name: = ' more than one ';
End;
