Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Chapter 34 SQL DROP METHOD

Delete methods

The outline

DROP METHOD name [ FROM className ]
Copy the code

parameter

  • name– The name of the method to be deleted. A name is an identifier. Do not specify method argument parentheses. Names can be qualified (schema.name), can also be unqualified (Name). Unless specifiedFROM classNameClause, otherwise the unqualified method name takes the default schema name.
  • FROM className– Optional – If specified,FROM classNameClause removes the method from the given class. Note that you must specify the class name of the method (MethBonusCalc) instead of the SQL name (BonusCalc). If this clause is not specified, IRIS will search for the method in all classes of the schema and remove it. However, if no method with this name is found, or if more than one method with this name is found, an error code is returned. If deleting the method would result in an empty classDropMethod also deletes the class.

describe

Using the DROP METHOD command, you can delete a METHOD. When a method is deleted, IRIS revokes it from all users and roles that have been granted the method and deletes it from the database.

To delete a method, you must have the %DROP_METHOD administrative permission specified by the GRANT command. If you want to try to delete a method of a class that has a defined owner, you must log in as the class owner. Otherwise, the system generates a SQLCODE-99 error (permission conflict).

If the class definition that contains the method definition is a deployed class, the method cannot be deleted. This operation fails with SQLCODE-400 error and displays % MSG Unable to Execute DDL to modify the deployed class: ‘className’.

The following combination of name and From className is supported. Note that the FROM clause specifies the class package name and method name, not the SQL name. In these examples, the system-wide default schema name is SQLUser, which corresponds to the User class package:

  • DROP METHOD BonusCalc FROM methBonusCalc: Deletes the methodSQLUser.BonusCalc().
  • DROP METHOD BonusCalc FROM User.methBonusCalc: Deletes the methodSQLUser.BonusCalc().
  • DROP METHOD Test.BonusCalc FROM methBonusCalc: Deletes the methodSQLUser.BonusCalc().
  • DROP METHOD BonusCalc FROM Employees.methBonusCalc: Deletes the methodEmployees.BonusCalc().
  • DROP METHOD Test.BonusCalc FROM Employees.methBonusCalc: Deletes the methodd Employees.BonusCalc().

If the specified Method does not exist, Drop Method generates a SQLCODE-362 error. If the specified className does not exist, the Drop method generates a SQLCODE-360 error. If the specified Method can reference two or more methods, Drop Method generates a SQLcode-361 error; You must specify a className to resolve this ambiguity.

If you use the PROCEDURE CHARECTURE keyword defines the method, you can by calling $SYSTEM. SQL. Schema. ProcedureExists () method to determine whether the method exists in the current namespace. Methods defined using the PROCEDURE keyword can be dropped by the DROP method or DROP PROCEDURE.

You can also delete methods, which remove them from the class definition and then recompile the class, or delete the entire class.

The sample

The following embedded SQL example attempts to remove mymeth from the user.employee class.

ClassMethod DropMethod(a)
{
	&sql(
		DROP METHOD mymeth FROM User.Employee
	)
	if SQLCODE = 0{ w ! ."Method deleted" 
	} elseif SQLCODE = -360{ w ! ."Nonexistent class:",%msg 
	} elseif SQLCODE=-362{ w ! ."Nonexistent function:",%msg 
	} else{ w ! ."Unexpected error code:,SQLCODE
	}
}
Copy the code